带你完虐面试官之MySQL优化

MySQL优化

慢查询

在MySQL中，什么是慢查询？

通常由聚合查询、多表查询、表数据量过大查询、深度分页查询导致的页面加载过慢、接口压测响应时间过长（超过1秒）

如何定位慢查询？

方案一：开源工具
调试工具：Arthas
运维工具：Prometheus、Skywalking

这里就展示Skywalking的使用，如图所示，右边这个slow Endpoints会展示每个接口的调用的时间，并且从多到少进行排序。

点击追踪后，可以查看这个接口，在什么阶段，耗时了多久，都展示出来了，非常的方便好用。

方案二：MySQL自带慢日志

简单的来说就是MySQL会把执行慢的sql记录到慢查询日志中。
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
但是是默认不开启的，如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息
/var/lib/mysql/localhost-slow.log

其中，Time：SQL执行时的时间日期；Query_time：SQL执行所耗时；最下面有整句SQL语句。能够方便的定位代码中写的SQL语句。

分析慢查询的原因

可以采用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息

语法：直接在select语句之前加上关键字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件；

这里查询出来的就是sql语句查询到的数据了，而是这条SQL执行的情况。

重点字段分别是：

• possible_key 当前SQL可能会使用到的索引
• key 当前sql实际命中的索引
• key_len 索引占用的大小
• Extra 额外的优化建议
• type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all

Extra

Extra有时候会给出建议，以下是建议的含义

Using where；Using Index；含义：查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询。

Using index condition 含义：查找使用了索引，但是需要回表查询数据

关于回表查询，下文讲到索引的时候会讲解。

type

type中的类型分别的意思为：

NULL：没有使用到表，是效率最快的。
system：查询系统中的表
const：根据主键查询
eq_ref：主键索引查询或唯一索引查询
ref：索引查询
range：范围查询
index：索引树扫描
all：全盘扫描

在我们的sql中，最低也要是range，如果是index或all，就需要优化了。

索引概念及索引底层数据结构

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

数据结构对比

MySQL默认使用的索引底层数据结构是B+树。在聊B+树之前，我们先聊聊二叉树和B树

二叉树

这是正常的二叉树，遵循着左边小右边大的原理；

这是最坏情况的二叉树，是因为插入的数据一个比一个小，所以一直往左边插入，导致形成了最坏情况的二叉树，已经退化成了链表了；

红黑树

红黑树，是一种可以保持平衡的二叉树，不会出现最坏二叉树的情况。但是为什么MySQL不适用呢？

因为当数据达到一定数量的时候，查找的效率就变得很慢。

B-Tree

B-Tree，B树是一种多叉路平衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。

以一颗最大度数（max-degree）为5（5阶）的b-tree为例，那这个B树每个节点最多存储4个key

B+Tree

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用的B+Tree实现其索引结构

B树与B+树对比：

①：磁盘读写代价B+树更低；②：查询效率B+树更加稳定；③：B+树便于扫库和区间查询

总结一下：

什么是索引？

• 索引是帮助MySQL高效获取数据的数据结构
• 提高数据检索的效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引的底层数据结构是什么？有什么优点？

MySQL的InnoDB引擎采用的B+树的数据结构来存储索引

优点：

• 阶数更多，路径更短
• 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据
• B+树便于扫库和区间查询，叶子节点是一个双向链表

聚簇索引和非聚簇索引、回表查询

聚集索引（clustered Index）也称聚簇索引：将数据存储于索引放到一块，索引结构的叶子结点保存了行数据，特点：必须有，而且只有一个

二级索引（secondary Index）也称非聚簇索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，特点：可以存在多个

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询

概念：先是通过二级索引找到了索引，然后通过聚集索引找到了行数据，就称为回表查询。

例子：

以上图片中，有一条SQL语句，select * from user where name = 'Arm', 这条SQL语句，会先通过二级索引，一层一层的往下找Arm，找到后拿到了，id为10的索引，再用这个id为10通过聚集索引查找id为10的行数据，这个过程称之为回表查询。

小总结：

什么是聚簇索引，什么是非聚簇索引？

• 聚簇索引（聚集索引）：数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个
• 非聚簇索引（二级索引）：数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个

什么是回表查询？

通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表

覆盖索引

概念：

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

• 使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
• 如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用 select *

MySQL超大分页处理

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

我们一起来看看执行limit分页查询耗时对比：

因为，当在进行分页查询时，如果执行limit 9000000,10；此时需要MySQL排序前9000010记录，仅仅返回9000000-9000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路：

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

例如：

select * 
from tb_sku t,
(select id from tb_sku order by id limit 9000000,10) a
where t.id=a.id;

测试了三次的结果分别都快了4秒左右

索引创建的原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。（单表超过10万数据）
2. 针对于常作为查询条件（WHERE）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单例索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引失效的情况

1）违反最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。指的是查询从索引的最左列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引：

这边创建了一个联合索引：name、status、address

然后我们使用Explain关键字来查看我们的语句是否导致索引失效

可以看到，当我们遵循最左前缀法则时，索引并不会失效。

我们再来看看违反了最左前缀法则，索引失效的情况：

以上两种情况都是跳过了name，都导致了索引失效的情况。

还有一种情况，就是跳过了中间的索引：

可以看到，key中显示的是我们联合索引的名字，但是key_len的长度是只有name的，也就是说，在这条查询语句中，只有name = '小米科技' 走的索引，后面的地址并没有走索引。

2）范围查询右边的列，不能使用索引。

还是使用刚刚创建的联合索引tb_seller_index：

可以看到，第二条查询语句中，由于status使用了">"，导致key_len只有309，也就是说，只命中了name和status，二status右边的address失效了。

3）不要在索引列上进行运算操作，索引将失效

在这条语句中，可以看到，这是对name这个字段做了一个截取，在使用了explain关键字查看后，发现key和key_len为NULL，也就是说索引失效了。

4）字符串不加单引号，造成索引失效。

可以看到，我们第一条语句中status的条件是加了单引号的，查出来的key_len是309，是命中了两个索引的。

但是第二条语句中status的条件没有加单引号，查出来的key_len是303，只命中了一个索引，就是name索引，所以字符串的索引字段，使用时，没有添加单引号，就会造成索引失效。

原理是，在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

5）以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引就会失效。

小伙伴们可以自己去试试第五条失效的情况！