mysql索引了解一下

索引是mysql中非常重要的知识，类似于书籍的目录，可以提高检索效率，降低数据库的io成本。本文主要介绍最常用的B-Tree索引。

mysql索引结构

 

B-Tree索引结构见上图。最底下的是叶子节点，上层是非叶子节点。非叶子节点中存放键值和指向下层的指针；叶子节点存放键值和数据，存放的数据根据实现的不同而不同。例如：

• InnoDB一级索引（主键索引，聚簇索引），data中存放的是表中每一行的数据
• InnoDB二级索引，data中存放的是主键值
• MyIsam索引中存放的是表中每一行的地址值，一级索引和二级索引无差别

可以看到，通过索引查找的复杂度变成了O(logN)。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储的方式。InnoDB通常将逐渐座位聚簇索引。如果建表时没有指定主键，存储引擎内部会隐式生成一个主键当做聚簇索引。

聚簇索引有一些重要的优点：

• 可以把相关的数据保存在一起
• 数据访问更快，聚簇索引不需要再通过主键再查一次
• 使用索引扫描的查询可以直接使用页节点中的主键值

聚簇索引也有一些缺点：

• 插入速度严重依赖插入顺序
• 更新聚簇索引列的代价高
• 可能导致全表扫描变慢，尤其是存储比较稀疏，或者由于页分裂导致数据存储不连续的时候，需要多次磁盘io

B-Tree是一个平衡树，每次插入的时候都需要重新调整使树平衡，mysql的数据是以“页”为最小单位存储在磁盘上，如果一个页存储满了，而新插入的数据需要放在该页上，会导致页分裂。因此会影响插入的效率，并且会增加索引文件的占用空间。可见，聚簇索引最好使用自增的键作为聚簇索引，插入时按聚簇索引插入。下面是使用连续自增的列作为主键，和非连续的列作为主键的插入测试：

CREATE TABLE `info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `encr` varchar(64) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `val` varchar(32) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `tbl_info_e` (
  `id` int(11) DEFAULT NULL,
  `encr` varchar(64) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `val` varchar(32) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`encr`)
);

 

覆盖索引

包含所有需要查询的字段值的索引是覆盖索引。使用覆盖索引可以极大的减少数据访问量，避免对主键索引的二次查询；使用覆盖索引时，explain的extra列中有Using index。

延迟关联实例

CREATE TABLE `tbl` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `val` int(11) NOT NULL DEFAULT '0',
  `source` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_k` (`val`)
)
向上表中插入600万数据，val是0 - 10的随机数；现进行分页查询：
```
select * from tbl where val=4 limit 250000,5; 耗时9.146s
select * from tbl a inner join (select id from tbl_rand where val=4 limit 250000,5) b on a.id=b.id; 耗时0.075s

使用索引优化order by

如果查询的列和排序的列都在索引中，并且索引列的排序规则和order by子句的顺序一致，那么mysql可以只扫描索引文件而不需要使用临时表进行排序。这样会大大提高排序的效率。

使用索引排序sql的explain type列为index

说明一下“索引列的排序规则和order by子句的顺序一致”：

-- 一个答题的功能，记录答题耗时和分数，查看排名的话，根据分数和耗时来排序，分数高，耗时少的排名越高
CREATE TABLE `t_order` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `costTime` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_t` (`score` DESC,`costTime` ASC)
);

-- 下列语句可以使用索引进行排序
select userId from tbl order by score desc, costTime asc;
select userId from tbl order by score asc, costTime desc;

-- 下列需要用到临时表
select userId from tbl order by score, costTime;
select userId from tbl order by score, costTime desc;

使用索引优化group by

mysql优化group by使用索引有两种方式：松散索引扫描和紧凑索引扫描。

松散索引扫描

使用松散索引扫描，explain中extra列中出现Using index for group-by。
满足松散索引扫描需要满足一下条件：

• 查询在单一表上
• Group by字段满足最左匹配
• Select列仅适用min或者max聚合函数
• AVG(DISTINCT), SUM(DISTINCT)和COUNT(DISTINCT)可以使用松散索引扫描（version 5.5+）

紧凑索引扫描

不满足松散索引扫描，但是查询字段和group by字段在索引中，查询中存在常量比较的where条件，且该字段在group by字段的前面或者中间（满足覆盖索引,extra 列中出现Using index）。

示例：

假定t1(c1,c2,c3,c4)表上有idx(c1,c2,c3)索引

以下查询会使用松散索引查询：
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2; 
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2; 
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2; 

以下查询会使用紧凑索引查询：
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3; 
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

以下查询使用临时表：
SELECT c1, c3 FROM t1 GROUP BY c1, c3;
 

索引的缺点

• 索引会占用磁盘空间
• 索引虽然可以加速一些查询的效率，但代价是维护索引的成本。只有索引带来的收益大于维护索引的成本时索引才是有效的
• 索引不是万能的，并不是总能提高查询效率。在一个要扫描表中大多数行的sql中，顺序扫描可能要比索引查找高效的多