MYSQL索引详解

本文优化并补充了大佬的文章：https://www.cnblogs.com/chenshishuo/p/5030029.html

索引的定义（索引别称index，key，键）

在关系数据库中，索引是对表中一列或多列的值进行排序的一种存储结构，它是表中一列或多列的值的集合，而且其中包含了对应表中记录的引用指针。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

要注意的是，索引也是表的组成部分，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立索引。

举个例子：首先，先假设有一张表，表有10W个记录，其中有一条记录我们已知a='1'，如果想要拿到对应记录的话，需要的sql语句是 SELECT * FROM xxx WHERE a='1'.

一般情况下，对于查询语句，在没有建立索引的时候，mysql会进行全表扫描，而且不扫描完10W个记录不会停止，如果我在nickname上建立索引，那么mysql相当于只扫描nickname这一列即可，而且因为这一列已排好序，找到对应结果或结果集可以直接返回。

mysql的索引分为单列索引(全文索引，主键索引，唯一索引，普通索引)和组合索引。
单列索引:一个索引只包含一个列，一个表可以有多个单列索引。
组合索引:一个组合索引包含两个或两个以上的列，

(一)索引的创建

1.单列索引

1-1)    普通索引（这个是最基本的索引）

建表时：INDEX IndexName(`字段名`(length)) 

建表后：CREATE INDEX IndexName ON `TableName`(`字段名`(length)) 

或ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length)

注意：如果字段数据是CHAR，VARCHAR类型，可以指定length，其值小于字段的实际长度，如果是BLOB和TEXT类型就必须指定length。

这个length的用处是什么?

有时候需要在长文本字段上建立索引，但这种索引会增加索引的存储空间以及降低索引的效率，这时就可以用到length，创建索引时用到length的索引，我们叫做前缀索引，前缀索引是选择字段数据的前n个字符作为索引，这样可以大大节约索引空间，从而提高索引效率。

此处展示的语句用于创建一个索引，索引使用字段数据的前10个字符。
CREATE INDEX part_of_name ON customer (name(10));

使用字段数据的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。

前缀索引是一种能使索引更小，更快的有效办法，但是MySql无法使用前缀索引做ORDER BY 和 GROUP BY以及使用前缀索引做覆盖扫描。

这里又引出了一个新概念，覆盖扫描！

如果一个索引（如：组合索引）中包含所有要查询的字段的值，那么就称之为覆盖索引，如：

SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;
因为要查询的字段（user_name, city, age）都包含在组合索引的索引列中，所以就使用了覆盖索引查询，查看是否使用了覆盖索引可以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引，覆盖索引可以极大的提高访问性能。

1-2)    唯一索引，要求字段所有的值是唯一的，这一点和主键索引一样，但是允许有空值。

建表时：UNIQUE INDEX IndexName(`字段名`(length)) 

建表后：CREATE UNIQUE  INDEX IndexName ON `TableName`(`字段名`(length)) 

或ALTER TABLE TableName ADD UNIQUE  INDEX IndexName(`字段名`(length)）

1-3)    主键索引，不允许有空值

一般在建表的时候自动创建，主键一般会设为 int 而且是 AUTO_INCREMENT自增类型的

1-4)全文索引
假设字段的数据类型是长文本，文本字段上(text等)建立了普通索引，我们需要查找关键字的话，那么其条件只能是where column like '%xxxx%' ，但是，这样做就会让索引失效，这时就需要全文索引了。

建表时：FULLTEXT INDEX IndexName(`字段名`(length)) 

建表后：CREATE FULLTEXT  INDEX IndexName ON `TableName`(`字段名`(length)) 

或ALTER TABLE TableName ADD FULLTEXT  INDEX IndexName(`字段名`(length)）

使用：
SELECT * FROM TableName
WHERE MATCH(column1， column2) AGAINST(‘xxx′， ‘sss′， ‘ddd′)
这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。

下面我们来举个例子：

假设有一个书籍表，结构如下，文章内容字段的数据类型是text

文章id	文章标题	文章内容
1	超级塞亚人	我是超级塞亚人我喜欢吃苹果，我不是天朝的人，也不是地球人
2	天朝大国	我大天朝威武，我大天朝13亿人，我大天朝
3	我喜欢游泳	游泳有很多好方法
4	动画片	我儿子喜欢看动画片，尤其是七龙珠，因为里面有塞亚人，而且塞亚人喜欢吃苹果,他们不是地球人
5	运动	我喜欢运动，喜欢跑步，喜欢游泳，喜欢健身，喜欢xxoo
6	打炮	我是一个二战的老兵，这是我的回忆录，我最幸福的时光就是在天朝吃着苹果打炮
7	..........	..........
8	..........	..........
9	..........	..........

我想在茫茫多书籍的内容里搜索关键词，如果用%xxx%搜索，那效率就太低了。

我们在文章内容字段上建立全文索引，下面是索引文件

关键词	文章id（引用指针）
塞亚人	1,4
苹果	1,4,6
天朝	1,2,6
地球	1,4
游泳	3,5
七龙珠	4
喜欢	1,4,5,6

那么当我想搜索  “塞亚人”的时候，这个索引文件直接告诉我在文章id为1和4的文章里有这个词。

可是这些关键词是如何提取出来的呢？这就是要提到一个新概念，“分词”！分词就是提取关键词，但是MYSQL的FULLTEXT对分词不够智能，对中文也不是很支持，所以我们一般不用全文索引。取而代之的是：

coreseek=sphinx+mmesg 这个程序就可以解决这个问题的啦。

sphinx就是索引程序。

mmseg就是分词程序。

国内有人修改了sphinx源码，内建和mmseg配合，整合到一起就是coreseek啦（中文版sphinx）！

2.组合索引

假设字段a，b都有索引，我们的查询条件是a=1，b=2查询过程是mysql会先挑选出符合a=1的结果集，再在这些结果集中挑选b=2的结果集，但是mysql并不会在查询a，b时都用到索引，只会用其中一个，这和我们的预期不一样，所以，我们要使用组合索引

建表时：INDEX IndexName(`字段名`(length)，`字段名`(length)，........) 

建表后：CREATE INDEX IndexName ON `TableName`(`字段名`(length)，`字段名`(length)，........) 

或ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length)，`字段名`(length)，........) 

(二)索引的删除

DORP INDEX IndexName ON `TableName`

（三）索引失效的情况　

　1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

　　要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

　　2.使用查询的时候遵循mysql组合索引的"最左前缀"规则，假设现在有组合索引（a，b，c），查询语句就只能是a=1或a=1

and b=1或a=1 and b=1 and c=1。这里有两点需要注意①a=1 and b=1和b=1 and a=1一样，没有区别，都会使用索引②组合索引（a，b，c）的最左前缀是a；组合索引（c，b，a）的最左前缀是c，最左前缀和表字段顺序无关

在组合索引中，如果where查询条件中某个列使用了范围查询（不管%在哪），则其右边的所有列都无法使用索引优化查询

　　3.like查询以%开头

　　4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

　　5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

        6.索引列不能是表达式的一部分，也不能作为函数的参数，否则无法使用索引查询。下面是例子：

1	SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');