4索引

Posted on Edited on
4索引

B+作为索引数据结构优点：树矮胖IO次数少，范围查找效率高
常见索引模型

  • hash
  • 有序数组
  • 其他

索引类型

  • 聚集索引、非聚集索引
  • 普通索引、唯一索引
  • 全文索引
  • 空间索引 MyISAM

默认索引的最大长度为767字节
MySQL5.7及以上版本中，innodb_large_prefix，可增加到3072字节(前提条件使用DYNAMIC或COMPRESSED 行格式)
超过最大限制可能创建索引失败，索引性能差

使用 Barracuda 文件格式。

  • hash
    区间搜索效率低，适合等值查询场景
  • 有序数组
    数组插入移动数据成本高，适合静态数据存储，等只查询和范围查询性能优秀
  • 搜索树
    二叉树存储树高较高，受限于IO效率，100个节点树高大概20.
    InnoDB 1200叉，存储1200^3=17亿，17亿树高3
    B+ 相对二叉树减少单次查询IO访问次数
  • 其他
    跳表、LSM树

InnoDB索引模型

  • 主键索引（聚簇索引 clustered index）
    叶子存储整行数据
  • 非主键索引（二级索引 secondary index）
    叶子存储聚集索引值（若是int只需要4字节，若是UID则需要64字节），需要再次查询聚集索引 即回表；主键的长度越小，普通索引的叶节点越小，占用空间越小

索引维护

数据页满再插入需申请新的数据页，挪动部分数据过去，称为页分裂（性能会受到影响）
页分裂空间利用率下降大概50%
删除数据导致利用率很低会进行合并

选择递增主键可减少页分裂。

覆盖索引

索引包含查询结果的所有字段（避免回表）即可成为覆盖索引，单字段也可称为覆盖索引。

前缀索引（最左原则）

索引字段ABC
查询条件AB，命中
查询条件AC ，命中A
查询条件B，不命中
查询条件BC，不命中

索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 在索引遍历过
程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

其他

读取数据时按页读入内存，每页大小16K
索引数据页内部通过有序数组二分法定位具体行

问题

如果你要重建索引 k，两个 SQL：
alter table T drop index k;
alter table T add index(k);
重建主键索引，这么写：
alter table T drop primary key;
alter table T add primary key(id);
对于上面这两个重建索引的作法，说出你的理解。如果有不合适的，为什么，更好的方法是什么？

重建索引k的做法合理，可以省空间。但重建主键不合理。不论是删除主键还是创建主键，都会将表重建。连着执行这两个语句，第一个就白做了。可使用alter table T engine=InnoDB代替

执行计划

重点关注 type、rows、key 和 Extra字段

type

MySQL 如何查找数据
system：表只有一行数据（系统表）。
const：通过主键或唯一索引查找，结果只有一行。
eq_ref：连接查询时，使用主键或唯一索引。
ref：使用非唯一索引查找。
range：使用索引范围查找。
index：全索引扫描。
ALL：全表扫描（性能最差）。

Extra

链接查询什么都没有写表示用NLJ？

  • using filesort 使用了文件排序（性能较差）。
    结果列数据大小超过分配的内存大小sort_buffer_size
  • using index condition 索引下推
  • using index 索引覆盖 （直接从索引中获取数据，无需回表，减少随机IO）。
    性能高
    索引维护有成本，所有字段都建索引并不一定高效
  • Using where：使用了 WHERE 条件过滤数据。
  • Using temporary：使用了临时表（常见于排序或分组）。
  • Using join buffer (Block Nested Loop)：BNL，使用了连接缓冲区，需优化

rows

预估行数

filtered

条件过滤百分比（条件有效性）
越高越好，
filtered = (符合条件的行数 / 描行数row ) * 100
使用索引时扫描行数和符合条件行数接近，filtered会越大。filtered太小时排查索引是否命中。

普通索引和唯一索引，应该怎么选择？

如身份证号码建索引如何选择呢？

  • 查询性能
    微乎其微
  • 更新性能
    普通索引数据更新使用changebuffer存储操作记录，减少读取磁盘到内存的IO性能消耗
    唯一索引数据更新要判断是否冲突，必须将磁盘数据读入内存，当数据不在内存时有IO消耗

写多读少系统（账单、日志），可充分发挥changebuffer的作用
读多的系统，写入后立即需要读取，需将磁盘数据读入内存，再应用缓存的操作，不会提升性能，还增加了维护changebuffer的代价。

change buffer

延迟合并减少IO，机械磁盘效果大
跟新数据页时，数据已存在内存中则直接更新内存
数据不在内存中，将缓存操作记录在change buffer（避免IO读），当对应的IO页加载到内存时，应用change buffer中的相关操作，写redo log ，此时merge结束，后续再刷入磁盘

适用于写多读少场景

change buffer占用buffer pool内存，不能无限增大
innodb_change_buffer_max_size设置

区别redo log 的WAL 是为了减少写入IO，changebuffer减少读取IO

可能引发的问题：对唯一索引数据大批量插入时缓存不命中导致缓存命中率低，系统阻塞。

MySQL为什么有时候会选错索引？

索引选择判断条件（优化器）：扫描行数、排序、回表、临时表
扫描行数统计：采样估算统计”基数”（不准确会导致使用错误索引，analyze table t重新统计修复）
不同值越多，索引区分度越好，不同值的个数称为“基数”

InnoDB默认选择N个数据页，统计页面上的不同值，得到一个平均值，乘以索引的页面数，得到索引的基数。
动态更新，变更行数超过1/M时自动触发重新统计
两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent的值选择：

  • 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
  • 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

mysql索引选择异常处理（如何让mysql选择正确索引）

  • force index
  • 考虑修改语句，引导 MySQL 使用我们期望的索引。
  • 新建一个更合适的索引，来提供给优化器做选择
  • 删掉误用的索引

怎么给字符串加索引？

  • 前缀索引
  • 倒排
  • 添加hash字段

前缀索引

使用前缀索引，定义好长度，即节省空间，又不增加查询成本。
索引长度长，占用空间大，数据页存放的值少，效率相对低。

如何定义好长度呢？
通过下面语句查询不同前缀，可查询到的数据量，设置可接受损失比例，如5%，在L4-L7中选择合适长度即可

1
2
3
4
5
6
select 
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;

前缀索引的影响

  • 扫描行数
  • 索引覆盖
    需要回表，即使前缀索引包含整个字符串也会回表，因为InnoDB不知道。

倒序存储与hash

  • 存储额外空间
    hash需额外字段存储
    倒序存储，不需要额外空间
  • CPU消耗
    hash调用crc32函数
    倒序调用reverse，消耗小点
  • 查询效率
    hash性能更稳定，冲突概率小，扫描行数接近1
    倒序，使用前缀，增加扫描行数

小结

  1. 完整索引，比较占用空间；
  2. 前缀索引，节省空间，但增加查询扫描次数，且不能使用覆盖索引；
  3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
  4. 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

问题

如果你在维护一个学校的学生信息数据库，学生登录名的统一格式是”学号@gmail.com”, 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。
系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑
登录验证这个行为的话，你会怎么设计这个登录名的索引呢？

为什么sql语句逻辑相同性能差异很大？

对索引字段做函数操作，优化器就放弃走树搜索功能(函数操作可能会破坏索引值的有序性）
隐式类型转换也是隐含了函数操作所以可能导致放弃索引
类型装换方式：转换为范围较大的类型
若索引字段类型范围小就会导致索引字段类型转换，优化器会放弃索引。若条件字段类型范围小则会继续使用索引。

索引不命中

  • force index
  • 重建索引