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 "")