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