mysql查询优化

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行记录。

如何修改大表的表结构，在主服务器建立一个新的表，

数据库分库分表的几种方式

把一个实例中的多个数据库拆分到不同的实例