MySQL优化

MySQL优化

1、索引的本质

索引是帮助MySQL高效获取数据的排好序的数据结构,索引也是存储在磁盘上的。

索引的数据结构:

• Hash索引

  • 对索引的key进行一次hash计算就可以定位出数据存储的位置
  • 很多时候Hash索引要比B+Tree索引更高效
  • 仅能满足"=",“IN”,不支持范围查询
  • hash冲突问题

• 二叉树
  采用二叉数，给Col2构建索引:没有索引查询Select * from table where Col2 = 89需要6次查找，而采用索引之后，只需要2次查找。
  
  链接:数据结构可视化网站
  而事实上索引的底层数据结构并没有采用二叉树，主要是因为二叉树对于单向递增的数据无法起到减少查找次数的作用。，比如若此处我们选择二叉树对Col1建立索引，那么我们建立的索引将起不到任何作用。
  

• 红黑树
  红黑树相较于二叉树做了平衡优化，但是在数据量大的情况下，树的高度不可控。因此，索引底层的数据结构并没有采用红黑树。
  

• B-Tree

  • 叶节点具有相同的深度，叶节点的指针为空
  • 所有索引元素不重复
  • 节点中的数据索引从左到右递增排序

• B+Tree(B-Tree变种)
  • 非叶子节点不存储data,只存储索引(冗余)，可以放更多的索引。
  • 叶子节点包含所有索引字段。
  • 叶子结点用指针连接，提高区间访问的性能(方便实现范围查找)。
    
    注意：MySQL页节点的大小设置为16KB，大概可以放2000多万数据，而其高度为3，3次磁盘IO就可以查找到。
• 数据库引擎是针对数据库表来说的：

• MyISAM存储引擎
• 数据文件和索引文件是分离的(非聚集)
  
• MyISAM存储引擎的查找方式，首先根据MYI文件(索引文件)查找数据对应的地址，然后再根据地址去MYD文件(数据文件)里查找数据。
  
• InnoDB引擎
• InnoDB索引实现(聚集)
  • 表数据文件本身就是按照B+Tree组织的一个索引结构文件
  • 聚集索引-叶节点包含了完整的数据记录
    注意
  • 为什么建议InnoDB表必须建立主键，并且推荐使用整形的自增主键?
    • 因为InnoDB引擎在存储数据的时候必须要用一棵B+Tree来组织，如果我们建立了主键，MySQL就以主键作为索引来组织B+Tree来存储，而且根据B+Tree的特性，从左到右排好序，所以自增的主键作为索引更加方便MySQL采用主键索引的方式进行数据存储。如果我们没有建立主键,MySQL则会去找一列没有重复数据的列作为索引，如果没有找到合适的列，MySQL则会创建一个隐藏列作为索引。采用自增的数据一直向后追加，构建B+Tree的效率比非自增的导致分裂的效率高。
  • 为什么非主键索引(二级索引)结构叶子节点存储的是主键值？
    • 一致性和节省存储空间。
• 联合索引的底层存储结构长什么样？
  
  • 注意：联合索引是按照联合索引的字段顺序从左到右进行维护的，例如，首先按照name进行排序，如果name相等，再按照age进行排序，如果name和age均相等，再按照position进行排序。
• 最左前缀原则:
  MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，以最左边的为起点任何连续的索引都能匹配上,同时遇到范围查询(>、<、between、like)就会停止匹配，当我们使用索引的时候一定要按照建立索引的顺序从左向右进行使用。
• 关于二级索引(非聚集索引)：
  • 注意：聚集索引的叶子节点存储的是完整的数据对象，而非聚集索引的叶子节点存储的是数据对象的地址或者数据对象的部分信息，比如id.
    
• 二级索引通过查询得到所差对象的id，然后进行回表操作，去对象所在的数据表中查询完整的数据对象。
• InnoDB引擎的表中，一般情况下只有主键索引是聚集索引，其余的索引都是非聚集索引，主要目的是为了节省空间。

2、Explain详解与索引最佳实践

• 实验sql表的创建
  actor表sql:

create table actor(
   id int(11) not null,
   name varchar(45) default null,
   update_time datetime default null,
   primary key (id) 
)engine=InnoDB default charset=utf8;