MySQL索引优化与SQL优化

概述

日常在 CURD 的过程中，都避免不了跟数据库打交道，大多数业务都离不开数据库表的设计和 SQL 的编写，那如何让你编写的SQL语句性能更优呢？先来整体看下 MySQL 逻辑架构图：

77_MySQL索引优化.png

MySQL 整体逻辑架构图可以分为 Server 和存储引擎层。

Server层

Server 层涵盖了 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），以及存储过程、触发器、视图等跨存储引擎的实现也在这一层来实现。

  • 连接器：负责跟客户端建立连接、获取权限、维持和管理连接。
  • 分析器：SQL 词法分析，SQL语法分析。
  • 优化器：索引选择，选择一个执行效率高的，生成执行计划。
  • 执行器：操作引擎，返回执行结果。
  • 查询缓存：执行 SQL 语句之前，先查缓存，缓存结果可能是以 key-value 对方式存储的，key 是查询的语句，value 是查询的结果。

存储引擎层

负责数据的存储和提取，是一种插件式的架构方式。支持 InnoDB、MyISAM、Memory 等多个存储引擎。MySQL 5.5.5 版本开始默认存储引擎是 InnoDB，也是目前常用的存储引擎。

数据准备

我们使用如下语句，创建测试的数据表：

CREATE TABLE `user_haicoder` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(24) NOT NULL DEFAULT '' COMMENT '用户姓名',
  `user_age` int(11) NOT NULL DEFAULT 0 COMMENT '用户年龄',
  `user_level` varchar(20) NOT NULL DEFAULT '' COMMENT '用户等级',
  `register_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (`id`),
  KEY `idx_userName_userAge_userLevel` (`user_name`,`user_age`,`user_level`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

我们使用如下语句插入数据：

INSERT INTO user_haicoder(user_name, user_age, user_level, register_time)VALUES('嗨客网', 30, 'A', NOW());
INSERT INTO user_haicoder(user_name, user_age, user_level, register_time)VALUES('haicoder', 31, 'B', NOW());
INSERT INTO user_haicoder(user_name, user_age, user_level, register_time)VALUES('hai', 31, 'C', NOW());

索引优化案例

全值匹配

按索引字段顺序匹配使用，我们执行如下 sql：

explain select * from user_haicoder where user_name = '嗨客网';

执行完毕后，如下图所示：

78_MySQL索引优化.png

通过 explain 分析，type 为 ref，使用索引，效率高。key_len 为 74，根据 key_len 计算规则，如果字段类型为 varchar(n)，并且是 utf-8 编码格式，则 key_len=3n+2，where 后使用了 user_name，则 key_len=3*24+2=74，证明索引用到了联合索引的第一个字段 user_name，从 ref 也可以看到一个 const。

使用联合索引两个字段时：

explain select * from user_haicoder where user_name = '嗨客网' and user_age = 30;

执行完毕后，如下图所示：

79_MySQL索引优化.png

同样 type 为 ref，使用联合索引三个字段时：

explain select * from user_haicoder where user_name = '嗨客网'and user_age = 30 and user_level = 'A';

执行完毕后，如下图所示：

80_MySQL索引优化.png

同样 type 为 ref。

最佳左前缀法则

如果建的是联合索引，要遵循最左前缀法则。要想使用索引，where 后面的条件需要从索引的最左前列开始并且不跳过索引中的列使用。我们执行如下查询语句：

explain select * from user_haicoder where user_name = '嗨客网'and user_age = 30 and user_level = 'A';

执行完毕后，如下图所示：

81_MySQL索引优化.png

按照索引字段顺序使用，三个字段都使用了索引。我们再次执行如下查询语句：

explain select * from user_haicoder where user_age = 30 and user_level = 'A';

执行完毕后，如下图所示：

82_MySQL索引优化.png

直接跳过 user_name 使用索引字段，索引无效，未使用到索引。我们再次执行如下查询语句：

explain select * from user_haicoder where user_age = 30 and user_level = 'A' and user_name = '嗨客网';

执行完毕后，如下图所示：

83_MySQL索引优化.png

where 后面查询条件顺序是 user_age、user_level、user_name 与我们建的索引顺序 user_name、user_age、user_level 不一致，为什么还是使用了索引，这是因为 MySql 底层优化器给咱们做了优化。但是，咱们最好还是按顺序使用索引。

不要在索引列上做任何操作

不要在索引列上做任何操作，比如计算、使用函数、自动或手动进行类型转换，会导致索引失效，从而使查询转向全表扫描。我们执行如下查询语句：

explain select * from user_haicoder where user_name = '嗨客网';

执行完毕后，如下图所示：

84_MySQL索引优化.png

wher 条件直接使用索引字段 user_name 用到了索引。我们再次执行如下查询语句：

explain select * from user_haicoder where right(user_name, 1) = '嗨';

执行完毕后，如下图所示：

85_MySQL索引优化.png

where 条件使用计算后的索引字段 user_name，没有使用索引，索引失效。

存储引擎不能使用范围条件右边的索引列

我们执行如下查询语句：

explain select * from user_haicoder where user_name = '嗨客网' and user_age = 30 and user_level = 'A';

执行完毕后，如下图所示：

86_MySQL索引优化.png

三个列都使用 “=” 号，顺序使用三个字段，三个字段都使用了索引。我们再次执行如下查询语句：

explain select * from user_haicoder where user_name = '嗨客网' and user_age > 30 and user_level = 'A';

执行完毕后，如下图所示：

87_MySQL索引优化.png

将 user_age修 改为 “>” 之后，Extra 为 Using index condition，表明索引没有被完全使用，并且 key_len 由 140 降为 78，说明最后一个字段 user_level 没有使用索引。即范围之后索引全无效。

但是如果我们把 “>” 变为 “>=” 呢？我们再次执行如下查询语句：

explain select * from user_haicoder where user_name = '嗨客网' and user_age >= 30 and user_level = 'A';

执行完毕后，如下图所示：

88_MySQL索引优化.png

加上等号后，key_len 变为了 140，但是 Using index condition 确又表明索引没有被完全使用，只能说明在满足 user_age 为 “=” 号条件时全部索引使用，否则，范围之后的索引失效。

尽量使用覆盖索引

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句。我们执行如下查询语句：

explain select * from user_haicoder where user_name = '嗨客网' and user_age = 30 and user_level = 'A';

执行完毕后，如下图所示：

89_MySQL索引优化.png

Extra 显示 “Using index condition”，表示查询的列未被索引列覆盖，并且 where 筛选条件是索引的前导列，说明用到了索引，但是部分字段未被索引列覆盖，必须通过 “回表” 来实现，所以不是纯粹地用到了索引，也不是完全没用到索引。我们再次执行如下查询语句：

explain select user_name, user_age from user_haicoder where user_name = '嗨客网' and user_age  = 30 and user_level = 'A';

执行完毕后，如下图所示：

90_MySQL索引优化.png

将 * 换成索引列，查询时使用了索引，用索引列覆盖查询的 *， 叫做覆盖索引。

不等于无法使用索引

mysql 在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描。我们执行如下查询语句：

explain select user_name, user_age from user_haicoder where user_name = '嗨客网';

执行完毕后，如下图所示：

91_MySQL索引优化.png

使用了索引，现在我们再次执行如下查询语句：

explain select user_name, user_age from user_haicoder where user_name != '嗨客网';

执行完毕后，如下图所示：

92_MySQL索引优化.png

Extra 显示 Using whre Using index，表示查询的列被索引列覆盖，但是 where 后面条件未使用索引，说明无法直接通过索引查找查询到符合条件的数据。

is null，is not null也无法使用索引

我们执行如下查询语句：

explain select * from user_haicoder where user_name = '嗨客网';

执行完毕后，如下图所示：

93_MySQL索引优化.png

使用了索引。我们执行如下查询语句：

explain select user_name, user_age from user_haicoder where user_name is not null;

执行完毕后，如下图所示：

94_MySQL索引优化.png

未使用索引。

like以通配符开头

like 以通配符开头（like ‘%aaa’）mysql 索引失效会变成全表扫描操作。现在我们执行如下查询语句：

explain select * from user_haicoder where user_name like '%A';

执行完毕后，如下图所示：

95_MySQL索引优化.png

% 开头，未使用索引。我们再次执行如下查询语句：

explain select * from user_haicoder where user_name like 'A%';

执行完毕后，如下图所示：

96_MySQL索引优化.png

% 结尾，使用了索引。

union、in、or都能够命中索引，建议使用in

union 能够命中索引，并且 MySQL 耗费的 CPU 最少：

select * from user_haicoder where user_level='A'
union all
select * from user_haicoder where user_level='B';

in 能够命中索引，查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in：

select * from user_haicoder where user_level in ('A', 'B');

or 新版的 MySQL 能够命中索引，查询优化耗费的 CPU 比 in 多，不建议频繁用 or：

select * from user_haicoder where user_level='A' or user_level='B';

order by和group by

如果有 order by、group by 的场景，请注意利用索引的有序性：

  1. order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。
  2. 如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a > 10 ORDER BY b;，索引 (a,b) 无法排序。

join查询

进行 join 联表查询的字段需要建立索引，join 最好不要超过三个表。需要 join 的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。

left join 是由左边决定的，左边的数据一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用 right join。

单表索引建议控制在5个以内

索引不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引。一个表的索引数较好不要超过 6 个。

SQL语句优化

  1. 用具体的字段列表代替 “select *”，不要返回用不到的任何字段。

  2. 如果明确知道只有一条结果返回，limit 1 能够提高效率。

  3. 利用延迟关联或者子查询优化超多分页场景，MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。示例如下，先快速定位需要获取的 id 段，然后再关联：

    select a.* from 表1 a,(select id from 表1 where 条件 limit 100000,20 ) b where a.id=b.id;

  4. 对于多张大数据量的表 JOIN，要先分页再 JOIN，否则逻辑读会很高，性能很差。

  5. 避免在 where 子句中使用 or 来连接查询条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

  6. 对于连续的数值，能用 between 就不要用 in 了，尽量使用 exists 代替 in。

  7. 优化 Group by，使用 where 子句替换 Having 子句。

    避免使用 having 子句，having 只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过 where 子句提前过滤查询的数目，就可以减少这方面的开销。

    on、where、having 这三个都可以加条件的子句，on 是最先执行，where 次之，having 最后。提高 GROUP BY 语句的效率, 可以通过将不需要的记录在 GROUP BY 之前过滤掉。

    # 低效
SELECT JOB, AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' 
# 高效
SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP by JOB

  8. 使用 union all 替换 union

    当 SQL 语句需要 union 两个查询结果集合时，这两个结果集合会以 union all 的方式被合并，然后再输出最终结果前进行排序。如果用 union all 替代 union，这样排序就不是不要了，效率就会因此得到提高。需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。

  9. 尽量使用数字型字段

    若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能。引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

  10. 写出统一的SQL语句

    对于以下两句 SQL 语句，很多人都认为是相同的。不过数据库查询优化器则认为是不同的，虽然只是大小写不同，但必须进行两次解析，生成 2 个执行计划。所以应该保证相同的查询语句在任何地方都一致，多一个空格都不行。

    select * from dual
select * From dual

  11. where 子句中索引列使用参数，也会导致索引失效

    因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

    select id from t where num=@num                                   -- 不能使用索引
select id from t with(index(索引名)) where num=@num            --可以改为强制查询使用索引：

  12. 使用复合索引须遵守最左前缀原则

    复合索引必须使用到最左边字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

  13. 当需要全表删除且无需回滚时，使用 Truncate 替代 delete。

  14. 使用表的别名

    当在 SQL 语句中连接多个表时, 使用表的别名并把别名前缀用于每个 Column 上，这样可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。

  15. 避免使用耗费资源的操作：

    带有 DISTINCT, UNION, MINUS, INTERSECT, ORDER BY 的 SQL 语句，会启动 SQL 引擎执行耗费资源的排序功能，DISTINCT 需要一次排序操作，而其他的至少需要执行两次排序。通常带有 UNION, MINUS, INTERSECT 的 SQL 语句都可以用其他方式重写，如果你的数据库的 SORT_AREA_SIZE 调配得好, 使用 UNION , MINUS, INTERSECT 也是可以考虑的, 毕竟它们的可读性很强。

  16. 尽量避免向客户端返回大数据量。

  17. Update 语句，如果只更改 1、2 个字段，不要 Update 全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

  18. 应尽可能的避免更新聚簇索引数据列，因为聚簇索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。

  19. 尽量使用表变量来代替临时表。

  20. 考虑使用 “临时表” 暂存中间结果。临时表并不是不可使用，适当地使用它们可以使某些查询更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。将临时结果暂存在临时表，后面的查询就在临时表中查询了，这可以避免程序中多次扫描主表，也大大减少了程序执行中 “共享锁” 阻塞 “更新锁”，减少了阻塞，提高了并发性能。但是，对于一次性事件，较好使用导出表。

  21. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先 create table，然后 insert。

  22. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

  23. 避免频繁创建和删除临时表，以减少系统表资源的消耗。

  24. 尽量避免使用游标，因为游标的效率较差。与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括 “合计” 的例程通常要比使用游标执行的速度快。

  25. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。

  26. 尽量避免大事务操作，提高系统并发能力。

  27. 在运行代码中，尽量使用 PreparedStatement 来查询，不要用 Statement。
嗨客网教程
嗨客网首页 首页 嗨客网知识体系 知识体系 嗨客网实战 实战 嗨客网随笔 随笔 嗨客网搜索 搜索