一篇文章搞定:MySQL 索引优化

た入场券 2022-02-14 23:53 266阅读 0赞

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

**一、MySQL索引基础**

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

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

![1240][]

**1. 什么是索引？**

> “索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据结构。”
> 
> ——《高性能MySQL》

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

**2. 索引类型**

可以使用SHOW INDEX FROM table\_name;查看索引详情：

![1240 1][]

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

![1240 2][]

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

![1240 3][]

![1240 4][]

可以通过ALTER TABLE table\_name ADD UNIQUE (column1,column2);创建唯一组合索引：

![1240 5][]

![1240 6][]

普通索引 INDEX：这是最基本的索引，它没有任何限制。可以通过ALTER TABLE table\_name ADD INDEX index\_name (column);创建普通索引：

![1240 7][]

![1240 8][]

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

![1240 9][]

![1240 10][]

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

![1240 11][]

![1240 12][]

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

DROP INDEX index\_name ON table\_name;删除索引。

**3、索引设计的原则**

1）适合索引的列是出现在where子句中的列，或者连接子句中指定的列；

2）基数较小的类，索引效果较差，没有必要在此列建立索引；

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

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

**二、MySQL索引优化实战**

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

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

![1240 13][]

**1、索引相关的重要概念**

基数：单个列唯一键（distict\_keys）的数量叫做基数。

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

![1240 14][]

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

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

SELECT \* FROM user;

![1240 15][]

查询满足性别为0（男）的记录数：

![1240 16][]

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

![1240 17][]

同理，查询name为’swj’的记录数：

![1240 18][]

返回记录的比例数是：

![1240 19][]

现在问题来了，假设name、gender列都有索引，那么SELECT \* FROM user WHERE gender = 0; SELECT \* FROM user WHERE name = ‘swj’;都能命中索引吗？

user表的索引详情：

![1240 20][]

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

![1240 21][]

SELECT \* FROM user WHERE name = ‘swj’;命中了索引index\_name，因为走索引直接就能找到要查询的记录，所以filtered的值为100。

![1240 22][]

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

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

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

**2. 索引优化实战**

有些时候虽然数据库有索引，但是并不被优化器选择使用。我们可以通过SHOW STATUS LIKE ‘Handler\_read%’;查看索引的使用情况：

![1240 23][]

**Handler\_read\_key**：如果索引正在工作，Handler\_read\_key的值将很高。

**Handler\_read\_rnd\_next**：数据文件中读取下一行的请求数，如果正在进行大量的表扫描，值将较高，则说明索引利用不理想。

索引优化规则：

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

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

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

name列创建普通索引：

![1240 24][]

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

EXPLAIN SELECT \* FROM user WHERE name LIKE ‘%s%’;

![1240 25][]

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

EXPLAIN SELECT \* FROM user WHERE name LIKE ‘s%’;

![1240 26][]

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

EXPLAIN SELECT \* FROM user WHERE name=1;

![1240 27][]

EXPLAIN SELECT \* FROM user WHERE name=‘1’;

![1240 28][]

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

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

ALTER TABLE user ADD INDEX index\_name (name,age,status);

![1240 29][]

user表索引详情：

SHOW INDEX FROM user;

![1240 30][]

根据最左原则，可以命中复合索引index\_name：

EXPLAIN SELECT \* FROM user WHERE name=‘swj’ AND status=1;

![1240 31][]

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

EXPLAIN SELECT \* FROM user WHERE status=1 AND name=‘swj’;

![1240 32][]

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

EXPLAIN SELECT \* FROM user WHERE status=2 ;

![1240 33][]

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

union:

EXPLAIN SELECT\*FROM user WHERE status=1

UNION ALL

SELECT\*FROM user WHERE status = 2;

![1240 34][]

in:

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

![1240 35][]

or:

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

![1240 36][]

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

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

EXPLAIN SELECT \* FROM payment WHERE customer\_id = 203 OR amount = 3.96;

![1240 37][]

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

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

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

status列创建索引：

ALTER TABLE user ADD INDEX index\_status (status);

![1240 38][]

user表索引详情：

SHOW INDEX FROM user;

![1240 39][]

负向条件不能命中缓存：

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

![1240 40][]

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

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

![1240 41][]

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

status,age列分别创建索引：

ALTER TABLE user ADD INDEX index\_status (status);

![1240 42][]

ALTER TABLE user ADD INDEX index\_age (age);

![1240 43][]

user表索引详情：

SHOW INDEX FROM user;

![image][]

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

EXPLAIN SELECT \* FROM user WHERE status>5;

![1240 44][]

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

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

![.gif][]

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

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

![1240 45][]

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

EXPLAIN SELECT \* FROM user WHERE age>24;

![1240 46][]

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

![1240 47][]

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

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

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

user表的索引详情：

![1240 48][]

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

Using Index代表从索引中查询：

EXPLAIN SELECT status FROM user where status=1;

![1240 49][]

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

EXPLAIN SELECT \* FROM user where status=1;

![1240 50][]

10）建立索引的列，不允许为null。

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

remark列建立索引：

ALTER TABLE user ADD INDEX index\_remark (remark);

![1240 51][]

IS NULL可以命中索引：

EXPLAIN SELECT \* FROM user WHERE remark IS NULL;

![1240 52][]

IS NOT NULL不能命中索引：

EXPLAIN SELECT \* FROM user WHERE remark IS NOT NULL;

![1240 53][]

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

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

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

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

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

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

**3. 小结**

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

--------------------

扫描下方二维码，**及时**获取更多**互联网求职面经**、**java**、**python**、**爬虫**、**大数据**等技术，和**海量资料分享**：  
公众号\*\*`菜鸟名企梦`**后台发送“csdn”即可免费领取【csdn】和【百度文库】下载服务；  
公众号**`菜鸟名企梦`**后台发送“资料”:即可领取**5T精品学习资料\*\*、**java面试考点**和**java面经总结**，以及**几十个java、大数据项目**，**资料很全，你想找的几乎都有**  
![扫码关注，及时获取更多精彩内容。（博主今日头条大数据工程师）][1240 54]

**推荐阅读**

[☞真实靠谱：百度的职级、薪资、及晋升潜规则][Link 1]

[1240]: /images/20220215/cf02a9cd849c4573ba3acd776ea83f7b.png
[1240 1]: /images/20220215/718279499620452eb0825664d76d6264.png
[1240 2]: /images/20220215/472be84c5d8648ccb59a014077ecebf9.png
[1240 3]: /images/20220215/6a7114f43eab4040b55d339ed03c4ebf.png
[1240 4]: /images/20220215/f4519febd83141bfbc4b812825401eb3.png
[1240 5]: /images/20220215/5858557f18f24e90995a7a24851f23b7.png
[1240 6]: /images/20220215/016741e4655a486ea8a6ab289c37589c.png
[1240 7]: /images/20220215/be720d96e2664b998d29a0635e61c56a.png
[1240 8]: /images/20220215/0fa8243e7e5d4cba85c3c0a1ab0ee351.png
[1240 9]: /images/20220215/3d316b562aa240f0a358cc3a8ee3675b.png
[1240 10]: /images/20220215/66acdfb8e6ad4d3db813f4443d4ff208.png
[1240 11]: /images/20220215/c5e0c6332323480ab225016f6a7901cd.png
[1240 12]: /images/20220215/e1295e28c65249818f0e365c96e084fc.png
[1240 13]: /images/20220215/2ae2410287cc49bf88021962aa29c81d.png
[1240 14]: /images/20220215/f1c5aa5f30bb4bfd869429b8532d1a80.png
[1240 15]: /images/20220215/e78f6376d25649879359f537035194e0.png
[1240 16]: /images/20220215/d1ce21842f374e5fb37399101675c787.png
[1240 17]: /images/20220215/98bac50ad9844a1492ca4b36829deaf7.png
[1240 18]: /images/20220215/1c3e59d3a19d46b7a1577b5d513da109.png
[1240 19]: /images/20220215/d10f17acbdd848cebbad759439fee248.png
[1240 20]: /images/20220215/fd6dc47a1b624dee9916804d689b609f.png
[1240 21]: /images/20220215/bcfcfa690cd744a49ae6dcad7185207c.png
[1240 22]: /images/20220215/b797a06ff1764dbbbbb7c861dcebe84a.png
[1240 23]: /images/20220215/de984947dff14ae0b7b88c0716056074.png
[1240 24]: /images/20220215/402c9a10eef04697a1f92b8b3a64a368.png
[1240 25]: /images/20220215/910ce5e5a8264f3e966f184a129ef29f.png
[1240 26]: /images/20220215/007ad7e051ab4b90a4bd7a43287f6991.png
[1240 27]: /images/20220215/969e1f199ee44108a76fbf61f2ed72ba.png
[1240 28]: /images/20220215/968e674bdba84268bdf35fd8e8232eeb.png
[1240 29]: /images/20220215/4669ae422d7f4c65bde3ee117542068e.png
[1240 30]: /images/20220215/e4bcac717cf5425093ad72e89e2478aa.png
[1240 31]: /images/20220215/b76f9eda8637498b8617279c0fafafef.png
[1240 32]: /images/20220215/acd8c74f1c59468281005f9c61b8473a.png
[1240 33]: /images/20220215/7997346cb6164ac4b1de3c0f038c33a3.png
[1240 34]: /images/20220215/9423dba1338640b384fb2c2ce0acde5b.png
[1240 35]: /images/20220215/86c61c5fcdaa4bbea5035af09ded3438.png
[1240 36]: /images/20220215/9c0155c69a444cc38564c75666a73e41.png
[1240 37]: /images/20220215/95c416c173fb4ba19c752f80936d99cb.png
[1240 38]: /images/20220215/9b2e681b881a4f938fd796102679565c.png
[1240 39]: /images/20220215/b16a30d1c0ac4f618d93fa731ceea2b6.png
[1240 40]: /images/20220215/2b4415d17d9a4915a369c728f8457cb3.png
[1240 41]: /images/20220215/794c177876bd4e738a4baecda63a4291.png
[1240 42]: /images/20220215/65289d5cb44445b7bc61b5da5f54b5ae.png
[1240 43]: /images/20220215/4c627665db544be58e3805e9373117ee.png
[image]: /images/20220215/a5a14e6355c548b090d6a9a76d044dad.png
[1240 44]: /images/20220215/552f564089a842579b361b32f15e8cc9.png
[.gif]: https://upload-images.jianshu.io/upload_images/14326004-a223767289cb43e6.gif?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240
[1240 45]: /images/20220215/74be0d2c75f6494587f2fe461368e65e.png
[1240 46]: /images/20220215/fbb37e68681042f78b4fd584c4ff8178.png
[1240 47]: /images/20220215/000544a0a5f1416c990ff87bdcd801be.png
[1240 48]: /images/20220215/572f24e17ebc4113ab1420d9eb73712f.png
[1240 49]: /images/20220215/fc118e7fdb9c4fa6b893d96dddfa1994.png
[1240 50]: /images/20220215/f6d73a0c84f840d0bb881d1f61feba0e.png
[1240 51]: /images/20220215/8d6ed50789674d4f952118a1aeb76e8e.png
[1240 52]: /images/20220215/7c948be996984a1fa369c91501b76c24.png
[1240 53]: /images/20220215/3c61a6b8323d48ee93261512d579abf5.png
[1240 54]: /images/20220215/7ba052d327fe46c184f7cedf07990e62.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI3ODg2OTY1OQ==&mid=2247485097&idx=1&sn=cb6d491b138725e2011592c5fd63bb82&chksm=eb51255ddc26ac4b9419eceee109e86d78938e291accf933217f41ba34578d076f4dfbef3d0c&scene=21#wechat_redirect