人们常说的 MySQL调优 就是对索引的优化，通过 Explain 执行计划查看索引的命中率问题。但是在我看来，对 MySQL 的优化应该从表的设计之初就开始了，好的字符集、存储引擎、主键、数据类型的选择会节省很多服务器的内存资源与I/O资源等等。

我通过 视频学习 + 书籍《高性能Mysql》《阿里巴巴 Java 开发手册 1.4.0》 + 网络文章，做了一些调优总结，主要分为以下3部分：

• 索引优化
• 查询优化
• 库表优化

Mysql性能调优

开篇

学习思路

学习过程中我们脑海要有一个清晰的调优轮廓，在听到 MySQL调优 的时候，能立马想到3大优化：索引优化、查询优化、库表优化。当听到 索引优化 的时候，能在脑海中立马想到索引优化的内容，能在脑海中罗列出来。

常见问题

• 千万级数据库表如何用索引快速查找
• 如何基于索引B+树精准建立高性能索引
• 聚集索引与覆盖索引与索引下推是什么
• 联合索引底层数据存储结构又是怎样的
• 如何使用Mysql查询计划定位线上慢sql问题
• Mysql的最左前缀优化原则是怎么回事
• 为什么推荐使用自增整型的主键而不是UUID
• Mysql并发支撑底层Buffer Pool机制详解
• 阿里内部为什么禁止超过三张表关联查询
• 阿里巴巴内部Mysql索引优化军规

索引

索引优化应该是对查询性能优化最有效的手段了

在数据了较小且负载较低时，索引对性能的影响可能还不明显，但当数据量逐渐增大时，索引的优势就体现出来了

索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为 MySQL只能高效的使用索引的最左前缀列

【强制】主键索引名为 pk_字段名;唯一索引名为 uk_字段名;普通索引名则为 idx_字段名。

索引类型

B-Tree索引

使用 B-Tree 数据结构来存储数据， InnoDB 则使用的是B-Tree的变种 B+Tree

B+Tree: 主要分为非叶子页节点与叶子页节点

每一个叶子节点都包含下一个叶子节点的指针，从而方便叶子节点的范围遍历 所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同 InnoDB 将数据划分为若干个页，InnoDB 中页的大小默认为 16KB 每个非叶子节点存储索引字段的值、指向下一个非叶子页的指针 叶子页节点，除了存储索引数据，也存储指向数据的指针与下一个叶子页的指针 一个叶子页可以存更多的数据，降低树的深度 叶子节点存储索引和数据 叶子节点使用双向指针相互连接形成一个双向链表方便范围查找

查看页大小：

shell
SHOW VARIABLES LIKE '%innodb_page_size%';

数据存储结构：

B+Tree对索引列是顺序组织存储的，所以很适合查找范围数据。 因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的order by操作

sql
CREATE TABLE prople(
    last_name   VACHAR(50)      NOT NULL,
    first_name  VACHAR(50)      NOT NULL,
    dob         date            NOT NULL,
    gender      enum('m','f')   NOT NULL,
    key(last_name,first_name,dob)
);

总结：

由于B+树非叶子节点只存储了索引，这样大大提高了每个非叶子节点可以存储的数据行数量，保证了树的高度可控。

同时叶子节点使用双向指针连接形成链表，在执行范围查询，排序等操作时避免了重复从根节点开始查找数据。

所以InnoDB最终选择了B+树作为索引的数据结构。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效
在 MySQL 中，只有 Memory 引擎显式支持哈希索引

缺点：

• 哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引中的值来避免读取行数据
• 哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序
• 哈希索引不支持部分索引列匹配查找
• 哈希索引只支持等值比较查询，包括（=、in()）不支持范围查询
• 哈希索引访问数据非常快，不过会存在哈希冲突，维护代价很高

总结：

哈希索引只适用于某些特定的场合，一旦适用，带来的性能提升将非常显著 举个例子：数据仓库应用中有一种经典的“星型”schema，需要关联很多查找表，哈希索引就非常适合查找表的需求

索引的优点

• 索引大大减少了服务器需要扫描的数据量

• 索引可以帮助服务器避免排序和临时表

• 索引索引可以将随机 I/O 变为顺序 I/O

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。 InnoDB的聚簇索引实际上是使用B+Tree结构，保存了索引和数据行 InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。 当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。 一个表只能有一个聚簇索引，InnoDB 将通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB 会隐式定义一个主键作为聚簇索引。

聚簇索引的每个叶子节点都包含了主键值、事务ID、用于事务和 MVCC 的回滚指针以及剩余列

最好避免随机的（不连续且值的分布范围非常大）聚簇索引

特别是对于 I/O 密集型的应用，例如：从性能的角度考虑，使用 UUID来作为聚簇索引会很糟糕，它使得聚簇索引的插入变得完全随机

UUID 主键插入行不仅花费的时间更长，而且索引占用的空间也更大

因为主键的值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子时页大小的15//16，留出部分空间用于以后修改），下一条记录就会写入新的页中，主键页也会被顺序的记录填满

优点：

• 减少读取的磁盘I/O，提高了I/O密集型应用的性能
• 数据访问更快。聚簇索引将索引和数据保存在同一个BTree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点：

• 插入速度严重依赖插入顺序
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成2个页来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
• 频繁的页分裂，页会变得稀疏病被不规则的填充，最终数据会有碎片（通过OPTIMIZE TABLE 来重建表并优化页的填充）

二级索引（非聚簇索引）

二级索引的叶子节点包含了引用行的主键列，二级索引访问需要两次索引查找，而不是一次 二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值 二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行（回表查询）。

EXPLAIN 执行计划

要使用 EXPLAI, 只需要在查询中 SELECT 关键字之前增加 EXPLAIN 这个词，MySQL 会设置一个标记，当执行查询的时候，这个标记会使其返回关于在执行计划中每一步的信息，而不是执行它。

id 列

只是一个编号，标识 SELECT 所属的行。如果在语句中没有子查询或者关联查询，那么只会有唯一的 SELECT，于是每一行都将显示1。否则，内存的 SELECT 语句一般会顺序编号，对应于其原始语句中的位置。

select_type 列

这一行显示了对应是简单还是复杂 SELEC，复杂类型可分为三类：简单子查询、所谓的派生表（在 FROM 子句中的子查询）、UNION查询

table 列

显示对应行正在访问哪个表，或者是该表的别名

type 列

访问类型，依次从最差到最优：

• ALL : 全表扫描
• index : 使用索引扫描做排序（索引排序）
• range : 范围扫描是一个有限制的索引扫描。从索引里的某个点开始，返回匹配这个值域的行
• ref : 索引访问，返回所有匹配单个值的行。ref_or_nul 是ref之上的变体，意味着 MySQL必须在初次查找的结果里进行第二次查找以找出NULL条目。
• eq_ref : 索引查找，MySQL 最多返回只返回一条符合条件的记录，可以在使用主键或唯一索引查找时看到
• const,system : 对查询某部分进行优化将其转换成一个常量时，会使用的类型

possible_keys 列

显示查询可以使用哪些索引

key 列

显示实际使用哪个索引来优化

key_len 列

显示 MySQL 在索引里使用的字节数

ref 列

显示之前的表在 key 列记录的索引中查找值所用的列或常量

rows 列

估计为了找到所需的行而需要读取的行数

filtered 列

针对表符合某个条件的记录数的百分比所做的一个悲观估算

Extra 列

• Using index : 使用了覆盖索引.
• Using where： 在存储引擎检索行后在进行过滤
• Using temporary ： 对查询结果排序时使用了一个临时表
• Using filesort ： 对结果使用了一个外部索引排序
• Using index Condition ： 使用索引下推

索引优化

个人总结索引的优化也是从以下3点考虑：

• 创建索引时的优化
• 查询索引时的优化
• 索引和表维护优化

创建索引时的优化

索引顺序

将选择性更高的列放到索引最前列（查看列的数据基数，数据基数越少则选择性越高）

当不考虑排序和分组时，将选择性最高的列放在前面通常是很好的，这时候索引的作用只是用于优化 where 条件的查找

mysql
示例： select * from payment where staff_id = 2 and customer_id = 584

是创建一个 （staff_id , customer_id）索引，还是颠倒一下顺序？怎么来确定哪个列的选择性更高？可通过以下查询预测一下对应的数据技术有多大：

mysql
    select sum(staff_id), sum(customer_id) from payment
    
    ------------- 1.rows -------------
     sum(staff_id)：7992
     sum(customer_id)：30
    ----------------------------------

根据前边的经验法则，考虑的是全局基数和选择性，应该将 customer_id 放到前面，因为对应条件值的 customer_id 数量更小，选择性更高

经验法则值得去研究跟分析，但一定别忘了where 子句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响

设计优秀的索引应该考虑整个查询，而不单单是 where 条件的部分

---

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们称之为 覆盖索引

如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？

覆盖索引必须要存储索引列的值，MySQL 只能使用 B-Tree 索引做覆盖索引

InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

当发起一个被索引覆盖的查询，在 Explain 的 Extra 列可以看到 Usung index 的信息

id	select_type	table	type	possible_keys	key	key_length	ref	rows	Extra
1	SIMPLE	a	const	PRIMARY	PRIMARY	8	const	1	Using index

索引下推

索引下推( Index Condition Pushdown ，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率

不使用索引条件下推优化时：

• 存储引擎读取索引记录
• 根据索引中的主键值，定位并读取完整的行记录
• 返回给MySQL服务层，服务层判断数据是否符合条件

当使用索引条件下推优化时：

• 存储引擎读取索引记录（不是完整的行记录）
• 存在某些被索引的列的判断条件时，MySQL服务层将这一部分判断条件传递给存储引擎
• 判断条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
• 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
• 存储引擎把记录交给服务层，服务层检测该记录是否满足条件的其余部分

EXPLAN 分析: 如果使用了 索引条件下推，Extra 会显示 Using index condition。

id	select_type	table	type	possible_keys	key	key_length	rows	Extra
1	SIMPLE	a	ref	code_index	code_index	153	1	Using index condition

---

查询索引时的优化

1、索引的排序与最左前缀原则

索引创建完成后，要考虑的是查询过程中索引的命中率问题。这个时候该我们的 最左前缀原则 上场了

只有当索引的列顺序和 ORDER BY 列的顺序完全一致，并且所有列的排序方向都一样时，MySQL 才能够使用索引来对结果做排序

如果查询需要关联多张表，则只有当 ORDER BY 子句引用的字段全部为第一个表时，才会使用索引做排序

有一种情况下 ORDER BY 子句可以不满足索引的最左前缀，就是前导列为常数的时候，如果 WHERE 子句或者 JOIN 子句中对这些列指定常量，就可以“弥补”索引的不足，查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀

mysql
示例：索引 （rental_date, inventory_id, customer_id）

# 第一列是常量 + 第二列 order by，查询命中索引没问题
...... where rental_date = '2005-05-25' order by inventory_id desc;


# order by 使用的两列是索引的最前列，符合最左前缀
...... where rental_date > '2005-05-25' order by rental_date, inventory_id desc; 

2、支持多种过滤条件（不要建立过多索引）

当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对其他查询进行优化

如果发现某些查询需要创建新索引，但是这个索引又会降低另一些查询的效率，那么应该想一下是否能优化原来的查询

应该同时优化查询和索引以找到最佳平衡，而不是闭门造车去设计最完美的索引

如果在查询中没有使用某个索引的第一列，可以通过在查询条件中新增 sex in(0,1)，来让MySQL选择该索引，必须加上最前列的条件，才能够匹配索引的最左前缀

mysql
示例：索引 （sex, country）

...... where sex in (0,1)  and country = '北京';

3、避免多个范围条件

对于范围条件查询，MySQL无法使用范围列后面的其他索引列了，但是对于 “多个等值条件查询” 则没有这个限制

4、如果一个查询匹配的结果有上百万行，对那些选择性非常低的列会怎样？

可以增加一些特殊的索引来做排序，优化这类索引的比较好的策略是使用 延迟关联，

分页靠后时，通过使用覆盖索引查询返回需要的主键，根据主键关联原表获得需要的行，减少 MySQL 扫描后丢弃的行数

mysql
# 优化排序: 根据筛选条件 order by 后查出主键，通过主键进行范围查询
select id from tunnel
	inner join ( 
	    select 
	        p.id from tunnel p 
	    where 
	        p.SEX = 'M' order by p.rating limit 10000, 10 
	) as x using (id)

# 优化查询：将原本的连接查询改为子查询，查询对应需要的数据列:
select a.*, 
    (select t.tunnel_name from tunnel t where t.tunnel_id = a.t.tunnel_id) from sensor a

索引和表维护优化

1、找到并修复损坏的表

表损坏通常是系统崩溃导致的，其他的引擎也会由于硬件问题、MySQL 本身的缺陷或者操作系统的问题导致索引损坏。损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时还会导致数据库的崩溃。

如果你遇到了一些古怪的问题 - 例如一些不应该发生的错误，可以尝试运行 check table 表名，来检查是否发生了表损坏，check table 通常能够找出大多数的表和索引的错误

2、更新索引的统计信息

统计信息不准确一般会导致 MySQL 优化器无法选择正确的索引，从而导致优化器选择错误的索引或者不走索引，引起索引失效，导致大量慢查询。

可以通过 show index from 表名 命令来查看索引基数，可以通过 analyze table 表名 来重新生成统计信息来解决这个问题。

推荐文章： [一招快速解决mysql innodb表索引统计信息不准确问题][3]

3、减少索引和数据的碎片

B-Tree 索引可能会碎片化，降低查询的效率。碎片化的索引会很差或者无序的方式存储在磁盘上。如果叶子页在物理分布上是顺序且紧密的，那么查询的性能会更好。否则对于范围查询，索引覆盖扫描等操作来说，速度会降低很多倍，对于索引的覆盖扫描这一点会更加明显。

表的数据存储也会碎片化。有三种碎片类型：

• 行碎片
• 行间碎片
• 剩余空间碎片

查看某个表的碎片大小：SHOW TABLE STATUS like '表名' ， Date_free 列就是就是碎片大小

可以通过 optimize table 表名 或者 导出在导入的方式 来重新整理表。

对于那些不支持 optimize table 的存储引擎，可以使用通过 alter table 表名 engine = 存储引擎 的操作来重建表

如何判断一个系统创建的索引是否合理？

一般来说，我们建议响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询，然后检查这些查询的 schema、SQL和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表（Extra 列且显示 Using temporary ），是否使用随机I/O访问文件，或者是有太多回表查询那些不在索引中的列的操作。

---

索引失效的场景

1、索引列参与运算或使用函数

在SQL语句中，对索引字段做函数操作时，会放弃了索引的树搜索功能，只使用了全索引扫描。

2、类型与字符集不一致

普通查询或者 JOIN 连接的字段如果 类型不一致 ，也会导致索引失效。例如: 用户表中的 userId 字段是字符串类型，查询的时候使用整数类型；在例如连接查询的时候，订单表中的 userId 是整型类型，当关联查询的时候；MySQL 都会使用 cast()函数进行类型的隐式转换，而使用函数就会导致索引失效

当进行联表查询时，如果关联字段对应的两张表各自的字符集不同，会将字段进行字符集转换，这里字符集转换用的是内部函数操作，此时优化器会放弃走树搜索的功能，会导致索引失效。

3、范围查询没有放到最右列

当索引列范围查询放在索引列常量查询之前时，会导致范围查询右侧的索引列失效。

对于索引中的字段，MySQL会一直向右匹配直到遇到范围查询（>、<、between、in、like）就会停止匹配。

4、使用 or 时前后存在非索引的列

5、使用 is not null 查询索引的列

IS NULL 可以使用索引，但是 IS NOT NULL 无法使用索引

查询优化

优化数据访问

查询性能低下最基本的原因是访问的数据太多。对于低效的查询，会给服务器带来额外的I/O、内存和CPU的消耗。可以通过以下两个步骤分析：

• 确认应用程序是否检索大量不需要的数据。意味着访问了太多的行，有时候也可能访问了太多的列
• 确认 MySQL 服务器是否在分析大量超过需要的数据行。

1、是否向数据库请求了不需要的数据

总是 select * 取出全部的列，多表关联时返回全部列，会让优化器无法完成覆盖索引扫描这类的优化。

2、是否扫描额外的记录

理想情况下扫描的行数跟返回的行数应该是相同的。

Explain 结果中 rows 列也显示了 MySql 预估需要访问的数据行。一个合适的索引能让 MySQL 以最高效、扫描行数最少的方式找到需要的记录。

当 Extra 列显示 Using where 的时候，表示 MySQL 服务层将通过 WHERE 条件来筛选存储引擎返回的记录。

3、重构复杂的关联查询

对于一个大查询我们需要”分而治之“，将大查询分为小查询，每个功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

删除旧数据时如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据，占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的 DELETE 语句切分成多个较小的查询可以尽可能小地影响 MySQL 性能。

很多高性能的应用都会对关联查询进行分解。可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。

mysql
# 示例：
    select * from tag 
        join tag_post on tag_post.tag_id = tag.id 
        join post on tag_post.post_id = post.id
        where tag.tag = 'mysql'
    
# 可以插接成下面这些查询来代替：
    select * from tag where tag = 'mysql'
    select * from tag_post where tag_id = 1234
    select * from post where post.id in (123,456,9098,8904)

4、MySQL 是如何优化和执行查询的

![MySQL服务器的逻辑架构][7]

MySQL服务器的逻辑架构:

• 第一层：处理客户端连接、授权认证等
• 第二层：服务器层，负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等
• 第三层：存储引擎，负责MySQL中数据的存储和提取。

![MySQL 执行一个查询的过程][8]

MySQL 执行一个查询的过程:

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回缓存中的结果
3. 服务器端进行 SQL 解析、预处理、再由优化器生产对应的执行计划
4. MySQL 根据优化器生产的执行计划，调用存储引擎的API来执行查询
5. 将结果返回给客户端

5、优化特定类型的查询

1）COUNT()的作用和优化

可以统计某个列值的数量，在统计列值时要求列值是非空的（不统计 NULL ）

如果在 COUNT() 的括号中指定了列的表达式，则统计的就是这个表达式的有值的结果数

COUNT() 的另一个作用就是统计结果集的行数，最简单的就是当我们使用 COUNT() 的时候，实际上，它会忽略所有的列而直接统计所有的行数。如果希望知道的是结果集的行数，最好使用 COUNT()，这样写意义清晰，性能也会很好。

最常见的错误：在括号内指定 列 却希望统计结果集的行数。

MyISAM 的 COUNT(*) 函数非常快，不过前提是没有任何的 WHERE 条件，因为此时无需实际地去计算表的行数，MySQL 可以利用存储引擎的特性直接获取这个值

简单优化：

可以直接使用 MyISAM 在 COUNT(*) 全表非常快的这个特性。

也可以条件反转查询行数，比如id大于5的城市数量，可能有几千条，我们取总数减去小于5的城市就能得到同样的结果，而且将扫描的行数减少到5以内

假设可能需要通过一个查询返回各种不同颜色的商品数量： SELECT SUM(IF(color = 'blue',1,0)) AS blue, SUM(IF(color = 'red',1,0)) AS red FROM items;

也可以使用 COUNT() 而不是 SUM(),满足条件设置为真，不满足设置为NULL即可。 SELECT COUNT(color = 'blue' OR NULL) AS blue,COUNT(color = 'red' OR NULL) AS red FROM items;

某些业务场景并不要求完全精确的 COUNT值，此时可以使用近似值来代替。EXPLAIN 出来的优化器估算的行就是一个不错的近似值，执行 EXPLAIN 并不需要真正的去执行查询。

更复杂的优化：

通常来说，COUNT() 都需要扫描大量的行才能获取精确结果，因此很难优化

除了前面的方法， 在 MySQL 层面还能做的就是使用覆盖索引扫描了

还不够的话，就需要考虑修改应用的架构，可以添加汇总表，或者增加类似 Memcached 这样的外部缓存系统。

2）优化关联查询

确保 ON 或者 USING 子句上有索引，在创建索引的时候就要考虑关联的顺序。

一般来说，除非有其他理由，否则只需要在关联循序中的第二个表的相应列上创建索引。

3）GROUP BY 和 DISTINCT 优化

Mysql 优化器会在内部处理的时候相互转化这两类查询，当无法使用索引的时候，GROUP BY 使用两种策略来完成：使用临时表或者文件排序来做分组。

如果没有通过 ORDER BY 子句显式的置顶排序列，当查询使用 GROUP BY 子句的时候，结果集会自动按照分组的字段来进行排序。如果不关心结果集的顺序，而这种默认排序又导致了文件排序，则可以使用 ORDER BY NULL， 让 MySQL 不再进行文件排序。

4）其他

【推荐】表的命名最好是加上“业务名称_表的作用”。

正例:alipay_task / force_project / trade_config

【推荐】表的命名最好是加上“业务名称_表的作用”。

【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只 出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。 说明

在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、 表名、字段名，都不允许出现任何大写字母，避免节外生枝。

正例:aliyun_admin，rdc_config，level3_name 
反例:AliyunAdmin，rdcConfig，level_3_name

【强制】使用 ISNULL()来判断是否为 NULL 值。

说明: NULL 与任何值的直接比较都为 NULL。
1)  NULL<>NULL的返回结果是NULL，而不是false。 
2)  NULL=NULL的返回结果是NULL，而不是true。 
3)  NULL<>1的返回结果是NULL，而不是true。

【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

说明:如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

---

库表优化

选择优化的数据类型

更小的通常更好

应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少

但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型

类型简单就好

简单数据类型的操作通常需要更少的CPU周期，例如：

1、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂，能用整型的就不要用字符类型

2、使用Mysql 内建的类型而不是字符串来存储日期和时间

3、用整型存储IP（IPv4地址实际上是32位无符号整数，不是字符串，使用小数点将地址分成4段只是为了让人们阅读容易。所以应该使用无符号整数存储 IP地址）MySQL 提供 INET_ATON() 和 INET_NTOA() 函数在这两种表示方法之间转换

尽量避免 NULL

通常情况下可为 NULL 的列改为 Not NULL带来的性能提升比较小，所有（调优时）没有必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题。

如果计划在列上建索引，就应该避免设计成可 NULL的列，因为可为 NULL 的列使得索引、索引统计和值比较都更加复杂

MySQL数据类型

整数类型：

tinyint（2^8）、 smallint（2^16）、 mediumint（2^24）、 int（int类型是 2^32次方，42亿数值完全够用，占用空间是4字节） bigint（2“64次方，占用空间 8字节)。存储数据的大小是相同的，但是类型不同表空间实际占用的内存空间大小也不同

|数据类型 |空间(Bytes)|带符号最小值|带符号最大值|不带符号最小值|不带符号最大值| |:--------:|:-------------:|:------:|:----------:|:----------:|:------------:|:------------:| |TINYINT |1 |-128 |127 |0 |255 | |SMALLINT |2 |-32768 |32767 |0 |65535 | |MEDIUMINT |3 |-8388608 |8388607 |0 |1677215 | |INT |4 |-2147483648 |2147483648 |0 |4294967295 | |BIGINT |8 |$-2^{63}$ |$2^{63}-1$ |0 |$2^{64}$ |

数据范围：带符号 最小值~最大值公式

$-2^{N-1}$ ~ $2^{N-1}-1$

数据范围：不带符号 最小值~最大值公式

0 ~ $2^{N}-1$

实数（浮点数）类型：

FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算

DECIMAL 类型用于存储精确的小数，可以指定小数点前后所允许的最大位数，这会影响列的空间消耗.每4个字节存9个数字，例如 DECIMAL(18,9)，小数点两边各存储9个数字，一共使用9个字节。小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节

浮点类型在存储同样范围的值时，通常比 DECIMAL 使用更少的空间。 FLOAT 使用4个字节存储，DOUBLE 占用8个字节。

应该尽量只在对小数进行精确计算时才使用 DECIMAL，例如：存储财务数据可考虑使用 BIGINT 替代 DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。这样可以避免浮动存储计算不精确和 DECIMAL 精确计算代价高的问题。电商场景中，金额的精度计算，单位精确到 ‘分’存储，可以把100作为倍数，金额乘以100，以 ‘分’ 为单位，存储整数类型

字符串类型：

VARCHAR

类型用来存储可变长度字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间。 需要使用1或2个额外字节记录字符串的长度，如果长度小于或等于255字节，则使用1个字节，否则使用2个字节。

CHAR

类型是定长的，MySQL 根据定义的字符串长度分配足够的空间，当存储 CHAR 值时，会删除所有的末尾空格。适合存储很短的字符串，或者所有值都同一个长度。例如：存储MD5值、使用 CHAR(1) 来存储 Y 和 N 的值。

大字段类型：

BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储

避免使用 BLOB 和 TEXT 类型，如果必须用，也要单独设计表存储

日期和时间类型：

DATE 类型，TIME 类型，YEAR 类型

日期类型 DATETIME 类型，展现的形式为：YYYY-MM-DD HH:MM

，与时区无关，使用8个字节的存储空间

日期类型 TIMESTAMP 类型， 只使用4个字节的存储空间，从 '1970-01-01 00:00:00' 开始，上限到 '2038-01-19 03:14:07'，显示值依赖于时区

除特殊行为之外，通常也应该尽量使用 TIMESTAMP , 因为它比 DATETIME 空间效率更高。

问题：

使用整数类型 INT(10) 和 INT(20) 有什么区别？

MySQL 可以为整数类型指定宽度，例如 INT(10)，对大多数应用这是没有意义的，它不会限制值的合法范围，对于存储与计算来说 INT(10) 和 INT(20) 是相同的

使用 VARCHAR(5) 和  VARCHAR(200) 存储 ‘hello’ 的空间开销是一样的，那么使用更短的列有什么优势吗？

有很大的优势。更长的列消耗更多的内存，因为 MySQL 通常分配固定大小的内存块来保存内部值，尤其内存临时表进行排序或操作时会特别糟糕，所以最好的策略是只分配真正需要的空间。

总结：

• 尽量避免过度设计，导致复杂查询的 schema 设计，或者有很多列的表设计
• 使用小而简单的合适的数据类型，除非真实数据模型中有确切的需要，否则应该尽可能的避免使用 NULL 值
• 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列
• 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存
• 尽量使用整型定义标识列
• 避免使用 MySQL 已经遗弃的特性，例如：指定浮点数的精度，或者整数的显示宽度
• 小心使用 ENUM 和 SET，避免使用 BIT