百味皆苦 java后端开发攻城狮
search clear

mysql高级知识点

2019-04-03
百味皆苦
mysql
 

存储引擎

  • 查看mysql提供的所有存储引擎mysql> show engines;
  • 查看默认存储引擎mysql> show variables like '%storage_engine%';
  • 查看表的存储引擎show table status like "table_name" ;
  • MySQL 当前默认的存储引擎是InnoDB,并且在5.7版本所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

MyISAM和InnoDB

  • 对比：
    • 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
    • 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
    • 是否支持外键： MyISAM不支持，而InnoDB支持。
    • 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。
  • InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看
  • InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。
  • 因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使用 REPEAaTABLE-READ（可重读） 并不会有任何性能损失。
  • InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

InnoDB

页介绍

  • InnoDB是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。
  • 真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。
  • 我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

行格式

  • 我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。

  • 4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式

  • 指定行格式语法：

    我们可以在创建或修改表的语句中指定行格式：

    CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
      
ALTER TABLE 表名 ROW_FORMAT=行格式名称

  • compact行格式

3vht0K.png

  • 一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分
  • 记录的额外信息：这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是变长字段长度列表、NULL值列表和记录头信息
  • 变长字段长度列表：我们知道MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型，我们也可以把拥有这些数据类型的列称为变长字段，变长字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来，所以这些变长字段占用的存储空间分为两部分：真正的数据内容和占用的字节数
  • 在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放
  • 如果该可变字段允许存储的最大字节数（M×W）超过255字节并且真实存储的字节数（L）超过127字节，则使用2个字节，否则使用1个字节。
  • 并不是所有记录都有这个 变长字段长度列表 部分，比方说表中所有的列都不是变长的数据类型的话，这一部分就不需要有。
  • null值列表：表中的某些列可能存储NULL值，如果把这些NULL值都放到记录的真实数据中存储会很占地方，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中
    • 首先统计表中允许存储NULL的列有哪些
    • 如果表中没有允许存储 NULL的列，则 NULL值列表 也不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列。二进制位的值为1时，代表该列的值为NULL，二进制位的值为0时，代表该列的值不为NULL
  • 记录头信息：除了变长字段长度列表、NULL值列表之外，还有一个用于描述记录的记录头信息，它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思
  • 记录的真实信息：记录的真实数据除了我们自定义的列数据以外，MySQL会为每个记录默认的添加一些列（也称为隐藏列）
  • InnoDB表对主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。
  • 模拟数据
mysql> SELECT * FROM record_format_demo;
+------+-----+------+------+
| c1   | c2  | c3   | c4   |
+------+-----+------+------+
| aaaa | bbb | cc   | d    |
| eeee | fff | NULL | NULL |
+------+-----+------+------+
2 rows in set (0.00 sec)

mysql>

3vLSvF.png

  • Redundant行格式
  • Redundant行格式是MySQL5.0之前用的一种行格式，也就是说它已经非常老了

3xh2IU.png

  • 模拟数据对应的存储格式

3x4tyR.png

  • Redundant行格式会把该条记录中所有列（包括隐藏列）的长度信息都按照逆序存储到字段长度偏移列表
  • 采用两个相邻数值的差值来计算各个列值的长度。
  • 。。。。。未完待续

InnoDB是如何存储数据的

  • 虽然数据保存在磁盘中，但其处理是在内存中进行的。为了减少磁盘随机读取次数，InnoDB 采用页而不是行的粒度来保存数据，即数据被分成若干页，以页为单位保存在磁盘中。InnoDB 的页大小，一般是 16KB。
  • 各个数据页组成一个双向链表，每个数据页中的记录按照主键顺序组成单向链表；每一个数据页中有一个页目录，方便按照主键查询记录。页目录通过槽把记录分成不同的小组，每个小组有若干条记录。有了槽之后，我们按照主键搜索页中记录时，就可以采用二分法快速搜索，无需从最小记录开始遍历整个页中的记录链表。

事务

  • 事务是逻辑上的一组操作，要么都执行，要么都不执行。
  • 四大特性：
    • 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
    • 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
    • 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
    • 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
  • 并发事务带来的问题：
    • 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
    • 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况
    • 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
  • 实际操作中使用到的一些并发控制语句:
    • START TARNSACTION：显式地开启一个事务。
    • COMMIT：提交事务，使得对数据库做的所有修改成为永久性。
    • ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

隔离级别

  • 设置隔离级别：SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

实际演示

  • 脏读（读未提交）

  • 避免脏读（读已提交）

  • 不可重复读（虽然避免了读未提交，但是却出现了，一个事务还没有结束，就发生了 不可重复读问题。）

  • 可重复读

  • 防止幻读（可重复读）

数据库三范式

第一范式（1NF）

  • 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只要数据库是关系型数据库，就自动的满足1NF。
  • 数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。
  • 如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息。
  • 关系型数据库: mysql/oracle/db2/informix/sysbase/sql server
  • 非关系型数据库:NoSql数据库: MongoDB/redis

第二范式（2NF）

  • 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。
  • 第二范式要求实体中没一行的所有非主属性都必须完全依赖于主键；即：非主属性必须完全依赖于主键。
  • 为什么要有主键？没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。
  • 其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。
  • 其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的。
  • 举例：学生信息组成学生表，姓名可以做主键么？不能！因为同名的话，就不唯一了，所以需要学号这样的唯一编码才行。那么其他字段依赖于主键是什么意思？就是“张三”同学的年龄和性别等字段，不能存储别人的年龄性别，必须是他自己的，因为张三的学号信息就决定了，这行记录归张三所有，不能给无关人员使用。

第三范式（3NF）

  • 满足第三范式（3NF）必须先满足第二范式（2NF）。
  • 第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键字段。
  • 就是各种信息只在一个地方存储，不出现在多张表中。就是要消除传递依赖，方便理解，可以看做是“消除冗余”。
  • 很多时候，我们为了满足第三范式往往会把一张表分成多张表。
  • 比如说大学分了很多系（中文系、英语系、计算机系……），这个系别管理表信息有以下字段组成： 系编号，系主任，系简介，系架构。
    • 学生信息表，张三同学的年龄、性别、学号都有了，我能不能把他的系编号，系主任、系简介也一起存着？如果你问三范式，当然不行，因为三范式不同意。
    • 因为系编号，系主任、系简介已经存在系别管理表中，你再存入学生信息表，就是冗余了。
    • 学生信息表中，系主任信息是不是依赖于系编号了？而这个表的主键可是学号啊！
    • 所以按照三范式，处理这个问题的时候，学生表就只能增加一个系编号字段。这样既能根据系编号找到系别信息，又避免了冗余存储的问题。

大表优化

  • 限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
  • 读写分离：经典的数据库拆分方案，主库负责写，从库负责读；
  • 垂直分区：根据数据库里面数据表的相关性进行拆分。简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。
    • 垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
    • 垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；
  • 水平分区：保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库
  • 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度
  • 数据库分片的两种常见方案：
    • 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。
    • 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。

分库分表主键处理

  • 因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。
  • 生成全局 id 方式：
    • UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
    • 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
    • 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
    • Twitter的snowflake算法
    • 美团的Leaf分布式ID生成系统

高性能优化建议

命令规范

  • 所有数据库对象名称必须使用小写字母并用下划线分割
  • 所有数据库对象名称禁止使用 MySQL 保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）
  • 数据库对象的命名要能做到见名识意，并且最后不要超过 32 个字符
  • 临时库表必须以 tmp_为前缀并以日期为后缀，备份表必须以 bak_为前缀并以日期 (时间戳) 为后缀
  • 所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）

设计规范

  • 所有表必须使用 Innodb 存储引擎：没有特殊要求（即 Innodb 无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用 Innodb 存储引擎。Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。
  • 数据库和表的字符集统一使用 UTF8：兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储 emoji 表情的需要，字符集需要采用 utf8mb4 字符集。
  • 所有表和字段都需要添加注释：使用 comment 从句添加表和列的备注，从一开始就进行数据字典的维护
  • 尽量控制单表数据量的大小,建议控制在 500 万以内：过大会造成修改表结构，备份，恢复都会有很大的问题。可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小
  • 谨慎使用 MySQL 分区表：分区表在物理上表现为多个文件，在逻辑上表现为一个表；谨慎选择分区键，跨分区查询效率可能更低；建议采用物理分表的方式管理大数据。
  • 尽量做到冷热数据分离,减小表的宽度：MySQL 限制每个表最多存储 4096 列，并且每一行数据的大小不能超过 65535 字节。减少磁盘 IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的 IO）；更有效的利用缓存，避免读入无用的冷数据；经常一起使用的列放到一个表中（避免更多的关联操作）。
  • 禁止在表中建立预留字段：预留字段无法确认存储的数据类型，所以无法选择合适的类型。对预留字段类型的修改，会对表进行锁定。
  • 禁止在数据库中存储图片,文件等大的二进制数据：会进行大量的随机 IO 操作，文件很大时，IO 操作很耗时。通常存储于文件服务器，数据库只存储文件地址信息
  • 禁止在线上做数据库压力测试
  • 禁止从开发环境,测试环境直接连接生成环境数据库

字段设计

    1. 优先选择符合存储需要的最小的数据类型
      • 列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的 IO 次数也就越多，索引的性能也就越差。
      • 将字符串转换成数字类型存储,如:将 IP 地址转换成整形数据
      • 对于非负型的数据 (如自增 ID,整型 IP) 来说,要优先使用无符号整型来存储
      • 过大的长度会消耗更多的内存。
    2. 避免使用 TEXT,BLOB 数据类型
      • 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中
      • TEXT 或 BLOB 类型只能使用前缀索引
    3. 避免使用 ENUM 类型
    4. 尽可能把所有列定义为 NOT NULL
    5. 使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间
    6. 同财务相关的金额类数据必须使用 decimal 类型

索引设计

  1. 限制每张表上的索引数量,建议单张表索引不超过 5 个
  2. 禁止给表中的每一列都建立单独的索引
  3. 每个 Innodb 表必须有个主键
  4. 常见索引列建议（出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列；包含在 ORDER BY、GROUP BY、DISTINCT 中的字段；多表 join 的关联列）
  5. 选择索引列的顺序
    • 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
    • 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
    • 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）
  6. 避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）
    • 重复索引示例：primary key(id)、index(id)、unique index(id)
    • 冗余索引示例：index(a,b,c)、index(a,b)、index(a)
  7. 对于频繁的查询优先考虑使用覆盖索引
    • 覆盖索引：就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引
    • 避免 Innodb 表进行索引的二次查询
    • 可以把随机 IO 变成顺序 IO 加快查询效率
  8. 索引 SET 规范
    • 不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引
    • 外键可用于保证数据的参照完整性，但建议在业务端实现
    • 外键会影响父表和子表的写操作从而降低性能

sql规范

  1. 建议使用预编译语句进行数据库操作
  2. 避免数据类型的隐式转换
    • 隐式转换会导致索引失效select name,phone from customer where id = '111';
  3. 充分利用表上已经存在的索引
    • 避免使用双%号的查询条件,如果无前置%,只有后置%，是可以用到列上的索引的
    • 一个 SQL 只能利用到复合索引中的一列进行范围查询
    • 使用 left join 或 not exists 来优化 not in 操作，因为 not in 也通常会使用索引失效。
  4. 数据库设计时，应该要对以后扩展进行考虑
  5. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询
    • 消耗更多的 CPU 和 IO 以网络带宽资源
    • 无法使用覆盖索引
    • 可减少表结构变更带来的影响
  6. 避免使用子查询，可以把子查询优化为 join 操作
  7. 避免使用 JOIN 关联太多的表
  8. 对应同一列进行 or 判断时，使用 in 代替 or
  9. 禁止使用 order by rand() 进行随机排序
  10. WHERE 从句中禁止对列进行函数转换和计算
  11. 在明显不会有重复值时使用 UNION ALL 而不是 UNION
    • UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
    • UNION ALL 不会再对结果集进行去重操作
  12. 拆分复杂的大 SQL 为多个小 SQL

为什么sql执行慢

上一篇 mysql高级数据脚本

下一篇 mysql高级锁机制

Comments

Content