MYSQL索引知识汇总【持续更新】

本文资源来源于网络和个人实际项目经验，如有错误改进点请指正

1、索引是什么

MySQL索引的基础，索引简单说类似于书籍的目录，或就比如汉字字典，想找到你想要的文字解释，最快的方法就是查目录，就能最快的定位你查询文字的定位页码，当然和字典查询一样，不仅有拼音查询还有笔画查询，存储引擎MySQL的索引也是如此，采用类似的方式进行索引数据查询，先去索引当中找到对应的值，然后根据匹配的索引找到对应的数据行，也由有多种类型的索引帮助定位查询结果。

2、索引对服务器性能的影响

1、大大减少服务器需要扫描的数据量
2、帮助服务器避免排序和临时表
3、将随机的I/O变成顺序的I/O
4、大大提升提高查询速度select
5、降低写【添加、更新、删除】的速度【因为创建的数据的时候也会创建字段的索引】，占用磁盘【索引也是一种数据

3、索引使用的场景判断

对于非常小的表，大部分情况下全表扫描效率会更高，不用建索引
对于中到大型表，索引非常有效
对于特大型表〔上千万或者上亿数据量〕，建立和使用索引的代价随之增大，可以使用【分区技术】来解决

4、索引的类型

索引的类型有很多种，都是实现在存储引擎层面
1、普通索引：最基本的索引，没有任何约束
2、唯一索引：与普通索引类似，但具有唯一性质约束
3、主键索引：特殊的唯一索引，但是不能为空
4、组合索引：包含2个或2个以上列组合起来创建索引。要注意位置顺序需要和索引建立一致
5、外键索引：只有innodb才能建立，保证数据的一致性，完整性，实现级联操作【基本不用，业务逻辑就可以关联】
6、全文索引：MySQL自带的全文索引只能用于myisam【mysql5.6后innodb也支持了】，并且只针对英文全文检索，中文实现难度大。类型限制为’char’、‘varchar’、‘text’【基本不用，适用太差，可以使用全文检索引擎斯芬克斯】

5、唯一索引和主键索引的区别

1、一张表只能有一个主键索引，但可以存在多个唯一索引
2、主键索引是唯一索引，但唯一索引不是主键索引
3、主键可以和外键构成完整性约束，防止数据不一致

6、MySQL索引创建的原则

1、最适合索引的列是出现在where字句中的列，或者链接子句中的列而不是select关键字后的列【where后面的字段、或者on后面的字段】
2、索引列的基数越大，索引的效果越好【就是越是利于索引的列越多，检索速度越快】
3、对字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间【title字段就是 ADD INDEX table_title (title(4)); 相当于只对字符前面进行索引建立，节省空间】
4、根据情况创建复合索引，复合索引可以提高查询效率【类似查询章、节，多一个检索条件，速度更快】
5、避免过多的建立索引，索引额外的站用磁盘空间，降低写操作的效率
6、主键尽可能的选择较短的数据类型【int/bigint】，可以有效减少索引的磁盘占用，提高查询效率

7、不使用索引的特殊情况【2018-09-24更新】

1、组合索引遵循前缀原则【key[a,b,c] 最左前缀原则】，如a,b–abc–ac都可以使用索引，b–bc不使用索引，并且需要注意索引顺序？。
2、组合索引使用查询中某个列有范围查询，则其右边的所有列都无法使用查询（截断），如组合索引key[a,b,c] , a>1 and b=1 and c=1 将不使用任何组合索引，a=1 and b>1 and c=1 将使用ab组合索引 c被截断（范围 > 、< 、like、>=、<=）。
3、like查询,%不能在前面，否则不能用索引，【要解决，可以搭建一个全文检索引擎斯芬克斯】
4、column is null 其实是可以使用到索引的
5、如果mysql会自动估计使用索引比全表扫描慢，会弃用索引【MySQL自己判断效率】
6、如果where条件用or 前后都必须有索引，否则不能起效【or前后索引强制要求】
7、列类型是字符串，查询时一定要加引号，否则类型不对索引会失效【类型不对会失效】