「MySQL系列」索引设计原则、索引失效场景、Limit 、Order By、Group By 等常见场景优化

 一 索引使用

1.1 概述

1. 定义

索引帮助MySQL高效获取数据的数据结构(按照一定规则)。

2. 定义解释

MySQL在存储数据之外，数据库系统还维护者满足特定查找算法的 数据结构，这些数据结构以某种方式引用(指向)数据， 这样就 可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

3. 优缺点

优点 提高数据检索效率，降低数据库IO成本。通过索引对数据进行排序降低数据排序成本，降低CPU消耗。缺点 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

1.2 索引结构(InnoDB)

MySQL数据库中默认的存储引擎InnoDB的索引结构为B+树，而根据 叶子节点的内存存储不同，索引类型分为主键索引和非主键索引。

1. 主键索引(聚簇索引)

主键索引的叶子节点存储的是整行数据，其结构如下：

2. 非主键索引(二级索引或辅助索引)

而非主键索引的叶子节点内容存储时的主键的值，其结构如下： 

1.3 索引使用规则

没有建立索引，执行计划如下

建立索引

create index idx_seller_name_status_address on tb_seller(name, status, seller); 

1. 全值匹配，对索引所有列都制定具体值

explain select * from tb_seller where name='小米科技' and status='1' and 
address='北京市'; 

 

2. 最左前缀法制

违背最左法则，索引失效

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

3. 范围查询右边的列，不能使用索引

根据前面的两个字段name，status查询是走索引的，但是最后一 个条件address 没有用到索引。

4. 索引列上进行运算操作，索引失效

5. 字符串不加单引号，造成索引失效

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

6. 用or分割开的条件

示例，name字段是索引列 ， 而createtime不是索引列，中间是 or进行连接是不走索引的 ：

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G; 

 

7. 以%开头的Like模糊查询，索引失效。

解决方案

8. 如果MySQL评估使用索引比全表更慢，则不使用索引

9. is NULL，is NOT NULL有时索引失效。

10. in，not in有时索引失效

11. 尽量使用覆盖索引，避免select

尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列))，减少select。

如果查询列，超出索引列，也会降低性能。

using index ：使用覆盖索引的时候就会出现 
using where：在查找使用索引的情况下，需要回表去查询所需的数据 
using index condition：查找使用了索引，但是需要回表查询数据 
using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要 
回表查询数据 

1.4 索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考 虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

对查询频次较高，且数据量比较大的表建立索引。 
 
索引字段的选择，最佳候选列应当从where子句的条件中提取，如 
果where子句中的组合比较多，那么应当挑选最常用、过滤效果最 
好的列的组合。 
 
使用唯一索引，区分度越高，使用索引的效率越高。 
 
索引可以有效的提升查询数据的效率，但索引数量不是多多益 
善，索引越多，维护索引的代价自然也就水涨船高。对于插入、 
更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当 
高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。 
另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找 
到一个可用的索引，但无疑提高了选择的代价。 
 
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索 
引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的 
字段总长度比较短，那么在给定大小的存储块内可以存储更多的 
索引值，相应的可以有效的提升MySQL访问索引的I/O效率。 
 
利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了 
N个索引，如果查询时where子句中使用了组成该索引的前几个字 
段，那么这条查询SQL可以利用组合索引来提升查询效率。 

二 常见SQL优化

2.1 数据库准备

1. sql

CREATE TABLE `emp` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `name` varchar(100) NOT NULL, 
  `age` int(3) NOT NULL, 
  `salary` int(11) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('2','Jerry','30','3500'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('3','Luci','25','2800'); 
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('5','Tom2','21','2200'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('6','Jerry2','31','3300'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('7','Luci2','26','2700'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('8','Jay2','33','3500'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('9','Tom3','23','2400'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('10','Jerry3','32','3100'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('11','Luci3','26','2900'); 
insert into `emp` (`id`, `name`, `age`, `salary`) 
values('12','Jay3','37','4500'); 
create index idx_emp_age_salary on emp(age,salary); 

2.2 order by优化

1. filesort 排序

第一种是通过对返回数据进行排序，也就是通常说的 filesort排 序，所有不是通过索引直接返回排序结果的排序都叫 FileSort排 序。

2. using index

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

多字段排序

了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排 序，通过索引直接返回有序数据。where 条件和Order by 使用 相同的索引，并且Order By 的顺序和索引顺序相同， 并且 Order by 的字段都是升序，或者都是降序。否则肯定需要额外的 操作，这样就会出现FileSort。

3. 对上面两种进行优化

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些 情况下，条件限制不能让Filesort消失，那就需要加快Filesort 的排序操作。对于Filesort ， MySQL 现在采用的是一次扫描算 法：一次性取出满足条件的所有字段，然后在排序区 sortbuffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率 比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小 和Query语句取出的字段总大小， 来判定是否那种排序算法，如 果max_length_for_sort_data 更大，那么使用第二种优化之后 的算法;否则使用第一种。

可以适当提高 sort_buffer_size max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

2.3 group by优化

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果 在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚 合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗， 则 可以执行order by null 禁止排序。如下 ：

drop index idx_emp_age_salary on emp; 
explain select age,count(*) from emp group by age; 

 

优化后

explain select age,count(*) from emp group by age order by null; 

 

从上面的例子可以看出，第一个SQL语句需要进行"filesort"，而 第二个SQL由于order by null 不需要进行 "filesort"， 而上 文提过Filesort往往非常耗费时间。

创建索引

create index idx_emp_age_salary on emp(age,salary); 

 

2.4 limit优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一 个常见又非常头疼的问题就是 limit5000000,10 ，此时需要 MySQL排序前5000010 记录，仅仅返回5000000 - 5000010 的记 录，其他记录丢弃，查询排序的代价非常大 。

limit分页操作, 越往后, 性能越低 :

优化方案

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

 

2.5 count优化

在很多的业务系统中，都需要考虑进行分页操作，但是当我们执 行分页操作时，都需要进行一次count操作，求取总记录数，如果 数据库表的数据量大，在InnoDB引擎中，执行count操作的性能是 比较低的，需要遍历全表数据，对计数进行累加。

优化方案

①. 在大数据量的查询中，只查询数据，而不展示总记录数 ； 
②. 通过缓存redis维护一个表的计数，来记录数据库表的总记录数，在执行插入/删除时，需要动态更新； 
③. 在数据库表中定义一个大数据量的计数表，在执行插入/删除时，需要动态更新。 

2.6 大批量插入优化

1. 环境准备

CREATE TABLE `tb_user` ( 
  `id` INT(11) NOT NULL AUTO_INCREMENT, 
  `username` VARCHAR(50) NOT NULL, 
  `password` VARCHAR(50) NOT NULL, 
  `name` VARCHAR(20) NOT NULL, 
  `birthday` DATE DEFAULT NULL, 
  `sex` CHAR(1) DEFAULT NULL, 
  PRIMARY KEY (`id`), 
  UNIQUE KEY `unique_user_username` (`username`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8 ; 

当使用 load 命令导入数据的时候，适当的设置可以提高导入的效率。

对于InnoDB 类型的表，有以下几种方式可以提高导入的效率：

主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数 据按照主键的顺序排列，可以有效的提高导入数据的效率。如果 InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主 键，所以如果可以给表创建一个主键，将可以利用这点，来提高 导入数据的效率。

脚本文件介绍 : 
  sql1.log ----> 主键有序 
  sql2.log ----> 主键无序 

插入ID顺序排列数据：

load data local infile '/root/sql1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n'; 

 

 插入ID无序排列数据：

关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在 导入结束后执行 SET UNIQUE_CHECKS=1，恢复唯一性校验，可以 提高导入的效率。

手动提交事务

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。