Mysql之快速上手sql优化

最新推荐文章于 2022-05-13 08:00:00 发布

BestandW1shEs

最新推荐文章于 2022-05-13 08:00:00 发布

阅读量683

点赞数

分类专栏： mysql 文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/bestandw1shes_lsy/article/details/122299032

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

一. 优化SQL步骤

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

本文将详细介绍，当面对一个有 SQL 性能问题的数据库时，应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题。

1. 查看SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息，show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级(当前连接)的计结果和 global 级(自数据库上次启动至今)的统计结果，如果不写，默认使用参数是“session”，通常该命令可以知道数据库中哪一类的 sql 语句执行的最多，可以为优化提供借鉴性指标。

(1) 显示当前 session 中所有统计参数的值，show status like 'Com_______'：

(2) 显示全局中所有统计参数的值，show global status like 'Com_______'：

(3) 针对Innodb引擎的，show status like 'Innodb_rows_%'：

(4) 常用统计参数：

参数	含义
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	慢查询的次数

1) Com_***：这些参数对于所有存储引擎的表操作都会进行累计；

2) Innodb_***：这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

2. 定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句：

(1) 慢查询日志：通过慢查询日志定位那些执行效率较低的 SQL 语句，用 --log-slow-queries[=file_name] 选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件，在程序执行完后将所有超过设置时间的 sql 记录在日志中，我们就会知道在哪个时间点，哪条 sql 执行的效率比较慢，它的执行时间是多少，就可以定位到效率比较低的 sql 语句；

(2) show processlist：慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

1) 通过 show processlist 查看：

2) 参数分析：

参数	含义
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语句，是判断问题语句的一个重要依据

3) 测试：

当 sql 正在执行的时候，通过 show processlist 命令可以查询到该条 sql 的执行情况。

3. explain分析执行计划

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

查询SQL语句的执行计划：

1) explain select * from tb_item where id = 1：

2) explain select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白联通3G手机3'：

3) 参数分析：

字段	含义
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) 环境准备：

创建数据库表和数据的sql语句：

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,
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','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','
超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','
系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui',
'test02');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学
生1');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学
生2');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师
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(NULL, '1', '5'),(NULL, '1', '7'),
(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

(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;

先加载 t_role表，再加载 user_role 表，最后加载 t_user表。

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'));

先加载 t_user 表，再加载 user_role 表，最后加载 t_role表。

3) id 有相同，也有不同，同时存在时id相同的可以认为是一组，从上往下顺序执行，在所有的组中，id值越大，优先级越高，越先执行：

explain select * from t_role r , (select * from user_role ur where ur.`user_id` = '2') a where r.id = a.role_id;

(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) explain select * from t_user;

该命令就是一个简单的select查询，查询中不包含子查询或者UNION，所以是SIMPLE。

2) explain select * from t_user where id = (select id from user_role where role_id = '9');

该命令包含了子查询，所以不是简单的select查询，由于是where id = (子查询)类型，所以该子查询语句被标记为SUBQUERY，除子查询的最外层查询被表是为PRIMARY。

3) explain select a.* from (select * from t_user where id in ('1','2'))a;

该命令包含了子查询，由于是from(子查询)类型，所以该子查询语句被标记为DERIVED，执行子查询后把结果放在临时表(<derived2>，2代表的是由id为2衍生出来的表)中，除子查询的最外层查询被表是为PRIMARY。

4) explain select * from t_user where id = '1' union select * from t_user where id = '2';

该命令包含了union，所以union后面的语句被表示为UNION，union前面的语句是外层的查询，被标记为PRIMARY，union前后的语句的并集被标记为UNION RESULT。

(4) explain 之 table：展示这一行的数据是关于哪一张表的。

(5) explain 之 type：显示的是访问类型，是较为重要的一个指标，通过type指标大概就能知道当前 sql 的执行效率的情况，可取值为：

type	含义
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	将遍历全表以找到匹配的行

举例说明：

1) explain select now();

该语句中now() 为当前时间，不查询任何表，所以 type 为 null。

2) explain select * from (select * from t_user where id = '1') a;

该语句模拟了表中只有一条数据的情况，所以 type 为 system。

3) explain select * from t_user where id = '1';

explain select * from t_user where username = 'stu1';

该语句中id是主键，username是唯一索引，并且查询结果只返回一条数据，所以两条语句的 type 为const；

4) explain select * from t_user u,t_role r where u.id=r.id;

该语句多表关联查询时，并且查询结果只返回一条数据，所以 type 为eq_ref；

5) explain select * from t_user where name = 'a';

由于表中没有非唯一索引，所以首先需要创建一个非唯一索引：create index idx_user_name on t_user(name);

6) explain select id from t_user;

该语句查询的是id，id是主键，主键是有索引的，相当于扫描了整张表的索引，所以 type 为index；

7) explain select * from t_user;

该语句查询所有数据，需要进行整表扫描，会到数据文件中进行全部扫描，进行整行匹配，所以 type 为all；

结果值从最好到最坏以此是：

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL；

system > const > eq_ref > ref > range > index > ALL；

一般来说，我们需要保证查询至少达到 range 级别，最好达到ref 。

(6) explain 之 key：

1) possible_keys：显示可能应用在这张表的索引，一个或多个；

2) key：实际使用的索引，如果为NULL，则没有使用索引(如果索引已经建了，但是没有走索引，就需要分析一些原因了)；

3) key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

(7) explain 之 rows：扫描行的数量。

举例说明：

1) explain select * from t_user where name = 'a';

因为name建立了索引，只扫描了索引为a的数据，所以 rows = 1。

2) explain select * from t_user where password = '1111';

因为password没有建立了索引，所以会扫描全表，所以 rows = 6。

(8) explain 之 extra：其他的额外的执行计划信息，在该列展示，可取值为：

extra	含义
using filesort	说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为“文件排序”, 效率低
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于 order by 和 group by，效率低
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错

如果出现了using filesort 和 using temporary 就需要考虑进行优化了。

举例说明：

1) explain select * from t_user order by id;

该语句用到的是id排序，id为主键，所以Extra为null。

2) explain select * from t_user order by password;

该语句用的是password排序，password既不是索引，也不是主键，所有就会按照文件排序，需要扫描文件当中的内容再进行排序，所以Extra为using filesort，此时的效率是非常低的。

3) explain select name from t_user order by name;

该语句用的是name排序，由于name建立了索引，所以Extra为using index。

4) explain select * from t_user group by password;

该语句用的是password分组，password既不是索引，也不是主键，所以Extra为using temporary，此时的效率是非常低的。

4. show profile分析SQL

Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持，show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪去了。

(1) 通过 have_profiling 参数，能够看到当前MySQL是否支持profile：

(2) 通过profiling 参数，能够看到当前MySQL是否开启profile(0关闭，1开启)：

(3) 通过set语句在Session级别开启profiling：

(4) show profiles;

开启后，就可以通过该命令查看刚刚所有sql执行的耗时时间。

(5) 通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端，由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。

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

参数分析：

字段	含义
Status	sql 语句执行的状态
Duration	sql 执行过程中每一个步骤的耗时
CPU_user	当前用户占有的cpu
CPU_system	系统占有的cpu

5. trace分析优化器执行计划

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

(1) 打开 trace ，设置格式为 JSON，并设置 trace 最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示：

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

(2) 执行SQL语句：select * from tb_item where id < 4;

(3) 最后，检查 information_schema.optimizer_trace 就可以知道MySQL是如何执行SQL的：select * from information_schema.optimizer_trace\G;

** ** ** ** ** ** ** ** ** ** ** ** ** * 1. row ** ** ** ** ** ** ** ** ** ** ** ** ** *
QUERY: select * from tb_item where id < 4
TRACE: {
	"steps": [{
			"join_preparation": {
				"select#": 1,
				"steps": [{
					"expanded_query": "/* select#1 */ select `tb_item`.`id` AS `id`,
					`tb_item`.`title` AS `title`,`tb_item`.`price`AS `price`,`tb_item`.`num`AS `num`,`tb_item`.`categoryid`AS `categoryid`,`
					tb_item`.`status`AS `status`,`tb_item`.`sellerid`AS `sellerid`,`tb_item`.`createtime`AS `createtime`,`tb_item`.`updatetime`AS `updatetime`
					from `tb_item` where(`tb_item`.`id` < 4)"
				}] /* steps */
			} /* join_preparation */
		},
		{
			"join_optimization": {
				"select#": 1,
				"steps": [{
						"condition_processing": {
							"condition": "WHERE",
							"original_condition": "(`tb_item`.`id` < 4)",
							"steps": [{
									"transformation": "equality_propagation",
									"resulting_condition": "(`tb_item`.`id` < 4)"
								},
								{
									"transformation": "constant_propagation",
									"resulting_condition": "(`tb_item`.`id` < 4)"
								},
								{
									"transformation": "trivial_condition_removal",
									"resulting_condition": "(`tb_item`.`id` < 4)"
								}
							] /* steps */
						} /* condition_processing */
					},
					{
						"table_dependencies": [{
							"table": "`tb_item`",
							"row_may_be_null": false,
							"map_bit": 0,
							"depends_on_map_bits": [] /* depends_on_map_bits */
						}] /* table_dependencies */
					},
					{
						"ref_optimizer_key_uses": [] /* ref_optimizer_key_uses */
					},
					{
						"rows_estimation": [{
							"table": "`tb_item`",
							"range_analysis": {
								"table_scan": {
									"rows": 9816098,
									"cost": 2.04e6
								} /* table_scan */ ,
								"potential_range_indices": [{
									"index": "PRIMARY",
									"usable": true,
									"key_parts": [
										"id"
									] /* key_parts */
								}] /* potential_range_indices */ ,
								"setup_range_conditions": [] /* setup_range_conditions */ ,
								"group_index_range": {
									"chosen": false,
									"cause": "not_group_by_or_distinct"
								} /* group_index_range */ ,
								"analyzing_range_alternatives": {
									"range_scan_alternatives": [{
										"index": "PRIMARY",
										"ranges": [
											"id < 4"
										] /* ranges */ ,
										"index_dives_for_eq_ranges": true,
										"rowid_ordered": true,
										"using_mrr": false,
										"index_only": false,
										"rows": 3,
										"cost": 1.6154,
										"chosen": true
									}] /* range_scan_alternatives */ ,
									"analyzing_roworder_intersect": {
										"usable": false,
										"cause": "too_few_roworder_scans"
									} /* analyzing_roworder_intersect */
								} /* analyzing_range_alternatives */ ,
								"chosen_range_access_summary": {
									"range_access_plan": {
										"type": "range_scan",
										"index": "PRIMARY",
										"rows": 3,
										"ranges": [
											"id < 4"
										] /* ranges */
									} /* range_access_plan */ ,
									"rows_for_plan": 3,
									"cost_for_plan": 1.6154,
									"chosen": true
								} /* chosen_range_access_summary */
							} /* range_analysis */
						}] /* rows_estimation */
					},
					{
						"considered_execution_plans": [{
							"plan_prefix": [] /* plan_prefix */ ,
							"table": "`tb_item`",
							"best_access_path": {
								"considered_access_paths": [{
									"access_type": "range",
									"rows": 3,
									"cost": 2.2154,
									"chosen": true
								}] /* considered_access_paths */
							} /* best_access_path */ ,
							"cost_for_plan": 2.2154,
							"rows_for_plan": 3,
							"chosen": true
						}] /* considered_execution_plans */
					},
					{
						"attaching_conditions_to_tables": {
							"original_condition": "(`tb_item`.`id` < 4)",
							"attached_conditions_computation": [] /* attached_conditions_computation */ ,
							"attached_conditions_summary": [{
								"table": "`tb_item`",
								"attached": "(`tb_item`.`id` < 4)"
							}] /* attached_conditions_summary */
						} /* attaching_conditions_to_tables */
					},
					{
						"refine_plan": [{
							"table": "`tb_item`",
							"access_type": "range"
						}] /* refine_plan */
					}
				] /* steps */
			} /* join_optimization */
		},
		{
			"join_execution": {
				"select#": 1,
				"steps": [] /* steps */
			} /* join_execution */
		}
	] /* steps */
}

二. 索引的使用

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

1. 验证索引提升查询效率

准备的一张表，表中一共存储了 300 万记录，本文准备的是tb_item表；

如果没有这么多数据，随便找一张表，执行 insert into 表名 select * from 表名，通过蠕虫复制，按2^n指数增长很快，多执行几次就行了。

(1) 根据ID查询：select * from tb_item where id = 1999\G;

查询速度很快，接近0s ，主要的原因是因为id为主键，有索引；

(2) 根据 title 进行精确查询：select * from tb_item where title = 'iphoneX 移动3G 32G941'\G;

查看SQL语句的执行计划：处理方案，针对title字段，创建索引：

索引创建完成之后，再次进行查询：通过explain ，查看执行计划，执行SQL时使用了刚才创建的索引：

(3) 创建了索引并不一定就能提高查询效率，还需要合理的使用才行，避免索引的失效。

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` datetime,
primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;



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('jingdong','京东','京东','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('tenxun','腾讯','腾讯','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');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰
店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小
店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('qiandu','千度科技','千度小
店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰
店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰
店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰
店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');




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

(2) 避免索引失效的几个注意点：

1) 全值匹配，对索引中所有列都指定具体值：

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

key为刚刚创建的复合索引，并且ref为const，表示按常量查询，查询的rows为1，这样的查询效率就比较高。

2) 最左前缀法则(复合索引遵循的法则)：

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

a. 满足最左前缀法则，走索引：

按着索引从最左列开始到最右列的顺序，并且没有跳过索引中的列，所以走索引，该顺序不是 sql 中 where 后面的顺序。

name 作为索引，索引长度403；

name + status 作为索引，索引长度410；

name + status + address 作为索引，索引长度813；

b. 违法最左前缀法则，索引失效：

跳过了最左列的索引name，不包含最左列索引，导致索引失效

c. 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

跳过了复合索引中的 status 列，所有只走了 name列的索引，从索引的长度403也可以看出只走了一个列的索引。

3) 范围查询右边的列，索引失效：

根据前面的两个字段name ， status 查询是走索引的，但是最后一个条件 address 没有用到索引，因为 status 是范围查询，它后面的索引会失效。

4) 不要在索引列上进行运算操作，索引将失效：

因为对name索引做了字符串截取操作，所以索引失效。

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

由于，在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，这样底层就对该字段进行了运算操作，造成索引失效。

6) 尽量使用覆盖索引，避免select *：

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

a. 需避免的情况：

该语句用到了索引，从索引中拿到具体的数据，然后再到表结构中去拿到该数据对应的一整行数据，因为索引中没有记录一整行数据，只是记录了name的数据。

b. 参数分析：

字段	含义
using index	使用覆盖索引的时候就会出现
using where	在查找使用索引的情况下，需要回表去查询所需的数据
using index condition	查找使用了索引，但是需要回表查询数据
using index ; using where	查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

c. 推荐使用的情况：

只要索引中包含了查询的列，就不需要回表查询。

d. 如果查询列，超出索引列，也会降低性能：

7) 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到：

name字段是索引列，而nickname不是索引列，中间是or进行连接是不走索引的。

如果换为and，是走了name字段的索引的。

8) 以%开头的Like模糊查询，索引失效：

如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效：

解决方案：通过覆盖索引来解决：

如果超出了索引的范围，索引也会失效：

pasword不在复合索引内，所以覆盖索引解决%的问题也会失效。

9) 如果MySQL评估使用索引比全表更慢，则不使用索引：

因为所有数据中，只有一条是西安市，其他都是北京市，北京市占的比例特别大了，几乎覆盖了所有列，所有当查询北京市的时候，它就不会再走索引，而是走全表扫描，这样速度更快，这是 mysql 底层对它做的优化。

10) is NULL ， is NOT NULL 有时索引失效：

其实该原来同上面第九点，如果null的数据占大部分，那么null不走索引，is not null走索引，如果is not null占大部分，那么null走索引，is not null不走索引。

11) in 走索引， not in 索引失效：

12) 单列索引和复合索引：

尽量使用复合索引，而少使用单列索引。

a. 创建复合索引

create index idx_name_sta_address on tb_seller(name, status, address);
就相当于创建了三个索引 ：
name
name + status
name + status + address

b. 创建单列索引

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);

数据库会选择一个最优的索引(辨识度最高索引)来使用，并不会使用全部索引

3. 查看索引使用情况

1. 查看当前会话：show status like 'Handler_read%';

2. 查看全局会话：show global status like 'Handler_read%';

3. 参数分析：

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

三. SQL优化

1. 大批量插入数据

(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 ;

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

(3) 对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：

a. 主键顺序插入：

由于导入的时候，存储引擎选择的是 InnoDB，InnoDB 底层的索引用的是B+树的索引，而B+树的索引是有序的，所以导入的时候，建议主键有序，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率，如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

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

sql2.log ----> 主键无序：

插入ID顺序排列数据：

插入ID无序排列数据：

b. 关闭唯一性校验：

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

c. 手动提交事务：

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

2. 优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案：

(1) 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个insert语句快：

1) 原始方式为：

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');

2) 优化后的方案为：

insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

(2) 在事务中进行数据插入：

start transaction;
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');

按照主键的顺序插入。

3. 优化order by语句

(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);

(2) 两种排序方式：

1) 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序：

2) 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高：

(3) 多字段排序：

了解了MySQL的排序方式，优化目标就清晰了：

1) 尽量减少额外的排序，通过索引直接返回有序数据；

2) where 条件和 Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同，并且Order by 的字段都是升序，或者都是降序。

否则肯定需要额外的操作，这样就会出现FileSort。

(4) 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 系统变量，来增大排序区的大小，提高排序的效率。

4. 优化group by 语句

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作，当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算，所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

(1) 如果查询包含 group by 但是想要避免排序结果的消耗，则可以执行order by null 禁止排序，如下：

1) 未禁用：explain select age,count(*) from emp group by age;

2) 禁用：explain select age,count(*) from emp group by age order by null;

从上面的例子可以看出，第一个SQL语句需要进行"filesort"，而第二个SQL由于order by null 不需要进行"filesort"，而Filesort往往非常耗费时间。

(2) 创建索引：create index idx_emp_age_salary on emp(age,salary)；

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;

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

6. 优化OR条件

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引，如果没有索引，则应该考虑增加索引。

获取 emp 表中的所有的索引：

举例：explain select * from emp where id = 1 or age = 30;解决方案，建议使用 union 替换 or ：

比较下重要指标，发现主要差别是 type 和 ref 这两项，type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

UNION 语句的 type 值为 const，OR 语句的 type 值为 range，可以看到这是一个很明显的差距；

UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快；

这两项的差距就说明了 UNION 要优于 OR 。

7. 优化分页查询

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

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

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

该方法有一个前提，就是自增的主键不能出现断层，必须是1.2.3.4.5.6.7........N

8. 使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的(告诉数据库使用哪些索引，不使用哪些索引)。

(1) USE INDEX：在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引(这个并不是强制性的，而且建议数据库使用该索引)。

create index idx_seller_name on tb_seller(name);

(2) IGNORE INDEX：如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint。

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

(3) FORCE INDEX：为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint 。

BestandW1shEs

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Mysql之快速上手sql优化

一. 优化SQL步骤在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化，本文将详细介绍在 MySQL中优化 SQL 语句的方法。本文将详细介绍，当面对一个有 SQL 性能问题的数据库时，应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题.
复制链接

扫一扫