MySQL的优化原则

        MySQL的优化原则可以从多个层面来进行，包括SQL语句优化、索引优化、数据库结构优化以及服务器配置优化等。下面是一些常见的MySQL优化原则：

优化原则—创建索引

1.【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值 禁止被更新 。

2.【强制】InnoDB和MyISAM存储引擎表，索引类型必须为 BTREE 。

3.【建议】主键的名称以 pk 开头，唯一键以 uni 或 uk 开头，普通索引以 idx 开头，一律使用小写格式，以字段的名称或缩写作为后缀。

4.【建议】多单词组成的columnname，取前几个单词首字母，加末单词组成column_name。如: sample 表 member_id 上的索引：idx_sample_mid。

5.【建议】单个表上的索引个数 不能超过6个 。

6.【建议】在建立索引时，多考虑建立 联合索引 ，并把区分度最高的字段放在最前面。

7.【建议】在多表 JOIN 的SQL里，保证被驱动表的连接列上有索引，这样JOIN 执行效率最高。

8.【建议】建表或加索引时，保证表里互相不存在 冗余索引 。 比如：如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除。

优化原则—SQL编写

1.【强制】程序端SELECT语句必须指定具体字段名称，禁止写成 *。

2.【建议】程序端insert语句指定具体字段名称，不要写成INSERT INTO t1 VALUES(…)。

3.【建议】除静态表或小表（100行以内），DML语句必须有WHERE条件，且使用索引查找。

4.【建议】INSERT INTO…VALUES(XX),(XX),(XX).. 这里XX的值不要超过5000个。 值过多虽然上线很快，但会引起主从同步延迟。

5.【建议】SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。

6.【建议】线上环境，多表 JOIN 不要超过5个表。

7.【建议】减少使用ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做。ORDER BY、GROUP BY、DISTINCT 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

8.【建议】包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果 集请保持在1000行以内，否则SQL会很慢。

9.【建议】对单表的多次alter操作必须合并为一次 对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行，多个alter需整 合在一起。 因为alter table会产生 表锁 ，期间阻塞对于该表的所有写入，对于业务可能会产生极 大影响。

10.【建议】批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep。

11.【建议】事务里包含SQL不超过5个。 因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。

12.【建议】事务里更新语句尽量基于主键或UNIQUE KEY，如UPDATE… WHERE id=XX; 否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。

优化原则—索引失效

1.最佳左前缀法则

2.主键插入顺序

3.计算、函数导致索引失效

4.类型转换导致索引失效

5.范围条件右边的列索引失效

6.不等于(!= 或者<>)索引失效

7.is null可以使用索引，is not null无法使用索引

8.like以通配符%开头索引失效

9.OR 前后存在非索引的列，索引失效

10.数据库和表的字符集统一使用utf8mb4