Mysql -- 索引原理以及查询优化

Mysql中比较常用MyISAM和Innodb这两种引擎，这两种引擎都采用了B+树结构去处理索引。
对于B-树、B+树的内容，可以参考下面两篇文章

https://www.cnblogs.com/h–d/p/14022357.html

https://www.cnblogs.com/makai/p/10861296.html

MyISAM引擎

• MyISAM是通过非聚集索引的方式来实现引擎的，即数据和索引是分开的两个文件。
• 建表时，默认会用主键来建立索引的B+树
• 索引树的叶子节点保存着对应数据的物理内存地址，所以按照索引查找时，先根据要查询的条件在索引树中找到具体的数据物理地址，然后再拿着物理地址去数据文件中找到需要的信息
  
  因为，MyISAM索引文件中存储的是物理地址，拿到物理地址后直接查询对应数据，所以MylSAM在查询时效率是很高的。但是MyISAM是不支持事务的，因此对应一些不涉及到事务的业务我们可以将数据库引擎设置为MyISAM来提高查询效率。

Innodb引擎

• Innodb是通过聚集索引的方式来实现引擎的，数据和索引在同一个文件里面。
• 建表时，默认会用主键来建立索引的B+树，如果没有设置主键，会使用不可见的内置自增主键
• 主键索引的叶子节点包含着该主键对应的数据内容
• 辅助索引的叶子节点是对应主键的值，为了空节省内存空间
• 因此根据主键查询时，筛选过后我们可以直接获取到数据，而根据辅助索引则分为两种情况：
  1、查询的字段全部在使用的索引中，找到叶子节点后从叶子节点中可以直接获取所有索引字段的内容直接返回
  2、查询的字段不存在在索引中，找到叶子节点后从叶子节点的数据中获取到主键，然后拿着主键回表，走主键索引查询数据
  

提高查询效率

索引优化

当我们要为某些字段创建索引时要注意

• 该字段最好不要存在NULL值
• 字段内容不宜过长
• 字段类型越简单越好 例如性别 女 男 可以使用 0 1 int 类型代替 尽量避免直接保存汉字
• 字段内容变化较少的字段索引效率比较高，例如性别一般只有两种 女0 男1
• 联合索引的最左侧一般是最常用到的字段
• 一张表的索引个数一般控制在 6 个左右，否则会影响插入等功能的效率

查询优化

• 尽量避免 select * ，特别是我们需要的字段是索引能全部包含的字段时，要写明字段 select a,b,c from
• 当只查询一条信息时，使用 limit 1
• 不要用索引使用函数 例如 where YEAR(date) ,会使索引失效
• 尽量不要使用左模糊 like ‘%小明’ ，左模糊会使索引失效
• 可以使用右模糊 like ‘小明%’，特别是当字段内容很长时，效率比全文检索要高
• 使用查询缓存优化缓存，当查询条件中包含函数时，一般是不会缓存的，因此对于带有函数的查询，我们最好将函数计算出来的值提前计算好带入条件中
• order by 后面的字段也要考虑索引的使用
• in 、 exsit 用小表驱动大表 （针对有索引的字段）
  ①select * from a where a.user_id in (select user_id from b)
  ②select * from a where exsit (select 1 from b where user_id=a.user_id)
  对于 ① 会先执行in里面的语句 select user_id from b 然后拿着这个结果去 检索 a 表，因此适合 b 表较小的情况
  对于 ② 中 exsit 语句其实就是检索 a 表的一个条件，会先执行 select a 然后拿着a中的 user_id 去判断 exsit 语句是 true 还是false，适合 a 表比较小的查询

其他

• 对表格进行定时备份，单表大小控制在1000万行以内，大表可能需要保留的更少
• 对表进行分区，例如按照日期分区，每隔一年建立一个分区，2020-01-01 ~ 2021-01-01 是pt0 2021-01-01 ~ 2022-01-01 是pt1，这样我们在查询时可以按照时间条件先判断我们要查找的数据在那个分区里面，指定分区查询：
  SELECT a,b,c FROM table_name PARTITION ( pt0);