Mysql 索引设计与使用

索引的设计与使用

索引概述

在MYSQL中所有的列类型都可以被索引, 对相关列使用索引是提高SELECT操作性能的最佳途径

每种存储引擎对每个表至少支持16个索引. 总索引长度至少为256个字节

MYSQL还支持全文本(FULLTEXT)索引, 此索引可以用于全文搜索

在MYSQL5.6之后, InnoDB和MyISAM存储引擎都可以支持FULLTEXT索引. 但是只限于CHAR VARCHAR和TEXT列

索引总是对整个列进行, 不支持局部索引

MYSQL还支持空间列索引. MYSQL5.7之前只有MyISAM存储引擎支持空间类型索引, 而且索引的字段必须是非空的. MYSQL5.7中, InnoDB存储引擎也支持空间类型索引, 索引以R-Trees的数据结构保存

默认情况下, MEMORY存储引擎使用HASH索引, 但也支持BTREE索引

索引在创建表的饿时候可以同时创建,也可以随时增加新的索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name...)
[index_option]
[algorithm_option | lock_option]...
index_col_name:
	col_name[(length)] [ASC | DESC]

也可以使用ALTER TABLE语法来增加索引

create index cityname on city (city(10));

如果以city为条件进行查询, 可以发现索引cityname被使用

explain select * from city where city = 'Fuzhou' \G

索引的删除语法如下

DROP INDEX index_name ON tbl_name

例如, 想要删除city表上的索引cityname, 可以操作如下

drop index cityname on city;

设计索引的原则

• 要在条件列上创建索引, 而不是查询列.
• 尽量使用高选择度索引. 考虑列中值得分部, 索引的列的基数越大, 索引的效果越好
• 使用短索引. 如果对字符串列进行索引, 应该制定一个前缀长度, 只要有可能就要这样做
• 利用左索引, 在创建一个n列的索引时, 实际相当于创建了MYSQL可利用的n个索引. 多列索引可起到几个索引的作用, 因为可利用索引中最左边的列集来匹配行. 这样的列集被称为最左前缀.比如在a b c 中创建了一个组合索引, 使用a = ? a = ? and b = ? a = ? and b = ? and c = ? 都可以使用这个索引. 通过这种方式可以大量减少索引的数量, 提高索引的使用效率
• 对于InnoDB存储引擎的表, 尽量手工指定主键. 记录会按照一定顺序保存, 如果拥有主键, 则会按照主键顺序保存. 如果没有主键但是有唯一索引, 那么按照唯一索引进行保存. 如果没有主键和唯一索引, 则会生成一个内部列. 根据显示的列来进行是查询最快的. 注意 InnoDB表的普通索引都会保存主键的建值. 所以主键要尽可能选择较短的数据类型, 有效减少索引的磁盘占用, 提高索引的缓存效果.

索引设计的误区

设计索引的时候往往有如下误区

• 不是所有的表都需要使用索引. 对于小表没什么必要只需要主键即可. 而对于大表来说最好时刻走索引, 因此要经常分析
• 不要过度使用索引, 不要以为索引是"越多越好". 每一个额外的索引都要占用额外的磁盘空间, 并且会降低写效率. 此外再MYSQL生成一个执行计划的时候, 要考虑索引, 这也要花费时间. 创建多余的索引给查询优化带来了更多的工作. 索引太多, 也可能会令mysql选择不到所要使用的最好索引!!
• 谨慎创建低选择度索引. 再MYSQL8.0之后也可以使用直方图取得类似的效果
• 谨慎使用唯一索引,使用唯一索引在特定查询上速度会提升, 同时也能满足业务上对于唯一索引的需求. 但是在MYSQL5.6之后, InnoDB引擎新增了Change Buffer特效来大幅提升写入性能. 而除主键之外的唯一索引会导致Change Buffer无法被使用

索引设计的一般步骤

1. 整理表上所有的SQL , 重点再select update delete操作的where条件使用到的列的组合, 关联查询的关联条件
2. 整理所有查询SQL的预期执行频率
3. 整理所有涉及的列的选择度, 列不同值相比总非空行数的比例越大, 选择度越好,
4. 遵循之前提到的设计原则, 给表选择合适的主键. 没有特别合适的列时, 建议使用自增列作为主键
5. 优先给那些执行频率最高的SQL创建索引, 执行频率很高的SQL, 使用到的索引效率对整体性能的影响也会比较大. 选择其中选择度最高的列来创建索引, 如果选择度都不够好, 那么应该考虑是否可以使用其他选择度更好的条件, 或者创建联合索引
6. 按执行顺序排序, 依次检查是否需要为每个SQL创建索引.
7. 索引合并, 利用复合索引来降低索引的总数, 充分利用最左前缀的原则, 让索引可以被尽可能多地复用, 同时保证复用率的情况下, 把选择度更高的列放到索引的更左侧
8. 上线之后, 通过慢查询分析 . 执行计划分析 . 索引使用统计, 来确定索引的实际使用情况, 并根据情况作出调整

BTREE索引与HASH索引

HASH索引有一些特别重要的特征再使用时需注意

• 只用于使用=或<=>操作符的等式比较
• 优化器不能使用HASH索引来加速ORDER BY操作
• MYSQL不能确定再两个值之间大约有多少行, 如果将一个MyISAM表改为HASH索引的MEMORY表, 会影响一些查询执行效率
• 只能使用整个关键字来搜索一行

而对于BTREE索引, 当使用> < >= <= BETWEEN != 或者<> 或者 LIKE 'pattern'(pattern不以通配符开始)操作符时, 都可以使用相关列上的索引. 下列范围查询适用于BTREE索引和HASH索引

select * from t1 where key_col = 1 or key_col in (15,18,20)

下列查询只适用于BTREE索引

select * from t1 where key_col > 1 and key_col < 10
select * from t1 where key_col LIKE 'ab%' OR key_col between 'lisa' AND 'simon'

例如创建一个和city表完全相同的MEMORY存储引擎的表city_memory

create table city_memory(
	city_id smallint unsigned not null AUTO_INCREMENT,
	city varchar(50) not null,
	country_id smallint unsigned not null,
	last_update timestamp not null default current_timestamp on update current_timestamp,
	primary key (city_id),
	key idx_fk_country_id (country_id)
)ENGINE=Memory DEFAULT CHARSET=UTF8;

当对索引字段进行范围查询时候, 只有BTREE索引可以通过索引访问(B+树最后顺序存储)

而HASH是全表扫描

explain select * from city_memory where country_id > 1 and country_id < 10;

索引在MYSQL8.0中改进

不可见索引

再MYSQL8.0中, 增加了对于不可见索引(invisible index)的支持

这是一个从Oracle中借鉴过来的新特效

所谓不可见指的是查看表结构的时候可以看到, 但是在查询的时候不会走这个

可以在创建表的时候指定invisible关键字来创建不可见索引

create table t1(
	i int,
	j int,
	k int,
	index i_idx(i) INVISIBLE
)ENGINE=InnoDB;

可以通过alter命令来修改索引是否可见

alter table t1 alter index i_idx invisible;
alter table t1 alter index i_idx visible;

为什么要使用不可见索引

当表数据量睁大的时候, 达到了几百GB, 几TB甚至更大时候. 如果这个时候对表上的索引进行调整, 往往会有很大风险.

比如删除一个认为不重要的索引, 当有的执行计划还在使用这个索引的话, 就会对这个大表进行全盘扫描.

不可见索引可以很好地确定影响的范围.

同时当增加一个索引之后, 如果对系统带来了负面的影响, 可以首先将索引设置为不可见, 待系统负载恢复之后, 再做索引的删除, 避免压力大的时候雪上加霜

倒序索引

MYSQL8.0中增加了对倒序索引(descending index)的支持

在之前的版本中, 创建表的索引的时候可以指定desc关键字, 但是实际上还是正序索引

由于倒序索引引入, MYSQL8.0取消了对group by操作的隐式排序, 如果业务中有依赖于此特性的, 再升级数据库版本的时候需要谨慎.