MySQL

explain

id：SQL语句的执行顺序，值越大优先级越高

select_type:表示select类型，常见有

• SIMPLE：简单表，不用连表查询或者子查询
• PRIMARY：主查询，即外层查询
• UNION：第二个或者后面的查询语句
• SUBQUERY：SELECT，WHERE之后包含的子查询

type：表示连接类型，性能由好到差NULL>system>const>eq_ref>ref>range>index>all

• NULL：不查询任何表 🤣👉皇帝の新查询，查了个寂寞

• system：查询系统表

• const：通过主键查询和通过唯一索引查询 神中神的优化😍

• eq_ref：联表查询的状况，按联表的主键或惟一键联合查询

• ref：通过非唯一索引查询

• range：使用了尾部%的模糊查询和范围查询(都使用了索引)

• index：使用了索引，但是对索引树进行了全树扫描

• all：全表扫描 整張表全部查好查滿，爛到流暢的SQL🤣👉🤡

possible_keys：可能用到的索引，一个或者多个

key：实际用到的索引，没有则为null

Key_len：索引所用的字节数

row：执行查询的行数，在InnoDB中是一个估值 一眼丁真，鉴定为没什么卵用😂

filtered：返回结果的行数与所读取行数的百分比，越大越好

extra：额外信息

索引

索引失效的场景

• 最左前缀法则，假设有索引(A，B，C)
  • A，B，C 查询：三个都用到索引
  • A，C查询：A用了索引
  • B，C查询：B，C都用不到索引
• 范围查询，后续索引失效
• 索引列运算，索引失效
• 字符串查询不加''会导致索引失效
• 模糊查询(%a%，%a，a%)
  • 尾部模糊查询索引不失效(a%)
  • 头部模糊查询索引失效(%a%，%a)
• 使用or查询，只要有一个查询条件不是索引则导致整个索引失效
• 根据数据分布情况决定是否走索引。如果数据分布比较均匀，那么MySQL范围查询就有可能走索引。而如果数据分布不均匀，那么MySQL范围查询可能会扫描大量的数据页，从而导致性能问题。

索引的使用

• 建议索引：当一张表的某个字段有两个索引(idx_no no是索引，idx_no_name no和name都是索引)，只用no作为查询条件时，默认使用联合索引，此时我们可以用索引提取选择或排除索引
  • use index(建议使用哪个索引): select * from table use index(idx_no) where no=?。这个只是建议使用，是否使用取决于mysql!!!
  • ignore index(忽略哪个索引)：select * from table ignore index(idx_no_name) where no=?
  • force index(强制使用哪个索引)：select * from table force index(idx_no) where no=?
• 覆盖索引：将量使用覆盖索引(查询中使用了索引，并且需要返回的字段，再改索引中全都能找到)，假设有索引idx_sno_name_age (除了sno，name，age。表里还有其他字段)

--1
select sno,name,age from table where sno=?
--2
select * from table where sno=?

此时1会覆盖索引，即只通过查询联合索引就可以得出结果

反观2，会查到对应的叶子节点，还会通过主键id在聚集索引中再次查找未能覆盖的属性(回表)

• **前缀索引(会回表)**：当字段类型为字符串(varchar，text等),时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘10，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

--前缀大小的选择，我们可以选择count(？)/count(*)值最大(区分度) 和 字符的长度 综合的来确定索引使用的截取字段的长度

--1
select count(username) from table
--2 去重，降低重复率
select count(distance username) from table
--3 截取username的前9个字符计算区分度
select count(distance substring(username,1,9))/count(*) from table

-- 此处假设 第1-第7 为最优截取长度

--前缀索引的建立
create index idx_username_7 on table(username(7))

• 单列+联合索引

加入此时我们有两个唯一索引name和phone，如果执行一下sql

select name,phone form table where name=? and phone=?

结果是只有name使用了索引，此时我们将两个唯一索引组合成一个联合索引，并查询就可以让两个字段都是用索引。

create idx_name_phone on table(phone,name);
select name,phone form table where name=? and phone=?

SQL优化

Insert

以下有几个SQL的insert操作：

insert into table values(1,'tom');
insert into table values(2,'cat');
insert into table values(3,'jerry');
insert into table values(4,'mouse');

• 每一次只插入一条数据，这种效率很低，因为每一次insert都要连接数据库，所以我们可以批量操作

insert into table values(1,'tom'),(2,'cat'),(3,'jerry'),(4,'mouse');

• 但是如果数据过多，一次全部插入可能导致内存消耗过大，插入的线程长时间霸占锁以及如果发生异常导致回滚会造成额外的开销，此时我们可以使用手动提交事务

start transcation;
insert into table values(1,'tom'),(2,'cat'),(3,'jerry'),(4,'mouse');
......
......
commit;

• 主键顺序插入，可以提高插入性能，减少磁盘碎片化，提高数据访问效率

大批量插入数据，使用insert语句插入性能较低，可以使用load指令进行插入

1.登录时：mysql --local-infile -u root -p;
2.开启从本地导入数据库的开关： set global local_infile=1;
3.加载数据： load data local infile 'sql path' into table tableName field terminate by ',' lines terminated by '\n';

primary key

前置知识

• B+ Tree
  • 非叶子节点不存储data，只存储索引
  • 叶子结点包含所有索引字段
  • 叶子节点用指针连接，提高区间访问性能

---

• 数据组织方式
  • 在innodb中，表数据都是根据主键顺序存放的这种方式叫做索引组织表(Index Organized tableIOT)

---

• 页分裂：主键的乱序插入会导致页分裂，损耗性能。所以主键策略推荐使用(主键自增和雪花算法)

---

• 页合并：当刚除一行记录时，实际上记录并没有被物理刚除，只是记录被标记(flaged)为刚除并且它的空间变得允许被其他记录声明使用当页中州除的记录达到 MERGETHRESHOLD(默认为页的50%)，lnnoDB会开始寻找最近的页(前或后)看看是否可以将两个页合并以优化空间使用。

主键设计原则

• 在不影响业务的情况下，尽量降低主键长度
• 选择自增或者雪花算法，避免使用UUID(乱序插入会导致页分裂)
• 业务操作时，避免对主键的修改(会修改对应的索引结构)

order by

前置知识

order by查询会出现几种情况：（通过explain的extra查看）

• **using es0(效率低)**：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSot 排序。
• Using index(效率高):通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
• Backward index scan：反向扫描索引

优化

• 建立合适的索引，多字段排序时，也遵循最左前缀法则
• 尽量使用覆盖索引
• 多字段排序，一个升序一个降序，在建立索引时创建索引排序规则(默认为升序)，也可以手动指定

create index idx_name_phone on table_name(name desc,phone asc)

• 在不可避免出现filesort时，可以适当增加缓冲区大小(默认为256k)。如果排序时缓冲区占满了，会在磁盘中排序，导致性能下降！！！

--查看缓冲区大小
show variables like 'sort_buffer_size'

group by

• 分组操作时，可以通过索引提升效率
• 分组操作时，索引也是符合最左前缀法则

limit

• 覆盖索引加子查询

select *from table_name t,(select id from table_name order by id limit 200000,10) a where t.id=a.id

count

• **count(主键)**：遍历整张表，取出主键，累加
• **count(字段)**：遍历整张表，取出字段，判断是否为null，是null就不计入累加。如果字段有not null则无需判断
• **count(1)**：遍历整张表，不取值。返回的每一行都会放一个’1’，直接进行累加
• *count()**：不会将全部字段取出，而是专门做了优化，直接累加
• 效率：count(*)≈count(1)>count(主键)>count(字段)

update

innodb的更新默认使用的是行锁，但是在判断条件不是索引时，行锁会升级成表锁。假设user表有索引id，普通字段name，phone

--会话一，二开启事务，更新语句但是没提交时，会话二的更新会被阻塞，当会话一commit之后，会话二才惊醒更新(会话一和会话二修改的是不同的行数据，但是会话二却阻塞了，说明了此时的行锁升级成了表锁)

--会话一
begin;
update user set name='张麻子' where phone='114514'
commit;
--会话二
begin;
update user set name='曹操' where phone='123456'
commit;

--会话一，二开启事务，更新语句但是没提交时，会话二的更新也能进行，说明此时成功使用了行锁
--会话一
begin;
update user set name='张麻子' where id=1
commit;
--会话二
begin;
update user set name='曹阿瞒' where id=2
commit;

根据上述实验说明，我们在进行update时尽量通过索引去进行update，防止行锁升级成表锁，降低并发性能

Lock

操作区分

锁类型	SQL操作
共享锁(Share Lock)	select…for share
排他锁(Excute Lock)	update,delete,insert,select…for update

是否兼容	共享锁(Share Lock)	排他锁(Excute Lock)
共享锁(Share Lock)	✔	❌
排他锁(Excute Lock)	❌	❌

粒度区分

锁类型	范围	特点
表级锁(Table Lock)	整张表	开销小，并发性差
行级锁(Row Lock)	一行数据	开销大，并发性强，会发生死锁

表锁

表的X锁和X锁

--开启表锁
lock tables `table_name` read/write
--解锁
lock tables

锁类型	自己可读	自己可写	他人可读	他人可写	自己可操作其他表
共享锁(Share Lock)	✔	❌	✔	❌	❌
排他锁(Excute Lock)	✔	✔	❌	❌	❌

意向锁(自动添加)

协调行锁，表锁的一种表锁，支持多粒度锁共存。(所用：用于快速识别表中所加的行锁)

• 如果在某行加了X锁，数据库会自动在表级别加一个意向排他锁
• 如果在某行加了S锁，数据库会自动在表级别加一个意向共享锁

是否兼容	意向共享锁(Intention Share Lock)	意向排他锁(Intention Excute Lock)
共享锁(Share Lock)	✔	❌
排他锁(Excute Lock)	❌	❌

元数据锁(自动添加)

通过在修改表结构时上元数据锁(Meta Data Lock)，来确保读写的正确性

行锁

记录锁

锁类型	自己可读	自己可写	他人可读	他人可写
共享锁(Share Lock)	✔	✔	✔	❌
排他锁(Excute Lock)	✔	✔	❌	❌

间隙锁(帮助innodb解决幻读)

使用了间隙锁的区间和间隙无法插入元素，使的innodb可以在rr(可重复读)的级别下解决幻读的问题。假设有一张表user:间隙锁只会解决读与写的冲突！！！！

id(primary key)	name(index)	age
1	tom	12
8	cat	14
14	jerry	18
19	mouse	11
25	kon	9

--(1,8)区间被加了间隙锁
select * from user where id=3 for update
--(25,+∞)区间被加了间隙锁
select * from user where id=30 for update
--[8,19)区间被加了间隙锁
select * from user where id b for update

临键锁

间隙锁+记录锁(包含间隙和数据)

--(1,8]区间被加了间隙锁
select * from user where id>1 and id<=8 for update

MVCC(多版本并发控制)

读-写或写-读，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生 脏读、 不可重复读、幻读的问题。
各个数据库厂商对 SOL标准 的支持都可能不一样。比如MVSOL在 REPEATABLE READ 隔离级别上就已经解决了读-写问题。

MVCC，即多版本并发控制。其实现方法主要依赖于隐藏字段，Undo Log，Read View

快照读(一致性读)

快照读读的是历史版本,不加锁的select都属于快照读，即不加锁的非阻塞读，比如：

select *from `table_name` where ...

快照读是基于性能的考虑，实现是基于MVCC，在很多情况下避免了加锁，降低了开销

当前读

当前读读的是最新数据，读取时还需要保证其他的并发事务不能修改数据，会对读取的记录，比如

--共享锁
select *from `table_name` where ...for share
--排他锁
select *from `table_name` where ...for update
insert into `table_name` values ...
delete from `table_name` where ...
update  `table_name` set ...

隐藏字段，Undo Log版本链

隐藏字段：

• trx_id：事务id
• roll_pointer:形成版本链表的指针，由最新的版本指向老版本

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer 属性 (INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本)，可以将这些 undo日志都连起来，串成一个链表(尾插法，并且是最新的版本指向老版本):

对该记录每次更新后，都会将旧值放到一条undo日志 中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，我们把这个链表称之为 版本链，版本链的头节点就是当前
记录最新的值。
每个版本中还包含生成该版本时对应的事务id

Read View

在MVCC中，多个事务对同一个记录进行更新会产生多个历史快照，并且这些历史快照都保存在Undo Log中。而Read View通过记录活跃事务的ID(事务begin了，但是还没commit)来确定我们应该读取哪个历史快照。

Read View主要分为4各部分：

• creator_trx_id：创建这个Read View的事务ID

改动时会分配事务id，读时为0

• trx_ids：表示生成Read View时当前系统中活跃的读写事务的事务id列表
• up_limit_id：活跃事务中最小的事务id
• low_limit_id：表示生成Read View时系统中应该分配给下一个事务的id值。low_limit_id是系统最大的事务id值。

比如有1，2,3,4事务在进行修改，此时有一个事务准备去读取数据(**假设1,4
已提交**)，便会生成一个Read View。
**trx_id=2,3**
**up_limit_id=2**
**low_limit_id=5**

Read View规则：

• trx_id = creator_trx_id ：意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
• trx_id < up_limit_id值：表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
• trx_id >= low_limit_id ：表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
• up_limit_id < trx_id < low_limit_id：那就需要判断一下trx_id属性值是不是在trx_ids列表中
  • 如果在，这说明该事务还是活跃的，不能访问
  • 如果不在，这说明该事务还是已提交，能访问

MVCC操作流程(查询)

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

---

读已提交

事务	说明
begin
select *from table_name where id>2	获取一次Read View
…
select *from table_name where id>2	获取一次Read View
commit

可重复读

事务	说明
begin
select *from table_name where id>2	获取一次Read View
…
select *from table_name where id>2
commit