Sql优化 - sql及索引优化
95 / 0 / 创建于 1年前 /
cccyzloong 的个人博客
可以选择从以下几个方面考虑，优先级从低到高
- sql及其索引
- 数据库的表结构
- 系统的配置
- 硬件
首先需要开启mysql的慢查询日志
- 查看慢sql日志开启情况
mysql> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | OFF | +----------------+-------+ 1 row in set (0.04 sec) - 开启
// 记录文件位置 mysql> set global slow_query_log_file='/home/mysql/sql_log/slow.log'; Query OK, 0 rows affected (0.00 sec) // 记录未使用索引的sql mysql> set global log_queries_not_using_indexes=on; Query OK, 0 rows affected (0.00 sec) // 记录执行时间超过一秒的sql mysql> set global long_query_time=1; Query OK, 0 rows affected (0.00 sec) // 开启慢sql日志 mysql> set global slow_query_log=on; Query OK, 0 rows affected (0.00 sec) - 慢sql的格式为
/usr/sbin/mysqld, Version: 8.0.29 (MySQL Community Server - GPL). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock Time Id Command Argument # Time: 2022-08-29T14:37:29.144372Z # User@Host: root[root] @ localhost [] Id: 10 # Query_time: 0.000865 Lock_time: 0.000008 Rows_sent: 0 Rows_examined: 0 use alex_blog; SET timestamp=1661783849; select id,name,email from users; - 可以使用Mysqldumpslow工具分析慢查询日志
通过explain 查看执行计划
- eg
mysql> explain select id,name,email from users; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) - 其中上面的每一列代表的含义为
- select_type 查询类型
- table 表名
- type 最重要的列，显示连接使用了何种类型。从最好到最差的连接为类型为const、eq_reg、ref、range、index、all
- possible_keys 显示可能用在这这表中的索引
- key 实际用到的索引
- key_len 使用索引的长度
- ref 显示索引的哪一列被使用了
- rows 扫描的行数
- filtered 结果行占总行数的百分比
- Extra:额外的信息说明
sql及其索引优化 max和count函数
max()函数
mysql> explain select max(updated_at) from users; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.06 sec) mysql> create index idx_update_time on users(updated_at); Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select max(updated_at) from users; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec) // 可以看到，添加索引后会直接在查询计划阶段获取结果count()函数
mysql> select * from users; +------+-----------+--------------+------------+------------+ | id | name | email | created_at | updated_at | +------+-----------+--------------+------------+------------+ | 1 | alex | alex@email | NULL | NULL | | 2 | bianca | bianca@email | NULL | NULL | | NULL | test_none | none@email | NULL | NULL | +------+-----------+--------------+------------+------------+ 3 rows in set (0.00 sec) mysql> select count(id = 1) as count_id from users; +----------+ | count_id | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) mysql> select count(id = 1 or null) as count_id from users; +----------+ | count_id | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) // 注意看其结果，如果数据列有null值的话，count时不加or null 会将此行数据计数 // 也就是说，如果此列的数据不是null值的话 count函数都会将此行数据进行计数
子查询优化
select 子句改为join的方式，此时要注意一对多关系
mysql> select * from s_1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> select * from s_2; +------+ | id | +------+ | 1 | | 1 | +------+ 2 rows in set (0.00 sec) mysql> select id from s_1 where id in (select id from s_2); +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> select distinct s_1.id from s_1 join s_2 on s_1.id=s_2.id; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) // 假设不需要去重 我们可以查看其执行计划 mysql> explain select s_1.id from s_1 left join s_2 on s_1.id=s_2.id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: s_1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s_2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: Using where; Using join buffer (hash join) 2 rows in set, 1 warning (0.00 sec) mysql> explain select id from s_1 where id in (select id from s_2)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: s_1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s_2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 50.00 Extra: Using where; FirstMatch(s_1); Using join buffer (hash join) 2 rows in set, 1 warning (0.00 sec) // 通过对比发现 如果使用子查询则在extra中会先进行FirstMatch(s_1)
group by 优化
- 采用mysql官方的sakila数据库 dev.mysql.com/doc/index-other.html
- 未优化的sql语句
mysql> explain select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 200 filtered: 100.00 Extra: Using temporary *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor partitions: NULL type: ref possible_keys: PRIMARY,idx_fk_film_id key: PRIMARY key_len: 2 ref: sakila.actor.actor_id rows: 1 filtered: 100.00 Extra: Using index 2 rows in set, 1 warning (0.00 sec) - 先前的sql改为先进行分组、在进行连接查询
mysql> explain select actor.first_name,actor.last_name from actor inner join (select actor_id,count(actor_id) from film_actor group by actor_id) c using(actor_id)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: actor partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 200 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ref possible_keys: <auto_key0> key: <auto_key0> key_len: 2 ref: sakila.actor.actor_id rows: 27 filtered: 100.00 Extra: NULL *************************** 3. row *************************** id: 2 select_type: DERIVED table: film_actor partitions: NULL type: index possible_keys: PRIMARY,idx_fk_film_id key: PRIMARY key_len: 4 ref: NULL rows: 5462 filtered: 100.00 Extra: Using index 3 rows in set, 1 warning (0.00 sec) // 可以看到 extra 字段 不再使用temporary
limit 查询优化
- limit 常用于分页处理、时常会伴随order by 语句、因此大多数时候会使用filesorts,大数据量时可能会造成性能问题
mysql> explain select film_id,description from film order by title limit 50,5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec) - 优化方法1：使用有索引的列或者主键进行order by操作
mysql> explain select film_id,description from film order by film_id limit 50,5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: index possible_keys: NULL key: PRIMARY key_len: 2 ref: NULL rows: 55 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) - 优化方式2：上面的例子中我们可以发现rows变成了55行，但是随着分页越来越多，扫描的行数也越来越多，因此可以记录上次的主键，在下次查询的时候使用主键过滤
mysql> explain select film_id,description from film where film_id > 50 and film_id <= 55 order by film_id limit 1,5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: NULL rows: 5 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) // 发现此时只需要扫描5行，在实际运用中需要注意主键是否连续
选择合适的列添加索引，一般在以下情况下建立索引
- 在 where、group by、order by、 on 从句中出现的列中见索引
- 索引的字段越小越好
- 离散度大的放在联合索引的前面
select * from payment where staff_id=2 and customer_id=584 // index(customer_id,staff_id)
查找重复及冗余索引
- 使用pt-duplicate-key-checker等工具进行分析
删除不再使用的索引
- pt-index-usage 工具进行简单分析
本作品采用《CC 协议》，转载必须注明作者和本文链接
关于 LearnKu
粤公网安备 44030502004330号
推荐文章：