MySQL数据库基础知识及优化

MySQL数据库基础知识及优化必会的知识点，你掌握了多少？

推荐阅读：

• 这些必会的计算机网络知识点你都掌握了吗

• 关于数据库事务和锁的必会知识点，你掌握了多少？

• 关于数据库索引，必须掌握的知识点

目录

• SQL语句基础知识及优化
  • SQL语句主要分为哪几类　＊
  • SQL约束有哪些？　＊＊
  • 什么是子查询？　＊＊
  • 了解MySQL的几种连接查询吗？　＊＊＊
  • mysql中in和exists的区别？　＊＊
  • varchar和char的区别？　＊＊＊
  • MySQL中int(10)和char(10)和varchar(10)的区别？　＊＊＊
  • drop、delete和truncate的区别？　＊＊
  • UNION和UNION ALL的区别？　＊＊
  • 什么是临时表，什么时候会使用到临时表，什么时候删除临时表？　＊
  • 大表数据查询如何进行优化？　＊＊＊
  • 了解慢日志查询吗？统计过慢查询吗？对慢查询如何优化？　＊＊＊
  • 为什么要设置主键？　＊＊
  • 主键一般用自增ID还是UUID？　＊＊
  • 字段为什么要设置成not null?　＊＊
  • 如何优化查询过程中的数据访问？　＊＊＊
  • 如何优化长难的查询语句？　＊＊
  • 如何优化LIMIT分页？　＊＊
  • 如何优化UNION查询　＊＊
  • 如何优化WHERE子句　＊＊＊
  • SQL语句执行的很慢原因是什么？　＊＊＊
  • SQL语句的执行顺序?　＊
• 数据库优化
  • 大表如何优化？　＊＊＊
  • 什么是垂直分表、垂直分库、水平分表、水平分库？　＊＊＊
  • 分库分表后，ID键如何处理？　＊＊＊
  • MySQL的复制原理及流程？如何实现主从复制？　＊＊＊
  • 了解读写分离吗？　＊＊＊

SQL语句基础知识及优化

SQL语句主要分为哪几类　＊

• 数据据定义语言DDL（Data Definition Language）：主要有CREATE，DROP，ALTER等对逻辑结构有操作的，包括表结构、视图和索引。
• 数据库查询语言DQL（Data Query Language）：主要以SELECT为主
• 数据操纵语言DML（Data Manipulation Language）：主要包括INSERT，UPDATE，DELETE
• 数据控制功能DCL（Data Control Language）：主要是权限控制能操作，包括GRANT，REVOKE，COMMIT，ROLLBACK等。

SQL约束有哪些？　＊＊

• 主键约束：主键为在表中存在一列或者多列的组合，能唯一标识表中的每一行。一个表只有一个主键，并且主键约束的列不能为空。
• 外键约束：外键约束是指用于在两个表之间建立关系，需要指定引用主表的哪一列。只有主表的主键可以被从表用作外键，被约束的从表的列可以不是主键，所以创建外键约束需要先定义主表的主键，然后定义从表的外键。
• 唯一约束：确保表中的一列数据没有相同的值，一个表可以定义多个唯一约束。
• 默认约束：在插入新数据时，如果该行没有指定数据，系统将默认值赋给该行，如果没有设置没默认值，则为NULL。
• Check约束：Check会通过逻辑表达式来判断数据的有效性，用来限制输入一列或者多列的值的范围。在列更新数据时，输入的内容必须满足Check约束的条件。

什么是子查询？　＊＊

子查询：把一个查询的结果在另一个查询中使用

子查询可以分为以下几类：

• 标量子查询：指子查询返回的是一个值，可以使用 =,>,<,>=,<=,<>等操作符对子查询标量结果进行比较，一般子查询会放在比较式的右侧。

  SELECT * FROM user WHERE age = (SELECT max(age) from user)  //查询年纪最大的人
  

• 列子查询：指子查询的结果是n行一列，一般应用于对表的某个字段进行查询返回。可以使用IN、ANY、SOME和ALL等操作符，不能直接使用

  SELECT num1 FROM table1 WHERE num1 > ANY (SELECT num2 FROM table2)
  

• 行子查询：指子查询返回的结果一行n列

  SELECT * FROM user WHERE (age,sex) = (SELECT age,sex FROM user WHERE name="zhangsan")
  

• 表子查询：指子查询是n行n列的一个数据表

  SELECT * FROM student WHERE (name,age,sex) IN (SELECT name,age,sex FROM class1) //在学生表中找到班级在1班的学生
  

了解MySQL的几种连接查询吗？　＊＊＊

MySQl的连接查询主要可以分为外连接，内连接，交叉连接

• 外连接

  外连接主要分为左外连接(LEFT JOIN)、右外连接(RIGHT JOIN)、全外连接。

  左外连接：显示左表中所有的数据及右表中符合条件的数据，右表中不符合条件的数据为null。

右外连接：显示左表中所有的数据及右表中符合条件的数据，右表中不符合条件的数据为null。

MySQL中不支持全外连接。

• 内连接：只显示符合条件的数据

• 交叉连接：使用笛卡尔积的一种连接。

  笛卡尔积，百度百科的解释：两个集合X和Y的笛卡尔积表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员 。例如：A={a,b}，B={0,1,2}，A × B = {(a,0)，(a,1)，(a,2)，(b,0)，(b,1)，(b,2)}

举例如下：有两张表分为L表和R表。

L表

A	B
a1	b1
a2	b2
a3	b3

R表

B	C
b1	c1
b2	c2
b4	c3

• 左外连接 ：select L.`*`,R.`*` from L left join R on L.b=R.b

  A	B	B	C
  a1	b1	b1	c1
  a2	b2	b2	c2
  a3	b3	null	null

• 右外连接：select L.`*`,R.`*` from L right join R on L.b=R.b

  B	C	A	B
  b1	c1	a1	b1
  b2	c2	a2	b2
  b4	c3	null	null

• 内连接：select L.`*`,R.`*` from L inner join R on L.b=R.b

  A	B	B	C
  a1	b1	b1	c1
  a2	b2	b2	c2

• 交叉连接：select L.`*`,R.`*` from L,R

  A	B	B	C
  a1	b1	b1	c1
  a1	b1	b2	c2
  a1	b1	b4	c3
  a2	b2	b1	c1
  a2	b2	b2	c2
  a2	b2	b4	c3
  a3	b3	b1	c1
  a3	b3	b2	c2
  a3	b3	b4	c3

mysql中in和exists的区别？　＊＊

in和exists一般用于子查询。

• 使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件；使用in一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据。
• in在内表查询或者外表查询过程中都会用到索引。
• exists仅在内表查询时会用到索引
• 一般来说，当子查询的结果集比较大，外表较小使用exist效率更高；当子查询寻得结果集较小，外表较大时，使用in效率更高。
• 对于not in和not exists，not exists效率比not in的效率高，与子查询的结果集无关，因为not in对于内外表都进行了全表扫描，没有使用到索引。not exists的子查询中可以用到表上的索引。

varchar和char的区别？　＊＊＊

• varchar表示变长，char表示长度固定。当所插入的字符超过他们的长度时，在严格模式下，会拒绝插入并提示错误信息，在一般模式下，会截取后插入。如char(5)，无论插入的字符长度是多少，长度都是5，插入字符长度小于5，则用空格补充。对于varchar(5)，如果插入的字符长度小于5，则存储的字符长度就是插入字符的长度，不会填充。
• 存储容量不同，对于char来说，最多能存放的字符个数为255。对于varchar，最多能存放的字符个数是65532。
• 存储速度不同，char长度固定，存储速度会比varchar快一些，但在空间上会占用额外的空间，属于一种空间换时间的策略。而varchar空间利用率会高些，但存储速度慢，属于一种时间换空间的策略。

MySQL中int(10)和char(10)和varchar(10)的区别？　＊＊＊

int(10)中的10表示的是显示数据的长度，而char(10)和varchar(10)表示的是存储数据的大小。

drop、delete和truncate的区别？　＊＊

	drop	delete	truncate
速度	快	逐行删除，慢	较快
类型	DDL	DML	DDL
回滚	不可回滚	可回滚	不可回滚
删除内容	删除整个表，数据行、索引都会被删除	表结构还在，删除表的一部分或全部数据	表结构还在，删除表的全部数据

一般来讲，删除整个表，使用drop，删除表的部分数据使用delete，保留表结构删除表的全部数据使用truncate。

UNION和UNION ALL的区别？　＊＊

union和union all的作用都是将两个结果集合并到一起。

• union会对结果去重并排序，union all直接直接返回合并后的结果，不去重也不进行排序。
• union all的性能比union性能好。

什么是临时表，什么时候会使用到临时表，什么时候删除临时表？　＊

MySQL在执行SQL语句的时候会临时创建一些存储中间结果集的表，这种表被称为临时表，临时表只对当前连接可见，在连接关闭后，临时表会被删除并释放空间。

临时表主要分为内存临时表和磁盘临时表两种。内存临时表使用的是MEMORY存储引擎，磁盘临时表使用的是MyISAM存储引擎。

一般在以下几种情况中会使用到临时表：

• FROM中的子查询
• DISTINCT查询并加上ORDER BY
• ORDER BY和GROUP BY的子句不一样时会产生临时表
• 使用UNION查询会产生临时表

大表数据查询如何进行优化？　＊＊＊

• 索引优化
• SQL语句优化
• 水平拆分
• 垂直拆分
• 建立中间表
• 使用缓存技术
• 固定长度的表访问起来更快
• 越小的列访问越快

了解慢日志查询吗？统计过慢查询吗？对慢查询如何优化？　＊＊＊

慢查询一般用于记录执行时间超过某个临界值的SQL语句的日志。

相关参数：

• slow_query_log：是否开启慢日志查询，1表示开启，0表示关闭。
• slow_query_log_file：MySQL数据库慢查询日志存储路径。
• long_query_time：慢查询阈值，当SQL语句查询时间大于阈值，会被记录在日志上。
• log_queries_not_using_indexes：未使用索引的查询会被记录到慢查询日志中。
• log_output：日志存储方式。“FILE”表示将日志存入文件。“TABLE”表示将日志存入数据库。

如何对慢查询进行优化？

• 分析语句的执行计划，查看SQL语句的索引是否命中
• 优化数据库的结构，将字段很多的表分解成多个表，或者考虑建立中间表。
• 优化LIMIT分页。

为什么要设置主键？　＊＊

主键是唯一区分表中每一行的唯一标识，如果没有主键，更新或者删除表中特定的行会很困难，因为不能唯一准确地标识某一行。

主键一般用自增ID还是UUID？　＊＊

使用自增ID的好处：

• 字段长度较uuid会小很多。
• 数据库自动编号，按顺序存放，利于检索
• 无需担心主键重复问题

使用自增ID的缺点：

• 因为是自增，在某些业务场景下，容易被其他人查到业务量。
• 发生数据迁移时，或者表合并时会非常麻烦
• 在高并发的场景下，竞争自增锁会降低数据库的吞吐能力

UUID：通用唯一标识码，UUID是基于当前时间、计数器和硬件标识等数据计算生成的。

使用UUID的优点：

• 唯一标识，不会考虑重复问题，在数据拆分、合并时也能达到全局的唯一性。
• 可以在应用层生成，提高数据库的吞吐能力。
• 无需担心业务量泄露的问题。

使用UUID的缺点：

• 因为UUID是随机生成的，所以会发生随机IO，影响插入速度，并且会造成硬盘的使用率较低。
• UUID占用空间较大，建立的索引越多，造成的影响越大。
• UUID之间比较大小较自增ID慢不少，影响查询速度。

最后说下结论，一般情况MySQL推荐使用自增ID。因为在MySQL的InnoDB存储引擎中，主键索引是一种聚簇索引，主键索引的B+树的叶子节点按照顺序存储了主键值及数据，如果主键索引是自增ID，只需要按顺序往后排列即可，如果是UUID，ID是随机生成的，在数据插入时会造成大量的数据移动，产生大量的内存碎片，造成插入性能的下降。

字段为什么要设置成not null?　＊＊

首先说一点，NULL和空值是不一样的，空值是不占用空间的，而NULL是占用空间的，所以字段设为NOT NULL后仍然可以插入空值。

字段设置成not null主要有以下几点原因：

• NULL值会影响一些函数的统计，如count，遇到NULL值，这条记录不会统计在内。

• B树不存储NULL，所以索引用不到NULL，会造成第一点中说的统计不到的问题。

• NOT IN子查询在有NULL值的情况下返回的结果都是空值。

  例如user表如下

  id	username
  0	zhangsan
  1	lisi
  2	null

  select * from `user` where username NOT IN (select username from `user` where id != 0)，这条查询语句应该查到zhangsan这条数据，但是结果显示为null。

• MySQL在进行比较的时候，NULL会参与字段的比较，因为NULL是一种比较特殊的数据类型，数据库在处理时需要进行特数处理，增加了数据库处理记录的复杂性。

如何优化查询过程中的数据访问？　＊＊＊

从减少数据访问方面考虑：

• 正确使用索引，尽量做到索引覆盖
• 优化SQL执行计划

从返回更少的数据方面考虑：

• 数据分页处理
• 只返回需要的字段

从减少服务器CPU开销方面考虑：

• 合理使用排序
• 减少比较的操作
• 复杂运算在客户端处理

从增加资源方面考虑：

• 客户端多进程并行访问
• 数据库并行处理

如何优化长难的查询语句？　＊＊

• 将一个大的查询分解为多个小的查询
• 分解关联查询，使缓存的效率更高

如何优化LIMIT分页？　＊＊

• 在LIMIT偏移量较大的时候，查询效率会变低，可以记录每次取出的最大ID，下次查询时可以利用ID进行查询

• 建立复合索引

如何优化UNION查询　＊＊

如果不需要对结果集进行去重或者排序建议使用UNION ALL，会好一些。

如何优化WHERE子句　＊＊＊

• 不要在where子句中使用!=和<>进行不等于判断，这样会导致放弃索引进行全表扫描。
• 不要在where子句中使用null或空值判断，尽量设置字段为not null。
• 尽量使用union all代替or
• 在where和order by涉及的列建立索引
• 尽量减少使用in或者not in，会进行全表扫描
• 在where子句中使用参数会导致全表扫描
• 避免在where子句中对字段及进行表达式或者函数操作会导致存储引擎放弃索引进而全表扫描

SQL语句执行的很慢原因是什么？　＊＊＊

• 如果SQL语句只是偶尔执行很慢，可能是执行的时候遇到了锁，也可能是redo log日志写满了，要将redo log中的数据同步到磁盘中去。
• 如果SQL语句一直都很慢，可能是字段上没有索引或者字段有索引但是没用上索引。

SQL语句的执行顺序?　＊

SELECT DISTINCT
	select_list
FROM
	left_table
LEFT JOIN
	right_table ON join_condition
WHERE
	where_condition
GROUP BY
	group_by_list
HAVING
	having_condition
ORDER BY
	order_by_condition

执行顺序如下：

• FROM：对SQL语句执行查询时，首先对关键字两边的表以笛卡尔积的形式执行连接，并产生一个虚表V1。虚表就是视图，数据会来自多张表的执行结果。

• ON：对FROM连接的结果进行ON过滤,并创建虚表V2

• JOIN：将ON过滤后的左表添加进来，并创建新的虚拟表V3

• WHERE：对虚拟表V3进行WHERE筛选，创建虚拟表V4

• GROUP BY：对V4中的记录进行分组操作，创建虚拟表V5

• HAVING：对V5进行过滤，创建虚拟表V6

• SELECT：将V6中的结果按照SELECT进行筛选，创建虚拟表V7

• DISTINCT：对V7表中的结果进行去重操作，创建虚拟表V8，如果使用了GROUP BY子句则无需使用DISTINCT，因为分组的时候是将列中唯一的值分成一组，并且每组只返回一行记录，所以所有的记录都h是不同的。

• ORDER BY：对V8表中的结果进行排序。

数据库优化

大表如何优化？　＊＊＊

• 限定数据的范围：避免不带任何限制数据范围条件的查询语句。
• 读写分离：主库负责写，从库负责读。
• 垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。
• 水平分表：在同一个数据库内，把一个表的数据按照一定规则拆分到多个表中。
• 对单表进行优化：对表中的字段、索引、查询SQL进行优化。
• 添加缓存

什么是垂直分表、垂直分库、水平分表、水平分库？　＊＊＊

垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。一般会将常用的字段放到一个表中，将不常用的字段放到另一个表中。

垂直分表的优势：

• 避免IO竞争减少锁表的概率。因为大的字段效率更低，第一数据量大，需要的读取时间长。第二，大字段占用的空间更大，单页内存储的行数变少，会使得IO操作增多。

• 可以更好地提升热门数据的查询效率。

垂直分库：按照业务对表进行分类，部署到不同的数据库上面，不同的数据库可以放到不同的服务器上面。

垂直分库的优势：

• 降低业务中的耦合，方便对不同的业务进行分级管理。
• 可以提升IO、数据库连接数、解决单机硬件资源的瓶颈问题。

垂直拆分（分库、分表）的缺点：

• 主键出现冗余，需要管理冗余列
• 事务的处理变得复杂
• 仍然存在单表数据量过大的问题

水平分表：在同一个数据库内，把同一个表的数据按照一定规则拆分到多个表中。

水平分表的优势：

• 解决了单表数据量过大的问题
• 避免IO竞争并减少锁表的概率

水平分库：把同一个表的数据按照一定规则拆分到不同的数据库中，不同的数据库可以放到不同的服务器上。

水平分库的优势：

• 解决了单库大数据量的瓶颈问题
• IO冲突减少，锁的竞争减少，某个数据库出现问题不影响其他数据库（可用性），提高了系统的稳定性和可用性

水平拆分（分表、分库）的缺点：

• 分片事务一致性难以解决
• 跨节点JOIN性能差，逻辑会变得复杂
• 数据扩展难度大，不易维护

在系统设计时应根据业务耦合来确定垂直分库和垂直分表的方案，在数据访问压力不是特别大时应考虑缓存、读写分离等方法，若数据量很大，或持续增长可考虑水平分库分表，水平拆分所涉及的逻辑比较复杂，常见的方案有客户端架构和恶代理架构。

分库分表后，ID键如何处理？　＊＊＊

分库分表后不能每个表的ID都是从1开始，所以需要一个全局ID，设置全局ID主要有以下几种方法：

• UUID：优点：本地生成ID，不需要远程调用；全局唯一不重复。缺点：占用空间大，不适合作为索引。

• 数据库自增ID：在分库分表表后使用数据库自增ID，需要一个专门用于生成主键的库，每次服务接收到请求，先向这个库中插入一条没有意义的数据，获取一个数据库自增的ID，利用这个ID去分库分表中写数据。优点：简单易实现。缺点：在高并发下存在瓶颈。系统结构如下图（图片来源于网络）

• Redis生成ID：优点：不依赖数据库，性能比较好。缺点：引入新的组件会使得系统复杂度增加

• Twitter的snowflake算法：是一个64位的long型的ID，其中有1bit是不用的，41bit作为毫秒数，10bit作为工作机器ID，12bit作为序列号。

  1bit：第一个bit默认为0，因为二进制中第一个bit为1的话为负数，但是ID不能为负数.

  41bit：表示的是时间戳，单位是毫秒。

  10bit：记录工作机器ID，其中5个bit表示机房ID，5个bit表示机器ID。

  12bit：用来记录同一毫秒内产生的不同ID。

• 美团的Leaf分布式ID生成系统，美团点评分布式ID生成系统

MySQL的复制原理及流程？如何实现主从复制？　＊＊＊

MySQL复制：为保证主服务器和从服务器的数据一致性，在向主服务器插入数据后，从服务器会自动将主服务器中修改的数据同步过来。

主从复制的原理：

主从复制主要有三个线程：binlog线程，I/O线程，SQL线程。

• binlog线程：负责将主服务器上的数据更改写入到二进制日志（Binary log）中。
• I/O线程：负责从主服务器上读取二进制日志（Binary log），并写入从服务器的中继日志（Relay log）中。
• SQL线程：负责读取中继日志，解析出主服务器中已经执行的数据更改并在从服务器中重放

复制过程如下（图片来源于网络）：

1. Master在每个事务更新数据完成之前，将操作记录写入到binlog中。
2. Slave从库连接Master主库，并且Master有多少个Slave就会创建多少个binlog dump线程。当Master节点的binlog发生变化时，binlog dump会通知所有的Slave，并将相应的binlog发送给Slave。
3. I/O线程接收到binlog内容后，将其写入到中继日志（Relay log）中。
4. SQL线程读取中继日志，并在从服务器中重放。

这里补充一个通俗易懂的图。

主从复制的作用：

• 高可用和故障转移
• 负载均衡
• 数据备份
• 升级测试

了解读写分离吗？　＊＊＊

读写分离主要依赖于主从复制，主从复制为读写分离服务。

读写分离的优势：

• 主服务器负责写，从服务器负责读，缓解了锁的竞争
• 从服务器可以使用MyISAM，提升查询性能及节约系统开销
• 增加冗余，提高可用性