MySQL的优化

概念

在应用的开发过程中，由于初期数据量较小，开发人员写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语句执行过程中表如何连接和连接的顺序。

字段	含义
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	执行情况的说明和描述

例如：

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