MySQL进阶

CHAR是定长字符串，VARCHAR是变长字符串
CHAR在存储的时候会填充空格达到指定长度，检索时去掉空格，VARCHAR在存储时需要用1到2个额外字节记录字符串长度，检索时无需处理
CHAR适合存储长度较短或者长度都差不多的字符串，例如 MD5 算法加密后的密码、身份证号码。VARCHAR类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。
CHAR和VARCHAR无论存储字母、数字还是中文，每个都只占用一个字符

NULL和''的区别

NULL是一个不确定的值，就算是两个NULL，也不一定相等，例如SELECT NULL=NULL的结果为 false，但是在DISTINCT,GROUP BY,ORDER BY时,NULL又被认为是相等的。
''的长度是0，不占用空间，而NULL需要占空间
NULL会影响聚合函数的结果，例如SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。
查询NULL值时，必须使用IS NULL 或 IS NOT NULLl 来判断，不能使用=、!=、<等比较运算符，但是''是可以使用比较运算符的

Boolean类型如何表示

MySQL中没有表示Boolean类型的，但是可以使用TINYINT(1)来表示，其可以存储0或1，分别对应false或true

视图

视图就是将一条sql语句封装起来,之后我们使用sql时,只需要查询视图即可

视图不保存数据,数据还是在表中

-- 创建视图
CREATE VIEW view_admin AS SELECT * FROM admin;

-- 查询视图
SELECT * FROM view_admin

-- 删除视图
DROP VIEW view_admin

存储过程

将一些逻辑处理的代码可以事先存储在数据库中，使用时直接调用即可

可以减少应用程序与数据库之间交互的次数

语法

存储过程事先需要创建好(包含逻辑),可以向存储过程中传入参数,

参数分为3种：in(输入参数) out(输出参数) inout(即可以输入,也可以输出)

示例

/*
create procedure 存储过程名（[in 变量名 类型，out 参数 2，…]）
begin
[declare 变量名 类型 [DEFAULT 值];]
存储过程语句块;
end;
*/

DELIMITER $$
CREATE
    PROCEDURE `news`.`news_count`(IN p_typeid INT,OUT p_count INT)
    BEGIN
        SELECT COUNT(*) INTO p_count FROM news WHERE typeid=p_typeid;
        SELECT p_count;
    END$$
DELIMITER ;

-- 综合案例
-- 保存账号时,使用存储过程验证账号是否存在,并返回一个结果
-- 入参 账号 密码 性别 出参 结果
DELIMITER $$
CREATE
    PROCEDURE `news`.`saveAdmin`(IN p_account VARCHAR(20),IN p_gender VARCHAR(1),OUT p_res INT)
    BEGIN
             SELECT COUNT(*) INTO p_res FROM admin WHERE account = p_account;
             IF p_res=0 THEN
               INSERT INTO admin(account,gender)VALUE(p_account,p_gender);
             ELSE
               SELECT p_res;
             END IF;
    END$$

DELIMITER ;

存储过程在互联网公司应用不多，因为其难以调试和扩展，而且没有移植性，还会消耗数据库资源

触发器

触发器是一个特殊的存储过程,使用时并不需要用户调用,而是在增、删、改之前或之后自动执行

示例

-- 删除管理员前先删除管理员和角色的绑定关系
DELIMITER $$
CREATE
    TRIGGER `news`.`delete_admin_role` BEFORE DELETE
    ON admin
    FOR EACH ROW 
    BEGIN
         DELETE FROM admin_role WHERE adminid=old.id;
    END$$
DELIMITER ;

-- old 表示操作表中原来的数据 old表示操作的那一行的数据

MySQL架构

Server层

连接器：身份验证和权限校验(MySQL登录的时候)
查询缓存：执行查询语句时，会先查询缓存(MySQL 8.0已废除)
分析器：没有命中缓存，SQL语句会经过分析器，就是先看SQL语句要干啥，再看SQL语句语法是否正确
优化器：按照MySQL认为最优方案去执行
执行器：执行语句，然后从存储引擎返回数据

MySQL存储引擎

存储引擎是具体的操作数据的方式,是用于存储、处理和保护数据的核心服务。存储引擎是基于表的，而不是数据库。MySQL现在版本默认存储引擎是InnoDB，在5.5.5版本以前默认的是MyISAM

InnoDB和MyISAM的区别

InnoDB支持行级锁和表级锁，MyISAM只支持表级锁
InnoDB支持事务，MyISAM不支持事务
InnoDB支持外键，MyISAM不支持
InnoDB支持MVCC，MyISAM不支持
MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持，恢复过程依赖于redolog
InnoDB 的性能比 MyISAM 更强大，因为MyISAM不支持并发

电信，移动-->把上个月的消费记录，统计到一张表中，数据表适合MyISAM引擎

MySQL索引

索引是类似于书的目录,可以通过目录(索引)快速的定位到数据真实的位置,是排好序的,快速查找的数据结构(B+树)

在数据库中单独去维护一个树,树中的每一个节点存储主键和数据的物理地址,可以通过树形结构,快速锁定到数据的位置

优势

快速的定位数据的位置,减少了IO次数

降低了排序的成本

劣势

维护索引需要占用存储空间.

增删改操作时,还需要额外的维护索引树,需要消耗时间

索引一定能提高查询效率吗？

大多数情况下，索引都是比全表扫描快的，但是如果数据库的数据量并不大，那么使用索引也不一定会带来很大的提升

索引数据结构

首先索引是用B+树

为什么不用hash表？

hash索引不支持顺序和范围查询，如果查询id<500，难道把1~499全部hash计算吗

二叉查找树

特点：

左子树所有节点的值均小于根节点的值。
右子树所有节点的值均大于根节点的值。
左右子树也分别为二叉查找树。

在最差的情况下会退化成链表，导致查询效率急速下降

AVL树

AVL 树的特点是保证任何节点的左右子树高度之差不超过 1，因此又叫做平衡二叉查找树

采用旋转保持平衡

频繁进行旋转，导致较大的计算从而降低了性能，而且每个树节点只存储一个数据，频繁进行IO

红黑树

进行颜色变换和旋转操作

特点：

每个节点非红即黑
根节点总是黑色
每个叶子节点都是黑色的空节点（NIL 节点）
如果节点是红色的，则它的子节点必须是黑色的（反之不一定）
从根节点到叶节点或空子节点的每条路径，必须包含相同数目的黑色节点（即相同的黑色高度）

不追求严格平衡，只是大致平衡，导致查询效率稍有下降，可能导致高度较高，频繁IO

B树&B+树

索引是一个树形结构: B+树;

首先二叉树(大的在右边，直接就是链表),红黑树(自平衡)被排除了

B-树 自平衡多路树在一个节点中放多个数据,横向扩展,降低树的高度

B+树 自平衡多路搜索树排好序

会存在数据冗余,

叶子节点之间会有一个相互指向的指针(对于自增主键,范围查找非常合适)

两者异同

B树的所有节点存放的都是key和data，而B+树只有叶子节点存放key和data，其他节点只存放key
B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

索引类型

主键索引

一张数据表只能有一个主键，并且主键不能为null，不能重复

在MySQL的InnoDB的表中，如果没有显示的指定主键时，InnoDB会去寻找检查表中是否有唯一索引并且不允许为null的字段，如果有，则选择该字段为主键，否则InnoDB将会创建一个6Byte的自增主键

添加主键：ALTER TABLE 表名 add PRIMARY KEY 表名(列名);

删除主键: ALTER TABLE 表名 drop PRIMARY KEY ;

二级索引

又称为辅助索引，因为二级索引的叶子结点存储的数据是主键，也就是说，通过二级索引可以定位主键的位置

唯一索引，普通索引，前缀索引等索引属于二级索引

唯一索引

是一种约束，唯一索引的列不能出现重复数据，但是可以为NULL，一张表可以创建多个唯一索引。该索引是为了属性列的唯一性，而不是为了查询效率

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

删除唯一索引：DROP INDEX 索引名 ON 表名;

普通索引

就是为了查询快，一张表可以创建多个，并允许数据重复和NULL

CREATE INDEX 索引名 ON 表名(列名);

删除索引：DROP INDEX 索引名;

前缀索引

只适用于字符串类型的数据。是对文本的前几个字符创建索引

全文索引

模糊查询 like,会导致索引失效,这个时候就需要用到全文索引

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;

SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(‘搜索词')

覆盖索引和联合索引

覆盖索引

就是一个索引包含所有需要查询的字段的值

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据索引就可以查到数据，不需要回表查询

正常来说，我们不用主键索引去查询的话，就会进行回表查询，但是覆盖索引比如我们查询name，刚好name正好有索引，那么直接就根据该索引查到数据，无需回表

联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

例如：

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

最左前缀匹配原则

在使用联合索引，MySQL会根据联合索引的字段顺序，从左到右依次查询匹配，如果有匹配的字段，就会过滤一些数据，直到联合索引中全部字段匹配完成

在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段后面的字段无法用到联合索引。但是，对于 >=、<=、BETWEEN、like 前缀匹配这四种范围查询，并不会停止匹配。

举个例子：（a,b,c）创建联合索引

生效：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

不生效：

where b=2；
where c=3；
where b=2 and c=3；

为什么是最左匹配原则

是因为（a,b,c）创建联合索引，是先按a排序，在a相同的情况再按b排序，在b相同的情况再按c排序，所以b和c是全局无序，局部相对有序

聚簇索引和非聚簇索引

聚簇索引

即索引结构和数据一起存放的索引，并不是一种单独的索引类型。简单讲就是找到了索引,就找到了数据，InnoDB 中的主键索引就属于聚簇索引。

对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

通过主键查找,找到了主键,也就找到了数据(属于聚簇式)

使用非主键的列添加索引查询，就是非聚簇的，因为需要通过索引找到主键，然后通过主键二次回表查询，再通过主键查询到数据

优点：

查询速度非常快
对于排序查找和范围查找优化

缺点：

依赖于有序数据，如果索引数据不是有序的，那就要在插入的时候排序
更新代价大，因为其叶子节点还存放着数据

非聚簇索引

索引和数据是分离的，找到了索引，还没找到数据，需要根据主键，再次回表查询，才能够查询到数据

通过学号(不是主键)查询学号和姓名,先通过学号找到主键,再次通过主键去查找数据,这种场景是非聚簇的

优点：更新代价小，因为叶子节点不存放数据

缺点：

依赖于有序数据
回表查询

非聚簇索引一定会回表查询吗？

不一定！！！

用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引，就不用回表查询，直接返回就行

即使是 MYISAM 也是这样，虽然 MYISAM 的主键索引确实需要回表，因为它的主键索引的叶子节点存放的是指针。但是！如果 SQL 查的就是主键，直接返回即可，这样就被称为覆盖索引

如何判断是聚簇索引还是非聚簇索引

这次查询中能否直接命中数据.

索引下推

可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

索引创建原则

哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段(where后面的语句)
查询中排序的字段
分组中的字段
与其他表关联,外键关系
不为NULL的字段

哪些情况不需要创建索引

表记录太少
where条件里用不到的字段
数据重复且分布平均的表字段(例如性别男,女)
频繁更新的字段

建议：避免冗余索引（name,city）建立索引，在建立（name）这就是冗余

避免索引失效，例如在索引列进行计算、模糊查询、未遵循最左匹配原则、隐式转换(例如字符串'123abc'会转换为123，'012abc'会转换为012也就是12，'5.3a66b78c'会转换为5.3，其他同理。)

MySQL日志

主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类，比较重要的是二进制日志binlog(归档日志)和事务日志redo log(重做日志)和undo log(回滚日志)

redo log

重做日志是InnoDB独有的，让MySQL拥有了崩溃恢复的能力，保证数据的持久性和完整性

MySQL 中数据是以页为单位，查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从Buffer Pool中查找，如果没有，再去磁盘中，减少IO，提升性能

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

刷盘时机

事务提交
log buffer空间不足的时候
事务日志缓冲区满
后台刷新线程
正常关闭服务器的时候

另外InnoDB存储引擎有一个后台线程，每个1秒，就会把redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

也就是说，一个没有提交事务的 redo log 记录，也可能会刷盘。

日志文件组

硬盘上存储redo log日志文件不只一个，而是以一个日志文件组的形式出现，每个redo日志文件大小相同，采用的是环形数组形式，从头开始写，写到末尾又回到头循环写

每次MySQL加载日志文件组恢复数据时，会情况加载过的redo log记录

如果日志文件组满了，MySQL就要停下来，清空一下记录

为什么不直接将数据页刷盘，而要用到redo log？

数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，没有必要将整个数据页刷盘，redo log主要就是为了提高我们的性能，也让数据库并发能力更强

binlog

逻辑日志，不管什么存储引擎，只要出现了表更新，都会产生binlog日志

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，保证数据一致性。

记录格式

statement：记录SQL语句原文，但是有个问题，例如update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。
row：会记录详细数据，包括具体时间，但是需要更大的容量，占用空间，消耗IO资源
mixed：前两者混合，判断SQL语句是否会引起数据不一致

写入机制

事务执行过程中，先把日志写到binlog cache，事务提交后，再把binlog cache写到binlog文件中。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

两阶段提交

redolog在事务执行中可以不断写入，binlog在事务提交后写入，两者写入时机不同，那么可能会出现如下问题：

c=0，将c更新为1，假如写入redolog日志后，binlog日志写期间发生异常，这就导致binlog里面没有对应的修改记录。之后用binlog日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为redo log日志恢复，这一行c值是1，最终数据不一致。

解决方法就是两阶段提交，将redo log的写入拆成了两个步骤prepare和commit

写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。

redo log设置commit阶段发生异常，那会不会回滚事务呢？

不会回滚事务，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。

undo log

回滚日志，保证数据库原子性，发生异常时，对已经执行的操作进行回滚，而且回滚日志优先于数据持久化到磁盘上，所以即使数据库突然宕机，数据库还是可以通过查询回滚日志回滚到之前未完成的事务

总结

redolog(重做日志)保证事务持久性，undolog(回滚日志)保证事务原子性

binlog保证数据一致性

MySQL事务

事务是数据库为了保证数据操作的原子性、隔离性、持久性、一致性，提供了一套机制，在同一个事务中，如果有多条sql执行，事务确保执行的可靠性

事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务满足ACID特性

原子性（Atomicity）：一个事务中的多条sql要么都执行,要么都不执行
一致性（Consistency）：数据经过很多次操作,最终的结果要与预期的一致,保证数据的完整性
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
持久性（Durability）：一旦事务提交,数据就持久保存在硬盘上

原子性、隔离性、持久性都是为了保证数据的一致性

并发事务带来的问题

多个事务并发运行，经常会操作同一个数据，这样很有可能带来一些问题

脏读

一个事务读取数据并且进行修改，这个行为对其他事物来说是可见的，即使当前事务没有被提交。这时另一个事务读取了这个未提交的数据，但是第一个事务回滚了，导致数据没有提交给数据库，那第二个事务读取的就是脏数据

丢失修改

一个事务访问数据的同时，另一个事务也来访问。那第一个事务修改了数据后，第二个事务也修改了数据，这样第一个事务修改的结果就丢失了

不可重复读

在一个事务中两次读到的数据不一样

幻读

一个事务读取了几行数据，另一个事务插入了一些数据，随后第一个事务查询时发现多了一些原本不存在的记录，就像是幻觉一样

不可重复读和幻读的区别

不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

事务隔离级别

READ-UNCOMMITTED(读取未提交) ：最低隔离级别，允许读取未提交的数据，可能会导致脏读、不可重复读和幻读
READ-COMMITTED(读取已提交) ：允许读取已提交的数据，防止脏读，但是会导致不可重复读和幻读
REPEATABLE-READ(可重复读) ：对同一字段多次读取结果是一样的，除非被本身事务自己修改，可以防止脏读、不可重复读，MySQL默认隔离级别，但会导致幻读
SERIALIZABLE(可串行化) ：最高的隔离级别，所有事务依次执行，完全不受干扰，可以防止脏读、不可重复读、幻读

但是！InnoDB实现的REPEATABLE-READ隔离级别是可以解决幻读问题发生，主要是下面两种情况：

快照读：由MVCC机制来保证不出现幻读
当前读：使用 Next-Key Lock 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。

事务实现原理

原子性：原子性实现依赖的是undolog(回滚日志),保存每次操作的反向操作,如果我们执行insert操作,那么日志中保存的就是delete操作,当我们回滚时就会执行反向操作

持久性：每次当执行修改数据操作时,先会将语句保存到redolog(重做日志)中,即使突然断电,正常后也可以从日志中恢复数据

MVCC(多版本并发控制)

用于在多个并发事务同时读写数据库时保证一致性和隔离性

主要是针对读已提交和可重复读

读已提交：只要别的事务提交了,那么另一个事务就可以看到，有可能同一个事务两次查询数据不一致,实时访问的是最新的数据,当前读,每次读时,都会给版本链拍照,所以读到的数据是最新的

可重复读：B事务开始后,第一次读到的数据和之后读到的一样,这个过程中别的事务已经修改过了,也叫快照读,第一次读的时候,会把版本链拍照,下次读时,以版本快照中读,所以第一次和第二次读到的数据是一致的.

使用隔离级别机制,为了实现并发的读-写,写-读操作,提高效率

当前读和快照读

当前读

一致性锁定读，给行记录加X锁或者S锁

常见SQL语句：

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对读的记录加一个S锁
SELECT...FOR SHARE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

快照读

一致性非锁定读，单纯的select语句，不包括以下两种

SELECT ... FOR UPDATE
# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;

快照读即记录的历史版本，每行记录可能有多个历史版本

快照读的情况下，如果读取的记录正在执行update/delete，读取操作不会等待X锁的释放，而是会去读取行的一个快照

只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用快照读：

在RC级别下，快照读总是读取被锁定行的最新一份快照数据
在RR级别下，快照读总是读取本事务开始时的行数据版本

适用于数据一致性要求不高且追求极致性能的业务

InnoDB对MVCC的实现

MVCC的实现依赖于：隐藏字段、Read View、undo log

隐藏字段

InnoDB为每行数据添加了三个隐藏字段

DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

ReadView

主要是做可见性判断，保存了当前对本事务不可见的其他活跃事务

m_low_limit_id：目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见
m_up_limit_id：活跃事务列表 m_ids 中最小的事务 ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个 ID 的数据版本均可见
m_ids：Read View 创建时其他未提交的活跃事务 ID 列表。创建 Read View时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）
m_creator_trx_id：创建该 Read View 的事务 ID

undo log

事务回滚时恢复数据之前的样子
MVCC，读取记录时，若该记录被占用或者不可见，则通过undo log读取之前版本的数据，实现非锁定读

会对历史数据形成一个版本链，对其拍照，链首是最新的记录，链尾是最早的记录

MVCC整体操作流程

首先获取事务自己的版本号，也就是事务ID
获取ReadView
查询得到的数据，然后与ReadView中的事务版本号进行比较
如果不符合ReadView规则，就需要从undo log中获取历史快照
最后返回符合规则的数据

如果某个版本数据对当前事务不可见，就顺着版本链继续找到下一个，如果版本链中的所有数据对该事务都不可见，那么查询结果就不会有该记录

MySQL锁

MySQL支持行锁，间隙锁，表锁

行锁

是针对索引字段加的锁，只给操作的行加锁，如果两个事务操作的是同一行，那必须一个一个执行，减少数据库操作冲突，锁粒度小，并发度高，但加锁开销大，加锁慢，会出现死锁，与存储引擎有关，InnoDB默认是行锁

表锁

是针对非索引字段加锁，对整个表进行加锁，实现简单，资源消耗少，加锁快，不会出现死锁。但是锁冲突概率很高，高并发下效率极低。与存储引擎无关，MyISAM默认是支持表锁的

行级锁使用的注意事项

因为行级锁是针对索引字段加锁，在执行UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表中的所有记录进行加锁。

不过，很多时候即使用了索引也有可能会走全表扫描，这是因为 MySQL 优化器的原因。

InnoDB有哪几类行锁？

记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。
间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

共享锁（Share Lock，S 锁）

又称为读锁，如果我们事务在读数据时，不想让其他事务写，还要让其他事务可以读，那么查询语句可以加共享锁

# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;

排他锁（Exclusive Lock，X 锁）

就是独占锁，写操作默认加排他锁，当我们读数据时，要求数据足够的准确，可以给读操作加排他锁

SELECT ... FOR UPDATE;

意向锁

如果要用到表锁，如何判断表中没有行级锁，可以使用意向锁快速判断是否可以对某个表使用表锁

意向锁是表级锁

意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）

SQL优化

由于业务量的增多,SQL执行效率对程序运行效率有很大的影响,所以我们需要对SQL进行优化

SQL优化的一些方法

查询SQL尽量不要使用select * ,而是具体字段
避免在where子句中使用or来连接条件, 可以使用union all把两个sql结果合并
尽量使用数值替代字符串类型
使用varchar代替char , char是定长,不足用空格补充,varchar是变长,按实际内容存储
在where和order by涉及的列上建立索引
尽量避免索引失效
- 在where子句中对字段进行null值判断,否则将导致引擎放弃索引进行全表扫描,例如select id from t where num is null,可以在num上设置默认为0,确保表中没有null值
- 能用between就不用in或者not in
- 模糊查询导致索引失效
- 避免在where子句中对字段进行函数操作
inner join、left join、right join,优先使用inner join
提高group by 效率,先过滤再分组
清空表时优先使用truncate
表连接、索引不易太多,一般5个以内
避免在索引列上使用内置函数