跳至内容
索引
索引的类型
b tree 索引
- b tree 索引
b+tree 索引
- InnoDb 默认是B+tree
hash索引
- 基于hash 实现
- 根据hash查找对应slot，对比值
- InoDb引擎中 索引被频繁使用，会基于Btree创建自适应hash索引，增加索引性能，目前是不受人工管理的
B+Tree相对于B-Tree 区别
- 非叶子节点只存储键值信息。
- 所有叶子节点之间都有一个链指针。
- 数据记录都存放在叶子节点中。
- b+tree 相比btree 树的深度浅，IO次数减少
btree 和 hash索引优缺点
- btree 范围查询可以使用索引
- btree 前缀查找支持索引
- btree 联合索引，支持最左索引原则
- hash索引只有精确匹配才生效，不支持模糊、范围索引、排序、联合索引不支持最左原则
- hash索引速度非常快复杂度为O(1)、索引维护相对简单、空间占用低
- hash索引获取行指针而不是数据，需要再次读取
- hash冲突会影响性能，所以需要找一个区分度高的列
- hash适用于文本索引，因为文本转化为hash保存空间小。如针对url加一列hash并增加索引，这样每次查询都可以走索引，而且索引值小
聚簇索引
- 聚簇索引并不是索引的一种类型，是innodb针对主键或非空索引的一种存储方式。叶子节点中会存储数据行。而不是储数据地址
- 减少io
- 访问速度快
- 范围查询也很快
- 如果数据和索引都在内存中，效率提升小
- 更新代价高（聚簇索引字段不更新，没问题）
- 插入新行，会有页分裂，页分裂会暂用而更多磁盘空间，主键使用auto_increment 会避免这种情况。不过并发高可能会产生竞争。
- 二级索引需要两次查找，先找到二级索引对应的聚簇索引主键值，然后再走聚簇索引拿数据值（二级索引不保存数据信息）
非聚簇索引
- myIsam 的索引是非聚簇索引，叶子节点是地址，
覆盖索引
- 是索引的一种使用方式，返回则值为索引列，不需要再回查表
- 由于innoDb 是聚簇索引，所以二级索引中，覆盖索引非常有用，无需再用主键走聚簇索引
- 覆盖索引在explain 表现为 Useing Index
- innodb 二级索引返回主键索引，也可以使用覆盖索引，减少一次IO，应为二级索引会挂载聚簇索引id
索引排序
- type 值为 index 说明mysql使用额索引来排序
- 假如索引不能覆盖查询所需的全部列，那不得不每次都回表查询一次，基本上都是随机I/O（数据存储是否连续），因此索引顺序读取数据的速度通常要比顺序读取全表慢
- 只有索引列和排序列保持一致时，mysql 会使用索引排序（Extra：Using where）
- 多列索引，不满足最左原则，最左的值为常量，仍然可以使用索引排序
冗余和重复索引
- 索引越多insert和update数据的tps 更慢，需要维护索引变更
- 联合索引可以覆盖返回值，比起单独索引性能会好很多
压缩索引
- MyIsam 中使用前缀压缩来减少索引大小，针对I/O密集应用会有较高性能提升，cpu密集型相反
索引和锁
- innoDB 查询有索引时会走行级锁（for update 方式），在范围查询for update 中，可能使用的范围索引，从而锁住了不需要的行，A>5 and B=2 。可能会锁住B!=2的数据行。假如没有索引命中，可能锁整个表
范围条件
- in 和 > between explain 后 type：range，但是in查询后面的索引条件是生效的，然鹅 > 则后有索引条件是不生效的
- in 查询 多字段，mysql 解析器 会解析为 x*y 条查询合并，索引 in是可以走索引
- mysql不支持再索引上多次使用范围查询
索引信息
- 查看索引信息show index from ，其中有一个区分度cardnality，表示有多少个不同的值，区分度越大约好
减少索引和数据碎片化
- btree 索引可能碎片化，btree 需要随机磁盘访问才能定位到叶子节点，如果叶子页再物理分布上是顺序的，那么查询性能会更好，否则对于范围查询、覆盖索引会降低很多性能
- 数据碎片花
- 行碎片化：数据行存储到多个地方的多个片段中，相当于一条数据磁盘上不连续
- 行间碎片化：多行不连续，影响范围查询和聚簇索引
- 剩余空间碎片化：数据页中又大量的剩余空间，导致大量空间浪费
- innoDB 会自动归并行碎片
判断索引合理
- 按响应时间来对查询分析
- 长时间查询检查schema、sql和索引结构
- explain判断是否扫描过多的行
- 是否做了额外的排序或者使用临时表
- 是否使用随机I/O访问数据、或者太多会标查询无索引数据
- 如果查询无法从索引中获益，查看是否可以创建一个更合适的索引
- 可以考虑ssd提升I/O性能
- 一些被忽略的查询，会出现性能。设计表时可以提前考虑， 也不能过度设计增加过多索引。业务、程序、负载随时间都在变化
