MySQL常见优化

1，MySQL版本：

• MySQL Community Server 社区版本，开源免费，但不提供官方技术支持。
• MySQL Enterprise Edition 企业版本，需付费，可以试用30天。
• MySQL Cluster 集群版，开源免费。可将几个MySQL Server封装成一个Server。
• MySQL Cluster CGE 高级集群版，需付费。
• MySQL Workbench（GUI TOOL）一款专为MySQL设计的ER/数据库建模工具。它是著名的数据库设计工具DBDesigner4的继任者。MySQL Workbench

又分为两个版本，分别是社区版（MySQL Workbench OSS）、商用版（MySQL Workbench SE）。

MySQL Community Server 是开源免费的，这也是我们通常用的MySQL的版本。根据不同的操作系统平台细分为多个版本，

5.X：
5.0-5.1：早期产品的延续，升级维护
5.4 - 5.x ：MySQL整合了第三方公司的新存储引擎 （5.5 5.7）

https://blog.csdn.net/vtopqx/article/details/87934889

2，mysql逻辑分层：连接层，服务层，引擎层，存储层

https://www.cnblogs.com/sunjingwu/p/9732371.html

3，存储引擎

• inndb
  事务有限 行锁
• myisam
  性能优先 表锁

查看当前的存储引擎：
show engines ;
show variables like '%storage_engine%'

4，SQL优化

原因：SQL问题（链接查询），索引失效，服务器参数设置问题（缓冲区，线程数设置不合理）

1，SQL：解析过程，from，on，join，where， group by， having. select, order by ,limit SQL解析过程
2，SQL优化，主要就是优化索引
3，什么是索引：相当于书的目录，索引是帮助MySQL高效获取数据的数据结构 ，索引是数据结构（默认B树）
4，索引本身很大，需要占用内存和硬盘。不是所有情况都适用，频繁更新的字段不适合

建立索引会降低增删改的效率

B树：https://www.bilibili.com/video/av36069871?from=search&seid=1181095641637380064

5，索引

• 单值索引：单列，一个表内可以多个单值索引
• 唯一索引：不能重复
• 复合索引：多个列构成的索引

6，SQL性能问题

1，分析SQL的执行计划：explain 可以模拟SQL优化器执行SQL语句

• explain + sql语句 查询执行计划
• select_type :查询类型
• table ：表
• type ：类型
• possible keys ：预测用到的索引
• key： 实际使用的索引
• key_len ：实际使用索引的长度
• ref：表之间的引用
• rows：通过索引查到的数据量
• extra：额外的信息

2，explain详解：

id：

值相同 从上往下，顺序执行
多表内连接查询，表执行顺序，因数量的个数改变而改变的原因：笛卡尔积
数据小的表，优先查询
id值不同，越大越优先查询

select_type

• primary 包含子查询SQL中的主查询（最外层）
• subquery 包含子查询SQL中的子查询（非最外层）
• simple：简单查询，不包含子查询，union
• derived：衍生查询，查询的时候用到了临时表
• from 子查询 中只有一张表，临时表 select * from（select * from t where t.id in (1,2)）tr
• 在from 子查询中，如果有两张 两张表 union查询，则前面的查询是衍生查询，第二张表则叫union ,
  select * from（select * from t where t.id = 1 union select * from t where t.id = 2）tr
• union：union查询的除最左表的右表则是union查询
• union Result：指union 连接出来的临时表

type

越左性能越好
system> const>eq_ref>ref>range>index>all
system,const 只是理想情况，实际能达到ref>range

要对type进行优化的前提 是有索引

• system：只有一条数据的系统表，或衍生表只有一条数据的主查询（select * from (select * from t ) t where id = 1） t表只有一条数据

• const：仅仅能查到一条数据的SQL，用于primary key 或unique 索引

• eq_ref：唯一性索引，对于每个索引键的查询，返回匹配唯一行数据，有且只能有一条数据
  常见于唯一索引，和主键索引

• ref：非唯一性，对于每个索引键的查询，返回匹配的所有行（0，多）
  select * from t where name = 'zs' zs 不唯一

• range：检索指定范围的行，where 后面是范围查询（between，in ，> < ）in可能会失效变成无索引all

• index：查询全部索引中数据

• all：查询全部表数据

type——小结

system/const：结果只有一条数据
eq_ref：结果多条，但是每条数据是唯一的
ref：结果多条，每条数据可以是0或者多条

possible_keys

可能用到的索引。预测用到的索引

key

实际使用到的索引， 如果是null 则是没有索引

key_len

索引长度：用于判断符合索引是否被完全使用
utf8 一个字符 3个字节 一个字节表示可以为null 两个字节表示可变长度

ref

指明当前表所 参照的字段，常量列 const，=‘’等于某个常量
引用字段 t.id = c.id

rows

行数，被索引优化查询的数据个数，通过条件查询出来的数据个数

extra

• using filesort：性能消耗大，需要额外一次排序（查找） where a='' order by b 常见于order by
  对于单索引，如果排序和查找是同一个字段则不会出现using filesort，反之
  复合索引，不能跨列，并且最佳最前缀
  单索引：where 什么字段 order by什么字段
  复合索引：where 和 order by按照复合索引的顺序使用，不要跨列或无序使用

• using temporary 性能损耗大，用到临时表，
  一般出现group by 中，出现在select a2 from t group by a1
  避免：where什么列，就根据哪些列group by

• using index：性能提升，索引覆盖。
  不读取源文件，只从索引文件中获取数据，不需要回表查询

（不回表查询：如果 select age from t where age = 1 ，假设age是索引列，那么只需要到索引树当中查询数据不需要回表查询其他的数据）
出现不回表查询则出现using index，

如果索引覆盖 （using index） 会对possible_keys 和 keys造成影响
如果没有where，则索引只出现在key中
如果有where，则出现在key 和 possible_keys中

• using where：需要回表查询，则会出现using where
  select age,name from where age = 1 ，假设age是索引，则此语句需要回表查询会出现using where

• impossible where：where 字句永远为false

3，SQL优化：

复合索引不能跨列，否则索引失效，可以通过key_len 来观察是否使用到索引
补充using filesort：复合索引不要跨列使用（where 和 order by 拼起来）没有跨列
where 和 order by 拼起来是否满足复合索引顺序，满足则不会出现using filesort反之

1，如果（a,b,c,d）复合索引 和使用的顺序全部一致，则复合索引全部使用，如果部分一致
则使用部分索引 select a,c where a = and b= and c= and d= 和索引顺序完全一致（不跨列使用）则复合索引全部使用

2，单表优化：根据SQL实际的解析顺序，调整复合索引的顺序（最佳左前缀）
索引需要逐步优化。将含in的范围查询放到where 的最后防止失效，失效则会导致后面的索引失效，并且需要回原表查询

3，多表优化：小表驱动大表，索引建立在经常使用的字段上，左外连接 给外表加索引。

4，避免索引失效的原则

（SQL优化是一种概率事件，并不一定达到预想情况）失效的情况大部分适用

索引优化 是一个大部分情况适用的结论，但由于SQL优化器等原因，结论不是百分百正确
一般情况，范围查询(>< in)，之后的索引失效

尽量使用索引覆盖，索引不会失效（using index）

• in 会使索引失效，

• 复合索引不要跨列（where+order by拼接起来 ），或者无序使用（最佳最前缀）

• 复合索引尽量使用全索引匹配

• 不要在索引上进行任何操作，比如计算，函数。否则索引失效

• 复合索引只要左边有一个失效则右边的索引全部失效

• 复合索引不能使用 不等于，is null，is not null。否则自身已经右侧索引全部失效

• like 尽量以常量开头，不要以%开头，否则索引失效

• 尽量不要包含类型转换（显示，隐式）varchar 字段 = 123 。存在隐式转换

• 尽量不要使用or ，否则索引失效

5，SQL优化方法。

1，exist 和 in 如果主查询的数据集大用 in，如果子查询数据集大，则使用exist
2，order by，经常看到using filesort 有两种算法，双路排序和单路排序，根据IO的次数

• 双路排序：双路：扫描两次磁盘。第一次扫描排序字段，排序在buffer 缓冲区进行排序，第二次扫描其他字段
• 单路排序：只读取一次（全部字段），在buffer中进行排序，但此种单路排序会有一定的隐患，不一定真的是“单路”1次IO，有可能多次IO

如果数据量特别大，则无法将所有字段的数据读取完毕，进行分片读取，多次读取。单路排序比双路排序占用更多的buffer缓冲区

可以考虑buffer的容量大小：set max_length_for_sort_data = 1024（字节）

如果set max_length_for_sort_data值太低，则MySQL会自动从 单路排序切换到双路排序。（太低：需要排序列的总大小超过了max_length_for_sort_data定义的字节数）

提高order by查询的策略：选择使用单路，双路；调整buffer的容量大小，避免select * ，保证全部的排序字段 排序的一致性（都是升序 或 降序）

6，SQL排序-慢查询日志

MYSQL提供的日志记录，用于记录MySQL响应时间超过阈值的SQL语句（long_query_time，默认十秒）
慢查询日志默认是关闭的，建议，开发调优时打开，而在最终部署时关闭。
检查是否开启慢查询日志：show variables like '%slow_query_log%'，

开启慢查询日志

1，临时开启：set global slow_query_log = 1 内存中开启
2，永久开启：/etc/my.cnf 中追加配置 [mysqld] slow_query_log=1 slow_query_log_file=日志路径

慢查询阈值：show variables like '%long_query_time%'

1，临时设置：set global long_query_time，设置完毕重新登录
2，永久设置：/etc/my.cnf 中追加配置[mysqld] long_query_time=3

查询超过阈值的SQL条数：show global status like '%slow_queries%'
通过日志查看具体的慢SQL，通过mysqldumpslow工具

7，分析海量数据

• show variables like '%profiling%'
  set profiling = no 开启
  show profiles; 会记录所有profiling 打开后所有执行的语句所花费的时间。只能看到总共消费时间。
• 精确分析：sql 诊断
  show profile all for query 查询到的SQLid
• 全局查询日志：记录开始之后的 全部SQL语句。（调优过程中打开）
  show variables like '%general_log%' set global general_log = 1，set global log_output='table'
  开启全局日志 开启之后记录所有SQL，会被记录到MySQL general_log 表中