Mysql学习笔记

Published: June 02, 2020 by Daltao

• Categories:
• technology 22

• Tags:

• 安装配置
  • 下载
  • 安装
    • windows
  • 启动服务器
  • 登陆客户端
  • MySQL Workbench
    • windows
• 实战
  • MySQL批量造数据
• 事务和并发
  • 隔离级别
  • 幻读
  • 死锁问题
  • 事务日志
  • 提交和回滚事务
  • 加锁
  • MVCC
• 索引
  • 单列和多列索引
  • 索引类型
  • 伪哈希索引
  • 索引的使用
  • 独立的列
  • 前缀索引
  • 合并索引
  • 多列索引
  • 聚簇索引
  • 覆盖索引
• 排序
  • 索引与加锁
• 表分区

安装配置

下载

去官网下载社区版本的MySQL软件。

之后初始化mysql服务器。

安装

windows

$ mysqld --initialize --console

2020-06-04T08:00:30.979793Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: aRLHfu<zi2f0

初始化成功后会在根目录下创建一个名字叫做data的目录，同时控制台会输出root用户的临时密码。

接下来我们要安装服务。

$ mysqld install

Service successfully installed.

安装成功后打开服务即可。

修改root用户密码。

$ mysqladmin -u root -p password 新密码

启动服务器

MySQL的服务器是bin/mysqld文件，启动它就好了。

登陆客户端

MySQL的bin目录下提供了客户端文件mysql，因此输入

$ mysql -h 主机 -u 用户 -p

就可以登录用户账号。

MySQL Workbench

windows

去官网下载Windows安装包，安装完成后即可使用。

实战

MySQL批量造数据

首先rand()函数会为我们产生[0,1)范围中的小数，因此我们可以通过floor(rand() * n)获得[0,n)之间的整数。这就是我们造随机数据的基础。

之后如果我们需要生成字符串，可以创建下面函数：

CREATE FUNCTION `rand_string`(n int) RETURNS varchar(255) CHARSET utf8mb4
    NO SQL
BEGIN
declare chars varchar(255) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare answer varchar(255) default '';
declare i int default 0;
while i < n do

set answer = concat(answer, substring(chars, floor(1 + rand() * 62), 1));
set i = i + 1;

end while;
RETURN answer;
END

它会生成长度为n的随机字符串。

好了，要插入数据，可以使用存储过程来为我们插入数据。

CREATE PROCEDURE `insert_pseudo_data_for_student`(n int)
BEGIN

declare i int default 0;
while i < n do

insert into student(name, code, area, age, graduated, sex) values(rand_string(3), rand_string(18), rand_string(20), floor(rand() * 18), floor(rand() * 2), floor(rand() * 2));
set i = i + 1;

end while;    

END

之后调用存储过程即可。

call insert_pseudo_data_for_student(100);

事务和并发

可以用START TRANSACTION开启事务，用COMMIT或ROLLBACK分别提交或回滚事务。

一个良好的事务处理系统，应该具有ACID四个特征：

• 原子性(A)：事务中的所有操作要么同时成功，要么同时失败。
• 一致性(C)：数据库仅从一个一致性的状态转移到另外一个一致性的状态。
• 隔离性(I)：一般来说，事务在提交之前，其修改对于其它事务是不可见的。但是这取决于你设置的隔离级别。
• 持久性(D)：一旦事务提交，则其所做的所有修改都会永久保存到数据库中。

隔离级别

隔离级别分成四种：

• READ UNCOMMITED（未提交读）： 事务的修改即使没有提交，对其它事务也是可见的。
• READ COMMITED（提交读）：事务直到提交后，其做的修改才对其它事务可见。但是这里会出现两次读结果不同的脏读问题。
• REPEATABLE READ（可重复读）：该级别保证同一个事务中多次读操作得到的结果是一致的。这个级别中可能会出现读取到其余事务提交后的插入的新行的幻读问题，要避免幻读问题，可以通过加上间隙锁来避免新数据插入到范围中。
• SERIALIZABLE（可串行化）：强制事务串行执行，避免了幻读问题。

可以通过下面语句修改隔离级别：

show  variables like 'transaction_isolation'; #查看隔离级别
set [ global | session ] transaction isolation level Read uncommitted | Read committed | Repeatable | Serializable;

幻读

这里简单说明一下幻读。比如我们执行了下面操作：

update user set updated = 1 where id > 2;

之后另外一个事务插入了一条id为$5$的记录，并提交了。这时候第一个事务就会发现还有一条满足条件的记录没有被更新，就和出现了幻觉一样。

死锁问题

事务通过加锁来占用资源，但是如果两个事务以不同的顺序加锁，则可能会出现死锁问题。

死锁可以通过数据库系统实现死锁检测来解决，这种方法比较有效。还有一种解决方案就是获取锁时加上超时机制，如果超时了就回滚事务并释放锁，InnoDB目前让拥有最少行级排他锁的事务回滚。

事务日志

事务日志可以提高事务的处理效率。存储引擎在修改数据的时候可以只修改内存中的拷贝，同时把修改行为记录到硬盘的事务日志中。由于日志是以追加的方式写入的，因此性能很高（顺序I/O不需要频繁移动磁头），之后内存中修改的数据可以慢慢刷回磁盘中。因此修改数据需要写两次磁盘。

由于日志的存在，即使是宕机，也可以恢复修改操作。

提交和回滚事务

MySQL默认使用自动提交模式，也就是说每一条SQL都被当成一个事务被执行提交。可以通过设置AUTOCOMMIT变量来改变提交模式：

SHOW VARIABLES LIKE 'AUTOCOMMIT';


+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

SET AUTOCOMMIT = 1; #0表示禁用，1表示启用

需要注意当执行一些DDL语句的时候，会强制提交当前的事务。

由于事务是由存储引擎实现的，因此在同一个事务中同时使用多个存储引擎是不可靠的。

加锁

InnoDB采用两阶段锁定协议（two-phase locking protocol）。在事务执行的过程中，随时都可以执行锁定，锁只有在事务提交或回滚后才会被释放，并且所有的锁是同时释放的。

InnoDB会根据隔离级别在需要的时候自动加锁，这类锁称为隐式锁定。在修改、插入、删除操作发生的时候InnoDB都会对选中的行加锁。

另外我们可以通过一些特殊SQL语句进行显式锁定（不属于SQL规范）：

• SELECT ... LOCK IN SHARE MODE
• SELECT ... FOR UPDATE
• LOCK TABLE <Table name> [READ|WRITE|LOW_PRIORITY WRITE]

MVCC

大部分支持事务的存储引擎都实现了MVCC，可以用于规避一些原本需要的加锁操作，降低开销。

InnoDB的MVCC实现，实在每一行后面都加上隐藏的两列实现的，一列保存这条记录的创建版本，一列保存这条记录的删除版本。每个事务在开始的时候都会得到一个自增的版本号。

• SELECT：查询操作只会查找创建版本小于等于事务版本且删除版本为空或大于事务版本的记录，因为这些记录对当前事务是可见的。
• INSERT：插入一行记录，同时设置创建版本为当前事务版本。
• DELETE：将该行记录的删除版本号修改为当前事务版本。
• UPDATE：分解为一次DELETE和INSERT操作。

MVCC仅工作在READ COMMITTED和REPEATABLE READ两个隔离级别下。因为READ UNCOMMITTED和SERIALIZABLE都不需要进行版本控制。

同时MVCC可以解决REPEATABLE READ中的幻读问题。

索引

单列和多列索引

MySQL支持单列的索引和多列的索引，但是注意多列的索引中，列的顺序非常重要，因为mysql只能使用多列索引的某些前缀列。

索引类型

MySQL支持的索引类型如下：

• B-Tree索引：使用B-Tree存储索引，可以减少对磁盘的读取次数。读取次数与树的高度相同，树的高度与表大小有关。
• 哈希索引：基于哈希表实现，仅用于精确匹配。对于每一行记录，存储索引会为所有索引列计算一个哈希码，而哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向数据行的指针。只有Memory存储引擎显式支持哈希索引，且支持非唯一哈希索引（即允许多行拥有相同的哈希值）。注意多列索引中必须所有列全部命中才会使用哈希索引，且哈希索引只支持等于、不等于和IN条件。
• 空间数据索引（R-Tree）：用于地理数据存储，支持的不是很完善。
• 全文索引：类似于搜索引擎，用倒排索引的方式实现。

索引可以加快检索数据，但是需要额外的存储空间和维护成本。

伪哈希索引

InnoDB有一个特殊功能叫做自适应哈希索引。当InnoDB注意到某些索引值被使用的特别频繁的时候，它会在B-Tree索引之外再创建一个哈希索引。

如果我们查询条件中需要使用很长的字符串列，我们可以利用相同的思路优化我们的查询。比如我们要根据一个很长的url字段检索数据，那么我们可以额外加上一列叫做url_crc，存储url的CRC32哈希值。于是之后的查询可以这样写：

CREATE TABLE `urls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(1024) NOT NULL,
  `url_crc` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_url_crc` (`url_crc`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TRIGGER `urls_BEFORE_INSERT` BEFORE INSERT ON `urls` FOR EACH ROW BEGIN
	SET NEW.url_crc=crc32(NEW.url);
END

CREATE TRIGGER `urls_BEFORE_UPDATE` BEFORE UPDATE ON `urls` FOR EACH ROW BEGIN
	SET NEW.url_crc=crc32(NEW.url);
END

这里使用触发器自动更新url_crc列。可以发现我们在url_crc列上建立B-Tree索引，而非在url列上，由于url_crc是整数，因此比较速度远快于url的比较，因此可以得到很大的性能优化。

注意由于存在哈希碰撞，因此查询条件必须包含url列：

SELECT * FROM urls where url='https://flink.apache.org/zh/' and url_crc=crc32('https://flink.apache.org/zh/');

由于生日悖论，哈希碰撞在数据量上万后很容易发生，因此可以使用FNV64函数来生成64位的哈希值。

索引的使用

要使用索引，需要满足一些条件。

选择性是指根据这个索引，整个表中不同的元素数目除去表大小得到的比例。比如唯一索引，其选择性一定为1，而对于01状态字段，其选择性最多为2/n，其中n为表大小。选择性越大，则说明索引越有价值，效果也越好。

独立的列

如果查询中的列不是独立的，那么MySQL就不会使用索引。其中独立指这个列不能是表达式的一部分，也不能是函数的参数。比如下面条SQL就不会用到age列上加的索引。

SELECT * FROM user where age + 1 = 26;

当然很显然条件等价于age = 25，但是MySQL无法自动解析这个公式。因此需要我们自己将索引列单独放在比较符号的一侧。

同样，下面的SQL也不会用到birth_day列上的索引，因为索引列不能作为函数参数。

SELECT * FROM user where YEAR(birth_day) <= YEAR(CURRENDATE);

前缀索引

对于很长的字符列，建立完整的索引会浪费很多空间且使得性能降低，一般的解决方案是仅为字符列的固定长度的前缀建立索引。

这样做与哈希索引是类似的（我们可以把字符列的前缀视作它的一个不均匀的哈希值），好处是降低了维护索引和使用索引的成本，但是缺点是有时无法通过索引定位到某条数据，可能定位到某块数据，我们必须遍历这块数据。

前缀索引使用的前缀越长，搜索越精确，但是成本也越高。对于前缀长度的选择，可以通过给定一个选择性阈值，比如说0.5，之后找到最小的前缀长度，能满足选择性。

前缀索引不能用于ORDER BY和GROUP BY。

合并索引

如果你查询条件中出现多个列都可以作为索引。老版本（5.0之前）的MySQL不会使用合并索引，比如下面SQL：

SELECT * FROM user where region='wenzhou' and age=25 ;

这种情况下使用单独的region和age索引效果都不好，因此会转而使用全表扫描。但是如果我们改写SQL成下面的样子，就可以同时使用两个索引了：

SELECT * FROM user where region='wenzhou' 
union
SELECT * FROM user where age=25 and region!='wenzhou';

在5.0版本及之后，MySQL可以同时使用多列索引并将结果合并。但是如果你在explain界面看到使用了合并索引的技术，这往往说明表的索引建立的非常糟糕，使用合并索引的性能有时甚至不如全表扫描。

• 当服务器对多个索引做相交操作时（多个AND条件），通常意味着你需要建立一个包含所有相关列的多列索引。
• 当服务器对多个索引做联合操作时（多个OR条件），需要花费大量的时间用于合并得到的数据（去重，重新排序等）。

多列索引

对于BTree结构，多列索引会按照最左列作为第一关键字，次左列作为第二关键字。因此每次使用索引的时候条件必须带上最靠左的若干列才能使用。

多列索引中哪些列应该排在前面，哪些排在后面。一般来说将选择性较高的列放在较左边是很好的选择。比如我们现在希望根据地区和性别筛选：

SELECT count(distinct(area)) / count(*) as area_selectivity, count(distinct(sex)) / count(*) as sex_selectivity, count(*) as total FROM student;

+------------------+-----------------+-------+
| area_selectivity | sex_selectivity | total |
+------------------+-----------------+-------+
|           0.9780 |          0.0020 |  1000 |
+------------------+-----------------+-------+

可以看到地区的选择性高于性别的选择性，因此我们将地区的选择性作为第一关键字，将性别作为第二关键字建立多列索引。

聚簇索引

聚簇索引是在同一个结构中保存B-Tree索引和数据行。当表有聚簇索引的时候，它的多个数据行实际上放在叶子页中。因为无法同时将数据行放在两个位置，因此一张表最多有一个聚簇索引。

不是所有存储引擎都支持聚簇索引的，但是InnoDB是支持的。InnoDB的BTree结构中，非叶子中仅存储索引列，而叶子中才包含了行数据。聚簇索引仅保证同一个叶子页中记录的连续，不是同一个叶子页的数据可能会距离很远。

InnoDB通过主键聚集数据，即在主键列上建立聚簇索引。如果没有定义主键列，InnoDB会选择一个唯一的非空索引替代。如果没有这样的唯一非空索引的话，InnoDB会定义一个隐藏的主键列来使用聚簇索引。

聚簇索引的优点有：

• 可以把相关数据保存在一起。
• 由于叶子页中可以直接取得数据，因此数据的访问速度更加的快。

聚簇索引的缺点是：

• 对于数据放在内存中的存储引擎，聚簇索引不能提供性能优化。
• 更新聚簇索引列会导致数据行移动，成本很高。
• 聚簇索引行在插入和移动（更新）的时候，如果插入一个满了的叶子页中，存储引擎需要将叶分成两个新的页面来容纳数据。这样会占用更多的磁盘空间。
• 二级索引（非聚簇索引）的叶子结点中存储的是数据行的主键而不是完整的数据行，因此需要再拿主键去聚簇索引上查询一次才行。即二级索引需要执行两次索引查找。（InnoDB自带的自适应哈希可以减少这样的重复操作）

覆盖索引

如果一个索引的叶子结点中包含所有要查找的列，我们就称其为覆盖索引。

由于InnoDB中二级索引存储的是主键，因此需要拿主键到聚簇索引上再次查找才能得到数据行，因此覆盖索引可以原来的两次查找变成一次。

覆盖索引的前提是必须存储索引列的值，而对于哈希索引、空间索引和全文索引都不会存储索引列的值，所以MySQL目前只能用B-Tree做覆盖索引。

如果使用了覆盖索引，在用explain的时候，extra列会显示using index，否则的话会显示using where。比如：

explain SELECT name FROM student where name='111';

+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | selectype | table     | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | index_name    | index_name | 182     | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------------+

但是需要注意不是说索引完全包含了查询列就可以使用覆盖索引，如果条件中出现了索引外的列，那么也是不能使用覆盖索引的，还是必须回聚簇索引去查行数据。

explain SELECT name FROM student where name='111';

+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | selectype | table     | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | index_name    | index_name | 182     | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------------+

排序

MySQL可以用两种方式实现结果集有序，一种是通过外部排序，一种是通过按照索引顺序扫描。如果explain出来的extra列值会出现using filesort，则表示用外部排序的方式。

在聚簇索引上，按照索引扫描做的是顺序IO，因此性能非常好。但是在二级索引上，如果不是覆盖索引，则需要回表扫描这个过程，这时候做的一般是随机IO，速度会比较慢。

只有当多列索引的列顺序和order by子句顺序完全一致，且每一列的排序方向也一致（倒序或正序）时，MySQL才能用索引对结果进行排序。

索引与加锁

在事务中，如果我们操作了一些行，自然需要对这些行进行加锁。

如果我们没有使用索引，那么存储引擎就无法在检索行的同时进行加锁。因此它必须把数据提交给服务器层的时候才能精确地过滤出行数据。这时候一般需要锁定额外的行。

在早先版本，这些额外锁定的行需要在事务结束后才会被释放，但是在MySQL5.1版本及之后，InnoDB可以在服务器端过滤掉行后就释放锁。

select * from student where id<=5 and id!=1 for update;

上面的这条语句会锁住id小于等于5的所有记录，包括id为1的记录。这是因为索引的扫描是范围扫描。

可以发现用了索引也是可能会有不需要的行被锁住的，何况不用索引的情况。在不使用索引的情况下，MySQL会做全表扫描并锁住整张表。比如对于student表，我们没有给area字段加索引，且使用下面sql进行加锁：

start transaction;
select * from t_student where area = 'Zhejiang' for update;

之后在另外一个session中执行下面sql：

start transaction;
select * from t_student where area = 'Beijing' for update;

你会发现第二条sql会一直等待锁。因为需要在查询之前获取全表锁，但是其中area为Zhejiang的记录的行锁已经被获取了，因此需要等待。

这里还有一个非常重要的一点，就是InnoDB二级索引中使用共享锁（读锁），但是访问主键索引需要用排他锁（写锁）。因此这种情况下我们不能使用覆盖索引来优化性能（必须去主键索引上加锁），使得SELECT FOR UPDATE比LOCK IN SHARE MODE或其他非锁定查询要慢得多。

表分区

分区表逻辑上是一张表，但是底层是由多张物理子表组成的。MySQL中分区表没有全局索引，只能对单张物理子表进行定义。

分区表的好处和哈希类似，可以帮助我们过滤掉想当一部分不需要的数据。同时将关联的数据分放到一张表中，这样易于管理。下面场景中适合使用分区技术：

• 表过大，或者只有表的尾部数据是热点数据，其余是历史数据。
• 分区表的数据更加容易维护，可以独立对分区进行优化。
• 分区表的数据可以分布在不同的物理设备中。
• 分区表可以避免表锁的竞争（相当于有多把表锁）
• 分区可以独立备份维护。

分区表的限制：

• 一张表最多能有1024个分区。
• 分区表不能使用外键约束。

分区表的操作逻辑：

• SELECT查询：当查询一个分区表的时候，分区表先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，之后在调用存储引擎接口访问各个分区的数据。
• INSERT操作：插入一条记录时，分区表先打开并锁住所有底层表，然后确认哪个分区接受这条记录，再将记录写入到对应的底层表中。
• DELETE操作：删除一条记录时，分区表先打开并锁住所有底层表，然后确定数据对应的分区，最后通过底层表执行删除操作。
• UPDATE操作：更新一条记录时，分区表先打开并锁住所有底层表，之后确定数据对应的分区，之后通过底层表执行更新操作。

创建一张分区表：

SELECT * FROM study.sales;CREATE TABLE `sales` (
  `id` int(11) NOT NULL,
  `order_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (year(`order_date`))
(PARTITION part0 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION part1 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION part2 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION part3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

Partition分区子句中可以使用各种函数，只要它返回一个确定的整数，且不是一个常数。

当然上面按照年份进行分区，会导致最后一年的分区成为热点，锁竞争严重。我们可以选择使用哈希进行分区就可以缓解这种问题。

这里有一些重要的点：

• 如果分区函数返回NULL，则数据会落在第一个分区中。
• 所有分区必须使用相同的存储引擎。

由于所有操作都会需要打开并锁住所有底层表，因此会有一定的性能损耗，可以用批量操作来减轻锁表带来的竞争。