mysql优化策略
mysql优化大致分5步， 1 缓存 2 慢日志分析 3 服务器参数优化 4 查询语句优化 5 分库分表 读写分离
1 加缓
看一下能不能加缓存解决，是不是周期性的卡顿，是否需要调整缓存失效策略
2 开启慢查询日志，看看是sql执行的时间长还是sql等待的时间长
查看慢查询日志
set global long_query_time = 1; show global variables like '%long_query_time%'; set long_query_time=1; show variables like '%long_query_time%';
show status like 'slow_queries';
查看 SQL 执行成本
show variables like 'profiling';
set profiling = 'ON'
show profiles;
explain 工具
EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
SHOW WARNINGS\G
查看sql优化器优化过的执行sql
EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
SHOW WARNINGS\G
分析优化器执行计划：trace
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
select * from student where id < 10;
select * from information_schema.optimizer_trace\G
MySQL监控分析视图-sys schema
select * from sys.schema_redundant_indexes;
select * from sys.schema_unused_indexes;
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='dbname' ;
3 如果是sql等待时间长需要调整服务器参数
innodb_buffer_pool_size
max_connections
等
4 如果是sql执行时间长需要优化索引，sql语句，表结构
1)优化索引优化
1 全值匹配最优 2 最佳左前缀法则 3 主键插入顺序 4 计算、函数、类型转换(自动或手动)导致索引失效 5 类型转换导致索引失效 6 范围条件右边的列索引失效 7 不等于(!= 或者<>)索引失效 8 is null可以使用索引，is not null无法使用索引 9 like以通配符%开头索引失效 10 OR 前后存在非索引的列，索引失效 11 数据库和表的字符集统一使用utf8mb4
2)sql语句优化
外连接和内连接优化 Simple Nested-Loop Join -- join就是一个for循环的过程 Index Nested-Loop Join -- 如果使用索引的话，可以有效的减少内循环次数 Block Nested-Loop Join -- 会把较小的结果集分块儿加载到内存里，减少外循环的读取次数，然后把另一个结果集加载到内存里，减少内循环的读取次数 hash Nested-Loop Join -- 使用hash算法的方式，精确的定位匹配结果
子查询优化
尽量不适用子查询，因为子查询会构建临时表，临时表上没有索引
排序优化
排序有三种方式，1 根据索引 2 单路排序 3 双路排序。优化的时候主要看需要排序的数据量，进行选择。1 最好使用索引 2 最好使用单路 3，数据库内存很小的情况下可以选择双路
分组优化（与排序基本一致）
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢
分页优化
select * from student 20000,10
select * from student where id >200000 limit 10 --效果要好于直接分页
3)表结构优化
拆分表：冷热数据分离
增加中间表
增加冗余字段
优化数据类型
优化插入记录的速度
使用非空约束
分析表、检查表与优化表
5 分库分表 读写分离
分库分表的方式在生产中通常包括：垂直分库、垂直分表、水平分库和水平分表四种。
垂直分表： 例如将学生信息分为学生基本信息和学生描述信息两张表
垂直分库： 例如将学生信息和学生成绩分到不同的数据库
水平分表： 例如将学生信息表按照奇数和偶数分到分到两张表里
水平分库： 例如将水平分表中分好的两张表存到不同的数据库中
