Mysql性能优化学习笔记

1、索引的本质

索引：
    帮助Mysql数据库搞笑获取数据排好序的数据结构。

索引的数据结构：
    1. 二叉树
    2. 红黑树
    3. Hash
    4. B-Tree

2、索引数据结构的解析

2.1 二叉树

二叉树：
    由于在某种特定的情况下，二叉树可能会退还成链表（如：按照元素大小插入时），因此MYSQL索引并为选择二叉树作为其底层数据结构。

2.2 红黑树

红黑树：又名二叉平衡树
    红黑树由于层级太多，当插入的数据过大时性能较低。红黑树与二叉树的区别就是会自旋，当某个子节点单边元素过多时会进行自旋进行节点调整。

2.3 Hash表

Hash表：
    Hash表不能满足范围查找问题，但是Hash表结构的索引可以快速的查找指定的索引的数据。因为Hash表结果的索引是通过Hash算法计算出结果然后与数据存储的磁盘地址做唯一的映射。

2.4 B-TREE

B-TREE：
    1. 叶节点具有相同的深度，叶节点的指针为空
    2. 所有索引元素不重复。
    3. 节点中的数据索引从左至右递增排序。
    
B+Tree：（Mysql索引使用的数据结构）
    1. 非叶子节点不存储date，只存储索引（冗余），可存放更多的索引。
    2. 叶子节点包含所有索引字段。
    3. 叶子节点用指针连接，提高区域间访问的性能。
注：Mysql默认使用的节点的大小为16kb。

查询Mysql每页节点的大小的命令：

SHOW GLOBAL STATUS LIKE "Innodb_page_size"

3、B树结构详解

B+Tree索引结构图：

4、MyIsam存储引擎

MyIsam存储引擎：
    1. MyIsam索引文件和数据文件是分离的（非聚集），简单的来说就是非聚集索引是索引文件和数据文件分离。
    2. MyIsam存储引擎的叶子节点存储的是当前索引指向数据在磁盘文件中所在的位置。在MyIsam存储引擎中，根据索引查询时首先查询的是.MYI文件中索引所对应的数据的文件位置，然后通过该位置查询.MYD文件中的数据。
    
注：数据库存储引擎形容的数据库表级别，而不是数据库级别

5、Innodb存储引擎

InnoDB存储引擎：
    InnoDB存储引擎的叶子节点存储的是当前索引所对应的完整数据，文件为.IDB后缀名的文件，该文件中即存储了索引也存储了数据。
    
InnoDB索引实现（聚集）：
    1. 表数据文件本身就是按B+Tree组织的一个索引结构文件。
    2. 聚集索引-叶节点包含了完整的数据记录，换句话说就是聚集索引的数据和索引是在一个文件中的。InnoDB中的主键就是聚集索引。
    3. InnoDB表必须有主键并且推荐使用整型的自增主键。因为InnoDB引擎底层实现时数据的存储必须有一个B+Tree来组织所以需要有主键，如果创建表的时候没有创建主键Mysql数据库会在表字段中选择一个不重复的字段作为主键，如果没有满足条件的字段则Mysql会自己创建一个隐藏列用于当作主键。另外之所以推荐使用整型是因为在查找索引的时候需要有大量的比对操作，而整型比对速度性能远高于字符串类型数据而且整型数据占用的空间远小于字符串类型。自所以推荐自增是因为索引B+Tree结构的叶子节点时顺序排列的，如果不自增的那么插入的数据会导致B+Tree重新排列影戏性能。
    4. 非主键索引结构叶子节点存储的是主键，主要为了一致性和节省存储空间。

6、Mysql索引优化和底层数据结构

6.1 联合索引：

联合索引的需要遵循的一些原则：
    1. 索引最左前缀原则，因为联合索引中索引是由多个字段组从左至右组成，在比对索引的时候也是从第一个字段开始比对。
    2. 不在索引列上做任何操作（计算，函数，类型转换等），会导致索引失效而转向全表扫描。
    3. 存储引擎不能使用索引中范围条件右边的列。
    4. 尽量使用覆盖索引（只访问索引的查询，索引列包含查询列），减少select * 语句。
    5. Mysql在使用不等于(!= 或 <>)的时候无法使用索引会导致全表扫描。
    6. IS NULL、IS NOT NULL一般情况下也无法使用索引。
    7. LIKE语句以通配符开头的Mysql索引失效会变成全表扫描。
    8. 字符串不能单引号索引失效。
    9. 少用OR或IN语句，因为使用它们时Mysql不一定使用索引，Mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。

以上为个人总结的一些笔记，若有雷同纯属巧合，若有错误欢迎指出，谢谢！