索引是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中,顺序扫描可能要比索引查找高效的多