MySQL适合创建索引的11种情况

逃离我推掉我的手 2023-10-13 17:07 70阅读 0赞

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成影响

**限制索引的数目**

索引数目不是越多越好，建议单表的索引不超过6个，原因：

1.  每个索引都会占用磁盘空间，索引越多，需要的磁盘空间也就越大
2.  索引会影响INSERT、DELETE、UPDATE等语句的姓名，因为表中数据更改的同时，索引也会进行调整和更新，会造成负担
3.  优化器在选择如下优化查询时，会根据统一信息，对每个可以用到的索引来进行选择，以生成一个最好的执行计划，如果同时有很多索引都可以用于查询，会增加MySQL优化器生成执行计划的时间，降低查询性能。

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

适合创建索引的情况如下：

**1.** **字段的数值有唯一性的限制**

索引本身可以起到约束的作用，比如唯一索引、主键索引都可以起到唯一性约束的，因此在我们的数据表中，如果`某个字段是唯一的`，就可以直接`创建唯一性索引`，或者`主键索引`。这样可以更快速地通过该索引来确定某条记录。

>  *  业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
> 
> 说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的。

**2.** **频繁作为** **WHERE** **查询条件的字段**

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

**3.** **经常** **GROUP BY** **和** **ORDER BY** **的列**

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要`对分组或者排序的字段进行索引`。如果待排序的列有多个，那么可以在这些列上建立`组合索引`。

**4. UPDATE、DELETE** **的** **WHERE** **条件列**

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。**如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。**

**5.DISTINCT** **字段需要创建索引**

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

**原因：**字段在索引中的键值是按降序或升序排序的，此时对字段去重是很快的，直接按照顺序去重下去，相邻数据之间存在重复值则去重，不存在则不需要去重。

**6.** **多表** **JOIN** **连接操作时，创建索引注意事项**

首先，`连接表的数量尽量不要超过 3 张`，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次，`对 WHERE 条件创建索引`，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后，`对用于连接的字段创建索引`，并且该字段在多张表中的`类型必须一致`。

原因：

*  用于连接的字段如果类型不一致，那么MySQL会隐式转换数据类型（自动使用函数），字段数据类型的转换会导致索引失效。

> 如：
> 
>  *  table1.userId = table2.userId;
>  *  \--table1.userId：为VARCHAR类型
>  *  \--table2.userId; 为INT类型
> 
> 在连接时，会自动把userId转换为INT，实现等值连接

**7.** **使用列的类型小的创建索引**

我们这里所说的`类型大小`指的就是该类型表示的数据范围的大小。

*  数据类型越小，在查询时进行的比较操作越快
 *  数据类型越小，索引占用的存储空间就越少，**在一个数据页内就可以`放下更多的记录`，从而减少磁盘`I/O`带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。**

这个建议对于表的`主键来说更加适用`，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

**8.** **使用字符串前缀创建索引**

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有下面2个问题：

*  B+树索引中的记录需要把该列的完整字符串存储起来，更费时，而且字符串越长，在索引中占用的存储空间越大。
 *  如果B+树索引中索引存储的字符串很长，在字符串比较时会占用更多的时间。

我们可以通过截取字段的前面一部分内容建立索引，这个就叫**前缀索引**。这样在查找记录时虽然不能精准定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。即节约空间，又减少了字符串的比较时间，还大体能解决排序问题。

例如：创建一张商品表，因为地址字段较长，在地址字段上建立前缀索引

CREATE TABLE SHOP(address varchar(120) not null);
    
    --给字段address建立前缀索引
    alter table shop index(address(12))

那现存的问题是：字符串需要截取多少才合适呢？截取多了，达不到节省索引存储空间的目的；截取少了，重复内容太多，字段的散列度（选择性）会降低。**怎么计算不同的长度的选择性呢？**

先看一下字段在全部数据中的选择度：

select  count(distinct address)/count(*)  from shop;

通过不同长度去计算，与全表的选择性对比：

公式：

*  这里的left是字符串中的函数，表示取该字符串字段前面的多少字符

count(distinct left(列名, 索引长度))/count(*)

例如：

select 
        count(distinct left(address,10)) / count(*)  as sub10    --截取前10个字符的选择度
    
        ,count(distinct left(address,15)) / count(*)  as sub15    --截取前15个字符的选择度
    
        ,count(distinct left(address,20)) / count(*)  as sub20    --截取前20个字符的选择度
    from shop;

*  计算的结果越接近1越好，在测试过程中，若发现所有设置为30后，再往后计算的结果变动很小很小，那么可以把30设置为最佳前缀索引的长度

索引列前缀对排序的影响：

*  前缀索引不包含完整的字符串信息，因此在索引排序时，无法保证记录先后顺序是否正确，因此 前缀索引的方式无法支持使用索引排序，只能使用文件索引

**拓展：Alibaba《Java开发手册》**

【`强制`】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会`高达 90% 以上`。

**9.** **区分度高(散列性高)的列适合作为索引**

`列的基数`指的是某一列中不重复数据的**个数**，比方说某个列包含值`2,5,8,2,5,8,2,5,8`，虽然有`9`条记录，但该列的基数却是`3`。也就是说，**在记录行数一定的情况下，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中**。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小的列建立索引效果可能不好。

可以使用公式**`select count(distinct a)/count(*) from t1`**计算区分度，越接近1越好，一般超过**`33%`**就算是比较高效的索引了。

**拓展：联合索引把区分度高（散列性高）的列放在前面。**

**10.** **使用最频繁的列放到联合索引的左侧**

*  **确保条件能使用到联合索引**
 *  **下面这种联合索引，order by优先于group by执行，因此order by后面的列需要在联合索引的前面，group b后面的列，在联合索引的后面**

SELECT * FROM STUDENT
    ORDER BY SCORE
    GROUP BY NAME;
    --需要创建SCORE和NAME的联合索引：idx_score_name(score esc,name esc)
    --esc默认不需要添加，当order by score desc时，需要改为idx_score_name(score desc,name esc)

**11.** **在多个字段都要创建索引的情况下，联合索引优于单值索引**