-
索引的作用类似于指向表的行的指针，允许查询时快速确定哪些行匹配
where子句中的条件，以提高查询效率 -
不必要的索引会带来列插入、更新、删除时的负担，因为索引的维护是需要成本的
B树索引特性
-
B树索引支持
=,>,>=,<,<=,between -
支持常量字符串开头的
like匹配 -
所有的值都是按顺序存储的，并且每一个叶子页到根的距离相等
-
很适合范围查找
-
大部分索引都是B树索引
哈希索引特征
- 哈希索引仅支持相等性测试(
=,<=>，in()) - 整个键都必须被使用
create table t(i bigint auto_increment primary key, name varchar(20), key using hash(name))
--建立哈希索引
create table t1(i bigint auto_increment primary key, name varchar(20), hName bigint, index(hName))
--在B-Tree基础上实现哈希索引，哈希值需要自己维护
独立的列
- 如果查询的列不是独立的，则不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数
select * from t where id + 1 = 5;
select * from t where hex(id) = 5;
--上述查询无法使用id列上的索引
前缀索引
- 前缀索引有利于减少索引文件的大小，从而节省磁盘空间，并且加快
insert的速度 - 对于字符串列(
char,varchar,binary,varbinary,text,blob)，可以使用前缀索引 -
text,blob，很长的varchar类型的列被索引时，必须使用前缀索引
create table t(bValue blob, index(bValue(10)));
多列索引
create table t(n0 int, n1 int, n2 int, index mIndex(n0, n1, n2));
- 多列索引使用时，左侧的列必须比右侧的列先出现。举例来说，如果有一个三列的索引
(col1, col2, col3)，则(col1)，(col1, col2)以及(col1, col2, col3)具备索引搜索功能 - 一旦左侧的列不是精确匹配时，右侧的列就无法使用了
索引扩展
Innodb会通过附加主键列自动扩展每个二级索引
create table t(id int primary key, age int, d date, index dIndex(d)) engine = Innodb;
--dIndex自动扩展为(d, id)
SET optimizer_switch = 'use_index_extensions=on';
--索引扩展默认是开启的，上述命令用于关闭索引扩展
优化器使用生成列索引
mysql支持在生成的列上建立索引:
create table t(i int, v int as (i + 1) stored , x int as (i + 2), index vIndex(v));
select * from t where i + 1 > 1;
--则会使用索引vIndex
select * from t where 1 + i > 1;
--不会使用索引vIndex
select * from t where i + 2 > 1;
--不会使用索引vIndex
不可见索引
- 适用于主键以外的索引
- 默认情况下，索引可见
- 把索引设为不可见，可以在不删除索引的情况下测试没有索引时的性能
- 关键字为
visible,invisible可以作用于create table,create index,alter table - 索引的可见性不会影响索引的维护
- 没有显示主键的表，若具有
not null且unique的列，则可能具备隐式主键，如此也不能将其设为不可见
ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;
--更改索引可见性
SELECT INDEX_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1';
--获取索引的可见性
降序索引
- 降序索引，关键字为
desc，将导致键值以降序存储 - 仅
Innodb支持降序索引
CREATE TABLE t (
c1 INT, c2 INT,
INDEX idx1 (c1 ASC, c2 ASC),
INDEX idx2 (c1 ASC, c2 DESC),
INDEX idx3 (c1 DESC, c2 ASC),
INDEX idx4 (c1 DESC, c2 DESC)
);
ORDER BY c1 ASC, c2 ASC -- optimizer can use idx1
ORDER BY c1 DESC, c2 DESC -- optimizer can use idx4
ORDER BY c1 ASC, c2 DESC -- optimizer can use idx2
ORDER BY c1 DESC, c2 ASC -- optimizer can use idx3
Innodb的聚簇索引
- 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式
-
Innodb的聚簇索引实际上在同一结构中保存了B-Tree索引和数据行 -
Innodb通过主键来聚集数据，只聚集在同一页面中的记录，包含相邻主键值的页面可能相距甚远 - 最好避免随机的(不连续且值的分布范围非常大)聚餐索引
-
Innodb应该尽可能的按照主键递增的顺序插入数据 - 如果没有定义主键，
Innodb会选择一个唯一的非空索引代替如果没有这样的索引，Innodb会隐式定义一个主键作为聚簇索引 - 在
Innodb中，除聚簇索引外的所有索引都称之为二级索引。二级索引保存的不是指向行的物理地址，而是行的主键值，所以二级索引访问需要两次索引查找
覆盖索引
- 如果一个索引包含所有需要查询的字段的值，那么就称之为覆盖索引
- 覆盖索引必须要存储索引列的值，所以只有
B-Tree索引支持覆盖索引 -
Innodb的二级索引，都包含了主键值，意味着其能利用额外的主键列进行覆盖查询
网友评论