Mysql一之索引的使用及索引的优缺点

素颜马尾好姑娘i 2022-09-04 01:58 173阅读 0赞

关于索引的分类上篇**[<Mysql一之索引概述>][Mysql]**已有概述这里就不做赘述了。该篇主要从四个方面去阐述，什么情况下使用索引、索引使用的注意事项、什么情况会导致索引失效、以及索引的优缺点；

### 一、什么情况下使用索引 ###

大概总结有以下几点吧，但不仅限于以下可能还有其他情况，也欢迎各位评论区补充。

1.  在经常需要搜索查询的列上创建索引，可以提升搜索查询的速度；
2.  在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构(例如Mysql的主键自增默认主键索引)；
3.  在经常用在连接的列上，这些列主要是一些外键或者主要的关联条件，也可以加快连接的速度；
4.  在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的，这样查询可以利用索引的排序，加快排序查询的时间；
5.  在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。当增加索引时，会提高检索性能，但是会降低修改更新的性能；
6.  唯一性很差的字段不合适做索引，如性别、姓名以及一些大字段；
7.  更新频繁的字段不适合，耗时且影响性能；

### 二、使用索引的注意事项 ###

1.  索引不会包含有NULL值的列：只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。应该用0、一个特殊的值或者一个空串代替空值(当然这些默认值一定要和业务字段区分好，不然将会是一个大坑)；
2.  复合索引：比如有一条语句是这样的：select \* from lgh\_user where area = 'beijing' and age = 22; 如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area,age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减；
3.  使用短索引：对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作；
4.  排序的索引问题：mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，因此尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
5.  like语句操作：一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like '%aaa%'  不会使用索引而like 'aaa%' 可以使用索引。
6.  不要在列上进行运算：select \* from lgh\_user where age - 1 >0 ；
7.  不使用NOT IN操作：NOT IN操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替；

其实注意事项聊完我感觉那些情况下会致使索引失效也就一目了然，但是针对咱们这些同学我感觉先不说标题党，如果一看标题没有致使索引失效的情况就放弃PASS了。所以我就再啰嗦一遍。

### 三、索引失效的情况 ###

如果是同样的sql如果在之前能够使用到索引，那么现在使用不到索引，大概存在以下几种主要情况:

*  随着表的增长，where条件出来的数据太多，大于15%，使得索引失效（会导致CBO计算走索引花费大于走全表）;
 *  统计信息失效 需要重新搜集统计信息;
 *  索引本身失效 需要重建索引;

具体情况：  
1、单独引用复合索引里非第一位置的索引列；  
示例：假如有INDEX(a,b,c)， 当条件为a或a,b或a,b,c时都可以使用索引， 但是当条件为b,c时将不会使用索引(不过这种情况貌似被sql执行器给优化了)。 复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。  
2、对索引列运算，运算包括（+、-、\*、/、！、<>、%、like’%\_’（%放在前面）、or、in、exist等），导致索引失效；  
错误的例子：select \* from test where id-1=9;  
正确的例子：select \* from test where id=10;  
注意：mysql sql 中如果使用了 not in ， not exists ， （<> 不等于 ！=） 这些不走  
< 小于 > 大于 <= >= 这个根据实际查询数据来判断，如果全盘扫描速度比索引速度要快则不走索引 。  
3、对索引应用内部函数，这种情况下应该建立基于函数的索引；  
示例：select \* from template t where ROUND(t.logicdb\_id) = 1 此时应该建ROUND(t.logicdb\_id)为索引。  
4、类型错误，如字段类型为varchar，where条件用number；  
示例：template\_id字段是varchar类型，  
错误写法：select \* from template t where t.template\_id = 1 ；  
正确写法：select \* from template t where t.template\_id = ‘1’ ；  
5、如果MySQL预计使用全表扫描要比使用索引快(数据量百万级上下)，则不使用索引；  
6、索引列没有限制 not null，索引不存储空值，如果不限制索引列是not null，oracle会认为索引列有可能存在空值，所以不会按照索引计算；

### 四、索引优缺点 ###

**优点：**

1.   通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
2.   可以大大加快数据的检索速度，这也是创建索引的最主要的原因；
3.   可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；
4.   在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
5.   通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能；
6.  覆盖索引的好处：如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的；

**缺点：**

1.  创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
2.  索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变；
3.  当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，一旦一个数据改变，并且改变的列比较多，可能会引起好几个索引跟着改变，这样就降低了数据的维护速度。
4.  每个索引都有统计信息，索引越多统计信息越多，过多索引会导致优化器优化过程需要评估的组合增多。创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

**如有披露或问题欢迎留言或者入群探讨**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDcwNzMz_size_16_color_FFFFFF_t_70][]

[Mysql]: https://lghszz.blog.csdn.net/article/details/109326870
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDcwNzMz_size_16_color_FFFFFF_t_70]: /images/20220829/ec9b221585044637b9e815ad61b6cee7.png