MySQL 慢查询和SQL优化
慢查询
什么是慢查询？
MySQL慢查询，全名是慢查询日志。
-
是MySQL提供的一种日志记录，用来记录那些在MySQL中超响应时间超过阈值的语句。
-
默认情况下MySQL慢查询是关闭的，需要手动设置这个参数
-
如果不是调优需要，一般不建议开启，因为开启慢查询会多少带来一定的性能消耗。
查看慢查询状态
mysql> show variables like '%slow%';
+---------------------------+---------------------------------------------------------------+
| Variable_name | Value |
+---------------------------+---------------------------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | D:\MySQL5.7\mysql-5.7.17-winx64\data\DESKTOP-L7S6JOM-slow.log |
+---------------------------+---------------------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)
查看慢查询阈值
默认是10秒
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)
开启慢日志和修改阈值
只要不在配置文件修改，下一次开机这些配置都会改回去。
--设置阈值为2秒
set long_query_time=2;
--开启慢日志
set global slow_query_log='ON';
但是我们平时写SQL都是一条结束了，很难做到两秒，这里我们添加大量测试数据来测试：
创建一千万条数据
注意一下：
- 我们创建了存储过程，还需要调用一下这个存储过程才能开启我们的任务。
- 创建的数据量和电脑配置息息相关，如果电脑配置不错，一千万条差不多。不然可以稍微减少几百万
- 我的电脑花了很久才创建好（>1hours）,如果大家也想试试，网上有很多方法，我这里也列举了我的方法。
创建表
-- 创建表
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`email` varchar(30) DEFAULT NULL,
`phone` char(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
使用JDBC+Druid连接添加一千万条数据
public class Insert10millionData {
public volatile static int index = 0;
//创建100个线程
public static void main(String[] args) {
for (int i = 0; i < 100; i++) {
new Thread(Insert10millionData::run).start();
}
}
//疯狂插入值
public static void run() {
//从Druid链接池获取链接
Connection connection = DruidFactory.getConnection();
try {
PreparedStatement statement = connection.prepareStatement("insert into users_100 values(null,?,?,?,?,?)");
while (index++ < 100000) {
//一万条一万条的提交
if (index % 10000 == 0) {
System.out.println("提交一波");
statement.executeBatch();
statement.clearBatch();
}
statement.setString(1, "user" + index);
statement.setString(2, "user" + index + "@aliyun.com");
statement.setString(3, "13701" + ((int) (Math.random() * 500000) + 500000));
statement.setInt(4, (int) (Math.random() * 100));
statement.setString(5, (int) (Math.random() * 2) == 1 ? "男" : "女");
statement.addBatch();
}
statement.executeBatch();
statement.clearBatch();
} catch (Exception e) {
throw new RuntimeException();
} finally {
try {
connection.close();
System.out.println("结束!");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
DruidFactory 连接池工厂
public class DruidFactory {
private static DruidDataSource dataSource;
static {
Properties prop = new Properties();
try {
prop.load(new FileInputStream("E:\\ideaProjects\\study\\Maven\\src\\main\\resources\\druid.properties"));
dataSource = new DruidDataSource();
dataSource.setUrl(prop.getProperty("jdbc.url"));
dataSource.setUsername(prop.getProperty("jdbc.username"));
dataSource.setPassword(prop.getProperty("jdbc.password"));
dataSource.setDriverClassName(prop.getProperty("jdbc.driverClassName"));
dataSource.setMaxActive(Integer.parseInt(prop.getProperty("jdbc.maxActive")));
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException throwables) {
throw new RuntimeException();
}
}
}
配置文件
jdbc.url=jdbc:mysql:///kaikeba?useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.maxActive=100
统计数量
千万条数据，多出来的一点是并发的问题，我没有做并发安全处理，但是主键id是连续的。
我们看到查询花费了15秒多，这符合慢查询的要求，我们看一下慢查询日志。
--开启慢查询
mysql> set global slow_query_log ='ON';
Query OK, 0 rows affected (0.02 sec)
--查询数据总数
mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
| 10009674 |
+-----------+
1 row in set (15.74 sec)
慢查询日志
我们看到刚刚更新的内容，有了慢日志，我们以后工作中出现数据库缓慢的问题，可以去查看慢日志。
如果发现一些离谱的代码，可以看看是哪个小宝贝写的。
MySQL, Version: 5.7.17 (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
# Time: 2020-09-15T15:28:38.793225Z
# User@Host: root[root] @ localhost [::1] Id: 3
# Query_time: 16.653456 Lock_time: 0.037899 Rows_sent: 1 Rows_examined: 0
use kaikeba;
SET timestamp=1600183718;
select count(*) from users;
分析指令 Explain
我们看一对案例：
我们发现主键查询对于千万级数据表几乎是秒查的，而非主键查询速度非常的慢。
--查询非主键
select * from users where name='user100';
+------+---------+--------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+------+---------+--------------------+-------------+------+------+
| 2241 | user100 | user100@aliyun.com | 13701915669 | 15 | 男 |
+------+---------+--------------------+-------------+------+------+
1 row in set (16.83 sec)
--查询主键
select * from users where id=10000000;
+----------+-------------+------------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+----------+-------------+------------------------+-------------+------+------+
| 10000000 | user9948351 | user9948351@aliyun.com | 13701995797 | 4 | 女 |
+----------+-------------+------------------------+-------------+------+------+
1 row in set (0.00 sec)
使用explain 命令 来分析我们的SQL语句
我们给出了一些关于此次查询的一些关键信息的解释：
- **select_type：**查询的类型，这里我们是一条普通的SQL语句所以是普通类型SIMPLE
- **type：**查询方式
- possible_keys: 可能用到的索引
- key: 实际用到的索引
- **rows：**预计扫描的行
从以上信息我们得知：
- 查询姓名的时候没有使用到索引，扫描方式是ALL，这是查询语句最慢的查询方式了
- 预计扫描870万行，几乎跑完了一整个数据表，这是非常可怕的
再来看一下主键的查询
我们看到扫描方式是const，常量的意思，这是最快的扫描方式了。可能使用到的索引和实际使用到的都是primary主键索引，并且预计的行数为1。主键的扫描方式就好像我知道那个值在哪，我直接去拿就行了，不要去扫描其他的，所以才会快的离谱，。由于使用到索引，我们可以看到key_len属性为4，表示索引的长度。我们知道InnoDB的索引默认是B+树，所以这个索引的len实际上就是B+树的高度。
通过explain的工具，我们看到SQL语句的更多信息。这也是我们以后做SQL优化的比较重要的指令。
对于这个type属性，我罗列了其所有可能的值：
type属性:
-
ALL
这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。
-
index
这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，就是我们花费空间去进行排序，然后再从有序的列表中去扫描，空间换时间的基本操作。
-
range
range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>’,’<'外，in和or也是索引范围扫描。
-
ref
出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
-
eq_ref
ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。
-
const
通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。
-
null
并没有使用到扫描操作，你可能在使用 alter table 修改表结构
SQL优化
慢查询
-
前面我们讲了一种监控SQL耗时的慢日志，目的是定位我们可能存在问题的SQL。对于慢的概念，即看电脑配置也看公司要求。
-
比如100W条数据中查询一条数据，电商的公司可能要求在2秒之内就要查出来，而对于维护网络资源的数据库管理员，可能不需要那么快，所以可以根据实际需求去选择是否开启慢日志和慢查询的阈值。
explain
- explain指令则是剖析我们的SQL语句，从根源上去分析SQL的问题
SQL语句层面优化
简单的说就是写出高质量的SQL，我准备把篇幅留给索引，这里引入巨佬的链接：常见的SQL优化方法。
简单总结：
- 减少检嵌套语句
- 减少多表查询
- 表的涉及要合理
- 分表分库优化
创建索引优化
我们看到，一般我们查询不是id就是姓名，姓名不容易重复，当然邮箱，电话号码也是可以的。比如我们很多国外的网站都是以邮箱作为用户名的，而移动端更多的使用手机号来绑定。这里看具体的业务要求了。
+----------+-------------+------------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+----------+-------------+------------------------+-------------+------+------+
| 10000000 | user9948351 | user9948351@aliyun.com | 13701995797 | 4 | 女 |
+----------+-------------+------------------------+-------------+------+------+
假如我们经常用用户名来查询的，那么我们个name创建一个索引，名字不一定唯一，我们建一个普通索引就完事了。我们看到创建索引也需要时间的，说明索引也是有开销的。
--创建普通索引
alter table users add index index_name(name);
--这耗时也离谱
Query OK, 0 rows affected (1 min 21.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次查询
之前是16秒多，这次0.03秒，优化了不知道多少倍。怎么样有点调优那味了吧。
mysql> select * from users where name='user100';
+------+---------+--------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+------+---------+--------------------+-------------+------+------+
| 2241 | user100 | user100@aliyun.com | 13701915669 | 15 | 男 |
+------+---------+--------------------+-------------+------+------+
1 row in set (0.03 sec)
使用explain查看sql的具体信息
我们看到我们之前定义的索引 index_name 被用到了,查询类型是ref，指的是该查询使用到了索引但不唯一，要继续往后扫描。
我们插入一个同名数据试试：
一样也能查出来。
+----------+---------+--------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+----------+---------+--------------------+-------------+------+------+
| 2241 | user100 | user100@aliyun.com | 13701915669 | 15 | 男 |
| 10009675 | user100 | user100@qq.com | 13706789719 | 22 | 男 |
+----------+---------+--------------------+-------------+------+------+
那么调优不是很简单吗，每个字段都建一个索引不就完事了吗？我现在告诉你建立索引耗时两分钟都在干什么了！
我们知道，InnoDB的索引和数据存在ibd文件里。
这是我们新建索引之前：
新建索引之后：
我们用了200M的磁盘空间去换取时间，在一定场合这个开销是合理的。但是如果我们建立多个索引而实际上并没有被使用，那么我们就白白消耗了空间，对于比较大的业务，上百张数据表，那么磁盘资源就更加紧张。
索引没被使用
我们给邮箱也添加一个普通索引
alter table users add index index_email(email);
执行以下SQL
耗时0.01秒，索引可以正常使用
select * from users where email='user102@aliyun.com';
--结果
+------+---------+--------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+------+---------+--------------------+-------------+------+------+
| 499 | user101 | user102@aliyun.com | 13701799507 | 88 | 女 |
| 1353 | user102 | user102@aliyun.com | 13701944078 | 42 | 女 |
+------+---------+--------------------+-------------+------+------+
2 rows in set (0.01 sec)
我们执行如下SQL
看上去没什么问题，我们看一下explain
select * from users where name='user101' and email='user102@aliyun.com';
--结果
+-----+---------+--------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+-----+---------+--------------------+-------------+------+------+
| 499 | user101 | user102@aliyun.com | 13701799507 | 88 | 女 |
+-----+---------+--------------------+-------------+------+------+
explain的结果
我们看到：
- 使用index_name索引就可以得到我们想要的值了，而我们的index_email索引的确是存在的，但是没有被使用，那我们如果花费了200M的空间却什么事情都不做，这不是搞事情吗？
对于以上情况，我们如果需求中既有使用邮箱查询和姓名查询的需求，而可能出其中某些索引不被使用的时候，应该使用组合索引。
创建组合索引
drop index index_name on users;
drop index index_email on users;
alter table users add index index_x(name,email);
还是用explain之前的SQL，我们看到这次我们使用组合索引，没有不被使用的索引
不过磁盘也占用很多
注意组合索引的最左原则导致的索引失效。
索引失效的场景
索引方便我们查询数据，但是索引有一些时候会失效。
参与运算
mysql> select * from users where id/100=3;
+-----+------------+-----------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+-----+------------+-----------------------+-------------+------+------+
| 300 | user131819 | user131820@aliyun.com | 13701894295 | 19 | 男 |
+-----+------------+-----------------------+-------------+------+------+
1 row in set (18.67 sec)
我们应该避免索引参与运算
mysql> select * from users where id=300;
+-----+------------+-----------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+-----+------------+-----------------------+-------------+------+------+
| 300 | user131819 | user131820@aliyun.com | 13701894295 | 19 | 男 |
+-----+------------+-----------------------+-------------+------+------+
1 row in set (0.04 sec)
like关键字
如果使用%通配符开头，索引失效
mysql> select * from users where name like '%user131819%';
+---------+-------------+------------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+---------+-------------+------------------------+-------------+------+------+
| 300 | user131819 | user131820@aliyun.com | 13701894295 | 19 | 男 |
| 1237393 | user1318190 | user1318190@aliyun.com | 13701881263 | 47 | 男 |
| 1237574 | user1318191 | user1318191@aliyun.com | 13701962811 | 51 | 男 |
| 1237636 | user1318192 | user1318192@aliyun.com | 13701627606 | 82 | 女 |
| 1237765 | user1318193 | user1318193@aliyun.com | 13701712012 | 58 | 男 |
| 1237814 | user1318194 | user1318194@aliyun.com | 13701656346 | 80 | 女 |
| 1237972 | user1318195 | user1318195@aliyun.com | 13701941532 | 20 | 男 |
| 1237988 | user1318196 | user1318196@aliyun.com | 13701781013 | 41 | 女 |
| 1238175 | user1318197 | user1318197@aliyun.com | 13701583243 | 12 | 女 |
| 1238233 | user1318198 | user1318198@aliyun.com | 13701630762 | 35 | 男 |
| 1238362 | user1318199 | user1318199@aliyun.com | 13701883081 | 99 | 女 |
+---------+-------------+------------------------+-------------+------+------+
11 rows in set (18.20 sec)
查询姓名时，我们尽量避免通配符起头。
mysql> select * from users where name like 'user131819%';
+---------+-------------+------------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+---------+-------------+------------------------+-------------+------+------+
| 300 | user131819 | user131820@aliyun.com | 13701894295 | 19 | 男 |
| 1237393 | user1318190 | user1318190@aliyun.com | 13701881263 | 47 | 男 |
| 1237574 | user1318191 | user1318191@aliyun.com | 13701962811 | 51 | 男 |
| 1237636 | user1318192 | user1318192@aliyun.com | 13701627606 | 82 | 女 |
| 1237765 | user1318193 | user1318193@aliyun.com | 13701712012 | 58 | 男 |
| 1237814 | user1318194 | user1318194@aliyun.com | 13701656346 | 80 | 女 |
| 1237972 | user1318195 | user1318195@aliyun.com | 13701941532 | 20 | 男 |
| 1237988 | user1318196 | user1318196@aliyun.com | 13701781013 | 41 | 女 |
| 1238175 | user1318197 | user1318197@aliyun.com | 13701583243 | 12 | 女 |
| 1238233 | user1318198 | user1318198@aliyun.com | 13701630762 | 35 | 男 |
| 1238362 | user1318199 | user1318199@aliyun.com | 13701883081 | 99 | 女 |
+---------+-------------+------------------------+-------------+------+------+
11 rows in set (0.00 sec)
函数
索引套在函数中，尽量避免这种情况
mysql> select * from users where round(id)=100;
+-----+------------+-----------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+-----+------------+-----------------------+-------------+------+------+
| 100 | user250735 | user250735@aliyun.com | 13701985934 | 67 | 女 |
+-----+------------+-----------------------+-------------+------+------+
1 row in set (17.20 sec)
隐式转换
当发生隐式转换时，索引失效
--修改一条数据方便看结果
update users set name='123' where id=1000000;
mysql> select * from users where name=123;
+---------+------+-----------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+---------+------+-----------------------+-------------+------+------+
| 1000000 | 123 | user923193@aliyun.com | 13701711580 | 35 | 男 |
+---------+------+-----------------------+-------------+------+------+
1 row in set, 65535 warnings (18.78 sec)
不要让隐式转换发生，明确我们查询条件的数据类型
mysql> select * from users where name='123';
+---------+------+-----------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+---------+------+-----------------------+-------------+------+------+
| 1000000 | 123 | user923193@aliyun.com | 13701711580 | 35 | 男 |
+---------+------+-----------------------+-------------+------+------+
1 row in set (0.00 sec)
组合索引
没有遵循最左原则导致的索引失效,上面之前提到了！
mysql> select * from users where email='user123@aliyun.com';
+------+---------+--------------------+-------------+------+------+
| id | name | email | phone | age | sex |
+------+---------+--------------------+-------------+------+------+
| 846 | user123 | user123@aliyun.com | 13701716454 | 4 | 女 |
| 1606 | user123 | user123@aliyun.com | 13701796195 | 57 | 男 |
+------+---------+--------------------+-------------+------+------+
2 rows in set (17.58 sec)
总结
- SQL语句优化
- 避免使用嵌套语句
- 避免使用多表查询
- 合理设计数据库
- 适当的分表分库
- 索引优化
- 适当的建立索引
- 合理使用索引
扫一扫
927
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
