Mysql之二-索引详解

索引是什么

索引是存储引擎用于快速找到记录的一种数据结构;
索引是在存储引擎层而不是server层的实现.

相关数据结构

1.B-Tree(应用范围比较广)(多路平衡查找数)

  • 范围寻找;
  • 顺序查找;
  • 按值选择;

2.Hash (适用于等值寻找)

  • 没法按顺序, 范围, 重复多容易导致hash碰撞;

聚簇索引和非聚簇索引

1.聚簇索引并不是一种索引类型, 而是一种数据存储方式.
2.Innodb的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行;

  • 聚簇表示数据行和键值紧邻在一起, 因为数据行只有一个, 所以一个表只能有一个聚簇索引;
  • 一般主键索引为聚簇索引, 其他为非聚簇索引;
  • 聚簇索引跟存储引擎相关,并不是所有存储引擎都支持聚簇索引;

覆盖索引

1.除了聚簇索引之外, 其他索引都是存储了键值加上主键的值, 如果还需要其他值, 就需要查到主键后, 回表查询一下聚簇索引, 再返回值;
2.mysql5.6之后, 可以使用覆盖索引优化这种现象, 避免回表;
3.比如弄一个(列A, B)的索引, 通过A查找到B时, 就不用回表了;

前缀索引和索引匹配顺序

1.当某些字符串需要建立索引时, 为了节约空间, 可以用前缀几位建立索引即可, 省空间;
2.索引的匹配顺序是按照最左原则匹配的;

  • 及建立一个索引(A,B), 那么你查找where B = XX , 由于B在右边, 就不会被匹配到;

索引下推

在mysql5.6之后启用, 默认开启;

1
2
3
4
// 若建立(A,B,C)索引
select * from test where A = 'xxx' and B like '%xx%'
// 若没有索引下推, mysql会在索引进行查找到A之后, 回表查到数据, 再进行匹配B
// 若有索引下推, mysql会直接在索引上, 直接匹配B, 去掉不符合的, 然后在回表查找那些有的, 大大减少了回表的次数

普通索引和唯一索引区别

1.对于普通索引来说,查找到满足条件的第一个记录 (5,500) 后,需要查找下一个记录,直到碰到第一个不满足 k=5 条件的记录。
2.对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索
(就是普通索引比唯一索引就多了一步查找, 性能损耗其实微乎其微)

索引更新

1.唯一索引的更新不能使用change buffer. 只有普通索引可以使用;
2.还有就是唯一索引更新时一定要去判断该键值是否唯一, 所以如果该键值没有在内存页中, 需要从磁盘读取出来, 这个是成本很高的, 而普通索引只是需要把更新操作写入change buffer中, 等待一起磁盘写入即可;
(这两类索引在查询能力上是没差别的,主要考虑的是对更新性能的影响)

change buffer和redo log

redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗
总的来说就是redo log会把一系列操作合在一起, 顺序写入磁盘, 而change buffer则是更新或插入时, 不用去磁盘找出该数据, 而是写入到内存change buffer中, 等待一起写入;

索引的选择和异常处理

1.索引选择之所以错误, 是因为mysql判断扫描的行数不准确;
2.解决方法:

  • 用force index强制选择索引;
  • 修改语句, 诱导数据库;
  • 新建一个更合适的索引, 提供优化器做选择, 或删除误用的索引;
  • 重新分析表, analyze table , 解决索引统计信息不准确问题;

字符串字段加索引

1.完整索引, 占空间;
2.前缀索引, 损失区分度, 减少空间;
3.倒序存储, 再创建前缀索引, 用于绕开字符串本身前缀区分度不够;
4.创建hash字段索引, 查询性能稳定;额外的存储和计算消耗, 和第三种一样, 不支持范围扫描;

mysql刷脏页会导致数据库卡顿一下

count(*)的优化

  1. 用redis,这个虽然可以使得数据库压力降低;但是因为是两个库, 无法形成一个事务, 可能会导致不一致的数据出现;
  2. 建立一张新表来存储, 这就是变成同一个系统, 可以保持一致, 缺点是压力;
hyhcoder wechat
扫码关注我的个人订阅号