索引使用及优化问题

一、索引的概述

1. 什么是索引？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。通俗一点地说，如果把数据库比作字典，要去查找一个数据（字典里面的字），通过目录就很方便快速定位，而索引就相当于字典的目录。

2. 索引的优缺点
优点：
快速访问数据表中的特定信息，提高检索速度；
创建唯一性索引，保证数据库表中每一行数据的唯一性；
加速表和表之间的连接。
缺点：
创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；
索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

二、索引创建

此处可参考：MySQL索引的创建与使用

1. 建表时创建索引
CREATE TABLE 表名(
字段名 数据类型[完整性约束]，
…
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]
);

说明：
用[ ]括起来的都是可以写可以不写的。 | 表示或者
UNIQUE:可选。表示索引为唯一性索引。
FULLTEXT:可选。表示索引为全文索引。
SPATIAL:可选。表示索引为空间索引。
INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是 一样的。
索引名:可选。给创建的索引取一个新名称。
字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。
长度:可选。指索引的长度，必须是字符串类型才可以使用。
ASC:可选。表示升序排列。
DESC:可选。表示降序排列。
注：索引方法默认使用BTREE。

2. 建表后创建
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法];
或者
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名(字段名) [USING 索引方法]；

三、索引的基本原理

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。
索引的原理很简单，就是把无序的数据变成有序的查询
1、把创建了索引的列的内容进行排序
2、对排序结果生成倒排表
3、在倒排表内容上拼上数据地址链
4、在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

四、索引的数据结构（B树、Hash）

1. B树索引
2. Hash索引

五、创建索引的原则

索引虽好，但也不是无限制的使用，最好符合一下几个原则
1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2）较频繁作为查询条件的字段才去创建索引
3）更新频繁字段不适合创建索引
4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
6）定义有外键的数据列一定要建立索引。
7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
8）对于定义为text、image和bit的数据类型的列不要建立索引。

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件，所以当我们对数据的增加，修改，删除都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO，会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。
所以我们想要删除百万数据的时候
1）可以先删除索引（此时大概耗时三分多钟）
2）然后删除其中无用数据（此过程需要不到两分钟）
3）删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

六、索引优化的语句

对于这样一张表

索引优化建议

1.对索引列进行计算
将表test_index中id大于100的数据记录中的age和sname查找出来。

2.对索引列进行拼接
将表test_index中sname为“张三”、addr为“重庆”的记录中的id和age查找出来。

3.在索引列上is null或is not null的使用
将表test_index中id大于等于0的记录中的age查找出来。

4.在索引列上or的使用
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
将表test_index中id等于101或102的记录中的age和sname查找出来。

5.尽可能避免索引列在like的首字符使用通配符
不要在like后面的第一个字符使用%
将表test_index中sname匹配“张三”的记录中的id和age查找出来。

6.复合索引的使用
如果我们建立的索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引。
在表test_index上新建了如下索引：

以上索引idx4相当于建立了index(id)、index(id,sname)、index(id,sname,addr) 这3个索引。在SQL语句的where条件中单独使用name或addr时不会使用到该索引，必须使用id时才会使用到该索引。

七、存在索引但是不被使用的情况：

1. where 子句里对索引列上有数学运算，用不上索引
2. 如果条件中有or，即使其中有部分条件带索引也不会使用(这也是为什么尽量少用or的原因)
3. like查询是以%开头
4. 存在索引列的数据类型隐形转换，则用不上索引，比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5. 对于多列索引，不是使用的第一部分，则不会使用索引

在我们编写的SQL语句中，不正确地使用索引列可能会导致索引不被使用，而进行全表扫描，极大地降低了数据库的性能。因此，学习正确的索引的使用方法实在是很有必要的。

参考资料：
https://blog.csdn.net/oHeiZhiShi123/article/details/79525867
https://www.jb51.net/article/128327.htm