深入浅出MySQL调优

深入浅出MySQL调优

本文章对MySQL调优做一个系统的介绍与实践，为了便于实战，本文一些操作将基于Linux环境下进行，希望对需要学习MySQL调优的人有所帮助。

---

---

1. 预备知识

1.1 MySQL体系结构

整个MySQL Server体系结构由以下部分组成

1. Connecntion Pool：连接池组件
2. Management Services & Utilities：管理服务和工具组件
3. SQL Interface：SQL接口组件
4. Parser：查询分析器组件
5. Optimizer：优化器组件
6. Caches & Buffers：缓冲器组件
7. File System：文件系统

整个体系结构又可以分为四层

1、连接层
最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2、服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3、引擎层
存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

4、存储层
数据存储层， 主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

1.2 MySQL存储引擎

1.2.1 存储引擎的概述

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

可以通过以下命令来查询当前数据库支持的存储引擎

show engines;

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB

1.2.2 存储引擎的特性

下面重点介绍几种常用的存储引擎， 并对比各个存储引擎之间的区别， 如下表所示 ：

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储限制	64TB	有	有	没有	有
事务安全	支持
锁机制	行锁(适合高并发)	表锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持(5.6版本之后)	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低	低
内存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外键	支持

这里将重点介绍最长使用的两种存储引擎： InnoDB、MyISAM

InnoDB

InnoDB存储引擎是Mysql5.5之后的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

MySQL支持外键的存储引擎只有InnoDB ，在创建外键的时候，要求父表必须有对应的索引 ，子表在创建外键的时候，也会自动的创建对应的索引。

下面两张表中 ， country_innodb是父表 ， country_id为主键索引，city_innodb表是子表，country_id字段为外键，对应于country_innodb表的主键country_id 。

create table country_innodb(
	country_id int NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL,
    primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


create table city_innodb(
	city_id int NOT NULL AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int NOT NULL,
    primary key(city_id),
    key idx_fk_country_id(country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);

存储方式

InnoDB 存储表和索引有以下两种方式 ：

①. 使用共享表空间存储， 这种方式创建的表的表结构保存在.frm文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。

②. 使用多表空间存储， 这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。

MyISAM

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。

文件存储方式

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是 ：

.frm (存储表结构)；

.MYD(MYData , 存储数据)；

.MYI(MYIndex , 存储索引)；

1.3 存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。

• InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
• MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

2. 优化SQL的步骤

2.1 查看SQL的执行频率

MySQL 客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的统计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

下面命令显示了当前 session 中所有统计参数的值：

//七个下划线
show status like 'Com_______'

下面命令显示了Innodb存储引擎的统计结果

show status like 'Innodb_rows_%';

我们通常关心的是以下几个统计参数

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

2.2 定位低效SQL方式

可以通过以下两种方式定位执行效率较低的 SQL 语句。具体操作将放在第6章节细讲

• 慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句，用–log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。
• show processlist : 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

2.3 分析SQL执行计划

2.3.1 环境准备

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.3.2 explain之id解析

explain字段能为大家展示一条SQL语句的执行计划，如下图所示。

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

在explain中id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。

id 情况有三种
1、 id 相同表示加载表的顺序是从上到下。如上图的SQL语句

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

3、 id 有相同，也有不同，同时存在。id相同的可以认为是一组，在同一组中从上往下顺序执行；在所有的组中，id的值越大的组，优先级越高，越先执行。(由于SQL底层有优化器，在不同SQL版本中可能会出现不同情况)

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ; 

2.3.3 explain之select_type解析

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为PRIMARY
SUBQUERY	在SELECT 或 WHERE 列表中的子查询
DERIVED	在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED
UNION RESULT	从UNION表获取结果的SELECT

2.3.4 explain之table解析

就是关于哪张表的查询，若此表在SQL语句中被取了别名则显示别名。

2.3.5 explain之type解析

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

type	含义
NULL	MySQL不访问任何表或索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。如where id = ’ 1’，MySQL就能将该查询转换为一个常量，然后将 “主键” 或 “唯一” 索引与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	范围操作，例如 where 之后出现 between ， < , > , in 等操作。
index	index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行

效率从最好到最坏依次是：

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

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

2.3.6 explain之key解析

possible_keys：显示可能会使用到的索引， 一个或多个。

key：实际使用的索引， 如果为NULL， 则没有使用索引。

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

2.3.7 explain之Extra解析

其他的额外的执行计划信息，在该列展示 。

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

2.4 show profile分析执行时间

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

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

SELECT @@have_profiling

默认profiling是关闭的，可以通过set语句在Session级别开启profiling：

set profiling=1; //开启profiling 开关；

通过profile，我们能够更清楚地了解SQL执行的过程。

首先，我们可以执行一系列的操作，如下图所示：

show databases;

use db01;

show tables;

select * from tb_item where id < 5;

select count(*) from tb_item;

执行完上述命令之后，再执行show profiles 指令， 来查看SQL语句执行的耗时，Duration中以秒为单位：

2.5 索引的使用

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

准备环境

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('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','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');

在没有添加索引的情况下我们需要进行这样一条查询

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'

可以看到，需要进行全表扫描，扫描的行数随着表的记录数的增加而增加，效率相当低。
现在我们为name status address建立索引

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

这时候已经变成了索引访问，根据之前2.3.5章节所述，效率将大大提升。

2.5.1 避免索引失效

1、最左匹配原则

如果索引是一个联合索引（有多列），使用时要遵循最左匹配原则。指的查询必须使用索引的最左列

刚才建立索引的是（name,status,address)

根据最左匹配原则，要使索引生效

一、查询中where必须包含最左字段（在这里是name），位置不限。如果没有，则索引全部失效

二、如果一个字段出现在筛选条件中，如果它有前面的字段，则它前面的字段也必须出现在where条件中（位置不限），否则索引部分失效（回表）或全部失效（第一种情况下全部失效），例如address出现了，status也必须出现

索引部分失效：SQL会根据最左索引筛选出一部分结果，然后从这部分结果中遍历 => 回表
索引全部失效：全文遍历

索引生效情况

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 name='小米科技' and status='1' 

//字段位置不限，情况未全部列出，仅仅举例
explain select * from tb_seller where status='1' and address='北京市' and name='小米科技'

explain select * from tb_seller where status='1' and name='小米科技' and address='北京市' 

explain select * from tb_seller where status='1' and name='小米科技'

索引失效的情况

//使用了address而没有使用status，索引部分失效
explain select * from tb_seller where name='小米科技' and address='北京市'

//没有使用name，索引全部失效
explain select * from tb_seller where status='1' and address='北京市'

2、范围查询字段右边的字段不能使用索引
例如status是范围查询的字段，如下所示

//在这种情况下，name和status是走索引的，而address则失效
explain select * from tb_seller where name='小米科技' and status > '1' and address='北京市'

3、进行运算操作的字段， 索引将失效。

//在name上进行运算操作，由于Name是最左字段，则索引全部失效
EXPLAIN SELECT * FROM tb_seller where SUBSTRING(name,3,2) = '科技'

//在其他字段进行运算操作，索引部分失效
EXPLAIN SELECT * FROM tb_seller where name = '小米科技' and status = '1' and SUBSTRING(address,1,2) = '北京'

4、字符串不加引号，索引失效

由于MySQL底层具有查询优化器，有些字符串和数字类型能够相互转换的，若没加引号，该字段索引将失效

//status字段的索引将失效
explain select * from tb_seller where name='小米科技'  and address='北京市' and status = 1

5、查询的字段超过索引的字段，将会降低性能
我们知道，在查询的时候要避免使用select * ，应使用覆盖索引（即查询的字段是已经创建索引的字段），但是如果查询的字段中包含了未创建索引的字段，性能就会降低。

//password字段不包含在索引中
explain select status,address,password from tb_seller where name='小米科技'  and address='北京市' and status = 1

6、用or进行连接时，如果其中一半没有用到索引，索引全部失效

//其中createtime字段没有用到索引
explain select * from tb_seller where name='小米科技' or createtime = '2000-01-01'

7、以%开头的模糊查询，索引失效问题
如果仅仅是尾部%匹配，索引不会失效，但属于范围查询，而头部模糊匹配，索引失效，全文扫描

//头部匹配，索引失效
explain select * from tb_seller where name like '%科技' 

解决方案：
使用覆盖索引

explain select name from tb_seller where name like '%科技%' 

8、如果MySQL评估使用索引比全表扫描更慢，则不使用索引
例如is NULL和is NOT NULL进行判断，如果表中的is NOT NULL数据量占绝大多数，则不使用索引，反之亦然。

注：查询计划中的Extra列中

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

2.6 查看索引的使用情况

//当前会话
show status like 'Handler_read%';	
//当前数据库
show global status like 'Handler_read%';	

• Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（越低越好）。

• Handler_read_key：如果索引正在工作，代表一个行被索引值读的次数。（越高越好）。

• Handler_read_next ：按照键顺序读下一行的请求数。如果用范围约束或如果执行索引扫描来查询索引列，该值增加。

• Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。

• Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。

• Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或查询没有利用索引。

2.7 优化SQL语句

2.7.1 插入数据时，使用主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，可以利用这点，来提高导入数据的效率。

2.7.2 优化Insert语句

当进行数据插入操作时，可以采用以下几种优化方案。

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

另外，插入的数据是有序的也会提高插入的效率。

例：

//无序且多条
insert into tb_test values(3,'Tom');
insert into tb_test values(1,'Cat');
insert into tb_test values(2,'Jerry');

优化后的方案为：

//有序且只有一条
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

2.7.3 优化orderby语句

排序方式通常有两种
一、对返回数据进行排序，也称为filesort排序，这种方式是得到结果后还要对结果进行遍历排序，效率低下

二、通过有序索引进行顺序扫描返回的有序数据，这种情况为using index或Backward index scan;Using index，不需要额外进行排序，所以在排序时包含有序索引字段会提升效率。

三、在对多字段进行排序时尽量同升序或同降序，如果出现一个升序一个降序的情况，第二个排序字段会出现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 系统变量，来增大排序区的大小，提高排序的效率。

2.7.4 优化group by语句

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

一、在无索引的情况下，如果仅仅为了分组而不需要排序，可以在语句最后加上order by null

explain select age,count(*) from emp group by age order by null;

二、通过索引，这个与order by一样，这里就不举例了

2.7.5 优化嵌套语句

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接查询替代。

示例

 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;

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

2.7.6 优化or条件

对于包含or的查询子句，要提高查询效率，则or左右两边的条件都必须用到索引，如果没有索引，则应考虑增加索引。

//id和age都为索引字段
explain select * from emp where id = 1 or age = 30;

也可以使用union替换or

explain select * from emp where id = 1 UNION SELECT * FROM emp where age = 30;

其中type的效率从好到差依次是

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

2.7.7 优化分页查询

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

例：

EXPLAIN SELECT * from tb_item LIMIT 2000000,10;

1、优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他内容

EXPLAIN SELECT * from tb_item t,(SELECT id FROM tb_item ORDER BY id limit 2000000,10) a where t.id = a.id

2、优化思路二

该方案仅适用于主键自增的表，可以把limit查询转换成从某个位置开始的范围查询

EXPLAIN SELECT * FROM tb_item where id > 2000000 LIMIT 10

2.7.8 使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

在有多个索引可用的情况下，sql在查询的时候会判断哪个索引是最高效的，这种判断有时候也会损耗时间，如果我们为sql查询指定索引可以加快查询效率。

EXPLAIN SELECT * FROM tb_seller use INDEX(idx_seller_name_sta_addr) where name = "小米科技"

扩展：
1、如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index。

2、为强制MySQL使用一个特定的索引，可在查询中使用 force index。

3 应用优化

前面，我们介绍了很多数据库的优化措施。但是在实际生产环境中，由于数据库本身的性能局限，就必须要对前台的应用进行一些优化，来降低数据库的访问压力。

3.1 使用连接池

对于访问数据库来说，建立连接的代价是很大的，如果我们频繁的创建和关闭连接时非常耗费资源的，所以我们有必要建立数据库连接池。

对于不同的编程语言都有不同的连接池框架，大家可以自行学习，这里不再细说。

3.2 减少重复请求

3.2.1 避免对数据进行重复检索

在编写应用代码时，要能够理清对数据库的访问逻辑，能够一次连接就获取到结果的就不用两次连接，这样可以大大减少对数据库无用的重复请求。

比如 ，需要获取书籍的id 和name字段 ， 则查询如下：

 select id , name from tb_book;

之后，在业务逻辑中有需要获取到书籍状态信息， 则查询如下：

select id , status from tb_book;

这样，就需要向数据库提交两次请求，数据库就要做两次查询操作。其实完全可以用一条SQL语句得到想要的结果。

select id, name, status from tb_book;

3.2.2 为数据库增加缓存层

在应用中，我们可以在应用中增加缓存层来达到减轻数据库负担的目的。缓存层有很多种，也有很多实现方式，只要能达到降低数据库的负担又能满足应用需求就可以。

因此可以部分数据从数据库中抽取出来放到应用端以文本方式存储， 或者使用框架(Mybatis, Hibernate)提供的一级缓存/二级缓存，或者使用redis数据库来缓存数据 。

3.3 负载均衡

负载均衡是应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上， 以此来降低单台服务器的负载，达到优化的效果。

3.3.1 主从复制分流查询

通过MySQL的主从复制，实现读写分离，使增删改操作走主节点，查询操作走从节点，从而可以降低单台服务器的读写压力。

…文章较长，续更