微信技术群：Day9884125

注意：正式阅读文章之前必看

1、此优化是建立在mysql5.7数据库版本，具体可能会和其他版本指令有差异，出现问题自行百度解决补充
2、里面代码字符串用的单引号，是word里面的，直接把小编中sql代码放在程序中跑，会报错，请先修改。因为是小编从自己word文档上复制出来的，且代码修改地方太多。小编不做手动处理，如果其他人原因可以修改一下

1 优化sql步骤

在应用的开发过程中，由于初期数据量小，开发人员写sql语句时更重视功能上的实现。但是当应用系统正式上线后，随着生产数量的急剧增加，很多sql语句开始逐渐暴露出性能问题，对生产的影响也越来越大，此时这些有问题的sql语句就成为整个系统性能的瓶颈，因此我们必须要对他们优化。

1.1 查看SQL执行频率

MySQL客户端连接成功后，通过show [session | global] status命令可以提供服务器状态信息。 show [session | global] status可以根据需要加上参数session 或者 global来显示session级（当前连接）的统计结果和global级（数据库上次启动自今）的统计结果。如果不写默认使用参数是session。
代码指令

-- 下面的命令显示了数据库当前session中所有统计参数的值：（查的是链接的数据库信息）
show status like ‘com_______’;

-- 查询数据库上次启动自今的统计参数值：
show global status like ‘com_______’;

-- 针对InnoDB存储引擎的指令：
show global status like ‘Innodb_rows_%’;

-- 登录mysql数据库指令：
mysql -u root -p

com_xxx表示每个xxx语句执行的次数，我们通常比较关心的是一下几个统计参数。
com_select：执行select操作的次数，一次查询只累加1.
com_insert：执行insert操作的次数，对于批量插入的insert操作，只累加1次。
com_update：执行update操作的次数
com_delete：执行delete操作的次数
com_rows_read：select查询返回的行数。
com_rows_inserted：执行insert操作插入的行数。
com_rows_updated：执行updated操作更新的行数。
com_rows_delete：执行delete操作删除的行数。
connections：视图连接mysql服务器的次数。
uptime：服务器工作时间
slow_queries：慢查询的次数
com_xxx：这些参数对于所有存储引擎的表操作都会进行累计
innodb_xxx：这几个参数只是针对innodb存储引擎的，累加的算法也略有不同

1.2 定位低效率执行sql

可以通过以下两种方式定位执行效率较低的sql语句。
l 慢查询日志：通过慢查询日志定位哪些执行效率较低的SQL语句，用---log-slow-queries[ =file_name]选项启动时。mysqld写一个包含所有执行时间超过long_query_time秒的sql 语句的日志文件。
l show processlist：慢查询日志在查询结果以后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前mysql进行的线程，包括线程的状态、是否锁表等，可以实时的查看sql的执行情况，同时对一些锁表操作进行优化。

id列：用户登录mysql时，系统分配的connection_id，可以使用函数connection_id（）查看
user列：显示当前用户，如果不是root，这个命令就只显示用户权限范围的sql语句
host列：显示这个语句是从哪个ip的那个端口上发的，可以用来跟踪出现问题语句的用户。
db列：显示这个进程目前连接的是哪个数据库
command列：显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
time列：显示这个状态持续的时间，单位是秒
state列：显示当前连接的sql语句的状态，很重要的列，state描述的是语句执行中的某一个状态，一个sql语句，以查询为例，可能需要经过copying to tmp table, sorting result
sending data等状态才可以完成
info列：显示这个sql语句，是判断问题语句的一个重要依据。

查询表结构指令：

-- tb_item是表名
desc tb_item;

1.3 explain分析执行计划

通过以上步骤查询到效率低的sql语句后，可以通过explain或者desc命令获取mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
查询sql语句的执行计划：

explain select * from tb_item where id=1;

注意：使用上面sql语句时，要先进入某一个数据库，指令为：use fire;

explain select * from cargobudgetreport where projectName='服服务类滨海支队综合查询5';

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：执行情况的说明和描述

1.3.1 环境准备：

创建三张表

l 用户表
l 角色表
l 用户和角色的关联表

create table ‘t_role’(
‘id’ varchar(32) not null,
‘role_name’ varchar（255）default null,
‘role_code’ varchar（255）default null,
‘description’ varchar（255）default null,
primary key(‘id’),
unique key ‘unique_role_name’(‘role_name’)
)engine=innoDB default charset = utf8;

create table ‘t_user’(
‘id’ varchar(32) not null,
‘username’ varchar（45）not null,
‘password’ varchar（96）not null,
‘name’ varchar(45) not null,
primary key(‘id’),
unique key ‘unique_user_username’(‘username’)
)engine=innoDB default charset = utf8;
create table ‘user_role’(
‘id’ int(11) not null auto_increment,
‘user_id’ varchar（32）default null,
‘role_id’ varchar（32）default null,
‘name’ varchar(45) not null,
primary key(‘id’),
key ‘fk_ur_user_id’(‘user_id’),
key ‘fk_ur_role_id’(‘role_id’),
constraint fk_ur_role_id foreign key (‘role_id’) references ‘t_role’ (‘id’) on delete no action on update no action,
constraint fk_ur_user_id foreign key (‘user_id’) references ‘t_user’ (‘id’) on delete no action on update no action,
)engine=innoDB default charset = utf8;

测试数据：

insert into ‘t_user’(‘id’, ‘username’, ‘password’, ‘name’)
values(‘1’, ‘super’, ‘123456’, ‘超级管理员’);
insert into ‘t_user’(‘id’, ‘username’, ‘password’, ‘name’)
values(‘2’, ‘admin’, ‘123456’, ‘系统管理员’);
insert into ‘t_user’(‘id’, ‘username’, ‘password’, ‘name’)
values(‘3’, ‘itcast’, ‘123456’, ‘test02’);
insert into ‘t_user’(‘id’, ‘username’, ‘password’, ‘name’)
values(‘4’, ‘stu1’, ‘123456’, ‘学生1’);
insert into ‘t_user’(‘id’, ‘username’, ‘password’, ‘name’)
values(‘5’, ‘stu2’, ‘123456’, ‘学生2’);
insert into ‘t_user’(‘id’, ‘username’, ‘password’, ‘name’)
values(‘6’, ‘t1’, ‘123456’, ‘老师’);

insert into ‘t_role’(‘id’, ‘role_name’, ‘role_code’, ‘description’)
values(‘5’, ‘学生’, ‘student’, ‘学生’);
insert into ‘t_role’(‘id’, ‘role_name’, ‘role_code’, ‘description’)
values(‘7’, ‘老师’, ‘teacher’, 老师’);
insert into ‘t_role’(‘id’, ‘role_name’, ‘role_code’, ‘description’)
values(‘8’, ‘教学管理员’, ‘teachmanager’, ‘教学管理员’);
insert into ‘t_role’(‘id’, ‘role_name’, ‘role_code’, ‘description’)
values(‘9’, ‘管理员’, ‘admin’, ‘管理员’);
insert into ‘t_role’(‘id’, ‘role_name’, ‘role_code’, ‘description’)
values(‘10’, ‘超级管理员’, ‘super’, ‘超级管理员’);

insert into user_role(‘id’, ‘user_id’, ‘role_id’)values(null, ‘1’, ‘5’), (null, ‘1’, ‘7’), (null, ‘2’, ‘8’),
(null, ‘3’, ‘9’), (null, ‘4’, ‘8’), (null, ‘5’, ‘10’);

1.3.2 explain之id

id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序，id情况有三种：
1）id相同表示加载表的顺序是从上到下。

explain select * 
from t_role r, t_user u, user_role ur 
where r.id=ur.role_id and u.id=ur.user_id;

2）id不同id值越大，优先级越高，越先被执行。

explain select *
from t_role 
where id=(select role_id 
          from user_role 
          where user_id=(select id from t_user where username=’stu1’))

1.3.3 explain之select_type

表示select的类型，常见的取值，如下表所示： 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 从上到下效率越来越低

1.3.4 explain之table

展示这一行的数据是关于哪一张表的

1.3.5 explain之type

type显示的是访问类型，是较为重要的一个指标，可取值为:
type含义：
null：mysql不访问任何表，索引，直接返回结果
system：表只有一行记录（等于系统表），这是const类型的特例，一般不会出现
const：表示通过索引一次就能找到，const用于比较primary key或者unipue索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转化为一个常量。const于将主键或唯一索引的所有部分与常量值进行比较。
eq_ref：类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常用于主键或唯一索引扫描。
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）。
range：只检索给定返回的行，使用一个索引来选择行。where之后出现between，<，>in 等操作。
index：index与all的区别为index类型只是遍历了索引树，通常比all快，all是遍历数据文件。
all：将遍历全表以找到匹配的行
从上到下效率越来越低

1.3.6 explain之key

possible_keys：显示可能应用在这张表的索引，一个或多个。
key：实际使用的索引，如果为null，则没有使用索引
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

1.3.7 explain之rows

扫描行的数量

1.3.8 explain之extra

其他的额外的执行技术信息，在该列展示。
extra含义：
using filesort: 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为文件排序
using temporary: 使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于order by 和group by
using index: 表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错。
如果出现using filesort和 using temporary这时候就要考虑对sql语句进行优化了

1.4 show profile分析sql

mysql从5.0.37版本开始增加了对show profiles和show profile语句的支持。show profiles能够在做sql优化时帮助我们了解时间都耗费到哪里去了。
通过have_profiling参数，能够看到当前mysql是否支持profile：
以下说明支持
使用的指令为：select @@have_profiling;
查看proiling是否开启：以下内容为0代表没有开启。默认profiling是关闭的，可以通过set语句在Session级别开启profiling
指令：select @@profiling;
set profiling=1; //开启profiling开关；
通过profile，我们能够更清楚的了解sql执行的过程。
首先，我们可以执行一系列的操作，如下图所示：

-- 显示mysql中有哪些数据库
show databases;      
-- 使用mysql中的某一个数据库
use db01;			
-- 查询该数据库下有哪些表
show tables;

select * from tb_item where id<5;
select count(*) from tb_item;

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

指令：show profiles

通过show profile for query query_id语句可以查看到该sql执行过程中每个线程的状态和消耗的时间：
在获取到最消耗时间的线程状态之后，mysql支持进一步选择all, block io, context switch、page faults等明细类型查看mysql在使用资源上消耗了过高的时间。例如，选择查看CPU的耗费时间：

1.5 trace分析优化器执行计划

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

set optimizer_trace=”enabled=on”, end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

执行sql语句：

select * from tb_item where id<4;

最后，检查information_schema.optimizer_trace就可以知道mysql是如何执行sql的：

select * from information_schema.optimizer_trace\G;

2 索引的使用

索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的mysql的性能优化问题。

2.1 验证索引提升查询效率

在我们准备的表结构tb_item中，一共存储了300万记录； A根据ID查询

select * from tb_item where id=1999\G;

查询速度很快，接近0秒，主要原因是因为id为主键，有索引，查询不是索引的title字段300万数据大约需要5秒；创建索引再查需要0.01秒。 处理方案，针对title字段，创建索引：

create index idx_item_title on tb_item(title);

创建索引花费时间比较长，大约一分钟时间。

2.2 索引的使用

2.2.1 准备环境

create table ‘tb_seller’(
‘sellerid’ varchar(100),
‘name’ varchar(100),
‘nickname’ varchar(50),
‘password’ varchar(60),
‘status’ varchar(1),
‘address’ varchar(100),
‘createtime’ datatime,
primary key(‘sellerid’)
)engine=innodb default charset=utf8mb4;

insert into ‘tb_seller’ (‘sellerid’, ‘name’, ‘nickname’, ‘password’, ‘status’, ‘address’, ‘createtime’)
values(‘alibaba’, ‘阿里巴巴’, ‘阿里小店’, ‘123456’, ‘1’, ‘北京’, ‘2020-01-01 12:00:00’);
insert into ‘tb_seller’ (‘sellerid’, ‘name’, ‘nickname’, ‘password’, ‘status’, ‘address’, ‘createtime’)
values(‘baidu’, ‘百度科技有限公司’, ‘百度小店’, ‘123456’, ‘1’, ‘北京’, ‘2020-01-01 12:00:00’);
insert into ‘tb_seller’ (‘sellerid’, ‘name’, ‘nickname’, ‘password’, ‘status’, ‘address’, ‘createtime’)
values(‘huawei’, ‘华为科技有限公司’, ‘华为小店’, ‘123456’, ‘1’, ‘北京’, ‘2020-01-01 12:00:00’);
insert into ‘tb_seller’ (‘sellerid’, ‘name’, ‘nickname’, ‘password’, ‘status’, ‘address’, ‘createtime’)
values(‘itcast’, ‘传智播客教育有限公司’, ‘传智播客’, ‘123456’, ‘1’, ‘北京’, ‘2020-01-01 12:00:00’);
insert into ‘tb_seller’ (‘sellerid’, ‘name’, ‘nickname’, ‘password’, ‘status’, ‘address’, ‘createtime’)
values(‘itheima’, ‘黑马程序员’, ‘黑马程序员’, ‘123456’, ‘1’, ‘北京’, ‘2020-01-01 12:00:00’);

create index idx_seller_name_sta_addr on tb_seller(name,status,address);

2.2.2 避免索引失效

1）全值匹配，对索引中所有列都指定具体值。 该情况下，索引生效，执行效率高。

explain select * 
from tb_seller 
where name=’小米科技’ and status=’1’ and address=’北京’\G;

2）最左前缀法则（复合索引当中遵守的法则）
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
3）不要在索引列上进行范围查询
4）不要在索引列上进行运算操作，索引将失效。

select * from tb_seller where substring(name, 3, 2)=’科技’;

5）字符串不加单引号，造成索引失效

explain select * from tb_seller where name=’科技’ and status=’0’;
explain select * from tb_seller where name=’科技’ and status=0;

6）尽量使用覆盖索引，避免select * ;
尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select *

1）用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。示例：name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的：

注意：小编在其他文档上看到过，or关键字在5时候是会索引失效的，5之后是不会索引失效。具体的情况
大家优化的时候可以验证一下。小编就不在这说具体验证了

explain select * 
from tb_seller 
where name=’黑马程序员’ or createtime=’2020-01-01 12:00:00’;

2）以%开头的like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

-- 索引生效
explain select * from tb_seller where name like ‘科技%’;  
-- 索引不生效
explain select * from tb_seller where name like ‘%科技’;
-- 索引不生效
explain select * from tb_seller where name like ‘%科技%’;

解决模糊查询导致索引失效的问题：可以使用覆盖id解决，就是查询结果中全都是索引列，查询中一旦出现不是索引列，则又会导致索引失效。
其中sellerid是主键，所以sellerid有索引，name, status, address是索引列

explain select sellerid, name, status, address 
from tb_seller 
where name like ‘%科技%’;

而password不是索引列,这时以下sql索引会失效

explain select sellerid, name, status, address, password 
from tb_seller 
where name like ‘%科技%’;

3）如果mysql评估使用索引比全表扫描更慢，则不会使用索引。
某一列的数据结果在数据库中占很大比例，则会放弃索引。
4）is null，is not null有时索引失效。
和数据库中的数据量有关，如果null在数据库中占绝大部分，则is not null走索引。

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

-- 走索引
explain select * 
from tb_seller 
where sellerid in (‘oppo’, ‘xiaomi’, ‘sina’);   

-- 不走索引
explain select * from tb_seller where sellerid not in (‘oppo’, ‘xiaomi’, ‘sina’);

6）单列索引和复合索引尽量使用复合索引，而少使用单列索引。 创建复合索引：

create index idx_name_sta_address on tb_seller(name, status, address);

-- 相当于创建三个索引：
name
name+status
name+status+address

创建单列索引:

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

数据库会选择一个最优的索引(识别度最高索引)来使用，并不会使用全部索引。
删除索引操作指令：

drop index idx_seller_address on tb_seller;

查看某一个表里有哪些索引指令：

show index from tb_seller;

2.3查看索引使用情况

-- 查看当前会话的索引使用情况
show status like ‘Handler_read%’;       
-- 查看全局的索引使用情况
show global status like ‘Handler_read%’;

Handler_read_first: 索引中第一条被读的次数，如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。
Handler_read_key: 如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
Handler_read_next: 按照键顺序读下一行的请求数，如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev: 按照键顺序读前一行的请求数，该读方法主要用于优化order by ...desc
Handler_read_rnd: 根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要mysql扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引类补救。
Handler_read_rnd_next: 在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

3 sql优化

3.1 大批量插入数据

环境准备：

create table ‘tb_user_1’(
‘id’ int(11) not null auto_increment,
‘username’ varchar(45) not null,
‘password’ varchar(96) not null,
‘name’ varchar(45) not null,
‘birthday’ datetime default null,
‘sex’ char(1) default null,
’email’ varchar(45) default null,
‘phone’ varchar(45) default null,
‘qq’ varchar(32) default null,
‘status’ varchar(32) not null comment ‘用户状态’,
‘create_time’ datetime not null,
‘update_time’ datetime default null,
primary key(‘id’),
unique key ‘unique_user_username’(‘username’)
)engine=InnoDB default charset=utf8

create table ‘tb_user_2’(
‘id’ int(11) not null auto_increment,
‘username’ varchar(45) not null,
‘password’ varchar(96) not null,
‘name’ varchar(45) not null,
‘birthday’ datetime default null,
‘sex’ char(1) default null,
’email’ varchar(45) default null,
‘phone’ varchar(45) default null,
‘qq’ varchar(32) default null,
‘status’ varchar(32) not null comment ‘用户状态’,
‘create_time’ datetime not null,
‘update_time’ datetime default null,
primary key(‘id’),
unique key ‘unique_user_username’(‘username’)
)engine=InnoDB default charset=utf8;

当使用load，命令导入数据的时候，适当的设置可以提高导入的效率。

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

脚本文件介绍： sql1.log --------->主键有序 sql2.log ---------->主键无序

1）插入ID顺序排列数据：
导入数据指令：

load data local infile ‘/root/sq1.log‘ into table ‘tb_user_1’ fields terminated by ‘,’ lines 
terminated by ‘\n’;

2）关闭唯一性校验：
在导入数据前执行set unique_checks=0，关闭唯一性校验，在导入结束后执行set unique_checks=1，恢复唯一性校验，可以提高导入的效率。

set unique_checks=0;
load data local infile ‘/root/sq1.log‘ into table ‘tb_user_1’ fields terminated by ‘,’ lines 
terminated by ‘\n’;
set unique_checks=1;

3）手动提交事务
如果应用使用自动提交的方式，建议在导入之前执行set autocommit=0，关闭自动提交，导入结束后再执行set autocommit=1，打开自动提交，也可以提高导入的效率。

set autocommit=0;
load data local infile ‘/root/sq1.log‘ into table ‘tb_user_1’ fields terminated by ‘,’ lines 
terminated by ‘\n’;
set autocommit=1;

3.2 优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。
如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
示例，原始方式为：

insert into tb_test values(1, ‘tom’);
insert into tb_test values(2, ‘cat’);
insert into tb_test values(3, ‘jerry’);

优化后的方案为：

insert into tb_test values(1, ‘tom’),(2, ‘cat’),(3, ‘jerry’);

在事务中进行数据插入：

start transaction;
insert into tb_test values(1, ‘tom’);
insert into tb_test values(2, ‘cat’);
insert into tb_test values(3, ‘jerry’);
commit;

数据有序插入：

insert into tb_test values(4, ‘tim’);
insert into tb_test values(1, ‘tom’);
insert into tb_test values(3, ‘jerry’);
insert into tb_test values(5, rose’);
insert into tb_test values(2, ‘cat’);

优化后：

insert into tb_test values(1, ‘tom’);
insert into tb_test values(2, ‘cat’);
insert into tb_test values(3, ‘jerry’);
insert into tb_test values(4, ‘tim’);
insert into tb_test values(5, ‘rose’);

3.3 优化order by语句

3.3.1 环境准备

create table ‘emp’(
‘id’ int(11) not null auto_increment,
‘name’ varchar(100) not null,
‘age’ int(3) not null,
‘salary’ int(11) default nnull,
primary key(‘id’)
)engine=InnoDB default charset=utf8mb4;

insert into ‘emp’ (‘id’, ‘name’, ‘age’, ‘salary’) values(1, ‘tom’, ’25’, ‘2300’);
insert into ‘emp’ (‘id’, ‘name’, ‘age’, ‘salary’) values(2, ‘jerry’, ‘30’, ‘3500’);
insert into ‘emp’ (‘id’, ‘name’, ‘age’, ‘salary’) values(3, ‘luci’, ‘25’, ‘2800’);
insert into ‘emp’ (‘id’, ‘name’, ‘age’, ‘salary’) values(4, ‘jay’, ‘36’, ‘3500’);
insert into ‘emp’ (‘id’, ‘name’, ‘age’, ‘salary’) values(5,’tom2’, ‘21’, ‘2200’);
insert into ‘emp’ (‘id’, ‘name’, ‘age’, ‘salary’) values(6,’jerry2’, ‘31’, ‘3300’);
insert into ‘emp’ (‘id’, ‘name’, ‘age’, ‘salary’) values(7,’luci2’, ‘26’, ‘2700’);
insert into ‘emp’ (‘id’, ‘name’, ‘age’, ‘salary’) values(8,’jay2’, ‘33’, ‘3500’);
insert into ‘emp’ (‘id’, ‘name’, ‘age’, ‘salary’) values(9,’tom3’, ‘23’, ‘2400’);
insert into ‘emp’ (‘id’, ‘name’, ‘age’, ‘salary’) values(10,’jerry3’, ‘32’, ‘3100’);
insert into ‘emp’ (‘id’, ‘name’, ‘age’, ‘salary’) values(10,’luci3’, ‘26’, ‘2900’);
insert into ‘emp’ (‘id’, ‘name’, ‘age’, ‘salary’) values(10,’jay2’, ‘37’, ‘4500’);

create index idx_emp_age_salary on emp(age, salary);

3.3.2两种排序方式

1）第一种是通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序都叫filesort排序。
指令代码：select * from emp order by dutyId desc;
查看性能代码：explain select * from emp order by sutyId desc;

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
注意：查询结果和排序条件必须都是索引列，不然就是下面结果。 此时dutyId不是索引列，empId是主键所以它是索引列
指令代码：select empId from emp order by dutyId desc;
查看性能代码：explain select empId from emp order by dutyId desc;

注意：查询结果中有一个不是索引列，即使排序条件是索引列，也不会是成为using index。
此时dutyId是索引列，empId是主键所以它是索引列，empName不是索引列

多字段进行排序
要么全是升序，要么全是降序。不可以既有升序又有降序
此时dutyId、age empId都是索引

-- 可以
explain select empId from emp order by dutyId desc, age desc;    
-- 可以
explain select empId from emp order by dutyId asc, age asc;		

注意 此处第三条代码，oracle是可以的，mysql5版本以上的可不可以，请百度自查验证，
然后补充，小编此处不做查阅
-- 不可以 不是统一升降序    
explain select empId from emp order by dutyId asc, age desc;	

-- 不可以 没有和符合索引位
explain select empId from emp order by age desc, dutyId asc;

置对应
了解了mysql的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就出现了filesort

3.3.3 filesort的优化

通过创建合适的索引，能够减少filesort的出现，但是在某些情况下，条件限制不能让filesort消失，那就需要加快filesort的排序操作。对于filesort，mysql有两种排序算法。
1）两次扫描算法：mysql4.1之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序，如果sort buffer不够，则在临时表temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。
2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

mysql通过比较系统变量max_length_for_sort_data的大小和query语句取出的字段总大小，来判断适合哪种排序算法，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。

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

show variables like ‘sort_buffer_size’;
show variables like ‘max_length_for_sort_data’;

3.4 优化group by语句

由于group by实际上也同样会进行排序操作，而且与order by相比，group by主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在group by的实现过程中，与order by一样也可以利用到索引。
如果查询包含group by 但是用户想要避免排序结果的消耗，则可以执行order by null禁止排序，如下：

drop index idx_emp_age_salary on emp;
explain select age, count(*) from emp group by age;

优化后：

explain select age, count(*) from emp group by age order by null;

进一步优化后：需要给age列建立索引

create index idx_emp_age on emp(age);
explain select age, count(*) from emp group by age order by null;

3.5 优化嵌套查询

mysql4.1版本之后，开始支持sql的子查询，这个技术可以使用select语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的sql操作，同时也可以避免事务或者表锁死，并且写起来也很容易，但是，有些情况下，子查询是可以被更高效的连接（join）替代。多表连接查询替换子查询。
示例：查找有角色的所有的用户信息：

explain select * from t_user where id in (select user_id from user_role);

优化后为：

explain select * from t_user u, user_role ur where u.id=ur.user_id;

3.6 优化or条件

对于包含or的查询子句，如果要利用索引，则or之间的每个条件列都必须用到索引，而且不能使用到符合索引；如果没有索引，则应该考虑增加索引。
获得emp表中的所有索引：

show index from emp;

有时候可以使用union代替or

-- 其中id是主键，也是索引
explain select * from emp where id=1 or id=10;

优化代码：

explain select * from emp where id=1 union select * from emp where id=10;
-- 其中id和age都是索引
explain select * from emp where id=1 or age=20;

优化后代码：

explain select * from emp where id=1 union select * from emp where age=20;

我们来比较下重要指标，发现主要差别是type和ref这两项
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unipue_subquery > index_subquery > range > index > all
union语句的type值为ref，or语句的type值为range，可以看到这是一个很明显的差距。
union语句的ref值为const，or语句的值为null，const表示是常量值引用，非常快
这两项的差距就说明了union要优于or。

3.7 优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好的提高性能，一个常见又非常头疼的问题就是limit 2000000,10（limit 从第多少条数据开始，一页多少条数据），此时需要mysql排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。

explain select * from tb_item limit 2000000,10;

3.7.1 优化思路一

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

explain select * 
from tb_item t, (select id from tb_item order by id limit 2000000,10) a 
where t.id=a.id;

3.7.2 优化思路二

该方案适用于主键自增的表而且主键不能出现断层，可以把limit查询转化成某个位置的查询。

explain select * from tb_item where id>1000000 limit 10;

3.8 使用sql提示

sql提示，是优化数据库的一个重要手段，简单来说，就是在sql语句中加入一些人为的提示来达到优化操作的目的。

3.8.1 use index

在查询语句中表名的后面，添加use index来提供希望mysql去参考的索引列表，就可以让mysql不在考虑其他可用的索引。不是强制性的是建议

create index idx_seller_name on tb_seller(name);

使用前：

explain select * from tb_seller where name=’小米科技’;

优化后：

explain select * from tb_seller use index(idx_seller_name) where name=’小米科技’;

3.8.2 ignore index

如果用户只是单纯的想让mysql忽略一个或者多个索引，则可以使用ignore index 使用前：

explain select * from tb_seller where name=’小米科技’;

优化后：

explain select * from tb_seller ignore index(idx_seller_name) where name=’小米科技’;

3.8.3 force index

为强制mysql使用一个特定的索引，可在查询中使用force index

create index idx_seller_address on tb_seller(address);

使用前：

explain select * from tb_seller where address=’北京’;

优化后：

explain select * from tb_seller force index(idx_seller_address) where address=’北京’;

mysql代码角度优化

注意：正式阅读文章之前必看

1 优化sql步骤

1.1 查看SQL执行频率

1.2 定位低效率执行sql

1.3 explain分析执行计划

1.3.1 环境准备：

1.3.2 explain之id

1.3.3 explain之select_type

1.3.4 explain之table

1.3.5 explain之type

1.3.6 explain之key

1.3.7 explain之rows

1.3.8 explain之extra

1.4 show profile分析sql

1.5 trace分析优化器执行计划

2 索引的使用

2.1 验证索引提升查询效率

2.2 索引的使用

2.2.1 准备环境

2.2.2 避免索引失效

2.3查看索引使用情况

3 sql优化

3.1 大批量插入数据

3.2 优化insert语句

3.3 优化order by语句

3.3.1 环境准备

3.3.2两种排序方式

3.3.3 filesort的优化

3.4 优化group by语句

3.5 优化嵌套查询

3.6 优化or条件

3.7 优化分页查询

3.7.1 优化思路一

3.7.2 优化思路二

3.8 使用sql提示

3.8.1 use index

3.8.2 ignore index

3.8.3 force index