MYSQL进阶

之前写了篇文章, 记录了MySQL的一些常用命令, 现在看来仍然有遗漏的地方, 所以补充一下

组合查询

即UNION

使用UNION来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。
每个查询必须包含相同的列、表达式和聚集函数。
默认会去除相同行，如果需要保留相同行，使用UNION ALL。
只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

内置函数

内置函数很多, 见: MySQL 函数

事务

我们一般使用START TRANSACTION或BEGIN开启事务, COMMIT提交事务中的命令, SAVEPOINT: 相当于设置一个还原点, ROLLBACK TO: 回滚到某个还原点下
一般的使用格式如下:

START TRANSACTION
-- ...
SAVEPOINT delete1
-- ...
ROLLBACK TO delete1
-- ...
COMMIT

锁

开启事务时, 默认加锁
根据类型可分为共享锁（SHARED LOCK）和排他锁（EXCLUSIVE LOCK）或者叫读锁（READ LOCK）和写锁（WRITE LOCK）。

读锁（共享锁）：针对同一块数据，多个读操作可以同时进行而不会互相影响。由读表操作加上的锁，加锁后其他用户只能获取该表或行的共享锁，不能获取排它锁，也就是说只能读不能写。
写锁（排它锁）：当当前写操作没有完成之前，它会阻断其他写锁和读锁。由写表操作加上的锁，加锁后其他用户不能获取该表或行的任何锁。

根据粒度划分又分表锁和行锁。表锁由数据库服务器实现，行锁由存储引擎实现。

除此之外,我们可以显示加锁

SELECT .... LOCK IN SHARE MODE; -- 加共享锁

SELECT .....FOR UPDATE; -- 加排他锁

加锁时, 如果没有索引，会锁表，如果加了索引，就会锁行

死锁

InnoDB默认支持行锁，获取锁是分步的，并不是一次性获取所有的锁，因此在锁竞争的时候就会出现死锁的情况
解决方法:

• 首先要解决死锁问题，在程序的设计上，当发现程序有高并发的访问某一个表时，尽量对该表的执行操作串行化，或者锁升级，一次性获取所有的锁资源。
• 然后也可以设置参数innodb_lock_wait_timeout，超时时间，并且将参数innodb_deadlock_detect打开，当发现死锁的时候，自动回滚其中的某一个事务。

事务的基本特性

即ACID特性:

1. 原子性: 一个事务中的操作要么全部成功, 要么全部失败
2. 一致性: 数据库总是从一个一致性的状态转换到另一个一致性状态, 比如A转账个B100块, 假如转账期间系统崩溃, A也不会损失100块, 因为事务还未提交, 不会保存到数据库中
3. 隔离性: 一个事务在提交前, 对其他事务是不可见的
4. 持久性: 一旦事务提交, 所做的修改就会永久保存到数据库中

事务并发引发的问题

1. 丢失更新
   事务A提交或撤销时, 事务B把数据覆盖了

2. 脏读
   事务A执行过程中, 修改了数据, 事务B读取了, 但事务A后来回滚了, 则事务B读取的数据为脏数据
   即: 读取了另一个事务未提交数据的现象为脏读
   

3. 不可重复读
   事务B读取了两次数据, 但其间事务A修改了数据, 导致事务B两次读取数据不一致
   

4. 幻读
   事务B两次读取同一范围的数据, 但其间事务A新增了数据, 导致事务B后面读取的数据与前面读取的数据行数不一致
   幻读强调集合增减, 不可重复读强调数据修改

   

隔离级别

由于并发事务会引发上面这些问题, 我们可以设置事务的隔离级别解决上面的问题.
MySQL的默认隔离级别（可重复读）

查看当前会话隔离级别
方式1

SHOW VARIABLES LIKE 'transaction_isolation';

+-----------------------+--------------+
| Variable_name  | Value |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+--------------+

方式2

SELECT @@transaction_isolation;


+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+

设置隔离级别

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

GLOBAL时, 只对执行完该语句之后产生的会话起作用, 当前已经存在的会话无效
SESSION时, 对当前会话的所有后续的事务有效, 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务 如果在事务之间执行，则对后续的事务有效。
不指定时, 只对当前会话中下一个即将开启的事务有效, 下一个事务执行完后，后续事务将恢复到之前的隔离级别, 该语句不能在已经开启的事务中间执行，会报错的
level的四个值: REPEATABLE READ READ COMMITTED READ UNCOMMITTED SERIALIZABLE

主从集群与读写分离

主从集群的示意图如下:

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

• binlog dump 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
• I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
• SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

同步流程:

1. MySQL变更数据时, 会记录到Binlog上
2. 主节点binlog dump线程监听, Binlog是否改变, 假如改变MySQL将主节点的Binlog同步给从节点
3. 从节点的I/O 线程接收Binlog数据, 并写入到relay log文件
4. 从节点的SQL 线程读取replay log文件, 并更新数据, 完成主从之间数据同步

由于MySQL主从集群只会从主节点同步到从节点, 不会反过来同步, 所以需要读写分离

读写分离需要在业务层面实现, 写数据只能在主节点上完成, 而读数据可以在主节点或从节点上完成

除此之外, 还可以将Binlog数据同步给Redis Kafaka等中间件, 同步数据

索引

索引是帮助MySQL高效获取数据的排好序的数据结构
MySQL的索引有

1. 主键索引
   建立在PRIMARY KEY上字段的索引称为主键索引, 值非空且唯一
2. 唯一索引
   建立有UNIQUE的字段上的索引, 可以有多个唯一索引, 值可以为空且多个空值不会冲突
3. 普通索引
   我们一般建立的INDEX
4. 前缀索引
   对于一些字符或字节类型的数据, 可以取前几个字符或字节建立索引, 但是不能ORDER BY和GROUP BY

B树与B+树

推荐两个在线工具:

1. B树可视化工具: B-Trees
2. B+树可视化工具: B+ Trees

简单来说, B树是在红黑树(一个平衡二叉树)的基础上将一个节点存放多个值, 实现的, 降低了树的高度, 每个节点都存放索引及对应数据指针, 同一层的节点是递增的
而B+树在B树的基础上进行优化, 非叶子节点存放 子节点的开始的索引, 叶子节点存放索引和数据的指针, 且叶子节点之间有双向的指针
如下示意图:

不同的引擎, 主键索引存放的数据也不一样, 比如常见的MyISAM和InnoDB
MyISAM的B+树叶子节点存放表数据的指针, InnoDB的B+树叶子节点存放处主键外的数据

其他的:

1. InnoDB支持事务, MyISAM不支持事务
2. InnoDB支持外键, MyISAM不支持
3. InnoDB支持聚集索引, MyISAM, (即上面说的)
4. InnoDB不保存表的行数, select count(*) ...时, 全表扫描, 而MyISAM保存了整个表的行数, 可以直接读取

联合索引及最左前缀原则

即多个列组成一个索引, 语法:

 create index indexName on  tableName(column1,column2,...,columnN)

由于联合索引的B+树的结构, 根据列建立, 所以我们的查找条件也要根据索引列的顺序(where column1=x, column2=y,columnN...), 否则会全表扫描

索引失效的情况及原因

1. 使用!=或者<>导致索引失效

   SELECT * FROM `user` WHERE `name` != 'lczmx';
   

   我们给name字段建立了索引，但是如果!= 或者<>这种都会导致索引失效，进行全表扫描，所以如果数据量大的话，谨慎使用

2. 类型不一致导致的索引失效
   这涉及MySQL的类型转换,

   • 索引为整形, 而比较值为字符串时, 会将字符串转化为数字, 除了是数字字符串外, 其他的都为0 ("1"=>1, "a"=>0)
   • 索引为字符串, 比较值为整形时, 会将索引装换为数字, 会破坏二叉树且不一定合法, 所以会走全表扫描

3. 函数导致的索引失效

   SELECT * FROM `user` WHERE DATE(create_time) = '2022-03-03';
   

   如果你的索引字段使用了索引，不走索引

   实际上, 一般只要是对索引操作的, 都不会走索引

4. 运算符导致的索引失效

   SELECT * FROM `user` WHERE age - 1 = 20;
   
   

   如果你对列进行了（+，-，*，/，!）, 那么都将不会走索引。

   实际上, 一般只要是对索引操作的, 都不会走索引

5. OR引起的索引失效

   SELECT * FROM `user` WHERE `name` = '张三' OR height = '175';
   

   OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。

6. 模糊搜索导致的索引失效

   SELECT * FROM `user` WHERE `name` LIKE '%mx';
   
   

   这个我相信大家都明白，模糊搜索如果你前缀也进行模糊搜索，那么不会走索引。

7. NOT IN NOT EXISTS导致索引失效

   SELECT s.* FROM `user` s WHERE NOT EXISTS (SELECT * FROM `user` u WHERE u.name = s.`name` AND u.`name` = 'lczmx')
   
   SELECT * FROM `user` WHERE `name` NOT IN ('lczmx');
   

   这两种用法，也将使索引失效。另IN会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描, 见: MySQL中使用IN会不会走索引

8. IS NULL不走索引，IS NOT NULL走索引

   SELECT * FROM `user` WHERE address IS NULL
   

   不走索引。

   SELECT * FROM `user` WHERE address IS NOT NULL;
   

   走索引。

   所以设计表的时候, 建议不可为空, 而是将默认值设置为""( NOT NULL DEFAULT "")