MySQL 索引与一个查询的优化，MySQL优化学习第4天

一个查询的优化

你应该会碰到这个场景，在 MySQL 中设置了自增主键，但后续删除了某些数据，导致主键不连续了，可以使用下述命令查询那些不连续的主键。

-- 写法1
select id from (select id from  表名 order by id asc) b where not exists (select 1 from 表名 where id=b.id-1)

-- 写法2
select id from (select id from 表名 order by id asc) t where (select 1 from 表名 where id=t.id-1) is null

在学习本文之前，你可以先初始化一张有百万数据的表格，并执行最普通的查询，记录时间。

select * from 表名

接下来编写深度分页语句进行查询

select * from 表名 limit 100000,10;

然后如果你的自增ID主键是连续的，可以对比下述两条语句执行顺序。

explain select * from 表名 limit 100000,10;

explain select * from 表名 where id > 100000 limit 10;

对比结构后发现，使用 where 条件，能减少将近一半的查询行数。

但是，上述第二条 SQL 语句在很多场景并不实用，因为实际应用中的数据库，很难不发生主键空缺的情况，那时上述代码就会失效。

如果使用了非主键（非索引）行进行排序在筛选，就会产生之前博客提及的 Using filesort 问题。

explain select * from 表名 order by sid limit 100000,10;

修改上述内容也非常简单，在 order by 排序的时候，仅排序主键即可。

接下来继续优化，修改为如下查询命令

explain select * from 表名 e inner join (select id from 表名 order by id limit 100000,10) ed on e.id = ed.id;

注意执行顺序是从下到上，先使用索引进行排序，然后使用主键查询最终的结果集，最后筛选出目标数据。

再次拿该表进行测试，随着深度分页的数目越来越大，然后查询的效率逐步变低。

select * from 表名 order by id desc limit 0,10;
select * from 表名 order by id desc limit 10000,10;
select * from 表名 order by id desc limit 100000,10;
select * from 表名 order by id desc limit 1000000,10;

按照上文编写的 where 进行查询，得到如下优化命令（但是会丢掉部分行）

select * from 表名 where id >=0 limit 10;
select * from 表名 where id > 10000 limit 10;
select * from 表名 where id > 100000 limit 10;
select * from 表名 where id > 1000000 limit 10;

同理你可以对比一下下述查询

select * from 表名 e inner join (select id from 表名 order by id limit 0,10) ed on e.id = ed.id;

select * from 表名 e inner join (select id from 表名 order by id limit 10000,10) ed on e.id = ed.id;
select * from 表名 e inner join (select id from 表名 order by id limit 100000,10) ed on e.id = ed.id;
select * from 表名 e inner join (select id from 表名 order by id limit 1000000,10) ed on e.id = ed.id;

当然如果 ID 的顺序很明确，并且无缺失，使用如下命令是最快的。

select * from 表名 where id  between 1000000 and 1000010 order by id desc

优化指南

由于数据库的表索引是把无序的数据变为有序，所以要合理的简历数据库索引
数据库索引的类型：

• normal：普通索引，一个索引值后面跟上多个行值；
• unique：唯一索引，一个索引后面只能有一个行值，添加主键，就是添加唯一索引；
• fulltext：全文索引；
  索引的方法，即索引的结构：
• b+tree：平衡树；
• hash：哈希表；

如何创建索引

1. 查询频繁的字段添加索引，更新频繁的字段不适合做索引；
2. 不会出现在 where 中的字段不应该创建索引，经常出现在 ORDER BY 、 GROUP BY 、 DISTINCT 中的字段适合做索引；
3. 一个查询尽量只使用一个索引；
4. 如果需要函数运算值（例如 left(‘字段名’, 3)）作为索引，建议再创建一列；
5. 实际应用的时候，多考虑复合索引，但是需要注意复合索引的顺序是从左到右；
6. 限制单表索引数量，建议单表索引不超过5个；
7. 主键建议使用自增ID值，不要使用 UUID ， MD5 ， HASH ，字符串列作为主键；