set gobal 修改正在运行的mysql
通常改为0.001秒也就是1毫秒可能比较合适,防止日志过多沾满磁盘空间,
log_queries_not_using_indexes是否记录未使用索引的sql
存储日志需要消耗大量的磁盘空间,,通常改为0.001秒也就是1毫秒可能比较合适,
慢查询日志分析工具,mysqldumpslow 。
mysqldumpslow -s r -t 10 slow-mysql.log c,总次数,t,总时间, l 锁的时间, r,总数据行, at al ar 代表平均排序 at=总时间/总次数
-s order 指定按哪种排序方式输出结果。
pt query digest \
--explain h=127.0.0.1 slow -mysql.log
高清楚这些查询为什么慢
一条sql的流程
1、客户端发送sql请求给服务器 这一步对查询性能不会产生影响。
2、服务器检查是否可以在查询缓存中命中该sql,如果命中了 直接返回客户端信息,如果没有进入下一阶段
3、服务器端进行sql解析,预处理,再由优化器生成对应的执行计划
4、根据执行计划,调用存储引擎api来查询数据
5、将结果返回给客户端
这就是mysql服务器处理查询请求的整个过程。
如果查询缓存是打开的,服务器会优先检查这个查询是否命中查询缓存中的数据,这个检查是通过一个大小写敏感的哈希查找实现的,hash查找只能进行全值匹配。如果查询恰好命中了查询缓存,那么在返回结果之前mysql会检查用户权限,这仍然是无需解析查询语句的,因为在查询缓存中已近存在了要查询的表的一些信息,如果权限没有问题,mysql会跳过所有环节,直接从缓存中拿到结果并返回给客户端,这种 情况下查询是不会被解析的,也不会生成查询计划,不会被执行。从查询缓存中直接返回结果,并不容易,建议不要使用查询缓存。 query_cache_type 设置查询缓存是否可用 size limit
下一各阶段 mysql依照这个执行计划 和存储引擎进行交互
这个阶段包括了多个过程:解析sql 预处理 优化sql执行计划
语法解析阶段是通过关键字对mysql语句进行解析,并生成一可对应的解析数,这一的阶段,mysql解析器将使用mysql语法规则验证和解析查询。
包括检查语法是否使用了正确的关键字,关键字的顺粗是否正确,
预处理阶段是根据mysql规则进一步检查解析数是否合法,比如检查查询中所涉及的表和数据列是否存在及名字或别名,是否存在歧义等等。语法检查全部通过了,查询优化器就可以生成查询计划了。
会造成mysql生成错误的执行计划的原因
1、统计信息不准确
2、执行计划中的成本估算不等同与实际的执行计划的成本:包括mysql服务器层并不知道哪些页面在内存中,哪些页面在磁盘中,哪些需要顺粗读取,哪些要页面随即读取。
3、mysql从不考虑其他并发的查询,这可能会影响当前查询的速度
4、mysql有时候也会基于一些固定的规则来生成执行计划
如何确定查询处理各个阶段所消耗的时间
使用profile
set profiling = 1;
执行查询
show profiles;
show profile for query 1;
查询每个阶段的消耗时间
show profile cpu for query 1;
查看cpu
使用performance_schema 查询每个阶段的时间
启动监控 update setup_instruments set enabled='yes',timed='yes' where name like 'stage%';
update setup_conssumers set enabled='yes' where name like 'events';
这样就启动了 performance_schema 各个阶段所需要的信息。
特定sql的查询优化。
大表的数据修改最好要分批处理,例如1000万行记录的表中删除/更新100万行记录一次只删除更新5000行记录。
如何修改大表的表结构,在主服务器建立一个新的表,
数据库分库分表的几种方式
把一个实例中的多个数据库拆分到不同的实例