看了会变强的 MySQL 知识汇总

前言

该汇总参考的是 MySQL 官方8.0的使用手册，以及《高性能 Mysql》第三版，关于学习 MySQL 最好的方法就是搞懂原理然后实践。

基础知识

1、MySQL 是由 C、C++ 编写
2、MySQL 可指定错误提示语言，但是没有中文
3、注释的两种方法：# 和 --
4、命令行模式，在多行输入需要取消时可以输入 \c
5、命令行如果想保存登录密码，可以在 -p 后不加空格直接追加密码，本地测试环境可以这么干
6、命令行模式，在查询语句以 \G 结尾，可以让查询结果的字段垂直显示
7、MySQL 由客户端-服务端程序组成，mysqld 是服务端核心服务，mysqld_safe 是 mysqld 的安全启动方式，类似守护进程，在 mysqld 的程序挂掉时可以自动拉起来，mysql.sever 是 MySQL 服务器启动脚本，常用的命令有

mysql.server {start|stop|restart|reload|force-reload|status}

8、MySQL 可单独设置时区，默认使用的是系统时区
9、安全更新模式 --safe-updates，该模式可以限制批量更新记录时必须设置 where 查询条件，如果没有会报错，另外还可以限制批量查询返回的集合上限，设置方法为

 set sql_safe_updateds=1, sql_select_limit=1000, max_join_size=100000;

10、MySQL 在连接服务端时会产生缓存，除了本机回环地址 127.0.0.1 以外的所有 ip 地址都会缓存，以此减少 DNS 查询。连接错误时会记录阻塞错误，当连续错误次数达到 max_connect_errors 后会拒绝连接，如果想恢复访问，只能清除缓存，或是等待 MySQL 缓存淘汰机制生效
11、MySQL 有调试模式，打开时后可以查看数据字典
12、MySQL 可以设置日志保存类型 log_output 是文件（FILE）、表格（table），还是 NONE，可以同时指定 3 种参数，但是如果有 NONE 会优先生效，也就是不记录日志，所以通常打开慢查询日志之前要确保日志可以记录
13、Mysql 开启慢查询日志的方式有两种，会话开启和配置开启，前者只在当前会话生效服务重启后失效，后者是全局并且永久生效，同时可以设置超时时间，支持微秒级别，文件默认保存在数据目录中，以 host-name-slow.log 命名，可以手动修改文件保存位置和名称，命令如下

# 会话开启
slow_query_log=on;

#设置最大执行时间
long_query_time=1;

#日志保存路径
slow_query_log_file=/var/www/log/test.log

14、MySQL 安装时可使用安全安装 mysql_secure_installation 会要求设置 root 密码、删除 root 本机以外的访问账户，删除匿名账户、删除 test 数据库
15、MySQL 字符集后缀 _ci 表示不区分大小写，utf8 和 utf8mb4 对比，前者支持 3 字节，后者支持 4 字节，后者可以支持存储中文复杂字、emoji
16、MySQL 配置外网连接，第一先创建用户：create user ‘root’@‘指定ip或是%无限制‘ identified by ‘密码’;
第二授权：grant all on . to ‘root’@‘指定ip或是%无限制’ with grant option; 第三步修改 MySQL 配置文件把绑定12.0.0.1的配置注释
17、mysqladmin 是一个 MySQL 管理客户端，不需要进入 MySQL 就可以管理
18、mysqlcheck 是一个 MySQL 表检查、修复、优化、分析的客户端，执行检查时会加读锁
19、mysqldumpslow 可以用来查看慢日志，结果是汇总处理过的数据
20、mysqldump 是备份的客户端，但不会备份日志

进阶知识

• 存储引擎

1、常用的存储引擎为 Innodb 和 Myisam，其中以 Innodb 最为流行
2、Innodb 和 Myisam 最重要的区别为

引擎	表锁	行锁	事务	外键	自动崩溃恢复	热备份
Myisam	支持	不支持	不支持	不支持	不支持	不支持
Innodb	支持	支持	支持	支持	支持	支持

注：Myisam 在备份时会对加表锁，阻塞写入操作，所以不支持热备份

3、事务是一组原子性的 SQL 查询，事务内的语句要不全部执行成功，要不全部失败，但是前提是事务内操作的表引擎需要全部支持事务，并且事务内要避免混用引擎。事务的特点是，原子性、一致性、隔离性、持久性

4、Innodb 采用 MVCC 来支持高并发，并且实现了四个标准的事务隔离级别，分别是 READ UNCOMMITTED (读未提交)、READ COMMITTED (读已提交)、REPEATABLE READ (可重复读)、SERIALIZABLE (串行化)，默认事务是可重复读

5、Innodb 的四种事务隔离级别对于数据一致性的影响

事务	脏读	不可重复读	幻读
READ UNCOMMITTED (读未提交)	是	是	是
READ COMMITTED (读已提交)	否	是	是
REPEATABLE READ (可重复读)	否	否	是
SERIALIZABLE (串行化)	否	否	否

脏读： 事务读取的数据为其它事务更新但未提交的数据，因为其它事务可能会回滚，导致数据不一致
不可重复读： 事务只会读取已经提交的修改，这会造成在一个事务中两次执行同样的查询，可能会得到不一样的结果
幻读： 事务在同一个查询中不同时间得到了不同的行集

6、MySQL 锁的类型有很多种，不只是表锁和行锁，还存在意向锁、间隙锁、下一键锁等，这里面有的锁是显性，有的是隐性，有的是用户控制，有的是系统维护，所以理解 MySQL 的锁并不是一个简单的事情。

7、记录锁也叫行锁分为 共享锁(s) 和 排他锁(x)，共享锁允许事务持有读取，独占锁允许事务持有更新，当事务持有记录的独占锁时，其它事务必须要等待，如果超过事务等待时间还未拿到锁会提示超时。

8、意向锁是一种表级锁，由系统隐性维护，主要是用来表明有人正在锁定表或表中的行记录，当一个事务执行了锁定时，另一个事务如果要给表加锁，就不需要去获取具体的锁，通过意向性锁就可以知道表是否可以加锁。

9、间隙锁是在索引记录的间隙使用的锁，以下例子会锁定 id 1～10的记录

select * from logs where id between 1 and 10; 

10、下一键锁

11、插入意向锁

12、AUTO-INC锁

13、死锁是指多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶行循环的现象，MySQL 实现了死锁检测和死锁超时机制，Innodb 处理死锁的方式是，将拥有最少插入、更新或删除行数的事务回滚。

# 死锁检测默认开启
innodb_deadlock_detect=on
 
# 死锁默认超时时间
innodb_lock_wait_timeout=50

14、查看死锁的方法，开启性能模式，通过 data_locks，data_lock_waits 查看锁定信息，取代了8.0版本之前 information_schema.innodb_lock 和 information_schema.innodb_lock_waits

15、从绝对意义上消除死锁是不可能的，但是可以通过一些手段尽量降低死锁的发生概率

• 对于相同的程序尽量以相同的顺序访问表
• 避免使用大事务，尽量拆分成小事务，大事务占用资源多，时间长，与其它事务产生冲突的概率就大
• 为表添加合适的索引，减少数据查询的无用数据锁定

16、行锁的优点是锁定粒度小，发生锁冲突的概率低，并且对并发的支持度高，缺点是锁的开销会变大

17、乐观锁和悲观锁不是一种锁，而是一种程序设计思想，乐观锁假设在事务执行前预期不会有其它事务对数据修改不会发生冲突，只在最终事务提交时检测数据是否被修改，悲观锁时在事务执行前就预期数据会被其它事务修改，提前对数据进行锁定，避免其它事务修改数据。乐观锁的适用场景偏向读多，悲观锁则是写多。

• 乐观锁可以通过对记录加版本号或时间戳来实现，在记录查询时获取版本号，只要记录发生变更就更新版本号，在最终提交时检测版本号是否为最初查询的，如果不一致就无法更新
• 悲观锁可以通过给记录添加排他锁来实现

• 数据类型

1、优化数据类型通用原则

• 选择符合业务的最小数据类型，更小的数据类型占用更少的磁盘、内存和 CPU 缓存，并且处理需要的 CPU 周期更少
• 简单就好，这样可以需要更少的 CPU 周期，例如存储 IP 地址转为整型会比适用字符串更高效
• 尽量避免使用 NULL，NULL 的存储会消耗更多的存储空间，并且在索引统计时增加搜索难度
  -指定合适的数据宽度，更长的宽度会消耗更多的内存，因为 MySQL 通常会分配固定大小的内存块来保存内部值，在用内存对临时表进行排序或操作时影响会很大

2、整数类型在设置时，可以指定宽度，例如 int(11)，这对大部分应用是没有意义的，它不会限制值的合法范围，只对填充零有影响，例如现在存储的值为1，选择填充零会变为 0000000001。

3、varchar 和 char 是最主要的字符串类型，varchar 类型用于存储可变长字符，char 类型用于存储定长字符，数据宽度的设置在这里是有意义，指定多长宽度，就最多存多少字符。

这里要特别声明【字符】和 【字节】是不等的，字符代表字符串的长度，字节代表该数据结构存储字符需要的字节单位，影响字节数的因素是字符集配置，例如：字符集 utfbmb4 使用的是 4 字节存储，utfb8 使用 3 字节存储，同一个字符串“我爱祖国”，前者需要 16 个字节存储，后者需要 12 个字节。

varchar 的宽度范围是 0 ~ 65535，但实际能设置的最大宽度是有限制的，MySQL 内部对于行是有 65535 字节最大限制的，也就是说所有列的最大字节数总和是不能超过这个值的。除了 MySQL 自身的限制，数据库引擎也会对行大小产生限制，Innodb 的默认页大小 innodb_page_size 为 16KB, 行大小不能超过它的一半值，但 64KB 页面限制小于 16KB。varchar 默认会有另外的字节存储字节长度，如果值小于 255 字节，使用 1 个字节存储，反之使用 2 个字节存储。

char 的最大宽度为255，默认可以保存末尾空格，但在取出时，会自动删除末尾空格。

4、Blob 和 Text 都是为存储很大的数据而设计的字符串类型，分别采用二进制和字符串方式存储，与其他类型不同，MYSQL 把这两种数据格式当成一个独立的对象存储，Innodb 会有专门的外部存储区域来进行存储，此时每个值在行内需要1～4个字节存储一个指针，然后在外部区域存储实际的值。这两个类型在列中只占 9 ~ 12 字节。

5、日期时间常用的格式为 datetime 和 timestamp，前者为 8 字节存储，时间值为存储的真实值，但是时间范围是 1001～9999，后者为 4 字节存储，时间值在取出时根据时区显示，时间范围是 1970～2038。

• 数据库设计的范式和反范式

1、范式是 符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度，而数据库库范式就可以理解为创建表时的一种设计原则，数据库范式有六种：第一范式、第二范式、第三范式、巴斯-科德范式、第四范式 和 第五范式，但通常只需要用到前三范式即可。

第一范式：每个列的数据为最小单位，不可拆分
第二范式：在第一范式基础上，确保每个非主键列都依赖于主键，而不是主键的一部分（联合主键）
第三范式：在第二范式基础上，确保每个非主键列不相互依赖

2、范式的优点和缺点

【优点】

• 更新操作快
• 表数据冗余小
• 表体积小，可以更好的放在内存里，执行速度会更快

【缺点】

• 通常需要关联，有时会造成很大的开销

3、反范式的优点和缺点

【优点】

• 通常不需要关联，查询效率更高

【缺点】

• 更新操作开销大
• 造成大量冗余

• 索引

1、索引是存储引擎用于快速找到记录的一种数据结构。

2、索引的方式分为：B-Tree 索引和 哈希索引

• MyISAM 使用前缀压缩技术使得索引更小，但 Innodb 则按照原数据格式进行存储
• B-Tree 索引适用于全键值、键值范围或前缀查找，索引从最左侧开始查找，否则无法使用索引，不能跳过跳过索引的列
• 哈希索引只包含哈希值和行指针，不存储字段值，所以需要回表，哈希索引不是按照索引顺序存储的，因此无法排序，哈希索引也不支持部分索引列匹配查找

3、索引类型有普通索引、唯一索引、全文索引、空间索引。

4、索引的优点

• 减少了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机 I/O 变为顺序 I/O

注：索引并不总是最好的工具，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的，对于非常小的表，大部分情况下全表扫描更高效，对于特大型表，例如 TB 级别的数据，建立索引和维护的成本会很高，在这种情况下块级别元数据技术比索引更有效。

5、高性能的索引策略

• 索引列不能是表达式的一部分，也不能是函数的参数

• 设置选择性高的列作为索引，索引选择性的计算方法为：不重复的索引值（基数）和数据表的记录数的比值，该值越高，代表索引查询效率越高，因为可以让 MySQL 在查询时过滤掉更多的行

• 对于 BLOB、TEXT 或者很长的 VARCHAR 类型，必须使用前缀索引，因为 MySQL 不允许这些列的完整长度，但是前缀索引无法使用 order by 和 group by，也无法使用前缀索引做覆盖索引

• 避免在多个列上建立单独的索引，这样并不能提高查询效率，例如 explain select from_id,scene from logs where from_id=1 and status =1, 这两个字段分别建立单独索引，查看执行计划可以发现 TYPE 为 index_merge，这是 MySQL 使用了索引合并策略，通常遇到这种情况说明索引该优化了

• 联合索引遵循最左匹配原则，索引查找需要从最左侧开始，确定索引顺序非常重要，通常将选择性最高的列放到索引最前列，联合索引最大可选择 16 列

• 聚簇索引并不是一个单独的索引类型，而是一种数据存储方式，聚簇索引的数据行存储在索引的叶子页，聚簇索引通常是表的主键，但是表如果没有主键，会默认使用第一个非NULL唯一索引，如果唯一索引也没有，Innodb 会生成一个隐式的聚簇索引，通过聚簇索引访问行的速度很快，因为索引直接指向包含行数据的页面，另外聚簇索引的列不能有 NULL 值

• 二级索引是聚簇索引以外的索引，在 Innodb 中二级索引的每条记录都包含该行的主键列，以及为二级索引指定的列，如果主键很长，二级索引占用的空间就更多，所以主键越短越好

• 覆盖索引是通过索引直接获取列的数据，这样就不需要回表了，覆盖索引必须要存储索引的值，而哈希索引、全文索引、空间索引都不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆盖索引

• MySQL 有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描，如果 EXPLAIN 出来的 type 列的值为 index， 则说明 MySQL 使用了索引扫描来做排序，如果索引不能覆盖查询所需的全部列，那就需要再次回表查询一次对应的行，这基本都是随机 I/O，在 I/O 密集型的工作负载时，速度不如全表扫描，所以索引设计时最好能同时满足查询和排序

• 冗余和重复的索引是指在相同列上创建多个索引，例如在 A 列上既有（A,B）索引，也有（A）索引，冗余索引是否需要要根据实际场景，如果扩展原有的索引会导致其变的太大，从而影响到其它使用该索引的查询，可以考虑冗余索引

• 联合索引的创建要根据选择性来确定，但是有的列的选择性很低，但又很高频，还是可以使用的，例如：性别（sex）、国家（country）和 登录状态（status）这种字段，如果建立联合索引就会有很多组合，（sex,country,status），（country,sex,status) 等，如果按不同的查询场景建立多个索引是不合适的，可以使用一种 in 查询的方法绕过限制，比如按照 (sex,country,status) 创建的索引，只想查国家，我们可以对 sex 字段使用 in 查询，in（男,女）来让 Innodb 使用到索引，当然这种方法只适合 in 中数据少的情况

• 查询性能优化

1、查询性能优化关注的应该是查询的响应时间，提升响应时间是优化的基本思路

2、查询性能低下最基本的原因是访问的数据太多，可以通过以下两个步骤来分析

• 确认应用程序是否在检索大量超过需要的数据（应用层）
• 确认 MySQL 服务器层是否在分析大量超过需要的数据行（服务层）

3、检查是否向数据库请求了不需要的数据，关于不需要的数据有以下定义

• 只需要用到少量行，但是查询了所有行然后再从中取出需要的行
• 多表关联时返回所有的列
• 单表 select * 查找所有的列
• 重复查询相同的数据

4、检查 MySQL 是否在扫描额外的记录，衡量指标有：响应时间、扫描的行数、返回的行数

• 响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多少时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间，可能是等待 I/O 操作完成，也可能是等待行锁。

• 关于使用复杂查询还是多个单个查询，从很早我听到的建议就是尽量使用复杂查询，但在如今服务器带宽和性能都得到大幅提升，已经不需要再拘泥于这种约束，可以使用多个小查询替代复杂查询，小查询还有以下的优势

1、更加方便的做缓存
2、减少锁的竞争
3、在应用层做关联，可以更容易地数据库进行拆分，更容易做到高性能和可扩展
4、查询本身效率也可能得到提升，比如 laravel 的模型关联并不是通过 Join 去连表，而是通过 in 来查询

5、MySQL 执行查询的步骤

1、客户端发送一条查询给服务器
2、服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划
3、MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询
4、将结果返回给客户端

6、MySQL 客户端和服务器的通信协议是“双半工”，关于通信的方式可以分为：单工通信、半双工、全双工

• 单工通信就是接收端和发送端已经固定，接收端只能接收数据，发送到只能发送数据，线路上的数据流永远是单向的，例如电视遥控器
• 半双工通信就是在同一时刻，线路上只允许一个方向上的数据传输
• 全双工通信就是在同一时刻，线路上允许两个方向上的数据传输，例如打电话

MySQL 半双工的通信方式让通信变得简单，但也从其它方面限制了 MySQL，一个明显的限制是，无法进行流量控制。一旦一端开始发送消息，另一端要接收完整消息才能进行响应，MySQL 客户端用一个单独的数据包将查询传递给服务器，当查询语句很长时，参数 max_allowed_packet 就很重要了。

7、MySQL 线程连接状态，更多的线程状态可以查看 MySQL线程状态

• sleep 线程正在等待客户端发送新的请求
• query 线程正在执行查询或者正在将结果发送到客户端
• locked 在 MySQL 服务器层，该线程正在等待表锁，在存储引擎级别实现的锁，并不会体现在线程状态中

8、count() 查询
count() 是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值的数量，也可以统计行数。在统计列值时会过滤掉 NULL 值，另外用 count(*) 时可以统计查询到的记录总行数，在这种情况下通配符 * 不会扩展成所有的列，它会忽略所有的列而直接统计所有的行数，并不会如网上所说对性能有影响

9、MyISAM 的 count 查询总行数的速度非常快，这是因为 MyISAM 内部维护有总行数，但是还有个前提，即查询没有任何的 where 条件，因为此时不需要去计算实时的行数，如果需要实时计算总行数，那和其它的引擎并没有区别