数据库学习-MySQL的优化

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

MySQL的优化方式有很多，大致我们可以从以下几点来优化MySQL：

从设计上优化
从查询上优化
从索引上优化
从存储上优化

查看SQL执行频率

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

-- 查看当前会话SQL执行类型的统计信息
show session status like 'Com_______';-- 7个下划线
-- 查看全局（自从上次MySQL服务器启动至今）SQL执行类型的统计信息
show global status like 'Com_______';
-- 查看针对InnoDB引擎的统计信息
show status like 'InnoDB_rows_%';

---

定位低效率执行SQL

可以通过以下两种方式执行效率低的SQL语句。

慢查询日志：通过慢查询日志定位那些执行效率低的SQL语句。
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语句，是判断问题语句的一个重要依据

---

 Explain分析执行计划

通过以上步骤查询到效率低的SQL语句后，可以通过explain命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中标如何连接和连接的顺序。

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

explain-id

id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。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-select_type

表示select的类型，常见的取值，如下表所示：

 explain-type

type显示的是访问类型，是较为重要的一个指标，可取值为：

结果值从最好到最坏依次是：system>const>eq_ref>ref>range>index>all

explain-table

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

explain-rows

扫描行的数量

explain-key

possible_keys：显示可能应用在这张表的索引，一个或多个
可以：实际使用的索引，如果为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 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 20;

---

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;

最后，检查information_schema.optimizer_trace就可以指定MySQL是如何执行的：

select * from information_schema.optimizer_trace \G;-- 在cmd中执行

---

使用索引优化

索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的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`)
)engine=innodb default charset=utf8mb4;

insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('alibaba','阿里巴巴','阿里小店','e10','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('baidu','百度','百度小店','e10','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('huawei','华为','华为小店','e10','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('itcast','传值','传智播客','e10','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('itheima','黑马程序员','黑马程序员','e10','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('luoji','罗技科技','罗技小店','e10','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('OPPO','OPPO科技','OPPO官方店','e10','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('alibaba','阿里巴巴','阿里小店','e10','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('ourpalm','掌趣科技','掌趣小店','e10','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('qiandu','千度科技','千度小店','e10','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('sina','新粮科技','新粮官方店','e10','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('Xiaomi','小米科技','小米官方店','e10','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) values('yijia','宜家家居','宜家官方店','e10','1','北京市','2088-01-01 12:00:00');

-- 创建组合索引
create index idn_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 address='北京市' and status='1' and name='小米科技';

最左前缀法则

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

-- 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
explain select * from tb_seller where name='小米科技';-- 403
explain select * from tb_seller where name='小米科技' and status='1';-- 410
explain select * from tb_seller where status='1' and name='小米科技';-- 410
-- 违反最左前缀法则，索引失效
explain select * from tb_seller where status='1';
-- 如果符合最左法则，但是出现跳跃某一列，只有最左索引生效
explain select * from tb_seller where name='小米科技' and address='北京市';

其他匹配原则

-- 范围查询右边的列，不能使用索引
-- 根据前面的两个字段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=1;

-- 范围查询右边的列，不能使用索引
-- 根据前面的两个字段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=1;

-- 尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），避免select *
-- 需要从原表及磁盘上读取数据
explain select * from tb_seller where name='小米科技' and address='北京市';-- 效率低
-- 从索引树中就可以查询到所有数据
explain select name from tb_seller where name='小米科技' and address='北京市';-- 效率高
explain select name,status,address from tb_seller where name='小米科技' and address='北京市';-- 效率高
explain select name,status,address,password from tb_seller where name='小米科技' and address='北京市';-- 效率低
/*
	using index：使用覆盖索引的时候就会出现
	using where：在查找使用索引的情况下，需要回表去查询所需的数据
	using index condition：查找使用了索引，但是需要回去查询数据
	using index;using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
	
	
*/

-- 用or分割开的条件，那么设计的索引都不会被用到
explain select * from tb_seller where name='黑马程序员' or createtime='2088-01-01 12:00:00';
explain select * from tb_seller where name='黑马程序员' or address='北京市';
explain select * from tb_seller where name='黑马程序员' or status='1';

-- 以%开头的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评估使用索引比全表更慢，则不使用索引
	-- 这种情况是由数据本身的特点来决定的
create index index_address on tb_seller(address);
explain select * from tb_seller where address='北京市';-- 没有使用索引
explain select * from tb_seller where address='西安市';-- 使用了索引
-- is null，is not null有时有效，有时索引失效
create index index_nickname on tb_seller(nickname);
explain select * from tb_seller where nickname is null;-- 索引有效
explain select * from tb_seller where nickname is not null;-- 索引无效
-- in走索引，not in索引失效
	-- 普通索引
explain select * from tb_seller where nickname in('阿里小店','百度小店');-- 使用索引
explain select * from tb_seller where nickname not in('阿里小店','百度小店');-- 不使用索引
	-- 主键索引
explain select * from tb_seller where sellerid in('alibaba','baidu');-- 使用索引
explain select * from tb_seller where sellerid not in('alibaba','baidu');-- 使用索引
-- 单列索引和复合索引，尽量使用复合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
/*
	等价于：
	name
	name+status
	name+status+address
*/
-- 如果一张表有多个单列索引，即使where中都使用了这些索引列，则只有一个最优索引生效
create index index_name on tb_seller(name);
create index index_status on tb_seller(status);
create index index_address on tb_seller(address);

explain select * from tb_seller where name='小米科技' and status='1' and address='西安市';

---

大批量数据加载优化

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

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

-- 首先，检查一个全局系统变量'local_infile'的状态，如果得到如下显示value=off，则说明这是不可用的
show global variables like 'local_infile';
-- 修改local_infile的值为on，开启local_infile
set global local_infile=1;
-- 加载数据
	-- 当通过load向表加载数据时，尽量保证文件中的主键有序，这样可以提高执行效率
-- sql1.log主键有序 sql2.log主键无序
-- 主键有序-22.617s
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
truncate table tb_user;
-- 主键无序-81.739s
load data local infile 'D:\\sql_data\\sql2.log' into table tb_user fields terminated by ',' lines terminated by '\n';

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

---

优化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')

-- 在事务中进行数据插入。
begin;
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');

---

优化order by语句

数据准备

create table `emp`(
	`id` int(11) not null auto_increment,
	`name` varchar(100) not null,
	`age` int(3) not null,
	`salary` int(11) default 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('4','Jay','36','2500');
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('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);

两种排序方式

第一种是通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序都叫filesort排序。
第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

-- 排序，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

filesort的优化

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

两次扫描算法：MySQL4.1之前，使用该方法排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序，如果sort buffer不够，则在临时表temporary table中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。
一次扫描算法：一次性取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL通过比较系统变量max_length_for_sort_data的大小和query语句取出的字段总大小，来判定是否哪种排序算法，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。
可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序的效率。

---

优化子查询

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的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;

system>const>eq_ref>ref>range>index>all

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

---

优化limit查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是limit 900000,10，此时需要MySQL排序前900010记录，仅仅返回900000-900010的记录，其他记录丢弃，查询排序的代价非常大。

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

优化思路二
该方案适用于主键自增的表，可以把limit查询转换成某个位置的查询。