MySQL高级（四）---in和exists，order by优化，慢查询日志，show profile，数据库锁
一、MySQL中in和exists
in和exists常用于where表达式中，其作用是查询某个范围内的数据。
#用法1
select * from A where in id(1,2);
#就等同于
select * from A where id = 1 and id = 2;#用法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与缓存数据做比较，满足条件的数据加入结果集以上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);测试：
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 |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
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;
测试：
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);在生产环境中，如果要手动分析日志、查找、分析SQL，是个体力活，提供了日志分析工具mysqldumoslow供我们使用。
五、Show Profile
是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL调优的依据。
默认情况下，参数处于关闭状态，并保存最近1次运行结果。
# 查看状态 默认关闭的
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
# 打开
mysql> set profiling=on;
查询所有语句执行的情况
>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 |
+----------+------------+-----------------------------------------------------------+
查询某个语句执行的详细信息
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.表锁（读写锁）
加锁:
lock table mylock read,book write; 给表mylock加读锁，book加写锁
解锁:
unlock tables;
# 查看哪些表加锁
show open tables; 1代表加锁 0代表未加锁
读锁测试
#给表加上读锁
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 #我们发现读其他表也不行写锁测试：
# 加锁
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;结论：读锁会阻塞写，但不会阻塞读；写锁会把读和写都阻塞；
2.行锁
偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也高。行锁支持事务，InnoDB中默认是行锁。
加锁的方式：自动加锁。对于update、delete、insert语句，InnoDB会自动给设计数据集加排他锁；对于普通的select语句，InnoDB不会加任何锁。
3.间隙锁
当我们用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条前的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）“。InnoDB也会对这个”间隙“加锁，这个锁机制就是所谓的间隙锁。
无索引行锁升级为表锁，所以需要尽可能让所有数据检索都通过索引来完成，避免无索引或索引失效导致行锁升级为表锁。
如何锁定一行？
select * from test where a = 8 for update;
#锁住之后不提交，别人是无法修改的。结论：
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表锁更高，但是在整体并发处理能力方面要远远优先于MyISAM的表级锁定，当系统并发量较高时，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。
但是，InnoDB在使用不当的情况下，会不如MyISAM的性能，比如行锁升级为表锁
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
