Mysql底层剖析——Mysql性能优化篇

---

---

一、从设计数据库来优化

1、变小的通常更好
        尽量使用正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少

2、简单就好
        比如使用整型而不是varchar类型存储IP地址

3、尽量避免使用Null
        因为查询中包含NULL的列，通常来说更难优化，可为Null的列会使用更多的存储空间，在MySQL里也需要特殊处理。

4、选择合适的表示列

5、整数通常是标识列最好的选择

6、避免使用ENUM和SET作为标识列

7、避免使用字符串类型作为标识列，因为它们很占用空间

8、避免太多的列
        MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，从行缓存中将编码过的列转换成数据结构的操作代价是非常高得。MyISAM的定长行结构实际上与服务器层的行结构总是需要转换。然而，MyISAM转换的代价依赖于列的数量，所以不应设置太多的字段，因用多少设置多少。

9、避免太多的关联如果希望查询执行的快速并且并发性号，单个查询最好在12个表以内做关联。

10、避免使用枚举，但特定情况使用枚举也挺好用。

11为什么通常不选用uuid作为索引？
        因为uuid是无序的，插入时容易导致页分裂，也分裂会导致移动大量数频繁的页分裂，页会变得稀疏并不规则地填充，最终数据会有碎片写入的目标页可能已经刷到磁盘上并从缓存中移除，或是还没有被加载到缓存中，InnoDB在插入前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机I/O

12、使用范式
范式

后一个范式都是在满足前一个范式的基础上建立的

        1NF:无重复的列.表中的每一列都是不可分割的基本数据项.不满足1NF的数据库不是关系数据库.
如联系人表(姓名,电话),一个联系人有家庭电话和公司电话,则不符合1NF,应拆分为(姓名,家庭电话,公司电话).

        2NF:属性完全依赖于主键.不能存在仅依赖于关键一部分的属性.
如选课关系(学号,课程名称,成绩,学分),组合关键字(学号,课程名称)作为主键.其不满足2NF，因为存在决定关系:课程名称->学分,即存在组合主键中的部分字段决定非主属性的情况.会导致数据冗余,更新/插入/删除异常.

        3NF:属性不传递依赖于其它非主属性.非主键列必须直接依赖于主键，而不能传递依赖。即不能是：非主键A依赖于非主键B，非主键B依赖于主键.
如学生表(学号,姓名,学院编号,学院名称),学号是主键,姓名、学院编号、学院名称都完全依赖于学号,满足2NF,但不满足3NF,因为学院名称直接依赖的是学院编号 ,它是通过传递才依赖于主键.

        范式可以避免数据冗余，减少数据库的空间，减轻维护数据完整性的麻烦.但等级越高的范式设计出来的表越多,可能会增加查询所需时间。当我们的业务所涉及的表非常多，经常会有多表连接，并且我们对表的操作要时间上要尽量的快，这时可以考虑我们使用“反范式”.也就是用空间来换取时间,把数据冗余在多个表中,当查询时可以减少或者是避免表之间的关联.

        反范式：用空间换时间
        比如两个表(用户id,好友id)和(用户id,用户昵称,用户邮箱,联系电话)符合3NF,如果需查询某个用户的好友(昵称)名单,此时需对2个表进行连接查询,可以把第一个表修改成(用户id,好友id,好友昵称)这样只需要查询第一个表就可获取所有好友昵称.。

范式的优点和缺点
        范式化的更新操作通常比反范式化要快
        当数据较好地范式化是，就只有很少或者没有重复数据，所以只需要修改更少的数据
        范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快
        缺点：通常需要关联

反范式
        能够很好地避免关联，将所有数据放在一张表里，可以使用索引。

13、加快ALTER TABLE 操作的速度
MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。

可通过下列两种方法解决
        一种是现在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换
另一种是“影子拷贝”，用要求的表结构创建一张和源表无关的新表，通过重命名来删除表操作交换两张表。
直接修改.frm文件会很快，但是Mysql不支持

csdn：https://blog.csdn.net/prstaxy/article/details/38417599

二、索引的优化

介绍索引的优化前先介绍两个数据结构

1、B-树

特点：所有的叶子结点具有相同的深度，等于树高h
Key和指针互相间隔，节点两端是指针
一个节点中的key从做到右非递减排列
所有节点组成树结构
指针要么weinull，要么指向下一个节点

2、B+树

MySQL使用的的是B+树


        这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

        同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

        同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
        MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

        上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

        第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。查询数据时会触发回表操作。例如，图11为定义在Col3上的一个辅助索引：

        聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

Csdn: https://blog.csdn.net/u013967628/article/details/84305511

        不是所有类型的索引都能成为覆盖索引，覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引都不存储索引列的值。

        InnoDB存储引擎的回表：在二级索引上根据索引值，找到对应的主键，然后再去一级索引上根据取到的主键找到对应的数据记录。

那么B树和B+树的区别在哪呢？

        B+跟B树不同B+树的非叶子节点不保存键值对应的数据，这样使得B+树每个节点所能保存的键值大大增加；
        B+树叶子节点保存了父节点的所有键值和键值对应的数据，每个叶子节点的关键字从小到大链接（这个特性对范围查找特别有利，范围查找只需要遍历链表即可，并不用像b树一样回旋查找，例如先查找5，再查找6，再查找7）

        B+树的根节点键值数量和其子节点个数相等;

        B+的非叶子节点只进行数据索引，不会存实际的键值对应的数据，所有数据必须要到叶子节点才能获取到，所以每次数据查询的次数都一样；

MySQL为什么使用B+树来实现？
        1.B+树能显著减少IO次数，提高效率
        2.B+树的查询效率更加稳定，因为数据放在叶子节点
        3.B+树能提高范围查询的效率，因为叶子节点指向下一
个叶子节点
        4.树高决定IO次数，降低树高就能减少IO次数
Csdn：https://blog.csdn.net/zzti_erlie/article/details/82973742

3、选择合适的索引

1、不重复的索引值/数据表记录的总数，索引的选择性越高查询效率越高，唯一索引的选择性是1.
2、不一定将选择性最高的列放在前面，具体情况具体而定
3、延迟关联
创建索引（actor，title，prod_id）

        这种方式叫作延迟管理，因为延迟了对列的访问，在查询的第一阶段可以使用覆盖索引，然后在From子句的子查询中找到匹配的prod_id,然后根据这些prod_id值在外层循环匹配到需要的所有列值，虽然无法使用索引覆盖整个查询，但是总比无法完全利用索引覆盖的好。

4、索引失效

1、使用like“%XX”前缀无法匹配造成失效
2、不遵循最左匹配原则
3、输入的类型不匹配，比如String类型输入了int类型
4、asc和desc混用
5、in会走索引，但是in里的范围太大索引会失效
6、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

5、正确的使用索引

        MySQL有两种方式可以生成语序的结果，通过排序操作，或者按照索引顺序来扫描，如果EXPLAIN出来的type列的值为index，则说明MySQL使用了索引扫描来做排序，注意要和Extra列的Using index区分。气体室要索引覆盖，如果不索引覆盖，就不得不没扫描一条索引记录就回表查询一次锁对应的行，这基本就是随机IO了，此时按索引顺序读取数据的速度通常比顺序扫描全表慢。

        只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样是，MySQL才能够使用索引来对结果做排序。

        ORDER BY也是需要满足最左前缀的要求，当前导列为常量的时候也可以不满足最左前缀的要求。
索引如下

三、查询性能优化

慢查询优化

1、查询了不需要的记录
        MySQL是先返回结果集再进行计算，在新闻网站取出100条记录但是只是在页面上显示前面10条，但是实际上会查询出全部的结果集，客户端的应用程序会接受全部的结果集数据，然后抛弃其中大部分数据，最简单的方法就是在查询后面加上LIMIT。

2、多表关联是返回全部列

3、总是取出全部列
        避免使用SELECT *

4、重复查询相同的数据