MySQL索引的相关学习总结

哪些情况需要创建索引：

1. 设置一个字段为主键后它自动建立唯⼀索引

2. 频繁作为查询条件的字段应该创建索引

3. 多表关联查询中，关联字段应该创建索引 on 两边都要创建索引

4. 查询中排序的字段，应该创建索引

5. 频繁查找字段覆盖索引

6. 查询中统计或者分组字段，应该创建索引 group by

 

哪些情况不需要创建索引：

1. 表记录太少 。

2. 经常进行增删改操作的表 。

3. 频繁更新的字段 。

4. where条件里使用频率不高的字段 。

 

有关操作索引的语句：

1.单列索引之普通索引

ALTER TABLE table_name ADD INDEX index_name(column_name(length));

CREATE INDEX index_name ON TABLE table_name;

2.单列索引之唯一索引

ALTER TABLE table_name ADD UNIQUE INDEX index_name(column_name(length));

CREATE UNIQUE INDEX index_name ON TABLE table_name;

3.单列索引之全文索引

ALTER TABLE table_name add FULLTEXT index_name(column(length));

CREATE FULLTEXT index_name(column_name(length)) ON TABLE table_name;

4.创建组合索引

ALTER TABLE 'table_name' ADD INDEX index_name(colunm1,colunm2,colunm3);

 

为什么使⽤组合索引：

1.mysql创建组合索引的规则是⾸先会对组合索引的最左边的，也就是第⼀个字段比如name的数据进行排序，在第⼀个字段的排序基础上，然后再对后⾯第⼆个字段比如age进行排序。其实就相当于实现了类似 order by name age这样⼀种排序规则。

2.为了节省mysql索引存储空间以及提升搜索性能，可建⽴组合索引（能使⽤组合索引就不使⽤单列索引)，比如一个三个字段的组合索引相当于三个单列索引，三个索引公用一个B+树从而节省空间。

使用组合索引遵守最左前缀原则。

 

关于执行计划EXPLAIN命令：

1.关于执行计划的用途

MySQL 提供了⼀个 EXPLAIN 命令, 它可以对 SELECT 语句的执行计划进行分析, 并输出 SELECT 执行的详细信息, 以供开发⼈员针对性优化。 使用EXPLAIN 这个命令来查看⼀个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过EXPLAIN 命令来查看。

2.执行计划的字段含义

id：任何一个SELECT语句都会分配一个唯一的标识符。id相同执行顺序从上到下，不同的话越大执行的优先级越大。如果id==null说明为一个查询结果是结果集。

select_type：当前SELECT的查询类型,常见类型有simple,primary,subquery,dependent subquery,union,dependent union,derived等。

type：依次从好到差： 除了all之外，其他的type都可以使⽤到索引，除了index_merge之外，其他的type只可以⽤到⼀个索引。system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery， index_subquery，range，index_merge，index，ALL。

• 注意事项：最少要查询的type使⽤到range及其以上级别。

1. system 表中只有⼀行数据或者是空表。
2. const（重要）使用唯⼀索引或者主键，返回记录⼀定是1⾏记录的等值where条件时，通常type是const。
3. ref（重要） 针对非唯⼀性索引，使用等值（=）查询⾮主键。或者是使⽤了最左前缀规则索引的查询。
4. fulltext 全⽂索引检索，要注意，全文索引的优先级很高，若全⽂索引和普通索引同时存在时，mysql不管代 价，优先选择使⽤全⽂索引。例句：explain select * from index_name where match(column_name) against('aaa');
5. range（重要） 索引范围扫描，常见于使⽤>,<,is null,between ,in ,like等运算符的查询中。
6. index（重要） 关键字：条件是出现在索引树中的节点的。可能没有完全匹配索引。索引全表扫描，把索引从头到尾扫⼀遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
7. all（重要）这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

 

 

 

possible_keys：此次查询中可能选用的索引，⼀个或多个。

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

key_len：⽤于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查 询不⼀定都能使⽤到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的 列，这里不会计算进去。 留意下这个列的值，算⼀下你的多列索引总长度就知道有没有使用到所有的列了。 另外，key_len只计算where条件用到的索引长度，而排序和分组就算⽤到了索引，也不会计算到 key_len中。

ref ：如果是使用的常数等值查询，这里会显示const 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段 如果是条件使用了表达式或者函数，或者条件列发省了内部隐式转换，这里可能显示为func rows 。

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

 

参考⽹站：

https://segmentfault.com/a/1190000008131735

https://blog.csdn.net/rewiner120/article/details/70598797