MySql-索引整理

索引是什么，索引分类，索引操作

索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。索引也需要占据磁盘空间，当每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

---

索引分类：
普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
组合索引：多列值组成一个索引，专门用于组合搜索，效率大于索引合并
ps.索引合并，使用多个单列索引组合搜索
全文索引：对文本的内容进行分词，进行搜索
覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

---

如何创建索引,删除和查看索引：

#普通索引
CREATE INDEX index_name ON table(colName) ;
ALTER TABLE table_name ADD INDEX index_name (colName) ;

#唯一索引
CREATE UNIQUE INDEX index_name ON table(colName) ;
alter table table_name add unique index index_name(colName);

#全文索引
CREATE FULLTEXT INDEX index_name ON table(colName)) ;
alter table table_name add fulltext index_name(colName)

#组合索引
ALTER TABLE table_name  ADD INDEX index_name (colName1,colName2) ;

#删除索引
DROP INDEX index_name ON table table_name;

#查看索引
SHOW INDEX FROM table_name;

索引原理

索引是在存储引擎中实现的，所以不同的存储引擎，会使用不同的索引。
MyISAM和InnoDB存储引擎，只支持B+树索引， 也就是说默认使用B+树，不能够更换。
这里就不展开B 树和 B+ 树的篇幅了，简单来说就是 B+ 树只在叶子节点（最下一层的节点）上存储数据，而B 树则非叶子节点和叶子节点上都存储数据；另外 B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的。关于B树和B+树可以前往数据结构示例查看。
从索引和数据文件是否分开，索引又可以分为聚集索引和非聚集索引（也叫聚簇索引）
InnoDB就是的索引聚集索引，如图，叶子节点存储了ibd具体的数据：

假如执行的sql是 select id,name from student where id=15 , 这时查询索引已经查询到了所需要的的数据，这叫覆盖索引，所以要多利用组合索引，容易形成覆盖索引

---

MyISAM的索引是非聚集索引，叶子节点存储了指向具体的数据的地址：

假如查询的sql是 select address from student where name=‘Bob’，这时候通过索引查询到了这列数据（的物理地址），但是索引中没有address，就需要通过这个物理地址再找一次，这个叫回表

使用索引的场景

索引这么牛逼，什么时候应该使用索引？

1、主键自动建立唯一索引
2、频繁作为查询条件的字段应该创建索引
3、多表关联查询中，关联字段应该创建索引 on 两边都要创建索引
4、查询中排序的字段，应该创建索引 （B + tree 有顺序）
5、统计或者分组字段，应该创建索引

当然索引也不是乱建的，它一会占用空间，二是进行增删改数据是还要更新索引，以下情况不适合建索引

1、表记录太少 索引是要有存储的开销
2、频繁更新 索引要维护
3、查询字段使用频率不高

为什么使用组合索引
由多个字段组成的索引 使用顺序就是创建的顺序，如
ALTER TABLE 'table_name' ADD INDEX index_name(col1,col2,col3）
在一颗索引树上由多个字段，这样不仅效率高、省空间、还容易形成覆盖索引。但是使用不当会导致索引失效，它遵循最左前缀原则。大体来说就是

• 1.使用 lile ‘常量%’ ，不要把%放前面，否则会索引失效
• 2.从左向右匹配直到遇到范围查询 如 > < between 索引失效

索引失效

查看执行计划

我们使用 explain 执行的sql可查看sql执行计划：

解释一下几个重要的属性

id

id值越大，优先执行

select_type

主要用于区别普通查询、联合查询(union、union all)、子查询等复杂查询。

type

依次从好到差如下，最少要索引使用到range级别，如果你的sql explain type 在range之后，就要想办法优化你的sql了

system > const > eq_ref > ref > fulltext > ref_or_null >
unique_subquery > index_subquery > range > index_merge > index > ALL

列举几个重要的：

• const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
• eq_ref：此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 ‘=’, 查询效率较高
• ref：针对非唯一性索引，使用等值（=）查询非主键。或者是使用了最左前缀规则索引的查询
• range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中
• index ：条件是出现在索引树中的节点的。可能没有完全匹配索引。
  索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
• all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

key

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

extra

这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种，常用的有

• using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中
• using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据（覆盖索引）
• using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤

索引失效分析

以下情况会导致索引失效（盗图，侵删）：