MySQL重要知识点（总结）

最近一段时间都学习mysql，将重要的知识点总结如下：

• 一、字段、表、索引设计规范相关
• 二、事务相关
• 三、锁相关
• 四、存储引擎相关
• 五、大表优化相关
• 六、索引优化相关
• 七、语句优化相关

一、字段、表、索引设计规范

1、字段设计规范

① 字段类型优先选择符合存储需要的最小类型

字段类型优先级：整型>date;time >enum>char;varchar>blob

原因：整型，time运算快，节省内存；enum列内部是用整型存储的，char，varchar要考虑字符集的转换和排序的校对集，速度慢；blob无法使用临时表。

② 够用就行（如smallint，varchar(N)）

原因：大的字段浪费内存，影响速度，如varchar(10),varchar(300),虽然存储的内容一样，但是,在表联查时，varchar(300)要花更多内存

③ 尽量避免使用允许为null()

原因：null不利于索引，要用特殊的字节标注，在磁盘上占的空间其实更大
例子：建两个相同字段的表，一个允许为null，一个不允许。可以发现为null的索引更大些。

null 测试 .PNG

④ 避免使用ENUM类型

修改ENUM值需要使用ALTER语句
ENUM类型的ORDER BY操作效率低，需要额外操作
禁止使用数值作为ENUM的枚举值

⑤ 使用TIMESTAMP（4个字节）或DATETIME类型（8个字节）存储时间

TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07
TIMESTAMP 占用4字节和INT相同，但比INT可读性高
超出TIMESTAMP取值范围的使用DATETIME类型存储

⑥ 金额类数据使用decimal

Decimal类型为精准浮点数，在计算时不会丢失精度
占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节
可用于存储比bigint更大的整型数据

2、表设计规范

① 定长与变长分离

核心且常用字段,宜建成定长,放在一张表.
而varchar, text,blob,这种变长字段,适合单放一张表, 用主键与核心表关联起来.

② 常用字段和不常用字段要分离.

需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来.

3、索引设计规范

① 限制每张表中索引的数量，建议单张表中的索引不超过5个

索引并不是越多越好！索引可以提高效率同样可以降低效率
因为mysql优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加mysql优化器生成执行计划的时间，同样会降低查询性能

② 禁止给表的每一列都建立单独索引

5.6版本之前，一个sql只能使用到一个表中的一个索引，5.6以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好

③禁止给表的每一列都建立单独索引

5.6版本之前，一个sql只能使用到一个表中的一个索引，5.6以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好

④每个Innodb表必须有一个主键

Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的
每个表都可以有多个索引，但是表的存储顺序只能有一种Innodb是按照主键索引的顺序来组织表的，不要使用更新频繁的列作为主键
不要使用UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长），主键建议使用自增ID值

二、事务

事务保证一组原子性的操作，要么全部成功，要么全部失败。一旦失败，回滚之前的所有操作。MySql采用自动提交，如果不是显式的开启一个事务，则每个查询都作为一个事务。

1、事务的四大特性

①原子性

事务的原子性，要么全部完成，要么完全不起作用

②隔离性

并发访问数据库时，一个用户的事务不被其他事务所干扰，数据库中各个事务相互独立

③持久性

一个事务被提交后，改变是持久的

④一致性

执行事务前后，数据保持一致，多个事务对同一个数据库读取的结果是相同的

2、并发事务带来哪些问题

• 脏读（Dirty read）:

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

• 丢失修改（Lost to modify）

指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

• 不可重复读（Unrepeatableread）

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

• 幻读（Phantom read）

幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别：不可重复读的重点是修改，幻读的重点在于新增或者删除

3、事务的隔离级别有哪些

• READ-UNCOMMITTED(读取未提交)

最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

• READ-COMMITTED(读取已提交)

允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

• REPEATABLE-READ(可重复读)

对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE(可串行化)

最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看

当前数据库的隔离级别

这里需要注意的是：与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的SERIALIZABLE(可串行化)隔离级别。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

三、锁

数据库通过锁机制来解决并发场景-共享锁（读锁）和排他锁（写锁）。读锁是不阻塞的，多个客户端可以在同一时刻读取同一个资源。写锁是排他的，并且会阻塞其他的读锁和写锁。简单提下乐观锁和悲观锁。

• 乐观锁：通常用于数据竞争不激烈的场景，多读少写，通过版本号和时间戳实现。

• 悲观锁：通常用于数据竞争激烈的场景，每次操作都会锁定数据。

要锁定数据需要一定的锁策略来配合。

• 表锁：锁定整张表，开销最小，但是会加剧锁竞争。

• 行锁：锁定行级别，开销最大，但是可以最大程度的支持并发。

但是MySql的存储引擎的真实实现不是简单的行级锁，一般都是实现了多版本并发控制（MVCC）。MVCC是行级锁的变种，多数情况下避免了加锁操作，开销更低。MVCC是通过保存数据的某个时间点快照实现的。

四、存储引擎

1、查看MySQL当前默认的存储引擎

我们也可以通过下面的命令查看默认的存储引擎。

mysql> show variables like '%storage_engine%';

查看表的存储引擎

通过下面的命令查看表的存储引擎

show table status like "table_name" ;

2、 MyISAM和InnoDB区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃回复问题的话）。

① 功能差异

② 存储差异

③ 选择差异

这块详细介绍可参考：我的另一篇《优化流程图》

五、大表优化当MySQL

单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下

1. 限定数据的范围

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

2. 读/写分离

数据量增多，单机的数据库不足以支撑业务，需要用到数据库集群。而读写分离，就是将数据库的读和写分离，对应到数据库一般就是主从数据库，一主一从或者一主多从；业务服务器把数据写到主数据库中，读操作都去从库读；主库会同步数据到从库，保证数据的一致性。

主从集群

这种集群方式，就是将访问的压力从主库转移到从库，单机的数据库不能支撑并发读写的时候，而且读的请求很多的情况下就适合数据库集群。如果写的操作很多的话，那就不适合这种集群方式，因为写的时候是写入主库，主库的压力还是没有变化，同时同步到从库也需要消耗资源。

单机的时候，一般数据库优化就是加索引，但是加了索引对查询有优化，但是写入的时候会有影响，因为写入的数据库不会更新索引。所以在主从数据库中，可以单独的对读库（从数据库）做索引，而写库（主数据库）可以减少缩影而提高写的效率。

但是主从数据库中需要注意：主从同步延迟、分配机制的考虑

①主从同步延迟

• 二次读取

一般操作是，在从库读取数据时，没有读到数据，就去主库进行数据读取。但是这种操作还是将读的压力返还给主库，如果有恶意的攻击，主库就爆了。
一般情况下，通过对数据库访问的API进行封装就能实现这个功能，业务之间没有耦合。

• 写了之后的马上读操作操作主库

在写了数据后，立马读操作就去访问主库，之后的读操作访问从库，这种业务上会有高度耦合。

• 根据业务，将重要的业务数据的读写都放在主库，其他的业务进行读写分离

类似付钱的这种业务，读写都到主库，避免延迟的问题，但是例如改个头像啊，个人签名这种比较不重要的就读写分离，查询都去从库查。

② 分配机制的考虑

分配机制的考虑，就是怎么制定去操作去主库写，去从库读。
*数据库中间件

有专门的独立系统来实现读写分离和数据库连接管理，业务服务器和数据库中间件通过SQL协议交流，在业务服务器看起来，数据库中间件就是一个数据库。

因为是通过sql协议的所以可以兼容不同的语言不需要单独写一套，并且有中间件来实现主从切换，业务服务器不需要关心这点。但是多了一个服务，就会有其他的消耗，难于管理，而且中间件性能要求高，实现复杂，难度高。

开源的中间件有Mysql Proxy,Atlas。

数据库中间件

读写分离理解和实现相对简单，但是只能减少访问的压力，不能分担存储的压力，当数据增多是，查询的速度还是会很慢。这时候就需要用到分库分表了。

正常情况下，单机不能支撑业务时，才会用数据库集群，软件设计越简单的设计越好。

一般数据库的优化是，先优化一些查询操作，然后优化业务的逻辑，或者加入缓存，最后不行再用集群，最后再分库分表。

3. 垂直分区

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。

垂直拆分

• 垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
• 垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

4、水平拆分

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分
水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

六、索引优化

1、常见索引列建议

① 出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列，包含在ORDER BY、GROUP BY、DISTINCT中的字段，多表join的关联列。并不要将符合1和2中的字段的列都建立一个索引， 通常将1、2中的字段建立联合索引效果更好。

• 举个栗子：
  在where条件常用的列上都加上索引
  例: where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品
  误: cat_id上,和, price上都加上索引.
  错: 只能用上cat_id或Price索引,因为是独立的索引,同时只能用上1个.

② 避免建立冗余索引、重复索引

③ 索引的列如果是表达式的一部分或者是函数的参数，则失效

④ 针对特别长的字符串，可以使用前缀索引，根据索引的选择性选择合适的前缀长度（详细参考：如何选择合适的前缀长度)

⑤ 使用多列索引的时候，可以通过 AND 和 OR 语法连接

⑥ 将范围查询放在条件查询的最后，防止范围查询导致的右边索引失效的问题

⑦ 索引最好不要选择过长的字符串，而且索引列也不宜为null

⑧ 对于频繁的查询优先考虑覆盖索引

2、选择索引列顺序

*区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
*尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）
*使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）
*举个栗子
在多列上建立索引后,查询哪个列,索引都将发挥作用
误: 多列索引上,索引发挥作用,需要满足左前缀要求.
以 index(a,b,c) 为例

索引使用情况

3、分析explain执行计划

EXPLAIN SELECT settleId FROM Settle WHERE settleId = "3679"

查看explain计划

• select_type，有几种值：simple（表示简单的select，没有union和子查询），primary（有子查询，最外面的select查询就是primary），union（union中的第二个或随后的select查询，不依赖外部查询结果），dependent union（union中的第二个或随后的select查询，依赖外部查询结果）

• type，有几种值：system（表仅有一行（=系统表），这是const连接类型的一个特例），const（常量查询）, ref(非唯一索引访问，只有普通索引)，eq_ref（使用唯一索引或组件查询），all（全表查询），index（根据索引查询全表），range（范围查询）

• possible_keys: 表中可能帮助查询的索引

• key，选择使用的索引

• key_len，使用的索引长度

• rows，扫描的行数，越大越不好

• extra，有几种值：Only index（信息从索引中检索出，比扫描表快），where used（使用where限制），Using filesort （可能在内存或磁盘排序），Using temporary（对查询结果排序时使用临时表）

七、语句优化建议

1. 建议使用预编译语句进行数据库操作

预编译语句可以重复使用这些计划，减少SQL编译所需要的时间，还可以解决动态SQL所带来的SQL注入的问题。
只传参数，比传递SQL语句更高效。
相同语句可以一次解析，多次使用，提高处理效率。

2. 避免数据类型的隐式转换

隐式转换会导致索引失效如:

select name,phone from customer where id = '111';

3. 充分利用表上已经存在的索引

避免使用双%号的查询条件。如：a like '%123%'，（如果无前置%,只有后置%，是可以用到列上的索引的）
一个SQL只能利用到复合索引中的一列进行范围查询。如：有 a,b,c列的联合索引，在查询条件中有a列的范围查询，则在b,c列上的索引将不会被用到。
在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的右侧，使用left join 或 not exists 来优化not in 操作，因为not in 也通常会使用索引失效。

4. 禁止使用SELECT * 必须使用SELECT <字段列表> 查询

原因：消耗更多的CPU和IO以网络带宽资源
无法使用覆盖索引
可减少表结构变更带来的影响

5. 避免使用不含字段列表的INSERT语句

如：
insert into values ('a','b','c');
应使用：
insert into t(c1,c2,c3) values ('a','b','c');

6. 避免使用子查询，可以把子查询优化为join操作

通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

7. 避免使用JOIN关联太多的表

对于Mysql来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。
在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大。
如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。
同时对于关联操作来说，会产生临时表操作，影响查询效率，Mysql最多允许关联61个表，建议不超过5个。

8. 减少同数据库的交互次数

数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率。

9. 对应同一列进行or判断时，使用in代替or

in 的值不要超过500个，in 操作可以更有效的利用索引，or大多数情况下很少能利用到索引。

10. 禁止使用order by rand() 进行随机排序

order by rand()会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO及内存资源。
推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

11. WHERE从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引

• 不推荐：

where date(create_time)='20190101'

• 推荐：

where create_time >= '20190101' and create_time < '20190102'

12. 在明显不会有重复值时使用UNION ALL 而不是UNION

UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
UNION ALL 不会再对结果集进行去重操作

13. 拆分复杂的大SQL为多个小SQL

大SQL逻辑上比较复杂，需要占用大量CPU进行计算的SQL
MySQL中，一个SQL只能使用一个CPU进行计算
SQL拆分后可以通过并行执行来提高处理效率