持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第1天，点击查看活动详情

1、使用索引

使用索引可以更快地查找具有特定列值的行，在不使用索引的情况下，MySQL 必须从第一行开始搜索并遍历整个表以找到所需的记录。所以在数据量级大的表中建立索引可以极大的优化查询速度。

CREATE INDEX tablename_columnname_idx ON tablename (columnname);

2、避免空值

尽可能将所有的列都声明为NOT NULL，或者使用特殊的值，比如：0。这样可以更好地使用索引，因为NULL需要特殊处理，从而使 SQL 操作变慢。

3、SELECT 查询时指定列

这个是非常常见的优化方案，在SELECT * 查询所有字段时，查询速度远低于查询某个具体的字段。尤其是在数据量大的表中。

// NO
SELECT * FROM user;

// YES
SELECT id,user_name FROM user;

4、使用合适的数据类型

MySQL 中有 20 多种不同的数据类型，专为不同情况下的用途设计。

一些数据类型包括 Timestamp、DateTime、Integer、ENUM、Float、Double、Char、LongText 和 Text。

在设计表时，需要尽量减少磁盘上使用的空间。占用较少磁盘空间的表会产生较小的索引，可以在更短的时间内处理。例如，如果一个表将托管少于 100 条记录，应该使用 TINYINT 数据类型作为唯一 ID，因为它占用的空间比 INT 少。

5、避免使用SELECT DISTINCT

大多数情况下，Distinct函数都会导致对最终结果集完成一次排序，所以，Distinct一直是SQL语言中成本最高的函数之一。DISTINCT 命令需要对数据库进行更多的排序和读取，这需要更多的处理能力。可以将DISTINCT 可以替换为 GROUP BY 以获得相同的结果。

// 使用DISTINCT
SELECT DISTINCT col1, col2 FROM table;

// 使用GROUP BY
SELECT col1, col2 FROM table GROUP BY col1, col2;

6、限制列数

MySQL 限制每个表有 4096 列。使用更少的列可以获得更高的性能。尽量使用不要超过一百列，更多的列会占用更多的CPU时间来进行处理。

7、在关键词末尾使用通配符%

在搜索数据时，通配符会导致最广泛的扫描，这是非常低效的。前导通配符是最低效的，尤其是与结尾通配符结合使用时。在这种情况下，MySQL 必须搜索所有记录以查找匹配项。因此，应该避免使用通配符。

// 避免结合使用
SELECT * FROM city WHERE name LIKE '%Al%';


SELECT * FROM city WHERE name LIKE 'Al%';

8、使用 UNION 子句优化 LIKE 语句

OR 运算符用于组合两个布尔表达式，并在满足任一条件时返回 true。在查询中使用OR时，MySQL 优化器可能会错误地选择全表扫描来检索结果集。会使查询运行速度变慢。使用 UNION 子句可以运行得更快并给出相同的结果。

示例如下：

// 优化前
SELECT * FROM city WHERE Name LIKE 'C%' or District LIKE 'C%';

// 优化后
SELECT * FROM city WHERE Name LIKE 'C%' UNION ALL SELECT * FROM city WHERE District LIKE 'C%';

SELECT * FROM city WHERE Name LIKE 'C%' UNION SELECT * FROM city WHERE District LIKE 'C%';