MySQL的查询过程

MySQL 基本架构

总体来说，MySQL大体分为两部分，分别是Server 层和存储引擎层。

Server 层

它包括连接器、查询缓存、分析器、优化器、执行器等。比如存储过程，触发器，视图都是在这一层实现的。

• 连接器（Connection Manager）：负责处理客户端与服务器之间的连接。它接受来自客户端的请求，并进行身份验证和权限检查，建立和管理连接。
• 查询缓存（Query Cache）：在旧版 MySQL 中有，但在较新的版本中已不推荐使用。它能够缓存查询和对应的结果，以提高查询性能。然而，在高并发和大型数据库中，它反而可能成为性能瓶颈，因为它在某些情况下会引起锁和不必要的开销。
• 分析器（Parser）：负责分析 SQL 查询语句，验证其语法和语义，确保查询的正确性。它将 SQL 语句转换成内部数据结构供优化器和执行器使用。
• 优化器（Optimizer）：接收来自分析器的查询请求，并决定如何最有效地执行查询。优化器的目标是找到最佳的执行路径，选择合适的索引、连接顺序和访问方法，以提高查询性能。
• 执行器（Executor）：负责执行优化器生成的执行计划，获取存储引擎返回的数据，并处理客户端请求。它与存储引擎交互，执行查询并返回结果给用户。

存储引擎层: 它负责数据的存储和提取。Mysql支持InnoDB、MyISAM、Memory 等多个存储引擎。我们日常开发中，一般用的存储引擎就是InnoDB。从 MySQL 5.5 版本开始，InnoDB 就成为了默认的存储引擎。

连接器

我们要执行查询SQL，一般在MySQL客户端, 需要输入连接命令，连接到MySQL服务端。在MySQL服务端，就是连接器负责跟你的客户端建立连接、获取权限、维持和管理连接。

如果连接成功后，没有后续的输入查询SQL等其他操作。这时候，这个连接是空闲的哈，可以用show processlist查看。

查询缓存

在老版本的MySQL中，连接成功后，我们执行查询SQL，会先执行查询缓存。

也就是说MySQL接受到一个查询SQL请求时，会先去查询缓存看看，如果缓存有这条SQL的查询结果，会直接返回。如果查询缓存没有，就继续往下执行，执行完之后，把结果写入缓存。其中，这个查询缓存是key-value的结果，你可以把它理解为一个map吧，其中key就是这个查询SQL，value则是这个查询的结果。

同时，如果你查询的表进行更新的时候，会清空缓存的。一个表更新比较频繁的话，使用查询缓存命中率会很低，你刚查完放到缓存，更新SQL又清空了，就很不划算。有些时候，一些静态配置表，很少更新的，才建议使用查询缓存。其他更新频繁的表，则不建议使用查询缓存，你可以通过这个参数query_cache_type 设值是否走查询缓存。

其实，MySQL 比较新的版本，如8.0 已经废弃了查询缓存，并且相应的参数 query_cache_type 也不再存在。因为在高并发和大型数据库环境下，查询缓存可能导致性能问题，并且在实际测试中发现，禁用查询缓存可能会提高整体性能和可伸缩性。

分析器

如果查询SQL没有命中查询缓存的话，继续往下执行，就到分析器上场了。它负责分析 SQL 查询语句，验证其语法和语义，确保查询的正确性。

如果你的SQL写错了，语法分析就会报错误提示： ERROR 1064 (42000): You have an error in your SQL syntax;

优化器

经过分析器之后，MySQL已经知道需要做什么了。但是在经过执行器之前，还会先经过优化器。优化器做的事情就是，怎么去做才是最好的。对于一条查询SQL来说就是:怎么去查是最佳效率的。

例如这个查询SQL：

select * from test_db.user_info_tab where user_id =123 and user_name='田螺';

其中，在user_info_tab表中，user_id为索引字段，user_name也是索引字段。

这条SQL执行的时候，可能使用索引user_id，也可能使用使用user_name。选择不同的索引，执行效率是不一样的。具体怎么选择，就是优化器所做的事情。

经过优化器之后，就来到了执行器阶段。也就是真正执行查询SQL了。

执行器

select * from test_db.user_info_tab where user_id =123 ;

在要开始执行时候，会判断一下，该用户是否对这个SQL有查询的权限，如果没有，则会报权限错误。如果有权限的时候，打开表直接执行。执行的过程，其实类似于执行调用引擎提供的接口。

我们现在假设user_id不是索引字段，我们使用的是InnoDb存储引擎，这个查询SQL执行过程就是这样：

1. 调用InnoDb存储引擎提供的接口，获取user_info_tab表的第一行。
2. 判断user_id是不是为123，如果不是，跳过这一行。如果是，把这一行放到结果集。
3. 调用InnoDb存储引擎提供的接口，获取user_info_tab表的下一行。
4. 判断user_id是不是为123，如果不是，跳过这一行。如果是，把这一行放到结果集。
5. 重复3、4步骤，一直扫描完user_info_tab表的所有行。最后把结果集返回客户端。

慢SQL知识

产生慢SQL的原因

1. 缺乏索引/索引未生效，导致数据库全表扫描，会产生大量的IO消耗，产生慢SQL。
2. 单表数据量太大，会导致加索引的效果不够明显。
3. SQL语句书写不当，例如join或者子查询过多、in元素过多、limit深分页问题、order by导致文件排序、group by使用临时表等。
4. 数据库在刷“脏页”，redo log写满了，导致所有系统更新被堵住，无法写入了。
5. 执行SQL的时候，遇到表锁或者行锁，只能等待锁被释放，导致了慢SQL。

慢SQL的危害

1. 系统的响应时间延迟，影响用户体验
2. 资源占用增加，增高了系统的负载，其他请求响应时间也可能会收到影响。
3. 慢SQL占用数据库连接的时间长,如果有大量慢SQL查询同时执行，可能会导致数据库连接池的连接被全部占用，导致数据连接池打满、缓冲区溢出等问题，使数据库无法响应其他请求。
4. 还有可能造成锁竞争增加、数据不一致等问题

全量SQL

除了执行时长超过1s的慢SQL之外，我们还额外关注了未来可能劣化的慢SQL，这样就需要获取全量SQL，再对其进行分析，筛选出其中风险较大的SQL。

慢SQL的查看配置

慢SQL文件名称
mysql> show global variables like 'slow_query_log_file';
+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_query_log_file | DESKTOP-Q3MJC54-slow.log |
+---------------------+--------------------------+
1 row in set (0.01 sec)

慢SQL的配置
mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_query_log      | ON                       |
| slow_query_log_file | DESKTOP-Q3MJC54-slow.log |
+---------------------+--------------------------+
2 rows in set (0.02 sec)

MySQL文件位置
mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| datadir       | C:\MySQL\MySQL Server 5.7\Data\ |
+---------------+---------------------------------+
1 row in set (0.02 sec)

# 检查慢查询的详细指标，可以看到下面 slow_query_log = ON，long_query_time = 10 ，都是因为我们调整过的
mysql> show global variables like '%quer%';
+----------------------------------------+--------------------------+
| Variable_name                          | Value                    |
+----------------------------------------+--------------------------+
| binlog_rows_query_log_events           | OFF                      |
| ft_query_expansion_limit               | 20                       |
| have_query_cache                       | YES                      |
| log_queries_not_using_indexes          | OFF                      |
| log_throttle_queries_not_using_indexes | 0                        |
| long_query_time                        | 10.000000                |
| query_alloc_block_size                 | 8192                     |
| query_cache_limit                      | 1048576                  |
| query_cache_min_res_unit               | 4096                     |
| query_cache_size                       | 0                        |
| query_cache_type                       | OFF                      |
| query_cache_wlock_invalidate           | OFF                      |
| query_prealloc_size                    | 8192                     |
| slow_query_log                         | ON                       |
| slow_query_log_file                    | DESKTOP-Q3MJC54-slow.log |
+----------------------------------------+--------------------------+
15 rows in set (0.03 sec)

如果我们的慢SQL很多，人工分析肯定分析不过来，这时候我们就需要借助一些分析工具，MySQL自带了一个慢查询分析工具mysqldumpslow，以下是常见使用示例

mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log # 取出使用最多的10条慢查询
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log # 取出查询时间最慢的3条慢查询
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log #得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqldslow.log # 按照扫描行数最多的

explain解析SQL执行

所执行的 SQL 前面加上 explain 关键字，MySQL 就不会真正去执行这条语句，而是模拟优化器执行 SQL 查询语句，最后会输出一系列的指标告诉我们这条语句的性能如何。

explain 关键字可以告诉我们下面这么多信息：

1. 表的读取顺序如何
2. 数据读取操作有哪些操作类型
3. 哪些索引可以使用
4. 哪些索引被实际使用
5. 表之间是如何引用
6. 每张表有多少行被优化器查询

根据提供的EXPLAIN执行计划结果，可以得到以下分析：

• PRIMARY部分： type: ALL 表示主查询使用了全表扫描，即没有使用索引。 rows: 16491 表示预估的检索行数为16491行。 Using where 表示在检索过程中使用了WHERE子句中的条件。
• DEPENDENT SUBQUERY部分： type: ALL 表示子查询也使用了全表扫描，没有使用索引。 rows: 16491 表示预估的检索行数为16491行。 Using where 表示在检索过程中使用了WHERE子句中的条件。

explain字段解释

select_type 字段

select_type 字段表示该 SQL 是什么查询类型，一共有以下 6 种：

• SIMPLE：简单查询，不包含子查询或 union 查询
• PRIMARY：主键查询
• SUBQUERY：在 select 或 where 中包含子查询
• DERIVED：from 中包含子查询
• UNION：类似包含 union 关键字的会被标记成 UNION 类型，这种查询方式比较少，
• UNION RESULT：类似包含 union 关键字的会被标记成 UNION RESULT 类型，这种查询方式比较少

type 字段

type 字段表示访问情况，通常用来衡量 SQL 的查询效率。其值的查询效率从最好到最差分别为：

• NULL 表示 MySQL 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
• system 表只有一行记录（等于系统表），这是 const 类型的特列。
• const
• const 表示该表最多有一个匹配记录。
• 通常情况下是 SQL 中出现了主键索引或唯一索引。
• eq_ref
• eq_ref 表示主键索引或唯一索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。
• 与 const 类型非常相似，唯一的区别是 eq_ef 通常出现在联表的情况下，而 const 通常出现在单表情况下。
• ref
• ref 表示使用了非唯一索引扫描，会返回匹配某个单独值的所有行。
• 与 const 非常类似，只不过 ref 会匹配到多个记录，而 const 则只会匹配到单个记录。
• fulltext
• ref_or_null
• index_merge
• 表示使用了索引合并的优化方法。
• 索引合并指的是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并。
• unique_subquery
• index_subquery
• range
• range 表示检索给定范围的行，使用一个索引来选择行，key 列显示使用了哪个索引。
• 一般就是在你的 where 语句中出现 between、<>、in 等的范围查询。
• index
• ALL
• ALL 表示该查询将遍历全表以找到匹配行，这是最糟糕的一种查询方式。

table 字段

表示数据来自哪张表

possible_keys 字段

显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用。

key 字段

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

查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在 key 列表。

key_len 字段

这一列显示了 mysql 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。举例来说，film_actor 的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 int 列组成，并且每个 int 是 4 字节。通过结果中的 key_len=4 可推断出查询使用了第一个列：film_id 列来执行索引查找。

key_len 计算规则如下：

字符串
char (n)：n 字节长度
varchar (n)：2 字节存储字符串长度，如果是 utf-8，则长度 3n + 2
数值类型
tinyint：1 字节
smallint：2 字节
int：4 字节
bigint：8 字节
时间类型
date：3 字节
timestamp：4 字节
datetime：8 字节
其他
如果字段允许为 NULL，需要 1 字节记录是否为 NULL

ref 字段

这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）。

rows 列

这一列是 mysql 估计要读取并检测的行数，注意这个不是结果集里的行数。

**Extra 列 **

这一列展示的是额外信息。

distinct 一旦 mysql 找到了与行相联合匹配的行，就不再搜索了。

Using index 这表示查找某个表的时候，所需要的信息直接从索引就可以拿到，而不需要再访问行记录。

Using where mysql 服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。

Using temporary mysql 需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

Using filesort MySQL 中无法利用索引完成的排序操作称为「文件排序」。

在 MySQL 中的 ORDER BY 有两种排序实现方式：

利用有序索引获取有序数据 文件排序 在 explain 中分析查询的时候，利用有序索引获取有序数据显示 Using index ，文件排序显示 Using filesort。至于什么时候使用索引排序，什么时候使用文件排序，这个问题太过于复杂，这里不做深入介绍。

profile 分析执行耗时

explain只是看到SQL的预估执行计划，如果要了解SQL真正的执行线程状态及消耗的时间，需要使用profiling。开启profiling参数后，后续执行的SQL语句都会记录其资源开销，包括IO，上下文切换，CPU，内存等等，我们可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化。

profiling默认是关闭，我们可以使用show variables like '%profil%'查看是否开启，

可以使用set profiling=ON开启。开启后，可以运行几条SQL，然后使用show profiles查看一下。

show profiles会显示最近发给服务器的多条语句，条数由变量profiling_history_size定义，默认是15。如果我们需要看单独某条SQL的分析，可以show profile查看最近一条SQL的分析。也可以使用show profile for query id（其中id就是show profiles中的QUERY_ID）查看具体一条的SQL语句分析。

MySQL的索引知识

索引就是帮助MySQL高效获取数据的一种【数据结构】。索引是一种树结构，MySQL中一般用的是【B+树】。

索引分类

单值索引

唯一索引

复合索引

① 单值索引

利用表中的某一个字段创建单值索引。一张表中往往有多个字段，也就是说每一列其实都可以创建一个索引，这个根据我们实际需求来进行创建。还需要注意的一点就是，一张表可以创建多个“单值索引”。

假如某一张表既有age字段，又有name字段，我们可以分别对age、name创建一个单值索引，这样一张表就有了两个单值索引。

② 唯一索引

也是利用表中的某一个字段创建单值索引，与单值索引不同的是：创建唯一索引的字段中的数据，不能有重复值。像age肯定有很多人的年龄相同，像name肯定有些人是重名的，因此都不适合创建“唯一索引”。像编号id、学号sid，对于每个人都不一样，因此可以用于创建唯一索引。

③ 复合索引

多个列共同构成的索引。比如说我们创建这样一个“复合索引”(name,age)，先利用name进行索引查询，当name相同的时候，我们利用age再进行一次筛选。注意：复合索引的字段并不是非要都用完，当我们利用name字段索引出我们想要的结果以后，就不需要再使用age进行再次筛选了。

创建索引

语法

语法：create 索引类型 索引名 on 表(字段);

创建索引的第一种方式

Ⅰ 创建单值索引

create index dept_index on tb(dept);

Ⅱ 创建唯一索引：这里我们假定name字段中的值都是唯一的

create unique index name_index on tb(name);

Ⅲ 创建复合索引

create index dept_name_index on tb(dept,name);

创建索引的第二种方式

先删除之前创建的索引以后，再进行这种创建索引方式的测试；

语法：alter table 表名 add 索引类型 索引名(字段)

Ⅰ 创建单值索引

alter table tb add index dept_index(dept);

Ⅱ 创建唯一索引：这里我们假定name字段中的值都是唯一的

alter table tb add unique index name_index(name);

Ⅲ 创建复合索引

alter table tb add index dept_name_index(dept,name);

补充说明

如果某个字段是primary key，那么该字段默认就是主键索引。

主键索引和唯一索引非常相似。相同点：该列中的数据都不能有相同值；不同点：主键索引不能有null值，但是唯一索引可以有null值。

索引删除和索引查询

① 索引删除

语法：drop index 索引名 on 表名;

drop index name_index on tb;

② 索引查询

语法：show index from 表名;

show index from tb;

MySQL一些使用上的规范

分析是否有不合理的查询

• 尽量避免使用select *，join语句使用select * 可能导致只需要访问索引即可完成的查询需要回表取数。
• 一种是可能取出很多不需要的数据，对于宽表来说，这是灾难；一种是尽可能避免回表，因为取一些根本不需要的数据而回表导致性能低下，是很不合算。
• 严禁使用select * from t_name，不加任何where条件，道理一样，这样会变成全表全字段扫描。
• MySQL中的text类型字段存储：
• 不与其他普通字段存放在一起,因为读取效率低，也会影响其他轻量字段存取效率。大宽表、大字段表，整体性能也不好。
• 如果不需要text类型字段，又使用了select *，会让该执行消耗大量io，效率也很低下
• 在取出字段上可以使用相关函数，但应尽可能避免出现 now() , rand() , sysdate() 等不确定结果的函数，在Where条件中的过滤条件字段上严禁使用任何函数，包括数据类型转换函数。大量的计算和转换会造成效率低下，这个在索引那边也描述过了。
• 分页查询语句全部都需要带有排序条件 , 否则很容易引起乱序
• 用in()/union替换or，效率会好一些，并注意in的个数小于300
• 严禁使用%前缀进行模糊前缀查询。
• 尽量避免使用子查询，可以把子查询优化为join操作，通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化。子查询性能差的原因：
• 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；
• 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；
• 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。
• 在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表。

检查是否有不合理的索引使用

• 索引区分度（> 0.2）

索引必须创建在索引选择性（区分度）较高的列上，选择性的计算方式为:

selecttivity = count(distinct c_name)/count(*) ;

如果区分度结果小于0.2，则不建议在此列上创建索引，否则大概率会拖慢SQL执行

• 遵循最左前缀，将索引区分度最高的放在左边

对于确定需要组成组合索引的多个字段，设计时建议将选择性高的字段靠前放。使用时，组合索引的首字段，必须在where条件中，且需要按照最左前缀规则去匹配。

正确理解和计算索引字段的区分度，文中有计算规则，区分度高的索引，可以快速地定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。

• 禁止使用外键，可以在程序级别来约束完整性
• varchar、text类型字段如果需要创建索引，必须使用前缀索引。
• 单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引，索引建立的原则理论上是多读少写的场景。
• ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引
• 联合索引注意最左匹配原则：查询时必须按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。如：
• -- 如果建立(depno,empname,job)顺序的索引，job是用不到索引的。
• 应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。
• 正确判断是否使用联合索引，应避免索引下推（IPC），减少回表操作，提升效率。
• 避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。
• 避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。
• 模糊查询'%value%'会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是'value%'是可以有效利用索引。
• 索引覆盖排序字段，这样可以减少排序步骤，提升查询效率

虽然说 MySQL的查询优化器会根据实际索引情况进行顺序优化，所以这边不做强制。但是同等条件下还是按照顺序进行排列，比较清晰，并且节省查询优化器的处理。

MySQL其他方向上的性能优化

优化方向有，硬件优化，系统配置优化，表结构优化 ，SQL语句优化，索引优化。

成本：硬件优化 > 系统配置优化 > 表结构优化 > SQL语句优化 > 索引优化。

效果：索引优化 > SQL语句优化 > 表结构优化 > 系统配置优化 > 硬件优化。

索引和 SQL语句优化相关知识前面已经阐述过了，下面主要针对其他三个方向上的优化进行讲解。

硬件优化

硬件优化无非就是对MySQL所在的服务器CPU，内存，磁盘进行优化。大内存，高IO，是现代基于web的数据库的必备 （百度的服务器内存 ：96G —128个，2个实例 ,CPU8到16颗）。不同版本的MySQL对多核CPU的支持也不一样。

服务器硬件对MySQL性能的影响及优化方案

1.CPU对于MySQL应用，推荐使用S.M.P.架构的多路对称CPU，例如：可以使用两颗Intel Xeon 3.6GHz的CPU，现在我较推荐用4U的服务器来专门做数据库服务器，不仅仅是针对于mysql。

2.物理内存对于一台使用MySQL的Database Server来说，服务器内存建议不要小于2GB，推荐使用4GB以上的物理内存，不过内存对于现在的服务器而言可以说是一个可以忽略的问题，工作中遇到了高端服务器基本上内存都超过了16G。

3.磁盘寻道能力（磁盘I/O）,以目前高转速SCSI硬盘(7200转/秒)为例，这种硬盘理论上每秒寻道7200次，这是物理特性决定的，没有办法改变。MySQL每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以，通常认为磁盘I/O是制约MySQL性能的最大因素之一，对于日均访问量在100万PV以上的系统，由于磁盘I/O的制约，MySQL的性能会非常低下！解决这一制约因素可以考虑以下几种解决方案：使用RAID-0+1磁盘阵列，注意不要尝试使用RAID-5，MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快。

系统配置优化

基本配置

当然还有其他的设置可以起作用，取决于你的负载或硬件：在慢内存和快磁盘、高并发和写密集型负载情况下，你将需要特殊的调整。然而这里的目标是使得你可以快速地获得一个稳健的MySQL配置，而不用花费太多时间在调整一些无关紧要的MySQL设置或读文档找出哪些设置对你来说很重要上。

Linux系统中MySQl配置文件一般位于/etc/my.cnf

innodb_buffer_pool_size

这是你安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方。默认大小为128M。这个值越大越好决于CPU的架构，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。

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设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6，你可以一开始就把它设置成4G。

max_connections

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

InnoDB配置

从MySQL 5.5版本开始，InnoDB就是默认的存储引擎并且它比任何其他存储引擎的使用都要多得多。那也是为什么它需要小心配置的原因。

innodb_file_per_table

这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里（innodb_file_per_table = OFF） 或者为每张表的数据单独放在一个.ibd文件（innodb_file_per_table = ON）。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的，比如数据压缩。但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是：有非常多的表（比如10k+）。

MySQL 5.6中，这个属性默认值是ON，因此大部分情况下你什么都不需要做。对于之前的版本你必需在加载数据之前将这个属性设置为ON，因为它只对新创建的表有影响。

innodb_flush_log_at_trx_commit

默认值为1，表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的，比如在一个主节点上。但是对于磁盘（读写）速度较慢的系统，它会带来很巨大的开销，因为每次将改变flush到redo日志都需要额外的fsyncs。将它的值设置为2会导致不太可靠（reliable）因为提交的事务仅仅每秒才flush一次到redo日志，但对于一些场景是可以接受的，比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了，但在系统崩溃时可能丢失一些数据：只适用于备份节点。

innodb_flush_method

这项配置决定了数据和日志写入硬盘的方式。一般来说，如果你有硬件RAID控制器，并且其独立缓存采用write-back机制，并有着电池断电保护，那么应该设置配置为O_DIRECT；否则，大多数情况下应将其设为fdatasync（默认值）。sysbench是一个可以帮助你决定这个选项的好工具。

innodb_log_buffer_size

这项配置决定了为尚未执行的事务分配的缓存。其默认值（1MB）一般来说已经够用了，但是如果你的事务中包含有二进制大对象或者大文本字段的话，这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量，如果它不是0，增加innodb_log_buffer_size。

其它设置

query_cache_size

query cache（查询缓存）是一个众所周知的瓶颈，甚至在并发并不多的时候也是如此。最佳选项是将其从一开始就停用，设置query_cache_size = 0（现在MySQL 5.6的默认值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。如果你已经为你的应用启用了query cache并且还没有发现任何问题，query cache可能对你有用。这是如果你想停用它，那就得小心了。

log_bin

如果你想让数据库服务器充当主节点的备份节点，那么开启二进制日志是必须的。如果这么做了之后，还别忘了设置server_id为一个唯一的值。就算只有一个服务器，如果你想做基于时间点的数据恢复，这（开启二进制日志）也是很有用的：从你最近的备份中恢复（全量备份），并应用二进制日志中的修改（增量备份）。二进制日志一旦创建就将永久保存。所以如果你不想让磁盘空间耗尽，你可以用 PURGE BINARY LOGS 来清除旧文件，或者设置 expire_logs_days 来指定过多少天日志将被自动清除。

记录二进制日志不是没有开销的，所以如果你在一个非主节点的复制节点上不需要它的话，那么建议关闭这个选项。

skip_name_resolve

当客户端连接数据库服务器时，服务器会进行主机名解析，并且当DNS很慢时，建立连接也会很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。唯一的局限是之后GRANT语句中只能使用IP地址了，因此在添加这项设置到一个已有系统中必须格外小心。

表结构优化

由于MySQL数据库是基于行(Row)存储的数据库，而数据库操作 IO 的时候是以page(block)的方式，也就是说，如果我们每条记录所占用的空间量减小，就会使每个page中可存放的数据行数增大，那么每次 IO 可访问的行数也就增多了。反过来说，处理相同行数的数据，需要访问的page就会减少，也就是 IO 操作次数降低，直接提升性能。

此外，由于我们的内存是有限的，增加每个page中存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数据命中的几率，也就是缓存命中率。

数据类型的选择

数据库操作中最为耗时的操作就是IO处理，大部分数据库操作 90% 以上的时间都花在了IO读写上面。所以尽可能减少IO读写量，可以在很大程度上提高数据库操作的性能。

我们无法改变数据库中需要存储的数据，但是我们可以在这些数据的存储方式方面花一些心思。下面的这些关于字段类型的优化建议主要适用于记录条数较多，数据量较大的场景，因为精细化的数据类型设置可能带来维护成本的提高，过度优化也可能会带来其他的问题。

数字类型

非万不得已不要使用double，不仅仅只是存储长度的问题，同时还会存在精确性的问题。

同样，固定精度的小数，也不建议使用decimal，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。

对于整数的存储，在数据量较大的情况下，建议区分开 tinyint / int / bigint 的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加unsigned定义。当然，如果数据量较小的数据库，也可以不用严格区分三个整数类型。

字符类型

非万不得已不要使用 text 数据类型，其处理方式决定了它的性能要低于char或者是varchar类型的处理。

对于长度固定的字段，建议使用 char 类型，不定长度字段尽量使用 varchar，且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理。

（注意：char(n) 不管该字段是否存储数据，都占n个字符的存储空间；varchar 不存的时候不占空间，存多长数据就占多少空间，可以节省存储空间。）

时间类型

尽量使用timestamp类型，因为其存储空间只需要 datetime类型的一半。但是timestamp存储的数据所以被限制在了1970~2038年之内。

对于只需要精确到某一天的数据类型，建议使用date类型，因为他的存储空间只需要3个字节，比timestamp还少。

enum与set

对于状态字段，可以尝试使用enum来存放，因为可以极大的降低存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。

如果是存放可预先定义的属性数据呢?可以尝试使用set类型，即使存在多种属性，同样可以游刃有余，同时还可以节省不小的存储空间。

default 尽量使用 not null(默认值尽量设为非空)

null类型比较特殊，SQL 难优化。虽然 MySQL null类型和 Oracle 的null有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外的存放空间。

很多人觉得null会节省一些空间，所以尽量让null来达到节省IO的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所以尽量确保 default值不是null，也是一个很好的表结构设计优化习惯。

为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的(推荐使用UNSIGNED)，并设置上自动增加的AUTO_INCREMENT(自增)标志。

就算是你 users 表有一个主键叫 “email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。

而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……

在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比 如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课 程ID叫“外键”其共同组成主键。

MySQL大表优化

• 数据库设计优化

合理的数据库设计可以极大地提高查询效率。我们在设计大表时，可以考虑拆分表、使用分区表、添加索引等方式来优化表结构。同时也要避免使用大量冗余字段、避免频繁使用join查询等操作。

• 索引优化

对于大表的查询操作，索引优化是非常重要的一环。可以考虑增加或者修改索引、使用覆盖索引、使用联合索引等方式来提高查询效率。同时也要注意定期清理冗余的索引以及对于经常使用的查询语句建立索引。

• 分区优化

将大表按照某个列分成多个分区表，每个分区表的数据量较小，可以提高查询和更新的性能。分区表还可以帮助在维护表结构的同时，减少锁表时间，提高并发处理能力。

• 数据清理归档

对于一些历史数据或者无用数据，可以进行定期归档，避免数据过多造成SQL查询效率降低。同时也要注意对于大表进行定期的数据备份以及紧急数据恢复的准备工作。

• 缓存优化

对于一些经常被查询的数据，可以使用缓存优化。使用Redis等缓存中间件来缓存常用的数据，以减少查询数据库的次数，提高查询效率。

• SQL语句优化

在编写SQL查询语句时，要尽可能地简单明了，避免复杂的查询语句，同时也要避免一些不必要的查询操作。对于复杂的查询语句，可以使用Explain执行计划来进行优化。同时也要注意避免使用OR等耗费性能的操作符。

• 分库分表

如果数据量千万级别，需要考虑分库分表哈。分库分表相关知识点，可以看我之前这篇文章哈，我们为什么要分库分表？