18条MySQL优化技巧

刚参加工作的我们 , 都以为使用 MySQL 是非常的简单的 , 无非都是照着 【select from where group by order by】 这个格式套来套去;从来不会关注 SQL 的耗费时长 , 更不会关注查询的性能 。
但是当用户量上来了 , 表数据不断暴增 , 导致我们以前写的 SQL 的查询时间越来越长 , 最后还被 DBA 和领导疯狂吐槽一波 。那么 , 此时我们是不是应该学习一下如何去优化我们的烂 SQL 呢?
下面 , 我将从多方面去深入讲解如何优化 SQL。
一、索引优化索引的数据结构是 B+Tree , 而 B+Tree 的查询性能是比较高的 , 所以建立索引能提升 SQL 的查询性能 。
1、建立普通索引
对经常出现在 where 关键字后面的表字段建立对应的索引 。
2、建立复合索引
如果 where 关键字后面常出现的有几个字段 , 可以建立对应的 复合索引 。要注意可以优化的一点是:将单独出现最多的字段放在前面 。
例如现在我们有两个字段 a 和 b 经常会同时出现在 where 关键字后面:
select * from t where a = 1 and b = 2;* Q1 *也有很多 SQL 会单独使用字段 a 作为查询条件:
select * from t where a = 2;* Q2 *此时 , 我们可以建立复合索引 index(a,b) 。因为不但 Q1 可以利用复合索引 , Q2 也可以利用复合索引 。
3、最左前缀匹配原则
如果我们使用的是复合索引 , 应该尽量遵循 最左前缀匹配原则 。MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配 。
假如此时我们有一条 SQL :
select * from t where a = 1 and b = 2 and c > 3 and d = 4;那么我们应该建立的复合索引是:index(a,b,d,c) 而不是 index(a,b,c,d) 。因为字段 c 是范围查询 , 当 MySQL 遇到范围查询就停止索引的匹配了 。
大家也注意到了 , 其实 a,b,d 在 SQL 的位置是可以任意调整的 , 优化器会找到对应的复合索引 。

还要注意一点的是:最左前缀匹配原则不但是复合索引的最左 N 个字段;也可以是单列(字符串类型)索引的最左 M 个字符 。
  • 例如我们常说的 like 关键字 , 尽量不要使用全模糊查询 , 因为这样用不到索引;
  • 所以建议是使用右模糊查询:select * from t where name like '李%'(查询所有姓李的同学的信息) 。
4、索引下推
很多时候 , 我们还可以复合索引的 索引下推 来优化 SQL。
例如此时我们有一个复合索引:index(name,age)  , 然后有一条 SQL 如下:
select * from user where name like '苏%' and age = 10 and sex = 'm';根据复合索引的最左前缀匹配原则 , MySQL 匹配到复合索引 index(name,age) 的 name 时 , 就停止匹配了;然后接下来的流程就是根据主键回表 , 判断 age 和 sex 的条件是否同时满足 , 满足则返回给客户端 。
但是由于有索引下推的优化 , 匹配到 name 时 , 不会立刻回表;而是先判断复合索引 index(name,age) 中的 age 是否符合条件;符合条件才进行回表接着判断 sex 是否满足 , 否则会被过滤掉 。
那么借着 MySQL 5.6 引入的索引下推优化  , 可以做到减少回表的次数 。
5、覆盖索引
很多时候 , 我们还可以 覆盖索引 来优化 SQL。
情况一:SQL 只查询主键作为返回值 。
主键索引(聚簇索引)的叶子节点是整行数据 , 而普通索引(二级索引)的叶子节点是主键的值 。
所以当我们的 SQL 只查询主键值 , 可以直接获取对应叶子节点的内容 , 而避免回表 。
情况二:SQL 的查询字段就在索引里 。
复合索引:假如此时我们有一个复合索引 index(name,age)  , 有一条 SQL 如下:
select name,age from t where name like '苏%';由于是字段 name 是右模糊查询所以可以走复合索引 , 然后匹配到 name 时 , 不需要回表 , 因为 SQL 只是查询字段 name 和 age , 所以直接返回索引值就 ok 了 。
6、普通索引
尽量 使用普通索引 而不是唯一索引 。
首先 , 普通索引和唯一索引的查询性能其实不会相差很多;当然了 , 前提是要查询的记录都在同一个数据页中 , 否则普通索引的性能会慢很多 。


推荐阅读