MySQL中级优化教程（三）——索引的建立

本是古典何须时尚 2022-01-14 02:29 285阅读 0赞

*  您可以到这里下载本篇博文所使用的数据库以及优化工具mysqldumpslow和pt-query-digest：
 *  [https://blog.csdn.net/IT\_TIfarmer/article/details/92224646][https_blog.csdn.net_IT_TIfarmer_article_details_92224646]
 *  关于explain语句的结果参数详解也可以参考如上博文。

> ###  前言： ###

如果一个索引包含（或覆盖）所有需要查询的字段的值，则称该索引为“覆盖索引”，对于那些经常出现，且只需要查找部分数据的select语句，我们就可以建立覆盖索引对其进行优化。

> ### 索引小知识： ###
> 
> 唯一索引：  
> 唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。
> 
> 主键索引：  
> 数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
> 
> 聚集索引：  
> 在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引和非聚集索引的区别，如字典默认按字母顺序排序，读者如知道某个字的读音可根据字母顺序快速定位。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字，则需按字典前面的索引，举例按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。
> 
> 索引列：  
> 可以基于数据库表中的单列或多列创建索引。多列索引可以区分其中一列可能有相同值的行。如果经常同时搜索两列或多列或按两列或多列排序时，索引也很有帮助。例如，如果经常在同一查询中为姓和名两列设置判据，那么在这两列上创建多列索引将很有意义。

###  如何选择合适的列建立索引？  ###

1.   在where从句，group by从句，order by从句， on从句中出现的列
2.  索引字段越小越好  
    解释：我们所建立的索引在数据库中是以页的方式进行存取，每一页的内存是固定的，建立索引列的字段越小，每一页所能存取的索引就越多，查找索引时扫描的页数就越小，IO也就越小。
3.  离散度大的列放到联合索引的前面

针对于第三条，我们做一下详细的解释：

*  离散度：一组数据相对于给定中心的偏离程度，数值越大数据越分散，越“平均”。
 *  联合索引：如果经常要用到多个字段的多条件查询，可以考虑建立联合索引，一般是除第一个字段外的其它字段不经常用于条件筛选情况，比如说a,b 两个字段，如果经常用a条件或者a+b条件去查询，而很少单独用b条件查询，那么可以建立a,b的联合索引。如果a和b都要分别经常独立的被用作查询条件，那还是建立多个单列索引。

举个栗子：

select * from payment where staff_id = 2 and customer_id = 584;

是index(staff\_id, customer\_id)好？ 还是index(customer\_id, staff\_id)好？  
由于customer\_id的离散度更大，可选择性更高，所以应该使用index(customer\_id, staff\_id)，那么如何去判断离散度呢？

payment表结构如下：

我们一般使用查看唯一值的方法来判断列的离散程度，唯一值越多，离散程度越高：

mysql> select count(distinct customer_id), count(distinct staff_id) from payment;
    +-----------------------------+--------------------------+
    | count(distinct customer_id) | count(distinct staff_id) |
    +-----------------------------+--------------------------+
    |                         599 |                        2 |
    +-----------------------------+--------------------------+

显然，customer\_id的值更多，离散度更大，所以把它作为联合索引的第一列：

create index cust_staff_index on payment(customer_id,staff_id);

[https_blog.csdn.net_IT_TIfarmer_article_details_92224646]: https://blog.csdn.net/IT_TIfarmer/article/details/92224646