在 MySQL 5.0 之前的版本尽量避免使用or查询

  1. 表很小，直接全票扫描比走索引快
  2. ON 或 WHERE 条件里面没有用索引
  3. 条件里面的索引列使用常量进行比较
  4. 一张表一个索引条件的占比很大，可以添加一个基数小的索引条件进行查询
  5. 用FORCE INDEX (index_for_column)强制走索引【use index (index_for_column) 只是建议走索引】
  6. --max-seeks-for-key=1000或使用SET max_seeks_for_key=1000告诉优化器假定没有键扫描导致超过1000个键查找。请参见第5.1.7节“服务器系统变量”。

批量insert数据

关闭自动提交。每秒提交数百次会限制性能（受存储设备的写入速度限制）

  1. 请关闭自动提交模式，因为它会在每次插入时对磁盘执行日志刷新
    SET autocommit=0;
    ... SQL import statements ...
    COMMIT;
  2. 如果您UNIQUE对辅助键有限制，则可以通过在导入会话期间暂时关闭唯一性检查来加快表的导入
    SET unique_checks=0;
    ... SQL import statements ...
    SET unique_checks=1;
  3. 如果FOREIGN KEY表中有约束，可以通过在导入会话的持续时间内关闭外键检查来加快表的导入：
    SET foreign_key_checks=0;
    ... SQL import statements ...
    SET foreign_key_checks=1;
  4. INSERT 如果需要插入许多行， 请使用多行语法来减少客户端和服务器之间的通信开销：
    INSERT INTO yourtable VALUES (1,2), (5,5), ...;
  5. AUTO_INCREMENT 自增ID 处理
    innodb_autoinc_lock_mode=0 传统锁定模式
    innodb_autoinc_lock_mode=1 连续锁定模式
    innodb_autoinc_lock_mode=2 交错锁定模式
    自增出现异常，id也会增长
    自增id设置
    auto_increment_offset+ N× auto_increment_increment

SELECT 优化

  1. 子查询优化
    ((a AND b) AND c OR (((a AND b) AND (c AND d))))
    -> (a AND b AND c) OR (a AND b AND c AND d) 删除不必要的括号
    (b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
    -> b=5 OR b=6 取消恒定的条件
    outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

优化方式1：

  • 如果需要要null
    EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

  • 如果不需要null
    outer_expr is not null

in 和 exists 区别，

  1. in 用于内部查询表比外部查询表小的时候，exists 用于内部查询表比外部查询表大的时候
  2. exists 会占用额外的空间，只对外部查询进行是否存在判断、in 会进行嵌套for 循环遍历

索引使用的常量表达式仅计算一次。索引=（计算 1+1），不能 索引+1=值
范围查询

  • Hash 索引 =， <=>， IN()，IS NULL，或IS NOT NULL
  • BTree 索引 >， <， >=， <=， BETWEEN， !=，或 <> ，like 通配符不能放在最前面
  • 使用use index(索引) 进行强制走索引 强制控制优化器：https://dev.mysql.com/doc/refman/5.6/en/index-hints.html
    例子：强制走索引
    SELECT * FROM table1 USE INDEX (col1_index,col2_index)
    WHERE col1=1 AND col2=2 AND col3=3;
    例子：忽略索引
    SELECT * FROM table1 IGNORE INDEX (col3_index)
    WHERE col1=1 AND col2=2 AND col3=3;
  1. Is null 优化
  2. 排序优化 https://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html
  • 设置 sort_buffer_size 默认256K
  • 对于未使用的慢ORDER BY查询 filesort，请尝试将max_length_for_sort_data 系统变量降低 为适合触发的值 filesort。（将此变量的值设置得太高的症状是磁盘活动过多和CPU活动较低的组合。）
    ORDER BY执行计划信息可用
    使用 EXPLAIN （请参见第8.8.1节“使用EXPLAIN优化查询”），可以检查MySQL是否可以使用索引来解析ORDER BY子句：
  • 如果输出Extra列 EXPLAIN不包含Using filesort，则使用索引，filesort而不执行a。
  • 如果输出Extra列 EXPLAIN包含 Using filesort，则不使用索引并filesort执行a。
  1. 分组优化 （有临时表）
  • 尽量避免默认排序
  1. DISTINCT优化
  • DISTINCT ORDER BY在很多情况下，结合需要一个临时表。
    在大多数情况下，DISTINCT子句可以视为的特殊情况GROUP BY。例如，以下两个查询是等效的：
SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;

SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;
  1. limit 优化
  • Limit 会影响order by执行计划
mysql> SELECT * FROM ratings ORDER BY category;

+----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
| 2 | 3 | 5.0 |
| 7 | 3 | 2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;

+----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 4 | 2 | 3.5 |
| 3 | 2 | 3.7 |
| 6 | 2 | 3.5 |
+----+----------+--------+

如果id是唯一的，可以加上id进行排序

mysql> SELECT * FROM ratings ORDER BY category, id;

+----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
| 2 | 3 | 5.0 |
| 7 | 3 | 2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;

+----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
+----+----------+--------+

  1. 范围优化

  2. 去除非索引绝对为true 或 false 的条件，如1=1，like '%**'

  3. 查询in、or里面的查询条件的数量多并且不是唯一索引的时候会印象性能
    存在单索引FORCE INDEX索引提示。这样的想法是，如果强制使用索引，那么执行潜入索引的额外开销将无济于事。

索引不是唯一索引，不是 FULLTEXT索引。

没有子查询。

没有DISTINCT，GROUP BY或ORDER BY子句存在。

  1. 索引合并

避免AND、OR混合使用
(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

可以考虑使用union 、union all
union 会进行出重操作

  1. 外连接优化

如果left join 或 right join 匹配到的行是null，且处理过程中不要这些null行
可以考虑使用inner join（内连接）
好处：left join、right join 会出现查出大表后再查小表的情况，inner join 会自动选择出小表、然后再去查大表
能大大小了table中匹配行

  1. 函数调用优化
    MySQL函数在内部被标记为确定性或不确定性。不确定函数有RAND()、UUID()等
  • 主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和
内存的使用
  • 主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类
型可以有效的减少索* 引的磁盘空间，提高索引的缓存效率
  • 无主键的表删除，在row模式的主从架构，会导致备库夯住
  • 选择性：select COUNT(DISTINCT LEFT(id_address,3))/COUNT(*) ; 越接近1查询性能越好。

资料

https://mp.weixin.qq.com/s/X9_nQ8iPx3ViaMBhZ3H33A