MySQL的优化

优化MySQL的几点：

从设计上优化

从查询上优化

从索引上优化

从存储上优化

1，SQL的执行频率

MySQL客户端连接成功后，通过show [session/global] status命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型

参数	含义
Com_select	执行select操作的次数，一次查询只累加1
Com_insert	执行INSERT 操作的次数,对于批量插入的 INSERT操作,只累加一次
Com_update	执行UPDATE 操作的次数
Com_delete	执行DELETE 操作的次数
Innodb_rows_read	select查询返回的行数
Innodb_rows_inserted	执行INSERT操作插入的行数
Innodb_rows_updated	执行UPDATE 操作更新的行数
Innodb_rows_deleted	执行DELETE 操作删除的行数
Connections	试图连接 MySQL服务器的次数
Uptime	服务器工作时间
Slow_queries	慢查询的次数

-- 查看当前会话SQL执行类型的统计信息
show session status like 'Com_______';-- 7个_

-- 查看全局会话SQL执行类型的统计信息(自从上次MySQL服务器启动至今)
show global status like 'Com_______';

-- 查看针对InnoDB引擎的统计信息
show status like 'Innodb_rows_%';

2，定位低效率执行SQL

方式：

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

show processlist:该命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看sQL的执行情况，同时对一些锁表操作进行优化

（1）慢日志查询

-- 查询慢查询日志配置信息
show variables like 'slow_query_log%';

-- 开启慢查询日志
set global slow_query_log=1;

-- 查看慢查询日志的最低时间 查询大于等于10秒的会记录
show variables like 'long_query_time%';

-- 修改最低时间
set global long_query_time=5;

（2）show processlist

show processlist;

1）id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看

2）user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

3）host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

4）db列，显示这个进程目前连接的是哪个数据库

5）command列，显示当前连接的执行的命令，一般取值为休眠(sleep），查询(query)，连接(connect）等

6）time列，显示这个状态持续的时间，单位是秒

7）state列，显示使用当前连接的sq语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sqi语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成

8）info列，显示这个sql语句，是判断问题语句的一个重要依据

3，explain分析执行计划

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序
select_type	表示SELECT的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION (UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SELECT)等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system .> const .……eq_ref ….ref ..…ref_or_null--> index_merge ---> index_subquery--> range ---…-> index ---> all)
possible_keys	表示查询时。可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

-- 查询执行计划
explain select * from user where uid=1;

-- 查询执行计划
explain select * from user where uname='张飞';

（1）id

-- 1、id相同，表示加载表的顺序是从上到下
explain select * from user u,user_role ur,role r where u.uid=ur.uid and ur.rid=r.rid;

-- 2、不同id越大，优先级越高，越先被执行
explain select * from role where rid =(select rid from user_role where uid=(select uid from user where uname='张飞'));

-- 3、id相同，也有不同，同时存在，id相同可认为是一组，从上往下顺序：在所有组中，id值越大，优先级越高，越先执行
explain select * from role r,(select * from user_role ur where ur.uid =(select uid from user where uname ='张飞')) t where r.rid=t.rid;

（2）select_type

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询,最外层查询标记为该标识
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DERIVED	在FROM列表中包含的子查询，被标记为DERIVED(衍生)MYSQL会递旧执行这些子查询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后，则标记为UNION;若UNION包含在FROM子句的子查询中，外层SELECT将被标记为:DERIVED
UNION  RESULT	从UNION表获取结果的SELECT

explain select * from user;-- SIMPLE
explain select * from user u,user_role ur where u.uid=ur.uid;--SIMPLE

explain select * from role where rid =(select rid from user_role where uid=(select uid from user where uname='张飞'));-- PRIMARY

explain select * from role where rid =(select rid from user_role where uid=(select uid from user where uname='张飞'));-- SUBQUERY

explain select *from (select * from user limit 2) t;-- DERIVED

explain select * from user where uid=1 union select * from user where uid=3;-- UNION/UNION  RESULT

（3）type

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

结果值从最好到最坏以此是: system > const > eq_ref > ref > range > index > ALL

explain select * from user;-- type

explain select now();-- NULL 

explain select * from mysql.tables_priv;-- system 查询系统表
 
explain select * from user where uid=2;-- const
create unique index index_uname on user(uname);-- 添加普通索引
explain select * from user where uname='张飞';-- const
drop index index_uname on user;

explain select * from user where uid>2;-- ranger

explain select uid from user;-- index

explain select * from user;-- ALL

（4）table

显示这一步所访问数据库中表名称有时不是真实的表名字，可能是简称

（5）rows

扫描行的数量

（6）key

possible_keys:显示可能应用在这张表的索引，一个或多个

key :实际使用的索引，如果为NULL，则没有使用索引

key_len:表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

（7）extra

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

4，show file分析SQL

通过have_profiling参数,能够看到当前MySQL是否支持profile:

select @@have_profiling;

set profiling=1; -- 开启profiling开关

执行完上述命令之后，再执行show profiles指令，来查看sQL语句执行的耗时:

show profiles; 

show profiles;

-- 查看每条时间
show profile for query 154;

-- 查看cup耗费时间
show profile cpu for query 154;

5，trace优化器

打开trace，设置格式为JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示

SET optimizer_trace="enabled=on" , end_markers_in_json=on;

set optimizer_trace_max_mem_size=1000000;

select * from information_schema.optimizer_trace \G;

6，索引优化

（1）全值匹配

和字段匹配即可，和字段无关

-- 全值匹配
explain select * from tb_seller where name='华为' and status='0' and address='北京市';

（2）最左前缀法

如果索引有多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列

-- 最左前缀法
-- 如果索引有多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列
explain select * from tb_seller where name='华为';-- 403

explain select * from tb_seller where name='华为' and status='0';-- 410
explain select * from tb_seller where status='0' and name='华为';-- 410

-- 违法最左前缀法，索引失效
explain select * from tb_seller where status='0';

-- 跳跃一个，只有最左端索引失效
explain select * from tb_seller where name='华为' and address='北京市';-- 403

（3）其他匹配原则

范围查询右边的列,不能使用索引

-- 根据前面的两个字段name , status查询是走索引的，但是最后一个条件address没有用到索引
explain select * from tb_seller where name='华为' and status>'1' and address='北京市';
 
-- 不要在索引列上进行运算操作,索引将失效
explain select * from tb_seller where substring(name,3,2)='科技';

-- 字符串不加单引号，索引失效
explain select * from tb_seller where name='华为' and status=0;

extra	解释
using index	使用索引覆盖就会出现
using where	在查找使用索引的情况下，需要回表去查询所需的数据
using index condition	查找使用了索引，但是需要回表查询数据
using index,using where	查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

-- 尽量使用覆盖索引，避免使用select *
explain select * from tb_seller where name='华为' and address='北京市';-- 效率低
-- 从索引树中获得所有数据
explain select name from tb_seller where name='华为' and address='北京市';-- 效率高
explain select name，status,password from tb_seller where name='华为' and address='北京市';-- 效率低

1，用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

2，以%开头的Like模糊查询，索引失效。——》弥补不足：不用*，使用索引列

3，如果MySQL评估使用索引比全表更慢,则不使用索引（由数据本身决定）

4，is NULL , is NOT NULL有时有效,有时索引失效（数据少的一方用索引）

5，in走索引，not in索引失效

6，单列索引和复合索引,尽量使用符合索引（如果一张表有多个单列索引，即使where中都使用了这些索引列，则只有一个最优索引生效）

7，SQL优化

（1）大批插入数据

1）主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果innoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点,来提高导入数据的效率

通过load向表加载数据时，保持主键有序，提高效率

主键有序时间<<主键无序时间

-- 1、首先，检查一个全局系统变量 'local_infile' 的状态， 如果得到如下显示 Value=OFF，则说明这是不可用的
show global variables like 'local_infile';
 
 
-- 2、修改local_infile值为on，开启local_infile
set global local_infile=1;

2）关闭唯一性校验

在导入数据前执行SET UNIQUE_CHECKS=O，关闭唯一性校验，在导入结束后执行SETUNIQUE_CHECKS=1，恢复唯一性校验,可以提高导入的效率。

（2）insert优化

1，如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快

2，在事务中进行数据插入

3，数据有序插入

（3）order by优化

1）两种排序方式

第一种是通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序都叫FileSort 排序

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高

-- 创建组合索引
create index idx_emp_age_salary on emp(age,salary);
 
-- 排序,order by
explain select * from emp order by age;        -- Using filesort
explain select * from emp order by age,salary; -- Using filesort
 
 
explain select id from emp order by age;  -- Using index
explain select id,age from emp order by age;  -- Using index
explain select id,age,salary,name from emp order by age;  -- Using filesort
 
-- order by后边的多个排序字段要求尽量排序方式相同
explain select id,age from emp order by age asc, salary desc;  -- Using index; Using filesort
explain select id,age from emp order by age desc, salary desc;  -- Backward index scan; Using index
 
-- order by后边的多个排序字段顺序尽量和组合索引字段顺序一致
explain select id,age from emp order by salary,age; -- Using index; Using filesort

 2）Filesort优化

通过创建合适的索引，能够减少Filesort的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快Filesort的排序操作。对于Filesort , MySQL有两种排序算法:

1）两次扫描算法:MySQL4.1之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort bufer中排序，如果sort buffer不够，则在临时表temporary table中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机/O操作

2）一次扫描算法:一次性取出满足条件的所有字段，然后在排序区sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高

MysQL通过比较系统变量 max_length_for_sort_data的大小和Query语句取出的字段总大小，来判定是否那种排序算法，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法;否则使用第一种

可以适当提高sort_buffer_size和max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率

（4）子查询优化

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的sQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JoIN）替代

explain select * from user where uid in (select uid from user_role );

explain select * from user u join user_role ur on u.uid=ur.uid;

连接(Join)查询之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作

（5）limit优化

方法一：

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

explain select * from tb_user limit 900000,10;

explain select * from tb_user a,(select id from tb_user order by id limit 900000,10) b where a.id=b.id;

方法二：

该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询

explain select * from tb_user where id>900000 limit 10;