阅读量:45
回复【哪吒】，加入Java技术交流群
本篇是MySQL知识体系总结系列的第二篇，该篇的主要内容是通过explain逐步分析sql，并通过修改sql语句与建立索引的方式对sql语句进行调优，也可以通过查看日志的方式，了解sql的执行情况，还介绍了MySQL数据库的行锁和表锁。
一、explain返回列简介1、type常用关键字
system > const > eq_ref > ref > range > index > all。
system：表仅有一行，基本用不到；
const：表最多一行数据配合，主键查询时触发较多；
eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型；
ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；
range：只检索给定范围的行，使用一个索引来选择行。当使用=、、>、>=、 慢日志查询
慢查询日志就是MySQL提供的一种日志记录，用于记录MySQL响应时间超过阈值的SQL语句（long_query_time，默认10秒） ；
慢日志默认是关闭的，开发调优时打开，最终部署时关闭。
1、慢查询日志
（1）检查是否开启了慢查询日志：
show variables like '%slow_query_log%'
在这里插入图片描述
（2）临时开启：
set global slow_query_log = 1;
（3）重启MySQL：
service mysql restart;
（4）永久开启：
/etc/my.cnf中追加配置：
放到[mysqld]下：
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
2、阈值
（1）查看默认阈值：
show variables like '%long_query_time%'
（2）临时修改默认阈值：
set global long_query_time = 5;
（3）永久修改默认阈值：
/etc/my.cnf中追加配置：
放到[mysqld]下：
long_query_time = 5;
（4）MySQL中的sleep：
select sleep(5);
（5）查看执行时间超过阈值的sql：
show global status like '%slow_queries%';
八、慢查询日志 --> mysqldumpslow工具1、mysqldumpslow工具
慢查询的sql被记录在日志中，可以通过日志查看具体的慢sql。
cat /var/lib/mysql/localhost-slow.log
通过mysqldumpslow工具查看慢sql，可以通过一些过滤条件，快速查出需要定位的慢sql。
mysqldumpslow --help
参数简要介绍：
s：排序方式
r：逆序
l：锁定时间
g：正则匹配模式
2、查询不同条件下的慢sql
（1）返回记录最多的3个SQL
mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log
（2）获取访问次数最多的3个SQL
mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log
（3）按时间排序，前10条包含left join查询语句的SQL
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log
九、分析海量数据1、show profiles
打开此功能：set profiling = on;
show profiles会记录所有profileing打来之后，全部SQL查询语句所花费的时间。
缺点是不够精确，确定不了是执行哪部分所消耗的时间，比如CPU、IO。
2、精确分析，sql诊断
show profile all for query 上一步查询到的query_id。
3、全局查询日志
show variables like '%general_log%'
开启全局日志：
set global general_log = 1;
set global log_output = table;
十、锁机制详解1、操作分类
读写：对同一个数据，多个读操作可以同时进行，互不干扰。
写锁：如果当前写操作没有完毕，则无法进行其它的读写操作。
2、操作范围
表锁：一次性对一张表整体加锁。
如MyISAM存储引擎使用表锁，开销小、加锁快、无死锁；但锁的范围大，容易发生冲突、并发度低。
行锁：一次性对一条数据加锁。
如InnoDB存储引擎使用的就是行锁，开销大、加锁慢、容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率发生高并发问题：脏读、幻读、不可重复读）
lock table 表1 read/write，表2 read/write，...
查看加锁的表：
show open tables;
3、加读锁，代码实例
会话0：
lock table student read;
select * from student; --查，可以
delete from student where id = 1;--增删改，不可以
select * from user; --查，不可以
delete from user where id = 1;--增删改，不可以
如果某一个会话对A表加了read锁，则该会话可以对A表进行读操作、不能进行写操作。即如果给A表加了读锁，则当前会话只能对A表进行读操作，其它表都不能操作。
会话1：
select * from student; --查，可以
delete from student where id = 1;--增删改，会“等待”会话0将锁释放
会话1：
select * from user; --查，可以
delete from user where id = 1;--增删改，可以
会话0给A表加了锁，其它会话的操作①可以对其它表进行读写操作②对A表：读可以，写需要等待释放锁。
4、加写锁
会话0：
lock table student write;
当前会话可以对加了写锁的表，可以进行任何增删改查操作；但是不能操作其它表；
其它会话：
对会话0中对加写锁的表，可以进行增删改查的前提是：等待会话0释放写锁。
5、MyISAM表级锁的锁模式
MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改前，会自动给涉及的表加写锁。
所以对MyISAM表进行操作，会有如下情况发生：
（1）对MyISAM表的读操作（加读锁），不会阻塞其它会话（进程）对同一表的读请求。但会阻塞对同一表的写操作。只有当读锁释放后，才会执行其它进程的写操作。
（2）对MyISAM表的写操作（加写锁），会阻塞其它会话（进程）对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
6、MyISAM分析表锁定
查看哪些表加了锁：
show open tables;1代表被加了锁
分析表锁定的严重程度：
show status like 'table%';
在这里插入图片描述
Table_locks_immediate：可能获取到的锁数
Table_locks_waited：需要等待的表锁数（该值越大，说明存在越大的锁竞争）
一般建议：Table_locks_immediate/Table_locks_waited > 5000，建议采用InnoDB引擎，否则采用MyISAM引擎。
7、InnoDB分析表锁定
为了研究行锁，暂时将自动commit关闭，set autocommit = 0;
show status like '%innodb_row_lock%';
Innodb_row_lock_current_waits：当前正在等待锁的数量Innodb_row_lock_time：等待总时长。从系统启动到现在一共等待的时间Innodb_row_lock_time_avg：平均等待时长。从系统启动到现在一共等待的时间Innodb_row_lock_time_max：最大等待时长。从系统启动到现在一共等待的时间Innodb_row_lock_waits：等待次数。从系统启动到现在一共等待的时间
8、加行锁代码实例
（1）查询student
（2）更新student
update student set age = 18 where id = 1
（3）加行锁
通过select id,name,age from student for update;给查询加行锁。
依旧修改成功，原因是MySQL默认是自动提交的，因此需要暂时将自动commit关闭
set autocommit = 0;
在这里插入图片描述9、行锁的注意事项
（1）如果没有索引，行锁自动转为表锁。
（2）行锁只能通过事务解锁。
（3）InnoDB默认采用行锁
优点：并发能力强，性能高，效率高
缺点：比表锁性能损耗大
高并发用InnoDb，否则用MyISAM。
