MySQL索引优化看这篇文章就够了!

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL索引优化看这篇文章就够了!

孤独键客 2019-04-08 10:21:50 浏览317
展开阅读全文

阅读本文大概需要 5 分钟。

来源:cnblogs.com/songwenjie/p/9410009.html

本文主要讨论MySQL索引的部分知识。将会从MySQL索引基础、索引优化实战和数据库索引背后的数据结构三部分相关内容,下面一一展开(本文图片可点开放大)。

一、MySQL索引基础

首先,我们将从索引基础开始介绍一下什么是索引,分析索引的几种类型,并探讨一下如何创建索引以及索引设计的基本原则。

此部分用于测试索引创建的user表的结构如下:

169fab4a662f4694?w=703&h=286&f=png&s=477

1. 什么是索引?

“索引(在MySQL中也叫“键key”)是存储引擎快速找到记录的一种数据结构。”

——《高性能MySQL》

我们需要知道索引其实是一种数据结构,其功能是帮助我们快速匹配查找到需要的数据行,是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。

2. 索引类型

可以使用SHOW INDEX FROM table_name;查看索引详情:

169fab4a6632809f?w=1080&h=162&f=png&s=59

主键索引 PRIMARY KEY:它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。注意:一个表只能有一个主键。

169fab4a6c34a14b?w=1080&h=115&f=png&s=69

唯一索引 UNIQUE:唯一索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引:

169fab4a664b50d2?w=457&h=68&f=png&s=2317

169fab4a674b2662?w=1080&h=130&f=png&s=54

可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引:

169fab4a6ca96cf8?w=645&h=71&f=png&s=2597

169fab4a830ef8e3?w=1080&h=141&f=png&s=60

普通索引 INDEX:这是最基本的索引,它没有任何限制。可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引:

169fab4a87fd4ac2?w=556&h=75&f=png&s=2528

169fab4a8b5a5b6c?w=1080&h=146&f=png&s=62

组合索引 INDEX:即一个索引包含多个列,多用于避免回表查询。可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引:

169fab4a83a7ca47?w=615&h=66&f=png&s=2588

169fab4a8bc8427f?w=1080&h=143&f=png&s=59

全文索引 FULLTEXT:也称全文检索,是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引:

169fab4a861e48b8?w=501&h=78&f=png&s=2458

169fab4aa1461639?w=1080&h=132&f=png&s=55

索引一经创建不能修改,如果要修改索引,只能删除重建。可以使用

DROP INDEX index_name ON table_name;删除索引。

3、索引设计的原则

1)适合索引的列是出现在where子句中的列,或者连接子句中指定的列;

2)基数较小的类,索引效果较差,没有必要在此列建立索引;

3)使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间;

4)不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。

二、MySQL索引优化实战

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

此部分用于测试的user表结构:

169fab4aa152f8a9?w=562&h=206&f=png&s=422

1、索引相关的重要概念

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

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

169fab4aaca7f066?w=569&h=117&f=png&s=210

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

返回数据的比例:user表中共有5条数据:

SELECT * FROM user;

169fab4aa21684a4?w=397&h=186&f=png&s=331

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

169fab4aa6de487b?w=417&h=119&f=png&s=183

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

169fab4ab180a9b8?w=230&h=127&f=png&s=124

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

169fab4ac0780dcf?w=451&h=124&f=png&s=224

返回记录的比例数是:

169fab4ac0780dcf?w=451&h=124&f=png&s=224

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

user表的索引详情:

169fab4ac24345fa?w=1080&h=127&f=png&s=11

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

169fab4ac3df5d8d?w=995&h=129&f=png&s=441

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

169fab4ac296dd81?w=1003&h=123&f=png&s=43

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

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

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

2. 索引优化实战

有些时候虽然数据库有索引,但是并不被优化器选择使用。我们可以通过SHOW STATUS LIKE 'Handler_read%';查看索引的使用情况:

169fab4acb45f932?w=381&h=224&f=png&s=298

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

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

索引优化规则:

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

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

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

name列创建普通索引:

169fab4ad1e262c7?w=1080&h=175&f=png&s=13

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

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

169fab4ae00ad805?w=989&h=127&f=png&s=432

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

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

169fab4ae1b1e740?w=1080&h=123&f=png&s=11

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

EXPLAIN SELECT * FROM user WHERE name=1;

169fab4aeeea3142?w=980&h=126&f=png&s=437

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

169fab4af64502e8?w=982&h=130&f=png&s=419

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

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

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

169fab4ae468dd5f?w=503&h=66&f=png&s=2806

user表索引详情:

SHOW INDEX FROM user;

169fab4ae260d343?w=1080&h=139&f=jpeg&s=2

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

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

169fab4affd9796f?w=1041&h=125&f=jpeg&s=3

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

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

169fab4b0047390e?w=1047&h=122&f=jpeg&s=3

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

EXPLAIN SELECT * FROM user WHERE status=2 ;

169fab4b06548ad2?w=994&h=123&f=jpeg&s=28

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

union:

EXPLAIN SELECT*FROM user WHERE status=1

UNION ALL

SELECT*FROM user WHERE status = 2;

169fab4b06d96e59?w=1023&h=145&f=jpeg&s=3

in:

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

169fab4b14bfcb89?w=1080&h=124&f=jpeg&s=2

or:

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

169fab4b1c978ec4?w=1080&h=125&f=jpeg&s=2

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

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

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

169fab4b23724f6c?w=1056&h=125&f=jpeg&s=3

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

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

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

status列创建索引:

ALTER TABLE user ADD INDEX index_status (status);

169fab4b25c75f18?w=499&h=58&f=png&s=2907

user表索引详情:

SHOW INDEX FROM user;

169fab4b2c6ecb0a?w=1080&h=116&f=jpeg&s=2

负向条件不能命中缓存:

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

169fab4b3331840f?w=982&h=129&f=jpeg&s=30

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

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

169fab4b38f8b6a9?w=986&h=133&f=jpeg&s=29

8)范围条件查询可以命中索引。范围条件有:<、<=、>、>=、between等。

status,age列分别创建索引:

ALTER TABLE user ADD INDEX index_status (status);

169fab4b25c75f18?w=499&h=58&f=png&s=2907

ALTER TABLE user ADD INDEX index_age (age);

169fab4b4082bcd3?w=427&h=56&f=png&s=2673

user表索引详情:

SHOW INDEX FROM user;

169fab4b555acbb1?w=1080&h=130&f=jpeg&s=2

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

EXPLAIN SELECT * FROM user WHERE status>5;

169fab4b4450f7a8?w=1080&h=131&f=jpeg&s=2

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

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

169fab4b563095df?w=1080&h=110&f=jpeg&s=2

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

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

169fab4b5cd19ef9?w=1080&h=120&f=png&s=11

8)数据库执行计算不会命中索引。

EXPLAIN SELECT * FROM user WHERE age>24;

169fab4b5f67f5db?w=1080&h=123&f=png&s=10

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

169fab4b627a3aa3?w=1080&h=122&f=jpeg&s=2

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

9)利用覆盖索引进行查询,避免回表。

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

user表的索引详情:

169fab4b7a2dd2ac?w=1080&h=135&f=jpeg&s=2

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

Using Index代表从索引中查询:

EXPLAIN SELECT status FROM user where status=1;

169fab4b7c297188?w=1052&h=126&f=png&s=46

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

EXPLAIN SELECT * FROM user where status=1;

169fab4b80770fc3?w=1012&h=125&f=png&s=42

10)建立索引的列,不允许为null。

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

remark列建立索引:

ALTER TABLE user ADD INDEX index_remark (remark);

169fab4b81039ab4?w=513&h=70&f=png&s=2745

IS NULL可以命中索引:

EXPLAIN SELECT * FROM user WHERE remark IS NULL;

169fab4b97c18e40?w=1080&h=122&f=png&s=10

IS NOT NULL不能命中索引:

EXPLAIN SELECT * FROM user WHERE remark IS NOT NULL;

169fab4b992d6c22?w=1020&h=123&f=png&s=45

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

a. 更新十分频繁的字段上不宜建立索引:因为更新操作会变更B+树,重建索引。这个过程是十分消耗数据库性能的。

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

c. 业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。虽然唯一索引会影响insert速度,但是对于查询的速度提升是非常明显的。另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,在并发的情况下,依然有脏数据产生。

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

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

3. 小结

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



·END·

程序员的成长之路

路虽远,行则必至




网友评论

登录后评论
0/500
评论
孤独键客
+ 关注