标签：事务 隔离 SQL 索引 student MySQL id SELECT name

一、事务隔离级别

1.1 事务

- 事务是逻辑上的一组操作，要么全部执行，要么全部不执行。
- 事务（Transaction）是并发控制单位，是用户定义的一个操作序列，这些操作要么都做，要么都不做，是一个不可分割的工作单位。
- 事务通常以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK结束。

- 事务最经典例子也经常被拿出来的例子就是银行转账了。比如小明要给小红转账1000元，这个转账会涉及到两个关键操作：将小明的余额减1000元，将小红的余额加1000元。万一这两个操作之间突然出现错误，导致小明余额减少但是小红余额没有增加，这种情况是肯定不允许的。事务就是保证这两个关键操作要么都成功，要么都不成功。

1.2 事务的特性(ACID)

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持久性（ Durability ）。这四个特性简称为 ACID 特性。

- 原子性: 事务最小的执行单位，不允许分割。事务的原子性确保动作要么全部执行，要么全部不执行。
- 一致性: 执行事务的前后，数据保持一致。例如转账的业务中，无论事务是否成功，转账者和收款人的总额应该是不变的。
- 隔离性: 并发访问数据库时，一个用户的事务不应该被其他事务所影响，各并发事务之间数据库是独立的。
- 持久性: 一个事务被提交后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有影响。

1.3 并发事务带来的问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但是可能会带来以下的问题：

- 脏读(Dirty read)：当一个事务正在访问数据并且对其进行了修改，但是还没提交事务，这时另外一个事务也访问了这个数据，然后使用了这个数据，因为这个数据的修改还没提交到数据库，所以另外一个事务读取的数据就是“脏数据”，这种行为就是“脏读”

- 不可重复读(Unrepeatableread)：指在一个事务内多次读取同一数据，在这个事务还没结束时，另外一个事务也访问了这个数据并对这个数据进行了修改，那么就可能造成第一个事务两次读取的数据不一致，这种情况就被称为不可重复读。

- 幻读（Phantom read）: 幻读与不可重复读类似，幻读是指一个事务读取了几行数据，这个事务还没结束，接着另外一个事务插入了一些数据，在随后的查询中，第一个事务读取到的数据就会比原本读取到的多，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读的区别

- 不可重复读：重点是修改
- 幻读：重点是新增或者删除

1.4 事务的隔离级别

- 读未提交(READ-UNCOMMITTED): 最低的隔离级别，允许读取尚未提交的数据变更，可能造成脏读、不可重复读、幻读。
- 读已提交(READ-COMMITTED): 允许读取并发事务已经提交的数据，可以避免脏读，但是可能造成不可重复、幻读。
- 可重复读(REPEATABLE-READ): 对同一字段多次读取的结果都是一致的，除非本身事务修改，可以避免脏读和不可重复读，但是可能造成幻读。
- 可串行化(SERIALIZABLE): 最高的隔离级别，完全服从ACID的隔离级别，所以的事务依次执行，可以避免脏读、不可重复读、幻读。

MySQL InnoDB存储引擎默认的事务隔离级别是可重复读（REPEATABLE-READ），可以通过命令select @@tx_isolation;语句来查看，MySQL 8.0 该语句改为SELECT @@transaction_isolation;

MySQL InnoDB存储引擎的可重复读并不能避免幻读，需要应用使用加锁读来保证，这加锁读使用到的机制就是Next-Key Locks。

InnoDB存储引擎在分布式事务的情况下一般会用到可串行化隔离级别。

(以下内容摘自《MySQL 技术内幕：InnoDB 存储引擎(第 2 版)》7.7 章)：

- InnoDB存储引擎提供了对XA事务的支持，并通过XA事务来支持分布式事务的实现。
- 分布式事务指的是允许多个独立的事务资源参与到一个全局的事务中。
- 事务资源通常是关系型数据库系统，但也可以是其他类型的资源。
- 全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对事务的原有ACID要求又有了提高。
- 另外，在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE。

并发控制语句:

- START TRANSACTION | BEGIN ：显示的开启一个事务。
- COMMIT：提交事务，使得对数据库做的所有修改成为永久性。
- ROLLBACK：回滚到结束用户的事务，并撤销正在进行的所有未提交的修改。

1.5 解决幻读的方法

解决幻读的方式有很多，但是它们的核心思想就是一个事务在操作某张表数据的时候，另外一个事务不允许新增或者删除这张表中的数据了。解决幻读的方式主要有以下几种：

- 将事务隔离级别调整为 SERIALIZABLE。
- 在可重复读的事务级别下，给事务操作的这张表添加表锁。
- 在可重复读的事务级别下，给事务操作的这张表添加 Next-Key Locks。

说明：Next-Key Locks 相当于 行锁 + 间隙锁

二、MySQL锁

2.1 锁概念

数据库锁设计的初衷是处理并发问题。

作为多用户共享的资源，当出现并发访问的时候，为了保证数据的一致性，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要机制。

数据表就好比您开的一家酒店，而每行数据就像酒店的房间，如果大家随意进出，就会出现多人抢夺同一个房间的情况，而在房间上装上锁，申请到钥匙的人才可以入住并且将房间锁起来，其他人只有等他用完退房后才可以再次使用，这样保证了房间的一致性，方便酒店进行管理。

2.2 MySQL的锁

1. 锁粒度：从大到小：`表锁`、`页锁`和`行锁`；以及特殊场景下使用的`全局锁`.
2. 锁级别: `共享(读)锁`、`排他(写)锁`、`意向共享(读)锁`、`意向排他(写)锁`。
3. 以及Innodb引擎为解决幻读等并发场景下事务存在的数据问题，引入的 `Record Lock(行记录锁)`、`Gap Lock(间隙锁)`、`Next-key Lock(Record Lock + Gap Lock结合)`
4. 面向编程的两种锁思想：悲观锁和乐观锁

2.3 表锁

表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大，因为同 一张表上任何时刻只能有一个更新在执行。被大部分的mysql引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认的是行级锁。

表级锁按照使用方式也可分为共享锁和排他锁。

共享锁用法：

LOCK TABLE table_name [ AS alias_name ] READ

排它锁用法：

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE

解锁用法：

unlock tables;

2.4 行锁

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。 行级锁按照使用方式分为共享锁和排他锁。

共享锁用法（S锁 读锁）：
若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

select ... lock in share mode;

共享锁就是允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有。

排它锁用法（X 锁 写锁）：
若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

select ... for update

排它锁，也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同 一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的， 这也是MyISAM被InnoDB替代的重要原因之一。

2.5 页锁

除了表锁、行锁外，MySQL还有一种相对偏中性的页级锁，页锁是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

使用页级锁定的主要是BerkeleyDB存储引擎。

2.6 全局锁

全局锁，是对整个数据库实例加锁。使用场景一般在全库逻辑备份时。

MySQL提供加全局读锁的命令：Flush tables with read lock (FTWRL)

这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等修改数据库的操作都会被阻塞。

风险：

1. 如果在主库备份，在备份期间不能更新，业务停摆
2. 如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟同步

还有一种锁全局的方式：set global readonly=true ，相当于将整个库设置成只读状态，但这种修改global配置量级较重，和全局锁不同的是：如果执行Flush tables with read lock 命令后，如果客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。但将库设置为readonly后，客户端发生异常断开，数据库依旧会保持readonly状态，会导致整个库长时间处于不可写状态，试想一下微信只能看，不能打字~~

2.7 共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁

对于共享（读）锁、排他（写）锁，比如咱们住酒店，入住前顾客都是有权看房的，只看不住想白嫖都是可以的，前台小姐姐会把门给你打开。当然，也允许不同的顾客一起看（共享 读），

看房时房间相当于公共场所。如果你觉得不错，偷偷跑到前台要定这间房，交钱后会给你这个房间的钥匙并将房间状态改为已入住，不再允许其他人看房（排他 写）。

对了，当办理入住时前台小姐姐也会通知看房的人说这间房已经有人定了！！等看房的人出门后，然后你锁上门哼着歌儿~~直到你退房前，其他人无法在看你的房。

可见，读锁是可以并发获取的（共享的），而写锁只能给一个事务处理（排他的）。当你想获取写锁时，需要等待之前的读锁都释放后方可加写锁；而当你想获取读锁时，只要数据没有被写锁锁住，你都可以获取到读锁，然后去看房。

另外还有意向读\写锁，严格来说他们并不是一种锁，而是存放表中所有行锁的信息。就像我们在酒店，当我们预定一个房间时，就对该行（房间）添加 意向写锁，但是同时会在酒店的前台对该行（房间）做一个信息登记（旅客姓名、男女、住多长时间、家里几头牛等）。大家可以把意向锁当成这个酒店前台，它并不是真正意义上的锁（钥匙），它维护表中每行的加锁信息，是共用的。后续的旅客通过酒店前台来看哪个房间是可选的，那么，如果没有意向锁，会出现什么情况呢？假设我要住房间，那么我每次都要到每一个房间看看这个房间有没有住人，显然这样做的效率是很低下的

1. 共享(读)锁 (Share Lock)

共享锁，又叫读锁，是读取操作（SELECT）时创建的锁。其他用户可以并发读取数据，但在读锁未释放前，也就是查询事务结束前，任何事务都不能对数据进行修改（获取数据上的写锁），直到已释放所有读锁。

如果事务A对数据B（1024房）加上读锁后，则其他事务只能对数据B上加读锁，不能加写锁。获得读锁的事务只能读数据，不能修改数据。

SQL显示加锁写法:

SELECT … LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，MySQL就会对查询结果中的每行都加读锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请读锁，否则会被阻塞。其他线程也可以读取使用了读锁的表，而且这些线程读取的是同一个版本的数据。

2. 排他（写）锁（Exclusive Lock）

排他锁又称写锁、独占锁，如果事务A对数据B加上写锁后，则其他事务不能再对数据B加任何类型的锁。获得写锁的事务既能读数据，又能修改数据。

SQL显示加锁写法:

SELECT … FOR UPDATE;

​ 在查询语句后面增加FOR UPDATE，MySQL 就会对查询结果中的每行都加写锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请写锁，否则会被阻塞。另外成功申请写锁后，也要先等待该事务前的读锁释放才能操作。

3. 意向锁（Intention Lock）

意向锁属于表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁：

- 意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；

- 意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是 InnoDB 自动加的，不需要用户干预。

再强调一下，对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。

共享锁：SELECT … LOCK IN SHARE MODE;
排他锁：SELECT … FOR UPDATE;

三、SQL优化

对于MySQL层优化，我一般遵从五个原则：

1. 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO
2. 返回更少的数据：值返回需要的字段和数据分页处理，减少磁盘IO及网络IO
3. 减少交互次数：批量DML操作，函数存储等减少数据连接次数。
4. 减少服务器CPU开销：尽量减少数据库排序操作以及全表查询，减少cpu内存占用。
5. 利用更多资源：使用表分区，可以增加并行操作，更大限度利用cpu资源。

总结到SQL优化，就三点：

1. 最大化利用索引
2. 尽可能避免全表扫描
3. 减少无效数据的查询

3.1 SQL顺序

1. 语法顺序

1. SELECT 
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10.LIMIT <limit_number>

2. 执行顺序

FROM
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join...>
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT

数据除重

ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

3.2 基础SQL优化

1. 尽量不要使用 select *，而是具体字段

正例:

select id,name from student;

理由:

1. 字段多数，大表能达到100多个字段甚至达到200多个字段。
2. 只取需要的字段，节省资源、减少网络开销
3. select * 进行查询时，很可能不会用到索引，就会造成全表扫描

2. 避免在where子句中使用or关键字

反例：

SELECT * FROM student WHERE id=1 OR salary=30000

正例：

使用 union all

SELECT * FROM student WHERE id=1
UNION ALL
SELECT * FROM student WHERE salary=30000

理由：

1. 使用or可能会使索引失效，从而全表扫描
2. 对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。
3. 也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描搞定。
4. 虽然mysql是有优化器的，出于效率与成本考虑，遇到or条件，索引还是可能失效的。

3. 使用varchar代替char

反例:

`deptname` char(100) DEFAULT NULL COMMENT '部门名称'

正例：

`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'

理由：

1. varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间。
2. char按声明大小存储，不足的话会补空格。
3. 其次对于查询来说，在一个相对较小的字段内搜索，效率更高。

4. 尽量使用数值代替字符串类型

1. `主键(id)`: primary key优先使用数值类型Int, tinyint
2. `性别(sex)`: 0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
3. `支付方式(payment)`: 1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
4. `服务状态(state)`: 1-开启、2-暂停、3-停止
5. `商品状态(state)`: 1-上架、2-下架、3-删除

5. 避免返回大量数据

如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。

通常采用分页，一页习惯10/20/50/100条。

6. 使用explain分析执行计划

EXPLAIN SELECT * FROM `student` WHERE id = 1;

type:

- `ALL`: 全表扫描，没有优化，最慢的方式
- `index`: 索引全扫描
- `range`: 索引方位扫描，常用于<,<=,>=,between等操作
- `ref`: 使用非唯一索引扫描或者唯一索引前缀扫描，使用主键的关联查询中
- `eq_ref`: 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
- `const`: 当查询时对主键或者唯一键进行精确查询，系统会把匹配行中的其他列作为常数处理
- `null MYSQL`: 不访问任何表或索引，直接返回结果
- `System`: 表中只有一条记录(实际中不存在这种情况)

性能排行: System > const > eq_ref > ref > range > index > All

possible_keys:

- 显示可能应用在这张表中的索引

key:

- 真正使用的索引方式

7. 创建name字段的索引

提高查询速度的最简单最佳的方式

alter table student add index index_name(name)

8. 优化like语句

模糊查询，可能会让索引失效

反例：

explain select id,name from student where name like '%c';
explain select id,name from student where name like '%c%';

正例：

explain select id,name from student where name like 'c%';

9. 字符串隐式转换

反例：

explain select id,name from student where name = 123;

正例：

explain select id,name from student where name = "123";

理由：

- 符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较

10. 索引不宜过多，一般5个以内

- 索引并不是越多越好，虽然提高了查询效率，但是会降低插入和更新的效率。
- 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间
- 再者，索引表的一个特点，其数据是排序的，排序是需要花时间的。
- insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视情况来定。
- 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要。

11. 索引不适合建立在大量重复数据的字段上

- 如性别字段。
- 因为SQL优化器是根据表中数据量来进行查询优化的。
- 如果有大量重复数据，MySQL查询优化器推算发现不走索引的成本更低，很有可能就放弃走索引了。

12. where限定查询的数据

数据中假定就一个男的记录

反例：

SELECT id,name from student where sex = '1';

正例:

SELECT id,name from student where sex = '1' and id = 1;

理由：

- 需要什么数据，就去查询什么数据，避免返回不必要的数据，节省开销。

13. 避免在索引列上使用内置函数

给birthday添加索引:

alter table student add index idx_birthday(birthday);

当前时间加7天:

SELECT NOW();
SELECT DATE_ADD(NOW(),INTERVAL 7 day);

反例：

EXPLAIN SELECT * FROM student where DATE_ADD(birthday,INTERVAL 7 day) > NOW();

正例:

EXPLAIN SELECT * FROM student where birthday > DATE_ADD(NOW(),INTERVAL 7 day);

理由：

- 使用索引列上的内置函数

14. 避免在where中队字段进行表达式操作

反例：

EXPLAIN SELECT * from student WHERE id+1-1=+1;

正例：

EXPLAIN SELECT * from student WHERE id=+1-1+1;

理由：

- SQL解析时，如果字段相关的是表达式就进行全表扫描

15. 避免在where字句中使用!= 或 <>操作符

应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。

反例：

EXPLAIN SELECT * from student WHERE salary != 3000;
EXPLAIN SELECT * from student WHERE salary <> 3000;

16. 去重distinct过滤字段要少

反例：

-- 索引失效
EXPLAIN SELECT DISTINCT * FROM student;

正例：

-- 索引生效
EXPLAIN SELECT DISTINCT id,name FROM student;

理由:

- 带distinct的语句占用cpu时间高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，如cpu时间

17. where中使用默认值代替null

环境准备：

-- 修改表，增加age字段，类型int，非空，默认值0
ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;
-- 修改表，增加age字段的索引，名称为idx_age
ALTER TABLE student ADD INDEX idx_age (age);

反例：

EXPLAIN SELECT * from student WHERE age is not NULL;

正例：

EXPLAIN SELECT * from student WHERE age > 0;

理由：

- 并不是说使用了is null 或者 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关
- 如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃索引的
- 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点

3.3 高级SQL优化

1. 批量删除优化

避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。

反例：

#一次删除10万或者100万+？
delete from student where id <100000;

#采用单一循环操作，效率低，时间漫长
for（User user:list）{
  delete from student;
}

正例：

#分批进行删除，如每次500
for(){
delete student where id<500;
}

delete student where id>=500 and id<1000;

理由：

- 一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作

2. 逻辑删除设计

商品状态（state）：1-上架、2-下架、3-删除

理由：

- 这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
- 同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
- 通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
- 操作速度快，特别数据量很大情况下

3. 提高group by语句的效率

可以在执行到该语句前，把不需要的记录过滤掉

反例：先分组，再过滤

select job，avg（salary） from employee  
group by job 
having job ='president' or job = 'managent';

正例：先过滤，后分组

select job，avg（salary） from employee 
where job ='president' or job = 'managent' 
group by job;

4.复合索引最左特性

创建复合索引，也就是多个字段

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)

满足复合索引的左侧顺序，哪怕只是部分，复合索引生效

EXPLAIN
SELECT * FROM student WHERE NAME='name1'

没有出现左边的字段，则不满足最左特性，索引失效

EXPLAIN
SELECT * FROM student WHERE salary=3000

复合索引全使用，按左侧顺序出现 name,salary，索引生效

EXPLAIN
SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000

虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化

EXPLAIN
SELECT * FROM student WHERE salary=3000 AND NAME='name1'

理由：

- 复合索引也称为联合索引
- 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
- 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的

5. 排序字段创建索引

什么样的字段才需要创建索引呢？原则就是where和order by中常出现的字段就创建索引。

#使用*，包含了未索引的字段，导致索引失效
EXPLAIN
SELECT * FROM student ORDER BY NAME;

EXPLAIN
SELECT * FROM student ORDER BY NAME,salary

#name字段有索引
EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME

#name和salary复合索引
EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME,salary

EXPLAIN
SELECT id,NAME FROM student ORDER BY salary,NAME

#排序字段未创建索引，性能就慢
EXPLAIN
SELECT id,NAME FROM student ORDER BY sex

6. 删除冗余和重复的索引

SHOW INDEX FROM student 

#创建索引index_name
ALTER TABLE student ADD INDEX index_name (NAME)

#删除student表的index_name索引
DROP INDEX index_name ON student ;

#修改表结果，删除student表的index_name索引
ALTER TABLE student DROP INDEX index_name ;

#主键会自动创建索引，删除主键索引
ALTER TABLE student DROP PRIMARY KEY ;

7. 不要有超过5个以上的表连接

- 关联的表个数越多，编译的时间和开销也就越大
- 每次关联内存中都生成一个临时表
- 应该把连接表拆开成较小的几个执行，可读性更高
- 如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
- 阿里规范中，建议多表联查三张表以下

8. inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小

- `inner join` 内连接，只保留两张表中完全匹配的结果集
- `left join`会返回左表所有的行，即使在右表中没有匹配的记录
- `right join`会返回右表所有的行，即使在左表中没有匹配的记录

理由：

- 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点
- 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

9. in子查询的优化

日常开发实现业务需求可以有两种方式实现：

- 一种使用数据库SQL脚本实现
- 一种使用程序实现
如需求：查询所有部门的所有员工：

#in子查询
SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);
#这样写等价于：

#先查询部门表
SELECT id FROM tb_dept

#再由部门dept_id，查询tb_user的员工
SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下程序实现，可以抽象成这样的一个嵌套循环：

List<> resultSet;
for(int i=0;i<B.length;i++) {
  for(int j=0;j<A.length;j++) {
    if(A[i].id==B[j].id) {
      resultSet.add(A[i]);
      break;
    }
  }
}

上面的需求使用SQL就远不如程序实现，特别当数据量巨大时。

理由：

- 数据库最费劲的就是程序链接的释放。假设链接了两次，每次做上百万次的数据集查询，查完就结束，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，就会额外花费很多实际，这样系统就受不了了，慢，卡顿

10. 尽量使用union all替代union

反例：

SELECT * FROM student
UNION
SELECT * FROM student

正例：

SELECT * FROM student
UNION ALL
SELECT * FROM student

理由：

- union和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复
- union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
- union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION

标签：事务,隔离,SQL,索引,student,MySQL,id,SELECT,name
From： https://www.cnblogs.com/codertl/p/17082406.html