mysql性能调优
1. 服务器性能参数查询
SHOW [GLOBAL|SESSION] STATUS LIKE '参数'; 一些常用的性能参数如下： • Connections：连接MySQL服务器的次数。 • Uptime：MySQL服务器的上线时间。 • Slow_queries：慢查询的次数。 • Innodb_rows_read：Select查询返回的行数 • Innodb_rows_inserted：执行INSERT操作插入的行数 • Innodb_rows_updated：执行UPDATE操作更新的行数 • Innodb_rows_deleted：执行DELETE操作删除的行数 • Com_select：查询操作的次数。 • Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。 • Com_update：更新操作的次数。 • Com_delete：删除操作的次数。
• last_query_cost：上次查询涉及到的数据页数。
2. 慢查询
1. 查看慢查询是否打开 show variables like '%slow_query_log%' 2. 开关慢查询（只有在需要调优的时候打开，其余时间应该关闭，因为它会影响性能） set global slow_query_log='ON';
set global slow_query_log='OFF';
3. 查看慢查询的阈值 show variables like '%long_query_time%'; 4. 修改慢查询阈值（也可以通过配置文件修改，但是需要重启服务） set global long_query_time = 1; # 全局修改，下次重启会失效 set long_query_time=1; #本次回话修改 ，下次重启会失效
5. 查看慢查询的条数
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
3. 慢查询日志分析工具 ：mysqldumpslow
4. 查看sql执行成本
1. 查看是否开启 show variables like 'profiling'; 2. 修改状态 set profiling = 'ON'; # 'OFF' 3. 查看当前回话的所有profiles show profiles; 4. 查看具体sql的执行成本 show profiles; # 最近一条sql show profile cpu,block io for query 2; # 查看第2条sql的执行成本，2来自于show profiles结果的序号
5. explain
1. 各个字段的含义
1. 关于id
id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好
2. select_type
mysql为每一个select关键字代表的小查询都定义了一个select_type属性，它表示了小查询在大查询汇总扮演的角色
3. type(重点)
结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）
4. rows: 预估需要扫描的条数，值越小越好
5. extra，额外提示信息，很重要，遇到的时候百度吧
6. explain的不同输出格式
1. explain select ... # 传统格式 2. explain format=json select ... # json格式，输出的信息额外包含执行成本 3. explain format=tree select ... # 输出的信息量很少
7. 索引失效的情况
计算、函数、类型转换(自动或手动)会导致索引失效
联合索引有最左前缀原则
联合索引时，范围字段右侧的索引全部失效
不等于(!= 或者<>)索引失效
is null可以使用索引，is not null无法使用索引（大部分情况下，列中不应该出现null，即使有，应该给个默认值）
like以通配符%开头索引失效
OR 前后存在非索引的列，索引失效。非索引列会导致扫描全表，如果两个都是索引列，会index_merge,两个索引都走，读最后的结果union
数据库和表的字符集应该统一使用utf8mb4
8. join连表
1. left join 右边一定要建立索引，不然会很慢（right join同理） 2. inner join 优化器会自动选择驱动表，一般选择条数较少的那个为驱动表
3. join原理，以t1为驱动表，t2为被驱动表
1. 从表t1中读入一行数据 R； 2. 从数据行R中，取出a字段到表t2里去查找； 3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分； 4. 重复执行步骤1到3，直到表t1的末尾循环结束。
4. 结论
保证被驱动表的JOIN字段已经创建了索引
需要JOIN 的字段，数据类型保持绝对一致。
LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。
INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
衍生表建不了索引
