MySQL——索引优化实战

上篇文章中介绍了索引的基本内容,这篇文章我们继续介绍索引优化实战。在介绍索引优化实战之前,首先要介绍两个与索引相关的重要概念,这两个概念对于索引优化至关重要。

本篇文章用于测试的user表结构:

索引相关的重要概念

基数

单个列唯一键(distict_keys)的数量叫做基数。

SELECT COUNT(DISTINCT name),COUNT(DISTINCT gender)FROM user;

user表的总行数是5,gender 列的基数是 2,说明 gender 列里面有大量重复值,name 列的基数等于总行数,说明 name列没有重复值,相当于主键。

返回数据的比例:

user表中共有5条数据:

SELECT*FROM user;

查询满足性别为0(男)的记录数:

那么返回记录的比例数是:

同理,查询name为'swj'的记录数:

返回记录的比例数是:

现在问题来了,假设name、gender列都有索引,那么 SELECT*FROM user WHERE gender=0; SELECT*FROM user WHERE name='swj';都能命中索引吗?

user表的索引详情:

SELECT*FROM user WHERE gender=0;没有命中索引,注意filtered的值就是上面我们计算的返回记录的比例数。

SELECT*FROM user WHERE name='swj';命中了索引index_name,因为走索引直接就能找到要查询的记录,所以filtered的值为100

结论:

返回表中 30% 内的数据会走索引,返回超过 30% 数据就使用全表扫描。当然这个结论太绝对了,也并不是绝对的30%,只是一个大概的范围。

回表

当对一个列创建索引之后,索引会包含该列的键值及键值对应行所在的 rowid。通过索引中记录的 rowid 访问表中的数据就叫回表。回表次数太多会严重影响 SQL 性能,如果回表次数太多,就不应该走索引扫描,应该直接走全表扫描。

EXPLAIN命令结果中的 UsingIndex意味着不会回表,通过索引就可以获得主要的数据。 UsingWhere则意味着需要回表取数据。

索引优化实战

有些时候虽然数据库有索引,但是并不被优化器选择使用。

我们可以通过 SHOW STATUS LIKE'Handler_read%';查看索引的使用情况:

Handlerreadkey:如果索引正在工作,Handlerreadkey的值将很高。

Handlerreadrnd_next:数据文件中读取下一行的请求数,如果正在进行大量的表扫描,值将较高,则说明索引利用不理想。

索引优化规则

1.如果MySQL估计使用索引比全表扫描还慢,则不会使用索引

返回数据的比例是重要的指标,比例越低越容易命中索引。记住这个范围值——30%,后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。

2.前导模糊查询查询不能命中索引

name列创建普通索引:

前导模糊查询查询不能命中索引:

EXPLAIN SELECT*FROM user WHERE name LIKE'%s%';

非前导模糊查询则可以使用索引,可优化为使用非前导模糊查询:

EXPLAIN SELECT*FROM user WHERE name LIKE's%';

3.数据类型出现隐式转换的时候不会命中索引,特别是当列类型是字符串,一定要将字符常量值用引号引起来

EXPLAIN SELECT*FROM user WHERE name=1;

EXPLAIN SELECT*FROM user WHERE name='1';

4.复合索引的情况下,查询条件不包含索引列最左边部分(不满足最左原则),不会命中符合索引

name,age,status列创建复合索引:

ALTER TABLE user ADD INDEX index_name(name,age,status);

user表索引详情:

SHOW INDEX FROM user;

根据最左原则,可以命中复合索引index_name:

EXPLAIN SELECT*FROM user WHERE name='swj'AND status=1;

注意,最左原则并不是说是查询条件的顺序:

EXPLAIN SELECT*FROM user WHERE status=1AND name='swj';

而是查询条件中是否包含索引最左列字段:

EXPLAIN SELECT*FROM user WHERE status=2;

5.union、in、or 都能够命中索引,建议使用 in。

union:

EXPLAIN SELECT*FROM user WHERE status=1

UNION ALL

SELECT*FROM user WHERE status=2;

in:

EXPLAIN SELECT*FROM user WHERE status IN(1,2);

or:

EXPLAIN SELECT*FROM user WHERE status=1OR status=2;

查询的CPU消耗:or > in >union

6.用or分割开的条件,如果or前的条件中列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到

EXPLAIN SELECT*FROM payment WHERE customer_id=203OR amount=3.96;

因为or后面的条件列中没有索引,那么后面的查询肯定要走全表扫描,在存在全表扫描的情况下,就没有必要多一次索引扫描增加IO访问。

7.负向条件查询不能使用索引,可以优化为 in 查询。

负向条件有:!=、<>、not in、not exists、not like 等。

status列创建索引:

ALTER TABLE user ADD INDEX index_status(status);

user表索引详情:

SHOW INDEX FROM user;

负向条件不能命中缓存:

EXPLAIN SELECT*FROM user WHERE status!=1AND status!=2;

可以优化为 in 查询,但是前提是区分度要高,返回数据的比例在30%以内:

EXPLAIN SELECT*FROM user WHERE status IN(0,3,4);

8.范围条件查询可以命中索引

范围条件有:<、<=、>、>=、between等

status,age列分别创建索引:

ALTER TABLE user ADD INDEX index_status(status);

ALTER TABLE user ADD INDEX index_age(age);

user表索引详情:

SHOW INDEX FROM user;

范围条件查询可以命中索引:

EXPLAIN SELECT*FROM user WHERE status>5;

范围列可以用到索引(联合索引必须是最左前缀),但是范围列后面的列无法用到索引,索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引:

EXPLAIN SELECT*FROM user WHERE status>5AND age<24;

如果是范围查询和等值查询同时存在,优先匹配等值查询列的索引:

EXPLAIN SELECT*FROM user WHERE status>5AND age=24;

9.数据库执行计算不会命中索引

EXPLAIN SELECT*FROM user WHERE age>24;

EXPLAIN SELECT*FROM user WHERE age+1>24;

计算逻辑应该尽量放到业务层处理,节省数据库的 CPU的同时最大限度的命中索引。

10.利用覆盖索引进行查询,避免回表

被查询的列,数据能从索引中取得,而不用通过行定位符 row-locator 再到 row 上获取,即“被查询列要被所建的索引覆盖”,这能够加速查询速度。

user表的索引详情:

因为status字段是索引列,所以直接从索引中就可以获取值,不必回表查询:

UsingIndex代表从索引中查询

EXPLAIN SELECT status FROM userwherestatus=1;

当查询其他列时,就需要回表查询,这也是为什么要避免 SELECT*的原因之一:

EXPLAIN SELECT*FROM userwherestatus=1;

11.建立索引的列,不允许为 null

单列索引不存 null 值,复合索引不存全为 null 的值,如果列允许为 null,可能会得到“不符合预期”的结果集,所以,请使用 not null 约束以及默认值。

remark列建立索引:

ALTER TABLE user ADD INDEX index_remark(remark);

IS NULL可以命中索引:

EXPLAIN SELECT*FROM user WHERE remark IS NULL;

IS NOT NULL不能命中索引:

EXPLAIN SELECT*FROM user WHERE remark IS NOT NULL;

虽然IS NULL可以命中索引,但是NULL本身就不是一种好的数据库设计,应该使用NOT NULL 约束以及默认值

12.更新十分频繁的字段上不宜建立索引

因为更新操作会变更B+树,重建索引。这个过程是十分消耗数据库性能的。

13.区分度不大的字段上不宜建立索引

类似于性别这种区分度不大的字段,建立索引的意义不大。因为不能有效过滤数据,性能和全表扫描相当。另外返回数据的比例在30%以外的情况下,优化器不会选择使用索引。

14.业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引

虽然唯一索引会影响insert速度,但是对于查询的速度提升是非常明显的。另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,在并发的情况下,依然有脏数据产生。

15.多表关联时,要保证关联字段上一定有索引

16.创建索引时避免以下错误观念

  • 索引越多越好,认为一个查询就需要建一个索引。
  • 宁缺勿滥,认为索引会消耗空间、严重拖慢更新和新增速度。
  • 抵制唯一索引,认为业务的唯一性一律需要在应用层通过“先查后插”方式解决。
  • 过早优化,在不了解系统的情况下就开始优化。

总结

对于自己编写的SQL查询语句,要尽量使用EXPLAIN命令分析一下,做一个对SQL性能有追求的程序员。衡量一个程序员是否靠谱,SQL能力是一个重要的指标。作为后端程序员,深以为然。

原文发布于微信公众号 - 撸码那些事(lumanxs)

原文发表时间:2018-08-04

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java呓语

第13.1.1章 语法解释ALTER DATABASE

ALTER DATABASE 允许修改数据库的特征,所有关于数据库的特征都存储在db.opt文件中。你必须先具备该数据库的ALTER特权,才能完成对数据库的AL...

8510
来自专栏pangguoming

oracle的行级触发器使用

行级触发器: 当触发器被触发时,要使用被插入、更新或删除的记录中的列值,有时要使用操作前、后列的值. :NEW 修饰符访问操作完成后列的值 :OLD 修饰符访问...

33950
来自专栏java工会

十个mysql语句的优化方法

20960
来自专栏PhpZendo

使用 MySQL Scheduler 和 Event 周期性创建数据表

使用 MySQL Scheduler 和 Event 周期性创建数据表,下面提供的是按月建表计划任务及事件通过 ON SCHEDULE EVERY 1 MINU...

20820
来自专栏PHP在线

MyISAM InnoDB 区别(回顾)

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。 在数据库表中,对字段建立索引可以大大提高查...

34950
来自专栏MYSQL轻松学

MYSQL常用SQL汇总

1、查看当前应用连接,连接数突增排查 select user,SUBSTRING_INDEX(host,':',1) as ip , count(*) as c...

39660
来自专栏杨建荣的学习笔记

MySQL修改数据类型的问题总结(r10笔记第74天)

昨天快下班的时候,突然开发的同事找我说有个紧急需求,负责这个业务的DBA同事回家了,想让我帮忙看看,运行个SQL语句,几秒钟就好。我一听,就本着人道主义的精神留...

353100
来自专栏散尽浮华

mysql操作命令梳理(2)-alter(update、insert)

在mysql运维操作中会经常使用到alter这个修改表的命令,alter tables允许修改一个现有表的结构,比如增加或删除列、创造或消去索引、改变现有列的类...

22160
来自专栏数据和云

运维技巧 - 活用临时表隔离冷热数据

编辑手记:Oracle给了我们很多工具,在日常数据库管理中活用这些工具方可发挥最大效能。 作者简介: 张洪涛 富士康 DBA 在数据库监控过程中发现考勤数据...

41250
来自专栏奇梦博客

Xiuno BBS 4.0 表结构 XiunoBBS

11010

扫码关注云+社区

领取腾讯云代金券