#夏日挑战赛# MySQL 索引与一个查询的优化,MySQL优化学习第4天 原创

发布于 2022-7-14 09:21
浏览
3收藏

「本文正在参加星光计划3.0–夏日挑战赛」

一个查询的优化

你应该会碰到这个场景,在 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 表名

<kbd> #夏日挑战赛# MySQL 索引与一个查询的优化,MySQL优化学习第4天-开源基础软件社区</kbd>

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

select * from 表名 limit 100000,10;

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

explain select * from 表名 limit 100000,10;

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

<kbd> #夏日挑战赛# MySQL 索引与一个查询的优化,MySQL优化学习第4天-开源基础软件社区

</kbd>

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

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

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

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

<kbd> #夏日挑战赛# MySQL 索引与一个查询的优化,MySQL优化学习第4天-开源基础软件社区</kbd>

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

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

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

<kbd> #夏日挑战赛# MySQL 索引与一个查询的优化,MySQL优化学习第4天-开源基础软件社区</kbd>

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

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

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;

<kbd> #夏日挑战赛# MySQL 索引与一个查询的优化,MySQL优化学习第4天-开源基础软件社区</kbd>

按照上文编写的 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 BYGROUP BYDISTINCT 中的字段适合做索引;
  3. 一个查询尽量只使用一个索引;
  4. 如果需要函数运算值(例如 left(‘字段名’, 3))作为索引,建议再创建一列;
  5. 实际应用的时候,多考虑复合索引,但是需要注意复合索引的顺序是从左到右;
  6. 限制单表索引数量,建议单表索引不超过5个;
  7. 主键建议使用自增ID值,不要使用 UUIDMD5HASH ,字符串列作为主键;

记录时间

今天是持续写作的第 <font color=red>285</font> / 365 天。
可以<font color=#04a9f4>关注</font>我,<font color=#04a9f4>点赞</font>我、<font color=#04a9f4>评论</font>我、<font color=#04a9f4>收藏</font>我啦。

©著作权归作者所有,如需转载,请注明出处,否则将追究法律责任
6
收藏 3
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐