刚参加工作的我们,都以为使用 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 个字符。
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、普通索引
尽量 使用普通索引 而不是唯一索引。
首先,普通索引和唯一索引的查询性能其实不会相差很多;当然了,前提是要查询的记录都在同一个数据页中,否则普通索引的性能会慢很多。
但是,普通索引的更新操作性能比唯一索引更好;其实很简单,因为普通索引能利用 change buffer 来做更新操作;而唯一索引因为要判断更新的值是否是唯一的,所以每次都需要将磁盘中的数据读取到 buffer pool 中。
7、前缀索引
我们要学会巧妙的使用 前缀索引,避免索引值过大。
例如有一个字段是 addr varchar(255),但是如果一整个建立索引 [ index(addr) ],会很浪费磁盘空间,所以会选择建立前缀索引 [ index(addr(64)) ]。
建立前缀索引,一定要关注字段的区分度。例如像身份证号码这种字段的区分度很低,只要出生地一样,前面好多个字符都是一样的;这样的话,最不理想时,可能会扫描全表。
前缀索引避免不了回表,即无法使用覆盖索引这个优化点,因为索引值只是字段的前 n 个字符,需要回表才能判断查询值是否和字段值是一致的。
怎么解决?
倒序存储:像身份证这种,后面的几位区分度就非常的高了;我们可以这么查询:
select field_list from t where id_card = reverse('input_id_card_string');增加 hash 字段并为 hash 字段添加索引。
8、干净的索引列
索引列不能参与计算,要保持索引列“干净”。
假设我们给表 student 的字段 birthday 建立了普通索引。
下面的 SQL 语句不能利用到索引来提升执行效率:
select * from student where DATE_FORMAT(birthday,'%Y-%m-%d') = '2020-02-02';
我们应该改成下面这样:
select * from student where birthday = STR_TO_DATE('2020-02-02', '%Y-%m-%d');
9、扩展索引
我们应该尽量 扩展索引,而不是新增索引,一个表最好不要超过 5 个索引;一个表的索引越多,会导致更新操作更加耗费性能。
1、Order By 优化
order by 后面的字段尽量是带索引的,这样能避免使用 sort_buffer 进行排序。
CREATE INDEX index_birthday ON student(birthday);
select 后面不要带上不必要的字段,因为如果单行长度太长导致查询数据太多,MySQL 会利用 rowid 排序来代替全字段排序,这样会导致多了回表的操作。
2、Join 优化
3、Group By 优化
4、OR 优化
在 Innodb 引擎下 or 关键字无法使用组合索引。
假设现在关于订单表有一条 SQL :
select id,product_name from orders where mobile = '12345678900' or user_id = 6;
一般我们为了提升上面 SQL 的查询效率,会想着为字段 mobile 和 user_id 建立一个复合索引 index(mobile,user_id);
可是我们使用 explain 可以发现执行计划里面并没有提示到使用复合索引,所以 or 关键字无法命中 mobile + user_id 的组合索引。
那么我们可以分别为两个字段建立普通索引,然后采用 union 关键字,如下所示:
(select id,product_name from orders where mobile = '12345678900')
union
(select id,product_name from orders where user_id = 6);
此时 mobile 和 user_id 字段都有索引,查询才最高效。
5、IN 优化
in 关键字适合主表大子表小,exist 关键字适合主表小子表大。由于查询优化器的不断升级,很多场景这两者性能差不多一样了,可以尝试改为 join 查询。
假设我们现在有一条 SQL ,要查询 VIP 用户的所有订单数据:
select id from orders where user_id in (select id from user where level = 'VIP');
我们可以发现不会有任何关于索引的优化,所以我们可以采用 join查询,如下所示:
select o.id from orders o join user u on o.user_id = u.id and u.level = 'VIP';
此时被驱动表应该是 user,那么可以利用到 user 表的主键索引,即可以使用 BKA 算法来提升 join 查询的性能。
6、Like 优化
like 用于模糊查询,但是如果是全模糊查询,将不能命中对应字段的索引。
假设现在关于学生表有一条 SQL:
SELECT name,age,birthday FROM student WHERE name like '%苏%';
使用 explain 可以发现执行计划提示查询未命中索引。
因为本来需求就是查询姓张的所有同学信息,所以没必要使用全模糊查询,使用右模糊查询即可。
换成下面的写法:
SELECT name,age,birthday FROM student WHERE name like '苏%';
但是产品经理一定要前后模糊匹配呢?全文索引 FULLTEXT 可以尝试一下,但是 MySQL 的全文索引不支持中文查询的。
所以说 Elasticsearch 才是终极武器!
1、数据类型:应该选择更简单或者占用空间更小的类型。
2、避免空值:
3、超长字符串: