避免使用 SELECT *

SELECT * 会消耗更多的 CPU。
SELECT * 无用字段增加网络带宽资源消耗，增加数据传输时间，尤其是大字段（如 varchar、blob、text）。
SELECT <字段列表> 可减少表结构变更带来的影响。

分页优化

普通的分页在数据量小的时候耗费时间还是比较短的。如果数据量变大，达到百万甚至是千万级别，普通的分页耗费的时间就非常长了。像这种查询偏移量过大的场景我们称为深度分页。

# 从第10000行开始，获取10条记录。
SELECT `score`,`name` FROM `cus_order` LIMIT 10000, 10;

# 从第1000000行开始，获取10条记录。
SELECT `score`,`name` FROM `cus_order` LIMIT 1000000, 10;

# 子查询查出当前分页的首行id
SELECT `score`, `name` FROM `cus_order`
WHERE id >= (SELECT id FROM `cus_order` LIMIT 1000000, 1)
LIMIT 10;

先查询出 limit 第一个参数对应的主键值，再根据这个主键值再去过滤并 limit，这样效率会更快一些。

延迟关联的优化思路，跟子查询的优化思路其实是一样的：都是把条件转移到主键索引树，减少回表的次数。

SELECT `score`,`name` FROM `cus_order` a, (SELECT id from `cus_order` LIMIT 1000000, 10) b
WHERE a.id = b.id;

也可以使用 INNER JOIN（内连接）来连接主查询和子查询，而不是逗号连接。

SELECT `score`,`name` FROM `cus_order` a
INNER JOIN(SELECT id from `cus_order` LIMIT 1000000, 10) b
ON a.id = b.id
LIMIT 10;

尽量避免多表做 join

超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

join 的效率比较低，主要原因是因为其使用嵌套循环（Nested Loop）来实现关联查询，三种不同的实现效率都不是很高：

Simple Nested-Loop Join ：没有进过优化，直接使用笛卡尔积实现 join，逐行遍历/全表扫描，效率最低。
Block Nested-Loop Join ：利用 JOIN BUFFER 进行优化，性能受到 JOIN BUFFER 大小的影响，相比于 Simple Nested-Loop Join 性能有所提升。不过，如果两个表的数据过大的话，无论如何优化，Block Nested-Loop Join 对性能的提升都非常有限。
Index Nested-Loop Join ：在必要的字段上增加索引，使 join 的过程中可以使用到这个索引，这样可以让 Block Nested-Loop Join 转换为 Index Nested-Loop Join，性能得到进一步提升。

建议不要使用外键与级联

选择合适的字段类型

某些字符串可以转换成数字类型存储比如可以将 IP 地址转换成整型数据。
- INET_ATON()：把 ip 转为无符号整型（4-8位）
- INET_NTOA()：把整型的 ip 转为地址
对于非负型的数据（如自增 ID,整型 IP，年龄）来说要优先使用无符号整型来存储。
- 无符号相对于有符号可以多出一倍的存储空间
小数值类型（比如年龄、状态表示如 0/1）优先使用 TINYINT 类型。
对于日期类型来说，一定不要用字符串存储日期。可以考虑 DATETIME、TIMESTAMP 和数值型时间戳。
金额字段用 decimal，避免精度丢失。
尽量使用自增 id 作为主键。
- 如果主键为自增 id 的话，每次都会将数据加在 B+树尾部（本质是双向链表），时间复杂度为 O(1)。在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。
- 如果主键是非自增 id 的话，为了让新加入数据后 B+树的叶子节点还能保持有序，它就需要往叶子结点的中间找，查找过程的时间复杂度是 O(lgn)。如果这个也被写满的话，就需要进行页分裂。页分裂操作需要加悲观锁，性能非常低。
- 分库分表这类场景就不建议使用自增 id 作为主键，应该使用分布式 ID 比如 uuid
不建议使用 NULL 作为列默认值。
- NULL 代表一个不确定的值，就算是两个 NULL,它俩也不一定相等。例如，SELECT NULL=NULL的结果为 false，但是在我们使用DISTINCT,GROUP BY,ORDER BY时,NULL又被认为是相等的。
- ''的长度是 0，是不占用空间的，而NULL 是需要占用空间的。
- NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。
- 查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、<、> 之类的比较运算符。而''是可以使用这些比较运算符的。

类型	存储空间	日期格式	日期范围	是否带时区信息
DATETIME	5~8字节	YYYY-MM-DD hh:mm:ss[.fraction]	1000-01-01 00:00:00[.000000] ～ 9999-12-31 23:59:59[.999999]	否
TIMESTAMP	4~7字节	YYYY-MM-DD hh:mm:ss[.fraction]	1970-01-01 00:00:01[.000000] ～ 2038-01-19 03:14:07[.999999]	是
数值型时间戳	4字节	全数字如1578707612	1970-01-01 00:00:01之后的时间	否

尽量用 UNION ALL 代替 UNION

UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作，更耗时，更消耗 CPU 资源。
UNION ALL 不会再对结果集进行去重操作，获取到的数据包含重复的项。
如果实际业务场景中不允许产生重复数据的话，还是可以使用 UNION。

批量操作

对于数据库中的数据更新，如果能使用批量操作就要尽量使用，减少请求数据库的次数，提高性能。

优化慢 SQL

# 开启慢查询日志功能
SET GLOBAL slow_query_log = 'ON';

# 慢查询日志存放位置
SET GLOBAL slow_query_log_file = '/var/lib/mysql/ranking-list-slow.log';

# 无论是否超时，未被索引的记录也会记录下来。
SET GLOBAL log_queries_not_using_indexes = 'ON';

# 慢查询阈值（秒），SQL 执行超过这个阈值将被记录在日志中。
SET SESSION long_query_time = 1;

# 慢查询仅记录扫描行数大于此参数的 SQL
SET SESSION min_examined_row_limit = 100;

使用 show variables like 'slow%'; 命令进行查看。

日志中的一些信息进行说明：

Time ：被日志记录的代码在服务器上的运行时间。
User@Host：谁执行的这段代码。
Query_time：这段代码运行时长。
Lock_time：执行这段代码时，锁定了多久。
Rows_sent：慢查询返回的记录。
Rows_examined：慢查询扫描过的行数。

正确使用索引

选择合适的字段创建索引

不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

被频繁更新的字段应该慎重建立索引

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

尽可能的考虑建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

考虑在字符串类型的字段上使用前缀索引代替普通索引

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

避免索引失效

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：

使用 SELECT * 进行查询; SELECT * 不会直接导致索引失效（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖；
创建了组合索引，但查询条件未准守最左匹配原则；
在索引列上进行计算、函数、类型转换等操作；
以 % 开头的 LIKE 查询比如 LIKE '%abc'; ；
查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到；
IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同)；
发生隐式转换；

删除长期未使用的索引

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用

MySQL-SQL优化