mysql索引

一、什么是索引

​ 索引，一种能大大提高数据库查询效率的数据结构。

二、为什么要使用索引

​ 当数据库的数据达到一定的数量级后（比如100万条），mysql性能回开始下降，查询会变得非常的慢。而在当今数据为王的大数据时代，数据量远远不止100万条。这时，索引就派上用场了。

三、索引的具体数据结构类型

​ Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

1. FULLTEXT
   即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH
   由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

3. BTREE
   BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

4. RTREE
   RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

以下内容都是基于BTREE索引

四、索引类型

普通索引：仅加速查询

唯一索引：加速查询 + 列值唯一（可以有null）

主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

组合索引（复合索引）：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

全文索引：对文本的内容进行分词，进行搜索

ps.索引合并，使用多个单列索引组合搜索

覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

五、如何避免索引失效

1、最佳左前缀匹配原则 ：即使用复合索引时，我们where字句的第一个判断条件应是复合索引最左边的字段。

2、复合索引的顺序尽量和where字句查询的顺序一致，mysql会从不相同的那个开始不适用索引的其他字段，这样索引查找的精度就小了。

3、where字句中范围条件右边的索引会失效。

4、尽量索引覆盖（索引覆盖，就是查询的字段全部包含在了where字句的查询条件中，具体理解索引覆盖，可以参考这篇博客 https://www.cnblogs.com/gered/p/12210055.html#_label1 ），避免了回表操作。由于一般select * 表示查询全部字段，我们一般不会说将表中所有字段都建立索引，应为那样的话不仅增大空间开销，还牵涉到查询效率的问题。比如说，我们为每个字段建立单值索引，这样不可避免的就会触发大量的回表查询。或者说我们建立覆盖索引，我们又必须满足1,2两个原则才能充分利用覆盖索引的优点，然而实际我们一般只查询其中的几个字段。因此，实际的开发中，应避免使用select *，要查多少个就实际写多少个。

5、mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描。这其实很好理解，你去索引中找一个不等于的值，索引无法发挥它有序的特性。

6、is not null 也无法使用索引,但是is null是可以使用索引的。is not null类似第5点，is null应该是在索引的最前面后者最后面。

7、like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。

8、字符串不加单引号索引失效。

9、少用or,用它来连接时会索引失效。

10、尽量全值匹配，充分利用复合索引。