提升MYSQL查询效率的10个SQL语句优化技巧

1. 优化SQL语句的执行顺序：在SQL语句中使用正确的表连接方式、正确的索引来建立表之间的关系，以最小化系统的I/O操作。尽量避免大表与大表之间的联接。

正确的表连接方式：

• INNER JOIN：表示只返回连接表中满足条件的记录。
• LEFT JOIN：表示返回连接表A中的所有记录，即使在连接表B中没有匹配的记录，也会显示A表的记录。
• RIGHT JOIN：表示返回连接的所有表B中的记录，即使在连接表A中没有匹配的记录，也会显示B表的记录。

示例1：查询订单表中，订单金额大于100元的订单，关联查询客户表中客户姓名和电话号码

SELECT o.order_id, c.customer_name, c.phone 
FROM orders AS o 
INNER JOIN customers AS c 
ON o.customer_id = c.customer_id 
WHERE o.amount > 100;

1. 使用索引优化SQL查询：索引可以加快查询速度，但不要在每个字段上都创建索引，索引的数量应该根据数据的复杂性和查询的需求来确定。当需要对某列进行排序时，用ORDER BY子句可能会导致查询变慢，可以考虑创建联合索引来优化查询。

示例2：查询订单表中，客户id为10的所有订单，按照订单金额降序排序

CREATE INDEX idx_customer_id_amount ON orders (customer_id, amount DESC); 

SELECT * FROM orders 
WHERE customer_id = 10 
ORDER BY amount DESC;

1. 使用Limit限制返回的记录数：使用Limit可以避免在查询语句返回所有记录时造成系统效率的下降，同时也能加快查询速度。在处理大型数据集时，可以使用Limit提高整体的性能。

示例3：查询商品表中前10个最贵的商品

SELECT * FROM products 
ORDER BY price DESC 
LIMIT 10;

1. 避免使用SELECT *：SELECT * 可能会返回许多无关的列，会降低查询性能，应该只返回需要的列。

示例4：查询客户表中所有客户的姓名和电话号码

SELECT customer_name, phone 
FROM customers;

1. 使用外键关联表：在需要关联多个表时，使用外键可以让数据库管理工具在执行操作时保证数据的完整性。

示例5：查询订单表中的所有订单，同时查询每个订单所包含的商品信息。

SELECT o.order_id, p.product_name, p.price 
FROM orders AS o 
INNER JOIN order_items AS oi ON o.order_id = oi.order_id 
INNER JOIN products AS p ON oi.product_id = p.product_id;

1. 避免使用子查询：在处理大数据量时，使用子查询可能会影响查询速度。

示例6：查询商品表中，售价高于平均售价的所有商品

SELECT * 
FROM products 
WHERE price > (SELECT AVG(price) FROM products);

1. 使用视图代替复杂查询：在复杂SQL语句中，可以使用视图来缓存数据，减少重复查询数据库的次数，提高查询性能。

示例7：创建一个代表顾客已完成的订单信息的视图，包含订单编号、顾客姓名和订单金额。

CREATE VIEW completed_orders AS 
SELECT o.order_id, c.customer_name, o.amount 
FROM orders AS o 
INNER JOIN customers AS c ON o.customer_id = c.customer_id 
WHERE o.status = 'completed';

1. 优化磁盘和文件：MySQL数据库需要读写数据，所以在优化查询性能时，需要考虑磁盘和文件使用的问题。

示例8：优化MySQL数据库的磁盘空间，通过清理历史记录、删除无用数据、优化查询语句等方式来达到数据的优化和压缩。

1. 控制SQL查询中返回记录的行数：当需要提取大量数据时，可以使用分页的方式控制SQL查询的提交速度，以避免导致系统的性能下降。

示例9：查询商品表中的前20个商品，显示商品名和售价

SELECT product_name, price 
FROM products 
LIMIT 20 OFFSET 0;

1. 使用EXPLAIN进行查询性能分析：使用EXPLAIN可以分析SQL查询语句执行过程中产生的问题，以找出导致系统性能问题的原因。

示例10：查询订单表中包含客户姓名的订单，使用EXPLAIN分析查询性能

EXPLAIN SELECT * 
FROM orders AS o 
INNER JOIN customers AS c ON o.customer_id = c.customer_id;