2017- 我必须得告诉大家的MySQL 优化原理
我必须得告诉大家的MySQL 优化原理
说起
如果能在头脑中构建一幅
MySQL 查询优化
我们总是希望
客户端/ 服务端通信协议
与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用
查询缓存
在解析一个查询语句前，如果查询缓存是打开的，那么
如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、
既然是缓存，就会失效，那查询缓存何时失效呢？
- 任何的查询语句在开始之前都必须经过检查，即使这条
SQL 语句永远不会命中缓存 - 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗
基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：
- 用多个小表代替一个大表，注意不要过度设计
- 批量插入代替循环单条插入
- 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
- 可以通过
SQL_CACHE 和SQL_NO_CACHE 来控制某个查询语句是否需要进行缓存
最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将
语法解析和预处理
查询优化
经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。
mysql> select * from t_message limit 10;
...省略结果集
mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 6391.799000 |
+-----------------+-------------+
示例中的结果表示优化器认为大概需要做
- 重新定义表的关联顺序（多张表关联查询时，并不一定按照
SQL 中指定的顺序进行，但有一些技巧可以指定关联顺序） - 优化
MIN() 和MAX() 函数（找某列的最小值，如果该列有索引，只需要查找B+Tree 索引最左端，反之则可以找到最大值，具体原理见下文） - 提前终止查询（比如：使用
Limit 时，查找到满足数量的结果集后会立即终止查询） - 优化排序（在老版本
MySQL 会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O 密集型应用，效率会高很多） - 随着
MySQL 的不断发展，优化器使用的优化策略也在不断的进化，这里仅仅介绍几个非常常用且容易理解的优化策略，其他的优化策略，大家自行查阅吧。
查询执行引擎
在完成解析和优化阶段以后，
返回结果给客户端
查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，
结果集返回客户端是一个增量且逐步返回的过程。有可能
- 客户端向
MySQL 服务器发送一条查询请求 - 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
- 服务器进行
SQL 解析、预处理、再由优化器生成对应的执行计划 MySQL 根据执行计划，调用存储引擎的API 来执行查询- 将结果返回给客户端，同时缓存查询结果
性能优化建议
看了这么多，你可能会期待给出一些优化手段，是的，下面会从
Scheme 设计与数据类型优化
选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的
这里总结几个可能容易理解错误的技巧：
- 通常来说把可为
NULL 的列改为NOT NULL 不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL 。 - 对整数类型指定宽度，比如
INT(11) ，没有任何卵用。INT 使用32 位（4 个字节）存储空间，那么它的表示范围已经确定，所以INT(1) 和INT(20) 对于存储和计算是相同的。 UNSIGNED 表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT 存储范围是-128 ~ 127 ，而UNSIGNED TINYINT 存储的范围却是0 - 255 。- 通常来讲，没有太大的必要使用
DECIMAL 数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT 。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT 存储。这样可以避免浮点数计算不准确和DECIMAL 精确计算代价高的问题。 TIMESTAMP 使用4 个字节存储空间，DATETIME 使用8 个字节存储空间。因而，TIMESTAMP 只能表示1970 - 2038 年，比DATETIME 表示的范围小得多，而且TIMESTAMP 的值因时区不同而不同。- 大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用
ALTER TABLE （如果只只是在列表末尾追加元素，不需要重建表） 。 schema 的列不要太多。原因是存储引擎的API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU 占用过高。- 大表
ALTER TABLE 非常耗时，MySQL 执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。当然有一些奇淫技巧可以解决这个问题，有兴趣可自行查阅。
创建高性能索引
索引是提高
索引相关的数据结构和算法
通常我们所说的索引是指
在介绍
由于二叉查找树可以任意构造，同样的值，可以构造出如图 ② 的二叉查找树，显然这棵二叉树的查询效率和顺序查找差不多。若想二叉查找数的查询性能最高，需要这棵二叉查找树是平衡的，也即平衡二叉树（
通过一次左旋操作就将插入后的树重新变为平衡二叉树是最简单的情况了，实际应用场景中可能需要旋转多次。至此我们可以考虑一个问题，平衡二叉树的查找效率还不错，实现也非常简单，相应的维护成本还能接受，为什么
一种行之有效的解决方法是减少树的深度，将二叉树变为
怎么理解这两个特征？
页是计算机管理存储器的逻辑块，硬件及
OS 往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（许多OS 中，页的大小通常为4K ） 。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。
最后简单了解下
接着插入下一个节点
最后插入一个节点
拆分后最终形成了这样一颗树。
通过旋转操作可以最大限度的减少页分裂，从而减少索引维护过程中的磁盘的
索引高性能策略
通过上文，相信你对
CREATE TABLE People(
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum(`m`,`f`) not null,t
key(last_name,first_name,dob)
);
对于表中每一行数据，索引中包含了
1、
“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。比如：
select * from where id + 1 = 5
我们很容易看出其等价于
2、前缀索引
如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率。
3、多列索引和索引顺序
在多数情况下，在多个列上建立独立的索引并不能提高查询性能。理由非常简单，
select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1
老版本的
select film_id,actor_id from film_actor where actor_id = 1
union all
select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1
当出现多个索引做相交操作时（多个
- 当出现多个索引做联合操作时（多个
OR 条件） ，对结果集的合并、排序等操作需要耗费大量的CPU 和内存资源，特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高。所以这种情况下还不如走全表扫描。 - 因此
explain 时如果发现有索引合并（Extra 字段出现Using union ） ，应该好好检查一下查询和表结构是不是已经是最优的，如果查询和表都没有问题，那只能说明索引建的非常糟糕，应当慎重考虑索引是否合适，有可能一个包含所有相关列的多列索引更适合。
前面我们提到过索引如何组织数据存储的，从图中可以看到多列索引时，索引的顺序对于查询是至关重要的，很明显应该把选择性更高的字段放到索引的前面，这样通过第一个字段就可以过滤掉大多数不符合条件的数据。索引选择性是指不重复的索引值和数据表的总记录数的比值，选择性越高查询效率越高，因为选择性越高的索引可以让
理解索引选择性的概念后，就不难确定哪个字段的选择性较高了，查一下就知道了，比如：
SELECT * FROM payment where staff_id = 2 and customer_id = 584
是应该创建
select count(distinct staff_id)/count(*) as staff_id_selectivity,
count(distinct customer_id)/count(*) as customer_id_selectivity,
count(*) from payment
多数情况下使用这个原则没有任何问题，但仍然注意你的数据中是否存在一些特殊情况。举个简单的例子，比如要查询某个用户组下有过交易的用户信息：
select user_id from trade where user_group_id = 1 and trade_amount > 0
4、避免多个范围条件
实际开发中，我们会经常使用多个范围条件，比如想查询某个时间段内登录过的用户：
select user.* from user where login_time > '2017-04-01' and age between 18 and 30;
这个查询有一个问题：它有两个范围条件，
5、覆盖索引
如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：
- 索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量
- 索引是有按照列值顺序存储的，对于
I/O 密集型的范围查询要比随机从磁盘读取每一行数据的IO 要少的多
6、使用索引扫描来排序
在设计索引时，如果一个索引既能够满足排序，有满足查询，是最好的。只有当索引的列顺序和
-- 最左列为常数，索引：(date,staff_id,customer_id)
select staff_id,customer_id from demo where date = '2015-06-01' order by staff_id,customer_id
7、冗余和重复索引
冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。比如有一个索引
8、删除长期未使用的索引
定期删除一些长时间未使用过的索引是一个非常好的习惯。关于索引这个话题打算就此打住，最后要说一句，索引并不总是最好的工具，只有当索引帮助提高查询速度带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，简单的全表扫描更高效。对于中到大型的表，索引就非常有效。对于超大型的表，建立和维护索引的代价随之增长，这时候其他技术也许更有效，比如分区表。最后的最后，
特定类型查询优化
优化COUNT() 查询
有时候某些业务场景并不需要完全精确的
优化关联查询
在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用
-
确保
ON 和USING 字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A 和表B 用列c 关联的时候，如果优化器关联的顺序是A 、B，那么就不需要在A 表的对应列上创建索引。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引（具体原因下文分析） 。 -
确保任何的
GROUP BY 和ORDER BY 中的表达式只涉及到一个表中的列，这样MySQL 才有可能使用索引来优化。
要理解优化关联查询的第一个技巧，就需要理解
SELECT A.xx,B.yy
FROM A INNER JOIN B USING(c)
WHERE A.xx IN (5,6)
假设
outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);
outer_row = outer_iterator.next;
while(outer_row) {
inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;
inner_row = inner_iterator.next;
while(inner_row) {
output[inner_row.yy,outer_row.xx];
inner_row = inner_iterator.next;
}
outer_row = outer_iterator.next;
}
可以看到，最外层的查询是根据
优化LIMIT 分页
当需要分页操作时，通常会使用
优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时，这样做的效率会提升非常大。考虑下面的查询：
SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;
如果这张表非常大，那么这个查询最好改成下面的样子：
SELECT film.film_id,film.description
FROM film INNER JOIN (
SELECT film_id FROM film ORDER BY title LIMIT 50,5
) AS tmp USING(film_id);
这里的延迟关联将大大提升查询效率，让
SELECT id FROM t LIMIT 10000, 10;
-- 改为：
SELECT id FROM t WHERE id > 10000 LIMIT 10;
其他优化的办法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表中只包含主键列和需要做排序的列。
优化UNION
除非确实需要服务器去重，否则就一定要使用
分区表
合理的使用索引可以极大提升
在业务层分表大大增加了编码的复杂程度，而且处理数据库的相关代码会大量散落在应用各处，维护困难。那是否可以将分表的逻辑抽象出来，统一处理，这样业务层就不用关心底层是否分表，只需要专注在业务即可。答案当然是肯定的，目前有非常多的数据库中间件都可以屏蔽分表后的细节，让业务层像查询单表一样查询分表后的数据。如果再将抽象的逻辑下移到数据库的服务层，就是我们今天要讲的分区表。分区可以看作是从技术层面解决大数据问题的有效方法，简单的理解，可以认为是
更好的理解分区表，我们从一个示例入手：一张订单表，数据量大概有
首先可以肯定的是，因为数据量巨大，肯定不能走全表扫描。使用索引的话，你会发现数据并不是按照想要的方式聚集，而且会产生大量的碎片，最终会导致一个查询产生成千上万的随机
对表分区，可以在创建表时，使用如下语句：
CREATE TABLE sales {
order_date DATETIME NOT NULL
-- other columns
} ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
PARTITION p_2014 VALUES LESS THAN (2014),
PARTITION p_2015 VALUES LESS THAN (2015)
PARTITION p_2016 VALUES LESS THAN (2016)
PARTITION p_2017 VALUES LESS THAN (2017)
PARTITION p_catchall VALUES LESS THAN MAXVALUE
)
分区子句中可以使用各种函数，但表达式的返回值必须是一个确定的整数，且不能是一个常数。
接下来简单看下分区表上的各种操作逻辑：
- SELECT：当查询一个分区表时，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后在调用对应的存储引擎接口访问各个分区的数据
- INSERT：当插入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应的底层表，
DELETE 操作与其类似 - UPDATE：当更新一条数据时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，然后取出数据并更新，再判断更新后的数据应该存放到哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作
有些操作是支持条件过滤的。例如，当删除一条记录时，
在使用分区表时，为了保证大数据量的可扩展性，一般有两个策略：
- 全量扫描数据，不用索引。即只要能够根据
WHERE 条件将需要查询的数据限制在少数分区中，效率是不错的 - 索引数据，分离热点。如果数据有明显的“热点”，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独存放在一个分区中，让这个分区的数据能够有机会都缓存在内存中。这样查询就可以值访问一个很小的分区表，能够使用索引，也能够有效的利用缓存。
分区表的优点是优化器可以根据分区函数来过滤一些分区，但很重要的一点是要在
上面两个分区策略基于两个非常重要的前提：查询都能够过滤掉很多额外的分区、分区本身并不会带来很多额外的代价。而这两个前提在某些场景下是有问题的，比如：
1、
假设按照
在
2、分区列和索引列不匹配
当分区列和索引列不匹配时，可能会导致查询无法进行分区过滤，除非每个查询条件中都包含分区列。假设在列
3、选择分区的成本可能很高
分区有很多种类型，不同类型的分区实现方式也不同，所以它们的性能也不尽相同，尤其是范围分区，在确认这一行属于哪个分区时会扫描所有的分区定义，这样的线性扫描效率并不高，所以随着分区数的增长，成本会越来越高。特别是在批量插入数据时，由于每条记录在插入前，都需要确认其属于哪一个分区，如果分区数太大，会造成插入性能的急剧下降。因此有必要限制分区数量，但也不用太过担心，对于大多数系统，
4、打开并锁住所有底层表的成本在某些时候会很高
前面说过，打开并锁住所有底层表并不会对性能有太大的影响，但在某些情况下，比如只需要查询主键，那么锁住的成本相对于主键的查询来说，成本就略高。
5、维护分区的成本可能会很高
新增和删除分区的速度都很快，但是修改分区会造成数据的复制，这与
分区表还有一些其他限制，比如所有的底层表必须使用相同的存储引擎，某些存储引擎也不支持分区。分区一般应用于一台服务器上，但一台服务器的物理资源总是有限的，当数据达到这个极限时，即使分区，性能也可能会很低，所以这个时候分库是必须的。但不管是分区、分库还是分表，它们的思想都是一样的，大家可以好好体会下。
视图
对于一些关联表的复杂查询，使用视图有时候会大大简化问题，因此在许多场合下都可以看到视图的身影，但视图真如我们所想那样简单吗？它和直接使用
视图本身是一个虚拟表，不存放任何数据，查询视图的数据集由其他表生成。
// 视图的作用是查询未支付订单
CREATE VIEW unpay_order AS
SELECT * FROM sales WHERE status = 'new'
WITH CHECK OPTION; // 其作用下文会讲
现要从未支付订单中查询购买者为
-- 查询购买者为csc且未支付的订单
SELECT order_id,order_amount,buyer FROM unpay_order WHERE buyer = 'csc';
使用临时表来模拟视图：
CREATE TEMPORARY TABLE tmp_order_unpay AS SELECT * FROM sales WHERE status = 'new';
SELECT order_id,order_amount,buyer FROM tmp_order_unpay WHERE buyer = 'csc';
使用合并算法将视图定义的
SELECT order_id,order_amount,buyer FROM sales WHERE status = 'new' AND buyer = 'csc';
首先视图可以简化应用上层的操作，让应用更专注于其所关心的数据。其次，视图能够对敏感数据提供安全保护，比如：对不同的用户定义不同的视图，可以使敏感数据不出现在不应该看到这些数据的用户视图上；也可以使用视图实现基于列的权限控制，而不需要真正的在数据库中创建列权限。再者，视图可以方便系统运维，比如：在重构
基于此，使用视图其实更多的是基于业务或者维护成本上的考虑，其本身并不会对性能提升有多大作用（注意：此处只是基于
// 视图的作用是统计每日支出金额，DATE('2017-06-15 12:00:23') = 2017-06-15
CREATE VIEW cost_per_day AS
SELECT DATE(create_time) AS date,SUM(cost) AS cost FROM costs GROUP BY date;
现要统计每日的收入与支出，有类似于上面的收入表，可以使用如下
SELECT c.date,c.cost,s.amount
FROM cost_per_day AS c
JOIN sale_per_day AS s USING(date)
WHERE date BETWEEN '2017-06-01' AND '2017-06-30'
这个查询中，
当然这个示例中的临时表数据不会太大，毕竟日期的数量不会太多，但仍然要考虑生成临时表的性能（如果
我们很少会在实际业务场景中去更新视图，因此印象中，视图是不能更新的。但实际上，在某些情况下，视图是可以更新的。可更新视图是指通过更新这个视图来更新视图涉及的相关表，只要指定了合适的条件，就可以更新、删除甚至是向视图中插入数据。通过上文的了解，不难推断出：更新视图的实质就是更新视图关联的表，将创建视图的
视图还有一个容易造成误解的地方
相比于其它关系型数据库的视图，
存储过程与触发器
回到第二个问题，有非常多的人在分享时都会抛出这样一个观点：尽可能不要使用存储过程，存储过程非常不容易维护，也会增加使用成本，应该把业务逻辑放到客户端。既然客户端都能干这些事，那为什么还要存储过程？
如果有深入了解过存储过程，就会发现存储过程并没有大家描述的那么不堪。我曾经经历过一些重度使用存储过程的产品，依赖到什么程度呢？就这么说吧，上层的应用基本上只处理交互与动效的逻辑，所有的业务逻辑，甚至是参数的校验均在存储过程中实现。曾经有出现过一个超大的存储过程，其文件大小达到惊人的
其成功的原因在一定程度上得益于存储过程的优点，由于业务层代码没有任何侵入业务的代码，在不改变前端展示效果的同时，可以非常快速的修复
当然存储过程还有其他的优点，比如，可以非常方便的加密存储过程代码，而不用担心应用部署到私有环境造成源代码泄露、可以像调试其他应用程序一样调试存储过程、可以设定存储过程的使用权限来保证数据安全等等。一切都非常美好，但我们的产品是基于
除此之外，
- 优化器无法评估存储过程的执行成本
- 每个连接都有独立的存储过程执行计划缓存，如果有多个连接需要调用同一个存储过程，将会浪费缓存空间来缓存相同的执行计划
因此，在
和存储过程类似的，还有触发器，触发器可以让你在执行
首先对一张表的每一个事件，最多只能定义一个触发器，而且它只支持“基于行的触发”，也就是触发器始终是针对一条记录的，而不是针对整个
虽然触发器有这么多限制，但它仍有适用的场景，比如，当你需要记录
外键约束
目前在大多数互联网项目，特别是在大数据的场景下，已经不建议使用外键了，主要是考虑到外键的使用成本：
- 外键通常要求每次修改数据时都要在另外一张表中执行一次查找操作。在
InnoDB 存储引擎中会强制外键使用索引，但在大数据的情况下，仍然不能忽略外键检查带来的开销，特别是当外键的选择性很低时，会导致一个非常大且选择性低的索引。 - 如果向子表中插入一条记录，外键约束会让
InnoDB 检查对应的父表的记录，也就需要对父表对应记录进行加锁操作，来确保这条记录不会在这个事务完成之时就被删除了。这会导致额外的锁等待，甚至会导致一些死锁。 - 高并发场景下，数据库很容易成为性能瓶颈，自然而然的就希望数据库可以水平扩展，这时就需要把数据的一致性控制放到应用层，也就是让应用服务器可以承担压力，这种情况下，数据库层面就不能使用外键。
因此，当不用过多考虑数据库的性问题时，比如一些内部项目或传统行业项目（其使用人数有限，而且数据量一般不会太大
绑定变量
可能大家看到“绑定变量”这个词时，会有一点陌生，换个说法可能会熟悉一些：prepared statement。绑定变量的
SELECT order_no, order_amount FROM sales WHERE order_status = ? and buyer = ?
为什么要使用绑定变量？总所周知的原因是可以预先编译，减少
当创建一个绑定变量
- 服务器只需要解析一次
SQL 语句 - 服务器某些优化器的优化工作也只需要做一次，因为
MySQL 会缓存部分执行计划 - 通信中仅仅发送的是参数，而不是整个语句，网络开销也会更小，而且以二进制发送参数和句柄要比发送
ASCII 文本的效率更高
需要注意的是，
- 客户端模拟的绑定变量：客户端的驱动程序接收一个带参数的
SQL ，再将参数的值带入其中，最后将完整的查询发送到服务器。 - 服务器绑定变量：客户端使用特殊的二进制协议将带参数的
SQL 语句发送到服务器端，然后使用二进制协议将具体的参数值发送给服务器并执行。 SQL 接口的绑定变量：客户端先发送一个带参数的SQL 语句到服务器端，这类似于使用prepared 的SQL 语句，然后发送设置的参数，最后在发送execute 指令来执行SQL ，所有这些都是用普通的文本传输协议。
比如某些不支持预编译的
用户自定义函数
所谓能力越大，责任也就越大，
这里有一个简单的示例来展示如何创建
// 1、首先使用c语言实现功能
// 2、编译
// 这里省略第1、2步，实现并编译成.so
// 3、使用SQL创建函数
drop function json_array;
create function json_array returns string soname 'lib_mysqludf_json.so';
// 4、使用函数
select json_array(
customer_id
, first_name
, last_name
, last_update
) as customer
from customer
where customer_id =1;
-- 5、得到的结果如下：
+------------------------------------------+
| customer |
+------------------------------------------+
| [1,"MARY","SMITH","2006-02-15 04:57:20"] |
+------------------------------------------+
字符集
关于字符集大多数人的第一印象可能就是：数据库字符集尽量使用
字符集是指一种从二进制编码到某类字符符号的映射，可以参考如何使用一个字节来表示英文字母。校对规则是指一组用于某个字符集的排序规则，即采用何种规则对某类字符进行排序。
一种字符集可能对应多种校对规则，且都有一个默认校对规则，那在
当客户端与服务器通信时，它们可以使用不同的字符集，这时候服务器将进行必要的转换工作。当客户端向服务器发送请求时，数据以
那如何选择字符集？
在考虑使用何种字符集时，最主要的衡量因素是存储的内容，在能够满足存储内容的前提下，尽量使用较小的字符集。因为更小的字符集意味着更少空间占用、以及更高的网络传输效率，也间接提高了系统的性能。如果存储的内容是英文字符等拉丁语系字符的话，那么使用默认的
除了字符集，校对规则也是我们需要考虑的问题。对于校对规则，一般来说只需要考虑是否以大小写敏感的方式比较字符串或者是否用字符串编码的二进制来比较大小，其对应的校对规则的后缀分别是_cs、_ci和_bin。大小写敏感和二进制校对规则的不同之处在于，二进制校对规则直接使用字符的字节进行比较，而大小写敏感的校对规则在多字节字符集时，如德语，有更复杂的比较规则。举个简单的例子，
utf8_bin 将字符串中的每一个字符用二进制数据存储，区分大小写utf8_general_ci 不区分大小写，ci 为case insensitive 的缩写，即大小写不敏感utf8_general_cs 区分大小写，cs 为case sensitive 的缩写，即大小写敏感
比如，创建一张表，使用
CREATE TABLE sales (
order_no VARCHAR(32) NOT NULL PRIMARY KEY,
order_amount INT NOT NULL DEFAULT 0,
......
) ENGINE=InnoDB COLLATE=utf8_general_cs;
因此，在项目中直接使用
字符集对数据库的性能有影响吗？
某些字符集和校对规则可能会需要多个的
不同字符集和校对规则之间的转换可能会带来额外的系统开销，比如，数据表
SELECT order_no,order_amount FROM sales ORDER BY buyer;
只有当
// 你说，这不是吃饱了撑的吗？我觉得也是，也许会有其适用的场景吧
// 这时候就不能使用索引排序呢，只能使用文件排序
SELECT order_no,order_amount FROM sales ORDER BY buyer COLLATE utf8_bin;
当使用两个字符集不同的列来关联两张表时，
MySQL 配置
MySQL 配置的工作原理
任何打算长期使用的配置项都应该写入配置文件，而不是在命令行中指定。一定要清楚的知道
root@msc3:~# which mysqld
/usr/sbin/mysqld
root@msc3:~# /usr/sbin/mysqld --verbose --help |grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
一个典型的配置文件包含多个部分，每个部分的开头是一个方括号括起来的分段名称。
# 配置文件
max_connections=5000
max-connections=5000
# 命令行
/usr/sbin/mysqld --max_connections=5000
/usr/sbin/mysqld --max-connections=5000
配置项可以有多个作用域：全局作用域、会话作用域
query-cache-size 全局配置项sort-buffer-size 默认全局相同，但每个线程里也可以设置join-buffer-size 默认全局，且每个线程也可以设置。但若一个查询中关联多张表，可以为每个关联分配一个关联缓存( join-buffer) ，所以一个查询可能有多个关联缓冲。
配置文件中的变量
# 设置全局变量，GLOBAL和@@global作用是一样的
set GLOBAL sort-buffer-size = <value>
set @@global.sort-buffer-size := <value>
# 设置会话级变量，下面6种方式作用是一样的
# 即：没有修饰符、SESSION、LOCAL等修饰符作用是一致的
set SESSION sort-buffer-size = <value>
set @@session.sort-buffer-size := <value>
set @@sort-buffer-size = <value>
set LOCAL sort-buffer-size = <value>
set @@ocal.sort-buffer-size := <value>
set sort-buffer-size = <value>
# set命令可以同时设置多个变量，但其中只要有一个变量设置失败，所有的变量都未生效
SET GLOBAL sort-buffer-size = 100, SESSION sort-buffer-size = 1000;
SET GLOBAL max-connections = 1000, sort-buffer-size = 1000000;
动态的设置变量，
// @exp 表示用户变量，上面的示例均是系统变量
// 错误
set @user = 123456;
set @group = select GROUP from USER where User = @user;
select * from USER where GROUP = @group;
// 正确
SET @user := 123456;
SELECT @group := `group` FROM user WHERE user = @user;
SELECT * FROM user WHERE `group` = @group;
有一些配置使用了不同的单位，比如1M=1024*1024 字节，但需要注意的是，这只能在配置文件或者作为命令行参数时有效。当使用1024*1024 这样的表达式，但在配置文件中不能使用表达式。
小心翼翼的配置MySQL
好习惯1 ：不要通过配置项的名称来推断一个变量的作用
不要通过配置项的名称来推断一个变量的作用，因为它可能跟你想象的完全不一样。比如：
read-buffer-size：当MySQL 需要顺序读取数据时，如无法使用索引，其将进行全表扫描或者全索引扫描。这时，MySQL 按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在缓存中，当缓存空间被写满或者全部数据读取结束后，再将缓存中的数据返回给上层调用者，以提高效率。read-rnd-buffer-size：和顺序读取相对应，当MySQL 进行非顺序读取（随机读取）数据块的时候，会利用这个缓冲区暂存读取的数据。比如：根据索引信息读取表数据、根据排序后的结果集与表进行Join 等等。总的来说，就是当数据块的读取需要满足一定的顺序的情况下，MySQL 就需要产生随机读取，进而使用到read-rnd-buffer-size参数所设置的内存缓冲区。
这两个配置都是在扫描max-read-rnd-buffer-size
好习惯2 ：不要轻易在全局修改会话级别的配置
对于某些会话级别的设置，不要轻易的在全局增加它们的值，除非你确认这样做是对的。比如： sort-buffer-size，该参数控制排序操作的缓存大小，
SET @@seession.sort-buffer-size := <value>
-- 执行查询的sql
SET @@seession.sort-buffer-size := DEFAULT #恢复默认值
# 可以将类似的代码封装在函数中方便使用。
好习惯3 ：配置变量时，并不是值越大越好
配置变量时，并不是值越大越好，而且如果设置的值太高，可能更容易导致内存问题。在修改完成后，应该通过监控来确认变量的修改对服务器整体性能的影响。
好习惯4 ：规范注释，版本控制
在配置文件中写好注释，可能会节省自己和同事大量的工作，一个更好的习惯是把配置文件置于版本控制之下。
说完了好习惯，再来说说不好的习惯。
坏习惯1 ：根据一些“比率”来调优
一个经典的按“比率”调优的经验法则是，缓存的命中率应该高于某个百分比，如果命中率过低，则应该增加缓存的大小。这是非常错误的意见，大家可以仔细思考一下：缓存的命中率跟缓存大小有必然联系吗？
坏习惯2 ：随便使用调优脚本
尽量不要使用调优脚本！不同的业务场景、不同的硬件环境对
给你一个基本的MySQL 配置
前面已经说到，
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
user = mysql
port = 3306
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
# DATA STORAGE #
datadir = /var/lib/mysql/
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 12G
分段
[client] 客户端默认设置内容[mysql] 使用mysql 命令登录mysql 数据库时的默认设置[mysqld] 数据库本身的默认设置
例如服务器
GENERAL
首先创建一个用户
在类
DATA STORAGE
为缓存分配内存
接下来有许多涉及到缓存的配置项，缓存设置多大，最直接的因素肯定是服务器内存的大小。如果服务器只运行
InnoDB 缓冲池InnoDB 日志文件和MyISAM 数据的操作系统缓存(MyISAM 依赖于OS 缓存数据) MyISAM 键缓存- 查询缓存
- 无法配置的缓存，比如：
bin-log 或者表定义文件的OS 缓存
还有一些其他缓存，但它们通常不会使用太多内存。关于查询缓存，前面文章query-cache-type=0表示禁用了查询缓存，相应的查询缓存大小 query-cache-size=0。除开查询缓存，剩下关于
如果只使用单一存储引擎，配置服务器就会简单许多。如果只使用
MyISAM
key-buffer-size
key-buffer-size 用于配置
// 1.通过SQL语句查询
SELECT SUM(INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'MYISAM'
// 2.统计索引文件的大小
$ du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`
比如：
root@dev-msc3:# du -sch `find /var/lib/mysql -name "*.MYI"`
72K /var/lib/mysql/static/t_global_region.MYI
40K /var/lib/mysql/mysql/db.MYI
12K /var/lib/mysql/mysql/proxies_priv.MYI
12K /var/lib/mysql/mysql/tables_priv.MYI
4.0K /var/lib/mysql/mysql/func.MYI
4.0K /var/lib/mysql/mysql/columns_priv.MYI
4.0K /var/lib/mysql/mysql/proc.MYI
4.0K /var/lib/mysql/mysql/event.MYI
4.0K /var/lib/mysql/mysql/user.MYI
4.0K /var/lib/mysql/mysql/procs_priv.MYI
4.0K /var/lib/mysql/mysql/ndb_binlog_index.MYI
164K total
你可能会问，刚创建好的数据库，根本就没什么数据，索引文件大小为
// key_blocks_unused的值可以通过 SHOW STATUS获取
// key_cache_block_size的值可以通过 SHOW VARIABLES获取
(key_blocks_unused * key_cache_block_size) / key_buffer_size
键缓存块大小是一个比较重要的值，因为它影响
关于缓存命中率，这里再说一点。缓存命中率有什么意义？其实这个数字没太大的作用。比如
# 计算每隔10s缓存未命中次数的增量
# 使用此命令时请带上用户和密码参数：mysqladmin -uroot -pxxx extended-status -r -i 10 | grep Key_reads
$ mysqladmin extended-status -r -i 10 | grep Key_reads
最后，即使没有使用任何key-buffer-size设置为较小值，比如
myisam-recover
myisam-recover选项用于配置
- DEFAULT：表示不设置，会尝试修复崩溃或者未完全关闭的表，但在恢复数据时不会执行其它动作
- BACKUP：将数据文件备份到
.bak 文件，以便随后进行检查 - FORCE：即使
.myd 文件中丢失的数据超过1 行，也让恢复动作继续执行 - QUICK：除非有删除块，否则跳过恢复
可以设置多个值，每个值用逗号隔开，比如配置文件中的 BACKUP,FORCE会强制恢复并且创建备份，这样配置在只有一些小的
因此，在默认使用key-buffe-size于一个很小的值myisam-recover=BACKUP,FORCE。当数据库中大部分表为key-buffer-size，而 myisam-recover则可以关闭，在启动后使用 CHECK TABLES和 REPAIR TABLES命令来做检查和修复，这样对服务器的影响比较小。
SAFETY
基本配置设置到位后，
max-allowed-packet
max-connect-errors
这个变量是一个
这个值默认为
LOGGING
接下来看下日志的配置，对于
关于
sync-binlog
sync-binlog控制当事务提交之后，
需要注意的是，在
剩下的
- log-error：用于配置错误日志的存放目录
- slow-query-log：打开慢日志，默认关闭
- slow-query-log-file：配置慢日志的存放目录
- log-queries-not-using-indexes：如果该
sql 没有使用索引，会将其写入到慢日志，但是否真的执行很慢，需要区分，默认关闭。
CACHES AND LIMITS
tmp-table-size && max-heap-table-size
这两个配置控制使用
如果查询语句没有创建庞大的临时表
应该简单的把这两个变量设为同样的值，这里选择了
max-connections
用于设置用户的最大连接数，保证服务器不会应为应用程序激增的连接而不堪重负。如果应用程序有问题，或者服务器遇到连接延迟问题，会创建很多新连接。但如果这些连接不能执行查询，那打开一个连接没什么好处，所以被“太多的连接”错误拒绝是一种快速而且代价小的失败方式。
在服务器资源允许的情况下，可以把
thread-cache-size
线程缓存保存那些当前没有与连接关联但是准备为后面新连接服务的线程。当一个新的连接创建时，如果缓存中有线程存在，
如何判断这个值该设置多大？
观察
open-files-limit
在类
tablecachesize
表缓存跟线程缓存类似，但存储的对象是表，其包含表
从
INNODB
innodb-buffer-pool-size
如果大部分是
当然，如果数据量不大且不会快速增长，就没有必要为缓冲池分配过多的内存，把缓冲池配置得比需要缓存的表和索引还要大很多，实际上也没有什么意义。很大的缓冲池也会带来一些挑战，例如，预热和关闭都会花费很长的时间。如果有很多脏页在缓冲池里，
可以看到示例的配置文件中把这个值配置为
InnoDB缓冲池 = 服务器总内存 - OS预留 - 服务器上的其他应用占用内存 - MySQL自身需要的内存 - InnoDB日志文件占用内存 - 其它内存(MyISAM键缓存、查询缓存等)
具体来看，至少需要为
一般来说，运行
如果大部分表都是
innodb-log-file-size && innodb-log-files-in-group
如果对
整体的日志文件大小受控于
修改日志文件的大小，需要完全关闭
想要确定理想的日志文件大小，需要权衡正常数据变更的开销，以及崩溃时恢复需要的时间。如果日志太小，
innodb-flush-log-at-trx-commit
前面讨论了很多缓存，
既然存在缓冲区，怎样刷新日志缓冲就是我们需要关注的问题。日志缓冲必须刷新到磁盘，以确保提交的事务完全被持久化。如果和持久化相比，更在乎性能，可以修改
- 0：每
1 秒钟将日志缓冲写到日志文件并刷新到磁盘，事务提交时不做任何处理 - 1：每次事务提交时，将日志缓冲写到日志文件并刷新到磁盘
- 2：每次事务提交时，将日志缓冲写到日志文件，然后每秒刷新一次到磁盘
innodb-flush-method
前面都在讨论使用什么样的策略刷新、以及何时刷新日志或者数据，那
这个选项既会影响日志文件，也会影响数据文件，而且有时候对不同类型的文件的处理也不一样，导致这个选项有些难以理解。如果有一个选项来配置日志文件，一个选项来配置数据文件，应该会更好，但实际上它们混合在同一个配置项中。这里只介绍类
fdatasync
fsync()和 fdatasync()函数来刷新数据和日志文件，其中 fdatasync()只刷文件的数据，但不包含元数据fsync()相比 fdatasync()会产生更多的fdatasync()会导致数据损坏，因此fsync()来代替 fdatasync()。
fsync()的缺点是操作系统会在自己的缓存中缓冲一些数据，理论上双重缓冲是浪费的，因为
- 有的文件系统和
os 可以累积写操作后合并执行，通过对I/O 的重排序来提升效率、或者并发写入多个设备 - 有的还可以做预读优化，比如连续请求几个顺序的块，它会通知硬盘预读下一个块
这些优化在特定的场景下才会起作用， fdatasync为 innodb-flush-method的默认值。
0_DIRCET
这个设置不影响日志文件并且不是所有的类
0_DSYNC
这个选项使得所有的写同步，即只有数据写到磁盘后写操作才返回，但它只影响日志文件，而不影响数据文件。
说完了每个配置的作用，最后是一些建议：如果使用类
innodb-file-per-table
最后一个配置，说说
innodb-data-home-dir = /var/lib/mysql
innodb-data-file-path = ibdata1:1G;ibdata2:1G;ibdata3:1G
这里在
innodb-data-file-path =ibdata1:1G;ibdata2:1G;ibdata3:1G:autoextend