思路
开启慢查询日志,抓取慢sql 通过explain查看执行计划,对慢sql语句分析 创建索引并调整语句,再对比优化结果
抓取慢sql
show variables like '%quer%';
Variable_name Value 含义 binlog_rows_query_log_events OFF ON 标识开启慢查询日志 ft_query_expansion_limit 20 have_query_cache YES log_queries_not_using_indexes OFF log_throttle_queries_not_using_indexes 0 long_query_time 10 慢查询时间阀值,单位秒 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 0 query_cache_type OFF query_cache_wlock_invalidate OFF query_prealloc_size 8192 slow_query_log ON slow_query_log_file /data/mysql/log/slow_query.log 慢查询日志存储位置
分析查询语句
先看type:All全表扫描,没有用到索引 再看key:Null没有使用索引列 然后看rows:数值越多耗时越长 最后看Extra:避免Using temporary和Using Filesort
id:选择标识符,代表执行顺序 select_type:表示查询的类型。
simple:简单的select查询,不包含联合查询和子查询 primary:查询中包含子查询 subquery:select 或者where中包含子查询 derived:from中包含子查询 union:联合查询 union result:union后的结果集 table:查询的表名 partitions:匹配的分区 type:表示表的连接类型
All:全表扫描 Index:只遍历索引树,索引树上就有要查询的结果,不需要IO Range:索引范围扫描 Ref:非唯一性索引扫描 Eq_ref:唯一索引扫描 Const:通过一次索引就能查询到,通常是Primary Key或者Unique System:Const特例,表数据只有一行 Null:不用访问索引就可以直接查询到结果 Possible_key:能使用哪个索引找到数据行,单并不一定会被使用到 Key:Possible_key中决定使用的索引 Key_len:显示索引中使用的字节数 ref:上述表的连接匹配条件,即哪些列或常量被用于查找索引上的值 Rows:找到所需记录要读取的行数 Extra:
Using Where:仅通过索引就可以过滤所需数据 Using temporary:需要使用临时表来存储结果集 Using Filesort:order by操作无法利用索引完成导致的“文件排序” Using join buffer:连接使用了缓存,可以通过添加索引来解决 Using Index:索引树中包含要查询的所有信息 Using Index Condition:根据辅助索引过滤数据,减少Server和磁盘的IO次数
索引创建原则
表一定要有业务无关的主键
主键要保证递增,可以防止因为乱序导致的内存碎片可以数据复制 适合添加索引的列:经常被查询、经常用于表链接,经常排序或者分组
经常被查询:可以提高性能 表链接:可以通过索引防止全表扫描或者临时表等 排序、分组:可以防止FIlesort文件排序 索引列尽量都是不重复的数据
BTree索引树是通过计算hashcode然后比较大小进行排序的,如果大量的重复数据会导致索引树的过滤效率降低 组合索引一般不超过5列,选择性高的放在前面
合理利用索引覆盖,禁止select *
索引覆盖可以防止回表操作,回表会导致大量的随机IO explain 判断sql是否合理利用索引
可以通过explain可以分析sql执行的情况,进而做优化 单表索引控制在5个以内
索引过多会导致插入和修改的时候,同时需要操作索引,会降低性能 不建议在频繁更新的字段上添加索引
会导致索引的频繁修改 会导致索引碎片,因为索引中删除,只是将对应的置位无效,而不是进行实际的删除操作 where条件中的索引列不能是表达式的一部分,避免对索引列进行函数计算
join类型的字段必须类型一致且都建立索引
不一致的话,会导致隐士的类型转换,部分情况会导致无法使用索引
索引失效
隐式的类型转换会导致索引失效,导致全表扫描 对索引列进行函数或者数学计算,例如日期格式化 模糊匹配未使用前缀匹配 使用了负方向查询,not,!=,not in等
Sql规范
按需查询避免 select *
无法使用覆盖索引,回表,增加IO 多查询的列,会有多余的IO和网络开销 避免大事务,将大事务拆成小事务。防止出现锁阻塞,导致的雪崩效应 少用多表join,禁止大表join,小表驱动大表,join列必须字符集一致,且有索引 尽量避免多层子查询嵌套 定期对慢sql优化