MYSQL笔记

2020-05-16| 数据库

什么是关系型和非关系型

关系型数据库，是指采用了关系模型来组织数据的数据库，关系模型可以简单理解为二维表格模型，其以行和列的形式存储数据，这一系列的行和列被称为表，一组表组成了数据库。一个关系型数据库就是由一组二维表及它们之间的关系组成的一个数据组织。典型的 MySQL、Oracle 都支持事务。
例子：MySQL、Oracle

非关系型数据库（NoSQL），严格上不是一种数据库，是一种数据结构化存储方法的集合，可以是文档或者键值对等。分类；键值对数据库（是NoSQL中最简单的类型）、文档型数据库（也叫面向文档的数据库）、列存储数据库、图形数据库。NoSQL 都不支持事务。
例子：Redis（键值对数据库）、MongoDB（文档型数据库）、Elasticsearch（文档型数据库）

文档型数据库
一个文档就是文档型数据库中的一条记录。文档通常存储关于一个对象及其任何相关元数据的信息。文档中是以键值对的形式存储数据。值的类型和结构可以有多种，包括字符串、数字、日期、数组等。文档存储的格式可以是JSON,BSON（二进制形式的JSON）和XML。文档中的键值对可以嵌套。
文档数据库介绍：https://blog.csdn.net/miaoyibo12/article/details/121084366

列存储数据库
简单地说，行存储就是数据在“页”中是按原本数据表的一行一行数据存储的，例如 MySQL、Oracle。列存储中数据是按原本数据表的一列一列存储的。行存储适用于OLTP场景，列存储适用于OLAP场景。
行存储：https://blog.csdn.net/li1325169021/article/details/121044179
列存储：https://www.jianshu.com/p/d1114dd4f77a

CHAR和VARCHAR的区别

CHAR(m)所占字节数都是m个字节，这表示CHAR(m)是固定长度；VARCHAR(m)所占字节数是实际长度+1个字节
VARCHAR多出的一个字节是用来保存实际长度值
因为CHAR无论存储的数据长度本身是多少，都会占用固定的长度，因此存储空间会有一定的浪费；VARCHAR由于是可变长度，更节约存储空间
VARCHAR的缺点是影响性能，若UPDATE后VARCHAR列的字符串长度变长了，就需要重新分配存储空间
对于InnoDB引擎，VARCHAR的性能影响可以忽略，具体见下面存储引擎的介绍 ———

DATETIME和TIMESTAMP的区别

TIMESTAMP类型的字段在insert和update时可指定更新为当前时间
TIMESTAMP类型显示的时间与系统时间所处的时区有关。例如，系统时间设为东八区时，显示的值为’2018-05-24 14:59:59’；系统时间设为东九区时，显示的值为’2018-05-24 15:59:59’
DATETIME类型只显示插入时的时间，即显示的时间不考虑时区变化
TIMESTAMP表示的日期范围比DATETIME要短得多

时间类型DATETIME直接相减的结果

如语句：

1	select create_time, update_time, update_time-create_time as pay_time from t_order;

这样写是错的！
因为，mysql在时间相减时，做了个转换，但不是UNIX_TIMESTAMP(create_time)这样的转换，而是把DATETIME直接转成了整数，如create_time=’2020-05-15 08:55:25’转成了20200515085525，因此得到的是两个无意义的整数相减的结果。
因此，对于时间类型的相减，正确处理应该是 UNIX_TIMESTAMP(update_time)-UNIX_TIMESTAMP(create_time)

HAVING和WHERE的不同使用场景

WHERE 就用在普通的SELECT语句作为查询条件，而 HAVING 用在聚合函数的字段上。如语句：

1	select currency_type, sum(amount) as total_amount from t_order where merchant_id='123456' group by currency_type having total_amount>0;

时间函数

UNIX_TIMESTAMP() 返回秒数，也可以带上时间。示例：
SELECT UNIX_TIMESTAMP(‘2020-06-16’); == 1592265600
SELECT UNIX_TIMESTAMP(‘2020-06-16 02:00:00’); == 1592272800
FROM_UNIXTIME() 输入秒数返回日期字符串，日期格式可指定
SELECT FROM_UNIXTIME(1592265600); == 2020-06-16 00:00:00
SELECT FROM_UNIXTIME(1592265600, ‘%Y-%m-%d’); == 2020-06-16

事务

事务四要素ACID、隔离级别、并发问题等见博客《隔离级别与脏读等并发问题》。

MySQL对事务的处理默认是“自动提交模式”。当你用SHOW GLOBAL VARIABLES LIKE '%autocommit%'可以查看autocommit开关是否打开，默认是打开的。这意味着，MySQL会在每条SQL执行完后自动提交事务，即一条SQL就是一个事务。

当你需要让多条SQL在一个事务中，需要先把autocommit开关关闭，再用SQL关键字：BEGIN、COMMIT、ROLLBACK手动开始、提交、回滚事务。需要注意，只有InnoDB引擎的表才支持事务，即在事务中只能操作InnoDB表。

菜鸟教程，手动处理事务的SQL：https://www.runoob.com/mysql/mysql-transaction.html

存储引擎

关系数据库表是用于存储和组织信息的数据结构。我们在实际开发过程中，可能需要各种各样的表，如查询快且少更新的表、经常更新的表。表的用途上的区别，就是对数据处理上的区别。对于MySQL来说，它提供了很多种类型的存储引擎，我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。

MySQL存储引擎包括：InnoDB、CSV、MyISAM、MEMORY等等。使用SHOW ENGINES命令可以查看你的Mysql支持的所有存储引擎。

InnoDB

特点：

支持行级锁（也支持表级锁）
支持事务
支持外键（MySQL支持事务和外键的存储引擎只有InnoDB）
支持AUTO_INCREMENT列
数据和索引放在同一个文件
创建InnoDB表时，在”MYSQL_HOME/data/db_name”文件夹下，新增2个文件：
table_name.frm -> 存储表定义
table_name.ibd -> 存储数据和索引
使用聚集索引，因此表中一定有主键ID。若用户未指定，InnoDB会自动指定。聚集索引在“索引”一节介绍

应用场景：

要求事务安全

MyISAM

特点：

只支持表级锁
不支持事务和外键
支持AUTO_INCREMENT列
数据和索引分开放在两个文件
创建MyISAM表时，在”MYSQL_HOME/data/db_name”文件夹下，新增3个文件：
table_name.frm -> 存储表定义
table_name.MYD -> 存储数据
table_name.MYI -> 存储索引
支持3种表：静态表、动态表、压缩表
- 静态表：记录都是固定长度，存储非常迅速，容易缓存，出现故障容易恢复；但是占用的空间比较多。PS：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。
- 动态表：记录不是固定长度的，占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能
- 压缩表：因为每个记录是被单独压缩的，所以只有非常小的访问开支
不使用聚集索引，因此表中可以没有主键ID

应用场景：

不要求事务安全
不经常UPDATE的表。这与“MyISAM的锁机制”有关

要对比两者的SELECT、INSERT效率，需要先了解它们在锁机制和索引上的区别。

锁机制

锁的分类：

表级锁：保证同一时刻只有一个线程操作该表，避免冲突
- 读锁：也叫共享锁（S锁）。不拒绝其他对同张表的读请求，会拒绝对同张表的写请求。持有该锁的线程只能进行读操作
- 写锁：也叫排它锁（X锁）。拒绝对同张表的所有请求。持有该锁的线程可以进行读、写操作
行级锁：保证同一时刻只有一个线程操作该记录，避免冲突
- 读锁：也叫共享锁（S锁）。不拒绝其他对同条记录的读请求，会拒绝对同条记录的写请求。持有该锁的线程只能进行读操作
- 写锁：也叫排它锁（X锁）。拒绝对同条记录的所有请求。持有该锁的线程可以进行读、写操作

总之，在同一级中，读锁与写锁互斥，不能同时存在。写锁与其他写锁互斥，读锁与其他读锁兼容。读锁不阻碍读请求，阻碍写请求。写锁阻碍所有读写请求。

其他分类：

临时锁与持续锁：指的是锁的时效性，临时锁-当前语句结束就释放锁，持续锁-当前事务结束才释放锁
乐观锁与悲观锁
- 悲观锁：假定当前事务操纵数据资源时，肯定会有其他事务同时访问该数据资源，为了避免当前事务的操作受到干扰，先锁定资源。悲观锁需使用数据库的锁机制实现，如使用行级排他锁或表级排它锁。可以解决丢失更新和不可重复读，但影响并发性能
- 乐观锁：假定当前事务操纵数据资源时，不会有其他事务同时访问该数据资源，因此不在数据库层次上的做锁定，而使用由程序逻辑控制的技术来避免可能出现的并发问题。唯一能够同时保持高并发和高可伸缩性的方法就是使用带版本检查的乐观锁。不能解决脏读的问题，因此仍需要数据库至少启用“读已提交”的事务隔离级别。

MyISAM的锁机制

MyISAM只支持表级锁。

什么时候锁：

执行SELECT前，会自动给所有涉及到的表加读锁
执行INSERT、UPDATE、DELETE前，会自动给所有涉及到的表加写锁

锁调度：

线程到来时，该表已经被锁的话，锁会放入锁等待队列中
写锁优先。同时有两个线程对同一张表分别请求读锁与写锁，先加写锁。在锁等待队列中，即使读锁请求时间更早，写锁也会放到读锁之前（用户可以修改这个规则）

因为经常性的UPDATE会不断请求表写锁，写锁优先，会导致SELECT操作阻塞，所以MyISAM表不适合经常UPDATE。

MyISAM不会出现死锁，原因：因为MyISAM总是一次性获得所需的全部锁，例如SELECT两张表时要求一次性获得这两张表的读锁，不能全部获得就等待，直到可以全部获得为止。（可对比下面的“InnoDB为什么会出现死锁”）

InnoDB的锁机制

InnoDB支持行级锁、表级锁。但InnoDB的表级锁属于意向锁，和MyISAM的表级锁有点不同。

什么时候锁：

行读锁：SELECT语句显式加行读锁(SELECT … LOCK IN SHARE MODE)（普通SELECT语句不会加任何锁）
行写锁：执行INSERT、UPDATE、DELETE前，会自动给所有涉及到的行加行写锁；SELECT语句显式加行写锁(SELECT … FOR UPDATE)
表读锁：也叫意向共享锁（IS锁）。给行加行读锁前，先加表读锁
表写锁：也叫意向排他锁（IX锁）。给行加行写锁前，先加表写锁

总之，一个事务要查询某一行，不需要任何锁；一个事务要修改某一行，需要行写锁+表写锁。

InnoDB 的行锁比较特殊，它不是加在某个数据行上，而是加在索引上的。这意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁（非意向锁）！【InnoDB 也支持非意向的表级锁

意向锁

意向锁的含义是，MySQL察觉到事务有意向对表中的某条记录加读锁/写锁，就给整张表加个表锁。但这个表锁的兼容性和MyISAM的表级锁不同。

意向锁的兼容性

意向锁之间：
1. IS和IS兼容
2. IS和IX兼容
3. IX和IX兼容
意向锁IX、IS和行级S锁、X锁之间：全部互相兼容！
意向锁IX、IS和表级S锁、X锁之间：
1. IS和S兼容
2. IS和X互斥
3. IX和S互斥
4. IX和X互斥

这里所说的不同种类的锁兼容的含义是，一张表可以同时加上这些互相兼容的锁。例如“IS和IX兼容”是指，一张表中可以同时存在多个IS锁和多个IX锁。

意向锁的作用

第一，在没有意向锁存在的情况下，当事务A准备给t_order表加表读锁（非意向锁）时，需要确认：

当前表没有被加表写锁
当前表中没有记录被加行写锁

为了确认第2点，事务A需要检查每一行记录的锁，效率很低。有了意向锁之后，事务A准备给t_order表加表读锁（非意向锁）时，只需要确认：

当前表没有被加IX锁

若确认当前表存在IX锁，事务A就能得知当前表中一定存在行写锁，就不需要检查每行记录，只需等待IX锁释放就行。

第二，意向锁之间的互相兼容，可以提高并发性能，也能保证并发环境下的事务隔离。举个例子，
事务A执行SQL：

1	SELECT * FROM t_order WHERE id = 1 FOR UPDATE;

此时t_order表存在两把锁：表上的IX锁、id=1的记录上的X锁。

事务B执行SQL：

1	SELECT * FROM t_order WHERE id = 10 FOR UPDATE;

因为IX锁可以兼容，所以事务B也可以给t_order表加上IX锁，再因为id=10的记录上不存在X锁，所以事务B不需要等事务A释放锁。

死锁

InnoDB为什么会发生死锁？

在InnoDB中，锁是逐步获得的，这就决定了在InnoDB中发生死锁是可能的。

举例，下面这句SQL：

1	update students set score = 100 where id < 30;

这一条SQL就是一个事务，这条SQL会更新满足条件的多条记录。找到第一条记录时，会给第一条记录上锁，第一条记录更新完成后，找到第二条记录并加锁，第二条更新完成再给第三条加锁，以此类推。

若这句SQL和上面那句同时开始：

1	update students set score = 100 where age > 30;

假设第一条SQL的加锁顺序：id = 15 -> 18 -> 20，第二条SQL的加锁顺序（age为普通索引，所以会用行锁）：(age, id) = (24, 18) -> (24, 20) -> (25, 15) -> (25, 49)。其中，对 id 的加锁顺序为 id = 18 -> 20 -> 15 -> 49。可以看到事务A先锁15，再锁18，而事务B先锁18，再锁15，从而形成死锁。

索引

索引就好比一本书的目录，它会让你更快的找到内容。但目录（索引）并不是越多越好，假如这本书1000页，有500页是目录，它当然效率低，目录是要占纸张的，而索引是要占磁盘空间的。

索引用于快速找出在某个列中有一特定值的行。如果不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻数据文件的中间，没有必要看所有数据。（这里的原理见下面B+TREE索引的介绍）

索引是帮助MySQL高效获取数据的数据结构。

索引类型

普通索引：最基本的索引，没有任何限制
唯一索引（约束）：与”普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。可指定多个列构成一个唯一索引
主键索引（约束）：它是一种特殊的唯一索引，不允许有空值
全文索引：仅可用于MyISAM表，针对较大的数据，生成全文索引很耗时间和空间
组合索引：遵循“最左”原则。创建组合索引时应该将最常用作查询条件的列放在最左边，依次递减

使用索引

单列索引：
SELECT UPDATE DELETE语句中WHERE条件带上索引列
SELECT语句的ORDER BY/GROUP BY带上索引列

组合索引：
SELECT UPDATE DELETE语句中WHERE条件中一定要包含最左边的索引列

注意：

若where条件中用到索引，则order by不会再用索引
order by不使用普通索引和组合索引
模糊查询中，若查询条件这样写：… where name like ‘%cai%’，则不会用到索引（原因是%放在最前）
mysql认为全文扫描比使用索引快时，即使where条件有索引也不会用索引，例如：在一张共100条记录的表中select前90条记录
1
select * from t_order where id<=90;

B+Tree索引

B+Tree是BTree的变种，MySQL的BTree索引就使用它来作为数据结构。MyISAM表、InnoDB表的索引就是用B+Tree存储，一个索引就是一个B+Tree。在B+Tree中，非叶子节点只保存key与指针，叶子节点的两个域分别是key与data。

MyISAM的B+Tree索引

在MyISAM的B+Tree中，叶子节点的data域存放的是数据记录的地址，这是因为MyISAM的索引和数据是分两个文件存放的。

下面是MyISAM索引的原理图。

主键索引（key为主键列值）：

普通索引（key为普通索引列值）：

MyISAM的主键索引和普通索引在结构上没有任何区别，只是主键索引要求key是唯一的，而普通索引的key可以重复。
因此，MyISAM中索引检索的算法是：首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB的B+Tree索引

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一，MyISAM的data域仅保存数据记录的地址。而InnoDB的数据和索引存放在一个文件里，数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录，key是主键列值。

这种索引就叫聚集索引，即数据和主键聚在一起。正因为InnoDB的数据文件要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二，InnoDB的普通索引data域存储相应记录主键的值而不是地址

这样导致使用普通索引搜索需要检索两遍索引：首先检索普通索引获得主键，然后用主键到主索引中检索获得记录。

知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整（如主键最大是id=500，现在要插入id=400），十分低效，而使用自增字段作为主键则是一个很好的选择。另外，若使用int型字段做主键，InnoDB也会强制要求该字段设置AUTO_INCREMENT。

MyISAN和InnoDB的对比

在介绍MyISAM时说了它的INSERT效率高，一个原因是它不像InnoDB要检查外键约束，还要维护事务，减少了一部分开销。另一个原因是，B+Tree结构在插入新记录时要分裂调整，但MyISAM的data域存的是数据的物理地址，和InnoDB的data域就是记录相比，内容较小，分裂调整的速度更快。但是，MyISAM的INSERT效率不一定就比InnoDB高，一个是因为MyISAM的表锁，一个是因为InnoDB如果使用自增主键，B+Tree的调整就不会那么麻烦。

什么是页？
“页”是指存储引擎从磁盘读取数据时，每次可以读取多少数据。换个说法，存储引擎每次从磁盘中读取一整页的数据，这“一整页的数据”有多少。为什么一次读取可以读到多条数据？这是因为B+Tree的一个叶子节点上就有多条数据，所以当查找到这个叶子节点时，就可以把这个叶子节点上的所有数据一次性读出来。
MySQL 页的理解：https://blog.csdn.net/li1325169021/article/details/121044179

InnoDB插入数据会进行什么分裂调整？
因为InnoDB的数据位置是按照主键的顺序安排的，主键相近的，数据会在一个叶子节点上。若主键不是规律自增的，就会在插入时要B+Tree重新调整主键位置，即调整了数据的位置，原本靠在一起的数据也许会被分开。靠在一起的数据在一个叶子节点上，即在一个“页”上，所以这就导致了“页”的分裂。而如果主键自增，那么每次插入都是在B+Tree的最后新增数据，不会改变前面数据的位置，不用进行页分裂。

另外，在查询效率上，也不能确定MyISAM和InnoDB谁更高，只能说各自都有优势。MyISAM的主键索引和普通索引的data域都是数据的物理地址，而InnoDB用普通索引检索时要搜两遍索引。但是，MyISAM和InnoDB的缓存机制不同（见下一节缓存池），MyISAM只缓存索引，InnoDB缓存索引和数据。因此，MyISAM每次查找就要从磁盘里拿数据，而InnoDB首先从内存里获取数据，如果没有再到磁盘里拿。InnoDB内存里的数据不是一下子就缓存。InnoDB在一次IO读取中，就会把这个节点上的所有数据缓存，若下次查找的数据还在这个节点上，就可以直接从缓存中取了。如果InnoDB内存足够大，查询足够多，InnoDB速率可能会超过MyISAM。

再者，如果InnoDB用主键进行范围查找，效率是很高的，因为它相近的数据都因为主键的原因在一个“页”上，这就是聚集索引，而MyISAM的主键不是聚集索引，数据的物理地址并不会靠在一起。

但是，需要注意的是，InnoDB查找时用到了索引，才会加行锁，否则是加表锁。

MyISAN和InnoDB的缓存池

MyISAN 的缓存池叫“Key Cache”，它只缓存索引，不缓存数据，所以 MyISAM 取数据要从磁盘中取。

InnoDB 的缓存池叫“Buffer Pool”，中文名叫“缓存池”“缓冲池”，它缓存索引，也缓存数据。

缓存池的缓存算法都是 LRU（最近最少使用算法），都占用系统内存空间。