MySql详解优化2

MySql基础知识请参考MySql详解基础 MySql优化知识请参考MySql详解优化

三、索引和查询优化

7、连接详解

（1）连接的本质

数据准备

CREATE TABLE t1 (m1 int, n1 char(1));
CREATE TABLE t2 (m2 int, n2 char(1));
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。所以我们把t1和t2两个表连接起来的过程如下图所示：

mysql> SELECT * FROM t1, t2;
+----+----+----+----+
| m1 | n1 | m2 | n2 |
+----+----+----+----+
|  3 | c  |  2 | b  |
|  2 | b  |  2 | b  |
|  1 | a  |  2 | b  |
|  3 | c  |  3 | c  |
|  2 | b  |  3 | c  |
|  1 | a  |  3 | c  |
|  3 | c  |  4 | d  |
|  2 | b  |  4 | d  |
|  1 | a  |  4 | d  |
+----+----+----+----+

​ 这个过程看起来就是把t1表的记录和t2的记录连起来组成新的更大的记录，所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为笛卡尔积。因为表t1中有3条记录，表t2中也有3条记录，所以这两个表连接之后的笛卡尔积就有3×3=9行记录。在MySQL中，连接查询的语法也很随意，只要在FROM语句后边跟多个表名就好了。

（2）连接过程

​ 如果我们乐意，我们可以连接任意数量张表，但是如果没有任何限制条件的话，这些表连接起来产生的笛卡尔积可能是非常巨大的。比方说3个100行记录的表连接起来产生的笛卡尔积就有100×100×100=1000000行数据！所以在连接的时候过滤掉特定记录组合是有必要的。

• 涉及单表的条件

​ 这种只设计单表的过滤条件我们之前都提到过一万遍了，我们之前也一直称为搜索条件，比如t1.m1 > 1是只针对t1表的过滤条件，t2.n2 < 'd'是只针对t2表的过滤条件。

SELECT * FROM t1, t2 where t1.m1 > 1;
SELECT * FROM t1, t2 where t2.n2 < 'd';

• 涉及两表的条件

​ 这种过滤条件我们之前没见过，比如t1.m1 = t2.m2、t1.n1 > t2.n2等，这些条件中涉及到了两个表，我们稍后会仔细分析这种过滤条件是如何使用的。

SELECT * FROM t1, t2 where t1.m1 = t2.m2;
SELECT * FROM t1, t2 where t1.n1 > t2.n2;

连接查询的大致执行过程

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

在这个查询中我们指明了这三个过滤条件：

• t1.m1 > 1
• t1.m1 = t2.m2
• t2.n2 < 'd'

那么这个连接查询的大致执行过程如下：

​ 首先确定第一个需要查询的表，这个表称之为驱动表。怎样在单表中执行查询语句我们在前一章都介绍过了，只需要选取代价最小的那种访问方法去执行单表查询语句就好了（就是说从const、ref、ref_or_null、range、index、all这些执行方法中选取代价最小的去执行查询）。此处假设使用t1作为驱动表，那么就需要到t1表中找满足t1.m1 > 1的记录，因为表中的数据太少，我们也没在表上建立二级索引，所以此处查询t1表的访问方法就设定为all吧。

​ 我们可以看到，t1表中符合t1.m1 > 1的记录有两条。针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到t2表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。因为是根据t1表中的记录去找t2表中的记录，所以t2表也可以被称之为被驱动表。上一步骤从驱动表中得到了2条记录，所以需要查询2次t2表。此时涉及两个表的列的过滤条件t1.m1 = t2.m2就派上用场了：

• 当t1.m1 = 2时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 2，所以此时t2表相当于有了t2.m2 = 2、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询。

• 当t1.m1 = 3时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 3，所以此时t2表相当于有了t2.m2 = 3、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询。

  所以整个连接查询的执行过程就如下图所示：

​ 也就是说整个连接查询最后的结果只有两条符合过滤条件的记录。从上面两个步骤可以看出来，我们上面介绍的这个两表连接查询共需要查询1次t1表，2次t2表。当然这是在特定的过滤条件下的结果，如果我们把t1.m1 > 1这个条件去掉，那么从t1表中查出的记录就有3条，就需要查询3次t2表了。也就是说在两表连接查询中，驱动表只需要访问一次，被驱动表可能多次。

（3）内连接和外连接

数据准备

-- 学生信息表
CREATE TABLE studentinfo (
    number INT NOT NULL COMMENT '学号',
    name VARCHAR(5) COMMENT '姓名',
    major VARCHAR(30) COMMENT '专业',
    PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生信息表';

-- 学生成绩表
CREATE TABLE score (
    number INT COMMENT '学号',
    subject VARCHAR(30) COMMENT '科目',
    score TINYINT COMMENT '成绩',
    PRIMARY KEY (number, score)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生成绩表';
-- 插入数据
insert into studentinfo values
(20180101,'杜子腾','软件学院'),
(20180102,'范统','计算机科学与工程'),
(20180103,'史珍香','计算机科学与工程');
insert into score values
(20180101,'母猪的产后护理',78),
(20180101,'论萨达姆的战争准备',88),
(20180102,'论萨达姆的战争准备',98),
(20180102,'母猪的产后护理',100);

-- 数据如下
mysql> select *  from studentinfo;
+----------+--------+------------------+
| number   | name   | major            |
+----------+--------+------------------+
| 20180101 | 杜子腾 | 软件学院         |
| 20180102 | 范统   | 计算机科学与工程 |
| 20180103 | 史珍香 | 计算机科学与工程 |
+----------+--------+------------------+
3 rows in set (0.05 sec)

mysql> select *  from score;
+----------+--------------------+-------+
| number   | subject            | score |
+----------+--------------------+-------+
| 20180101 | 母猪的产后护理     |    78 |
| 20180101 | 论萨达姆的战争准备 |    88 |
| 20180102 | 论萨达姆的战争准备 |    98 |
| 20180102 | 母猪的产后护理     |   100 |
+----------+--------------------+-------+
4 rows in set (0.09 sec)

内连接和外连接

​ 现在我们想把每个学生的考试成绩都查询出来就需要进行两表连接了（因为score中没有姓名信息，所以不能单纯只查询score表）。连接过程就是从student表中取出记录，在score表中查找number相同的成绩记录，所以过滤条件就是student.number = socre.number，整个查询语句就是这样：

mysql> SELECT s1.number, s1.name, s2.subject, s2.score FROM studentinfo AS s1, score AS s2 WHERE s1.number = s2.number;
+----------+--------+--------------------+-------+
| number   | name   | subject            | score |
+----------+--------+--------------------+-------+
| 20180101 | 杜子腾 | 母猪的产后护理     |    78 |
| 20180101 | 杜子腾 | 论萨达姆的战争准备 |    88 |
| 20180102 | 范统   | 论萨达姆的战争准备 |    98 |
| 20180102 | 范统 | 母猪的产后护理     |   100 |
+----------+--------+--------------------+-------+

​ 从上述查询结果中我们可以看到，各个同学对应的各科成绩就都被查出来了，可是有个问题，史珍香同学，也就是学号为20180103的同学因为某些原因没有参加考试，所以在score表中没有对应的成绩记录。那如果老师想查看所有同学的考试成绩，即使是缺考的同学也应该展示出来，但是到目前为止我们介绍的连接查询是无法完成这样的需求的。我们稍微思考一下这个需求，其本质是想：驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。为了解决这个问题，就有了内连接和外连接的概念：

• 对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上面提到的连接都是所谓的内连接。

• 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。在MySQL中，根据选取驱动表的不同，外连接仍然可以细分为2种：

• 左（外）连接：选取左侧的表为驱动表。

• 右（外）连接：选取右侧的表为驱动表。

​ 可是这样仍然存在问题，即使对于外连接来说，有时候我们也并不想把驱动表的全部记录都加入到最后的结果集。这就犯难了，有时候匹配失败要加入结果集，有时候又不要加入结果集，把过滤条件分为两种就解决了这个问题了么，所以放在不同地方的过滤条件是有不同语义的：

• WHERE子句中的过滤条件

  WHERE子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。

• ON子句中的过滤条件

  对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。

​ 需要注意的是，这个ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，所以如果把ON子句放到内连接中，MySQL会把它和WHERE子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。

  一般情况下，我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。

左（外）连接的语法

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

​ 其中，中括号里的OUTER单词是可以省略的。对于LEFT JOIN类型的连接来说，我们把放在左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表。所以上述例子中t1就是外表或者驱动表，t2就是内表或者被驱动表。需要注意的是，对于左（外）连接和右（外）连接来说，必须使用ON子句来指出连接条件。

​ 了解了左（外）连接的基本语法之后，再次回到我们上面那个现实问题中来，看看怎样写查询语句才能把所有的学生的成绩信息都查询出来，即使是缺考的考生也应该被放到结果集中：

mysql> SELECT s1.number, s1.name, s2.subject, s2.score FROM studentinfo AS s1 LEFT JOIN score AS s2 ON s1.number = s2.number;
+----------+--------+--------------------+-------+
| number   | name   | subject            | score |
+----------+--------+--------------------+-------+
| 20180101 | 杜子腾 | 母猪的产后护理     |    78 |
| 20180101 | 杜子腾 | 论萨达姆的战争准备 |    88 |
| 20180102 | 范统   | 论萨达姆的战争准备 |    98 |
| 20180102 | 范统   | 母猪的产后护理     |   100 |
| 20180103 | 史珍香 | NULL               | NULL  |
+----------+--------+--------------------+-------+

​ 从结果集中可以看出来，虽然史珍香并没有对应的成绩记录，但是由于采用的是连接类型为左（外）连接，所以仍然把她放到了结果集中，只不过在对应的成绩记录的各列使用NULL值填充而已。

右（外）连接的语法

SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

​ 右（外）连接和左（外）连接的原理是一样一样的，语法也只是把LEFT换成RIGHT而已，只不过驱动表是右边的表，被驱动表是左边的表。

内连接的语法

  内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集，我们最开始介绍的那些连接查询的类型都是内连接。不过之前仅仅提到了一种最简单的内连接语法，就是直接把需要连接的多个表都放到FROM子句后边。其实针对内连接，MySQL提供了好多不同的语法，我们以t1和t2表为例看看：

SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];

也就是说在MySQL中，下面这几种内连接的写法都是等价的：

• SELECT * FROM t1 JOIN t2;
• SELECT * FROM t1 INNER JOIN t2;
• SELECT * FROM t1 CROSS JOIN t2;
• SELECT * FROM t1, t2;

​ 现在我们虽然介绍了很多种内连接的书写方式，不过熟悉一种就好了，这里我们推荐INNER JOIN的形式书写内连接（因为INNER JOIN语义很明确嘛，可以和LEFT JOIN和RIGHT JOIN很轻松的区分开）。这里需要注意的是，由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句。

​ 我们前面说过，连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。不论哪个表作为驱动表，两表连接产生的笛卡尔积肯定是一样的。而对于内连接来说，由于凡是不符合ON子句或WHERE子句中的条件的记录都会被过滤掉，所以对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。但是对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合ON子句连接条件的记录，所以外连接的驱动表和被驱动表不能轻易互换。

（4）join 语句原理

​ 上面的介绍都只是为了唤醒大家对连接、内连接、外连接这些概念的记忆，这些基本概念是为了真正进入本章主题做的铺垫。真正的重点是MySQL采用了什么样的算法来进行表与表之间的连接，了解了这个之后，大家才能明白为什么有的连接查询运行的快如闪电，有的却慢如蜗牛。

驱动表和被驱动表

​ join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会非常长。在MySQL5.5 以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

-- 创建表
CREATE TABLE a(f1 INT, f2 INT, INDEX(f1))ENGINE=INNODB;
CREATE TABLE b(f1 INT, f2 INT)ENGINE=INNODB;

-- 插入数据
INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

-- 内连接查询
explain select * from a join b on a.f1=b.f1;

-- 外连接查询
explain select * from a left join b on a.f1=b.f1 where a.f2=b.f2;

explain select * from a left join b on a.f1=b.f1 and a.f2=b.f2;

对于内连接来说，A一定是驱动表吗？不一定，优化器会根据你查询语句做优化，决定先查哪张表。先查询的是驱动表， 反之就是被驱动表。

对于外连接来说，A也不一定是驱动表。

简单嵌套循环连接（Simple Nested-Loop Join）

  我们前面说过，对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表。我们上面已经大致介绍过t1表和t2表执行内连接查询的大致过程，我们温习一下：

• 步骤1：选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
• 步骤2：对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

  如果有3个表进行连接的话，那么步骤2中得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，重复上面过程，也就是步骤2中得到的结果集中的每一条记录都需要到t3表中找一找有没有匹配的记录，用伪代码表示一下这个过程就是这样：

for each row in t1 {   #此处表示遍历满足对t1单表查询结果集中的每一条记录
    
    for each row in t2 {   #此处表示对于某条t1表的记录来说，遍历满足对t2单表查询结果集中的每一条记录
    
        for each row in t3 {   #此处表示对于某条t1和t2表的记录组合来说，对t3表进行单表查询
            if row satisfies join conditions, send to client
        }
    }
}

​ 这个过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接（Nested-Loop Join），这是最简单，也是最笨拙的一种连接查询算法。

索引嵌套循环连接(Index Nested-Loop Join)

  我们知道在嵌套循环连接的步骤2中可能需要访问多次被驱动表，如果访问被驱动表的方式都是全表扫描的话，性能堪忧，查询t2表其实就相当于单表扫描，我们可以利用索引来加快查询速度。回顾一下最开始介绍的t1表和t2表进行内连接的例子：

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

我们使用的其实是嵌套循环连接算法执行的连接查询，再把上面那个查询执行过程表拉下来给大家看一下：

查询驱动表t1后的结果集中有两条记录，嵌套循环连接算法需要对被驱动表查询2次：

• 当t1.m1 = 2时，去查询一遍t2表，对t2表的查询语句相当于：

  SELECT * FROM t2 WHERE t2.m2 = 2 AND t2.n2 < 'd';

• 当t1.m1 = 3时，再去查询一遍t2表，此时对t2表的查询语句相当于：

  SELECT * FROM t2 WHERE t2.m2 = 3 AND t2.n2 < 'd';

  可以看到，原来的t1.m1 = t2.m2这个涉及两个表的过滤条件在针对t2表做查询时关于t1表的条件就已经确定了，所以我们只需要单单优化对t2表的查询了，上述两个对t2表的查询语句中利用到的列是m2和n2列，我们可以：

​ 在m2列上建立索引，因为对m2列的条件是等值查找，比如t2.m2 = 2、t2.m2 = 3等，所以可能使用到ref的访问方法，假设使用ref的访问方法去执行对t2表的查询的话，需要回表之后再判断t2.n2 < d这个条件是否成立。

  这里有一个比较特殊的情况，就是假设m2列是t2表的主键或者唯一二级索引列，那么使用t2.m2 = 常数值这样的条件从t2表中查找记录的过程的代价就是常数级别的。我们知道在单表中使用主键值或者唯一二级索引列的值进行等值查找的方式称之为const，而设计MySQL的大佬把在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为：eq_ref。

​ 在n2列上建立索引，涉及到的条件是t2.n2 < 'd'，可能用到range的访问方法，假设使用range的访问方法对t2表的查询的话，需要回表之后再判断在m2列上的条件是否成立。

  假设m2和n2列上都存在索引的话，那么就需要从这两个里边儿挑一个代价更低的去执行对t2表的查询。当然，建立了索引不一定使用索引，只有在二级索引 + 回表的代价比全表扫描的代价更低时才会使用索引。

  另外，有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分，这种情况下即使不能使用eq_ref、ref、ref_or_null或者range这些访问方法执行对被驱动表的查询的话，也可以使用索引扫描，也就是index的访问方法来查询被驱动表。所以我们建议在真实工作中最好不要使用*作为查询列表，最好把真实用到的列作为查询列表。

基于块的嵌套循环连接（Block Nested-Loop Join）

  扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。现实生活中的表可不像t1、t2这种只有3条记录，成千上万条记录都是少的，几百万、几千万甚至几亿条记录的表到处都是。内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前面记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前面的记录从内存中释放掉。我们前面又说过，采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。

  当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。

​ 所以设计MySQL的大佬提出了一个join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。使用join buffer的过程如下图所示：

  最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。设计MySQL的大佬把这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。

  这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144字节（也就是256KB），最小可以设置为128字节。当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。

  另外需要注意的是，驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录呢。

总结

• 整体效率比较：INLJ>BNLJ>SNLJ

• 永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）（小的度量单位指的是表行数 * 每行大小）

• 为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）

• 增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）

• 减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多）

（5）hash join

从MySQL的8.0.20版本开始将废弃BNLJ,因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

• Nested Loop: 对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。
• Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小(相对较小)的表利用Join Key在内存中 建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行。
• 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
• 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分 就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O的性能。
• 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是Join的 重型升降机。Hash Join只能应用于等值连接(如WHERE A.COL1 = B.COL2),这是由Hash的特点决定的。

8、IN子查询优化

（1）小表驱动大表

IN和EXISTS

/* 优化原则：小表驱动大表，即小的数据集驱动大的数据集 */
/* IN适合B表比A表数据小的情况*/
SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`)
/* EXISTS适合B表比A表数据大的情况 */
SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id);

EXISTS语法

• 语法： SELECT....FROM tab WHERE EXISTS(subquery); 该语法可以理解为：
• 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（ true 或是false ）来决定主查询的数据结果是否得以保留。
• EXISTS(subquery) 子查询只返回 true 或者 false ，因此子查询中的 SELECT * 可以是SELECT 1 OR SELECT X ，它们并没有区别。
• EXISTS(subquery) 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。
• EXISTS(subquery) 子查询往往也可以用条件表达式，其他子查询或者 JOIN 替代，何种最优需要具体问题具体分析。

（2）将子查询转换成连接查询

​ MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

​ 子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。

原因：

• 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
• 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会受到一定的影响。
• 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

​ 在MySQL中，可以使用连接（JOIN）查询来替代子查询。 连接查询 不需要建立临时表，其 速度比子查询要快 ，如果查询中使用索引的话，性能就会更好。尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代。

9、ORDER BY排序优化

（1）ORDER BY字段索引原理

在MySQL中，支持两种排序方式，分别是FileSort和Index排序。

• Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
• FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O，效率较低。

优化建议：

• 可以在ORDER BY 子句中使用索引，目的是在 ORDER BY 子句避免使用 FileSort 排序 ，尽量使用 Index排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。
• 如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
• ORDER BY 满足两情况，会使用 Index 方式排序：

  • ORDER BY 语句使用索引最左前列。

  • 使用 WHERE 子句与 ORDER BY 子句条件列组合满足索引最左前列。

（2）ORDER BY优化

不加索引，不管有没有加limit，都会使用filesort

explain select sql_no_cache * from student order by age,classid;
explain select sql_no_cache * from student order by age,classid limit 10;

加limit限制索引生效，不加索引失效

-- 创建索引
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);

-- 不加limit限制,索引失效
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

-- 加limit限制，索引生效
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

order by 顺序错误，索引失效

-- 索引生效
EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;
EXPLAIN  SELECT * FROM student ORDER BY age,classid LIMIT 10;
EXPLAIN  SELECT * FROM student ORDER BY age,classid,name LIMIT 10;

-- 索引失效
EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10;
EXPLAIN  SELECT * FROM student ORDER BY classid,name LIMIT 10;
EXPLAIN  SELECT * FROM student ORDER BY age,name LIMIT 10;
EXPLAIN  SELECT * FROM student ORDER BY name,classid,age LIMIT 10;

order by排序不一致，索引失效

-- 排序不一致，索引失效
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;
-- 排序一致，索引有效
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

WHERE子句中使用索引的最左前缀定义为常量，则ORDER BY能使用索引

-- 索引生效
EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;
EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid,NAME; 
-- 索引失效
EXPLAIN  SELECT * FROM student WHERE classid=45 ORDER BY age;
-- 索引生效
EXPLAIN  SELECT * FROM student WHERE classid=45 ORDER BY age LIMIT 10;

案例分析，查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序

-- 先删除索引排除干扰
DROP INDEX idx_age_classid_name ON student;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;

-- 优化方案1，创建索引
CREATE INDEX idx_age_name ON student(age,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;

-- 优化方案2，尽量让where的过滤条件和排序使用上索引 建一个三个字段的组合索引
DROP INDEX idx_age_name ON student;
CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;

结论：

• 两个索引同时存在，mysql自动选择最优的方案。但是， 随着数据量的变化，选择的索引也会随之变化的 。
• 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

（3）FileSort有两种算法

如果实在是无法用上索引，只能FileSort，也要对FileSort进行优化，FileSort有两种算法：MySQL就要启动双路排序算法和单路排序算法。

双路排序算法

• MySQL4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 ORDER BY 列，対他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
• 从磁盘取排序字段，在buffer中进行排序，再从磁盘取其他字段。

单路排序算法

• 对磁盘进行两次扫描，IO是很耗时的，所以在MySQL4.1之后，出现了改进的算法，就是单路排序算法。
• 从磁盘读取查询需要的所有列，按照 ORDER BY 列在 buffer 対它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
• 但是单路排序算法有问题：如果 SortBuffer 缓冲区太小，导致从磁盘中读取所有的列不能完全保存在 SortBuffer 缓冲区中，这时候单路复用算法就会出现问题，反而性能不如双路复用算法。

优化策略：

• 增大 sort_buffer_size 参数的设置。

  SHOW VARIABLES LIKE '%sort_buffer_size%';
  

  不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程 (connection)的 1M-8M之间调整。MySQL5.7, InnoDB存储引擎默认值是 1048576字节，1MB。

• 增大 max_length_for_sort_data 参数的设置。

  SHOW VARIABLES LIKE '%max_length_for_sort_data%';  #默认 1024字节
  

  提高这个参数，会增加用单路排序算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。如果需要返回的列的总长度大于max」ength_for_sort_data，使用双路算法，否则使用单路算法。 1024・8192字节之间调整。

• Order by 时select * 是一个大忌。最好只Query需要的字段。

• 当Query的字段大小总和小于max_length_for_sort_data ，而且排序字段不是TEXT|BLOB类型时，会用改 进后的算法-一单路排序，否则用老算法一一多路排序。

• 两种算法的数据都有可能超出sort_buffer_size的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO。但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

（4）GORUP BY分组优化

• group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
• group by 先排序再分组，遵照索引建的最佳左前缀法则
• 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
• where效率高于having，能写在where限定的条件就不要写在having中
• 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
• 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

（5）排序分组总结

/* 创建a b c三个字段的索引 */
INDEX a_b_c(a,b,c)
/* 1.ORDER BY 能使用索引最左前缀 */
ORDER BY a;
ORDER BY a, b;
ORDER BY a, b, c;
ORDER BY a DESC, b DESC, c DESC;
/* 2.如果WHERE子句中使用索引的最左前缀定义为常量，则ORDER BY能使用索引 */
WHERE a = 'Ringo' ORDER BY b, c;
WHERE a = 'Ringo' AND b = 'Tangs' ORDER BY c;
WHERE a = 'Ringo' AND b > 2000 ORDER BY b, c;
/* 3.不能使用索引进行排序 */
ORDER BY a ASC, b DESC, c DESC; /* 排序不一致 */
WHERE g = const ORDER BY b, c;   /* 丢失a字段索引 */
WHERE a = const ORDER BY c;     /* 丢失b字段索引 */
WHERE a = const ORDER BY a, d;   /* d字段不是索引的一部分 */
WHERE a IN (...) ORDER BY b, c; /* 对于排序来说，多个相等条件(a=1 or a=2)也是范围查询*/

10、分页查询优化

（1）没有查询条件，没有排序

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

EXPLAIN SELECT * FROM student LIMIT 200000,10;

SELECT * FROM student LIMIT 200000,10;

（2）优化：在索引上完成排序分页操作

最后根据主键关联回原表查询所需要的其他列内容。如果排序的字段不是主键，给排序字段加索引。

问题1：如果不带排序条件，MySQL默认是什么排序？

通常认为是主键，但通过查资料发现并不一定，这里有个物理顺序和逻辑顺序的区别，如：删除原有数据后再插入复用旧id的数据，可能会由于存放在不同页上造成物理顺序与逻辑顺序不一致，此时可以通过优化表改善：optimize table table_name。

问题2：排序字段有索引就一定快吗？

1w的时候速度较快，换成查询100w之后的数据呢（深分页）？通过执行计划发现，并没有走索引，为什么没有走索引？因为mysql优化器发现这条sql查询行数超过一定比例（据说是30%，但测试下来并不完全是）就会自动转换为全表扫描，能不能强制走索引呢？可以的，加force index(idx)。但是强制索引效果也不一定快了。

EXPLAIN SELECT * FROM student ORDER BY id LIMIT 200000,10;

SELECT * FROM student ORDER BY id LIMIT 200000,10;

（3）优化：连表子查询

直接通过索引树就能拿到查询字段的值，所以快的原因是子查询方式减少了回表查询操作，进而减少了大量数据的回表IO，因此更高效。

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 200000,10) a 
WHERE t.id = a.id;

SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 200000,10) a WHERE t.id = a.id;

（4）优化：该方案适用于主键自增的表，可以转换成条件查询

EXPLAIN SELECT * FROM student WHERE id > 200000 LIMIT 10;

SELECT * FROM student WHERE id > 200000 LIMIT 10;

（5）分页优化总结

• 可以参考谷歌/百度搜索分页，每次只能跳转到当前页前后10页，也就是最多可以跳10页，要想达到深分页情况需要耐心。
• 对没有排序条件的分页查询增加主键排序
• 尽量对排序字段加索引
• 无论是否有索引，当分页页数达到一定阈值强制使用双路排序方式（通过子查询或代码发起两次查询）
• 适当调高sort_buffer_size大小
• 联合索引情况，避免跨列使用

11、count(*)查询优化

（1）执行计划分析

explain select count(*) from student;
explain select count(1) from student;
explain select count(id) from student;
explain select count(name) from student;

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.09 sec)

mysql> select count(1) from student;
+----------+
| count(1) |
+----------+
|  1000000 |
+----------+
1 row in set (0.11 sec)

mysql> select count(id) from student;
+-----------+
| count(id) |
+-----------+
|   1000000 |
+-----------+
1 row in set (0.10 sec)

mysql> select count(name) from student;
+-------------+
| count(name) |
+-------------+
|     1000000 |
+-------------+
1 row in set (0.25 sec)

id是主键，name是辅助索引中的字段，四个sql的执行计划一样，说明这四个sql执行效率应该差不多。

最终耗费的时间大约应该是：count(1)>count(name) ≈ count(*) > count(id) , 在mysql5.7及后续版本中，这4个count耗费的时间已经很相近了。

（2）优化查询速度

• myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被 mysql存储在磁盘上，查询不需要计算。
• 可以增加一张计数表，增删的时候更新行数。

四、MySQL配置优化

1、配置详解

[client]
port = 3306
socket = /tmp/mysql.sock
 
[mysqld]
###############################基础设置#####################################
#Mysql服务的唯一编号 每个mysql服务Id需唯一
server-id = 1
 
#服务端口号 默认3306
port = 3306
 
#mysql安装根目录
basedir = /opt/mysql
 
#mysql数据文件所在位置
datadir = /opt/mysql/data
 
#临时目录 比如load data infile会用到
tmpdir  = /tmp
 
#设置socke文件所在目录
socket  = /tmp/mysql.sock
 
#主要用于MyISAM存储引擎,如果多台服务器连接一个数据库则建议注释下面内容
skip-external-locking
 
#只能用IP地址检查客户端的登录，不用主机名
skip_name_resolve = 1
#数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character-set-server = utf8mb4
 
#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server = utf8mb4_general_ci
 
#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
 
#是否对sql语句大小写敏感，1表示不敏感
lower_case_table_names = 1
 
#最大连接数
max_connections = 400
#最大错误连接数
max_connect_errors = 1000
 
#TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true
 
#SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet = 128M
 
#MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
#MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout = 1800
wait_timeout = 1800
 
#内部内存临时表的最大值 ，设置成128M。
#比如大数据量的group by ,order by时可能用到临时表，
#超过了这个值将写入磁盘，系统IO压力增大
tmp_table_size = 134217728
max_heap_table_size = 134217728


 
##----------------------------用户进程分配到的内存设置BEGIN-----------------------------##
##每个session将会分配参数设置的内存大小
#用于表的顺序扫描，读出的数据暂存于read_buffer_size中，当buff满时或读完，将数据返回上层调用者
#一般在128kb ~ 256kb,用于MyISAM
#read_buffer_size = 131072
#用于表的随机读取，当按照一个非索引字段排序读取时会用到，
#一般在128kb ~ 256kb,用于MyISAM
#read_rnd_buffer_size = 262144
#order by或group by时用到
#建议先调整为2M，后期观察调整
sort_buffer_size = 2097152
#一般数据库中没什么大的事务，设成1~2M，默认32kb
binlog_cache_size = 524288
 
############################日  志 设置##########################################
#数据库错误日志文件
log_error = error.log
 
#慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = slow.log
#检查未使用到索引的sql
log_queries_not_using_indexes = 1
#针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数
log_throttle_queries_not_using_indexes = 5
#作为从库时生效,从库复制中如何有慢sql也将被记录
log_slow_slave_statements = 1
#慢查询执行的秒数，必须达到此值可被记录
long_query_time = 2
#检索的行数必须达到此值才可被记为慢查询
min_examined_row_limit = 100
 
#mysql binlog日志文件保存的过期时间，过期后自动删除
expire_logs_days = 5
 
############################主从复制 设置########################################
#开启mysql binlog功能
log-bin=mysql-bin
#binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW

#作为从库时生效,想进行级联复制，则需要此参数
log_slave_updates
 
#作为从库时生效,中继日志relay-log可以自我修复
relay_log_recovery = 1
 
#作为从库时生效,主从复制时忽略的错误
slave_skip_errors = ddl_exist_errors
 
##---redo log和binlog的关系设置BEGIN---##
#(步骤1) prepare dml相关的SQL操作，然后将redo log buff中的缓存持久化到磁盘
#(步骤2)如果前面prepare成功，那么再继续将事务日志持久化到binlog
#(步骤3)如果前面成功，那么在redo log里面写上一个commit记录
#当innodb_flush_log_at_trx_commit和sync_binlog都为1时是最安全的，
#在mysqld服务崩溃或者服务器主机crash的情况下，binary log只有可能丢失最多一个语句或者一个事务。
#但是都设置为1时会导致频繁的io操作，因此该模式也是最慢的一种方式。
#当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
#当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。
#commit事务时,控制redo log buff持久化磁盘的模式 默认为1
innodb_flush_log_at_trx_commit = 2
#commit事务时,控制写入mysql binlog日志的模式 默认为 0
#innodb_flush_log_at_trx_commit和sync_binlog都为1时，mysql最为安全但性能上压力也是最大
sync_binlog = 1
##---redo log 和 binlog的关系设置END---##
 
############################Innodb设置##########################################
#数据块的单位8k，默认是16k，16kCPU压力稍小，8k对select的吞吐量大
#innodb_page_size的参数值也影响最大索引长度，8k比16k的最大索引长度小
#innodb_page_size = 8192
#一般设置物理存储的60% ~ 70%
innodb_buffer_pool_size = 1G
 
#5.7.6之后默认16M
#innodb_log_buffer_size = 16777216
#该参数针对unix、linux，window上直接注释该参数.默认值为NULL
#O_DIRECT减少操作系统级别VFS的缓存和Innodb本身的buffer缓存之间的冲突
innodb_flush_method = O_DIRECT
 
#此格式支持压缩, 5.7.7之后为默认值
innodb_file_format = Barracuda
 
#CPU多核处理能力设置，假设CPU是2颗4核的，设置如下
#读多，写少可以设成2:6的比例
innodb_write_io_threads = 4
innodb_read_io_threads = 4
 
#提高刷新脏页数量和合并插入数量，改善磁盘I/O处理能力
#默认值200（单位：页）
#可根据磁盘近期的IOPS确定该值
innodb_io_capacity = 500
 
#为了获取被锁定的资源最大等待时间，默认50秒，超过该时间会报如下错误:
# ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
innodb_lock_wait_timeout = 30
 
#调整buffer pool中最近使用的页读取并dump的百分比,通过设置该参数可以减少转储的page数
innodb_buffer_pool_dump_pct = 40
 
#设置redoLog文件所在目录, redoLog记录事务具体操作内容
innodb_log_group_home_dir = /opt/mysql/redolog/
 
#设置undoLog文件所在目录, undoLog用于事务回滚操作
innodb_undo_directory = /opt/mysql/undolog/
 
#在innodb_log_group_home_dir中的redoLog文件数, redoLog文件内容是循环覆盖写入。
innodb_log_files_in_group = 3
 
#MySql5.7官方建议尽量设置的大些，可以接近innodb_buffer_pool_size的大小
#之前设置该值较大时可能导致mysql宕机恢复时间过长，现在恢复已经加快很多了
#该值减少脏数据刷新到磁盘的频次
#最大值innodb_log_file_size * innodb_log_files_in_group <= 512GB,单文件<=256GB
innodb_log_file_size = 1024M
 
#设置undoLog文件所占空间可以回收
#5.7之前的MySql的undoLog文件一直增大无法回收
innodb_undo_log_truncate = 1
innodb_undo_tablespaces = 3
innodb_undo_logs = 128
 
#5.7.7默认开启该参数 控制单列索引长度最大达到3072
#innodb_large_prefix = 1
 
#5.7.8默认为4个, Inodb后台清理工作的线程数
#innodb_purge_threads = 4
 
#通过设置配置参数innodb_thread_concurrency来限制并发线程的数量，
#一旦执行线程的数量达到这个限制，额外的线程在被放置到对队列中之前，会睡眠数微秒，
#可以通过设定参数innodb_thread_sleep_delay来配置睡眠时间
#该值默认为0,在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议:
#(1)如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
#(2)如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128,
###并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数
#innodb_thread_concurrency = 0
############################其他内容 设置##########################################
[mysqldump]
quick
max_allowed_packet = 128M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 256k
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
#增加每个进程的可打开文件数量.
open-files-limit = 28192

2、配置优化

（1）innodb_buffer_pool_size

这是你安装完InnoDB数据库后第一个应该设置的选项。缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。理论上可以设置为内存的四分之三，典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。

Innodb_buffer_pool_pages_free这个值如果是0，表示用光了设置的buffer_pool_size

mysql> show global status like 'innodb_buffer_pool_pages_%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_pages_data    | 7156  |
| Innodb_buffer_pool_pages_dirty   | 0     |
| Innodb_buffer_pool_pages_flushed | 10778 |
| Innodb_buffer_pool_pages_free    | 1017  |
| Innodb_buffer_pool_pages_misc    | 19    |
| Innodb_buffer_pool_pages_total   | 8192  |
+----------------------------------+-------+

（2）innodb_log_file_size

这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到MySQL 5.1，它都难于调整，因为一方面你想让它更大来提高性能，另一方面你想让它更小来使得崩溃后更快恢复。幸运的是从MySQL 5.5之后，崩溃恢复的性能的到了很大提升，这样你就可以同时拥有较高的写入性能和崩溃恢复性能了。一直到MySQL 5.5，redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高。

一般 innodb_log_file_size 设置成 innodb_buffer_pool_size * 0.25

（3）max_connections

如果你经常看到‘Too many connections’错误，是因为max_connections的值太低了。这非常常见因为应用程序没有正确的关闭数据库连接，你需要比默认的151连接数更大的值。max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。

对于innodb_buffer_pool_size 和 innodb_log_file_size 有时候以G为单位，设置成小数，不成功，比如2.5G，mysql可能无法启动，这个时候可以用M为单位，比如：2500M。

五、表分区

1、什么是数据库分区

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面 （可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。

如果一张表的数据量太大的话，那么myd、myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这 一张表对应的三个文件，分割成许多个小块，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。

如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

2、数据的两种分割方式

（1）横向分区（数据库的功能，可以建表的时候设置，可以细分多种类型）

就是横着来分区了，举例来说明一下，假如有100W条数据，分成十份，前10W条数据放到第一个分区，第二个10W条数据放到第二个分区，依此类推，也就是把表分成了十分。取出一条数据的时候，这条数据包含了表结构中的所有字段，也就是说横向分区，并没有改变表的结构。

（2）纵向分区（表的设计，不是数据库的功能）

什就是竖着来分区了，举例来说明，在设计用户表的时候，开始的时候没有考虑好，而把个人的所有信息都放到了一张表里面去，这样这个表里面就会有比较大的字段，如个人简介，而这些简介呢，也许不会有好多人去看，所以等到有人要看的时候，在去查找，分表的时候，可以把这样的大字段，分开来。

3、表分区-range

按照range分区，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。

-- 创建表和分区
create table if not exists `range_part` (
   `id` int not null auto_increment comment '用户id',
	 `name` varchar(50) not null default '' comment '名称',
	 `sex` int not null default '0' comment '0为男，1为女',
	 primary key (`id`)
	 )engine=innodb default charset=utf8 auto_increment=1
partition by range (id) (
   partition p0 values less than (3),
	 partition p1 values less than (6),
	 partition p2 values less than (9),
	 partition p3 values less than (12),
	 partition p4 values less than maxvalue
);

-- 插入表数据
insert into `range_part` (`name` ,`sex`) values
('tank', '0') ,('zhang',1),('ying',1),('张',1),
('映',0),('test1',1),('tank2',1),('tank1',1),
('test2',1),('test3',1),('test4',1),('test5',1),
('tank3',1),('tank4',1),('tank5',1),('tank6',1),
('tank7',1),('tank8',1),('tank9',1),('tank10',1),
('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1);

分区的文件如下：

-- 查看表分区信息
select * from information_schema.partitions where table_schema='test' and
table_name='range_part';
-- 查看表数据行数
mysql> select count(id) as count from range_part;
+-------+
| count |
+-------+
|    25 |
+-------+
## 删除第四个分区
alter table range_part drop partition p4;
## 查看表分区信息
select * from information_schema.partitions where table_schema='test' and
table_name='range_part';
## 查看数据【注意：存放在分区里面的数据丢失了，p4分区里面有14条数据，其他分区只有11条数据。】
mysql> select count(id) as count from range_part;
+-------+
| count |
+-------+
|    11 |
+-------+
## 可以对现有表进行分区,并且会按規则自动的将表中的数据分配相应的分区中
alter table range_part partition by range(id) (
   partition p1 values less than (1),
   partition p2 values less than (5),
   partition p3 values less than maxvalue
);
-- 查看表数据行数 没有变更
mysql> select count(id) as count from range_part;
+-------+
| count |
+-------+
|    11 |
+-------+

-- 查看具体分区的数据
mysql> select *  from range_part partition(p2);
+----+-------+-----+
| id | name  | sex |
+----+-------+-----+
|  1 | tank  |   0 |
|  2 | zhang |   1 |
|  3 | ying  |   1 |
|  4 | 张    |   1 |
+----+-------+-----+

新的分区文件如下：

4、表分区-list

创建list分区时，如果有主銉的话，分区时主键必须在其中，不然就会报错。如果没有主键，分区就能创建成功，一般情况下，一张表肯定要有一个主键，这算是一个list分区的局限性。

create table if not exists `list_part` (
     `id` int(11) not null comment '用户id',
	 `province_id` int(2) not null default 0 comment '省',
	 `name` varchar(50) not null default '' comment '名称',
	 `sex` int(1) not null default '0' comment '0为男，1为女'
) engine=innodb default charset=utf8
partition by list (province_id) (
         partition p0 values in (1,2,3),
		 partition p1 values in (4,5,6),
		 partition p2 values in (7,8,9),
		 partition p3 values in (10,11)
);

分区文件如下：

5、表分区-hash

hash分区主要用来确保数据在预先确定数目的分区中平均分布，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

## 创建分区
create table if not exists `hash_part`(
   `id` int not null auto_increment comment '评论id', 
	 `comment` varchar(1000) not null default '' comment '评论',
	 `ip` varchar(25) not null default '' comment '来源ip',
	 primary key (`id`)
) engine=innodb default charset=utf8 auto_increment=1
partition by hash(id)
partitions 3;

6、表分区-key

KEY分区和HASH分区相似，但是KEY分区支持除text和BLOB之外的所有数据类型的分区，而HASH分区只支持数字分区，KEY分区不允许使用用户自定义的表达式进行分区，KEY分区使用系统提供的HASH函数进行分区。当表中存在主键或者唯一键时，如果创建key分区时没有指定字段系统默认会首选主键列作为分区字列,如果不存在主键列会选择非空唯一键列作为分区列,注意唯一列作为分区列唯一列不能为null。

CREATE TABLE key_part (
   id INT ,
   var CHAR(32) 
)
PARTITION BY KEY(var)
PARTITIONS 10;

7、表分区-linear key

同样key分区也存在线性KEY分区，概念和线性HASH分区一样。

CREATE TABLE keyline_part (
   id INT NOT NULL,
	 var CHAR(5))
PARTITION BY LINEAR KEY (var)
PARTITIONS 3;

8、分区管理

-- 删除两个分区
ALTER TABLE tb_key COALESCE PARTITION 2;
-- 增加三个分区
ALTER TABLE tb_key add PARTITION partitions 3;
-- 移除分区，使用remove移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除
ALTER TABLE tablename REMOVE PARTITIONING;

9、分区-range多字段

多字段的分区键比较是基于数组的比较。它先用插入的数据的第一个字段值和分区的第一个值进行比较，如果插入的第一个值小于分区的第一个值那么就不需要比较第二个值就属于该分区；如果第一个值等于分区的第一个值，开始比较第二个值同样如果第二个值小于分区的第二个值那么就属于该分区。多列分区第一列的分区值一定是顺序增长的，不能出现交叉值，第二列的值随便，否则报错。

CREATE TABLE range_part_cs (
   a INT,
	 b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
     PARTITION p0 VALUES LESS THAN (5,10),
		 PARTITION p1 VALUES LESS THAN (10,20),
		 PARTITION p2 VALUES LESS THAN (15,30),
		 PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

-- 插入数据
insert into range_part_cs(a,b)values(1,20),(10,15),(10,30);

第一组值：(1,20);1<5所以不需要再比较20了，该记录属于p0分区。

第二组值:(10,15)，10>5,10=10且15<20，所以该记录属于P1分区

第三组值:(10,30),10=10但是30>20，所以它不属于p1,它满足10<15所以它属于p2

10、分区-list多字段

由于分区是组合字段，filtered只有50%，对于组合分区索引也最好是建组合索引，其实如果能通过id字段刷选出数据，单独建id字段的索引也是有效果的，但是组合索引的效果是最好的，其实和非分区键索引的概念差不多。

CREATE TABLE list_part_cs (
   id INT NOT NULL, 
	 hired DATETIME NOT NULL
)
PARTITION BY LIST COLUMNS(id,hired) 
(
   PARTITION a VALUES IN ( (1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00') 
),
   PARTITION b VALUES IN ( (2,'1992-01-01 10:00:00') ),
	 PARTITION c VALUES IN ( (3,'1993-01-01 10:00:00') ),
	 PARTITION d VALUES IN ( (4,'1994-01-01 10:00:00') )
);