Mysql 性能优化（2）

最新推荐文章于 2024-06-25 10:30:00 发布

Payne Yu

最新推荐文章于 2024-06-25 10:30:00 发布

阅读量291

点赞数 1

分类专栏：数据库文章标签： mysql 性能优化

本文链接：https://blog.csdn.net/weixin_42338555/article/details/87626857

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

Mysql 性能优化（1）–设置开启慢查询记录并使用慢查询工具分析

一、分析sql查询

使用explain查询sql的执行计划

explain select customer_id,first_name,last_name from customer;

在这里插入图片描述

二、优化sql查询

2.1 优化max和count函数

**max函数：**获取最大订单日期：

explain select max(payment_date) from payment;

在这里插入图片描述
由上图可知，上述语句进行全部扫描，并未使用索引。我们给payment_date 字段添加索引：

create index idx_paydate on payment(payment_date);

在这里插入图片描述
count函数： 在一条sql中查询2007和20016年的电影数量；

select count(release_year='2006' or null) as '2006年电影数量',count(release_year='2007' or null) as '2007年电影数量' from film;

注：null值是不在count中计数的

2.2 子查询优化

通常我们会把子查询优化为join查询，但在优化的时候需要注意是否有一对多的关系，要注意数据重复。

2.3 order by 优化

order by子句，尽量使用Index方式排序，在索引列上遵循索引的最佳左前缀原则。

复合（联合）索引形如 key (‘A1’,’A2’,’A3’ )，排序的思路一般是，先按照A1来排序，A1相同，然后按照A2排序，以此类推，这样对于（A1）,(A1,A2), (A1,A2,A3)的索引都是有效的

2.4 group by 优化

group by与order by的索引优化基本一样，group by实质是先排序后分组，也就是分组之前必排序，遵照索引的最佳左前缀原则可以大大提高group by的效率。

当无法使用索引列排序时，适当增大sort_buffer_size参数 + 适当增大max_length_for_sort_data参数可以提高filesort排序的效率。注意：可能会出现Using temporary，也就是说mysql在对查询结果排序时使用了临时表。

where高于having，能写在where限定条件中的就尽量写在where中。

三、索引优化

3.1 创建索引

在这里插入图片描述

3.1 优化索引

重复索引：一个字段上建立多个索引，因为见过在主键上建立唯一索引的这种二笔写法，深深地震惊了我。
冗余索引：指多个索引的前缀列相同，或者在联合索引中包含了主键的索引。

以下语句可查询重复索引：

SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.COLUMN_NAME, 
a.INDEX_NAME AS 'index1', b.INDEX_NAME AS 'index2'
FROM information_schema.STATISTICS a 
JOIN information_schema.STATISTICS b 
ON a.TABLE_SCHEMA = b.TABLE_SCHEMA    
AND a.TABLE_NAME = b.TABLE_NAME 
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX   
AND a.COLUMN_NAME = b.COLUMN_NAME 
WHERE a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME <> b.INDEX_NAME

四、数据库优化

4.1 选择合适的数据类型

最小数据类型
简单数据类型
尽量避免NULL

选择数据类型时：
需要确定合适的大类型：数字、字符串、时间。
选择具体的类型。考虑长度、范围，允许的精度，需要的物理空间。
例如：
选择时间时，DATETIME 和 TIMESAMP 都可以存储相同类型的数据：日期，时间，精确到秒。然而TIMESAMP 只使用 DATETIME 一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。另一方面，TIMESAMP 允许的时间范围要小得多，有时候它的特殊能力会成为障碍。

不好的设计：