MySql优化策略

MySql的优化

优化的方向：1.设计上；2.查询上；3.索引上；4.存储上

查看Sql执行频率

--- 查看服务器状态信息可以查看当前数据库主要操作类型：
show [session|global] status
show session status like 'Com%'; -- 查看当前会话统计结果。
show global status like 'Com%'； -- 查看自数据库上次启动至今统计结果。
show status like 'Innodb_row_%'； -- 查看针对Innodb引擎的统计结果。

查看 SQL执行频率

参数	含有
Com_select	执行select操作的次数
Com_insert	执行insert的操作次数，批量添加数据的insert操作只算一次操作。
Com_update	执行update操作的次数。
Com_delete	执行delete操作的次数。
Innodd_rows_read	select查询返回的行数。
Innodb_rows_inserted	执行insert操作插入的行数。
Innodb_rows_update	执行update操作更新的行数。
Innodb_rows_deleted	执行delete操作删除的行数。
Connections	试图链接MySql服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询次数。

定位低效率执行SQL

可以通过两种方法定位执行效率低的SQL语句。

1.慢日志查询

通过慢查询日志定位哪些执行效率较低的SQL语句。

-- 查看慢日志配置信息
show variables like '%slow_query_log%';
-- 查看慢日志记录SQL的最低阈值时间
show varuables like 'long_query_time%';
-- 修改慢日志记录SQL的最低阈值时间
set global long_query_time = 4;

2.show processlist：

该命令可以查看MySql在进行的线程，包括线程的状态、是否锁表等，可以实时地查看Sql的这些情况，同时对一些锁表操作进行优化。可以定位低效率执行Sql。

show processlist;

3.explain分析执行计划

定位到效率低的Sql语句后可以通过explain命令获取MySql如何执行select语句的信息，包括select语句执行过程中表如何连接和连接的顺序。

explain select * form 表名 where 列名 = ’查询条件‘; -- 分析执行计划指令：

字段含义表：

字段	含义
id	select查询的序列号，表示查询中执行select子句或操作表的顺序，id越大，优先级越高，越先执行。id相同则按从上到下顺序执行。
select_type	表示select的类型，常见取值有SIMPLE（简单表，即不使用表连接或子查询）、PRIMARY（主查询，即外层查询）、union（union中的第二个或者后面的查询语句）、subquery（子查询的第一个select）等。
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为（system->const->er_ref->ref->ref_on_null->index_merge->index_subquery->range->index->all）
possible_key	表示可能所有的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	这些情况的说明和描述

表示select的类型常见取值：

select_type	含义
SHIMPLE	简单的select语句查询，查询中不包括子查询或union。
PRIMARY	查询中若包括任何复杂的子查询，最外层的标记为PRIMARY
SUBQUERY	在select或where列表中包含的第一个子查询
DERIVED	在from列表中包含的子查询，被标记为DERIVEDMySql会递归执行这些子查询，把结果房子临时表中
UNION	若第二个select查询在union后，则标记为union；若union包含在from子句查询中，外层select将被标记为：DERIVED
UNIPN RESULT	从union表获取结果的select

type表示访问类型，可取值为：

type	含有
NULL	MySql不访问任何表，索引，直接返回结果
system	系统表，少量数据，往往不需要进行磁盘进行磁盘IO；如果是5.7版本的话就不是system了，而是all，即使只有一条记录
const	命中主键（primary key）或者唯一（unique）；被连接的部分是一个常量值；
eq_ref	对于前表的每一行，后表只有一行被扫描。（1）join查询；（2）命中主键查询或者非空唯一索引。（3）等值连接。
ref	非唯一索引扫描，返回匹配某个单独值的使有行。对于前表的每一行（row），后表可能多于一行的数据被扫描。
range	只检索给定返回的行，使用一个索引来选择行。where之后出现between，<,>,in等操作
index	需要扫描索引上的全部数据。
all	全部扫描，此时id上无索引。

结果值从好到坏：system -> const -> eq_ref -> ref -> range -> index -> all

exitra :其他额外的执行计划信息

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引进行读取，称为文件排序，效率低。
using temporary	需要建立临时表来暂存中间结果，常见与order by和group by；效率低
using index	sql所需要返回列数据均在一棵索引树上，避免访问吧的数据行，效率不错。

show profile分析sql

show profiles能够帮助我们了解时间都耗费到了哪里

select @@have_profiling;
set profiling = 1;  -- 开启profiling开关；
show profile for query query_id -- 查看sql执行过程中每个线程的状态和消耗时间

mysql支持进一步选择all，cpu，block io，context switch，page faults等明细类型类查看mysql在使用什么资源上消耗了过多的时间：
show profile cpu for query query_id

show profile 字段

字段	含义
Status	sql语句执行的状态
Duration	sql执行过程中每一个步骤的耗时
CPU_user	当前用户每一个步骤的耗时
CPU_system	系统占有的cpu

trace 分析优化器执行计划

Mysql5.6对sql的跟踪trace，通过trace文件能够进一步了解优化器为什么选择A计划而不是B计划。
打开ttrace，设置格式为json，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

set optimizer_trace=”enabled= on“,end_markers_in_json= on; -- 打开trace，设置格式为json。
set optimizer_trace_max_mem_size = 1000000; -- 设置trace最大能够使用的大小。
select * from user where uid < 2; -- 执行sq语句。

select * from information_schema.optimizer_trace\G; -- 检查mysql如何执行sql。

使用索引优化

select优化

索引是数据库优化最重要的手段之一。
避免索引失效

• 权值匹配
• 最左前缀法制
• 访问查询右边的列不能使用索引。
• 不要在索引列上进行运算操作，索引将失效。
• 字符串不加单引号，会造成索引失效。
• 尽量使用覆盖索引，避免select *需要从原表及磁盘上读取数据，从索引树中就可以查询到所有数据
• 如果查询列超出索引范围，也会降低性能
• 用or分割开的条件那么涉及到的索引都不会被用到
• 以%开头的模糊查询，索引会失效
• 如果mysql评估使用索引比全表更慢，则不使用索引，这种情况由数据本身特点决定
• is null，is not null索引有时有效，有时无效

插入数据优化

大批量插入数据使用load命令导入数据时，适当的设置可以提高导入的效率。对于innodb类型的表有以下几种方式提高导入效率：

• 主键顺序插入：1、首先检查全局系统变量’local_infile‘的状态是否开启内部主键，2、修改local_incile值：set global local_infile = 1;
• 关闭唯一性校验：set UNIQUE_CHECKS = 0;
• 如果对一张表插入很多行数据时，尽量使用多个值表的insert语句
• 在事务中进行数据插入。
• 数据有序插入

优化order by语句

两种排序方式：

• Filesort排序，所有不是通过索引直接返回结果的排序都是Filesort排序，通过创建合适的索引，能够减少Filesort的出现。对于Filesort，MySql有两种排序算法：（1）两次扫描算法：MySql4.1之前，使用该排序方式，首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序，如果sort buffer不够，则在临时表temporary中存储排序结果，完成排序后根据行指针回表读取记录，该操作会导致大量IO操作。（2）一次扫描算法：一次取出满足条件的所有字段，然后在排序区sort buffer中直接输出结果集，但排序时内存开销较大。MySql通过变量max_length_for_sort_data的大小和Query语句取出的字段总大小来判断所有哪种排序算法。
• using index：通过有序索引顺序扫描直接返回有序数组，不需要额外排序，操作效率高

group by优化

分组时避免排序结果的消耗：执行order by null：select age,count(*) from emp group by age order by null;

优化子查询

使用高效的连接（join）替代

优化limit查询

如果一般查询大量数据最后几条数据时，则会排序之前的所有数据，查询排序代价非常大。
优化思路：

• 在索引上完成分页操作，最后根据主键关联回原表查询所需要的其他内容
• 该方案适用于主键自增的表，可以把limit查询转换成某个位置查询。