1、什么是慢查询?
慢查询是 MySQL 中提供的一种慢查询日志,它用来记录在 MySQL 中响应时间超过阀值的语句。
默认情况下,MySQL 数据库并不启动慢查询日志,需要我们手动来设置这个参数,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会给 MySQL 服务器带来一定的性能影响。
慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。
使用 mysql> show variables like ‘%slow_query_log%’; 来查询慢查询日志是否开启,执行效果如下图所示:
slowquerylog 的值为 OFF 时,表示未开启慢查询日志。
2、如何开启慢查询日志?
set global slowquerylog=1
修改 MySQL 的配置文件 my.cnf,配置如下:
slowquerylog=1
slowquerylogfile=/tmp/mysqlslow.log
3、如何分析定位慢查询?
使用 MySQL 中的 explain 分析执行语句,比如:
explain select * from book_info limit 200,10;
其中:
id — 选择标识符。id越大优先级越高,越先被执行。
select_type — 查询中每个select子句的类型
table — 显示这一步所访问数据库中表名称(显示这一行的数据是关于哪张表的,可能是简称)
partitions — 匹配的分区
type — 访问类型(表示MySQL决定如何查找表中的行)
possible_keys — 该查询可以利用的索引。如果没有任何索引可以使用,就会显示为null,这项内容对优化索引时的调整非常重要
key — 优化器从possible_keys中选择使用的索引
key_len — 索引字段的长度
ref— 列出是通过常量,还是某个字段的某个字段来过滤的
rows — 大概估算的行数
filtered — 按表条件过滤的行百分比
Extra — 执行情况的描述和说明
type 字段类型如下:(性能从差到好)
all — 全表扫描
explain select * from subject;
index — 遍历索引(和全表扫描一样,只不过扫描时是按索引次序进行而不是行)
explain select id from subject;
range — 范围扫描(就是一个有限制的索引扫描,常出现在between 或 where 下)
explain select * from subject where id between 1 and 3;
ref — 索引访问,返回所有匹配某个单个值的行(使用非唯一索引或者唯一索引的非唯一性前缀查找数据)
eq_ref — 索引访问(在使用主键或唯一性索引查找时看到,最多只返回一条记录)
const、system — (当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问)
explain select * from teacher where teacher_no = ‘10001’;
NULL — MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。
explain select min(id) from subject;
Extra 字段类型如下:
Using index — 表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错
Using where — 这说明服务器在存储引擎收到行后将进行过滤
Using temporary — 表示使用了临时表保存中间结果,常见于排序order by 和分组查询group by
Using join buffer — 使用了连接缓存
explain select student.,teacher.,subject.* from student,teacher,subject;
Using filesort — MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,MySQL中无法利用索引完成的排序操作称为“文件排序”
explain select * from subject order by name;
4、MySQL 的优化手段都有哪些?
(1)表结构设计优化:
使用可以存下数据最小的数据类型
使用简单的数据类型,int 要比 varchar 类型在 MySQL 处理简单
尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int
尽可能使用 not null 定义字段,因为 null 占用 4 字节空间
尽量少用 text 类型,非用不可时最好考虑分表
尽量使用 timestamp,而非 datetime
单表不要有太多字段,建议在 20 个字段以内
(2)查询优化:
避免 SELECT *,只查询需要的字段。
小表驱动大表,即小的数据集驱动大的数据集,比如,当 B 表的数据集小于 A 表时,用 in 优化 exist,两表执行顺序是先查 B 表,再查 A 表,查询语句:select * from A where id in (select id from B) 。
慢查询优化,开启慢查询之后,找到慢sql,使用explain进行分析修改
一些情况下,可以使用连接代替子查询,因为使用 join 时,MySQL 不会在内存中创建临时表。
(3)索引使用上的优化:
尽量使用主键查询,而非其他索引,因为主键查询不会触发回表查询。
不做列运算,把计算都放入各个业务系统实现
查询语句尽可能简单,大语句拆小语句,减少锁时间
不使用 select * 查询
or 查询改写成 in 查询
不用函数和触发器
避免 %xx 查询
少用 join 查询
使用同类型比较,比如 ‘123’ 和 ‘123’、123 和 123
尽量避免在 where 子句中使用 != 或者 <> 操作符,查询引用会放弃索引而进行全表扫描
列表数据使用分页查询,每页数据量不要太大
用 exists 替代 in 查询
避免在索引列上使用 is null 和 is not null
避免在 where 子句中对字段进行表达式操作
尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型
(4)读写分离:
一般情况下对数据库而言都是“读多写少”,换言之,数据库的压力多数是因为大量的读取数据的操作造成的,我们可以采用数据库集群的方案,使用一个库作为主库,负责写入数据;其他库为从库,负责读取数据。这样可以缓解对数据库的访问压力。
(5) 表拆分:
当数据库中的数据非常大时,查询优化方案也不能解决查询速度慢的问题时,我们可以考虑拆分表,让每张表的数据量变小,从而提高查询效率。
垂直拆分或水平拆分
通常情况下,我们使用取模的方式来进行表的拆分,比如,一张有 400W 的用户表 users,为提高其查询效率我们把其分成 4 张表 users1,users2,users3,users4,然后通过用户 ID 取模的方法,同时查询、更新、删除也是通过取模的方法来操作。
5、MySQL 常见读写分离方案有哪些?
(1)应用层解决方案:
可以通过应用层对数据源做路由来实现读写分离。
(2)中间件解决方案:
通过 MySQL 的中间件做主从集群,比如:Mysql Proxy、Amoeba、Atlas 等中间件都能符合需求。
优点:与应用层解耦。
缺点:增加一个服务维护的风险点,性能及稳定性待测试。
6、什么是 MySQL 多实例?如何配置 MySQL 多实例?
MySQL 多实例就是在同一台服务器上启用多个 MySQL 服务,它们监听不同的端口,运行多个服务进程,它们相互独立,互不影响的对外提供服务,便于节约服务器资源与后期架构扩展。
多实例的配置方法有两种:
一个实例一个配置文件,不同端口;
同一配置文件(my.cnf)下配置不同实例,基于 MySQL 的 d_multi 工具。
7、怎样确保备库无延迟?
通常保证主备无延迟有以下三种方法:
每次从库执行查询请求前,先判断 secondsbehindmaster 是否已经等于 0。如果还不等于 0 ,那就必须等到这个参数变为 0 才能执行查询请求,secondsbehindmaster 参数是用来衡量主备延迟时间的长短;
对比位点确保主备无延迟。MasterLogFile 和 ReadMasterLogPos,表示的是读到的主库的最新位点,RelayMasterLogFile 和 ExecMasterLog_Pos,表示的是备库执行的最新位点
对比 GTID 集合确保主备无延迟。AutoPosition=1 ,表示这对主备关系使用了 GTID 协议;RetrievedGtidSet,是备库收到的所有日志的 GTID 集合;ExecutedGtid_Set,是备库所有已经执行完成的 GTID 集合。