MySQL调优

数据库优化常见方案

1. 优化shema,sql语句+索引
2. 加缓存，memcached,redis
3. 主从复制，读写分离
4. 垂直拆分
5. 水平拆分

为了知道怎么优化SQL,必须先清楚SQL的生命周期

SQL生命周期

1. 应用服务器连接数据库服务器，建立一个TCP/IP连接，发送SQL请求给MySQL服务器
2. 查询缓存，有缓存则直接返回数据到应用服务器，没有则进入到SQL解析器
3. SQL解析器：匹配SQL语句，主要是解析语法是否正确,查询中的表，列名是否存在，检查表名，列名是否有歧义
4. 查询优化器:MySQL服务器自己对SQL做优化找到SQL的最佳执行方案，生成执行计划，优化的方面有索引优化（利用索引和列是否为空来优化count,min，max等聚合函数），顺序优化（重新定义表的关联关系），将外连接转换为内连接，使用等价变换，比如（1=1 and a>1）将被优化为a>1,如果索引列包含查询的所有列，则使用索引返回需要的数据，把子查询转换成关联查询，减少表的查询次数，
5. SQL执行器：判断用户权限，根据执行计划调用存储引擎接口获取数据
6. 将处理结果通过连接返回到应用服务器

慢查询日志

在优化SQL前需要先找到需要优化的SQL,一般是通过慢查询日志来查询

查询是否开启慢查询日志

SHOW VARIABLES LIKE 'slow_query_log';

开启慢查询日志

SET GLOBAL slow_query_log = 'ON';

查询慢查询日志的路径

默认和数据文件放一起

show VARIABLES like '%slow_query_log_file%';

慢查询记录时间的阈值

默认十秒

show VARIABLES like '%long_query_time%';

查询是否开启记录未使用索引的SQL

show VARIABLES like '%log_queries_not_using_indexes%';

找到了需要优化的SQL,下面开始分析SQL的组成

SQL执行计划

MySQL使用explain关键字来分析SQL，只要在SQL语句前加上一个explain关键字，就可以得到一个SQL的执行计划

explain select * from sys_user where id = 2979;

执行计划字段详解

ID

执行顺序的标识，值越大的越优先执行，相同的值由上往下执行

select_type

查询语句的类型，下面是各个值

1. SIMPLE：简单的select查询，不包含任何子查询和联合查询
2. PRIMARY:主查询，如果有子查询的话，最外层的查询会被标记为PRIMARY
3. SUBQUERY:在select或where列表中包含了子查询，表示该语句属于子查询语句
4. DERIVED:生成的临时表的查询语句，也就是子查询from的一部分
5. DEPENDENT SUBQUERY:子查询中的第一个SELECT,子查询依赖于外出查询的结果
6. UNION:表示union中的第二个或后面的select 语句
7. UNION RESULT:从UNION语句中获取结果

table

显示这一行的数据来源于那张表

type

定位SQL性能因素最重要的指标，值包括system,const,eq_ref,ref,Range,index,All,性能从高到低

• System: 表只有一行记录，基本不会出现
• Const:通过索引一次就找到了数据，一般出现在使用了primary key或者unique索引匹配到了数据，匹配的条件常量(字符串，数字)
• eq_ref:使用主键索引或者非空唯一索引，在表中只有一条记录与索引键匹配，匹配条件是某个表的列(需要转义替换才能拿到的值，简单理解为关联查询)
• ref:非唯一性索引扫描，和eq_ref不同的是eq_ref匹配的是唯一索引，ref它返回所有匹配某个单独值的行，它可能会找到多个符合条件的行
• range:范围数据扫描
• index:全索引扫描,通过扫描整棵索引树来获取到的结果
• All:全表扫描

possible_keys

可能会用到的索引

Key

实际使用的索引，如果为空，表示没有使用索引

key_len

使用到的索引key长度，如果为联合索引则显示已命中的联合索引长度之和(如：联合索引为a+b+c,如果索引命中了a+b,那么长度就为a+b的索引长度，通过可以通过key_len来分析联合索引所命中的情况)

关于possible_keys和key的三种关系场景

possible_keys != null && key != null：正常使用到了索引的情况

possible_keys != null && key==null,这种情况说明通过索引并不能提升多少效率，一般在表的数据量很少，或者是索引的字段离散性不高，执行计划发现用索引和扫描差不多

possible_keys == null && key!= null：这种情况一般为where条件没有命中索引，但是查询的列是索引字段，也就是查询的列命中了覆盖索引的情况

ref

实际用到的索引是哪个表的列，const代表常量

row

扫描的数据行数，不是准确的值，只是估算，一般来说扫描的数据行数越少，性能越好

filtered

返回结果的行数占需读取行数的百分比，值越大越好

rows

查询的结果集大小

Extra

对整个SQL做概括性总结，包含使用了什么索引，排序方式

• using where:使用了where条件.
• using index:使用了覆盖索引（通常是一种好现象，意味着查询的数据直接在二级索引返回了，从而减少了回表的过程）。
• using filesort:文件排序，使用了非索引的字段进行排序(通常这种情况需要优化)。
• using index sort:使用了索引排序,通常这是一种好现象，索引天然有序，避免了通过sort buffer来排序的流程
• using temporary:使用了临时表（常见于group by,order by）
• using join buffer:使用 了join buffer缓存(这种情况关注一下查询的字段是不是没有建立索引)
• using index condition:索引下推

SQL优化

优化原则

正确使用索引

优化查询列

尽量避免select *，改使用select 列名，避免返回多余的列。

优化前：select * from sys_suer
优化后:select id,username,nickname,mobile from sys_user

优化where子句

优化方案：避免索引失效，可能导致全表扫描的情况

1. 避免对字段进行null判断，用特殊值代替，如0

   优化前：select * from sys_user where id = null
   优化后：select * from sys_user where id = 0
   

2. 避免使用!=或<>操作符

   优化前:select * from sys_user where dept_id <> 2;
   优化后:explain select * from sys_user where dept_id > 2 union all select * from sys_user where dept_id < 2; 
   

3. 避免使用or连接条件

   优化前: select * from sys_user where id = 3 or id = 4;
   优化后：select * from sys_user where id = 3 union all select * from sys_user where id = 2;
   

4. 避免使用参数，表达式，函数，操作

   在应用层将参数转换成常量

5. 避免在where子句中的“=”左边进行函数,算术运算或者其他表达式运算

优化长难语句

优化方案：分解关联查询，执行单个查询，减少锁的竞争，减少冗余记录的查询

优化关联查询

优化方案：确定ON或者USING子句中是否有索引，确保GROUP BY和ORDER BY只有一个表中的列

优化子查询

优化方案：使用关联查询，优化GROUP BY和DISTINCT,这两种可以根据索引来优化，使用索引列分组效率更高，如果不需要ORDER BY进行GROUP BY时加ORDER BY NULL,mysql不会再进行文件排序

优化LIMIT分页

优化方案：记录上次查询的大ID,下次查询时直接根据该ID来查询因为LIMIT偏移量越大，查询效率越低，因为MySQL不是跳过偏移量，而是先把偏移量+要取出的出来，然后抛弃偏移量后再返回

优化前:select * from sys_user order by id desc limit 1,20
优化后:select * from sys_user where id > 1 order by id desc limit 20

优化 UNION查询

优化方案： UNION ALL的效率高于UNION

like语句优化

优化前：select * from sys_user where username like "%ws%"
优化后：select * from sys_user where username like "ws%"

优化后符合最左前缀原则，会走索引，第一种会索引失效

索引优化

分类

• 功能上分类：普通索引(NORMAL)，唯一索引(UNIQUE)，主键索引（PRIMARY KEY），全文索引（FULLTEXT）
• 实现方式分类：聚簇索引(主键属于聚簇索引)，非聚簇索引
• 字段个数分类：单列索引，多列索引（联合索引，覆盖索引）

原则

1. 左前缀原则，mysql会一直向右匹配直到遇到范围查询（>,<,between,like）就停止匹配
2. 频繁作为查询条件的字段适合创建索引
3. 频繁更新的字段不适合创建索引
4. 尽量扩展索引，不要新建索引，一个联合索引比多个单个索引效率更高

建议

1. 尽量使用自增主键
2. 索引字段越小越好，因为查询索引的时候需要把索引列转换成一个关键字来查询，字段越小，转换的时间越短
3. 索引不要超过6个
4. 删除冗余和无效的索引
5. 尽量使用数字型字段
6. 非空字段应该指定列为NOTNULL，在mysql中，含有空值的列很难进行查询优化，因为他使得索引的统计信息变得更加复杂，应该用0或者一个特殊的值来代替空值
7. 将离散大的字段（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查询字段的差异值，返回值越大说明字段的离散程度越高

索引案例

DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `nickname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '昵称',
  `gender` tinyint(1) NULL DEFAULT 1 COMMENT '性别((1:男;2:女))',
  `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码',
  `dept_id` int NULL DEFAULT NULL COMMENT '部门ID',
  `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '用户头像',
  `mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系方式',
  `status` tinyint(1) NULL DEFAULT 1 COMMENT '用户状态((1:正常;0:禁用))',
  `email` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户邮箱',
  `deleted` tinyint(1) NULL DEFAULT 0 COMMENT '逻辑删除标识(0:未删除;1:已删除)',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `sys`(`username`, `dept_id`, `nickname`, `deleted`) USING BTREE,
  INDEX `dept_id`(`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1021664 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息表' ROW_FORMAT = DYNAMIC;

有效的索引

1. 符合最左匹配原则，where后面的字段顺序和索引顺序一致

   explain select * from sys_user where username = '有来技术' and dept_id = 2 and nickname = "test用户" and deleted = 1
   

2. 覆盖索引，虽然不符合最左匹配原则，但是查询的列都在索引中

   explain select username,nickname,deleted from sys_user where nickname = "test用户" and deleted = 1
   

   3.索引下推，虽然username是索引字段，但是后面加上了模糊查询，并且是以%开头，应该是用不了索引的，但是MYSQL在5.6引入了(index Condition Pushdown)简称ICP特性，在存储引擎层优化了这种情况，也能使用索引

   explain select * from sys_user where username = '有来技术' and email like '%youlai'

无效的索引

1. 不符合最左匹配原则

   explain select * from sys_user where dept_id = 2 and nickname = "test用户" and deleted = 1
   

   

2. 模糊查询以%开头

    explain select * from sys_user where username like '%有来技术'
   

   

3. 使用!=导致索引失效，虽然username是索引字段，但是因为使用了！=，需要回表根据值来过滤数据，所以索引失效了

    explain select * from sys_user where username != '有来技术' 
   

   

4.使用了计算表达式

explain select * from sys_user where dept_id - 1 = 1

5.使用索引自身类型不同的值

explain select * from sys_user where username = 1