一、什么是慢查询?
慢查询是 MySQL 中提供的一种慢查询日志,它用来记录在 MySQL 中响应时间超过阀值的语句,具体指运行时间超过 longquerytime 值的 SQL,则会被记录到慢查询日志中。longquerytime 的默认值为 10,意思是运行 10S 以上的语句。默认情况下,MySQL 数据库并不启动慢查询日志,需要我们手动来设置这个参数,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会给 MySQL 服务器带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。
使用 mysql> show variables like ‘%slow_query_log%’; 来查询慢查询日志是否开启,执行效果如下图所示:
slowquerylog 的值为 OFF 时,表示未开启慢查询日志。
1.1、如何开启慢查询日志?
开启慢查询日志,可以使用如下 MySQL 命令:
mysql> set global slowquerylog=1
不过这种设置方式,只对当前数据库生效,如果 MySQL 重启也会失效,如果要永久生效,就必须修改 MySQL 的配置文件 my.cnf,配置如下:
slowquerylog =1 slowquerylogfile=/tmp/mysqlslow.log
1.2、如何定位慢查询?
使用 MySQL 中的 explain 分析执行语句,比如:
explain select * from t where id=5;
如下图所示:
其中:
- id — 选择标识符。id越大优先级越高,越先被执行。
- select_type — 表示查询的类型。
- table — 输出结果集的表
- partitions — 匹配的分区
- type — 表示表的连接类型
- possible_keys — 表示查询时,可能使用的索引
- key — 表示实际使用的索引
- key_len — 索引字段的长度
- ref— 列与索引的比较
- rows — 大概估算的行数
- filtered — 按表条件过滤的行百分比
- Extra — 执行情况的描述和说明
其中最重要的就是 type 字段,type 值类型如下:
- all — 扫描全表数据
- index — 遍历索引
- range — 索引范围查找
- index_subquery — 在子查询中使用 ref
- uniquesubquery — 在子查询中使用 eqref
- refornull — 对 null 进行索引的优化的 ref
- fulltext — 使用全文索引
- ref — 使用非唯一索引查找数据
- eq_ref — 在 join 查询中使用主键或唯一索引关联
- const — 将一个主键放置到 where 后面作为条件查询, MySQL 优化器就能把这次查询优化转化为一个常量,如何转化以及何时转化,这个取决于优化器,这个比 eq_ref 效率高一点
二、MySQL 的优化手段都有哪些?
MySQL 的常见的优化手段有以下五种:
2.1、查询优化
- 避免 SELECT *,只查询需要的字段。
- 小表驱动大表,即小的数据集驱动大的数据集,比如,当 B 表的数据集小于 A 表时,用 in 优化 exist,两表执行顺序是先查 B 表,再查 A 表,查询语句:select * from A where id in (select id from B) 。
- 一些情况下,可以使用连接代替子查询,因为使用 join 时,MySQL 不会在内存中创建临时表。
2.2、优化索引的使用
- 尽量使用主键查询,而非其他索引,因为主键查询不会触发回表查询。
- 不做列运算,把计算都放入各个业务系统实现
- 查询语句尽可能简单,大语句拆小语句,减少锁时间
- 不使用 select * 查询
- or 查询改写成 in 查询
select id from t where a=10 or a=20
select id from t where a=10
union all
select id from t where a=20
- 对于连续的数值,能用 between 就不要用 in
- 不用函数和触发器
- 避免 %xx 查询
- 少用 join 查询
- 使用同类型比较,比如 ‘123’ 和 ‘123’、123 和 123
- 尽量避免在 where 子句中使用 != 或者 <> 操作符,查询引用会放弃索引而进行全表扫描
- 列表数据使用分页查询,每页数据量不要太大
- 用 exists 替代 in 查询
select num from a where num in (select num from b)
select num from a where exists (select 1 from b where num=a.num)
- 避免在索引列上使用 is null 和 is not null
- 尽量使用主键查询
- 避免在 where 子句中对字段进行表达式操作 / 函数操作
select id from t where a/2=100
select id from t where a=100*2
- 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型
- 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定
2.3、表结构设计优化
- 使用可以存下数据最小的数据类型。
- 使用简单的数据类型,int 要比 varchar 类型在 MySQL 处理简单。
- 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int。
- 尽可能使用 not null 定义字段,因为 null 占用 4 字节空间。
- 尽量少用 text 类型,非用不可时最好考虑分表。
- 尽量使用 timestamp,而非 datetime。
- 单表不要有太多字段,建议在 20 个字段以内。
2.4、表拆分
当数据库中的数据非常大时,查询优化方案也不能解决查询速度慢的问题时,我们可以考虑拆分表,让每张表的数据量变小,从而提高查询效率。
a)垂直拆分:是指数据表列的拆分,把一张列比较多的表拆分为多张表,比如,用户表中一些字段经常被访问,将这些字段放在一张表中,另外一些不常用的字段放在另一张表中,插入数据时,使用事务确保两张表的数据一致性。 垂直拆分的原则:
- 把不常用的字段单独放在一张表;
- 把 text,blob 等大字段拆分出来放在附表中;
- 经常组合查询的列放在一张表中。
b)水平拆分:指数据表行的拆分,表的行数超过200万行时,就会变慢,这时可以把一张的表的数据拆成多张表来存放。
通常情况下,我们使用取模的方式来进行表的拆分,比如,一张有 400W 的用户表 users,为提高其查询效率我们把其分成 4 张表 users1,users2,users3,users4,然后通过用户 ID 取模的方法,同时查询、更新、删除也是通过取模的方法来操作。
2.5、读写分离
一般情况下对数据库而言都是“读多写少”,换言之,数据库的压力多数是因为大量的读取数据的操作造成的,我们可以采用数据库集群的方案,使用一个库作为主库,负责写入数据;其他库为从库,负责读取数据。这样可以缓解对数据库的访问压力。
三、MySQL 常见读写分离方案有哪些?
MySQL 常见的读写分离方案,如下列表:
1)应用层解决方案 可以通过应用层对数据源做路由来实现读写分离,比如,使用 SpringMVC + MyBatis,可以将 SQL 路由交给 Spring,通过 AOP 或者 Annotation 由代码显示的控制数据源。 优点:路由策略的扩展性和可控性较强。 缺点:需要在 Spring 中添加耦合控制代码。
2)中间件解决方案 通过 MySQL 的中间件做主从集群,比如:Mysql Proxy、Amoeba、Atlas 等中间件都能符合需求。 优点:与应用层解耦。 缺点:增加一个服务维护的风险点,性能及稳定性待测试,需要支持代码强制主从和事务。