B-Tree
- B-Tree通常意味着所有值都是按顺序存储的，每个叶到根的距离相同
- B-Tree索引能够加快访问数据的速度，因为存储引擎不需要进行全表扫描来获取需要的数据
- B-Tree对索引列是顺序组织的，所以很适合查找范围数据，也支持
order by - B-Tree适用于全键值、键值范围或键前缀查找(键前缀查找只适用于根据最左前缀查找)
B-Tree的限制
- 如果不是按照索引最左列开始查找，则无法使用索引
- 不能跳过索引的列
- 如果查询中有某个列的范围查询，则其右边的列无法使用索引优化查询
- 例子
create table if not exists Test
(
id int not null auto_increment,
name varchar(260),
age tinyint,
sex tinyint,
primary key(id)
);
show index from test;
create table if not exists Test3
(
id int,
age tinyint,
sex tinyint,
key(id, age, sex),
key(age, sex)
);
show index from test3;
哈希索引
- 哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效
- MySql中只有
MEMORY引擎显示支持哈希索引，这也是MEMORY引擎的默认索引类型，此引擎也支持B-Tree索引。并且，此引擎支持非唯一哈希索引(其解决哈希冲突的方法是链表法) - 哈希索引自身只存储对应的哈希值，所以索引结构十分紧凑，这也让哈希查找的速度非常快
哈希索引限制
- 哈希索引只包含哈希值和行指针，并不存储字段，所以不能使用索引中的值，来避免读取行。不过访问内存中的行的速度很快，所以这一条规则对性能影响不大
- 哈希索引数据不是按照索引值顺序存储的，所以无法用于排序
- 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是索引列的全部内容来计算哈希值的
- 哈希索引只支持等值比较查询(=、 in())，不支持任何范围查询
- 例子
create table if not exists Test4
(
id int,
age tinyint,
sex tinyint,
key using hash(id, age)
) ENGINE = memory;
show index from test4;
高性能的索引策略
-
使用独立的列，索引列不能是表达式的一部分也不能是函数的参数
select id from T where id + 1 = 5此Sql语句就无法使用id列的索引 -
前缀索引
有时候需要索引很长的字符列，这会让索引变得大且慢，一个策略是模拟哈希索引，或者是索引开始的部分字符。Mysql的前缀索引无法进行order by和group by
create table if not exists Test6
(
id int not null auto_increment,
name varchar(260),
primary key(id),
key(name(5))
)
聚簇索引
- 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行(数据行存放在索引的叶子叶中)。因为无法把数据行放在两个不同的地方，所以一个表只能由一个聚簇索引
- InnoDB通过主键聚集数据，如果没有定义主键，InnoDb会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引
聚簇索引的优点
- 可以把相关数据保存在一起以减少从磁盘读取的次数
- 访问速度更快，因为聚簇索引将索引与数据保存在同一个B-Tree中
- 使用聚簇索引扫描的查询可以直接使用叶节点的主键值
聚簇索引的缺点
- 聚簇数据提高了I/O密集型应用的性能，但是如果数据全部存储在内存中，则访问的顺序就没什么重要的了，则聚簇索引也没什么优势了
- 插入速度严重依赖于插入顺序，按照主键顺序插入时加载数据到InnoDB表中速度最快
- 更新聚簇索引的代价很高，因为会强制InnoDB将每个更新行移动到新的位置
- 基于聚簇索引的表在插入新行或者主键被更新导致需要移动行
- 二级索引(非聚簇索引)的叶子节点包含了引用行的主键值而非行指针，所以需要两次索引查找
- 如果正在使用的InnoDB表没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种键的数据应该与应用无关，最简单的方法是使用
AUTO_INCREMENT自增列，这样可以保证数据行是按照顺序写入的。 - 需要避免使用随机且分布范围很大的聚簇索引。InnoDB应该尽可能按照主键顺序插入数据，并尽可能使用单调增加的聚簇键来插入新行
网友评论