一、MySQL中in和exists

in和exists常用于where表达式中，其作用是查询某个范围内的数据。

#用法1
select * from A where in id(1,2);
#就等同于
select * from A where id = 1 and id = 2;
1
2
3

#用法2
select * from A where id in(select id from B);
#等同于
select id from B; 先执行in中的查询，并缓存结果集
select * from A where A.id = B.id;
缓存B中查询出来的id，A表查询时把A表的id与缓存数据做比较，满足条件的数据加入结果集
1
2
3
4
5

以上SQL语句中，in中的子查询语句仅仅执行依次，他查出B的所有的id并缓存起来，然后检查A表中的id在缓存中是否存在，如果存在则将A的当前记录加入到结果集中，直到遍历完A表中所有记录为止。
而**exists会对外表进行循环匹配，他不在乎后面的内表子查询的返回值是什么，只在乎有没有存在返回值，存在返回值，则条件为真，该条数据匹配成功，加入查询结果集中；如果没有返回值，条件为假，丢弃该条数据。
**可以理解为，将主查询中的数据，放在子查询中做条件验证，根据验证结果（true和false）来决定主查询的数据结果是否得以保留。
总结：
in的使用场景：外表大，内表小；
exist的使用场景：内表大，外表小；

二、order by优化

1.尽量使用index方式排序，避免使用filesort方式排序。

CREATE TABLE tblA(
id int primary key not null auto_increment,
age int,
birth timestamp not null
);
insert into tblA(age,birth) values(22,now());
insert into tblA(age,birth) values(23,now());
insert into tblA(age,birth) values(24,now());
#建立索引
create index idx_A_ageBirth on tblA(age,birth);
1
2
3
4
5
6
7
8
9

测试：

mysql> explain select *from tblA where age>20 order by age;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
mysql> explain select *from tblA where age>20 order by birth;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
mysql> explain select *from tblA where age>20 order by birth,age;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.尽可能在索引列上完成排序，遵照索引的最左前缀原则

MySQL支持两种方式的排序，Filesort和index，index效率高，他只MySQL扫描索引本身完成排序，Filesort效率低。

explain select *from tblA order by age asc,birth desc;

acs是按照升序 desc是按照降序，order by默认是升序排列，所以会出现filesort

3.filesort的排序算法：双路排序单路排序

双路排序：MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘。最后得到最终数据
单路排序：从磁盘读取查询所需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，他的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是他会使用更多的空间。因为他把每一行都保存在了内存中。
问题：因为在sort_buffer中，如果将所有字段都取出来，有可能取出的数据大小超过了sort_buff的容量，导致需要排完序再取，再排再取，从而多次IO，比双路效率还低。
优化策略：增大sort_buffer_size,max_length_for_sort_data参数的设置。

三、group by优化

group by实质是先排序后进行分组，遵照索引键的最佳做前缀法则，where高于having，能写where的条件下不写having。

四、慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体运行时间超过long_query_time（默认是10）值的SQL，则会被记录在慢查询日志中。
默认MySQL数据库没有开启慢查询，需要手动来设置这个参数。

## 默认
mysql> show variables like '%slow_query_log%';
+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_query_log      | ON                       |
| slow_query_log_file | LAPTOP-RV7D6SSH-slow.log |
+---------------------+--------------------------+
## 开启 
mysql> set global slow_query_log=1;  #只能当前数据库生效，并且mysql重启后会失效
## 查看long_query_time的默认时间
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
## 设置阙值时间,重新连接之后才能看到新修改的值
set global long_query_time=3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

测试：

mysql> select sleep(4);
# 可以看见LAPTOP-RV7D6SSH-slow.log生成了对应的日志信息
Time                 Id Command    Argument
# Time: 2020-08-27T12:47:55.771555Z
# User@Host: root[root] @ localhost [::1]  Id:  4091
# Query_time: 4.003121  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use sql_test;
SET timestamp=1598532475;
select sleep(4);
1
2
3
4
5
6
7
8

在生产环境中，如果要手动分析日志、查找、分析SQL，是个体力活，提供了日志分析工具mysqldumoslow供我们使用。

五、Show Profile

是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL调优的依据。
默认情况下，参数处于关闭状态，并保存最近1次运行结果。

# 查看状态 默认关闭的
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
# 打开
mysql> set profiling=on;
1
2
3
4
5
6
7
8
9

查询所有语句执行的情况

>mysql> show profiles;
+----------+------------+-----------------------------------------------------------+
| Query_ID | Duration   | Query                                                     |
+----------+------------+-----------------------------------------------------------+
|        1 | 0.05578475 | show variables like 'profiling'                           |
|        2 | 0.24246700 | select *from tbl_emp                                      |
|        3 | 0.60606875 | select *from t_emp                                        |
|        4 | 0.02984200 | select *from t_emp e inner join t_dept d on e.deptid=d.id |
|        5 | 0.00248925 | select *from t_emp e inner join t_dept d on e.deptid=d.id |
|        6 | 0.00046575 | show profiling                                            |
+----------+------------+-----------------------------------------------------------+
1
2
3
4
5
6
7
8
9
10
11

查询某个语句执行的详细信息

mysql> show profile cpu,block io for query 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000055 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables       | 0.453528 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                 | 0.000024 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock          | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing           | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics           | 0.000013 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing            | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing            | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sending data         | 0.151400 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                  | 0.000017 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000834 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables       | 0.000023 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items        | 0.000120 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up          | 0.000022 | 0.000000 |   0.000000 |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+
在status一列中，如果出现了以下几种，将会大大影响sql语句的执行效率
- converting heap to myisam  查询结果大，内存都不够用了，开始向磁盘转移
- creating tmp table  创建临时表
- copying to tmp table on disk 把内存中临时表复制到磁盘
- loked
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

六、数据库锁

在数据库中，除了传统的计算资源的争抢，数据也是一种供多用户共享的资源，如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，从这个角度说，锁对数据库而言显得尤为重要，也更加复杂。
数据库锁的分类：
从数据操作的类型：读锁，写锁；
读锁：针对同一份数据，多个操作可以同时进行而不会互相影响。
写锁：当前操作没有完成前，他会阻断其他写锁和读锁
从数据操作的粒度分：表锁、行锁。

1.表锁（读写锁）

加锁: 
lock table mylock read,book write;  给表mylock加读锁，book加写锁
解锁:
unlock tables;
# 查看哪些表加锁
show open tables; 1代表加锁 0代表未加锁
1
2
3
4
5
6

读锁测试

#给表加上读锁
lock table mylock read;
mysql> select *from mylock; #读是可以的
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
#加锁的这个客户端，无法修改
mysql> update mylock set name='a2' where id=1;
RROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated； #修改是不行的
#重新再开一个客户端,发生了阻塞，但是当解锁之后，查询结果会立刻出来
mysql>  update mylock set name='a2' where id=1;
mysql> select *from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES  #我们发现读其他表也不行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

写锁测试：

# 加锁
mysql> lock table mylock write;
# 自己可以读自己加过锁的表
mysql> select *from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a2   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
# 自己可以修改加锁的表
mysql> update mylock set name='a4' where id=1;
Query OK, 1 row affected (0.00 sec)；
# 自己不能读其他的表
mysql> select *from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES；
## 另一个客户端无法访问加锁的表，处于阻塞状态,写锁被取消后，结果出现
mysql>select *from mylock;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

结论：读锁会阻塞写，但不会阻塞读；写锁会把读和写都阻塞；

2.行锁

偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也高。行锁支持事务，InnoDB中默认是行锁。
加锁的方式：自动加锁。对于update、delete、insert语句，InnoDB会自动给设计数据集加排他锁；对于普通的select语句，InnoDB不会加任何锁。

3.间隙锁

当我们用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条前的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）“。InnoDB也会对这个”间隙“加锁，这个锁机制就是所谓的间隙锁。
无索引行锁升级为表锁，所以需要尽可能让所有数据检索都通过索引来完成，避免无索引或索引失效导致行锁升级为表锁。
如何锁定一行？

select * from test where a = 8 for update;
#锁住之后不提交，别人是无法修改的。
1

结论：
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表锁更高，但是在整体并发处理能力方面要远远优先于MyISAM的表级锁定，当系统并发量较高时，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。
但是，InnoDB在使用不当的情况下，会不如MyISAM的性能，比如行锁升级为表锁