MySQL.md

返回首页

MySQL

存储引擎

负责MySQL中数据的存储和提取。

• InnoDB

默认的存储引擎，支持事务、外键，行锁设计，使用多版本并发控制(MVCC)获得高并发

• MyISAM

不支持事务，表锁设计，支持全文索引

• Memory

将表中的数据存放在内存当中，默认使用哈希索引，只支持表锁，并发性能较差

数据类型

• 整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT， 分别使用 1, 2, 3, 4, 8 字节的存储空间

• INT(5)表示当数值宽度小于5位的时候在数字前面填满宽度，一般是在数字位数不够的空间用字符"0"进行填充，如果不显示指定宽度则默认为INT(11)。

• 浮点型

• 浮点数：FLOAT(单精度)、DOUBLE(双精度)
• 定点数：DECIMAL，内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

都可以指定列宽，比如DECIMAL(M,D)表示该值一共显示M位数字（整数位+小数位），其中D位位于小数点后面.

• 日期时间

TIMESTAMP(和UNIX时间戳相同,只能表示从1970年到2038年)、TIME、YEAR、DATE、DATETIME

• 字符串

• 定长：CHAR，长度固定为创建表时声明的长度，长度可以为从0～255的任何值。
• 变长：VARCHAR，值为可变长字符串，长度可以指定为0～255。

分区

分区是指根据一定的规则，在物理上把一个表分解成多个更小的、更容易管理的部分。

• 优点

和单个磁盘或者文件系统分区相比，可以存储更多数据。跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。可以通过删除与这些数据有关的分区来快速删除数据。

• 分区类型

RANGE分区、LIST分区、HASH分区、KEY分区

索引

许多查询只涉及文件中的少量记录，使用索引可以快速定位到这些记录，避免全表扫描。索引是存储引擎用于快速找到记录的一种数据结构。MySQL会先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

B+ 树索引

B+ Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。

• 数据结构

• 聚集索引

按照每张表的主键构造一颗B+树，同时叶子结点中存放的即为整张表的行记录数据

• 辅助索引

叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

全文索引

哈希索引

索引优化

SQL优化

• 设计数据库时：数据库表、字段的设计，存储引擎
• 利用好MySQL自身提供的功能，如索引等
• 横向扩展：MySQL集群、负载均衡、读写分离
• SQL语句的优化

事务

一组原子性的SQL查询操作。一组操作要么全部执行成功，要么全部执行失败。

• 典型的银行转账案例
• ACID

原子性(atomicity)：整个事务中的所有操作要么全部提交成功，要么全部失败回滚，不可能只执行其中的一部分操作。

一致性(consistency)：在事务执行时，如果系统意外崩溃。事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。

隔离性(isolation)：一个事务所做的修改在最终提交以前，对其他事务是否可见。

持久性(durability)：一旦事务提交，则其所做的修改就会永久保存到数据库中，此 时即使系统崩溃，修改的数据也不会丢失。

• 隔离级别

并发一致性问题，较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

• READ UNCOMMITTED（未提交读）

事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，会导致脏读。

• READ COMMITTED（提交读）

一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。

• REPEATABLE READ（可重复读）

MySQL的默认事务隔离级别。

• SERIALIZABLE（可串行化）

最高的隔离级别，通过强制事务串行执行。

并发控制

当某个用户在修改某一部分数据时，MySQL会通过锁定防止其他用户读取同一数据

• 读写锁

读锁是共享，多个客户在同一时刻可以同时读取同一个资源，互不干扰。

写锁则是排他的，一个写锁会阻塞其他的写锁和读锁，确保一个用户能执行写入操作。

• 锁粒度

尽量只锁定需要修改的部分数据，而不是所有的资源。锁的各种操作，包括获得锁、检查锁是否已经解除、释放锁等，都会增加系统的开销，如果系统花费大量的时间来管理锁，而不是存取数据，那么系统的性能可能会因此受到影响。

• 表锁(table lock)

  锁定整张表，一个用户在对表进行写操作(插入、删除、更新等)前，都需要获得写锁，会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的。如：

  ALTER TABLE之类的语句会使用表锁。

• 行级锁(row clock)

  对表中的操作的行数据加锁。

• 多版本并发控制(MVCC)

它在很多情况下避免了加锁操作，因此开销更低。大都实现了非阻塞的读操作，写操作也只锁定必要的行。

日志

• 错误日志

记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

• 二进制日志(BINLOG日志)

记录了所有的DDL(数据定义语言)语句和DML(数据操纵语言)语句，但是不包括数据查询语句。语句以"事件"的形式保存，它描述了数据的更改过程。此日志对于灾难时的数据恢复起着极其重要的作用。

• 查询日志

记录了客户端的所有语句，而二进制日志不包含查询数据的语句。

• 慢查询日志

记录了所有执行时间超过参数long_query_time(单位：秒，默认为10秒)的所有SQL语句的日志。

备份

主要分为逻辑备份和物理备份

逻辑备份：缺点是从MySQL中导出数据和通过SQL语句将其加载回去的开销。

参考资料与链接

• MySQL索引背后的数据结构及算法原理