mysql索引创建规则、联合与一般索引、执行计划、索引选择，索引重建与下推

左手的ㄟ右手 2022-04-05 11:52 562阅读 0赞

# #

## 数据库索引怎么建,什么时候用到 ##

索引优缺点:为主键外建where子句建立索引可以加速数据库查询,但是索引占用内存,同时update和insert的时候需要同步修改;**索引的实现通常使用其变种B+树**。

建立索引方式:create index 索引名 on 表名(列名)；

细节问题:

1.  )如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命性的结果下降,每次查找一条特定的数据都会进行一次全表扫描
2.  ）防止过度索引,比如性别只有两个值,此时对该列建立索引不仅没有什么优势,反而会影响更新插入等操作的速度.

3.)如果where条件进行了联合查询,则可以使用复合索引,如:

Select \* from users where area=”beijing” and age=22;此时可以对area和age建立联合主键，而不是单单对area做索引，因为联合索引更加迅速

如果我们创建了(area,age,salary)复合主键,此时其实相当于创建了(area,age,salary),(area,age),(area)三个索引,这被称为最佳左前缀;因此在建立主键的时候应该将最常用的结果并且字段类型比较小的字段放在前面.

1.  索引不会含有null值得列,如果复合索引中只要有一列含有null值,那么这一列在复合索引中就是无效的.
2.  Mysql查询中只使用一个索引,如果where子句中已经使用了索引,那么order by中的列是不会使用索引的.如果排序使用了多个列,可以考虑复合索引
3.  对于like “%a%”不会使用索引,但是类似 like “aa%”可以使用索引

### 哪些字段适合添加索引： ###

1、表的主键、外键必须有索引；

2、数据量超过300的表应该有索引；

3、经常与其他表进行连接的表，在连接字段上应该建立索引；

4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

5、索引应该建在选择性高的字段上；

6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

F、如果(a，b，c)是联合索引，where a='a' and b>'b' and c='c',那么c实际不会走索引。

8、频繁进行数据操作的表，不要建立太多的索引；

9、删除无用的索引，避免对执行计划造成负面影响；

上面说明了联合索引和多个单字段索引分别在什么情况下使用：

1.  复合索引的几个字段是否经常同时以AND方式出现在Where子句中
2.  单字段查询是否极少甚至没有

如果上面两个答案都是‘是’则使用联合索引

## 对字符串建索引的注意点： ##

如果字符串长度比较长，会导致索引占用内存比较大，但是如果只去字段的一部分作为索引，又会降低索引区分度。矛盾了

1前(后)缀索引  
1.1会降低区分度，可以使用类似count(distinct(left(table\_name,5))) as l5来统计  
1.2导致覆盖索引无法正常使用  
2增加字段的hash值作为一列，然后使用该列建索引,无法使用等值查询

## 执行计划 ##

如果建了索引之后不确定效果，可以使用mysql自带的执行计划看下索引效果，下面介绍执行计划的使用

Sql写法：

explain tbl_name

explain [extended] select select_options

前者可以得出一个表的字段结构等等，后者主要是给出相关的一些索引信息，而今天要讲述的重点是后者。

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;width:66.2pt;"> id </td> 
 <td style="border-color:#c0c0c0;width:480.55pt;"> SELECT识别符。这是SELECT的查询序列号 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:66.2pt;"> select_type </td> 
 <td style="width:480.55pt;"> SELECT类型,可以为以下任何一种: 
 <ul> 
 <li>SIMPLE:简单SELECT(不使用UNION或子查询)</li> 
 <li>PRIMARY:最外面的SELECT</li> 
 <li>UNION:UNION中的第二个或后面的SELECT语句</li> 
 <li>DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询</li> 
 <li>UNION RESULT:UNION 的结果</li> 
 <li>SUBQUERY:子查询中的第一个SELECT</li> 
 <li>DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询</li> 
 <li>DERIVED:导出表的SELECT(FROM子句的子查询)</li> 
 </ul></td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:66.2pt;"> table </td> 
 <td style="width:480.55pt;"> 输出的行所引用的表 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:66.2pt;"> type </td> 
 <td style="width:480.55pt;"> 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序: 
 <ul> 
 <li>system:表仅有一行(=系统表)。这是const联接类型的一个特例。</li> 
 <li>const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!</li> 
 <li>eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。</li> 
 <li>ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。</li> 
 <li>ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。</li> 
 <li>index_merge:该联接类型表示使用了索引合并优化方法。</li> 
 <li>unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。</li> 
 <li>index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)</li> 
 <li>range:只检索给定范围的行,使用一个索引来选择行。</li> 
 <li>index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。</li> 
 <li>ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。对于较大的表必须避免</li> 
 </ul></td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:66.2pt;"> possible_keys </td> 
 <td style="width:480.55pt;"> 指出MySQL能使用哪个索引在该表中找到行 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:66.2pt;"> key </td> 
 <td style="width:480.55pt;"> 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:66.2pt;"> key_len </td> 
 <td style="width:480.55pt;"> 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:66.2pt;"> ref </td> 
 <td style="width:480.55pt;"> 显示使用哪个列或常数与key一起从表中选择行。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:66.2pt;"> rows </td> 
 <td style="width:480.55pt;"> 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:66.2pt;"> filtered </td> 
 <td style="width:480.55pt;"> 显示了通过条件过滤出的行数的百分比估计值。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:66.2pt;"> Extra </td> 
 <td style="width:480.55pt;"> 该列包含MySQL解决查询的详细信息 
 <ul> 
 <li>Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。</li> 
 <li>Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。</li> 
 <li>range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。</li> 
 <li>Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。</li> 
 <li>Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。</li> 
 <li>Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。</li> 
 <li>Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。</li> 
 <li>Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。</li> 
 <li>Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。</li> 
 </ul></td> 
 </tr> 
 </tbody> 
</table>

Using filesort：如果mysql在排序的时候没有使用到索引那么就会输出using filesort。sql中order by/group by都会要求数据的排序  
Using Temporary：数据需要排序，就要先取出来，取出来放到磁盘临时文件或者内存中。MySQL根据sort\_buffer\_size来判断是否使用磁盘临时文件，如果需要排序的数据能放入sort\_buffer\_size则无需使用磁盘临时文件，仅仅使用内存就可以了，此时explain只会输出using filesort 否则需要使用磁盘临时文件explain会输出using temporary;using filesort；这里需要指出：如果使用了磁盘临时文件，那么就需要磁盘IO(很慢)，肯定会拉慢sql的执行效率，需要避免。

### 可选索引有多个时，怎么选择？ ###

主键索引优先于一般索引  
选择索引基数大，扫描行数小的索引

索引基数怎么预估？扫描行数怎么预估？  
统计总页数，抽样n个页获取每个页的平均基数  
根据基数来预估扫描行数  
主键索引优先于一般索引

### 为什么需要索引重建 ###

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

这道题目，我给你的“参考答案”是：

偶尔重建非主键索引 k的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

索引下推

联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

<table> 
 <tbody> 
 <tr> 
 <td>&nbsp;</td> 
 <td> mysql&gt; select * from tuser where name like '张 %' and age=10 and ismale=1; </td> 
 </tr> 
 </tbody> 
</table>

你已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。当然，这还不错，总比全表扫描要好。

然后呢？

当然是判断其他条件是否满足。

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

下面是无索引下推，会回表四次，如下图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70][]

有索引下推，仅仅回表两次，会先把age为30和20的过滤掉，如下图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 1][]

说明：索引下推 借鉴于极客时间 mysql实战45讲。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70]: /images/20220405/ec1348ceca3048c99e7c2058dc8e96f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 1]: /images/20220405/c777941c270740babe07816fd07f63e1.png