本章是MySQL优化，算是重要部分，mysql即将完结，大家加油，演示代码较多，详细代码以及注解都在最后一部分！！！大家加油！！！熟能生巧！！！
一、查看sql执行频率
查看哪个语句更频繁，后期优化时更有针对性
二、定位低效率执行sql
慢日志只能查看已经执行完的语句，show processlist;可以查看到正在执行的语句
三、explain分析执行计划
准备数据的表之间的关联
id
表示的是查询中执行select子句或者是操作表的顺序
select_type
表示select的类型
type
表示sql对数据库的一个访问类型，很重要的一部分
all是最差的结果，就是把全表一个一个遍历出来
其他指标字段
四、show profile分析
通过时间的比较看哪一个效率更高
五、查看trace优化器
这个命令需要在命令提示符中才可以执行
六、索引优化
避免索引失效应用
全值匹配
最左前缀法则
其他匹配原则
这个表述并不准确，应该是用or分割开的条件，那么涉及的索引都不会被用到
七、sql优化
大量插入数据优化
关闭唯一性校验节省不了多少时间
优化sql语句
insert into会先连接，执行完在关闭，多条语句，会增加消耗
事务手动提交要记得开启和关闭
尽量有序添加
order by优化
高版本基本上都是一次扫描算法
子查询优化
mysql中多表查询的效率是高于子查询的
limit查询
在设计表中可以看见是否是主键自增的表
八、完整代码
-- 查看当前会话sql执行类型的统计信息
show session status like 'Com_______'; -- 七个下划线
use mydb2;
insert into p3 values('c33',2);
-- 查看全局（自从上从mysql服务器启动至今） 执行类型的统计信息
show global status like 'Com_______';
-- 查看针对innodb引擎的统计信息
show status like 'innodb_rows_%';
-- 查看慢日志配置信息
show variables like '%slow_query_log%';
-- 开启慢日志查询
set global slow_query_log = 1;
-- 查看慢日志记录sql的最低阈值时间,默认如果sql的执行时间>=10秒，则算慢查询，则会将1该操作记录到慢日志中去
show variables like '%long_query_time%';
select sleep(12);
-- 修改慢日志记录sql的最低阈值时间
set global long_query_time = 5;
-- 通过show processlist 查看当前客户端连接服务器的线程执行
show processlist;
select sleep(50);
create database mydb13_optimeize;
use mydb13_optimeize;
-- 查询执行计划
explain select * from user where uid = 1;
-- 查询执行计划
explain select * from user where uname = '张飞';
use mydb13_optimeize;
explain select * from user where uid = 1;
-- id相同表示加载表的顺序是从上到下
explain select * from user u,user_role ur,role r where u.uid = ur.uid and ur.uid = r.rid;
-- id不同，id值越大，优先级越高，越先被执行
explain select * from role where rid = (select rid from user_role where uid =(select uid from user where uname = '张飞'));
-- 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;
-- select_type
-- simple:没有子查询和union
explain select * from user;
explain select * from user u,user_role ur where u.uid = ur.uid;
-- PRIMARY:表示主查询，也就是子查询中的最外层查询
explain select * from role where rid = (select rid from user_role where uid =(select uid from user where uname = '张飞'));
-- subquery：在select和where中包含子查询
explain select * from role where rid = (select rid from user_role where uid =(select uid from user where uname = '张飞'));
-- derived；在from中包含子查询，被标记为衍生表
explain select * from (select * from user limit 2)t;
-- UNION第二个select出现在union之后
-- union result :从union表获取结果的select
explain select * from user where uid = 1 union select * from user where uid = 3;
-- type
use mydb13_optimeize;
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); -- 添加普通索引
-- eq_ref:表示左表有主键，而且坐标的每一行和右表的每一行刚好匹配
create table user2(
id int,
name varchar(20)
);
insert into user2 values(1,'张三'),(2,'李四'),(3,'王五');
create table user2_ex(
id int,
age int
);
insert into user2_ex values(1,'20'),(2,'21'),(3,'22');
-- 验证
explain select * from user2 a,user2_ex b where a.id = b.id; -- ALL ALL
-- 给user2表添加主键索引
alter table user2 add primary key(id);
explain select * from user2 a,user2_ex b where a.id = b.id; -- ALL ,eq_ref
-- 在user_ex表添加一个重复的行数据
explain select * from user2 a,user2_ex b where a.id = b.id; -- ALL ALL
-- ref :左表有普通索引，和右表配置时可能会匹配多行
-- 删除user2表的主键索引
alter table user2 drop primary key;
-- 给user表添加普通索引
create index index_id on user2(id);
explain select * from user2 a,user2_ex b where a.id = b.id;
-- range:范围查询
explain select * from user2 where id > 2;
-- index:把索引列的全部数据都扫描
explain select id from user2;-- index:扫描索引列
explain select * from user2; -- all
-- 其他字段指标
explain select * from user where uid = 1;
explain select * from user_role where uid = 1;
drop index index_uname on user;
explain select * from user order by uname;
explain select uname,count(*) from user group by uname;
explain select uid,count(*) from user group by uid;
-- 查看当前的mysql是否支持profile
select @@have_profiling;
-- 如果不支持，则需要设置打开
set profiling = 1;
-- 执行sql
show databases;
use mydb13_optimeize;
show tables;
select count(*) from user;
select * from user where uid > 2;
show profiles;
show profile for query 20;
show profile cpu for query 20;
-- trace分析优化器执行计划
use mydb13_optimeize;
set optimizer_trace = "enabled=on",end_markers_in_json= on;
set optimizer_trace_max_mem_size=1000000;
select * from user a,user_role b,role c where a.uid = b.uid and b.rid = c.rid;
select * from information_schema.optimizer_trace \G;
use mydb13_optimeize;
-- 创建名为 tb_seller 的表
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`)
);
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
-- 创建组合索引
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 address = '北京市' and name = '阿里巴巴' and status = '1' ;
-- 最左前缀法则
-- 如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，而且不跳过索引中的列
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'; -- NULL
-- 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效
explain select * from tb_seller where name = '阿里巴巴' and address = '北京市'; -- 403
-- 范围查询右边的列，不能使用索引
-- 根据前面的两个字段name，status查询是走索引的，但是最后一个条件address没有用到索引
explain select * from tb_seller where name = '阿里巴巴' and status > '1' and address = '北京市'; -- 410
-- 不要在索引列上进行运算操作，索引将失效
explain select * from tb_seller where substring(name,3,2) = '巴巴';
-- 字符串不加单引号，造成索引失效
explain select * from tb_seller where name = '阿里巴巴' and status = 1; -- 403
-- 尽量使用覆盖索引，避免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 = '北京市'; -- 效率低
-- 用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 '%黑马%';
-- 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_nick on tb_seller(nickname);
explain select * from tb_seller where nickname is NULL; -- 索引有效
explain select * from tb_seller where nickname is not NULL;
-- 索引无效
-- 3.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'); -- 使用索引
-- 4.单列索引和复合索引，尽量使用复合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
/*
等价于：name
name + status
name + status + address
*/
-- 如果一张表有多个单列索引，即使where中都使用了这些索引列，则只有一个最优索引生效
drop index idx_seller_name_sta_addr on tb_seller;
show index from tb_seller;
create index index_name on tb_seller(name);
create index index_status on tb_seller(status);
create index index_address on tb_seller(address);
-- name 重复的概率低
explain select * from tb_seller where name = '阿里巴巴' and status = '1' and address = '北京市';
explain select * from tb_seller where status = '1' and address = '北京市';
explain select * from tb_seller where status = '1' and address = '西安市';
use mydb13_optimeize;
CREATE TABLE tb_user
(
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)
);
-- 首先，检查一个全局系统变量'local_infile'的状态，如果得到如下显示Value = OFF 则说明这是不可用的
show global variables like 'local_infile';
-- 修改local_infile值为on，开启local_infile
set global local_infile = 1;
-- 加载数据
-- 结论：当通过load向表加载数据时，尽量保证文件中的主键有序，这样可以提高执行效率
-- 主键有序 -12.872s
load data local infile "D:\\BaiduNetdiskDownload\\sql\\sql1.log" into table tb_user fields terminated by ',' lines terminated by '\n';
use mydb13_optimeize;
truncate table tb_user;
-- 主键无序 - 116.664s
load data local infile "D:\\BaiduNetdiskDownload\\sql\\sql2.log" into table tb_user fields terminated by ',' lines terminated by '\n';
-- 关闭唯一性校验
set unique_checks = 0;
truncate table tb_user;
load data local infile "D:\\BaiduNetdiskDownload\\sql\\sql1.log" into table tb_user fields terminated by ',' lines terminated by '\n';
set unique_checks = 1;
-- 创建表 emp
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`)
);
-- 插入数据到 emp 表
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('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);
-- 排序：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
show variables like 'max_length_for_sort_data'; -- 4096
show variables like 'sort_buffer_size'; -- 262144
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;
-- 优化limit
select count(*) from tb_user;
select * from tb_user limit 0,10; -- 0s
select * from tb_user limit 900000,10;-- 0.374s
explain select id from tb_user order by id limit 900000,10;-- 0.202s
explain select * from tb_user a,(select id from tb_user order by id limit 900000,10) b where a.id=b.id;-- 0.184s
explain select * from tb_user where id > 900000 limit 10; -- 0.001s
