索引优化

时间：2022-09-11 16:09:13

索引介绍

  1. 什么是索引
    索引是储存引擎中的一种数据结构. 或者说是数据的一种组织方式. 又称之为键key
  2. 为什么要用索引
    为了优化查询
    ps: 创建完索引之后会降低增.删.改的效率
    好就好在读写比例是10: 1
  3. 如何正确看待索引:
    开发人员最懂业务. 任何一个软件都有吸引人的亮点. 亮点背后对应的就是热数据. 这一点是开发人员最清楚的
    开发人员最了解热数据对饮的数据库表字段有哪些. 所以应该在开发软件过程中就提前为软件相应字段加上索引.
    而不是等到软件上线后. 让DBA发现慢查询sql再做处理. 因为:
    1. 一个软件慢会影响用户体验. 但是慢的原因有很多. 你不能立即确定是sql的问题.
      所以等定位到是sql的问题时. 可能已经被拖了很久.
    2. 因为大多数DBA都是管理型DBA而不是开发型的. 所以即便是DBA从日志中看到慢查询sql.
      也会因为不懂业务而难分析出慢的原因. 最后这个锅还是得扣在开发的脑袋上. 躲得了初一躲不了十五

平衡二叉树

索引优化

B树

索引优化

B+树

索引优化

索引到底是个什么样子的结构: B+树

二叉树. 平衡二叉树. B(balance)树 -> B+数

表 -> 书

记录 -> 一页内容
索引 -> 书的目录

select * from where id = 12; -- 命中索引
select * from where name = "xxx"; -- 无法命中索引

二叉树. 平衡二叉树 和 B数的区别:
二叉树: 每次io只能取一条记录. 其排序规律是左边排小. 右边排大. 根节点常驻内存
B树: 左右两条数据. 进一步压缩了高度. 且通过二分法分隔数据. 并且通过指针指向匹配页的地址

B+树做的优化

1. 并不是所有节点都放真正的数据. 只有叶子节点才放数据. B+数是高度是最低的
2. 天然排好了的顺序. 右边的值永远比左边的值要大. 且叶子节点之间都存在指向关系.
    在范围查询中比B树更高级(一旦找到按一个树叶节点. 就不需要通过树根再查起了)

innodb存储引擎索引分类

1. hash索引: 更适合等值查询. 不适合范围查询. 为什么???最下面文章有解释
2. B+数索引: 适合范围查询
    聚集索引/聚簇索引 -> 以主键字段为值作为key创建的索引(一张表中只有一个)
    辅助索引 -> 针对非主键字段创建的索引(一张表中可以有多个). 存索引字段数据当key. values是主键字段

innodb -> 索引组织表

select name, age, gender from user where id = 3; -- 聚集索引
select name, id from where name = "maxs_hu"; -- 覆盖索引
select name, age, gender from user where name = "maxs_hu"; -- 需`要先查到id. 在聚集索引查询. 属于回表查询
回表查询: 通过辅助索引拿到主键值. 然后再回到聚集索引从根再查一下
覆盖索引: 不需要回表就能拿到你要的全部数据

索引使用

250万条记录 -> ibd文件为167M

索引的简单使用

create table t1(
id int,
name varchar(16)
);

create index idx_id on t1(id); -- 添加索引
drop index idx_id on t1(id); -- 删除索引

命中索引也未必能起到很好的提速效果

​1. 对区分度高和且占用空间小的字段建立索引

​2. 针对范围查询命中了索引. 如果范围很大. 查询速度依然很低. 如何提速:

​ -> 缩小范围

​ -> 分段取值. 一段一段取值最后将值全部取完

  1. 索引下推技术(默认开启)

  2. 不要把字段放到函数中或者参与运算

​ select * from t1 where id * 12 = 24; -- 错误用法. 每个都要计算
​ select * from t1 where id = 24/12; -- 这样预算可以提速

  1. 索引覆盖: 辅助索引不包含所有的字段. 其大小要远远小于聚集索引. 因此可以减少大量io操作

  2. 最左前缀匹配原则
    create index idx_id_name_gender on s1(id, name, gender);

当我们创建一个联合索引的时候，如(id, name, gender)，相当于创建了（id）、(id, name)和(id, name, gender)三个索引，这就是最左匹配原则。

  1. 限制索引的数目. 不要每列都创建索引
    每列创建索引都需要占用磁盘空间. 索引越多. 需要的磁盘空间越大.
    修改表时. 对索引的重构和更新很麻烦. 越多的索引. 会使更新表变得很浪费时间

慢查询优化的基本步骤

​ 0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
​ 1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
​ 2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
​ 3.order by limit 形式的sql语句让排序的表优先查
​ 4.了解业务方使用场景
​ 5.加索引时参照建索引的几大原则
​ 6.观察结果，不符合预期继续从0分析

推荐文章

https://www.cnblogs.com/linhaifeng/protected/articles/14425413.html

相关文章