MySql学习笔记

参考《Mysql必知必会》《MySqlCookBook》

常见事务术语

脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。(读未提交会发生)

不可重复读 ：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。(重点是单条数据不同，要避免需要锁单条数据)（读已提交的隔离级别会发生）

幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。(重点记录数不同，要避免需要锁整张表)（可重复读的隔离级别会发生）

序列化的隔离等级，能预防各种问题，不过效率太低(Mysql默认是可重复读)

为什么使用二进制日志(二进制日志是持续录入修改的)

复制：可以把对服务器做的更改以流的方式传输到另一台服务器，主服务器复制到从服务器，用于分配负载

时间点恢复：二进制日志可以直接恢复到发生异常前(必须有备份，从某个时间点的备份根据二进制日志恢复，一般在恢复时会禁用二进制日志)

二进制日志文件使用

RESET MASTER 清理全部二进制文件，并重新开始

SHOW BINARY LOGS 查看所有生成的二进制文件

MySQL的binlog(二进制日志)的录入格式

statement模式：只记录可能改变数据的sql(可能并未修改)，不记录改变的每一行

row级别：仅保存那条记录被修改

mixed： 一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row

注意上面无法解决使用系统变量问题 例如使用 @@hostname 引用主机名(使用@@变量,使用内置变量)

常用的数据库存储引擎

Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束，不支持全文索引(5.6之后支持)聚集索引

MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键，支持全文索引(非聚集索引)

MEMORY引擎：所有的数据都在内存中，不支持事务，全文索引，外键，数据的处理速度快，但是安全性不高(支持B+树，Hash前面两种不支持Hash,默认使用hash索引)

Innodb引擎与MyIASM引擎都使用B+树

聚集索引和非聚集索引的区别

聚集索引，在索引页里直接存放数据，而非聚集索引在索引页里存放的是索引，这些索引指向专门的数据页的数据。

InnoDB必须要有聚集索引，且只有一个聚集索引，可以有多个非聚集索引

1，若定义了PK，则PK就是聚集索引

2，若没有第一个not Null Unique列就是聚集索引

3，否则会创建一个隐藏的row-id做聚集索引

注意InnoDB,主键(聚集索引)索引直接存储数据，非主键索引，存放主键索引

MyISAM,主键索引与非主键索引几乎没有什么区别

非主键索引查找过程

后面的为普通索引无法直接定位数据存储是索引，必须使用索引再次回表查询

若查询的列只需要一次查询就能全部获取，无需回表，就是索引覆盖

事物的四大特性(ACID)

原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

ACID靠什么保证

原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

一致性一般由代码层面来保证

隔离性由MVCC来保证

持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

当前读，快照读和MVCC

当前读：获取记录的最新版本，要保证其他并发事务不修改，会对读取的列加锁(悲观锁)

快照读：快照读的实现是基于多版本并发控制(MVCC)避免读写加锁，基于多版本，可能读到的不是最新版本(序列化级别下会退化为当前读)

按照锁的粒度分数据库锁

行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁，开销最大(只有InnoDB使用行锁)

表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少(MyIASM,MEMORY使用)

页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁，取了折衷的页级，一次锁定相邻的一组记录

数据库的锁

内部锁(Mysql自身服务器内部执行内部锁，管理多个会话对表内容的争用)

外部锁(Mysql为客户会话提供的选项来显式获取表锁，以阻止其他会话访问)

使用Mysql锁

LOCK TABLES 表名 [READ|WRITE] …（可以同时锁定多个表） 获取锁(读锁(共享锁)，写锁(排他锁)，与Java读写锁一致)

UNLOCK TABLES 释放锁

锁队列

除了共享锁(读锁)一个表不能同时加多个其他锁，其他锁会阻塞在锁队列

not null的优点

NULL 列需要更多的存储空间：需要一个额外字节作为判断是否为 NULL 的标志位。

查询时可以减少空指针异常

主键

一列（或一组列，若为一组，所以列的组合必须唯一，单个可以不唯一）其值能唯一区分表的每个行，没有主键，更新删除特定行非常困难，因为没有安全的方法保证只涉及相关行，（主键唯一且非空）建议不更新主键值，使用纯粹的主键，使用不变属性当作主键

SHOW 函数

SHOW DATABASES 显示已有的所以数据库

SHOW TABLES 显示数据库已有的表

SHOW COLUMNS FROM 表名 显示表数据列信息(与DESCRIBE 表名一样)

SHOW GRANTS 显示授权用户的全部权限

SHOW VARIABLES 查看所有参数信息

SHOW ENGINES 查看所有数据库引擎信息(只有InnoDB支持事务)

SHOW CREATE TABLE 表名 查看生成指定表的语句

SHOW FULL TABLES 显示所有表包含视图与表

SHOW CREATE VIEW my_anime 查看创建视图的语句

SHOW PROCESSLIST 查看MYSQL各连接状态

未排序的数据

对于未排序的数据，返回数据的顺序没有特殊含义，可能与添加顺序相同，也可能不同（若没有删除，顺序是一致的，若发生过删除，会回收部分空间，造成顺序不一致）

ORDER BY(顺序从上向下看)

默认降序排序，可以指定多列，可以在指定列添加DESC进行降序操作(只对前面的一个属性有效)ASC升序 默认就是升序 SELECT * FROM cher ORDER BY age,id DESC

AND与OR

筛选条件中and优先级高于or,可以使用 括号 显示指定顺序

REGEXP

支持使用通配符同样使用\\转义 SELECT * FROM cher WHERE name REGEXP ‘利*’

函数

CONCAT 拼接函数

LTRIM RTRIM TRIM 用于移除字符串空格

支持常用字符串，日期函数，数学函数

运算

支持+，-，*，/运算 对于非数字字段全部按0处理(除TRUE外)，若发生数学异常直接返回NULL SELECT name-age FROM cher

COUNT聚集函数

COUNT（*） 统计多少行不忽略NULL值 COUNT(列名) 统计某一列忽略NULL值

MyISAM会维护一个数量变量 没有where的count()非常快

GROUP BY

分组，SELECT的列或聚集函数中的列必须在GROUP BY后给出 SELECT age,COUNT(age) FROM cher GROUP BY age

GROUP BY必须在WHERE 后 ORDER BY 前 也会对NULL分组 使用HAVING过滤分组

注意 WHERE 在分组前过滤 HAVING在分组后过滤

连接

内连接(SELECT * FROM cher INNER JOIN anime ON cher.id = anime.id)只保留两边都符合的数据(INNER 可以省略默认内连接)

左外连接(SELECT * FROM cher LEFT OUTER JOIN anime ON cher.id = anime.id)相比内连接 左边不符合的数据也会被保留

右外连接(SELECT * FROM cher RIGHT OUTER JOIN anime ON cher.id = anime.id)与左外连接相似 保留右边的(注意外连接OUTER可以省略)

UNION

连着输出两次查询的结果（必须保证查询列数一样，类型没有要求），会自动去重，可以使用UNION ALL取消去重

SELECT name,age FROM cher UNION SELECT name,type FROM anime

查询插入

查询数据插入表中，按顺序来的不关心列名，列数量必须一致 INSERT INTO anime(id,name) SELECT id,name FROM cher

删除，更新注意

尽量减少使用不带WHERE 的删除更新

尽量每个表都有主键

删除更新前，先执行查询操作

AUTO_INCREMENT

对于自增列，若插入0，NULL，或不指定，自动生成序列号，从1开始，若手动插入大于当前序号，会提高当前序号到插入值后一位，若小于，序合不变（自增列，默认生成的值，只增不减）

RENAME TABLE

RENAME TABLE table1 to table2,tableA to tableB 用于重命名表

为什么使用视图

重用SQL语句，简化复杂的SQL操作，使用表的部分，保护数据可以对用户只授权一部分，更新显示格式与表示

视图特点

视图主要用于查询

通常视图是可以更新的(直接操作原表)，若MySql无法处理正确的数据集则无法更新(例如使用了聚合函数)

数据库必须把视图的查询转化成对基本表的查询，性能差

数据库的三范式

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。

第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

第三范式：任何非主属性不依赖于其它非主属性。

Hash索引

只有MEMORY引擎支持hash索引，且为其默认索引

缺点：

需要读取表中索引数据进行散列计算，消耗时间

不能使用hash索引排序

hash索引只支持等值比较

hash索引不支持索引的部分匹配(hash是根据整体计算的)

若散列冲突十分严重，维护代价会很高

优点：

在散列冲突较少时对于等值查找十分快

乐观锁和悲观锁

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

常用数据库信息查询函数

SELECT VERSION() 获取当前 MySQL 数据库版本

SELECT DATABASE() 查看当前自己处于那个数据库

SELECT PASSWORD(‘root’) 查看指定用户的密码(加密后的)

克隆表结构

CREATE TABLE new_cher LIKE cher 创建指定表与指定表结构一致

REPLACE

若主键存在就删除原始数据插入新数据，不存在直接插入 REPLACE INTO test(id,name) VALUES(4,‘博丽灵梦’)

ON DUPLICATE KEY UPDATE

尝试插入，若主键冲突，转为使用后面的修改 INSERT INTO test(id,name) VALUES(4,‘OK’) ON DUPLICATE KEY UPDATE id = 1,name=‘pppppppp’

创建用户

CREATE USER ‘username’@‘网络地址’ IDENTIFIED by ‘密码’ username用户名 网络地址：允许登录的网络地址可以使用%通配符 密码 若不需要密码可以省略 后面的IDENTIFIED by ‘密码’

GRANT

分配权限在指定表给指定用户 GRANT INSERT(权限还可以指定SELECT(列名1,列名2),也可以指定ALL所有权限) ON cher(表，可以使用数据.*指定数据库的所有表) TO ‘sk’@’%’(用户)

查看指定用户的所有权限 SHOW GRANTS FOR ‘sk’@’%’

移除指定权限类似添加权限 REVOKE INSERT ON cher FROM ‘sk’

mysql.user

默认权限操作都是在操作这个表，不过哪些操作(GRANT,REVOKE)修改后会重新加入内存，直接操作改变，必须使用刷新表或重启，使修改生效 可以使用FLUSH PRIVILEGES 使权限重新加载

使用角色

创建角色，可以直接使用GRANT给角色添加权限与给用户添加权限一致 CREATE Role ‘ok’

为指定角色分配权限 GRANT ‘ok’(可以指定多个角色) TO ‘sk’@’%’

information_schema(数据库)

存储数据库元数据(关于数据的数据)，例如数据库名、表名、列的数据类型、访问权限等，是视图，而不是基本表，没有对应的文件

操作JSON

5.7开始允许操作JSON 直接修改数据类型为JSON就行了 插入JSON 注意外面用 单引号 里面用双引号

使用 json列->‘表达式’ 取值 $代表整个对象selectid,content-{>}^{\prime }$.a’ a from json 也可也当作查询条件

判断JSON是否有对应属性，且值相同，返回0/1 json_contains(content->’$.a’,‘12’)

判断是否包含指定属性 select json_contains_path(content,‘one’((one,all)模式，一个或全部),’$.a’(可以编写多个路径)) from json

json_set，json_insert，json_replace，json_remove

json_set更新json,可以更新已有属性，也可以添加属性json_insert，只添加没有的属性，json_replace，只更新属性不添加属性json_remove(content,’$.a^{\prime })移除指定属性，这些函数都可以设置多个参数updatejsonsetcontent=jso{n}_{s}et(content{,}^{\prime }$.a’,12,’$.name’,‘json’) where id = 1;

json_keys，json_length

select json_keys(content),json_length(content) from json 分别获取所有的键，和键值对数量

复制

在服务器主库上执行的所有DDL(数据库定义语言)和DML(数据库操作语言)都会被记录到二进制日志，连接到它的从库服务器获取日志复制到从库保存为中继日志，由一个IO线程负责，还有一个SQL线程，顺序执行中续日志的语句

复制的优点

提供了读写分离的能力，将负载分散到多个从库，提高性能，主库用于写操作，从库用于读取操作

减小数据库复制对业务的影响，可以在从库备份而不影响主库数据

减小数据分析对业务的影响，对信息分析在从库进行，不会影响主库性能

提升数据的安全性，任意一台MySQL服务器断开，都不会丢失数据，即使是master宕机，也只是丢失了那部分还没有传送的数据(异步复制时才会丢失这部分数据)

复制的拓扑形式

传统复制 单个主库多个从库

链式复制

主主复制

两个主库互相之间都可以接受写入与复制

多源复制

一个从库从多个主库，而非一个主库复制

半同步复制

默认复制是异步的，可能主库崩溃，尚未达到从库的数据会丢失，为了解决这个问题可以使用半同步复制

半同步复制中，主库会一直等待，直到至少有一个从库接收到写入的数据，之后从库发送确认信息，主库收到确认消息，然后再继续后续操作

完全同步复制，主库会一直等待直到所有从库提交事务

分区类型(分区仅适用InnoDB,且不能与外键结合使用)

RANGE：范围表分区，按照一定的范围值来确定每个分区包含的数据

LIST：列表表分区，按照一个一个确定的值来确定每个分区包含的数据

HASH：哈希表分区，按照一个自定义的函数返回值来确定每个分区包含的数据

KEY：key表分区，与哈希表分区类似，只是用MySQL自己的HASH函数来确定每个分区包含的数据

分区修剪

Mysql优化器会计算分区表达式，确定那个分区包含该值，不扫描没有匹配值的分区，称分区修剪

SELECT,UPDATE,DELETE，支持分区修改，INSERT不支持分区修剪（也可也WHERE后使用PARTITION(分区名…)指定分区）

分区管理

添加分区：会在短时间锁住整个表

重组分区：若存在MAXVALUE分区，则不能再添加分区，可以使用REORGANIZE MAXVALUE调整分区，也可以将多个分区重组为一个分区，重组分区过程中，MySql必须移动大量数据，将锁定表

删除分区：DROP 分区 相比DELETE TABLE 要快很多

TRUNCATE分区：仅删除数据，保留表结构

EXPLAIN

分析SQL语句 explain format = JSON 指定分析格式为JSON

id: 执行语句的序列号

select_type: 查询类型

（(1) SIMPLE(简单SELECT，不使用UNION或子查询等)

(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)）

table: 操作的表

partitions: 查询的分区

type: 对表访问类型

（ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。）

possible_keys: 查询可能使用的索引

key: 实际使用的索引

key_len: 索引字段的字节数

ref: 显示索引的哪一列被使用了

rows: 扫描出的行数(估计值)

fitered: 按条件过滤的行(百分比)

Extra: 重要的额外信息

（Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used：Query语句中使用from dual 或不含任何from子句）

数据库优化

对频繁查找的部分添加索引，若没有索引，以该字段为查找条件必须扫描整个数据库

优化数据结构： 写入数据少，查询就快 处理查询时磁盘上的内容会被加载进主内存，所以表越小，占用的主存空间越小 被索引占用的空间越小，同时加载的索引越多

删除重复冗余索引： 多余的索引会减慢插入速度

使用资源组： 可以使用资源组限制操作只使用一定的资源

最左前缀原则：对数据筛选力度比较大的放到左边 先筛选

避免使用 select *，仅列出需要查询的字段。

垂直分割分表。

选择正确的存储引擎。

char与varchar

char(20)不足的使用空格占位，varchar(20)只存必要的但是若加载进内存会占用全部长度，varchar若长度超过255需要两个字节存储长度（默认需要一个记录长度）

optimizer_search_depth

Mysql使用启发式算法查找连接表的最佳顺序，optimizer_search_depth指定深度（默认62）

生成列

指定生成列 full_name VARCHAR(21) GENERATED ALWAYS AS (CONCAT(first_name,’ ',last_name)) STORED NOT NULL

通过已经存在的值运算得出 生成列类型 STORED会存储起来，插入时计算并存储，VIRTUAL不实际存储读取时再计算

B树与B+树

B树

B+树

B+树所有数据都存储在叶节点，必须查找到叶子节点才能结束，且叶子节点使用指针连接(注意是双向链表)

为什么B+树更适合做索引

查询效率稳定：每次都要查询到根节点，路径长度一致

更方便遍历：叶子节点有指针相连，可以直接遍历，B树需要中序遍历

可以范围查询：底部双向链表相连，B树只能中续遍历

可以加载更多索引：B+树索引不存放数据，可以在小内存加载更多索引加快查询

索引优点

索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度

索引可以帮助服务器避免排序和创建临时表(索引默认是排序的，排序需要建立临时表)

索引可以将随机IO变成顺序IO//(使用有顺序的索引关联没有顺序的数据，使随机IO变成顺序IO)

索引缺点

增删改时要维护索引，耗费时间(随数据量增大而增大)

索引需要占用额外的物理空间

若某列重复度太高，建立索引效果不明显

在哪里建立索引

在经常搜索的列建立索引，在经常作为条件的列建立索引

在强制唯一的列上建立索引

在经常JOIN的列建立索引

在经常使用范围查找(>,BETWEEN,IN,排序等)的列建立索引，因为索引已经排序

优秀的索引

内存占用小，筛选力度高

索引查询失效

like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效

or语句前后没有同时使用索引

组合索引，不是使用最左列索引集合(最左原则，实际是从左边依次添加建立索引，例如使用A,B,C 相当于建立 A A,B A,B,C)

在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

对索引字段进行计算操作、字段上使用函数

索引类型

普通索引：没有任何限制，可以重复与空值，唯一任务就是加快系统对数据库访问速度

唯一索引：数据不能重复允许有空值，若是组合索引，只有组合唯一就行了，主要为了避免数据重复

主键索引：专门为主键创建的索引，不允许空值与重复

空间索引：对空间数据类型使用，必须声明NOT NULL只能在MyISAM中使用

全文索引：主要查找文本中的关键字，只能在CHAR，VARCHAR,TEXT上使用，允许重复与空值

Mysql文件类型

myISAM文件

xxx.frm：数据库表结构文件

xxx.MYD：数据库数据内容

xxx.MYI：数据库索引文件（每个索引子节点都指向数据库内容地址）

查询一条记录若存在索引，先在xxx.MYI(索引文件)中查询地址，再根据地址到xxx.MYD（数据库内容）查找对应数据

InnoDB文件

xxx.frm：数据库表结构文件

xxx.idb：索引+数据 因为是聚集索引，根节点直接存储数据不需要回表查询

InnoDB推荐使用整性自增主键

InnoDB底层使用数据页（默认16K）存储数据，存满了，会申请一个新的数据页来存储数据，若为自增ID可以直接申请新的，若为非自增，为了保证有序，需要插入到合适位置，若目标页满需要大量移动数据(页分裂)影响效率

主键占用内存越少，每个数据页存储的数据越多，减小树的高度，每次加载也越多，减少IO次数