Mysql的索引数据结构、sql性能分析工具、索引使用和设计原则

短命女 2023-10-13 16:29 33阅读 0赞

##### 索引概述 #####

**介绍**：索引是帮助mysql高效获取数据的有序数据结构，在数据之外，数据库系统还维护满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。  
**优缺点**：

<table> 
 <thead> 
  <tr> 
   <th>优势</th> 
   <th>劣势</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>提高数据检索的效率，降低数据库的IO成本</td> 
   <td>索引列也是要占用空间的</td> 
  </tr> 
  <tr> 
   <td>通过索引列对数据进行排序，降低数据排序的成本，降低CPU的小号</td> 
   <td>索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低</td> 
  </tr> 
 </tbody> 
</table>

我们平时在应用索引的时候，往往是忽略劣势的，应为磁盘是很便宜的，一半业务系统对更新数据的频率不会太高。

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

##### 索引结构 #####

**1.二叉树**  
![在这里插入图片描述][38d6f8f8a39b43a58a3934bc45df91c6.png_pic_center]  
二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量的情况下，层级较深，检索速度慢。  
**2.红黑树**  
![在这里插入图片描述][7b181afd1b5a4425b03df7fa3c90d530.png_pic_center]  
红黑树能解决顺序插入的问题，但是依然存在，大数据量的情况下，层级较深，检索速度慢。  
**3.B-Tree**(多路平衡查找树)  
上面的二叉树的结构都是每一个节点，最多2个指针，1个key,如果一个节点可以多个key，多个指针，就可以解决大数据量的情况下，层级较深，检索慢的问题。  
树的度数：指的是一个节点的子节点个数，比如一个最大度数为5的B-Tree,代表的含义是每一个节点最多有5个指针，4个Key  
数据结构可视化网站地址：[https://www.cs.usfca.edu/~galles/visualization/BTree.html][https_www.cs.usfca.edu_galles_visualization_BTree.html]  
![在这里插入图片描述][6dcb85f67f8947babe74c9c59413e842.png_pic_center]  
**4.B+Tree**  
B+树和B树的区别是，所有的数据都会出现在叶子节点，非叶子节点只起到索引的作用，这样就可以存放更多的数据，叶子节点形成一个单向链表。  
![在这里插入图片描述][b9e93c628c7c41c299095b6e822dc17c.png_pic_center]

**5.Mysql的B+树**  
Mysql索引数据结构对经典的B+树进行了优化。在原B+树的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+树，提高区间访问的性能。  
![在这里插入图片描述][5c17e4ed663b4bbda517296e31c7ddee.png_pic_center]  
**6.Hash索引**  
哈希索引就是采用一定的hash算法，将键值换算称新的hash值，，映射到对应的槽位上，然后存储在hash表中。如果两个或者多个键值，映射到一个相同的槽位上，他们就产生了hash冲突，可以通过链表来解决。

![在这里插入图片描述][6f45d008abe348dfb4468c8ef1a42e3f.png_pic_center]  
如上图所示，每一行数据对应一个hash值，比如要给name字段田间hash索引，通过一定的hash算法计算hash值得槽位，计算出来之后存储在对应的槽位即可，槽位中存储的是key是name字段，value是对应的hash值这个指针指向name=key的这一行数据。

Hash索引的特点：  
1）Hash索引只能用于对等比较（=，in）不支持范围查询（between,>,<）等；  
2）无法利用索引完成排序操作；  
3）查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引。  
在Mysql中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

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

##### 索引分类 #####

<table> 
 <thead> 
  <tr> 
   <th>分类</th> 
   <th>含义</th> 
   <th>特点</th> 
   <th>关键字</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主键索引</td> 
   <td>针对表中主键创建的索引</td> 
   <td>默认自动创建，只能有一个</td> 
   <td>PRIMARY</td> 
  </tr> 
  <tr> 
   <td>唯一索引</td> 
   <td>避免同一个表中某数据列中的重复</td> 
   <td>可以有多个</td> 
   <td>UNIQUE</td> 
  </tr> 
  <tr> 
   <td>常规索引</td> 
   <td>快速定位特定数据</td> 
   <td>可以有多个</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>全文索引</td> 
   <td>全文索引查找的是文本中的关键词，而不是比较索引中的值</td> 
   <td>可以有多个</td> 
   <td>FULLTEXT</td> 
  </tr> 
 </tbody> 
</table>

在InnoDB存储引擎中，根据索引的存储形式，可以分为以下两种：

<table> 
 <thead> 
  <tr> 
   <th>分类</th> 
   <th>含义</th> 
   <th>特点</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>聚集索引</td> 
   <td>将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据</td> 
   <td>必须有，而且只有一个</td> 
  </tr> 
  <tr> 
   <td>二级索引（非聚集索引）</td> 
   <td>将数据与索引分开存储，索引结构的<strong>叶子节点关联的是对应的主键</strong></td> 
   <td>可以存在多个</td> 
  </tr> 
 </tbody> 
</table>

如下图所示，id是主键，主键为聚集索引，为name列建立了一个非聚集索引，由图可看出，聚集索引将索引与数据存储放在一块，而二级索引叶子节点存放的是主键，需要进行一次回表查询才能查到,需要进行二次索引扫描。  
![在这里插入图片描述][81e00db373eb4816b1ae0c42c972ef3d.png_pic_center]

![在这里插入图片描述][12b92405409b4ddb87b293075bd7f2ea.png_pic_center]

聚集索引选取规则：  
1.如果存在主键，主键索引就是聚集索引。  
2.如果不存在主键，将使用第一个唯一索引作为聚集索引。  
3.如果没有主键和唯一索引，INNODB会自动生成一个rowid作为隐藏的聚集索引。

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

##### 索引语法 #####

**1.创建索引**

create [unioque|fulltext] index index_name on table_name(index_clomun)

**2.查看索引**

show index from table_name

**3.删除索引**

drop index index_name on table_name

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

##### SQL性能分析工具 #####

以下讲解sql的性能分析工具

**1.SQL执行频率**  
Mysql客户端连接成功后，通过show\[session|global\] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的增删改查的访问频次：

show global status like 'Com_______'

通过上面语句可以查看结果如下：  
![在这里插入图片描述][f4c2f9ec021f4ef0ac11f091942205ce.png_pic_center]  
**2.慢查询日志**  
慢查询日志记录了所有执行时间操作指定参数（long\_query\_time,单位：秒，默认10秒）的所有sql语句的日志。  
MYSQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cof）中配置如下信息：  
我们可以通过以下指令查看mysql慢查询日志是否开启

show variables like 'slow_query_log'

下面时配置慢查询日志的相关配置

#开启Mysql慢日志查询开关
    slow_query_log=1
    #设置慢日志的时间为2秒，sql语句执行时间草果2秒，就会视为慢查询，记录慢查询日志
    long_query_time=2

开启慢查询日志之后，在windows系统下，慢查询日志默认在\\data\\xxxx-slow.log下就是慢查询日志文件。  
我们通过慢查询日志可以统计出哪些查询语句执行耗时长。但是只能记录大于设定时间的才会记录

**3.profile**  
设置数据库的profile可以查看每一条查询语句的执行耗时情况，有的支持profile，有的不支持profile,我们可以通过以下命令查看是否支持profile:

select @@have_profiling;

执行结果如下：  
![在这里插入图片描述][091ba22ffd90477798cd8035eedaeb6a.png_pic_center]  
通过以下命令查看profile是否开启：

select @@profiling

查询结果如下：

![在这里插入图片描述][335c5d574f094632aa61a222087ee95e.png_pic_center]  
如果查询结果为0，可通过以下命令开启profile：

set global profiling = 1;

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

show profiles;

查询结果如下：  
![在这里插入图片描述][a3afbed9103d4d0db4fa3032412795c3.png_pic_center]

**4.explain 执行计划**

Explain 或者 DESC命令获取Mysql如何执行SELECT语句的信息，包括在SELECT 语句执行过程中表如何连接的顺序，语法就是直接在select语句之前加上关键字explain/desc,如下：

explain select 字段列表 from 表明 where 条件

任意执行一个语句，explain执行结果如下：  
![在这里插入图片描述][5a9c488420b04b53809d2cf7f3178b35.png_pic_center]  
接下来讲解Explain 执行计划各个字段每一列作用  
**1）id**  
select 查询的序列号，表示查询中执行了select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。  
现在有sys\_user,sys\_user\_role,sys\_role 3张表，这里自己建立表，并添加一定的数据。  
例1：查询每个用户所扮演的角色，sql如下：

select su.realname, sr.role_name from sys_user as su, sys_user_role as sur, sys_role as sr where su.id = sur.user_id and sur.role_id = sr.id;

查询结果如下：  
![在这里插入图片描述][4514f0a69bab4732bb7148e46aa3c786.png_pic_center]  
给select语句前面加上expalin，查看执行计划

explain select su.realname, sr.role_name from sys_user as su, sys_user_role as sur, sys_role as sr where su.id = sur.user_id and sur.role_id = sr.id;

执行结果如下：  
![在这里插入图片描述][0b5a482a65c04a69a415eb1aff8adb91.png_pic_center]  
从上面我们可以看出，id值一样，那么表的执行顺序为su-sur-sr  
例2：查询为临时角色的用户，通过子查询实现，sql如下：

select realname from sys_user where id in(select user_id  from sys_user_role where role_id in(select id from sys_role where role_name = '临时角色'));

执行结果如下：  
![在这里插入图片描述][b195a273acf44333b95fac4033a9cfb6.png_pic_center]  
从上面可以看出以上三个用户都有临时角色，然后加上explain，查看执行计划:

explain select realname from sys_user where id in(select user_id  from sys_user_role where role_id in(select id from sys_role where role_name = '临时角色'));

执行结果如下：  
![在这里插入图片描述][38f5732c6a624e5c8171e24880a5e929.png_pic_center]  
从上可以看出，id不一样，id值大的先执行，id值相同的从上往下执行，所以上面的执行顺序为sys\_role,sys\_user\_role,subquery2,sys\_user。

**2）select\_type**  
表示select类型，常见的取值有simple(简单表，即不使用表连接或者子查询)、primary(主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(select/where之后包含子查询)等

**3）type**  
表示连接类型，性能由好到差的连接类型为NULL、system 、const、eq\_ref、ref、range、index、all,在sql优化的时候尽量越靠左越好，一般我们使用主键和唯一索引条件进行查询会出现const,使用非唯一索引出现ref，如果出现All一般代表全表扫描，性能会很低。  
比如还是上面查询每个用户所扮演的角色的sql语句，执行结果如下：  
![在这里插入图片描述][ad0dd32f92584e4192d3f1e6a0fd841b.png_pic_center]  
执行性能分别有All,ref，eq\_ref

**4）possible\_key**  
显示可能应用在这张表上的索引，一个或多个。  
**5）key**  
实际使用的索引，如果为NULL，则没有使用索引。  
**6）key\_len**  
表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好  
**7）rows**  
mysql认为必须要执行查询的函数，在innodb引擎的表中，是一个估计值，可能并不总是准确的  
**8）filtered**  
表示返回结果的函数占需读取行数的百分比，filtered的值越大越好。

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

##### 索引使用 #####

**1.最左前缀法则**  
如果索引了多列（联合索引），要遵守最左前缀负责，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。**联合索引的最左边的字段必须存在，但并不是一定要放在第一位。**  
**2.范围查询**  
联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效，举一个如下的例子：

已建立profession, age, satus的联合索引
    explain select * from tb_user where profession = '软件工程' and age >30 and status = 0;
    explain select * from tb_user where profession = '软件工程' and age >=30 and status = 0;

上面的两个sql语句，第一句status会失效，第二句因为使用了>= status不会失效

**3.索引的使用原则**

**① 索引列运算**  
不要在索引列上进行运算操作， 索引将失效。

假设已经给phone字段加了索引，如果在查询的时候，对phone字段进行了运算，索引将会失效
    explain select * from tb_user where substring(phone,10,2) = '15'；

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

假设已经给phone字段加了索引，并且phone是字符串类型，如果在查询的时候，对phone字段没有加单引号，索引将会失效
    explain select * from tb_user where phone = 1212121;

**③ 模糊查询**  
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。  
例子：

explain select * from tb_user where profession like '软件%';索引不会失效
    explain select * from tb_user where profession like '%工%';索引失效
    explain select * from tb_user where profession like '%工程';索引失效

**④ or连接的条件**  
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那马涉及到的索引都不会被用到，只有or两边都建立了索引，才会走索引

假设id是主键，age没有建立索引
    explain select * from tb_user where id = 10 or age =23;索引会失效
    如果对age列也建立了索引，那么上面的sql语句将会走索引。

**⑤ 数据分布影响**  
如果Mysql 评估使用索引比全表更慢，则不使用索引。

**⑥SQL提示**  
场景：加入有一个user表，表中有 profession，phone，status 3个字段，加入对三个字段创建了一个联合索引，对profession字段创建了一个单列索引，那么以下的sql在执行的时候，mysql数据库会自动评判是走联合索引还是走单列索引：

explain select * from user where profession = “软件工程”
    以上sql语句，Mysql会自动评判走联合索引还是单列索引。

假如我们需要让sql语句走指定的索引，这时就引出了SQL提示。

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQl语句中加入一些人为的提示来达到优化操作的目的。  
use index:建议数据库走指定的索引，mysql数据库也会自己判定走不走；  
ignore index:表示数据库忽略指定的索引；  
force index:强制数据库走指定的索引。  
例子如下：

expalin select * from user use index(指定索引名称)where profession = '软件工程';
    expalin select * from user ignore index index(指定索引名称)where profession = '软件工程';
    expalin select * from user force index(指定索引名称)where profession = '软件工程';

**⑦ 覆盖索引&回表查询**  
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少select\*;  
在执行expalin时，如果extr中出现的信息如下：

<table> 
 <thead> 
  <tr> 
   <th>using index condition</th> 
   <th>查找使用了索引，但是需要回表查询数据</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>using where;using index</td> 
   <td>查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据</td> 
  </tr> 
 </tbody> 
</table>

下面以一个例子来讲解以下覆盖索引和回表查询：

![在这里插入图片描述][aa1d71e91a5246c7b7a3ecc2350a227a.png_pic_center]  
如上图所示，假如有一个tb\_user表，表中有id,name,gender,createdate4个字段，对name建立单列索引。  
当执行下面sql语句的时候，会直接走聚集索引，直接查询出所有数据

select * from tb_user where id = 2;

当执行下面sql语句的时候，由于给name字段加了索引，且查询的列字段就只有id,name，由于这两个字段都建立索引，所以直接走了辅助索引，而且辅助索引中存放了主键id,索引不用回表查询。

select id, name from tb_user where name = 'Arm';

当执行下面sql语句的时候，会走回表查询，因为要查询的字段中包含gender，该字段是没有建立索引的，会先走辅助索引找到对应的主键id，在根据主键id回表查询聚集索引找到gender的值。

select id, name, gender from tb_user where name = 'Arm'

**4.前缀索引**  
当字段类型为字符串（varchar,text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。  
语法：

create index inx_xxxx on table_name(column(n));n代表前缀长度

前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。  
下面是怎么选取n的方法，我们通过以下sql语句来求取指定n长度的选择：

select count(disinct subString(列名,1,5))/count(*) from table_name

下面举个例子，现在又一个user表，user表中有一个remark字段，该数据类型为text，假如我们现在需要对remark字段建立前缀索引来进行查询，但是又不能对整个字段建立索引，因为那样会占用很大的磁盘空间，user表如下：  
![在这里插入图片描述][bb21739b17674c2ba4ce260b31038faa.png_pic_center]

select count(distinct subString(remark,1,2))/count(*) from user;

执行结果如下：  
![在这里插入图片描述][a964830f71614efa9310aabd48e0f5d5.png_pic_center]  
当n=2时选择性为0.574，  
当n=3时，执行结果为1，如下：  
![在这里插入图片描述][abb01010ce09478ab21899b1d411da21.png_pic_center]  
证明我们完全就对前3个字符建立索引就可以得到很好的查询性能，不必要对整个字段建立索引，占用空间。

**5.单列索引&联合索引**  
单列索引：即一个索引只包含单个列。  
联合索引：即一个索引包含了多个列。  
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。  
多条件联合查询1时，Mysql优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。  
创建出来的联合索引如下图所示：  
![在这里插入图片描述][b09b12c5e28643099e313d8984deba7c.png_pic_center]  
在创建联合索引的时候，需要考虑字段的顺序。

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

##### 索引设计原则 #####

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

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

[38d6f8f8a39b43a58a3934bc45df91c6.png_pic_center]: https://img-blog.csdnimg.cn/38d6f8f8a39b43a58a3934bc45df91c6.png#pic_center
[7b181afd1b5a4425b03df7fa3c90d530.png_pic_center]: https://img-blog.csdnimg.cn/7b181afd1b5a4425b03df7fa3c90d530.png#pic_center
[https_www.cs.usfca.edu_galles_visualization_BTree.html]: https://www.cs.usfca.edu/~galles/visualization/BTree.html
[6dcb85f67f8947babe74c9c59413e842.png_pic_center]: https://img-blog.csdnimg.cn/6dcb85f67f8947babe74c9c59413e842.png#pic_center
[b9e93c628c7c41c299095b6e822dc17c.png_pic_center]: https://img-blog.csdnimg.cn/b9e93c628c7c41c299095b6e822dc17c.png#pic_center
[5c17e4ed663b4bbda517296e31c7ddee.png_pic_center]: https://img-blog.csdnimg.cn/5c17e4ed663b4bbda517296e31c7ddee.png#pic_center
[6f45d008abe348dfb4468c8ef1a42e3f.png_pic_center]: https://img-blog.csdnimg.cn/6f45d008abe348dfb4468c8ef1a42e3f.png#pic_center
[81e00db373eb4816b1ae0c42c972ef3d.png_pic_center]: https://img-blog.csdnimg.cn/81e00db373eb4816b1ae0c42c972ef3d.png#pic_center
[12b92405409b4ddb87b293075bd7f2ea.png_pic_center]: https://img-blog.csdnimg.cn/12b92405409b4ddb87b293075bd7f2ea.png#pic_center
[f4c2f9ec021f4ef0ac11f091942205ce.png_pic_center]: https://img-blog.csdnimg.cn/f4c2f9ec021f4ef0ac11f091942205ce.png#pic_center
[091ba22ffd90477798cd8035eedaeb6a.png_pic_center]: https://img-blog.csdnimg.cn/091ba22ffd90477798cd8035eedaeb6a.png#pic_center
[335c5d574f094632aa61a222087ee95e.png_pic_center]: https://img-blog.csdnimg.cn/335c5d574f094632aa61a222087ee95e.png#pic_center
[a3afbed9103d4d0db4fa3032412795c3.png_pic_center]: https://img-blog.csdnimg.cn/a3afbed9103d4d0db4fa3032412795c3.png#pic_center
[5a9c488420b04b53809d2cf7f3178b35.png_pic_center]: https://img-blog.csdnimg.cn/5a9c488420b04b53809d2cf7f3178b35.png#pic_center
[4514f0a69bab4732bb7148e46aa3c786.png_pic_center]: https://img-blog.csdnimg.cn/4514f0a69bab4732bb7148e46aa3c786.png#pic_center
[0b5a482a65c04a69a415eb1aff8adb91.png_pic_center]: https://img-blog.csdnimg.cn/0b5a482a65c04a69a415eb1aff8adb91.png#pic_center
[b195a273acf44333b95fac4033a9cfb6.png_pic_center]: https://img-blog.csdnimg.cn/b195a273acf44333b95fac4033a9cfb6.png#pic_center
[38f5732c6a624e5c8171e24880a5e929.png_pic_center]: https://img-blog.csdnimg.cn/38f5732c6a624e5c8171e24880a5e929.png#pic_center
[ad0dd32f92584e4192d3f1e6a0fd841b.png_pic_center]: https://img-blog.csdnimg.cn/ad0dd32f92584e4192d3f1e6a0fd841b.png#pic_center
[aa1d71e91a5246c7b7a3ecc2350a227a.png_pic_center]: https://img-blog.csdnimg.cn/aa1d71e91a5246c7b7a3ecc2350a227a.png#pic_center
[bb21739b17674c2ba4ce260b31038faa.png_pic_center]: https://img-blog.csdnimg.cn/bb21739b17674c2ba4ce260b31038faa.png#pic_center
[a964830f71614efa9310aabd48e0f5d5.png_pic_center]: https://img-blog.csdnimg.cn/a964830f71614efa9310aabd48e0f5d5.png#pic_center
[abb01010ce09478ab21899b1d411da21.png_pic_center]: https://img-blog.csdnimg.cn/abb01010ce09478ab21899b1d411da21.png#pic_center
[b09b12c5e28643099e313d8984deba7c.png_pic_center]: https://img-blog.csdnimg.cn/b09b12c5e28643099e313d8984deba7c.png#pic_center