mysql支持的索引
索引是在存储引擎层实现。而不是在mysql内实现
- B-tree索引
index 普通索引 没有限制
primary 主键索引 必须唯一，且不能为空
unique key 唯一索引 必须唯一
index 前缀索引
当索引的字段长度很大，则使用前缀索引，使用更少的空间，并综合索引的选择性，达到平衡。
//如下面。根据字段列的长度，进行统计
select count(*) as cnt,left(city,7) as pref from city group by pref order by cnt desc limit 10
//计算索引选择性
select count(distinct left(city,7))/count(*) from city
//添加索引
alter table tablename add key (keyname(length))
alter table country add key(country(3))
B-tree索引
特点
- B-tree 索引是以B+树结构存储数据
- B-tree 索引能够加快数据的查询速度
- B-tree索引适合进行范围查找 索引是顺序存储的
在什么情况下使用到了b-tree索引
- 全值匹配的查询
- 匹配最左前缀的查询
- 匹配列前缀查询
- 匹配范围值查找
- 精确匹配左前列并范围匹配另外一列
- 只访问索引的查询
B-tree索引的限制
- 如果不是按照最左列开始查找，则无法使用索引
- 使用索引时不能跳过索引中的列
- not in 和<> 操作无法使用索引
- 如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引
Hash 索引
特点
- 基于hash表实现的，只有查询条件精确匹配hash索引中所有列才能使用到hash索引
- 对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码
限制
- hash索引必须进行二次查询
- hash索引无法用于排序
- hash索引无法用于范围查找也不支持部分索引查找
- hash索引中hash码的计算可能存在hash冲突
使用索引的好处
- 索引可以减少存储引擎需要扫描数据量
- 索引可以帮助排序，避免使用临时表
- 索引可以把随机io变为顺序io
索引会增加写操作的成本
过多的索引会增加查询优化器的选择时间
索引的优化策略
索引列上不能使用表达式或者函数
前缀索引和索引列的选择性
联合索引
列的顺序选择
- 经常被使用的列优先
- 列的选择性高的优先
- 宽度小的列优先
覆盖索引
在btree索引上直接获得查询中所需数据
- 可以优化缓存，减少磁盘io操作
- 可以减少随机io，变随机io为顺序io
- 可以避免对innodb主键索引的二次查询
- 可以避免myisam表进行系统调用
无法使用覆盖索引
- 存储引擎不支持覆盖索引
- 查询中使用了太多的列
- 使用了双%的like查询 双%无法使用索引
聚簇索引
聚簇索引是一种数据的存储方式。聚簇表示数据行和相邻的键值紧凑地存储在一起。innodb是通过主键聚集数据
优点
- 可以把相关的数据保存在一起
- 数据访问更快
- 使用索引覆盖扫描查询时，可以直接使用页节点中的主键值
缺点
- 聚簇索引最大限度地提升了io密集型的性能，但是如果数据都放入内存，则访问的顺序并没有那么重要
- 插入的速度严重依赖于插入的顺序
- 更新的代价很高，会强制innodb把被更新的行移动到新位置
- 基于聚簇索引的表，在插入新行，或者主键被更新导致需要移动行时，可能会导致页分裂
- 聚簇索引可能导致全表扫描变慢
- 二级索引会比想象的大，因为在二级索引中含有主键
- 二级索引需要两次访问，而不是一次
