MySQL优化（一）常用SQL优化-蒲公英云

MySQL优化（一）常用SQL优化

以你之姓@ 2022-02-26 05:58 353阅读 0赞

一、新增：

二、删除：delete from 删除语句加where条件，如果是删除全部记录，使用truncate table 表名，而不使用delete语句。

三、修改

四、查询：索引这里就不重复了，一般在建表时对常用的查询字段就应该加上索引。

1、单表查询：首先尽量只查询单表，可以分解成多次单表查询的尽量分解；其次，在多表查询的优化中，也可以以表来单位来优化，如加索引等；

2、连接查询：对连接查询on后面的条件加上索引；

3、合并查询：

4、聚合函数查询：

5、分组查询group by：少用group by语句。如果使用了group by，消除group by的无谓排序：在group by ..后面加上 order by null；

6、排序order by：SQL查询会默认按照主表的id排序，尽量不要加上其他的排序；

7、in查询：in查询范围不要太大；

8、分页查询：千万级MySQL分页，使用 SELECT * FROM LIMIT 0,10 分别对表进行常规分页,当偏移量过大，如limit 1000000000,10扫描的行数会非常多造成查询过慢。MySQL的limit工作原理就是先读取前面n条记录，然后抛弃前n条，读后面m条想要的，所以n越大，偏移量越大，性能就越差。优化方法有下面几种：

（1）子查询法：

SELECT c1,c2,cn... FROM table WHERE id>=(SELECT id FROM table LIMIT 20000,1 )  LIMIT 10;

（2）只读索引方法：：高性能MySQL一书中提到的

SELECT * FROM `content` AS t1   
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2   
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

优化前：SELECT c1,c2,cn… FROM member ORDER BY last_active LIMIT 50,5 优化后：

SELECT c1, c2, cn .. . FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)

区别在于，优化前的SQL需要更多I/O浪费，因为先读索引，再读数据，然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了，然后通过member_id读取需要的列。

（3）in查询法：第一步用用程序读取出ID，然后再用IN方法读取所需记录：

SELECT id FROM table LIMIT 20000, 10;
    SELECT c1, c2, cn .. . FROM table WHERE id IN (id1, id2, idn.. .)

建议使用第一种子查询方法，即：

#查询分页
SELECT
    id
FROM
    t_user
WHERE
    ...
AND id >= (
    SELECT
        id
    FROM
        t_user
    WHERE
        ...
    LIMIT (page - 1) * pageSize,
    1
)
LIMIT pageSize;