如何提升Mysql查询效率？过来看看你踩了几个坑

天天踩坑，每次踩的坑都不同。

今天分享一下做全栈开发这么多年所得的sql知识经验，每一种错误基本都被组长diss过，有时候彭于晏分晏也挺难的。

1. 使用SELECT *

低效写法：

SELECT * FROM users;

原因： 当查询所有列时，即使只使用其中的一小部分数据，也会增加网络传输时间和内存消耗。

改进示例：

SELECT id, name, email FROM users;

2. 不使用索引

低效写法：

SELECT * FROM products WHERE description LIKE '%51%';

原因： LIKE 模式匹配通常不会利用索引，特别是当通配符出现在模式的开头时。

改进示例：

• 尽可能避免使用前导通配符。
• 如果必须使用，考虑对全文本字段建立全文索引。

3. 在WHERE子句中使用函数

低效写法：

SELECT * FROM orders WHERE DATE(order_date) = '2024-11-21';

原因： 在条件表达式中使用函数会阻止索引的使用。

改进示例：

SELECT * FROM orders WHERE order_date >= '2024-11-21' AND order_date < '2024-11-22';

4. 过度使用JOIN

低效写法：

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id JOIN products p ON o.product_id = p.id;

原因： 多个JOIN操作可能会导致笛卡尔积，从而显著增加查询复杂性和执行时间。

改进示例：

• 只选择必要的表进行JOIN。
• 考虑使用子查询或临时表来减少JOIN的数量。

5. 使用子查询而不是JOIN

低效写法：

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'US');

原因： 子查询可能会被多次执行，特别是在嵌套子查询中。

改进示例：

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.country = 'US';

6. 不使用分页

低效写法：

SELECT * FROM logs ORDER BY log_time DESC LIMIT 10000 OFFSET 10000;

原因： 大量的OFFSET会导致数据库扫描大量行，效率低下。

改进示例：

• 使用键值范围分页。

SELECT * FROM logs WHERE log_time < (SELECT log_time FROM logs ORDER BY log_time DESC LIMIT 1 OFFSET 10000) ORDER BY log_time DESC LIMIT 10000;

7. 使用OR条件

低效写法：

SELECT * FROM products WHERE category = 'electronics' OR brand = 'Apple';

原因： OR 条件可能导致索引失效。

改进示例：

• 使用UNION ALL来替代OR。

SELECT * FROM products WHERE category = 'electronics'
UNION ALL
SELECT * FROM products WHERE brand = 'Apple' AND category != 'electronics';

8. 不使用EXISTS

低效写法：

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers);

原因： IN 子查询可能会导致全表扫描。

改进示例：

SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id);

9. 使用DISTINCT

低效写法：

SELECT DISTINCT product_id FROM order_items;

原因： DISTINCT 会强制数据库对结果进行排序和去重，增加了额外的开销。

改进示例：

• 避免不必要的DISTINCT。
• 如果确实需要去重，考虑使用GROUP BY。

SELECT product_id FROM order_items GROUP BY product_id;

10. 不使用事务

低效写法：

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

原因： 没有事务保护的数据修改可能会导致数据不一致。

改进示例：

• 使用事务来确保数据的一致性和完整性。

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

一个好的程序员是那种过单行线马路都要往两边看的人