MySql索引笔记

Aze
182 阅读4分钟

1、为什么需要目录？

揭示内容结构、便于快速检索【注:目录是“目”和“录”的合称，目指篇目、录指序录。】

2、设置目录的原则是什么？

【思】为什么有些书目录只显示第x章，没有二级标题，而有些书则需要设置二级、三级甚至四级标题呢？ 概括性高、顾名思义

一、为什么需要索引？

加快对表中记录的查找或排序（注意：不仅可以快速查找，也要排序）

二、实现原理

一种排序的数据结构，通常是B-树及其变种的B+树（以空间换时间），会用额外的磁盘空间来存储索引数据。

为什么使用B+树来实现索引呢？

  • 与二叉树相比，B-tree身型更为矮胖，所以可以减少磁盘的io，提高速度；
  • B+tree的关键字都分布在叶节点（关键字可能重复出现在枝节点），B-tree的关键字散落在所有节点（关键字全局唯一）；
  • B+树的内部节点不会存储指针信息，只做索引使用，因此可以用更少的内存加载更多的索引数据；
  • B+树查询效率更加稳定，而且所有叶子节点通过指针连在一起，更方便遍历；（对于一个系统而言，公平与稳定优于时好时坏）

三、建立原则

  • 查询频次很高：where、order by、group by所涉及的字段，主键+外键必须有索引
  • 列值的区分度较高、字段小：比如性别，只有男、女，即使建立了索引，也没什么效果，而像text、image等大字段类型就不适合作为索引，当查询数据超过总数据的20%时，索引就会失效
  • 查多改少的列适合：索引的维护不仅需要空间，更需要时间，经常被修改的列，不适合建立索引

四、三大类型

  • ️mysql5.0以下，一次查询对于一个数据表只能使用一个索引
  • 唯一索引：任何2列都不能重复，必须保证取值的全局唯一性
  • 主键索引：定义主键将自动生成主键索引
  • 聚集索引：定义了表中数据的物理存储顺序，所以一个表只能包含一个聚集索引，表中行的物理顺序与键值的索引顺序相同。
  • innodb先找主键、其次找第一个唯一非空索引、最后实在没办法了，就在自己内部生成一个隐藏的主键作为聚集索引

五、实例分析

  • 使用explain分析sql查询效率；
  • 使用show index from table_name查看索引的散列程度
  • 多列索引使用
  • 全字段匹配（必须是全部字段，依次匹配，不能跳过中间字段）
  • 匹配部分最左前缀
  • 匹配第一列（部分匹配或范围匹配）
  • 精确匹配左前列并范围匹配另外一列
  • 最左前缀原则：区分度高的列左移

举例：假设建立了索引（province，city，county），这意味着我们同时建立了（province）单列索引、（province，city）组合索引、（province、city、county）组合索引。因此，如果你使用（city，county）或（province，county）作为查询条件，则不会使用索引。所以，如果你要给已有索引的表添加新的索引，那么就要考虑新增的索引是否是已有多列索引的最左前缀。如果是这样，则不用再增加索引。 特别注意：最左前缀原则与sql语句的写法顺序没有关系！mysql优化器会自动处理优化

六、常见误区

  • like %keyword不会使用索引，like keyword%会使用索引
  • order by排序时，多个等于条件查询就是范围查询，没有order by排序就没有限制
  • 如果某一列是范围查询（>、<、like、between），那么其右边所有列无法使用索引（如果in后面所有列都是等值匹配，则in依旧使用索引，=和in可以乱序）

七、注意事项

INNODB的索引会限制单独Key的最大长度为767字节，超过这个长度必须建立小于等于767字节的前缀索引。 blog.51cto.com/ustb80/1073… dba.stackexchange.com/questions/1…

八、FAQ：

主键和索引是什么关系？ 一次查询可以用多个索引吗？ 为什么是最左前缀原则？

九、参考资料：

avatar
文章
阅读
粉丝