mysql索引分享

悠悠 2023-03-04 09:20 10阅读 0赞

查询，是数据库所提供的一个重要功能，快速获取到目标数据很重要。

sql查询很慢怎么处理？

建索引！！！！

那索引是如何加快查询？

从数据结构的角度来看，大量数据中，快速查询数据的方式有哪些？

1 )数组下标定位   2）hash实现的散列表  3)树    4)跳表  .....

而mysql中主要采用了B+和hash两种方式，其中以树的方式最常用。

## btree索引的底层数据结构 -----B+树 ##

经典B+树示意图如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70][]

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针，示意图如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 1][]

B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

二叉树 二叉查找树 平衡二叉树 红黑树 B+ B-，为什么最终选择了B+树？

一般树与B树最大的区别是儿子节点只能有两个，决定了大量数据的时候深度仍然过大。在大规模数据存储的时候，一般树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。因为要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。

B+与B-对比：

B-示意图如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 2][]

（1）B+树空间利用率更高，可减少I/O次数，

B+树中所有非叶节点没有指向某个关键字具体信息的指针，仅有指向树结构的指针，所以每一个节点可以存放更多的关键字数量，即一次性读入内存所需要查找的关键字也就越多，减少了I/O操作。

（2）B+树的查询效率更加稳定，

因为B+树的每次查询过程中，都从根节点到叶子节点的某条路径。所有关键字的查询路径长度相同，导致每一次查询的效率未定。

（3）B+树中的顺序访问指针对范围查询支持更好。

## btree索引在不同引擎中的应用 ##

### MyISAM ###

使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

某表一共有三列，假设我们以Col1为主键，是一个MyISAM表的主索引（Primary key）示意：

![20181218180009156][]

可以看出MyISAM的索引文件仅仅保存数据记录的地址，主键索引结构与数据的存储是分开的。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

![format_png][]

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

### InnoDB ###

也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

下图是InnoDB主索引（同时也是数据文件）的示意图：

![format_png 1][]

可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

![20181218180009247][]

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解索引地层结构对正确使用和优化索引都非常有帮助:

1）不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

2）用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

## hash索引示意图 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 3][]

hash与btree对比：

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像BTree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

那为什么大家不都用 Hash 索引而还要使用 BTree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端。

（1）Hash 索引仅仅能满足"=","IN"和查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash 索引不能利用部分索引键查询，即不适用最左前缀原则。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描，不可使用覆盖索引。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

## 大表查询时的执行计划 ##

explain sql;

<table> 
 <tbody> 
 <tr> 
 <td> id </td> 
 <td> SELECT识别符。这是SELECT的查询序列号 </td> 
 </tr> 
 <tr> 
 <td> select_type </td> 
 <td> 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> table </td> 
 <td> 输出的行所引用的表 </td> 
 </tr> 
 <tr> 
 <td> type </td> 
 <td> 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序: 
 <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> possible_keys </td> 
 <td> 指出MySQL能使用哪个索引在该表中找到行 </td> 
 </tr> 
 <tr> 
 <td> key </td> 
 <td> 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 </td> 
 </tr> 
 <tr> 
 <td> key_len </td> 
 <td> 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。 </td> 
 </tr> 
 <tr> 
 <td> ref </td> 
 <td> 显示使用哪个列或常数与key一起从表中选择行。 </td> 
 </tr> 
 <tr> 
 <td> rows </td> 
 <td> 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 </td> 
 </tr> 
 <tr> 
 <td> filtered </td> 
 <td> 显示了通过条件过滤出的行数的百分比估计值。 </td> 
 </tr> 
 <tr> 
 <td> Extra </td> 
 <td> 该列包含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的执行效率，需要避免。

explain  
select \* from stats\_stu\_answer where student\_id = '329787affd664653ad6da41e44e97b29' order by bundle\_id;  
explain  
select \* from stats\_stu\_answer where student\_id = '329787affd664653ad6da41e44e97b29' order by project\_id;

## 索引类型与性能 ##

## 联合索引 ##

某个索引中使用多个字段，称为联合索引。

左前缀原则：如果我们创建了(a,b,c)联合索引,此时其实相当于创建了(a,b,c),(a,b),(a)三个索引,这被称为最佳左前缀;

应该将最常用、类型比较小、区分度比较高的字段放在联合索引的前面。

select \* from t where b=1 ;会走索引吗？

select \* from t where a =1 and  b>1 and c=1 和

select \* from t where a =1 and  b=2 and c>1

走的联合索引有什么区别吗？如果走的话，走的是(a,b,c),(a,b),(a)中的哪个？

假设a b c 的值均可以为 1 2 3，共计27条记录，那联合索引排序如下：

再看两条关于联合索引的sql：

explain  
select \* from stats\_stu\_answer where practice\_id = '01705ded970800163e031633005c0002';  
explain  
select project\_id,student\_id from stats\_stu\_answer where practice\_id = '01705ded970800163e031633005c0002';

覆盖索引：sql中涉及的所有字段都在某个索引中，即字段被索引全覆盖。此时仅仅通过该索引查询即可，无需再回表。

非主键索引一定会有回表操作吗？未必，如果存在针对该sql的覆盖索引则不必回表。

### innodb引擎中主键与一般索引 ###

\#lesson\_id为索引，所以下面的sql会使用哪个索引？

select \* from stats\_stu\_answer where lesson\_id = '016b9c13cd8800163e031633003a0001' and id = '180783';

1）聚集性

innodb的主键索引同时是聚集索引，用主键作为where条件查询时，可以定位主键后直接获取数据。

一般索引是非聚集索引，用非主键字段作为where条件查询时，需要在索引结构中先获取主键值，再回表，到表数据中根据主键获取具体数据。

2）唯一性

因为主键索引一定是唯一的，所以在查询过程中，如果主键命中了一条，则可以停止继续查找。但是一般的索引则可能对应多条记录，所以只有超过条件范围才会停止查找。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存；所以要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。所以唯一性带来的性能差别可忽略不计。

问题：如过字段a上单独建了索引，又有联合索引(a,b,c)那么下面的sql会走哪个索引？

select \* from t where a =1 and  b=2 and c>1;

## mysql引擎选择索引的策略 ##

sql引擎在执行sql和选择索引的时候，会整体考虑这个sql，包括select、where 、order by 、group by后面的所有列，如避免回表选择覆盖索引，如为了避免内存排序选择包含where条件和order by 列的在索引。

主键索引优先于一般索引。

如果是都是一般索引，MySQL真正执行语句之前，会通过统计信息来估算记录数，这个统计信息就是**索引的“区分度”**。InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引页的页面数，得到这个索引的区分度。一个索引上不同的值越多，这个索引的区分度越好，

当同为普通索引的时候，选择索引基数大，扫描行数小的索引

## 索引创建原则总结 ##

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

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

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

4、经常出现在Where\\order by\\group by子句中的字段，特别是大表的字段，应该建立索引,避免全表扫描和内存排序；

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 索引字段显式发生了显式函数转化

update\_time单列是索引，假设数据仅有2020年的数据。

explain

select \* from stats\_stu\_answer where MONTH(update\_time) = 4;

explain 
select \* from stats\_stu\_answer where update\_time>='2020-06-01' and update\_time<='2020-06-30';

2 索引字段数据类型不同

CREATE TABLE \`tradelog\` (  
\`id\` int(11) NOT NULL,  
\`tradeid\` int(11) DEFAULT NULL,  
\`operator\` int(11) DEFAULT NULL,  
\`t\_modified\` datetime DEFAULT NULL,  
PRIMARY KEY (\`id\`),  
KEY \`tradeid\` (\`tradeid\`),  
KEY \`t\_modified\` (\`t\_modified\`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  
  
CREATE TABLE \`trade\_detail\` (  
\`id\` int(11) NOT NULL,  
\`tradeid\` varchar(32) DEFAULT NULL,  
\`trade\_step\` int(11) DEFAULT NULL, /\* 操作步骤 \*/  
\`step\_info\` varchar(32) DEFAULT NULL, /\* 步骤信息 \*/  
PRIMARY KEY (\`id\`),  
KEY \`tradeid\` (\`tradeid\`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

explain select d.\* from tradelog l, trade\_detail d where d.tradeid=l.tradeid and [l.id][]=2;

参考：

[http://blog.codinglabs.org/articles/theory-of-mysql-index.html][http_blog.codinglabs.org_articles_theory-of-mysql-index.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70]: /images/20230208/df46543f06d447aa971d705653c8313e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 1]: /images/20230208/4f5267231e164f1d8707c7c2d038b904.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 2]: /images/20230208/2caa60ca5de1434dbaea2c3e8d6c4d04.png
[20181218180009156]: /images/20230208/0c2d2f6049bc4e2282d96d1ce6ba7e56.png
[format_png]: /images/20230208/d7df6483ba3448a8a941b9f1f9243f76.png
[format_png 1]: /images/20230208/2e2191d8a58542ac8d7928e51639befc.png
[20181218180009247]: /images/20230208/8b5dfa3fbce24bb7a9102e3ea37c3878.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70 3]: /images/20230208/c3456eaa0a754208a80ca8e51dec9b32.png
[l.id]: http://l.id
[http_blog.codinglabs.org_articles_theory-of-mysql-index.html]: http://blog.codinglabs.org/articles/theory-of-mysql-index.html