MySQL索引笔记

MySQL索引类型

普通索引
主键索引（聚簇索引）
唯一索引
复合索引（组合索引）

索引创建语句：

#创建普通索引
create index idx_name on tableName(column)
alter table tb1 add key(column)
#创建唯一索引
create unique index idx_name on tableName(column)
#建组合索引
create index idx_name on tableName(col1, col2)
#删除索引
alter table tableName drop index idx_name;

索引规则

组合索引：最左前缀规则
在查询时，若用到两个独立的索引，这种叫索引合并。若所查询的字段刚好是索引的列，这种叫覆盖索引。
索引合并：指检索时用到两个独立的索引。

问一问

什么是索引覆盖

select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。
当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息。

什么是聚集索引和辅助索引

聚集索引（主键索引）：

聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据。
聚集索引的叶子节点称为数据页，聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

辅助索引（二级索引）：
非主键索引，叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值。

Explain

判断一条SQL走没走所以，用了哪个索引，可以用 explain 关键字了解MySQL的内部执行过程。
官方文档关于 explain 的解释

id|select_type|table    |type |possible_keys         |key          |key_len|ref|rows|Extra                  |
--|-----------|---------|-----|----------------------|-------------|-------|---|----|-----------------------|
 1|SIMPLE     |tablename|range|idx_pub,idx_status_pub|idx_status_pub|8     |   |5352|Using index condition; Using filesort|

select_type

simple：不包括union和子查询的查询都算simple类型。
primary：包括union，union all，其中最左边的查询即为primary。
union：包括union，union all，除了最左边的查询，其他的查询类型都为union。

type：访问方法

ref：普通二级索引与常量进行等值匹配
ref_or_null：普通二级索引与常量进行等值匹配，该索引可能是null
const：主键或唯一二级索引列与常量进行等值匹配
range：范围区间的查询
all：全表扫描

possible_keys

对某表进行单表查询时可能用到的索引

key

经过查询优化器计算不同索引的成本，最终选择成本最低的索引

rows

如果使用全表扫描，那么rows就代表需要扫描的行数
如果使用索引，那么rows就代表预计扫描的行数

filtered

如果全表扫描，那么filtered就代表满足搜索条件的记录的满分比
如果是索引，那么filtered就代表除去索引对应的搜索，其他搜索条件的百分比

index_merge

索引合并

参考资料

MySQL官方文档
MySQL的万字总结（缓存，索引，Explain，事务，redo日志