MySQL数据库查询性能优化的4个技巧干货

1. 创建合适的索引

索引可以加快查询速度，但过多的索引会降低插入和更新操作的速度，因此需要创建合适的索引。

1.1 查看查询语句的执行计划

在执行SQL语句前，可以通过 EXPLAIN关键字查看这个查询语句的执行计划。执行计划是MySQL优化器访问一个SQL语句时的查询计划。

比如下面的语句：

EXPLAIN SELECT * FROM users WHERE username='admin';

执行结果：

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ref  | username      | username | 767 | const| 1    | Using index |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

可以从结果中看到，MySQL使用了username索引，这是因为username有索引。如果没有索引，MySQL将会全表扫描，执行速度会很慢。

1.2 创建合理的索引

• 索引的选择原则：最左匹配原则，即如果有复合索引，那么使用这个索引时，必须从左往右按照索引顺序进行匹配，否则索引无效。
• 索引的覆盖原则：如果一个查询可以使用索引覆盖，那么查询速度会非常快。比如下面这个语句：

SELECT id FROM users WHERE username='admin';

如果username字段上有索引，那么查询结果只需要读取索引而不需要从磁盘读取数据，即索引覆盖了这个查询。如果一个查询可以使用到索引覆盖，那么应该优先建立简洁的覆盖索引。

示例

对于以下SQL语句：

SELECT * FROM users WHERE province='Guangdong' AND city='Shenzhen' AND age>20;

可以根据province、 city 和 age这三个条件建立复合索引，如下所示：

CREATE INDEX users_prov_city_age ON users (province,city,age);

2. 避免在查询中使用“*”

使用“*”查询所有列会影响查询性能，因为它需要读取表中所有的数据。同时，如果查询中的不需要的字段被缓存在内存中，就会浪费内存。

示例

以下查询会查询表中的所有信息：

SELECT * FROM users;

可以改为只查询所需的字段，如下所示：

SELECT id,username FROM users;

3. 避免使用子查询和不必要的联表查询

子查询和联表查询会增加查询的复杂度和查询时间。

3.1 使用JOIN代替子查询

使用JOIN代替子查询可以减小SQL语句的嵌套程度，提高查询效率。

以下查询使用了子查询：

SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

可以改为使用JOIN语句，如下所示：

SELECT u.* FROM users u JOIN (SELECT AVG(age) AS avgAge FROM users) as t ON u.age > t.avgAge;

3.2 避免不必要的联表查询

联表查询是指在查询中使用了多个表，如果不必要则会浪费很多查询时间。

以下查询使用了不必要的联表查询：

SELECT * FROM users u LEFT JOIN users_detail d ON u.id = d.user_id;

如果只需要users表中的数据，则可以省略LEFT JOIN users_detail d ON u.id = d.user_id语句，如下所示：

SELECT * FROM users;

4. 使用分页和限制查询结果集

对于大型查询，不需要返回全部结果。避免查询大量的数据可通过LIMIT和分页来实现。

4.1 使用LIMIT和OFFSET

LIMIT 和 OFFSET 关键字可以用来限制查询结果集。比如：

SELECT * FROM users ORDER BY age LIMIT 10 OFFSET 0;

表示查询年龄最小的10个用户。

4.2 使用分页

如果要查询的数据量过大，使用分页的方式来获取数据，可以优化查询性能。如下所示：

SELECT * FROM users ORDER BY age LIMIT 10 OFFSET 0;
SELECT * FROM users ORDER BY age LIMIT 10 OFFSET 10;

表示查询年龄最小的10条记录，再查询下一组10条记录。

总结

在MySQL数据库查询性能优化中，创建合适的索引、避免使用“*”、避免使用子查询和不必要的联表查询，以及使用分页和限制查询结果集是非常重要的技术。优化查询性能可以提高数据库的整体性能和响应速度。