MySQL的优化

于 2022-07-12 23:12:12 发布
阅读量288 收藏
点赞数
分类专栏： 数据库 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/JAX_fire/article/details/125686182
版权

概念

在应用的开发过程中，由于初期数据量较小，开发人员写SQL语句时更注重功能上的实现，但是当应用系统正式上线后，随着产生数据量的急剧增长，很多SQL语句开始逐渐显露出性能问题，对产生的影响也越来越大，此时有些问题SQL语句就成为整个系统性能的瓶颈，因此需要对其进行优化
MySQL有多种优化方式，可大致分为以下几类

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

查看执行频率

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

--下面命令显示了当前session中所有统计参数的值
SHOW SESSION STATUS LIKE 'Com_______';--查看当前会话统计结果
SHOW GLOBAL STATUS LIKE 'Com_______';--查看数据库上次启动至今统计结果
SHOW STATUS LIKE 'Innodb_rows_%';--查看针对Innodb引擎的统计结果

例如：

-- 查看当前会话SQL执行类型的统计信息
SHOW SESSION STATUS LIKE 'Com_______';

-- 查看全局（自从上次MySQL服务器启动至今）执行类型的统计信息
SHOW GLOBAL STATUS LIKE 'Com_______';

-- 查看针对InnoDB引擎的统计信息
SHOW STATUS LIKE 'Innodb_rows_%';

定位低效率执行SQL

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

  • 慢查询日志：通过慢查询日志定位那些执行效率较低的SQL语句
  • show processlist：该命令查看当前MySQL在进行的线程，包括线程状态、是否锁表等，可以实时的查看SQL的执行情况，同时对一些锁表操作进行优化。

定位低效率执行SQL-慢日志查询

--查看慢查询配置信息
SHOW VARIABLES LIKE '%slow_query_log%';
--开启慢日志查询
SET GLOBAL slow_query_log=1;
--查看慢日志记录SQL的最低阈值时间
SHOW VARIABLES LIKE 'long_query_time%';
--修改慢日志记录SQL最低阈值时间
SET GLOBAL long_query_time=4;

定位低效率执行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语句执行过程中表如何连接和连接的顺序。

字段含义
idselect查询的序列号，是一组数字，表示的是查询中执行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执行情况的说明和描述

例如：

CREATE DATABASE mydb13_optimize;
USE mydb13_optimize;

--数据准备
DROP TABLE IF EXISTS `privilege`;
CREATE TABLE `privilege`  (
  `pid` int NOT NULL AUTO_INCREMENT,
  `pname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`pid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of privilege
-- ----------------------------
INSERT INTO `privilege` VALUES (1, '玩跑车');
INSERT INTO `privilege` VALUES (2, '挖煤');
INSERT INTO `privilege` VALUES (3, '敲代码');

-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`  (
  `rid` int NOT NULL AUTO_INCREMENT,
  `rname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`rid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (1, '女神');
INSERT INTO `role` VALUES (2, '屌丝');
INSERT INTO `role` VALUES (3, '老板');

-- ----------------------------
-- Table structure for role_privilege
-- ----------------------------
DROP TABLE IF EXISTS `role_privilege`;
CREATE TABLE `role_privilege`  (
  `rid` int NULL DEFAULT NULL,
  `pid` int NULL DEFAULT NULL,
  INDEX `rid`(`rid`) USING BTREE,
  INDEX `pid`(`pid`) USING BTREE,
  CONSTRAINT `role_privilege_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `role` (`rid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `role_privilege_ibfk_2` FOREIGN KEY (`pid`) REFERENCES `privilege` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of role_privilege
-- ----------------------------
INSERT INTO `role_privilege` VALUES (1, 1);
INSERT INTO `role_privilege` VALUES (1, 3);
INSERT INTO `role_privilege` VALUES (2, 2);

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `uid` int NOT NULL AUTO_INCREMENT,
  `uname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`uid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '小巧');
INSERT INTO `user` VALUES (2, '张飞');
INSERT INTO `user` VALUES (3, '貂蝉');

-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role`  (
  `uid` int NULL DEFAULT NULL,
  `rid` int NULL DEFAULT NULL,
  INDEX `uid`(`uid`) USING BTREE,
  INDEX `rid`(`rid`) USING BTREE,
  CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`rid`) REFERENCES `role` (`rid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES (1, 1);
INSERT INTO `user_role` VALUES (1, 2);
INSERT INTO `user_role` VALUES (2, 2);
INSERT INTO `user_role` VALUES (3, 3);

SET FOREIGN_KEY_CHECKS = 1;



EXPLAIN SELECT * FROM user WHERE uid = 1;
EXPLAIN SELECT * FROM user WHERE uname='张飞';

explain分析执行计划-explain之id

id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id情况有三种：

  1. id相同表示加载表的顺序是从上到下
  2. id不同 id值越大，优先级越高，越先被执行
  3. id有相同，也有不同，同时存在。id相同可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行

例如：

SELECT * FROM user WHERE uid=1;

--  id相同表示加载表的顺序是从上到下
EXPLAIN SELECT * FROM user u,user_role ur,role r WHERE u.uid=ur.uid AND ur.rid = 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;

explain分析执行计划-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

例如：

-- 没有子查询和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：
-- UNION RESULT 
EXPLAIN SELECT * FROM user WHERE uid=1 UNION SELECT * FROM user WHERE uid=3;

explain分析执行计划-explain之type

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

type含义
UNLLMySQL不访问任何表，索引，直接返回结果
system系统表，少量数据，往往不需要进行磁盘IO；如果是5.7及以上版本的话就不是system了，而是all，即使记录只有一条
const命中主键（primary key）或者唯一（unique）索引；被连接的部分是一个常量（const）值
eq_ref对于前表的每一行，后表只有一行被扫描。（1）join查询（2）命中主键（primary key）或者非空唯一（unique not null）索引 （3）等值连接
ref非唯一性索引扫描，返回匹配某个单独值的所有行。对于前表的每一行（row），后表可能有多于一行的数据被扫描
range值检索给定返回的行，使用一个索引来选择行。where之后出现between，<，>，in等操作
index需要扫描索引上的全部数据
all全表扫描，此时id上无索引

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

例如：

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 = '张飞';

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

ALTER TABLE user2 ADD PRIMARY KEY(id);

EXPLAIN SELECT * FROM user2 a,user2_ex b WHERE  a.id = b.id; -- ALL

-- 在user_ex表添加一个重复的行数据
EXPLAIN SELECT * FROM user2 a,user2_ex b WHERE  a.id = b.id; -- ALL


-- range：范围查询
EXPLAIN SELECT * FROM user2 WHERE id>2;

-- INDEX :把索引列的全部数据都扫描
EXPLAIN SELECT id FROM user2;

explain分析执行计划-其他指标字段

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

extra含义
using filesort说明MySQL会对数据使用一个外母的索引排序，而不是按照表内的索引顺序进行读取，成为“文件排序”，效率低。
using temporary需要建立临时表（temporary table）来暂存中间结果，常见于order by和group by；效率低
using indexSQL所需要返回的所有列数据均在一科索引树上，避免访问表的数据行，效率不错。

例如：

EXPLAIN SELECT * FROM user WHERE uid = 1;

EXPLAIN SELECT * FROM user_role WHERE uid = 1;

EXPLAIN SELECT * FROM user ORDER BY uname;

EXPLAIN SELECT COUNT(*) FROM user GROUP BY uname;

EXPLAIN SELECT uid,COUNT(*) FROM user GROUP BY uid;

show profile分析SQL

MySQL从5.0.37版本开始增加了对show profiles和show profile语句的支持。show profiles能够在SQL优化时帮助我们了解时间都耗费到哪里去了。
通过have_profiling参数，能够看到当前MySQL是否支持profile：

SELCET @@have_profiling;
SET profiling=1; -- 开启profiling开关；
  • 通过show profile query query_id语句可以查看到该SQL 执行过程中每个线程的状态和消耗的时间。
  • 在获取到最消耗事件的线程状态后，MySQL支持进一步选择all、cpu、block io、context swtich、page faults等明细类型查看MySQL在使用什么资源上耗费了过高的时间。
字段含义
StatusSQL语句执行的状态
DurationSQL执行过程中每一个步骤的耗时
CPU_user当前用户占有的cpu
CPU_system系统占有的cpu

例如：

-- 查看当前的MySQL是否支持profile
SELECT @@have_profiling;
-- 如果不支持，则需要设置打开
SET PROFILING = 1;

-- 执行SQL
SHOW DATABASES;
USE mydb13_optimize;
SHOW TABLES;
SELECT COUNT(*) FROM USER;
SELECT * FROM user WHERE uid >2;

SHOW PROFILES;

SHOW PROFILE FOR QUERY 214;

SHOW PROFILE CPU FOR QUERY 214;

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_men_size = 1000000;

最后检查information_schema.optimizer_trace就可以知道MySQL是如何执行的。

SELECT * FROM information_schema.optimizer_trace\G;

例如：

SET optimizer_trace='enabled=on',end_markers_in_json=on;

SET  optimizer_trace_max_men_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;

使用索引优化

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

数据准备

USE mydb13_optimize;

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 `mydb13_optimize`.`tb_seller`(`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('alibaba', '阿里巴巴', '阿里小店', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');
INSERT INTO `mydb13_optimize`.`tb_seller`(`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('baidu', '百度科技有限公司', '百度小店', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');
INSERT INTO `mydb13_optimize`.`tb_seller`(`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('huawei', '华为科技有限公司', '华为小店', 'e10adc3949ba59abbe56e057f20f883e', '0', '北京市', '2088-01-01 12:00:00');
INSERT INTO `mydb13_optimize`.`tb_seller`(`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('itcast', '传智博客教育科技有限公司', '传智播客', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');
INSERT INTO `mydb13_optimize`.`tb_seller`(`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('ithema', '黑马程序员', '黑马程序员', 'e10adc3949ba59abbe56e057f20f883e', '0', '北京市', '2088-01-01 12:00:00');
INSERT INTO `mydb13_optimize`.`tb_seller`(`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('luoji', '罗技科技有限公司', '罗技小店', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');
INSERT INTO `mydb13_optimize`.`tb_seller`(`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('ourpalm', '掌趣科技股份有限公司', '掌趣小店', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');
INSERT INTO `mydb13_optimize`.`tb_seller`(`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('qiandu', '千度科技', '千度小店', 'e10adc3949ba59abbe56e057f20f883e', '2', '北京市', '2088-01-01 12:00:00');
INSERT INTO `mydb13_optimize`.`tb_seller`(`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('sina', '新浪科技有限公司', '新浪官方旗舰店', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');
INSERT INTO `mydb13_optimize`.`tb_seller`(`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('xiaomi', '小米科技', '小米官方旗舰店', 'e10adc3949ba59abbe56e057f20f883e', '1', '西安市', '2088-01-01 12:00:00');
INSERT INTO `mydb13_optimize`.`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);

避免索引失效应用-全值匹配

该情况下，索引生效，执行效率高。

例如：

--  全值匹配，和字段匹配成功即可，和字段无关
EXPLAIN SELECT * FROM tb_seller WHERE address = '北京市' AND name = '小米科技' AND `status` = '1' ;

EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' AND `status` = '1' AND address = '北京市';

避免索引失效应用-最左前缀法则

该情况下，索引生效，执行效率高。

例如：

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

EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技';

EXPLAIN SELECT * FROM tb_seller WHERE `status` = '1' AND name = '小米科技';

EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' AND `status` = '1' ;

-- 违法最左前缀法则，索引失效
EXPLAIN SELECT * FROM tb_seller WHERE `status` = '1';

-- 若果符合最左法则，但是出现跳跃某一列，只有最左列索引生效。

EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技'AND address = '北京市';

避免索引失效应用-其他匹配原则

该情况下，索引生效，执行效率高。

例如：

-- 尽量使用覆盖索引，避免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 = '北京市'; -- 效率高


-- 尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select*
EXPLAIN SELECT * FROM tb_seller WHERE name = '小米科技' AND address = '北京市'; -- 效率高

/*
extra:
	using index:使用覆盖索引的时候就会出现
	using where:在查找使用索引的情况下，需要回表去查询所需的数据
	using index condition:查询使用了索引，但是需要回表查询数据
	using index;using where:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
*/
-- 用or分割开的条件，如果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 not in ('alibaba','baidu'); -- 使用索引
EXPLAIN SELECT * FROM tb_seller WHERE sellerid  in ('alibaba','baidu'); -- 使用索引

-- 单列索引和复合索引，尽量使用符合索引
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;

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 = '西安市';
EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND `status` ='1' ;
EXPLAIN SELECT * FROM tb_seller WHERE  `status` ='1' AND address = '西安市';

SQL优化

大批量插入数据

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

  1. 主键顺序插入
    因为InnoDB类型的表时按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认床架弄一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这一点，来提高导入数据的效率。
  2. 关闭唯一性校验
    再倒入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1,恢复唯一性校验，可以提高导入的效率

例如：

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

-- 1.首先，检查一个全局系统变量'local_infile'的状态，如果得到如下显示Value=OFF，则说明这是不可用的
SHOW GLOBAL VARIABLES LIKE 'local_infile';

-- 2.修改local_infile值为ON，开启local_infile
SET GLOBAL local_infile=1;

-- 3.加载数据
-- 结论：当通过load向表加载数据时，尽量保证文件中的主键有序，这样可以提高执行效率
/*
sql1.log --->主键有序
sql2.log -->主键无序
*/

LOAD DATA LOCAL INFILE 'sql1.log' INTO TABLE tb_user FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

truncate table tb_user;
LOAD DATA LOCAL INFILE '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 'sql1.log' INTO TABLE tb_user FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

SET UNIQUE_CHECKS=1;

SELECT * FROM tb_user;

TRUNCATE TABLE tb_user;

优化insert语句

当畸形数据的insert操作时，可以考虑一下几种优化方案
如果需要同时对一张表插入很多行数据时，应该尽量使用多个值的表insert语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

优化order by语句

两种排序方式

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

Filesor的优化
通过创建合适的索引，能够减少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系统变量，来增大排序区的大小，提高排序的效率。

例如：

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 `mydb13_optimize`.`emp`(`id`, `name`, `age`, `salary`) VALUES (1, 'Tom', 25, 2300);
INSERT INTO `mydb13_optimize`.`emp`(`id`, `name`, `age`, `salary`) VALUES (2, 'Jerry', 30, 3500);
INSERT INTO `mydb13_optimize`.`emp`(`id`, `name`, `age`, `salary`) VALUES (3, 'Luci', 25, 2800);
INSERT INTO `mydb13_optimize`.`emp`(`id`, `name`, `age`, `salary`) VALUES (4, 'Jay', 36, 3500);
INSERT INTO `mydb13_optimize`.`emp`(`id`, `name`, `age`, `salary`) VALUES (5, 'Tom2', 21, 2200);
INSERT INTO `mydb13_optimize`.`emp`(`id`, `name`, `age`, `salary`) VALUES (6, 'Jerry', 31, 3300);
INSERT INTO `mydb13_optimize`.`emp`(`id`, `name`, `age`, `salary`) VALUES (7, 'Luci2', 26, 2700);
INSERT INTO `mydb13_optimize`.`emp`(`id`, `name`, `age`, `salary`) VALUES (8, 'Jay2', 33, 3500);

-- 创建索引组合
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,salary FROM emp ORDER BY age; -- using index
EXPLAIN SELECT id,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'; -- 1024
SHOW VARIABLES LIKE'sort_buffer_size';

优化子查询

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

优化limit查询

一般分页查询时，通过创建覆盖索引能比较好地提高性能。一个常见又非常头疼的问题就是limit 900000,10 ， 此时需要MySQL排序前900010记录，仅仅返回900000~900010记录，其他记录丢弃，查询排序的代价非常大。
优化思路一：
在索引上完成排序分页操作，最后根据主键关联返回原表查询所需要的其他列表内容。
优化思路二：
该方案适用于主键自增的表，可以把limit查询转换成某个位置的查询。

例如：

-- 优化limit
SELECT  COUNT(*) FROM tb_user;

SELECT * FROM tb_user limit 0,10;

EXPLAIN SELECT * FROM tb_user limit 900000,10;

EXPLAIN SELECT * FROM tb_user a, (SELECT id FROM tb_user ORDER BY id LIMIT 900000,10)b WHERE a.id =b.id;

EXPLAIN SELECT * FROM tb_user WHERE id > 900000 LIMIT 10;
火眼猊
关注
专栏目录
程序/进程执行时间分析（user cpu time, system cpu time, elpapsed time）
鸟恋旧林的博客
09-16 1万+
1. 背景 近日做程序优化任务，代码不长，时间测试起来需要在for循环里加，破坏了程序执行的流水线，导致时间不准。网上查看了些相关资料，学到了一些东西，虽然对本任务没用，但对于程序/进程执行时间可加深理解。 首先，需要明白的是： 我们平时常用的测量运行时间的方法并不是那么精确的，换句话说，想精确获取程序运行时间并不是那么容易的。也许你会想，程序不就是一条条指令么，每一条指令序列都有固定...
【MySQL】MySQL的优化（二）
最新发布
专注于Java领域开发 关注我 带你玩转Java
03-14 1018
通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
Mysql分析-profile详解
热门推荐
hann的专栏
02-06 2万+
一。前言 当我们要对某一条sql的性能进行分析时，可以使用它。 Profiling是从 mysql5.0.3版本以后才开放的。 启动profile之后，所有查询包括错误的语句都会记录在内。 关闭会话或者set profiling=0 就关闭了。（如果将profiling_history_size参数设置为0，同样具有关闭MySQL的profiling效果。） 此工具可用来
mysql profile的作用_Mysql分析-profile详解
weixin_33987275的博客
02-07 263
一。前言当我们要对某一条sql的性能进行分析时，可以使用它。Profiling是从 mysql5.0.3版本以后才开放的。启动profile之后，所有查询包括错误的语句都会记录在内。关闭会话或者set profiling=0 就关闭了。(如果将profiling_history_size参数设置为0，同样具有关闭MySQL的profiling效果。)此工具可用来查询SQL执行状态，System l...
MySql优化之show profile分析SQL
xiaxiaomao1981的博客
02-19 374
Mysql从5.0.3版本开始增加了对show profiles和show profile的支持，show profile能够帮我们了解在sql语句执行过程中时间耗费在了哪些地方 以下操作都是session级别的 我们可以通过 select @@have_profiling来显示当前mysql是否支持profile: 通过 select @@profiling查看profile是否...
mysql profiles_MySQL性能分析show profiles详解
weixin_42503076的博客
01-27 391
前言前几篇文章我们讲了什么是 MySQL 索引，explain分析SQL语句是否用到索引，以及索引的优化等一系列的文章，今天我们来讲讲Show profiles，看看SQL耗时到底出现在哪个环节。Show profiles是什么是MySQL提供可以用来分析当前会话中SQL语句执行的资源消耗情况。可以用于SQL的调优测量。默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果开启 profil...
Mysql优化之Zabbix分区优化
09-09
MySQL优化是提升数据库性能的关键，尤其对于Zabbix这样的监控系统来说，其性能直接影响到监控数据的收集和告警的及时性。Zabbix的数据主要存储在`history`和`trends`两个表中，随着时间的推移，这两个表会变得非常...
mysql优化笔记+资料
11-18
以下是一份详细的MySQL优化笔记，涵盖了多个方面： 一、查询优化 1. 使用索引：为经常用于搜索的列创建索引可以显著加快查询速度。B树和哈希索引是最常见的类型，适用于不同的查询场景。 2. 避免全表扫描：尽量使用...
MySQL优化方案参考
12-15
MySQL优化是一个涵盖多个层面的复杂任务，涉及到硬件配置、操作系统设置以及数据库本身的调整。这篇文章将主要探讨硬件层、系统层和MySQL层的优化方案。 在硬件层，优化首先从CPU开始。为了最大化CPU性能，应调整...
MySQL 调优基础(一) CPU与进程
weixin_34190136的博客
09-29 132
一般而言，MySQL 的调优可以分为两个层面，一个是在MySQL层面上进行的调优，比如SQL改写，索引的添加，MySQL各种参数的配置；另一个层面是从操作系统的层面和硬件的层面来进行调优。操作系统的层面的调优，一般要先定位到是那种资源出现瓶颈——CPU、 内存、硬盘、网络，然后入手调优。所以其实MySQL 的调优，其实不是那么简单，它要求我们对 硬件、OS、MySQL 三者都具有比较深入的理解。比...
MySQL数据库CPU问题一则
老叶茶馆
01-30 692
作者：张政俊，中欧基金DBAMysql 一般出现 CPU 负载过高问题的时候，我们都会去看下故障期间的慢sql日志，然后找出全表扫描、索引不合理、函数运算过多的sql，让开发同学优化下。...
mysql分析之profile详解（过去用法，但常用），一篇博客帮你理解 profile 的用法
梦想橡皮擦，专栏100例写作模式先行者，现象级专栏 《Python 爬虫 100 例》作者、《滚雪球学 Python 专栏》原创者
01-19 2万+
Mysql分析-profile详解
MySQL SQL剖析(SQL profile)
乐沙弥的世界
10-11 5226
分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优
(五)逻辑架构
小怪的博客
02-14 4905
逻辑架构1. 逻辑架构剖析1.1 服务器处理客户端请求1.2、 Connectors1.3、第1层：连接层1.4、第2层：服务层1.5、第3层：引擎层1.6、存储层1.7、小结2、SQL执行流程2.1 MySQL 中的 SQL执行流程==MySQL的查询流程==1、查询缓存2.解析器3.优化器4. 执行器2.21. 确认profiling 是否开启2.33、数据库缓冲池(buffer pool)3.1 缓冲池 vs 查询缓存1. 缓冲池（Buffer Pool）2. 查询缓存3.2 缓冲池如何读取数据3.3
user CPU sys CPU
Crack The World
10-29 4678
实际运行时间：从开始运行到运行结束的时间。 用户CPU时间：执行每行代码的时间 系统CPU时间；占用CPU时间（比如说内存copy,例如read,write,在内核内执行该服务所花费的时间） CPU时间= 用户cpu时间+系统cpu时间 real 0m0.072s user 0m0.030s sys 0m0.050s 时钟时间(墙上时钟时间wall clock time)：就是一个
mysql调优_MySQL 调优基础(一) CPU与进程
weixin_39642981的博客
11-26 250
一般而言，MySQL 的调优可以分为两个层面，一个是在MySQL层面上进行的调优，比如SQL改写，索引的添加，MySQL各种参数的配置；另一个层面是从操作系统的层面和硬件的层面来进行调优。操作系统的层面的调优，一般要先定位到是那种资源出现瓶颈——CPU、 内存、硬盘、网络，然后入手调优。所以其实MySQL 的调优，其实不是那么简单，它要求我们对 硬件、OS、MySQL 三者都具有比较深入的理解。比...
数据库优化：优化SQL步骤
菜鸡也有大佬梦
12-24 3821
1. Mysql的体系结构概览 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sWTxGEtx-1577188310478)(assets/000001.jpg)] 整个MySQL Server由以下组成 Connection Pool : 连接池组件 Management Services & Utilities : 管理服务和工具组件 SQL Inte...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

火眼猊

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值