MYSQL 索引优化知识点整理

索引就是排好序的数据结构
索引可以用最快的时间定位到数据，索引中包含了 文件的指针，能知道磁盘的位置，然后磁盘（io）寻道旋转，获取数据

mysql索引类型

• 为什么不用二叉树？

  • 极端情况下会退化为单链表，查询效率低

• 为什么不用红黑树？

  • 会对数组做二叉平衡，数据量大的时候深度会变得很大，查询效率依旧很低

• B+树索引

  • 数据量大时可以增加度，让树的高度控制的比较低（一般3到5之间）

  • 度不能无限扩大，每一次磁盘io查找一页（约为4k的数据）

    
    
    度为4的 B+索引

• 为什么hash索引不常用？

  • 优势
    • 通过一次hash运算定位到磁盘的具体位置，查找速度非常快
  • 弊端
    • Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询
    • Hash 索引无法被用来避免数据的排序操作
    • Hash 索引不能利用部分索引键查询
    • Hash 索引在任何时候都不能避免表扫描
    • Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高

• InnoDB 索引实现

  • 聚集索引（包含了完整的数据）
  • 必须有主键，如果没有创建，则会默认创建一个主键类型为整型（推荐用自增的主键，新插入的数据可以连续的插入到磁盘空间，而且查找比较方便 ）
  • 非主键索引结构叶子节点存储的是主键值

• MyISAM 索引实现

  通过B+树找到节点，节点中存储的是文件指针，在通过文件指针定位到该行数据

  • 非聚集索引（索引文件和数据文件是分离）

• 索引使用

  使用EXPLAIN关键字分析结构的性能瓶颈

  • 全值匹配
  • 最佳左前缀法则
  • 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引中范围条件右边的列
  • 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句
  • mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
  • is null,is not null 也无法使用索引
  • like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作
  • 字符串不加单引号索引失效
  • 少用or,用它连接时很多情况下索引会失效

图片.png

• 索引使用口诀

  全值匹配我最爱，最左前缀要遵守；
  带头大哥不能死，中间兄弟不能断；
  索引列上少计算，范围之后全失效；
  LIKE百分写最右，覆盖索引不写星；
  不等空值还有or，索引失效要少用。