MySQL索引优化

电玩女神 2022-03-22 14:15 375阅读 0赞

# MySQL索引优化 #

## **一、MySQL索引基础** ##

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

此部分用于测试索引创建的user表的结构如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW5nX3hz_size_16_color_FFFFFF_t_70]

### **什么是索引？** ###

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

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

### **索引类型** ###

可以使用SHOW INDEX FROM table\_name;查看索引详情：  
![在这里插入图片描述][20190128180814157.png]  
主键索引 PRIMARY KEY：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。注意：一个表只能有一个主键。  
![在这里插入图片描述][20190128180832639.png]  
唯一索引 UNIQUE：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以通过ALTER TABLE table\_name ADD UNIQUE (column);创建唯一索引：  
![在这里插入图片描述][20190128180847926.png]  
![在这里插入图片描述][20190128180853942.png]  
可以通过ALTER TABLE table\_name ADD UNIQUE (column1,column2);创建唯一组合索引：  
![在这里插入图片描述][20190128180916493.png]  
![在这里插入图片描述][20190128180957582.png]

普通索引 INDEX：这是最基本的索引，它没有任何限制。可以通过ALTER TABLE table\_name ADD INDEX index\_name (column);创建普通索引：  
![在这里插入图片描述][2019012818101967.png]  
组合索引 INDEX：即一个索引包含多个列，多用于避免回表查询。可以通过ALTER TABLE table\_name ADD INDEX index\_name(column1,column2, column3);创建组合索引：  
![在这里插入图片描述][20190128181311445.png]  
![在这里插入图片描述][2019012818120314.png]

全文索引 FULLTEXT：也称全文检索，是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table\_name ADD FULLTEXT (column);创建全文索引：  
![在这里插入图片描述][20190128181325708.png]  
![在这里插入图片描述][20190128181343250.png]

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

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

### **索引设计的原则** ###

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

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

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

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

## **MySQL索引优化实战** ##

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

此部分用于测试的user表结构：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW5nX3hz_size_16_color_FFFFFF_t_70 1]

### **索引相关的重要概念** ###

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

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

![在这里插入图片描述][2019012818142148.png]

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

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

SELECT \* FROM user;  
![在这里插入图片描述][2019012818145398.png]  
查询满足性别为0（男）的记录数：

那么返回记录的比例数是：  
![在这里插入图片描述][20190128181504750.png]  
同理，查询name为’swj’的记录数：  
![在这里插入图片描述][2019012818151414.png]  
返回记录的比例数是：  
![在这里插入图片描述][20190128181528361.png]  
现在问题来了，假设name、gender列都有索引，那么SELECT \* FROM user WHERE gender = 0; SELECT \* FROM user WHERE name = ‘swj’;都能命中索引吗？

user表的索引详情：  
![在这里插入图片描述][20190128181538312.png]  
SELECT \* FROM user WHERE gender = 0;没有命中索引，注意filtered的值就是上面我们计算的返回记录的比例数。  
![在这里插入图片描述][20190128181549581.png]  
SELECT \* FROM user WHERE name = ‘swj’;命中了索引index\_name，因为走索引直接就能找到要查询的记录，所以filtered的值为100。  
![在这里插入图片描述][20190128181558965.png]  
因此，返回表中30%内的数据会走索引，返回超过30%数据就使用全表扫描。当然这个结论太绝对了，也并不是绝对的30%，只是一个大概的范围。

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

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

### **索引优化实战** ###

有些时候虽然数据库有索引，但是并不被优化器选择使用。我们可以通过SHOW STATUS LIKE ‘Handler\_read%’;查看索引的使用情况：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW5nX3hz_size_16_color_FFFFFF_t_70 2]

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

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

### 索引优化规则 ###

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

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

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

name列创建普通索引：

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

EXPLAIN SELECT \* FROM user WHERE name LIKE ‘%s%’;  
![在这里插入图片描述][20190128181623800.png]

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

EXPLAIN SELECT \* FROM user WHERE name LIKE ‘s%’;  
![在这里插入图片描述][20190128181633183.png]  
3）数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。

EXPLAIN SELECT \* FROM user WHERE name=1;  
![在这里插入图片描述][20190128181644989.png]  
EXPLAIN SELECT \* FROM user WHERE name=‘1’;  
![在这里插入图片描述][20190128181655756.png]  
4）复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中符合索引。

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

ALTER TABLE user ADD INDEX index\_name (name,age,status);  
![在这里插入图片描述][20190128181705755.png]  
user表索引详情：

SHOW INDEX FROM user;  
![在这里插入图片描述][201901281817172.png]  
根据最左原则，可以命中复合索引index\_name：

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

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

EXPLAIN SELECT \* FROM user WHERE status=1 AND name=‘swj’;  
![在这里插入图片描述][20190128181724741.png]  
而是查询条件中是否包含索引最左列字段：

EXPLAIN SELECT \* FROM user WHERE status=2 ;  
![在这里插入图片描述][20190128181736840.png]

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

union:

EXPLAIN SELECT\*FROM user WHERE status=1

UNION ALL

SELECT\*FROM user WHERE status = 2;  
![在这里插入图片描述][20190128181746468.png]  
in:

EXPLAIN SELECT \* FROM user WHERE status IN (1,2);  
![在这里插入图片描述][20190128181758897.png]  
or:

EXPLAIN SELECT\*FROM user WHERE status=1OR status=2;  
![在这里插入图片描述][2019012818181018.png]  
查询的CPU消耗：or>in>union。

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

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

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

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

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

status列创建索引：

ALTER TABLE user ADD INDEX index\_status (status);  
![在这里插入图片描述][20190128181818563.png]  
user表索引详情：

SHOW INDEX FROM user;  
![在这里插入图片描述][20190128181829373.png]  
负向条件不能命中缓存：

EXPLAIN SELECT \* FROM user WHERE status !=1 AND status != 2;  
![在这里插入图片描述][2019012818183899.png]

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

EXPLAIN SELECT \* FROM user WHERE status IN (0,3,4);  
![在这里插入图片描述][20190128181851386.png]  
8）范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等。

status,age列分别创建索引：

ALTER TABLE user ADD INDEX index\_status (status);  
![在这里插入图片描述][20190128181858891.png]  
ALTER TABLE user ADD INDEX index\_age (age);  
![在这里插入图片描述][20190128181905849.png]  
user表索引详情：

SHOW INDEX FROM user;

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

EXPLAIN SELECT \* FROM user WHERE status>5;  
![在这里插入图片描述][20190128181918813.png]  
范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引：

EXPLAIN SELECT \* FROM user WHERE status>5 AND age<24;  
![在这里插入图片描述][20190128181928111.png]  
如果是范围查询和等值查询同时存在，优先匹配等值查询列的索引：

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

![在这里插入图片描述][20190128181939155.png]  
8）数据库执行计算不会命中索引。

EXPLAIN SELECT \* FROM user WHERE age>24;  
![在这里插入图片描述][20190128181948472.png]

EXPLAIN SELECT \* FROM user WHERE age+1>24;  
![在这里插入图片描述][20190128181957485.png]  
计算逻辑应该尽量放到业务层处理，节省数据库的CPU的同时最大限度的命中索引。

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

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

user表的索引详情：  
![在这里插入图片描述][20190128182012497.png]  
因为status字段是索引列，所以直接从索引中就可以获取值，不必回表查询：

Using Index代表从索引中查询：

EXPLAIN SELECT status FROM user where status=1;  
![在这里插入图片描述][20190128182028757.png]  
当查询其他列时，就需要回表查询，这也是为什么要避免SELECT\*的原因之一：

EXPLAIN SELECT \* FROM user where status=1;  
![在这里插入图片描述][20190128182040449.png]  
10）建立索引的列，不允许为null。

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

remark列建立索引：

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

img

IS NULL可以命中索引：

EXPLAIN SELECT \* FROM user WHERE remark IS NULL;  
![在这里插入图片描述][20190128182109203.png]  
IS NOT NULL不能命中索引：

EXPLAIN SELECT \* FROM user WHERE remark IS NOT NULL;  
![在这里插入图片描述][20190128182120472.png]  
虽然IS NULL可以命中索引，但是NULL本身就不是一种好的数据库设计，应该使用NOT NULL约束以及默认值。

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

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

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

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

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

### **小结** ###

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

###### 本文详情来源：[https://mp.weixin.qq.com/s/FgMi\_1qeed08O8x1UHmfMw][https_mp.weixin.qq.com_s_FgMi_1qeed08O8x1UHmfMw] ######

### 更多优秀文章 ###

> java界的小学生  
> [https://blog.csdn.net/shang\_xs][https_blog.csdn.net_shang_xs]

> 更多MySQL相关
> 
> SQL使用常见错误之如何对MySQL友好
> 
> [https://blog.csdn.net/shang\_xs/article/details/90713370][https_blog.csdn.net_shang_xs_article_details_90713370]

### 公众号推荐 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW5nX3hz_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW5nX3hz_size_16_color_FFFFFF_t_70]: /images/20220322/88ac8f9e82954d06a5ad7183059ccc8a.png
[20190128180814157.png]: /images/20220322/519b6a7a98194bb88ae568f961f4b81b.png
[20190128180832639.png]: /images/20220322/679bd09ce41c470dbe5978a3874ed95c.png
[20190128180847926.png]: /images/20220322/de21909c63874c53a1421fb4642d9156.png
[20190128180853942.png]: /images/20220322/33d19cac05a84ff5a7770a71def2fe21.png
[20190128180916493.png]: /images/20220322/c952e96242784eadaaa55f4a1710b846.png
[20190128180957582.png]: /images/20220322/b157b802ac284015911810dff452bd1f.png
[2019012818101967.png]: /images/20220322/9c55527047274d3daa8054814c14faf8.png
[20190128181311445.png]: /images/20220322/7d929caaebe44910a15742c130f7471c.png
[2019012818120314.png]: /images/20220322/9b7407460289442f9e977366fb5acc0b.png
[20190128181325708.png]: /images/20220322/b1b07ef6aa4b4a04b44d7876dad5b107.png
[20190128181343250.png]: /images/20220322/a225e8abc833418894f827776b97c7c0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW5nX3hz_size_16_color_FFFFFF_t_70 1]: /images/20220322/bbd5b40509554dff8b6878caaad98c5a.png
[2019012818142148.png]: /images/20220322/5e9da3170cc84ae9b18d381182c3e393.png
[2019012818145398.png]: /images/20220322/71fffa05b3954bcb9ed06241acb18113.png
[20190128181504750.png]: /images/20220322/d84ce78b8f7342019a72ae7dad2ffa81.png
[2019012818151414.png]: /images/20220322/e3610591ddef4a079eddfc693df6a9f4.png
[20190128181528361.png]: /images/20220322/d46b05d5bad94c1891a421e94fe099dd.png
[20190128181538312.png]: /images/20220322/022f710fc8fd47769c0acead6720c69b.png
[20190128181549581.png]: /images/20220322/f68eb9c613fd4d49b3b84ce4ce3cc94d.png
[20190128181558965.png]: /images/20220322/6806952fa4774fa6a1539157318febb3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW5nX3hz_size_16_color_FFFFFF_t_70 2]: /images/20220322/dc89df6f6a544c8dbc0ff8117fa2e906.png
[20190128181623800.png]: /images/20220322/980e1af02414446f838fd8e46e21f8f0.png
[20190128181633183.png]: /images/20220322/4d90e58394b24419b87a047e268fd104.png
[20190128181644989.png]: /images/20220322/dcc0f59a976d41db99da45c1497628e0.png
[20190128181655756.png]: /images/20220322/19d6cf74f8904f1a9008c3732e0db35f.png
[20190128181705755.png]: /images/20220322/297f69ae033f43f085d1a6cb46f3c6fb.png
[201901281817172.png]: /images/20220322/8893338c017941b79197379e9e68ddfd.png
[20190128181724741.png]: /images/20220322/9d42bb4f567745d1a4bb5fc82f444f38.png
[20190128181736840.png]: /images/20220322/2c1bfbb0956f4c6a93521b784036dda8.png
[20190128181746468.png]: /images/20220322/154d7a9edd4e4937ac7099d94726c62d.png
[20190128181758897.png]: /images/20220322/8d547b228c9b46288af9b5930d846f38.png
[2019012818181018.png]: /images/20220322/fea65bcda54a400793bee04b30a5cafc.png
[20190128181818563.png]: /images/20220322/3a811afe2fec47f89ff521f25b4ea4b3.png
[20190128181829373.png]: /images/20220322/b2baa4464f2944409eeed538ea11ccdf.png
[2019012818183899.png]: /images/20220322/69c627f44c70469f86dbb7ba7650577d.png
[20190128181851386.png]: /images/20220322/af599edb2dee4b94bf0d53220a524764.png
[20190128181858891.png]: /images/20220322/48579ef3dcab4d6098fa4c17652bbc22.png
[20190128181905849.png]: /images/20220322/72a3573efe664b3082ee478b6864b24c.png
[20190128181918813.png]: /images/20220322/390efb27e9a4436abbdf1f35a989c0ed.png
[20190128181928111.png]: /images/20220322/6abac74581d64eb48dd47e08b05bd4d6.png
[20190128181939155.png]: /images/20220322/1d9e3627c0ed4deaaa0f61042b8393b1.png
[20190128181948472.png]: /images/20220322/c2885ef76d6e44968f2245ae79ce5d7f.png
[20190128181957485.png]: /images/20220322/4425b001b5e7482295b881b0c3436346.png
[20190128182012497.png]: /images/20220322/53dce234ccab4fef886cb9057b7c9f3a.png
[20190128182028757.png]: /images/20220322/b6de8f07229d4c8e92b3be93d8b6d6f5.png
[20190128182040449.png]: /images/20220322/0e8b84919365456fa7f55f1e30a8c4cd.png
[20190128182109203.png]: /images/20220322/8cb7d4d271c6479a8340a1f1d667858b.png
[20190128182120472.png]: /images/20220322/b6d1466a9ef94496b8b64d136d334b06.png
[https_mp.weixin.qq.com_s_FgMi_1qeed08O8x1UHmfMw]: https://mp.weixin.qq.com/s/FgMi_1qeed08O8x1UHmfMw
[https_blog.csdn.net_shang_xs]: https://blog.csdn.net/shang_xs
[https_blog.csdn.net_shang_xs_article_details_90713370]: https://blog.csdn.net/shang_xs/article/details/90713370
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW5nX3hz_size_16_color_FFFFFF_t_70 3]: /images/20220322/cacc865c904d4f03a87b091b8734f45a.png