MySQL的优化

概念
在应用的的开发过程中，由于初期数据量小，开发人员写SQL语句时更重视功能上的实现，但是当应用统正式上线后，随着生产数据量的急剧增长，很多SQL语句开始逐渐显露出性能问题，对生产的影响也来越大，此时这些有问题的SQL语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化.
MySQL的优化方式有很多，大致我们可以从以下几点来优化MySQL：
从设计上优化
从查询上优化
从索引上优化
从存储上优化

-- SQL的优化
--查看当前会话SQL执行类型的统计信息
show session status like 'Com_______';
--查看全局（自从上次MySQL服务器启动至今）执行类型的统计信息
show global status like 'Com_______';
SHOW GLOBAL STATUS LIKE 'Com%';（查询以com打头的所有类型）
--查看针对InnoDB引擎的统计信息
show status like 'Innodb_rows_%';

查看sQL执行频率

参数	含义
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
可以通过以下两种方式定位执行效率较低的SQL语句。
慢查询日志：通过慢查询日志定位那些执行效率较低的SQL语句。
show processlist：该命令查看当前MysQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。

--开启慢日志查询
set global slow_query_log = 1;
--查看慢日志记录SQL的最低阈值时间，默认如果SQL的执行时间>=10秒，则算慢查询，则会将该操作记录到慢日志中去
show variables like '%long_query_time%';
select sleep(12);
select sleep(10);
--修改慢日志记录SQL的最低阈值时间
set global long_query_time = 5;

定位低效率执行
SQL-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列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过 copying to tmp table、sorting result、sending data等状态才可以完成
8）info列，显示这个sql语句，是判断问题语句的一个重要依据

explain分析执行计划
通过以上步骤查询到效率低的SQL语句后，可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息，
包括在SELECT语句执行过程中表如何连接和连接的顺序。
--准备测试数据
create database mydb13_optimize;
use mydb13_optimize;
执行sql脚本sql_optimize.sql添加数据

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

--Explain分析执行计划-Explain 之 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不同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;

Explain分析执行计划-Explain之select_type
表示SELECT的类型，常见的取值，如下表所示：

Explain分析执行计划-Explain 之type
type显示的是访问类型，是较为重要的一个指标，可取值为：

-- Explain分析执行计划-Explain 之 type
use mydb13_optimize;
explain select * from user;
--NULL不访问任何表，任何索引，直接返回结果
explain select now();
explain select rand();
--system查询系统表，表示直接从内存读取数据，不会从磁盘读取，但是5.7及以上版本不再显示system，直接显示ALL
explain select * from mysql.tables_priv;
-- const
explain select * from user where uid = 2;
explain select * from user where uname ='张飞';
create unique index index_uname on user(uname);
drop index index_uname on user;
create index index_uname on user(uname);--添加普通索引

Explain分析执行计划-其他指标字段
Explain 之 table
显示这一步所访问数据库中表名称有时不是真实的表名字，可能是简称，
explain 之 rows
扫描行的数量。
Explain 之 key
possible_keys：显示可能应用在这张表的索引，一个或多个。
key：实际使用的索引，如果为NuLL，则没有使用索引。
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长
度越短越好。

Explain之 extra
其他的额外的执行计划信息，在该列展示。

show profile分析SQL
Mysql从5.0.37版本开始增加了对show profiles和show profile语句的支持。show profiles能够在做sQL优化时帮助我们了解时间都耗费到哪里去了。。
通过have_profiling参数,能够看到当前MysQL是否支持profile:
select @@have_profiling;
set profiling=1; --开启profiling开关;

show profile分析sQL
通过profile，我们能够更清楚地了解sQL执行的过程。首先，我们可以执行一系列的操作
show databases;
use mydb13_optimize;
show tables;
select from user where id <2;
select count(*) from user;

SHOW PROFILES;
通过show profile for query query_id 语句可以查看到该sQL执行过程中每个线程的状态和消耗的时间：
show profile for query 8;
系统执行语句的过程，详细步骤

在获取到最消耗时间的线程状态后,MysQL支持进一步选择all、cpu、block io、context switch、page faults等明细类型
类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间：
show profile cpu for query 133;

trace分析优化器执行计划
MySQL5.6提供了对sQL的跟踪trace,通过trace文件能够进一步了解为什么优化器选择A计划，而不是选择B计划

trace分析优化器执行计划

打开trace，设置格式为JSoN，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
执行SQL语句：
select * from user where uid < 2;
最后, 检查information_schema.optimizer_trace就可以知道MysQL是如何执行sQL的：
select * from information_schema.optimizer_trace\G;(需要在dos命令下进行运行)

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

数据准备
create table 'tb seller' (
sellerid varchar (100),
'name’ varchar (100),
nickname’ varchar (50),
password varchar (60),
status' varchar (1),
address varchar (100),
createtime' datetime,
primary key('sellerid')
);
--创建组合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address) ;

避免索引失效应用-全值匹配
该情况下，索引生效，执行效率高。
explain select *from tb_seller where name='小米科技'and status='1' and address='北京市';
--全值匹配，和字段匹配成功即可，和字段无关
explain select from tb_seller where name='小米科技'and status='1'and address='北京市';
explain selectfrom tb_seller where address='北京市'and name='小米科技'and status='1';

--违法最左前缀法则，索引失效：
explain select * from tb_seller where status='1'
--如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
explain select * from tb_seller where name='小米科技' and 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 =1;(没加单引号也可以进行查询，是因为MySQL的优化器产生作用了)
使用索引优化
避免索引失效应用-其他匹配原则
--尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select *。
explain select * from tb_seller where name='小米科技'and address='北京市'；--403
--如果查询列，超出索引列，也会降低性能。

/*Extra:
using index：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index；using where：查找使用了索引，但是需要的数据都在索引列中能找到,所以不需要回表查询数据
*/
--用or分割开的条件，那么涉及的索引都不会被用到。
explain select *from tb_seller where name='黑马程序员'or createtime ='2088-01-81 12:80:08';
--以%开头的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 '%科技%';-- 不用索引
--弥补不足，不用*，使用索引列
explain select name from tb_seller where name like '%科技%';

-- 如果MySQL评估使用索引比全表更慢，则不使用索引。.
--1、如果MySQL评估使用索引比全表更慢，则不使用索引。
--这种情况是由数据本身的特点来决定的
create index index_address on tb_seller(address);
explain select from tb_seller where address='北京市'；--没有使用索引，因为大部分都是北京市，引用索引效率会更低
explain select from tb_seller where address='西安市'；--有使用索引

--2、 is NULL,is NOT NULL  有时有效，有时索引失效。
create index index_address on tb_seller(nickname);
explain select * from tb_seller where nickname is NULL; -- 索引有效，因为 null 比较少，更容易定位
explain select * from tb_seller where nickname is not NULL; -- 索引无效
--3、in走索引，not in索引失效 ，如果是主键索引的话，两个都走索引

--4、单列索引和复合索引，尽量使用复合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address) ;
等价于：
name
name+status
name+status+address    三个索引，如果一张表有多个单列索引，即使where中都使用了这些索引，则只有一个最优索引生效，这个最优的索引是由优化器进行决定的。

SQL优化
1）大批量插入数据
--1、首先，检查一个全局系统变量'local_infile’的状态，如果得到如下显示Value=OFF，则说明这是不可用的
show global variables like 'local_infile';
--2、修改local_infile值为on,开启local_infile
set global local_infile=1;
--主键有序，速度较快
load data local infile 'D:\\sql_datal\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
--主键无序，速度慢
load data local infile 'D:\\sql_data\sql2.log' into table tb_user fields terminated by ',' lines terminated by '\n';
--3、加载数据
结论：当通过load向表内加数据时，尽量保证文件中的主键有序，这样可以提高执行效率

SQL优化
大批量插入数据
2）、关闭唯一性校验
在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢
复唯一性校验，可以提高导入的效率。
--关闭唯一性校验
SET UNIQUE_CHECKS=0;
truncate table tb_user;
load data local infile 'D:\\sql_data\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
SET UNIQUE_CHECKS=1;优化insert语句
当进行数据的insert操作的时候，可以考虑采用以下几种优化方案：

--1、如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的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');

--2、在事务中进行数据插入。
begin;
insert into tb test values(1,'Tom');
insert into tb test values(2,'Cat');
insert into tb test values(3,'Jerry');
commit;

--3、数据有序插入
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');

优化order by语句
1、环境准备
CREATE TABLE emp ( A
id int(11) NOT NULL AUTO INCREMENT,
name' varchar(100) NOT NULL,
age' int(3) NOT NULL,
salarY' int (11) DEEAULT NULL,
PRIMARY KEY ('id')
);
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('41,1Jay1,136',13500');
insert into 'emp’ ('id', 'name’, 'age’, 'salary') values ('5', 'Tom2', '21', '2200');
insert into 'emp ('id , 'name , 'age, 'salary') values('6','Jerry2','31',13300');
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('11','Luci3','26','2900');
insert into 'emp' ('id', 'name',age','salary') values('12','Jay3','37','4500');

create index idx_emp_age_salary on emp (age,salary) ;

2、两种排序方式
第一种是通过对返回数据进行排序，也就是通常说的 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

3、Filesort的优化
通过创建合适的索引，能够减少Filesort的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快Filesort的排序操作。对于Filesort，MysQL有两种排序算法：
1)两次扫描算法：MySQL4.1之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer中排序，如果
sort buffer不够，则在临时表 temporary table中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/o操作。
2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。
MysaL通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小,来判定是否那种排序算法,如果
max_length_for_sort_data更大,那么使用第二种优化之后的算法;否则使用第一种。
可以适当提高sort_buffer_size 和max_length_for_sort_data 系统变量,来增大排序区的大小,提高排序的效率。

show variables like 'max_length_for_sort_data'; -- 4096
show variables like 'sort_buffer_size';

优化子查询
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。
explain select * from user where uid in (select uid from user role );

system>const>eq_ref>ref>range>index>ALL
连接（Join）查询之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

优化limit查询
一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 900000,10，此时需要MySQL排序前900010记录，仅仅返回900000-900010的记录，其他记录丢弃，查询排序的代价非常大。
1、优化思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
--优化limit
select count(*) from tb_user;
select * from tb_user limit 0,10;
explain select * from tb_user limit 900000,10; -- 0.684
explain select * from tb_user a, (select id from tb_user order by id limit 900000,10) b where a.id = b.id; -- 0.486
2、优化思路二
该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询。
explain select * from tb_user where id > 900000 limit 10;（id为自增长的）