MySQL优化
2021/8/14 19:06:11
本文主要是介绍MySQL优化,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
来源: https://www.imooc.com/video/3688
数据库优化目的
- 避免出现页面错误
- 增加DB稳定性
- 提高网站整体性能
DB优化方向
数据准备
MySQL慢查询日志开启方式和存储格式
如何发现有问题的SQL 答:使用慢查询日志或EXPLAIN关键字进行语句分析
mysql> show variables like "%query_log%"; +---------------------+----------------------------------+ | Variable_name | Value | +---------------------+----------------------------------+ | slow_query_log | ON | | slow_query_log_file | D:/MySQL_data/slow_query_log.txt | +---------------------+----------------------------------+ 2 rows in set (0.00 sec) mysql> show variables like "long_query_time"; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 3.000000 | +-----------------+----------+ 1 row in set (0.00 sec)
慢查询日志分析工具 —— mysqldumpslow
慢查询日志分析工具 —— pt-query-digest
安装连接:
问题SQL分析
SQL以及索引优化 - EXPLAIN
COUNT()和MAX()优化
- MAX()
mysql> use sakila; Database changed mysql> select max(payment_date) from payment; +---------------------+ | max(payment_date) | +---------------------+ | 2006-02-14 15:16:03 | +---------------------+ 1 row in set (0.12 sec) mysql> explain select max(payment_date) from payment; +----+-------------+---------+------+---------------+------+---------+------+-------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+-------+-------+ | 1 | SIMPLE | payment | ALL | NULL | NULL | NULL | NULL | 16451 | | +----+-------------+---------+------+---------------+------+---------+------+-------+-------+ 1 row in set (0.03 sec) mysql> create index idx_payment_date on payment(payment_date); Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select max(payment_date) from payment; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ 1 row in set (0.00 sec)
- COUNT()
COUNT(*)和COUNT(1)都会将 null 统计在内
mysql> select * from tmp; +------+ | id | +------+ | NULL | | 2 | | 3 | | 0 | +------+ 4 rows in set (0.00 sec) mysql> select count(*) from tmp; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) mysql> select count(1) from tmp; +----------+ | count(1) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec)
使用COUNT的正确案例
eg: 查出2006年电影数量
SELECT COUNT(release_year='2006' OR NULL) FROM film;
子查询优化
group by查询优化
limit查询优化
mysql> EXPLAIN select film_id, description from film order by title limit 50; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | film | ALL | NULL | NULL | NULL | NULL | 949 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec)
- 优化1: 使用有索引的列或主键进行ORDER BY操作
mysql> EXPLAIN select film_id, description from film order by film_id limit 50; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ | 1 | SIMPLE | film | index | NULL | PRIMARY | 2 | NULL | 50 | | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ 1 row in set (0.04 sec)
- 优化2: 记录上次返回的主键,在下一次查询时使用主键过滤
mysql> EXPLAIN select film_id, description from film where film_id > 55 and film_id <= 60 order by film_id limit 1, 5; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | film | range | PRIMARY | PRIMARY | 2 | NULL | 5 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.05 sec)
避免了数据量大时扫描过多的记录(保证索引有序)
建立合适的索引
- 如何判断指定字段的离散程度
mysql> select count(distinct customer_id), count(distinct staff_id) from payment; +-----------------------------+--------------------------+ | count(distinct customer_id) | count(distinct staff_id) | +-----------------------------+--------------------------+ | 599 | 2 | +-----------------------------+--------------------------+ 1 row in set (0.01 sec) customer_id的离散程度 高于 staff_id
索引优化SQL
- 如何查找重复以及冗余索引?
数据库结构优化
数据库表范式优化`
反范式优化
数据库表的垂直拆分
数据库表的水平拆分
数据库系统配置优化
MySQL配置文件优化
这篇关于MySQL优化的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享
- 2024-02-22docker mysql 5.7