mysql 5.7索引文件后缀_mysql innodb 索引使用指南

索引相关概念聚簇索引(clustered index)

使用innodb引擎时，每张表都有一个聚簇索引，比如我们设置的主键就是聚簇索引

聚簇是指数据的存储方式，表示数据行和相邻的键值紧凑的储存在一起

特点：查询数据特别快，因为聚簇索引和行数据存储在磁盘的同一页，这样可以减少磁盘I/O操作次数(MySQL 索引 B+Tree)

注意：主键应该尽量简短

二级索引(secondary index)

除了聚簇索引外的其他索引叫做二级索引(辅助索引)，比如我们给除主键外其他字段创建的索引

特点：二级索引里面存储了聚簇索引，最后要通过聚簇索引找到行数据。可见，聚簇索引的效率会影响其他索引

覆盖索引(covering index)

索引包含了查询语句需要的所有数据，这种索引称为覆盖索引

特点：索引的叶子节点中已经包含要查询的数据，不需要回表操作所以很快(减少了磁盘I/O操作次数)

组合索引(multiple-column index)

组合索引也称为复合索引(联合索引)，是指把多个字段组合起来创建一个索引(最多16个字段)

特点：遵循最左前缀匹配原则

最左前缀匹配原则(leftmost prefix principle)

mysql会从左向右匹配直到遇到不能使用索引的条件(>、

设想用a,b,c字段创建一个组合索引(a,b,c)

由于a是索引的最左边前缀，所以where条件中必须匹配字段a，mysql优化器才会用到这个索引

在匹配字段a的前提下，才能匹配字段b

在匹配字段a的前提下，并且匹配字段b，然后才能匹配字段c

使用explain查看执行计划

explain命令用来查看select语句执行计划，确认该SQL语句有没有使用索引，是否做全表扫描，是否使用覆盖索引等idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra

type：代表数据访问类型(由左至右，由最差到最好)

| All | index | range | ref | eq_ref | const | system | null |

possible_keys：表示哪些索引可能有利于高效的查找

key：显示mysql决定采用哪个索引来优化查询

key_len：显示mysql在索引里使用的字节数

ref：显示了之前的表在key列记录的索引中查找值所用的列或常量

rows：为了找到所需的行大致需要读取的行数

extra：表示额外的信息(左边较差，右边较好)

| Using filesort| Using temporary | Using where | Using index condition | Using index |

Using index：使用了覆盖索引，速度很快，限于查询字段都位于同一个索引中的场景

Using index condition：表示使用了ICP优化(Index Condition Pushdown)，能减少引擎层访问基表的次数和MySQL Server访问存储引擎的次数

Using where：表示在存储引擎检索行后mysql服务器再进行过滤

Using filesort：返回结果前需要做一次外部排序(内存或硬盘)，速度慢应该尽量避免

Using temporary：在对查询结果排序时会使用一个临时表，速度慢

创建一张测试表

使用InnoDB引擎创建teacher表，id设为自增主键

mobile、name和birthday建立第一个组合索引idx_one(注意三个字段在索引中顺序)

email、age和name字段建立第二个组合索引idx_two(同样注意顺序)CREATE TABLE `teacher` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(64) DEFAULT NULL,

`birthday` timestamp NULL DEFAULT NULL,

`email` varchar(32) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`mobile` varchar(16) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_one` (`mobile`,`name`,`birthday`),

KEY `idx_two` (`email`,`age`,`name`)

) ENGINE=InnoDB AUTO_INCREMEN