mysql 索引

索引的目的

• 加快查询速度，查询优化最有效的手段

可以使用索引语法

• 查询条件
  • 等值：=
  • 范围：> < betewm in
  • 模糊查询：like，索引最左匹配原则
  • and or
• join：利用索引关联
• order by：利用索引有序

索引类型

• 聚簇索引和非聚簇索引
• 主键索引：
• 普通索引：可以重复
• 唯一索引：不可重复，NULL可以重复
• 联合索引：
• 全文索引：倒排索引

索引数据结构

• hash
• B+树
  • 二叉搜索树：二叉，不一定平衡
  • B树：
    • 多路搜索树，适合硬盘/文件系统特点，按块读写
    • 自动层次控制，防止数据倾斜
    • 数据分布在所有节点中，
      • 单次查询效率可能高
      • 范围查询的效率低
  • B+树：继承B树特点，不同的是
    • 数据分布在叶子节点中，可以使用有限的内存空间存放更多的索引
    • 叶子节点链表间通过双向指针连接形成有序链表，适合范围查询
    • innodb：怎么算索引层数
      • 从information_schema:存放的是元数据，可以查的表定义，行数，空间大小，也可以间接查层数：
        • 参考：https://blog.csdn.net/qq_41999455/article/details/104946754
      • 一个bigint：8字节+指针6字节，一个page 16K，一个page可以存放1000个节点

innodb的索引实现/存储结构

• page，16K
  • 页内每条记录通过单项指针相连，记录全局有序
  • 页有页目录，二分查找
  • 这边对应order by
• 主键索引：主键+页号
• 二级索引：索引列值+主键+页号， 逻辑上相当于（索引列值+主键）联合索引

innodb怎样使用索引加快查询

• 见查询语句的执行

怎么良好使用索引机制

• 使用索引
  • 执行计划，索引优化
    • 注意mysql查询优化器，基于评估量的
  • 性能监控，直接查看执行记录
  • 强制走索引：force index，最好不要用
• 建立索引
  • 索引使用监控
  • 一般经验
    • 经常使用的：where+join on
    • 有区分度的（基数小的）
      • 男女的没用
    • 索引长度小，有一定区分度就可以了
      • 比如很长的字段，就建立前几个
    • 联合索引注意顺序
    • 二级索引-回表-覆盖索引：Extra：Using index
      • 索引下推：5.6引入的优化机制吧
        • 组合索引时，判读其他条件，减少回表次数
        • 组合索引name,age。查询条件 name like ‘张%’ and age=10。
        • 没有索引下推只会看name，有索引下推会过滤age，减少回表次数
    • 主键最好是单调递增
      • 基本是顺序写入，数据文件紧凑
      • 记录不改位置，二级索引就不用变更
      • 引申出一个频繁更新，删除，会产生大量的碎片，影响查询效率
        • 更新是因为变长字段，所以能用定长就定长
        • 删除是打个标记
        • 碎片会评估后整理，可以手动整理
        • 插入：页分裂
        • 页合并
    • 索引不是越多越好，评估后该加就加
    • 更新频繁的列不建索引
    • 大文本，大对象不建索引

mysql执行计划怎么看

• explain
  • id：执行顺序，
    • id相同从上到下，
    • id不同从小到大
  • table 表名
  • type 类型
  • key 使用的索引
  • rows 扫描行数的预估量
  • extra 额外的信息，比如排序信息，索引下推等等

慢查询

• 开启慢查询日志
• 分析sql语句
  • 额外数据，不需要的行和列
  • limit优化
  • 索引
  • 关联
• 执行计划
• 性能分析

千万级大表优化

mysql innodb和myisam

• myisam基本上是一个表，非常简单
• innodb
• 事务
• 锁
• 外键
• 读写效率