高级程序员必须会的MySQL性能优化原理
内容点
- 从三驾马车开始性能分析
- 掌握expain查询计划中透露出来的信息
- MySQL的索引在执行过程中是如何被使用的
- MySQL中的表现数据和索引在底层是如何存储的
- 如何正确使用索引，才不会引起索引失效
设计知识点
- 最左前缀原则，在组合索引中的正确使用
- 通过B+Tree读取数据和索引的好处
- 聚集索引和非聚集索引对于存储数据和索引的区别
MySQL课程主题和目标-1
主题
使用MySQL索引进行性能优化？
目标
1、性能分析要从三驾马车开始（慢查询日志、explain执行计划、show profile）
2、有了性能分析报告，接下来如何对性能进行优化呢？
3、我们一定要搞清楚通过explain显示查询计划中关于索引是否被用到
4、你知道MySQL的索引在执行过程中是如何被使用的吗？
5、你知道MySQL中的表数据和索引在底层是如何存储的吗？
6、你了解聚集索引(IOT索引组织表)和非聚集索引（堆组织表）的存储方式的不同吗？
7、你知道聚集索引中的主键索引和辅助索引是如何存储索引和数据的吗？
8、你会正确使用组合索引吗？
9、你明白最左前缀原则是怎么回事吗？
10、你明白深是索引覆盖吗？索引覆盖如何优化检索性能？
11、如何正确使用索引，才不会引起索引失效？
MySQL架构介绍、存储引擎介绍、日志文件、数据文件介绍-2
MySQL架构篇
存储引擎介绍：
- MySQL存储引擎类
| 存储引擎 | 说明 |
| MyISAM | 高速引擎，拥有较高的插入，查询速度，但不支持事务 |
| InnoDB | 5.5版本后MySQL的默认数据库，支持事务和行级锁定，比MyISAM处理速度稍慢 |
| ISAM | |
| MRG_MyISAM(MERGE) | 将多个表联合成一个表使用，在超大规模数据存储时很有用 |
| Memory | 内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失 |
| Falcon | 一种新的存储引擎，支持事务处理，传言可能是InnoDB的替代者 |
| Archive | |
| CSV |
查看存储引擎：
mysql> show engines;
物理文件
日志文件（顺序IO）
错误日志（error log）
二进制日志（bin log）
通用查询日志（general query log）
慢查询日志（slow query log）
默认是关闭的
重做日志（redo log）
作用：
1、确保事务的持久性
2、防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性
回滚日志（undo log）
作用：
1, 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（mvcc），也即非锁定读
中继日志（relay log）
是在主从复制环境中产生的日志
主要作用是为了从机可以从中继日志中获取到主机同步过来的SQL语句，然后执行到从机中。
MySQL性能分析之慢查询日志介绍和show profile介绍-3
MySQL性能分析篇
性能分析的思路
1、首先需要使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句 2、其次【查看执行计划】查看有问题的SQL的执行计划 3、最后可以使用【show profile[s]】查看有问题的SQL的性能使用情况
慢查询日志
数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化SQL。更重要的是得先找到需要优化的SQL。MySQL数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL语句，这将极大程度帮助我们快速定位到症结所在，以便对症下药。
至于查询时间的多少才算慢，每个项目、业务都有不同的要求。
MySQL的慢查询日志功能默认是关闭的，需要手动开启。
profile分析语句
Query Profiler 是MySQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的硬性性能瓶颈在什么地方。
通常我们是使用的explain，以及slow query log 都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU、IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MySQL5.0.37以及以上版本中才有实现，
默认情况请，MySQL的该功能没有打开，需要手动开启。
MySQL索引讲解-4
索引介绍
索引是什么
- 高效获取数据的数据结构
- 使用B+树结构（多路搜索树，并不一定是二叉的）
- 索引是存储在磁盘文件中的（可能在单独的索引文件中，也可能和数据一起存储在数据文件中）
索引的优势和劣势
优势：
- 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势：
- 索引会占据磁盘空间
- 索引虽然会提供查询效率，但是会降低更新表的效率（为什么呢？答：更新的列上建有索引，数据库不但要更新表里的数据，而且要更新相应的索引。所以这个索引就会降低update执行的效率。）
常用索引分类：
单列索引
- 普通索引：MySQL中基本索引类型
- 唯一索引：索引列中的值必须唯一的，但是允许为空值
- 主键索引：是一种特殊的唯一索引， 不允许为空
组合索引
- 在表中的多个字段组合上创建的一个索引
- 组合索引的使用，需要遵循最左前缀原则
- 一般情况下，建议使用组合索引代替单列索引
索引的存储结构
索引存储结构介绍
- 索引是在存储引擎中实现的
- MyISAM 和 InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换
B树和B+树
数据结构示例网站：https://www.cnblogs.com/nullzx/p/8729425.html
B树
B树是为了磁盘或其他存储设备而设计的一种多叉平衡查找树。
- B树的高度一般都是在2-4这个高度，树的高度直接决定IO读写的次数以及查询的时间复杂度（log(n)）
- B树三层可以存储bigint类型的主键10亿条
- 如果是三层树结构 --- 支撑的数据可以达到20G，如果是四层树结构 --- 支撑的数据可以达到几十T
B+树
B树和B+树的区别
B树和B+树的最大区别在于非叶子节点是否存储数据的问题
B树是非叶子节点和叶子节点都会存储数据
B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，是有顺链表
聚集索引（InnoDB）
Cluster Index ： 聚簇索引（索引组织表）
InnoDB存储引擎的数据组织方式，是聚簇索引表：完整的记录，存储在主键索引中，通过主键索引，就可以获取记录所有的列，也就是说表数据和索引是在一起，这就是聚集索引。
主键索引
如何使用索引
那些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 多表关联查询中，关联字段应该创建索引
- 查询中统计或者分组字段，应该创建索引
- 查询中排序的字段，应该创建索引
哪些情况不需要创建索引
- 表记录太少
- 经常进行增删改操作的表
- 频繁更新的字段
- where条件里使用频率不高的字段
explain查看执行计划-5
测试建表语句
CREATE TABLE USER ( id INT PRIMARY KEY, NAME VARCHAR (100), age INT, sex CHAR (1), address VARCHAR (100) );
添加索引
ALTER TABLE USER ADD INDEX idx_name_age (NAME(10), age); ALTER TABLE USER ADD INDEX idx_sex (sex);
特别说明：name列的长度是varchar(100),但是创建索引的时候，指定的长度却是10，这是使用了前缀索引这个概念，
插入数据
INSERT INTO user(id,name,age,sex,address) VALUES (1,'zhangsan',20,'0','xian');
参数说明：
system
表中只有一行数据或者是空表
const（重要）
使用唯一索引或者主键。返回记录一定是1行记录的等值where 条件时，通常type是const。
eq_ref （重要）
此类型通常出现在多表的join查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常是 = 。查询效率高。
ref （重要）
针对非唯一索引，使用等值（=）查询，或者是使用了最左前缀规则索引的查询。
组合索引
extra （重要）
这个列包含不适合在其他列中显示但十分重要的额外信息，这个列可以显示的信息非常多。
- using index
查询时不需要回表查询，直接通过索引就可以获取查询的结果数据。
- 表示相应的select查询中使用到了覆盖索引，避免访问表的数据行，效率不错
- 如果同时出现using where，说明索引被用来执行查找索引键值
- 如果没有同时出现using where ，表明索引用来读取数据而非执行查找动作
- using where
表示mysql将对storage engine提取的结果进行过滤， 过滤条件字段无索引
- using index condition（重要）
using index condition会优先过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用where 子句中的其他条件去过滤这些数据行
- using filesort（重要）
- 排序时无法使用到索引时，就会出现这个，常见与order by和group by语句中。
- 说明mysql会使用一个外部的索引排序，而不是按照索引顺序进行读取
- mysql中无法利用索引完成的排序操作称为“文件排序”
介绍
MySQL提供了一个EXPLAIN命令，它可以对SELECT语句的执行计划进行分析，并输出SELECT执行的详细信息，以供开发人员针对性优化
用法：
只需要在SELECT语句前加上EXPLAIN命令即可。
索引失效分析-6
性能优化手段-7
MySQL性能优化篇
服务器层面优化
将数据保存在内存中，保证从内存读取数据
- 设置足够大的 innodb_buffer_pool_size，将数据读取到内存中。
建议innodb_buffer_pool_size设置为总内存大小的3/4 或者 4/5
- 怎样确定innodb_buffer_pool_size足够大，数据是从内存读取而不是硬盘？
SHOW GLOBAL STATUS LIKE '%innodb_buffer_pool_page_%';
内存预热
将磁盘数据在MySQL server启动的时候，读取到内存中
降低磁盘写入次数
- 对于生产环境来说，很多日志是不需要开启的，比如：通用查询日志，慢查询日志，错误日志
- 使用足够大的写入缓存 innodb_log_file_size
推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size
- 设置合适的innodb_flush_log_at_trx_commit ，和日志落盘有关系
提高磁盘读写
- 可以考虑使用SSD硬盘。
SQL设计层面优化
具体优化方案如下：
- 设计中间表，一般针对统计分析功能，或者实时性不高的需求
- 为减少关联查询，创建合理的冗余字段，
- 对于字段太多的大表，考虑垂直拆表
- 对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表
- 每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型。建议自增主键（不考虑分布式系统的情况下）
SQL语句优化（开发人员）
索引优化
- 为搜索字段（where中的条件）、排序字段、select查询列，创建合适的索引，不过要考虑数据的业务场景，查询多还是增删多？
- 尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查询条件放到最左边
- 尽量使用覆盖索引，select语句中尽量不要使用 *
- order by、group by语句要尽量使用到索引
其他优化
- 尽量不适用count(*) 、尽量使用 count(主键)
count （*） ： 查询行数，是会遍历所有的行、所有的列 count （列） ： 查询指定列不为null的行数（过滤null），如果列可以为空，则count（*）不等于count（列），除非指定的列是非空的列才会让count（*）等于count（列） count（列） ： 比如 count（1）
- join两张表的关联字段最好都建立索引，而且最好字段类型是一样的
select * from orders o left join user u on o.user_id = u.id
orders 表中的user_id 和user 表中的id，类型要一致
- where 条件中尽量不要使用1=1 、 not in语句。建立使用not exists
- 不用MySQL内置的函数，因为内置函数不会建立查询缓存
SQL查询语句和查询结果都会在第一次查询只会存储到MySQL的查询缓存中，如果需要获取到查询缓存中的查询结果，查询的sql语句必须和第一次的查询sql语句一致
select * from user where birthday = now()
- 合理利用慢查询日志，explain执行计划查询、show profile查看sql执行时的资源使用情况