【mysql】六、mysql的学习---SQL优化
mysql的学习
资料来源 https://www.bilibili.com/video/BV1CZ4y1M7MQ?from=search&seid=3518646188262100291
三、存储过程和函数：【mysql】三、mysql的学习---存储过程和函数
四、触发器：【mysql】四、mysql的学习---触发器
五、存储引擎：【mysql】五、mysql的学习---存储引擎
六、SQL优化：【mysql】六、mysql的学习---SQL优化
七、应用优化：【mysql】七、mysql的学习---应用优化
八、查询缓存：【mysql】八、mysql的学习---查询缓存
九、内存优化：【mysql】九、mysql的学习---内存优化
十、Mysql并发参数调整和锁： 【mysql】十、mysql的学习---Mysql并发参数调整和锁
十一、常用的SQL技巧：【mysql】十一、mysql的学习---常用的sql技巧
本篇文章主要介绍 SQL优化 的相关知识
1. 查看sql执行效率
-- mysql客户端连接成功后，通过show [session|global] status 命令可以提供服务器状态信息。 -- show [session|global] status 可以根据需要加上参数session或者global来显示session级(当前连接)的计算结果和global级(自数据库上次启动至今)的统计结果。如果不写，默认使用参数是session。 -- Com_xxx表示每个xxx语句执行的次数，我们通常关心的是以下几个统计参数： -- 1. Com_select：执行select操作的次数，一次查询只累加1 -- 2. Com_insert：执行insert操作的次数，对于批量插入的insert操作，只累加一次 -- 3. Com_update：执行update操作的次数 -- 4. Com_delete：执行delete操作的次数 -- 5. Innodb_rows_read：select查询返回的行数 -- 查看SQL执行频率 默认为session (注意: _ 符号数量是7个) show status like 'Com_______' -- 查看SQL执行频率 全局的global show global status like 'Com_______'
-- 查看SQL执行频率查 Innodb的情况 show global status like 'Innodb_rows_%'
2 定位低效率执行sql
--可以通过以下两种方式定位执行效率较低的sql语句： --(1)慢查询日志：通过慢查询日志定位那些执行效率较低的sql语句，用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的sql语句的日志文件。 --(2)show processlist：慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前mysql在进行的线程，包括线程的状态、是否锁表等，可以实时地查看sql的执行情况，同时对一些锁表操作进行优化。
-- 查看当前MySQL在进行的线程 show processlist
--查询连接ID select connection_id()
-- 这里我使用存储过程向user表中批量添加数据，可以看到执行show processlist;语句可以看出当前执行慢的具体sql。
-- 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语句，是判断问题语句的一个重要依据
3. explain分析执行计划
3.1 环境准备
-- 通过以上步骤查询到效率低的sql语句后，可以通过explain或者desc命令获取mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
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) not null, role_id varchar(32) not null, primary key(id), key fk_user_id(user_id), key fk_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','6df93e89ee7c11ea8be9f875a471a574','超级管理员'); insert into t_user(id,username,password,name) values(2,'admin','6df93e89ee7c11ea8be9f875a471a574','系统管理员'); insert into t_user(id,username,password,name) values(3,'itcast','a3d8793cee7c11ea8be9f875a471a574','test02'); insert into t_user(id,username,password,name) values(4,'stu1','b8c1a8ffee7c11ea8be9f875a471a574','学生1'); insert into t_user(id,username,password,name) values(5,'stu2','c0cb5c46ee7c11ea8be9f875a471a574','学生2'); insert into t_user(id,username,password,name) values(6,'t1','c86cbf6cee7c11ea8be9f875a471a574','老师1'); 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(1,1,5); insert into user_role(id,user_id,role_id) values(2,1,7); insert into user_role(id,user_id,role_id) values(3,2,8); insert into user_role(id,user_id,role_id) values(4,3,9); insert into user_role(id,user_id,role_id) values(5,4,8); insert into user_role(id,user_id,role_id) values(6,5,10);
| 含义 | |
|---|---|
| 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 |
3.2 explain之id
-- id字段是select查询的序列号，是一组数字，表示的是查询中执行的select子句或者是操作表的顺序。id情况分三种： -- -- 1. id相同表示加载表的顺序是从上到下。 -- 2. id不同id值越大，优先级越高，越先被执行。 -- 3. id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
-- 1. 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. explain select * from t_role where id = (select role_id from user_role where user_id = (select id from t_user where username = 'stu1'))
-- 3. explain select * from t_role r,(select * from user_role ur where ur.user_id ='2' union all select * from user_role ur where ur.user_id ='1') a where r.id = a.role_id
3.3 explain之select_type
表示select的类型，常见的取值如下：
| 含义 | |
|---|---|
| simple | 简单的select查询，查询中不包含子查询或者union |
| primary | 查询中若包含任何复杂的子查询，最外层查询标记为该标识 |
| subquery | 在select或where列表中包含了子查询 |
| derived | 在from列表中包含的子查询，被标记为derived(衍生)mysql会递归执行这些子查询，把结果放在临时表中 |
| union | 若第二个select出现在union后，则标记为union；若union包含在from子句的子查询中，外层select将被标记为derived |
| union result |
-- 执行计划为simple explain SELECT * FROM `t_user`;
-- 执行计划为primary、subquery explain SELECT * FROM `t_user` where id = (select id from user_role where role_id = '9')
-- 执行计划为derived explain SELECT a.* FROM (select * from t_user where id in ('1','2')) a
-- 执行计划为union、union result explain select * from t_user where id = '1' union select * from t_user where id = '2'
3.4 explain之table
-- 展示这一行数据是关于哪一张表的
3.5 explain之type
-- type显示的是访问类型，是较为重要的一个指标，可取值为： -- 结果值从最好到最坏依次是： -- null > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range > index > all -- 一般来说，我们需要保证查询至少达到range级别，最好达到ref。
| 含义 | |
|---|---|
| null | mysql不访问任何表，索引，直接返回结果 |
| system | 表只有一行记录(等于系统表)或者为空表，这是const类型的特例，一般不会出现 |
| const | 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，mysql就能将该查询转换为一个常量。const用于将"主键"或"唯一"索引的所有部分与常量值进行比较。 |
| eq_ref | 类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描。 |
| ref | 非唯一性索引扫描，返回匹配某个单独值得所有行。本质上也是一种索引访问，返回所有匹配某个单独值得所有行(多个) |
| range | 只检索给定返回得行，使用一个索引来选择行。where之后出现between，<，>，in等操作 |
| index | index与all得区别为 index类型只是遍历了索引树，通常比all快，all是遍历数据文件 |
| all |
-- null explain select now()
-- system explain select a.* from (select * from t_user where id = '1') a
-- const explain select * from t_user where id = '1'
-- eq_ref explain select * from t_user u,t_role r where u.id = r.id
-- ref -- 首先创建一个普通的索引 create index idx_user_name on t_user(name) -- 查看表的所有索引 show index from t_user -- ref例子 explain select * from t_user where name = 'a'
-- range explain select * from t_user where name in ('学生1','学生2')
-- index explain select id from t_user
-- all explain select * from t_user
3.6 explain之key
-- possible_keys：显示可能应用在这张表的索引，一个或多个。 -- key：实际使用的索引，如果为null，则没有使用索引 -- key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
3.7 explain之rows
-- 扫描行的数量
3.8 explain之extra
-- 其它额外的执行计划信息，在该列展示 -- 如果出现了前两种情况，则说明需要优化，最后一种使用了索引
| 含义 | |
|---|---|
| using filesort | 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为"文件排序"，效率低 |
| using temporary | 使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于order by和group by，效率低 |
| using index |
-- using filesort explain select * from t_user order by password
-- using index explain select name from t_user order by name -- 注意：下面的语句会导致结果仍然是using filesort explain select * from t_user order by name
-- using temporary explain select * from t_user group by password
4. show profile分析sql
-- mysql从5.0.37版本开始增加了对show profiles和show profile语句的支持。show profiles能够在做sql优化时帮助我们了解时间都耗费到哪里去了。 -- 通过have_profiling参数，能够看到当前mysql是否支持profile。
-- 查看当前mysql是否支持profile select @@have_profiling;
-- 查看是否开启了profile 1为开启 0为未开启 select @@profiling;
-- 当前会话开启profile set profiling = 1 -- 查看执行的sql耗费时间 show profiles
-- 查看某个sql具体耗费时间在什么地方 status：经历了几个阶段 duration：每个阶段的耗时 show profile for query + Query_ID eg: show profile for query 279
-- 从图可以看出时间基本上都耗费在了sending data上了 -- sending data状态表示mysql线程开始访问数据行并把结果返回给客户端，而不仅仅是返回给客户端，由于在sending data窗台下，mysql下线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态 -- 在获取到最消耗时间的线程状态后，mysql支持进一步选择all、cpu、block io、context switch、page faults等明细类型类查看mysql在使用什么资源上耗费了过高的时间，例如
-- 查看cpu的耗费时间 show profile cpu for query 279
5. trace分析优化器执行计划
-- mysql5.6提供了对sql的根据trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是选择B计划。 -- 这里说一下优化器，就是mysql会将我们的sql通过优化器先进行优化才会执行。 -- 打开trace，设置格式为json，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
set optimizer_trace="enabled=on",end_makers_in_json=on; set optimizer_trace_max_mem_size=1000000; -- 执行sql语句： select * from user where id < 4 -- 最后,检查information_schema.optimizer_trace就可以知道mysql是如何执行sql的 select * from information_schema.optimizer_trace
6. 大批量插入数据
6.1 使用load
-- 当使用load命令导入数据的时候，适当的设置可以提高导入的效率 -- 准备： -- 1. 两个.log文件，里面各自存储100w条数据，第一个的主键是有序的，即从1--100w，第二个是无序的。 -- 字段之间用逗号隔开，每条数据之间用换行符隔开。 -- 2. 两个一模一样的表，表结构相同。
-- by ',' ： 意思是字段之间用逗号隔开 -- by '\n'： 意思是每条数据之间用换行符隔开 load data local infile '/root/sql1.log' into table `tb_user_1` fields terminated by ',' lines terminated by '\n'; -- 20s左右 load data local infile '/root/sql2.log' into table `tb_user_2` fields terminated by ',' lines terminated by '\n'; -- 1分20s左右
6.2 关闭唯一性校验
-- 在导入数据前执行set unique_checks = 0 ，关闭唯一性校验，在导入结束后执行 set unique_checks = 1，恢复唯一性校验，可以提高导入的效率。
6.3 手动提交事务
-- 如果应用使用自动提交的方式，建议在导入前执行set autocommit = 0 ，关闭自动提交，导入结束后再执行set autocommit = 1，打开自动提交，也可以提高导入的效率。
7 优化insert语句
-- 当进行数据的insert操作的时候，可以采取以下几种优化方案：
7.1 多条insert合并为一条
-- 优化前 insert into test values (1,'a'); insert into test values (2,'b'); insert into test values (3,'c'); -- 优化后 insert into test values (1,'a'),(2,'b'),(3,'c');
7.2 开启手动提交事务
start transaction; insert into test values (1,'a'); insert into test values (2,'b'); insert into test values (3,'c'); commit;
7.3 数据有序插入
-- 优化前 insert into test values (5,'a'); insert into test values (2,'b'); insert into test values (3,'c'); -- 优化后 insert into test values (1,'a'); insert into test values (2,'b'); insert into test values (3,'c');
8 优化order by语句
8.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 NULL, 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 ('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);
8.2 两种排序方式
-- 了解了mysql的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。where条件和Order by 使用相同的索引，并且Order by的顺序和索引顺序相同，并且order by的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort。 -- 1. 通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序都叫FileSort排序 -- 2. 通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高 -- Using filesort explain select * from emp order by age -- Using filesort explain select * from emp order by salary -- Using filesort explain select * from emp order by age desc -- Using filesort explain select * from emp order by age,salary -- Using filesort explain select * from emp order by age,salary desc -- Using index explain select age from emp order by age -- Using index; Using filesort explain select age from emp order by salary -- Using index explain select age from emp order by age,salary -- Using index explain select salary from emp order by age,salary -- Using index; Using filesort explain select salary from emp order by age,salary desc -- Using index explain select age,salary from emp order by age,salary -- Using index; Using filesort explain select age,salary from emp order by age,salary desc -- Using filesort explain select age,salary,name from emp order by age,salary -- Using filesort explain select age,salary,name from emp order by age,salary desc
8.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更大，那么使用第二种优化后的算法；否则使用第一种。 -- 可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序的效率 show variables like 'max_length_for_sort_data'; show variables like 'sort_buffer_size';
9 优化group by语句
-- 由于group by实际上也同样会进行排序操作，而且与order by相比，group by主要只是多了排序之后得分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在group by的实现过程中，与order by一样也可以利用到索引。
-- 例：比如age没有索引 -- extra选项：Using temporary + Using filesort select age,count(*) from emp group by age; -- 即只想分组,不想排序 extra选项: Using temporary select age,count(*) from emp group by age order by null; -- 将age字段加上索引 -- 下面的语句结果为 extra: Using index select age,count(*) from emp group by age order by null;
10. 优化嵌套查询
-- mysql4.1版本之后开始支持sql的子查询。这个从技术上可以使用select语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的sql操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接join替代。 -- 优化前 select * from t_user where id in (select user_id from user_role); -- 优化后 select * from t_user u,user_role ur where u.id = ur.user_id;
11. 优化or条件
-- 对于包含or的查询子句，如果要利用索引，则or之间的每个条件都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。建议使用union来代替or -- id主键索引,name无索引 [都不走索引] select * from emp where id = 1 or name = 'Tom'; -- 有一个age和salary的复合索引 age在前 salary在后 [结果仍然是都不走索引] select * from emp where age = 20 or salary = 3500; -- 有一个age和salary的复合索引 age在前 salary在后 [结果是走索引 type = index_merge] select * from emp where id = 1 or age = 20; -- 对上一行例子的优化 select * from emp where id = 1 union select * from emp where age = 20; -- id为主键索引 结果会走索引但是效率不会太高 即type = range select * from emp where id = 1 or id = 2; -- id为主键索引 结果会走索引并且效率也会高 即type = const select * from emp where id = 1 union select * from emp where id = 2
12. 优化分页查询
-- 一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是limit 2000000,10，此时需要mysql排序前2000010记录，仅仅返回2000000 - 2000010 的记录，其它记录丢弃，查询排序的代价非常大。
12.1 优化思路一
-- 在索引上完成排序分页操作，最后根据主键关联回原表所需要的其它列内容。 select * from tb_item t,(select id from tb_item order by id limit 2000000,10) a where t.id = a.id;
12.2 优化思路二
-- 该方案适用于主键自增的表，可以把limit查询转换成某个位置的查询。 select * from tb_item where id > 1000000 limit 10;
13. 使用sql提示
-- sql提示，是优化数据库的一个重要手段，简单来说，就是在sql语句中加如一些人为的提示来达到优化操作的目的。
13.1 use index
-- 在查询语句中表名的后面，添加use index来提供希望mysql去参考的索引列表，就可以让mysql不再考虑其它可用的索引。 -- 创建索引 create index idx_seller_name on tb_seller(name) -- 优化前 select * from tb_seller where name = '小米科技' -- 优化后 select * from tb_seller use index(idx_seller_name) where name = '小米科技'
13.2 ignore index
-- 如果用户只是单纯的想让mysql忽略一个或者多个索引，则可以使用ignore index作为hint。 select * from tb_seller ignore index(idx_seller_name) where name = '小米科技'
13.3 force index
-- 为强制mysql使用一个特定的索引，可在查询中使用force index作为int -- 创建索引 create index idx_seller_address on tb_seller(address); -- select * from tb_seller force index(idx_seller_address) where address = '北京市'
持续更新！！！
