22-08-26 西安 MySQL高级（04）索引失效、关联查询优化、排序分组优化、千万级数据分页的优化、Innodb的行锁

索引失效 

对我而言这诗也就只能记住半句，“全值匹配我最爱”。。。虽然我记不住诗，我也觉得没必要记住

---

用不用索引，是优化器说了算，优化器是基于成本开销的，索引失效就是了解优化器的规则

全值匹配我最爱

-- 添加联合索引
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME, age, pos);

-- type=ref key_len=74 
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
 
-- type=ref key_len=78
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
 
-- type=ref key_len=140
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

这个其实是说，查询优化器会选择最长的那个索引，如下

---

最佳左前缀法则

最佳左前缀法则主要指联合索引，指的是查询从索引的最左前列开始并且不跳过索引中的列。

idx_staffs_nameAgePos(name, age, pos);

反面例子1：带头大哥不能死

-- type=ALL key_len=NULL
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';
 
-- type=ALL key_len=NULL 
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

反面例子2：中间兄弟不能断，不然就接不上了，只用到一个name索引，没有使用pos上的索引

-- type=ref key_len=74
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND pos = 'dev';

正面例子：索引顺序不重要，但是不推荐打乱顺序，让mysql做隐式的转换和优化

-- type=ref key_len=140
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND NAME = 'July' AND pos = 'dev' ;

---

计算、函数、类型转换导致索引失效

不在索引列上做任何操作（计算、函数、(自动或者手动)类型转换），会导致索引失效而转向全表扫描

函数导致索引失效

一个一个取出来做函数运算

 那怎么写好呢，如下可以是相同的效果。但是使用到了name字段的索引

计算导致索引失效

一个一个取出来做运算，第一条sql会导致索引失效

 类型转换导致索引失效

 正确写法：直接给字符串

---

范围条件右边的列索引失效

这个右边，不要看where语句里的左右，要看的是索引里的右边。。

如下，因为classId使用了范围，则name索引失效。这个是看索引里的顺序的，而不是where里怎么写。

下面这么写可以吗？没有意义，优化器本身是可以颠倒顺序的，它看的是索引的顺序

那怎么优化呢？

创建索引的时候，如下这么创建：

---

is null 可以使用索引，is not null无法使用索引

---

like以通配符%开头索引失效

 like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

-- type=ref key=idx_staffs_nameAgePos  key_len=74
EXPLAIN SELECT * FROM staffs WHERE NAME='July';
 
-- type=ALL key=null  key_len=null
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%July%';
 
-- type=ALL key=null  key_len=null
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%July';
 
-- type=range key=idx_staffs_nameAgePos  key_len=74
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE 'July%';

---

不等于（!=或者<>）索引失效

mysql 在使用不等于(!= 或者<>)的时候有时候无法使用索引会导致全表扫描

-- type=ref key=idx_staffs_nameAgePos  key_len=74
EXPLAIN SELECT * FROM staffs WHERE NAME='July';
 
-- type=ALL  possible_key=idx_staffs_nameAgePos key=null
EXPLAIN SELECT * FROM staffs WHERE NAME!='July';
 
-- type=ALL  possible_key=idx_staffs_nameAgePos key=null
EXPLAIN SELECT * FROM staffs WHERE NAME<>'July';

解释下: 3条sql都是能查出来结果的

---

OR前后存在非索引的列，索引失效

---

统一使用utf8mb4

数据库和表的字符集统一使用utf8mb4
统一使用utf8mb4(5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

---

关联查询优化

查询优化方向

1、物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
2、逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。

1、关联查询问题引入

此时没建立索引时。type=all，都是全表扫描，rows=20+20

为了避免全表扫描我们需要针对card字段建立索引，

提出问题：class表和book都有card字段，应该往哪个表上建？？

---

2、在被驱动表上建立索引

先从驱动表取一条数据，然后根据被驱动表找匹配关系，跟嵌套循环是一样的

情况一：book表的card字段建立

SQL_NO_CACHE   每次都要从mysql查询最新的，不从缓存取得

# 添加索引优化
ALTER TABLE `book` ADD INDEX idx_book_card ( `card`);
 
# 查看索引
SHOW INDEX FROM book;
 
# 下面开始explain分析
EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;

此时，在left join情况下，class是驱动表，book表示被驱动表。

我们只在被驱动表上的“连接字段上”建立索引，则被驱动表type会使用ref，驱动表还是all

要注意的问题:

连接字段的俩名字可以不一样，但是类型一定要一样，不然会类型转换导致索引失效

情况二：class表的card字段建立

#删除book表的索引
DROP INDEX idx_book_card ON book;
 
# 添加索引优化
ALTER TABLE `class` ADD INDEX idx_class_card (card);
 
# 查看索引
SHOW INDEX FROM class;
 
# 下面开始explain分析
EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;

此时，在left join情况下，左表class是驱动表，book表示被驱动表。我们在驱动表上创建了索引。则rows=20+20，无效果 

结论

A left  join B，A驱动表，B是被驱动表，我们在被驱动表上做索引；
 
A right join B，B驱动表，A是被驱动表，我们在被驱动表上做索引；

---

3、内连接

对于内连接怎么区分驱动表与被驱动表，有下面3条结论

1.查询优化器是有权利决定哪个是驱动表，哪个是被驱动表

2.如果表的连接条件中，只能有一个字段有索引，则有索引的字段所在的表会作为被驱动表。

3.在2个表的连接条件都存在索引、或者都不存在索引的情况下，小表会作为驱动表，小表驱动大表。

小表驱动大表深层次理解：

1.更准确的说应该是小的结果集驱动大结果集，要把where条件考虑上

2.小表作为驱动表的本质目的就是 减少外层循环的数据数量

3.小的度量单位指的是 表行数*每行大小

---

4、JOIN语句的原理 

从Explain查询的结果来看，上面一定是驱动表，下面一定是被驱动表

对于左外连接来说，前面这个不一定是驱动表。。。

索引嵌套循环连接（我们追求的）

减少了内层表（被驱动表）数据的匹配次数，要求被驱动表上必须有连接字段的索引

索引嵌套循环连接对应示意图如下：

 如果被驱动表加索引，效率是非常高的

但如果索引不是主键索引，所以还得进行一次回表查询。相比之下，如果被驱动表的索引是主键索引，效率会更高。

==========

块嵌套循环连接 ，引入join buffer缓冲区

针对join连接的字段没有索引

mysql的改进方案

需要注意的2个点

1.这里缓存的不只是关联表的列，select后面的列也会缓存起来。

2.在一个有N个join关联的sql中会分配（N-1）个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。

那么就可以从以下几个方面入手进行优化：

1、为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
2、增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）
3、减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多)

---

5、使用关联查询的建议

1、保证被驱动表的join字段已经被索引

2、永远是选择小表驱动大表（小表作为驱动表）

3、inner join 时，mysql会自己把小结果集的表选为驱动表。

4、子查询尽量不要放在被驱动表，有可能使用不到索引。

5、能够直接多表关联的尽量直接关联，不用子查询。

一般mysql优化，或者编写高效的sql语句，注意2点

1 符合最佳最前缀原则，

2 永远用小表驱动大表。

---

子查询优化

1、子查询的问题

子查询可以通过一个SQL语句实现比较复杂的查询。但是，子查询的执行效率不高。原因：

①执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和O资源，产生大量的慢查询。

②子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。

③对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

---

2、子查询怎么去优化？

解决方案：直接不用子查询

在MySQL中，可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

---

3、 子查询优化举例

 查询班长的信息

---

 排序分组优化

1、order by 关键字优化

在age和birth建立一个复合索引如下：

MySQL支持二种方式的排序，FileSort和Index

Index效率高.它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY子句，尽量复用已建好索引的方式排序,避免使用FileSort方式排序

index(a,b,c)
只要建过索引就是排过序，尽量复用，不要另起炉灶，以下是好使的2种情况
where  a = 'xx'  order by b ,c

order by a,b,c

#不出现 filesort
EXPLAIN SELECT * FROM tblA ORDER BY age,birth;
 
#出现 filesort
EXPLAIN SELECT * FROM tblA ORDER BY age ASC,birth DESC;
 
#出现 filesort
EXPLAIN SELECT * FROM tblA ORDER BY birth;
 
#不出现 filesort
EXPLAIN SELECT * FROM tblA WHERE age = 20 ORDER BY birth;
 
#不出现 filesort
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age,birth;
 
#不出现 filesort
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age;
 
#出现 filesort
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth;
 
#出现 filesort
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth,age;

ORDER BY满足两情况，会使用Index方式排序

1、ORDER BY 语句使用索引最左前列

2、使用Where子句与Order BY子句条件列组合满足索引最左前列

---

2、group by 关键字优化

分组必排序 一句话，先有order by后有group by

1、group by实质是先排序后进行分组，遵照索引建的最佳左前缀        

2、where高于having，能写在where限定的条件就不要去having限定了。

3、group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引

---

MySQL千万级数据分页的优化

1、limit底层分页原理

limit（0,10）跳过0条，要查10条。

在SELECT 语句中使用LIMIT子句来约束结果集中的行数。LIMIT子句接受一个或两个参数。两个参数的值必须为零或正整数。俩个参数LIMIT子句语法

offest:参数要返回的第一行的偏移量。第一行的偏移量为0，而不是1

count:指定要返回的最大行数

SELECT 
   column1,column2,...
FROM
   TABLE
LIMIT OFFSET,COUNT;

不加索引，600万数据量下，老师测试的是21s，主要因为limit后面的偏移量太大导致的。

/*偏移量为4950000，取30*/
SELECT SQL_NO_CACHE
  a.empno,
  a.ename,
  a.job,
  a.sal,
  b.deptno,
  b.dname 
FROM
  emp a 
  LEFT JOIN dept b 
    ON a.deptno = b.deptno 
ORDER BY a.id DESC 
LIMIT 4950000, 30;

limit底层分页原理

其实是因为limit后面的偏移量太大导致的。比如 limit 4950000,30。

这个等同于数据库要扫描出 4950030条数据，然后再丢弃前面的 49500000条数据返回剩下30条数据给用户，这种取法明显不合理。 

生产危险隐患：当偏移量非常大的时候，它会导致MySQL扫描大量不需要的行然后再抛弃掉

---

2、使用索引覆盖+子查询优化

---

3、起始位置重定义

---

4、服务降级不让使用

---

事务的隔离性由锁来实现

当多个线程并发访问某个数据的时候，尤其是一些敏感的数据（比如订单、金额），我们就需要保证这个数据在任何时刻“最多只有一个线程”在访问，保证数据的完整性 和 一致性。

1、并发事务访问相同记录

1.读-读情况

读-读情况，即并发事务相继读取相同的记录，这种情况非常安全。。。不需要考虑锁

======================

2.写-写情况

写-写情况，即并发事务相继对相同的记录做出改动,可能会发生脏写问题。

任何一种隔离级别都不允许脏写问题的发生。所以在多个未提交事务相继对这条记录做改动时，需要让它们排队执行，这个排队的过程其实是通过锁来实现的。

这个所谓的锁其实是一个内存中的结构，在事务执行前本来是没有锁的，也就是说一开始是没有锁结构和记录进行关联的。
注意：有几个事务，就会有几个锁结构

当一个事务想对这条记录做改动时，首先会看看内存中有没有与这条记录关联的锁结构，当没有的时候就会在内存中生成一个锁结构与之关联。比如，事务T1要对这条记录做改动，就需要生成一个事务T1的锁结构与之关联：

锁结构的属性解释：
trx信息：代表这个锁结构是哪个事务生成的。
is-waiting:代表当前事务是否在等待。

在事务T1提交或者回滚之后，就会把该事务生成的锁结构释放掉，然后看看还有没有别的事务在等待获取锁， 发现了事务T2还在等待获取锁，所以把事务T2对应的锁结构的is-waiting属性设置为false,然后把该事务对应的线程唤醒，让它继续执行，此时事务T2就算获取到锁了。

===================

3.读-写情况 （重点）

读-写或写-读，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生脏读、不可重复读、幻读的问题。

注意：MySQL在REPEATABLE READ隔离级别上就已经解决了幻读问题

---

2、并发问题的2种解决方案

脏写的问题，任何一种隔离级别都给解决掉了，这里的并发问题主要指脏读、不可重复读、幻读

方案一：读操作利用多版本并发控制(MVCC)，写操作进行加锁。

方案二：读、写操作都采用加锁的方式。

---

3、2种方案对比

• 采用MVCC方式的话，读-写操作彼此并不冲突，性能更高。
• 采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。

一般情况下我们当然愿意采用MVCC来解决读-写操作并发执行的问题，但是业务在某些特殊情况下，要求必须采用加锁的方式执行。

---

共享锁和排它锁

共享锁(Shared Lock,S Lock)和 排他锁(Exclusive Lock,X Lock),也叫读锁(readlock)和写锁(write lock)。

需要注意的是对于InnoDB引擎来说，读锁和写锁可以加在表上，也可以加在行上。

对读取的记录加 S锁：
 

SELECT ... LOCK IN SHARE MODE;
#或
SELECT ... FOR SHARE;(8.0新增语法)

对读取的记录加 X锁：

SELECT ... FOR UPDATE;

读操作是可以加S锁或X锁的，演示思路如下：

1.开启事务1，加s锁，开启事务2，加s锁（成功），s锁之间是共享的

2.在1的基础上再开启事务3，加x锁（阻塞）提交事务1，事务3仍然阻塞，继续提交事务2，事务3结束阻塞

3.开启事务1，加X锁，开启事务2，加s锁/x锁（都会阻塞），因为X锁是排它的

8.0中的新特性

能查就查，查不了也不会去阻塞，会执行相应的行为

在8.O版本中，SELECT...FOR UPDATE,SELECT...FOR SHARE添加NOWAIT、SKIP LOCKED语法，
跳过锁等待，或者跳过锁定。

通过添加NOWAIT、SKIP LOCKED语法，能够立即返回。如果查询的行已经加锁：
1.那么NOWAIT会立即报错返回。
2.而SKIP LOCKED也会立即返回，只是返回的结果中不包含被锁定的行。

写操作指增删改，是一定加要X锁（排它锁）的

---

MVCC 多版本并发控制

MVCC更好的去处理 读写冲突，提高数据库的并发性能。MVCC的实现依赖于：隐藏字段、Undo Log、Read View。

1、快照读和当前读

快照读

快照读又叫一致性读，读取的是快照数据。不加锁的简单的SELECT都属于快照读，如下

SELECT * FROM player WHERE ...

快照读：读取到的并不一定是数据的最新版本，可能是之前的历史版本

当前读

当前读：读取的是记录的最新版本，最新数据。读取时会对读取的记录加锁，加锁的SELECT或者对数据进行增删改都会进行当前读，如：

---

2、行格式中的隐藏字段

---

3、ReadView 

ReadView和事务是一对一的。

ReadView就是一个事务在使用MVCC机制进行快照读操作时产生的读视图。ReadView要解决的核心问题是：判断版本链中的哪个版本是当前事务可见的

ReadView中4个重要的内容如下

 活跃指，已经启动但是没提交的事务，提交ReadView访问规则了的事务不在ids里边

ReadView访问规则

在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见，某个版本也就是下文的被访问的版本。

在隔离级别为读已提交(Read Committed)时，一个事务中的每一次SELECT查询都会重新获取一次Read View。

当隔离级别为可重复读的时候，一个事务只在第一次SELECT的时候会获取一次Read View,
而后面所有的SELECT都会复用这个Read View,如下表所示：

---

Innodb的行锁到底锁了什么

1、mysql锁级别

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

==========

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高

页级锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

从操作粒度来说：表级锁>页级锁>行级锁

为了尽可能的提高并发度，每次锁定的数据范围越小越好

---

2、索引命中与没命中

索引没命中，行锁变表锁

案例一 

1.在session一会话窗口，

BEGIN;
UPDATE t_customer SET age=55 WHERE phone='13811112222'

2.在session2会话窗口，操作另外俩条记录

UPDATE t_customer SET age=55 WHERE id=5; #转圈等锁。
或者
UPDATE t_customer SET age=44 WHERE id=6; #转圈等锁。

 会发现转圈现象，有了表锁？？？

3.对session1中的事务 commit/rollback;接着session就好使了

原因：在session1中操作数据时，phone字段上面我们没有建索引，不会命中索引，使得行锁变表锁

---

3、行锁通过锁住索引实现

InnoDB的行锁，是通过锁住索引来实现的，如果加锁查询的时候没有使用到索引，会将整个聚簇索引都锁住，相当于锁表了。

按照主键索引 id

id主键索引+聚簇索引+一级索引  都是一个意思

操作前

1.session1中，注意此时我们使用到了主键索引id，则会是行锁

BEGIN;
UPDATE t_customer SET age=55 WHERE id=4

2.在session2中，只要你不跟人家抢那一行，都是OK的

UPDATE t_customer SET age=55 WHERE id=5;  # OK
或者
UPDATE t_customer SET age=33 WHERE id=6; # OK
或者
UPDATE t_customer SET age=11 WHERE id=4; # 转圈圈

按照二级索引cname

辅助索引+非聚簇索引+二级索引 都是一个意思

1.在cname字段自建一个索引

CREATE INDEX idx_cname ON t_customer(cname);

此时t_customer表中数据如下：

2.按照我们自建的索引去命中

在session1中，使用到了我们自建的索引。所以会是行锁，只会把这一条记录锁住

BEGIN;
UPDATE t_customer SET age=1 WHERE cname='z3'

===============

在session2中，这俩个SQL操作的是另外两条记录，所以可以。

UPDATE t_customer SET age=44 WHERE cNAME='z4'; #ok
 
UPDATE t_customer SET age=55 WHERE cNAME='z5'; #ok

在session2中，这俩个操作都是不行的，因为被session1行锁了。

UPDATE t_customer SET age=11 WHERE cNAME='z3' # 转圈圈
UPDATE t_customer SET age=11 WHERE id=4 # 转圈圈

在session1中，使用率commit/rollback,一切都回归正常