Mysql索引创建以及优化步骤

QD_IT伟

于 2024-07-30 11:08:03 发布

阅读量312

点赞数 3

文章标签： mysql 数据库

WW_jkg

本文链接：https://blog.csdn.net/qq_41324075/article/details/140791771

版权

有一张表user（无索引）：

假如要执行的sql语句为：select * from user where age = 45;

需要从第一行开始，一直扫描到最后一行，称为全表扫描，性能很低；有没有提升性能，减少搜索时间的方法呢？

索引介绍

B+tree 结构介绍

在Mysql中，索引就是帮助搜索数据的一种有序的数据结构，它以某种方式引用（指向）数据。

Mysql中的索引是在存储引擎层实现的，因此不同的存储引擎又有着不同的索引结构，主要包含以下几种：

图片

简单介绍下经典的B+tree 的结构：

图片

可以看出：

所有的数据都会出现在叶子节点，叶子节点形成一个单向链表；
非叶子节点仅仅索引数据，具体的数据都是在叶子节点存放的；

Mysql索引数据结构对经典的B+tree进行了优化，在原有B+tree的基础上，增加了一个指向相邻叶子节点的链表指针，形成了带有顺序指针的B+tree，提高区间访问的性能，利于排序；

图片

索引分类

在mysql数据库，将索引的具体类型主要分为以下几类：

图片

在Innodb 存储引擎中，根据索引的存储形式，分为两种：

图片

执行一条查询语句，我们分析一下具体的查找过程：

图片

具体过程如下:

因为是根据name字段查询，所以先根据 name=‘Arm’ 到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10；
由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row；
最终拿到这一行的数据，直接返回即可。
索引语法

创建索引：

create [unique|fulltext] index 索引名 on 表名（字段名1，字段名2,……）;

查看索引：

show index from 表名；

删除索引：

drop index 索引名 on 表名；

SQL性能分析

sql执行频率

Mysql客户端链接成功后，通过以下命令可以查看当前数据库的 insert/update/delete/select 的访问频次：

show [session|global] status like ‘com_____’;

session: 查看当前会话；
global: 查看全局数据；
com insert: 插入次数；
com select: 查询次数；
com delete: 删除次数；
com updat: 更新次数；

图片

通过查看当前数据库是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据，如果以增删改为主，可以考虑不对其进行索引的优化；如果以查询为主，就要考虑对数据库的索引进行优化。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位秒，默认10秒）的所有sql日志：

开启慢查询日志前，需要在mysql的配置文件中（/etc/my.cnf）配置如下信息：

1. 开启mysql慢日志查询开关：

slow_query_log = 1
#2. 设置慢日志的时间，假设为2秒，超过2秒就会被视为慢查询，记录慢查询日志：
long_query_time=2
#3. 配置完毕后，重新启动mysql服务器进行测试：
systemctl restarmysqld
#4. 查看慢查询日志的系统变量，是否打开：
show variables like “slow_query_log”;
#5. 查看慢日志文件中（/var/lib/mysql/localhost-slow.log）记录的信息：
Tail -f localhost-slow.log

最终发现，在慢查询日志中，只会记录执行时间超过我们预设时间（2秒）的sql，执行较快的sql不会被记录。

图片

Profile 详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

#1. 通过 have_profiling 参数，可以看到mysql是否支持profile 操作：
select @@have_profiling;
#2. 通过set 语句在session/global 级别开启profiling:
set profiling =1;

开关打开后，后续执行的sql语句都会被mysql记录，并记录执行时间消耗到哪儿去了。比如执行以下几条sql语句：

select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = ‘白起’;
select count(*) from tb_sku;

#3. 查看每一条sql的耗时基本情况：
show profiles;

图片

#4. 查看指定的字段的sql 语句各个阶段的耗时情况：

show profile for query Query_ID;

图片

#5. 查看指定字段的sql语句cpu 的使用情况：

show profile cpu for query Query_ID;

explain 详情

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中，表如何连接和连接的顺序。

语法 :直接在 select 语句之前加上关键字 explain/desc;

explain select 字段 from 表名 where 条件;

图片

索引使用

索引失效的情况

1.1 最左前缀法则
如果存在联合索引，要遵守最左前缀法则。即查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃其中某一列，索引将会部分失效（后面的字段索引失效)。

假设在 tb_user 表中

联合索引，涉及三个字段，顺序为：profession（索引长度为47）, age（索引长度为2）, status（索引长度为5）。

a)explain select * from tb_user where profession = '软件工程 ’ and age = 31 and status=‘0’;

图片

b)explain select * from tb_user where profession = '软件工程 ’ and age = 31 ;

图片

c)explain select * from tb_user where profession = '软件工程‘;

图片

以上的这三组测试中，我们发现只要联合索引最左边的字段profession存在，索引就会生效；

a)explain select * from tb_user where age = 31 and status=‘0’;

图片

b)explain select * from tb_user where status=‘0’;

图片

以上的这两组测试中，我们发现只要联合索引最左边的字段 profession不存在，索引并未生效；

explain select * from tb_user where profession = '软件工程 ’ and status=‘0’;

图片

上述的一条SQL查询时，联合索引最左边的列profession字段是存在的，索引满足最左前缀法则的基本条件。但是查询时，跳过了age这个列，所以后面的列索引是不会使用的，也就是索引部分生效，所以索引的长度为47；

explain select * from tb_user where age = 31 and status=‘0’ and profession = '软件工程’;

图片

可以看到，索引长度54，完全满足最左前缀法则，联合索引生效。

⚠️ 最左前缀法则中是指查询时，联合索引的最左边的字段（即第一个字段）必须存在，与编写sql时，条件编写的先后顺序无关。

1.2 范围查询
联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效。

explain select * from tb_user where profession = '软件工程 ’ and age >31 and status=‘0’;

图片

上述可以看到，当范围查询使用>或<时，查询走联合索引了，但是索引长度为49，说明范围查询右边的status字段没有走索引；

explain select * from tb_user where profession = '软件工程 ’ and age >= 31 and status=‘0’;

图片

上述可以看到，当范围查询使用>=或<=时，查询走联合索引了，但是索引长度为54，说明所有字段都是走索引的；

⚠️ 所以，在业务允许的情况下，尽可能使用类似于>=或<=这类的范围查询，避免使用>或<。

1.3 索引列运算
在索引列上进行运算操作，索引将失效。

假设在 tb_user 表中，存在单列索引：phone

explain select * from tb_user where phone = ‘17799990015’;

图片

上述看到，当根据phone字段进行等值匹配查询时，索引生效。

explain select * from tb_user where substring(phone,10,2)=’15’;

图片

上述看到，当根据phone字段进行函数运算操作之后，索引失效。

1.4 字符串不加引号
字符串类型字段使用时，不加引号，索引将失效。

explain select * from tb_user where profession = '软件工程 ’ and age = 31 and status = '0 ';
explain select * from tb_user where profession = '软件工程 ’ and age = 31 and status = 0;

图片

explain select * from tb_user where phone = ‘1779990015’;
explain select * from tb_user where phone = 1779990015;

图片

上述看到，字符串不加单引号时，对于查询结果没有影响，但是由于数据库存在隐式类型转换，索引将失效。

1.5 模糊查询
尾部模糊匹配，索引不会失效；头部模糊匹配，索引失效。

explain select * from tb_user where profession like ‘软件%’;
explain select * from tb_user where profession like ‘%工程’;
explain select * from tb_user where profession like ‘%工%’;

图片

上述看到，在like模糊查询中，在关键字后面加%，索引可以生效；在关键字前面加%，索引将会失效。

1.6 or 连接条件
or 前的条件中的列有索引，后面的列中没有索引，则涉及的所有索引都不会被用到。

步骤一：存在单列索引：phone

explain select * from tb_user where id=10 or age=23;

explain select * from tb_user where phone=’1779990017’ or age=23;

图片

上述看到，由于age 没有索引，所以即使id, phone有索引，索引也会失效。

步骤二：对age字段建立索引

create index idx_user on tb_user(age);

图片

步骤三：再次执行上述的sql语句

图片

可以看到，当or 连接的条件，左右两侧字段都有索引时，索引才会生效。

1.7 数据分布影响
Mysql评估使用索引会比全表更慢，则不会使用索引。

explain * from tb_user where phone >= ‘1779999005’;
explain * from tb_user where phone >= ‘1779999015’;

图片

上述看到，相同的sql语句，传入字段值不同时，所执行的计划也不同，这是因为：

mysql在查询时，会评估使用索引的效率与走全表扫描的效率，哪种效率高使用哪种。因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则不如走全表扫描来的快，此时索引就会失效。

再来看下，is null & is not null 的操作是否会走索引：

步骤一：

explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;

图片

步骤二：把profession 字段全部更新为null：

update tb_user set profession = null;

图片

步骤三：再次执行上述语句

图片

最终看到，相同的sql语句，先后执行两次，查询的计划不同。

这是因为和数据库的数据分布有关系，查询是mysql会评估，走索引还是全表扫描，如果全表扫描块，则放弃索引走全表扫描，因此，is null & is not null 是否走索引，得具体情况具体分析，不是固定的。

SQL提示

字段profession 存在联合索引(idx_user_pro_sta) & 单列索引（idx_user_pro）。

explain select * from tb_user where profession=’软件工程’;

图片

可以看出，mysql 最终选择了idx_user_pro_age_sta 联合索引，这是mysql自动选择的结果。

那么，我们在查询时，可以使用mysql的sql提示，加入一些人为的提示来达到优化操作的目的：

user index：建议mysql使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）；
ignore index：忽略指定的索引；
force index：强制使用索引。

演示：

1）explain select * from tb_user use index(idx_user_pro) where profession=’软件工程’;

图片

2）explain select * from tb_user ignore index(idx_user_pro) where profession=’软件工程’;

图片

3）explain select * from tb_user force index(idx_user_pro) where profession=’软件工程’;

图片

覆盖索引

覆盖索引是指查询使用了索引且返回需要的列，在该索引列中已经全部能够找到。

在查询时，尽量使用覆盖索引，减少select *。

表中存在联合索引 idx_user_pro_age_sta (关联了三个字段profession, age, status)，该索引也是一个二级索引，该叶子节点下面的是这一行的主键id。当查询返回的数据在id, profession, age, status 中，则直接走二级索引返回数据，如果查询字段超出这个范围，就需要拿到主键id, 再去扫描聚集索引获取额外的数据，这个过程就是回表。

当我们一直使用 select * 查询返回所有字段值，很容易造成回表查询（除非根据主键查询，此时只会扫描聚集索引）。

演示如下：

explain select id, profession, from tb_user where profession=’软件工程’ and age=31 and status=’0’;
explain select id, profession, age, status from tb_user where profession=’软件工程’ and age=31 and status=’0’;
explain select id, profession, age, status, name from tb_user where profession=’软件工程’ and age=31 and status=’0’;
explain select * from tb_user where profession=’软件工程’ and age=31 and status=’0’;

图片

为了让大家更清楚地理解，什么是覆盖索引和回表查询，我们一起来看下一组sql的执行过程：

A. 表结构及索引示意图:

id是主键，是一个聚集索引。name字段建立了普通索引，是一个二级索引(辅助索引)。

图片

B. 执行SQL: select * from tb_user where id = 2;

根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

图片

C. 执行SQL: selet id,name from tb_user where name = ‘Arm’;

虽然是根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。

图片

D. 执行SQL: selet id,name,gender from tb_user where name = ‘Arm’;

由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。

图片

🤔思考一下：一张表四个字段（id, username, password, status）,如何对sql优化最优？

select id, username, password from tb_user where username=’itcast’;

答案：针对username, password 建立联合索引，避免回表查询。

前缀索引

当字段类型为字符串（varchar, text, longtext等）时，有时候需要索引很长的字符串，导致索引较大，查询是浪费大量的磁盘IO，影响查询效率。此时可以只对字符串的一部分前缀建立索引，节约索引空间，提高索引效率。

1）语法：create index idx_xxx on 表名（column(n)）;

create index idx_email on tb_user(email(5)); # 为表tb_user的 email 字段建立长度为5的前缀索引。

图片

2）前缀长度

可以根据索引的选择性来决定，选择性=不重复的索引值（基数）/数据表的记录总数。

索引选择性越高则查询效率最高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

select count(distinct email)/count(*) from tb_user;

select count(distinct substring(email, 1, 5)/count(*) from tb_user;

3)前缀索引的查询流程

图片

单列索引&联合索引

单列索引：一个索引只包含单个列

联合索引：一个索引包含了多个列

当and 连接的两个字段 phone、name上都有单列索引，mysql最终只会选择一个索引，也就是说只能走一个字段的索引，此时会进行回表查询的。

图片

此时，我们创建一个phone和name字段的联合索引。

create unique index_user_phone_name on tb_user(phone, name);

查询以下执行计划：

图片

此时查询时，走了联合索引，在联合索引中包含phone, name的信息，在叶子节点下挂的是对应的主键id，所以查询不需要回表查询。

⚠️ 所以在业务场景中，若存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

查询使用联合索引时，具体的结构示意图如下：

图片

索引设计原则

1)针对数据量较大，且查询比较繁琐的表建立索引；
2)针对于常作为查询条件（where），排序（order by），分组(group by)操作的字段，建立索引；
3)尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高；
4)如果是字符串类型的字段，字段的长度过长，可以针对字段的特点，建立前缀索引；
5)尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率；
6)要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率；
7)如果索引列不能存储null值，在创建表时使用not null约束它。当优化器知道每列是否包含null值时，它可以更好地确定哪个索引最有效地用于查询。

SQL优化

主键优化

1.1 主键顺序插入

从磁盘中申请页，主键顺序插入；

图片

第一个页没有满，继续往第一页插入；

图片

当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接；

图片

当第二页写满了，再往第三页写入；

图片

1.2 主键乱序插入
1.2.1 页分裂
1.假如1#,2#页都已经写满了，存放了如图所示的数据；

图片

2.此时再插入id为50的记录，我们来看看会发生什么现象会再次开启一个页，写入新的页中吗?

图片

不会。因为，索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后。

图片

3.但是47所在的1#页，已经写满了，存储不了50对应的数据了。那么此时会开辟一个新的页 3#。

图片

4.但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50。

图片

5.移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个页，应该是3#， 3#的下一个页是2#。所以，此时，需要重新设置链表指针。这种现象，称之为 “页分裂”，是比较耗费性能的操作。

图片

1.2.2 页合并
1.目前表中已有数据的索引结构(叶子节点)如下:

图片

2.当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。

图片

3.继续删除2#数据，当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

图片

4.删除数据，并将页合并之后，再次插入新的数据21，则直接插入3#页。

图片

这个里面所发生的合并页的这个现象，就称之为 “页合并”。

1.2.3 索引设计原则
满足业务需求的情况下，尽量降低主键的长度；
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键；
尽量不要使用UUID做主键或者是其他自然主键，如身份证号；
业务操作时，避免对主键的修改。

order by优化

MySQL的排序，有两种方式:

图片

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

接下来，我们来做一个测试:

A. tb_user表中所建立的部分索引删除掉

图片

B. 执行排序SQL

explain select id,age,phone from tb_user order by age ;

图片

explain select id,age,phone from tb_user order by age, phone ;

图片

由于 age, phone 都没有索引，所以此时再排序时，出现Using filesort，排序性能较低。

C. 创建索引

create index idx_user_age_phone_aa on tb_user(age, phone);

D. 创建索引后，根据age, phone进行升序排序

explain select id,age,phone from tb_user order by age;

图片

explain select id,age,phone from tb_user order by age , phone;

图片

建立索引之后，再次进行排序查询，就由原来的Using filesort，变为了 Using index，性能就是比较高的了。

E. 创建索引后，根据age, phone进行降序排序

explain select id,age,phone from tb_user order by age desc , phone desc ;

图片

也出现 Using index，但是此时Extra中出现了 Backward index scan，这个代表反向扫描索引。因为在Mysql中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。在 MySQL8版本中，支持降序索引，我们也可以创建降序索引。

F. 根据phone，age进行升序排序，phone在前，age在后

explain select id,age,phone from tb_user order by phone , age;

图片

排序时,也需要满足最左前缀法则,否则也会出现 filesort。因为在创建索引的时候， age是第一个字段，phone是第二个字段，所以排序时，也就该按照这个顺序来，否则就会出现 Using filesort。

G. 根据age, phone进行降序一个升序，一个降序

explain select id,age,phone from tb_user order by age asc, phone desc ;

图片

因为创建索引时，未指定顺序，所以默认都是按照升序排序的，

图片

而查询时，一个升序，一个降序，此时就会出现Using filesort。为了解决上述的问题，我们可以创建一个索引，这个联合索引中 age 升序排序，phone 倒序排序。

H. 创建联合索引(age 升序排序，phone 倒序排序)

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

图片

I. 然后再次执行如下SQL

explain select id,age,phone from tb_user order by age asc , phone desc ;

图片

升序/降序联合索引结构图示:

图片

由上述的测试,我们得出order by优化原则:

1）根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则；
2）尽量使用覆盖索引；
3）多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)；
4）如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。