mysql调优

调优金字塔

架构调优：不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做。写的并发量有多大，是否需要采用分布式；最后考虑读的压力是否很大，是否需要读写分离。

MySQL调优：业务表结构设计是否合理，SQL语句优化是否足够，该添加的索引是否都添加了，是否可以剔除多余的索引。

硬件和OS调优：需要对硬件和OS有着非常深刻的了解。

慢查询

long_query_time参数设定的时间阈值的SQL语句的日志。默认情况下，慢查询日志是关闭的。

请求了不需要的数据。总是取出全部列无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。（如下的问题sql）

select * from user limit 10000,20；

响应时间：响应时间是两个部分之和:服务时间和排队时间

扫描的行数，返回的行数：查看该查询扫描的行数是非常有帮助的。这在一定程度上能够说明该查询找到需要的数据的效率高不高。理想情况下扫描的行数和返回的行数应该是相同

访问类型：EXPLAIN语句中的type列反应了访问类型，全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。

set GLOBAL slow_query_log=1;#开启慢查记录
set global long_query_time=10;#10秒
set global log_queries_not_using_indexes=1;#开启未用索引记录

# 漫查文件路径
show VARIABLES like '%slow_query_log_file%';

Explain执行计划

Explain可以了解到：表的读取顺序，数据读取操作的操作类型，哪些索引可以使用，哪些索引被实际使用，表之间的引用，每张表有多少行被优化器查询。  

id ： 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id

select_type ： SELECT关键字对应的那个查询的类型

table ：表名

partitions ：匹配的分区信息

type ：针对单表的访问方法，结果值从最好到最坏依次是system>const>eq_ref>ref>range>index>ALL。 一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys ：可能用到的索引

key ：实际上使用的索引

key_len ：实际使用到的索引长度 索引列的类型是VARCHAR(100)，使用的字符集是utf8，那么该列实际占用的最大存储空间就是100 x 3 = 300个字节。 如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。 变长字段，都会有2个字节的空间来存储该变长列的实际长度。 bigint，key_len大小就是8个字节。

ref ：当使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows ：预估的需要读取的记录条数

filtered ：某个表经过搜索条件过滤后剩余记录条数的百分比

Extra ：—些额外的信息

    查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询，如下id一样。

EXPLAIN
SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 WHERE order_no = 'a');

 UNION去重时会多生成一条没有id的计划。union all不存在。

驱动表与被驱动表：A表和B表join连接查询，如果通过A表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到B表中查询数据，然后合并结果。那么我们称A表为驱动表，B表为被驱动表。

 查询优化器

优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。

WHERE子句中的几个搜索条件的顺序对查询结果没有任何影响。优化器会分析决定。

高性能的索引使用策略

不在索引列上做任何操作：如 WHERE id + 1 = 17。

尽量全值匹配：建立了联合索引列后，如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配。

最佳左前缀法则：建立了联合索引列，如果搜索条件不够全值匹配，从索引的最左前列开始并且不跳过索引中的列。

范围条件放最后：也是针对联合索引来说的，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，右边的列进行范围查找性能会好很多。

覆盖索引尽量用：也是针对联合索引。

不等于要慎用：无法使用索引会导致全表扫描（!=， <>）

 Null有影响：除了增加长度，还容易导致全表扫描

Like查询：like以通配符开头('%abc...')，mysql索引失效会变成全表扫描。但是如果使用覆盖索引可以改善这个问题。

字符类型加引号：字符串不加单引号索引失效

使用or关键字时要注意：用相同索引字段来作为条件，否则容易全表扫描。

ASC、DESC别混用：联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。

尽可能按主键顺序插入行：从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕，它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

优化Count查询：很难优化，MySQL层面能做的基本只有索引覆盖扫描了，可以用估算值取代精确值，可以增加汇总表，或者增加类似Redis这样的外部缓存系统。

 优化limit分页：尽量避免深分页信息。