MySQL 随笔

数据库设计步骤

一个数据库系统的设计步骤主要可以分为六个步骤，并且是可以据此不断循环往复以满足新需求的：

1. 需求分析

需求分析是数据库系统设计的第一步，也是最基础的、耗时最长的一个阶段。在这个阶段，需要对现实世界中的相关对象（客户）进行详细调查，然后逐步分析，确定客户对系统的数据需求和业务处理需求，进而形成需求分析文档。

2. 概要设计

依赖于需求分析文档，通过对需求分析中涉及到的实体进行综合、归纳和抽象，确定各个实体所必须的属性，以及各个实体之间的关联，最终形成 DBMS 概念模型，也就是 E-R 图。

3. 逻辑结构设计

根据概要设计生成的概念模型 E-R 图，需要将其设计为一个一个的表，确定各个表中每个字段（属性）的简单约束描述，确定各个表的主外键，并应用数据库设计的三大范式进行审核，对其优化。

何为范式呢？可以看一下这篇文章：，为免文章失效，我简单通俗地总结摘录一下，括号里的内容是理解不到位的地方：

元组：表中的一行记录。
码：可以唯一确认一个元组的属性集合，如果这样的属性集合（集合之间相交不为空集可视为不同属性集合）不只一个，则每一个属性集合都是候选码，从诸多候选码中选出一个，便为主码，若主码的属性集合包括实体的所有属性，那么称其为全码。若一个属性集合它不是该实体的主码，但却是另一个实体的(主)码，那么，这个属性集合被称为此实体的外码。
主属性：所有候选码的并集中的所有元素都是主属性，不属于主属性的属性则为非主属性。

第一范式(1NF)：属性不可分，举个例子，电话可以分为座机和手机，那此时电话就是可分的属性。
第二范式(2NF)：符合 1NF，并且，每个非主属性都依赖于(主)码。通俗点说就是只要说出一个具体的主码值，那么其余所有非主属性的值便都已明了，表示为码->非主属性。
第三范式(3NF)：符合 2NF，并且，没有传递依赖。（简单来说，如果码 A 能确定某个属性集合 B，而 B 又能确定另一个属性集合 C，因此 A 可以确定 C，那么就说发生了传递依赖。）
BC范式(BCNF)：符合 3NF，并且，主属性不依赖于主属性。（即不存在任何主码外的主属性依赖于主码的任意真子集，也就是主属性之间也没有传递依赖。）

4. 物理模型设计

根据逻辑结构设计中所得到的各个逻辑表，设计与具体数据库相关联的物理表，明确各个表的表名、每个表的各个字段的字段名、数据类型和其余约束条件，各个表的主外键约束等与具体数据库相关的物理实现。

5. 数据库实施

根据物理模型设计的结果编写相应的 SQL 语句建立数据库、建立各个表，在应用程序中或者直接执行 SQL 语句进行表的各种读写操作。

6. 数据库运行与维护

数据库成功实施后，便可以在不断的运行维护中对原有的表结构进行评价、修改和优化。

MySQL 索引

索引是一个单独的，存储在磁盘上的一个数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，MySQL 的所有列类型都可以被索引，对相关列使用索引是增加查询效率的最佳途径。

索引实现原理

对于不同的存储引擎，索引实现方式会有所不同，但使用 B+ 树存储结构实现的索引却是最普遍的，下面是 MyISAM 存储引擎的原理图：

如果熟悉 B+ 树的同学相信一眼就看明白了，叶子节点保存着对数据表所有记录的引用地址，同时每个叶子节点都可以包含多个表记录（一般是一页数据 16 KB），每个叶子节点之间又通过链表的形式相互连接，这个链表总体上是有顺序的。

与 MyISAM 存储引擎不同，InnoDB 本身就是 B+ 树结构的索引文件，为什么这么说呢？因为 InnoDB 要求每个表都必须有主键（如果没有设置主键，就会自动选择一个能够唯一标识一个记录的列作为主键，如果还是找不到，就会创建一个长度为6个字节的长整型的隐藏字段作为主键），而 InnoDB 正是基于这个指定的主键来建立它的数据表，如下图所示：

与 MyISAM 存储引擎相比，叶子节点不再是数据表记录的引用地址，而是直接包含了完整的数据表记录，这个也被称为聚集索引。对于辅助索引来说，MyISAM 的基本存储结构并没有什么太大变化，其叶子节点的值仍然是数据表记录的引用地址，而 InnoDB 的值则不再直接包含数据表记录，而是数据表记录的主键，这意味着如果使用辅助索引，对于 InnoDB 存储引擎的辅助索引查询，它会走两次索引，第一次是走辅助索引获取主键值，第二次是走主键索引，根据第一次获取的主键来找到具体的表记录。

索引类型

1. 普通索引和唯一索引

普通索引是 MySQL索引中的基本索引类型，允许在定义索引的列中插入重复值和空值；唯一索引则要求定义索引的列中无重复值，但也允许空值；而主键索引是一种特殊的唯一索引，它除了不允许重复值，还不允许空值(NULL)。

2. 单列索引和组合索引

单列索引是指索引只包含一个列，一个表可以有多个单列索引；组合索引是指在表的多个字段组合上创建的索引，使用组合索引时遵从最左前缀原则。例如

# 创建了一个组合 book_name 和 age 的组合索引
mysql> CREATE INDEX book_name_age_index ON book(book_name, age);
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

上面新建的索引在索引中就会以（book_name， age）的形式保存，根据最左前缀原则，索引可以匹配 book_name，（book_name， age）形式，但是不能匹配 age，（age， book_name）。

3. 全文索引

全文索引(FULLTEXT)，是指定义在索引列上的支持值的全文查找，允许重复值和空值的一种类型，全文索引只能用于 InnoDB 或 MyISAM 表，只能为 CHAR、VARCHAR、TEXT 列创建。

4. 空间索引

空间索引是对空间数据类型的列建立索引，在 MySQL 中空间类型有四种，分别是

Geometry是所有空间集合类型的基类，其他类型如POINT、LINESTRING、POLYGON都是Geometry的子类。
Point，顾名思义就是点，有一个坐标值。
LineString，线，由一系列点连接而成。如果线从头至尾没有交叉，那就是简单的（simple）；如果起点和终点重叠，那就是封闭的（closed）。
Polygon，多边形。可以是一个实心平面形，即没有内部边界，也可以有空洞，类似纽扣。最简单的就是只有一个外边界的情况，例如POLYGON((0 0,10 0,10 10, 0 10))。

空间索引只能在存储引擎为 MyISAM 的表上创建，MySQL 使用 SPATITAL 关键字进行扩展，使得其能够与其余索引一样以同样语法创建。

索引创建

任何时候都可以以此种方式新建索引：

CREATE [UNIQUE | FULLTEXT | SPATITAL] [INDEX | KEY] index_name ON table_name(column_name[(length)] [, column_name[(length)] ] [...])：在表中的一列或多列上建立普通索引；如果是BLOB和TEXT类型，必须指定 length，其余情况可以省略。

# book 表结构
mysql> DESC book;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| book_name | varchar(30) | YES  |     | NULL    |                |
| age       | int(11)     | YES  | MUL | 1       |                |
+-----------+-------------+------+-----+---------+----------------+

# 下面创建了一个普通索引， 如果添加了 UNIQUE 字段则是创建一个唯一索引
mysql> CREATE INDEX test_index ON book(book_name, age);
Query OK, 0 rows affected (1.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

在上述 book 表结构描述中， Key有两个值，其中 PRI 表示主键索引 primary key；MUL 表示该列的值可以重复，并且该列是一个非唯一索引的前导列（外键也是一个非唯一索引）；此外， UNI 表示此列是唯一索引，值不可重复但允许空值，

以修改表结构方式新建索引:

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATITAL] [INDEX | KEY] index_name column_name[(length)] [, column_name[(length)] ] [...]

# 在已有表 course 中的 i 列创建了一个名为 unique_index_i 的普通索引
mysql> ALTER TABLE course ADD INDEX unique_index_i(i);
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建表的时候创建索引：

CREATE TABLE table_name(
# 省略诸多属性字段
...,
[UNIQUE | FULLTEXT | SPATITAL] [INDEX | KEY] index_name(column_name[(length)] [, column_name[(length)] ] [...])
);

# 示例，创建一个临时表，在 MySQL 关闭之时会自动销毁
# testTable 表中在 name 属性列上建立了一个普通索引 name_index
mysql> CREATE TEMPORARY TABLE testTable(
    ->    id int auto_increment primary key,
    ->    name varchar(30),
    ->    INDEX name_index(name)
    -> );
Query OK, 0 rows affected (0.02 sec)

索引分析

使用索引最重要的就是加快查询速度，那么如何知道索引是否生效，以及哪些查询速度较慢，也就是慢查询有哪些呢？我们可以利用 MySQL 的慢查询日志得知有哪些慢查询。

慢查询优化

1. 开启慢查询日志
在 MySQL 8 中如何开启慢查询日志呢？

在配置文件 my.ini 或 my.cnf 中找到或增加下面的配置项

  # 慢查询日志开关，0 为关闭，1 为开启，MySQL 8 默认开启
  slow-query-log=1

  # 存放慢查询日志的文件
  slow_query_log_file="XTZJ-20220209AS-slow.log"

  # 如何定义慢查询，这里默认查询时间超过 10 秒的就是慢查询，单位秒
  long_query_time=10

然后重新启动服务器即可。windows 配置文件一般在 datadir 里：

  # 数据文件所在目录
  mysql> SELECT @@datadir;
  +---------------------------------------------+
  | @@datadir                                   |
  +---------------------------------------------+
  | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ |
  +---------------------------------------------+
  1 row in set (0.00 sec)

  # MySQL bin 所在目录
  mysql> SELECT @@basedir;
  +------------------------------------------+
  | @@basedir                                |
  +------------------------------------------+
  | C:\Program Files\MySQL\MySQL Server 8.0\ |
  +------------------------------------------+
  1 row in set (0.00 sec)

命令行启动 MySQL 时带上 --slow-queries-log 选项：

  C:\Program Files\MySQL\MySQL Server 8.0\bin>net start mysql80 --slow-queries-log
  MySQL80 服务正在启动 ...
  MySQL80 服务已经启动成功。

2. 分析慢查询日志

直接打开慢查询日志文件，找到对应的 SQL 查询语句，然后利用 EXPLAIN 或者 DESCRIBE(DESC) 关键字来模拟优化器执行 SQL 查询语句，进而可以得到为何这个查询会如此慢。下面简单介绍一下这两个关键字：

[DESC | EXPLAIN] SELECT 语句：使用 DESCRIBE (简写为 DESC) 或者 EXPLAIN 命令可以对查询语句进行分析

mysql> DESCRIBE SELECT * FROM book;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | book  | NULL       | index | NULL          | test_index | 128     | NULL |    9 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+

可以看到这条命令执行完后返回了上面这张表，其中各个字段的简单含义如下所示：

id: select 识别符，这是 select 语句的查询序列号，id 越大的越先执行，id 相等的从上到下执行
select_type：表示 select 语句的查询类型
table：表示操作的是哪张表
type：表示表的连接类型
possible_keys：表示 MySQL 在搜索数据记录时可选用的各个索引
key：表示 MySQL 真正选用的索引
key_len：给出索引按字节记录的长度，数值越小，表示查询越快
ref：给出了关联关系中另一个数据表的列名
rows：表示 MySQL 在执行这个查询时预计会扫描的行数
Extra：表示与本查询相关的信息

记录一下有疑问的地方及学习文章：
《什么是覆盖索引?》

《explain详解》

SQL 优化

由于索引的存在，会使得当数据插入、删除和更新时都要顺手维护一下索引，所以，为了更快，我们需要在数据被修改之前关闭索引，修改完成后打开索引：

ALTER TABLE table_name DISABLE KEYS : 关闭表中的索引
ALTER TABLE table_name ENABLE KEYS：打开表中的索引
此方法适用于 MyISAM 和 InnoDB。

除了索引，表中有些列还要求一些诸如唯一性检查、外键检查等，所以在插入数据时需要禁用这些检查以提升速度：

SET UNIQUE_CHECKS = 0 :0 表示关闭唯一性检查，1 表示打开
SET FOREIGN_KEY_CHECKS = 0 :0 表示关闭外键检查，1 表示打开
对于唯一性检查，适用于 MyISAM 和 InnoDB，而 MyISAM 由于无需主键，故而不用外键，因此外键检查只适用于 InnoDB。

插入数据时，使用批量插入比单个插入更高效：

INSERT INTO table_name(column_1,column_2,...) VALUES(column_1,column_2,...), (column_1,column_2,...), ...
还可以使用 LOAD DATA INFILE 进行批量导入当需要批量导入数据时，使用 LOAD DATA INFILE 语句导入数据的速度比INSERT语句快。
据说这主要是针对 MyISAM 常见的优化手段，而对于 InnoDB，则是说在插入数据之前，关闭事务的自动提交：
SET AUTOCOMMIT = 0 ：0 表示关闭自动提交，1 表示打开

事务

事务是指一系列指令的集合，可以是一条指令，也可以是多条指令，这些指令要么全部成功执行，要么全部失败，只要有一条指令执行失败，那么之前所执行的指令就会全部失效，也就是回滚到事务开始之前。在 MySQL 事务中，一条 SQL 语句就是一个事务，命令执行成功就会自动提交。如果我们想要显式开始一个事务，可以使用 BEGIN 和 COMMIT, ROLLBACK，执行成功则是 COMMIT（提交），失败则是 ROLLBACK（回滚）：

# 建一个表来测试
mysql> CREATE TABLE book(
    ->  id int AUTO_INCREMENT PRIMARY KEY,
    ->  book_name VARCHAR(30),
    ->  author VARCHAR(30),
    ->  count INT
    -> );
Query OK, 0 rows affected (1.12 sec)

# 开启一个事务
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

# 事务中的第一个操作
mysql> INSERT INTO book(book_name, author, count) value('高等数学', '同济大学数学系', 10);
Query OK, 1 row affected (0.13 sec)

# 事务中的第二个操作
mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
+----+-----------+----------------+-------+
1 row in set (0.05 sec)

# 提交一个事务
mysql> COMMIT;
Query OK, 0 rows affected (0.06 sec)

事务的四大特性 ACID

原子性（Atomicity）：事务中的操作是一个不可分割的整体，要么全部成功完成，要么全部失败，一个操作（指令）失败就算全部失败，应该立即回滚到事务执行之前。比如，当你上厕所的时候，正准备一泻千里，幸好发现自己没有带手纸，于是你赶紧整理好仪容，恢复到进厕所之前的样子，然后拿到纸后，再重新来一遍。
一致性（Consistency）：事务的成功执行使得数据的状态发生了变化，但是不管状态如何变化，都应该满足数据的完整性约束。比如，我原来是个帅哥，不会因为放了屁后就不是帅哥了。
隔离性（Isolation）：不同事务之间是相互隔离的，即如果对同一数据进行操作，不同事务对这个数据的修改是其余事务所不可见的。通俗点说就是在一个事务看来，在它对数据的操作中，是没有其余事务在修改这些数据的，就是你忙你的，我忙我的，我不想看到我的东西不是我原来看到的样子。
持久性（Durability）：当事务成功完成后，对数据的更改是永久性的。也就是说，该做的事情我都做完了，不管你发生什么意外，都必须要给我这个结果。

mysql

MySQL 随笔

数据库设计步骤

MySQL 索引

索引实现原理

索引类型

索引创建

索引分析

慢查询优化

SQL 优化

事务

事务的四大特性 ACID

相关

关于MySQL中的事务、提交（commit）、回滚（rollback）

MySQL索引类型

Windows7环境，Dbeaver 安装连接Mysql5.7

linux 恢复mysql 误删除数据

mac 安装mysql workbench8.0.23闪退。

Linux-Mysql 遗忘密码如何解决？

mysql数据库死锁的产生原因及解决办法

linux安装mysqlclient库

【Mysql学习】django.db.utils.OperationalError: (1045:Access denied for user 'roo

mysql改变配置文件后启动报错

mysql binlog恢复命令

MySQL left join、right join和join的区别

标签