Mysql十大经典案例 SQL优化

ゝ一世哀愁。 2023-10-09 11:26 40阅读 0赞

## 分页优化案例 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdqaWVjaGFvOTQ1_size_16_color_FFFFFF_t_70][]

**普通写法：**

select * from buyer where sellerid=100 limit 100000，5000

普通limit M，N的翻页写法，在越往后翻页的过程中速度越慢，原因mysql会读取表中的 前M+N条数据，**M越大，性能就越差**。

**优化写法：**

select t1.* from buyer t1,
    (select id from buyer sellerid=100 limit 100000，5000) t2
    where t1.id=t2.id;

注意：需要在t表的sellerid字段上创建索引，id为表的主键 create index ind\_sellerid on buyer(sellerid);

**原始语句：**

select id, ...
    from buyer
    where sellerId = 765922982
    and gmt_modified >= '1970-01-01 08:00:00'
    and gmt_modified <= '2013-06-05 17:11:31'
    limit 255000, 5000；

**优化后语句：**

select t2.*
    from (select id
    from buyer where sellerId = 765922982
    and gmt_modified >= '1970-01-01 08:00:00'
    and gmt_modified <= '2013-06-05 17:11:31'
    limit 255000, 5000）t1,
    buyer t2
    where t1.id=t2.id

**查询时间：60S -> 0.2S**

## 子查询优化案例 ##

**典型子查询**

SELECT first_name
    FROM employees
    WHERE emp_no IN
    (SELECT emp_no FROM salaries_2000 WHERE salary = 5000);

MySQL的处理逻辑是遍历employees表中的每一条记录，代入到子查询中中去

**改写子查询**

SELECT first_name
    FROM employees emp,
    (SELECT emp_no FROM salaries_2000 WHERE salary = 5000) sal
    WHERE emp.emp_no = sal.emp_no;

**执行时间：1200S -> 0.1S**

## SQL优化最佳实践 ##

### 1、分页优化 ###

**采用高效的 Limit 写法，避免分页查询给数据库带来性能影响**

### 2、子查询优化 ###

**子查询在5.1，5.5版本中都存在较大风险，将子查询改为关联 使用Mysql 5.6的版本，可以避免麻烦的子查询改写**

### 3、查询需要的字段 ###

**避免用 SELECT \* 查询所有字段数据，只查询需要的字段数据**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdqaWVjaGFvOTQ1_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190925122456429.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdqaWVjaGFvOTQ1,size_16,color_FFFFFF,t_70