Mysql 优化

写在前面：该篇文章在博文"https://thinkwon.blog.csdn.net/article/details/104778621"的基础上进行编写和总结，如果有兴趣的同学可以阅读原博文，原博文描述的更全面

使用explain查看sql语句的执行计划，优化sql

一、分析纬度

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

二、语法

-- EXPLAN sql语句 
EXPLAIN select * from core_member_info;

三、各字段含义

• id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

1、id相同，执行顺序由上至下

2、id不同，id值越大优先级越高，越先被执行

3、id相同不同，同时存在，id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

4、id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

• select_type：分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

1、SIMPLE，简单的select查询，查询中不包含子查询或者UNION

2、PRIMARY，查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

3、SUBQUERY，在SELECT或WHERE列表中包含的子查询

4、DERIVED，在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

5、UNION，若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

6、UNION RESULT 从UNION表获取结果的SELECT

• table：指的是当前查询语句执行的表

• type：显示的是查询使用了哪种访问类型，非常重要，可以看到有没有走索引，性能比较：system > const > eq_ref > ref > range > index > all

1、system：表只有一行记录（等于系统表），平时不会出现

2、const，表最多只有一行匹配，通用用于主键或者唯一索引比较时

3、eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

4、ref，使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行（可能查询出多行数据）

5、range， 只检索给定范围的行，使用一个索引来选择行，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

6、index，扫描索引上的全部数据

7、all，将遍历全表以找到匹配的行

• possible_keys：显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

• key：实际使用的索引。如果为NULL，则没有使用索引

• ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
• rows：返回估算的结果集数目，并不是一个准确的值。
• filtered：查询条件过滤的行数的百分比
• extra：额外信息

            1、Using index 使用覆盖索引

            2、Using where 使用了用where子句来过滤结果集

            3、Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。

            4、Using temporary 使用了临时表

• 其他，sql优化的目标可以参考阿里开发手册

【推荐】 
SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好。 
说明：
 1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
 2） ref 指的是使用普通的索引（normal index）。
 3） range 对索引进行范围检索。 
反例： explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。

sql的生命周期

• 应用服务器与数据库服务器建立一个连接
• 数据库进程拿到请求sql
• 解析并生成执行计划，执行
• 读取数据到内存并进行逻辑处理
• 通过步骤一的连接，发送结果到客户端
• 关掉连接，释放资源

大表数据查询怎么优化

• 优化shema、sql语句+索引
• 第二加缓存，memcached, redis
• 主从复制，读写分离
• 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统
• 水平切分，针对数据量大的表，如将订单表按照月份拆分成多个表

超大分页

不允许直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存；

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.

在阿里巴巴《Java开发手册》中,对超大分页的解决办法是类似于上面提到的

【推荐】
利用延迟关联或者子查询优化超多分页场景。 
说明：
MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。 
正例：先快速定位需要获取的id段，然后再关联： SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

慢查询日志

当语句执行时间较长时，通过日志的方式进行记录，这种方式就是慢查询的日志

1、开启慢查询

-- 临时开启慢查询 
set global slow_query_log = on;  
-- 永久设置慢查询日志开启：修改配置文件/etc/my.cnf中对应的参数

2、设置慢查询时间临界点

-- 查询时间高于1的都会被记录到慢查询日志中 
set long_query_time = 1;

3、设置慢查询存储的方式
 

-- 默认是none，可以选择设置为table或者file； 
-- 设置为file表示慢查询日志是通过file体现的； 
-- 设置为table则慢查询信息会保存到mysql库下的slow_log表中 
set globle log_output = file; 

-- 查询慢查询存储的方式 
show variables like '%log_output%';

4、查询慢查询日志的开启状态和慢查询日志储存的位置

show variables like '%quer%';

主键自增ID和UUID选择

mysql主键可选择自增ID或通过mysql函数UUID()生成值，推荐使用自增ID，不要使用UUID，如果主键索引是自增ID，那么只需要不断向后排列即可，UUID随机生成，不能判断与之前主键的大小，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降

优化查询过程中的数据访问

 

• 访问数据太多导致查询性能下降，可以考虑进行横向或者纵向的分表。
• 确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
• 确认MySQL服务器是否在分析大量不必要的数据行
• 避免犯如下SQL语句错误：

        1、查询不需要的数据。解决办法：使用limit解决

       2、多表关联返回全部列。解决办法：指定列名

       3、总是返回全部列。解决办法：避免使用SELECT *

• 重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
• 是否在扫描额外的记录。解决办法：使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化：

         1、使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。

         2、改变数据库和表的结构，修改数据表范式，如用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

        3、重写SQL语句，让优化器可以以更优的方式执行查询。

       4、使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。

       5、将一个大的查询分为多个小的相同的查询

• 一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销
• 分解关联查询，让缓存的效率更高

优化特定类型的查询语句

• count(*)会忽略所有的列，直接统计所有列数，不要使用count(列名)
• MyISAM中，没有任何where条件的count(*)非常快。
• 当有where条件时，MyISAM的count统计不一定比其它引擎快。
• 可以使用explain查询近似值，用近似值替代count(*)
• 增加汇总表
• 使用缓存

优化关联查询

• 确定ON或者USING子句中是否有索引。
• 确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。

优化子查询

• 用关联查询替代
• 优化GROUP BY和DISTINCT
• 这两种查询据可以使用索引来优化，是最有效的优化方法
• 关联查询中，使用标识列分组的效率更高
• 如果不需要ORDER BY，进行GROUP BY时加ORDER BY NULL，MySQL不会再进行文件排序。
• WITH ROLLUP超级聚合，可以挪到应用程序处理

优化LIMIT分页

• LIMIT偏移量大的时候，查询效率较低
• 可以记录上次查询的最大ID，下次查询时直接根据该ID来查询

优化UNION查询

• UNION ALL的效率高于UNION

UNION：在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

UNION ALL：简单的将两个结果合并后就返回，不会进行数据去重和排序

优化WHERE子句

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
• 应尽量避免在 where 子句中对字段进行 null 值判断，可以设置默认值0，确保表中查询列没有null值，否则将导致引擎放弃使用索引而进行全表扫描
• 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
• 应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

select id from t where num=10 or num=20 
-- 可以这样查询： 
select id from t where num=10 union all select id from t where num=20

• in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不要用 in 了
• 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描
• 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

select id from t where substring(name,1,3)=’abc’ 
-- name以abc开头的id应改为: 
select id from t where name like ‘abc%’

• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如

select id from t where num/2=100 
-- 应改为: 
select id from t where num=100*2

• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引

数据库表结构设计和优化

1、将字段很多的表分解成多个表：可以将使用频率很低的字段分离出来形成新表

2、增加中间表：对于需要经常联合查询的表，可以建立中间表以提高查询效率，通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询

3、合理增加冗余字段：如两个表中都需要记录同一字段，可以在两个表中添加该列，避免较多的联合查询

数据库CPU飙升处理

1、使用top命令查看是什么引起的cpu飙升

2、如果不是mysql引起的，进行相关的处理

3、如果是mysql引起的，通过show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成，kill 掉这些线程同时观察 cpu 使用率是否下降，进行相应的查询优化后再重新执行这些sql，查看cpu是否有下降

mysql单表数据量太大处理

1、限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们默认控制在一个月的范围内

2、读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读

3、使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存

4、分库分表

• 垂直分表：数据库表列的拆分，表列拆分为多表

缺点： 1、主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决；2、垂直分区会让事务变得更加复杂

优点：1、可以使得行数据变小，在查询时减少读取的Block数，减少I/O 次数；2、此外，垂直分区可以简化表的结构，易于维护

• 水平分表：根据一列或多列数据的值把数据行放到两个独立的表中，如订单表根据月份拆分为多个表

缺点：1、给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作；2、在许多数据库应用中，这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数

优点：1、降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数

MySQL的复制原理以及流程

主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后从数据库将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

• 主从复制的作用

1. 主数据库出现问题，可以切换到从数据库。
2. 可以进行数据库层面的读写分离。
3. 可以在从数据库上进行日常备份。

• 主从复制解决的问题

1. 数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
2. 负载均衡：降低单个服务器的压力
3. 高可用和故障切换：帮助应用程序避免单点失败
4. 升级测试：可以用更高版本的MySQL作为从库

• 主从复制工作原理

1. 在主库上把数据更高记录到二进制日志（主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中）
2. 从库将主库的日志复制到自己的中继日志（从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中）
3. 从库读取中继日志的事件，将其重放到从库数据中（从：sql执行线程——执行relay log中的语句）