阅读量:16
本文主要总结了慢查询优化的过程中常用的以及不合理的操作，适合有 MySQL 基础的开发人员。
索引相关
索引基数
基数是数据列所包含的不同值的数量，例如，某个数据列包含值 1、3、7、4、7、3，那么它的基数就是 4。
索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。
如果某数据列含有很多不同的年龄，索引会很快地分辨数据行；如果某个数据列用于记录性别（只有“M”和“F”两种值），那么索引的用处就不大；如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。
在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是“30%”。
索引失效原因
索引失效的原因有如下几点：
索引的建立
索引的建立需要注意以下几点：
EXPLIAN中有用的信息
基本用法
EXPLIAN基本用法如下：
提高性能的特性
EXPLIAN提高性能的特性如下：
Extra 字段
Extra 字段使用：
using filesort、using temporary 这两项出现时需要注意下，这两项是十分耗费性能的。
在使用 group by 的时候，虽然没有使用 order by，如果没有索引，是可能同时出现 using filesort，using temporary 的。
因为 group by 就是先排序在分组，如果没有排序的需要，可以加上一个 order by NULL 来避免排序，这样 using filesort 就会去除，能提升一点性能。
type 字段
type 字段使用：
字段类型和编码
MySQL 返回字符串长度
CHARACTER_LENGTH(同CHAR_LENGTH)方法返回的是字符数，LENGTH 函数返回的是字节数，一个汉字三个字节。
varchar 等字段建立索引长度计算语句
select count(distinct left(test,5))/count(*) from table；越趋近 1 越好。
MySQL 的 utf8
MySQL 的 utf8 最大是 3 个字节不支持 emoji 表情符号，必须只用 utf8mb4。需要在 MySQL 配置文件中配置客户端字符集为 utf8mb4。
JDBC 的连接串不支持配置 characterEncoding=utf8mb4，最好的办法是在连接池中指定初始化 SQL。
例如：hikari 连接池，其他连接池类似 spring . datasource . hikari . connection - init - sql =set names utf8mb4。否则需要每次执行 SQL 前都先执行 set names utf8mb4。
MySQL 排序规则
一般使用 _bin 和 _genera_ci：
那么，同样是区分大小写，utf8_general_cs 和 utf8_bin 有什么区别？
初始化命令
SQLyog 中初始连接指定编码类型使用连接配置的初始化命令，如下图：
SQL语句总结
常用但容易忘的
SQL 语句常用但容易忘的总结如下：
锁相关
锁相关(作为了解，很少用)：
优化时用到
优化时用到：
查看状态
查看状态：
SQL 编写注意
SQL 编写请注意：
踩坑
踩坑总结如下：
千万大表在线修改
MySQL 在表数据量很大的时候，如果修改表结构会导致锁表，业务请求被阻塞。
MySQL 在 5.6 之后引入了在线更新，但是在某些情况下还是会锁表，所以一般都采用 PT 工具( Percona Toolkit)。
如对表添加索引：
pt-online-schema-change --user='root' --host='localhost' --ask-pass --alter "add index idx_user_id(room_id,create_time)"
D=fission_show_room_v2,t=room_favorite_info --execute
慢查询日志
有时候如果线上请求超时，应该去关注下慢查询日志，慢查询的分析很简单，先找到慢查询日志文件的位置，然后利用 mysqldumpslow 去分析。
查询慢查询日志信息可以直接通过执行 SQL 命令查看相关变量，常用的 SQL 如下：
-- 查看慢查询配置
-- slow_query_log 慢查询日志是否开启
-- slow_query_time 指定了慢查询的阈值
-- long_query_time 指定了慢查询的阈值
-- log_queries_not_using_indexes 是否记录所有没有利用索引的查询
SHOW VARIABLES LIKE'%quer%';
--查看慢查询是日志还是表的形式
SHOW VARIABLES LIKE'log_output'
-- 查看慢查询的数量
SHOW GLOBAL STATUS LIKE'solw_queries';
mysqldumpslow 的工具十分简单，我主要用到的参数如下：
