Mysql良好使用习惯和SQL优化思路

---

前言

后端程序员和数据库一直在打交道，曾经有一位面试官告诉我，你不必面面俱到，但一定要有一个领域是你非常擅长的，要做到这块没有你解决不了的问题，比方你可以把 Mysql 这块吃透。可见数据库是非常重要的，虽然每个后端开发天天都使用到它，但想成为一个真正的mysql大神，路且长。今天复习下 Mysql 的良好使用习惯以及优化的思路，加深记忆。前三章来自《阿里开发规范》,第四章来自参考《掌握这12个SQL优化方法，你基本上就无敌了》。

---

一、 DML良好习惯

1. 表达是与否概念的字段，使用 is_xxx 的方法命名，数据类型为 unsigned tinyint (1 : 是，0 : 否）。
2. 表名、字段名必须使用小写字母或者数字，两个下划线中间不能只有数字。
3. 表名命名使用单数，用于展示表里的实体对象内容，而不使用复数，具有数量含义。
4. 索引命名规范，主键索引 pk_字段名，唯一索引 uk_字段名，普通索引 idx_字段名。
5. 小数类型使用decimal，不能使用 float,double，会出现精度丢失问题。如果存储数据范围超过decimal的范围，建议将数据拆分成整数和小数部分。
6. 如果存储的字符串长度几乎一致，使用char定长字符串类型。
7. varchar是可变长字符串，长度不可超过 5000，超过5000用text，并独立到其他表，用主键对应，避免影响其他字段索引效率。
8. 库名与应用名尽量一致。
9. 表命名最好是“业务名_表作用"。
10. 字段允许冗余，以提高查询查询性能，但必须保持数据一致性。冗余字段应当不是频繁修改或较长字段(超长varchar或者text)。
11. 单表行数500万行或者单表容量超过2G，才考虑分库分表。

二、索引良好习惯

1. 业务上具有唯一性的字段，即使是组合索引，也必须建成唯一索引。一是能明显提高查找速度，而是避免脏数据。
2. join字段，类型保持一致；多表关联，被关联的字段需要索引。
3. 在varchar字段上建立索引时，必须指定索引长度。加入一段索引长度的介绍：

索引长度通常指的是数据库中索引的长度，它表示在数据库中对某一列或多列创建索引时，索引所占用的存储空间的大小。在数据库中，索引的长度是一个关键的因素，它会影响查询性能、索引的创建速度以及存储占用等方面。

在关系型数据库中，一些常见的索引类型包括B树索引、B+树索引等。在MySQL等数据库中，可以通过设置索引的长度来控制索引的大小。索引长度的设置通常取决于以下几个因素：

列的数据类型： 不同数据类型的列占用的存储空间不同，例如，整数类型占用的空间相对较小，而字符串类型占用的空间则根据字符的个数而变化。
数据的分布情况：
如果索引列的值分布较为均匀，可以考虑设置较小的索引长度，以减小索引的大小。反之，如果数据分布不均匀，可能需要设置较大的索引长度以确保索引的效果。
查询需求： 索引的长度也与查询的需求有关。一些特殊的查询可能需要更长的索引，以确保索引可以满足特定的查询条件。

在MySQL中，可以使用以下语法来设置索引长度：
sql CREATE INDEX index_name ON table_name (column_name(length));
其中，column_name(length)表示对表中的某一列创建索引，并指定索引的长度。这样的设置可以用于优化特定场景下的查询性能。

4. 如果有 order by 的场景，请注意利用索引的有序性。order by最后的字段是组合索引的一部分，并且放在索引组合顺序的最后。避免出现file_sort的秦光，影响查询性能。
   正例：where a=? and b=? order by c; 索引：a_b_c
   反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b 无法排序。

在数据库的上下文中，当查询中的排序操作无法利用索引时，可能会出现 “filesort” 的问题。Filesort 是 MySQL 数据库中的一种排序算法，它可能在执行查询时涉及到对文件进行排序。当 MySQL 无法使用索引直接满足 ORDER BY 子句的排序需求时，就会执行这种外部排序。

5. 合理利用覆盖索引进行查询插座，避免回表。
   覆盖索引是指一个查询可以通过直接使用索引来满足，而无需访问实际的数据行。这样的查询可以从索引本身获取所需的所有信息，而不必进一步检索表中的数据行。

对于包含所有查询需要的列的索引，我们可以说该索引是一个覆盖索引。这种情况对于性能优化非常有利，因为避免了对实际数据行的额外访问，减少了磁盘
I/O 操作和内存开销。
覆盖索引的好处包括：

减少 I/O 操作： 由于不需要访问实际的数据行，查询可以直接从索引中获取所需的信息，从而减少了磁盘 I/O 操作。 减少内存开销：
查询所需的数据可以直接从索引中读取，而无需将整个数据行加载到内存中，因此减少了内存的使用。 提高查询性能： 通过减少 I/O
操作和内存开销，覆盖索引可以显著提高查询的性能，特别是对于那些需要访问大量数据的查询。
要创建覆盖索引，需要确保索引包含查询中涉及的所有列。例如，如果查询涉及列 A、B、C，并且表上有索引 (A, B,
C)，那么这个索引就是一个覆盖索引。

-- 创建一个表
CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    salary DECIMAL(10, 2)
);

-- 创建一个包含所有查询所需列的索引
CREATE INDEX idx_covering_index ON my_table (name, age, salary);

-- 查询中的覆盖索引可以满足所有需求，无需额外访问实际数据行
EXPLAIN SELECT name, age FROM my_table WHERE salary > 50000;

6. 利用延迟关联或者子查询优化超多分页场景

MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回
N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过
特定阈值的页数进行 SQL 改写。
正例：先快速定位需要获取的 id 段，然后再关联：
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

7. SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts
   最好。
8. 建组合索引的时候，区分度最高的在最左边

说明：
1）consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2）ref 指的是使用普通的索引（normal index）。
3）range 对索引进行范围检索。
反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级
别比较 range 还低，与全表扫描是小巫见大巫。

9. 建组合索引的时候，区分度最高的在最左边

正例：如果 where a=? and b=? ，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即
可。
说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where a>?
and b=? 那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。

10. 防止因字段类型不同造成的隐式转换，导致索引失效

三、SQL语句使用

1. 不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

count(*) 会统计NULL值，count(列名)不会。

2. count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
3. 当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为NULL，因此使用 sum()时需注意 NPE 问题。

正例：可以使用如下方式来避免 sum 的 NPE 问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g))
FROM table

4. 不使用resultClass当返回参数，将返回参数与DO类区分解耦，方便维护
5. ${} 容易出现sql注入，禁止使用
6. iBATIS 自带的 queryForList(String statementName,int start,int size)不推荐使用

说明：其实现方式是在数据库取到statementName对应的SQL语句的所有记录，再通过subList
取 start,size 的子集合。
正例：Map<String, Object> map = new HashMap<String, Object>();
map.put(“start”, start);
map.put(“size”, size);

7. 不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出

说明：resultClass=”Hashtable”，会置入字段名和属性值，但是值的类型不可控。

8. 不要写一个大而全的数据更新接口，易出错；效率低；增加log存储
9. @Transactional 事务不要滥用。事务会影响数据库的 QPS，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。

四、SQL优化

1 缓存：sql优先在缓存中查询，如果查到了则直接返回，缓存查不到，再去数据库查询。
缓存必须sql语句完全相同才会命中
表中数据变化，缓存移除
2 分析器的工作主要是对要执行的SQL语句进行词法解析、语法解析，最终得到抽象语法树，然后再使用预处理器对抽象语法树进行语义校验，判断抽象语法树中的表是否存在，如果存在的话，在接着判断select投影列字段是否在表中存在等。
3 主要将SQL经过词法解析、语法解析后得到的语法树，通过数据字典和统计信息的内容，再经过一系列运算 ，最终得出一个执行计划，包括选择使用哪个索引。
4 根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据，完成SQL的执行。

1. 尽量避免字查询

SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name = ‘chackca’);
其字查询在Mysql5.5版本中，内部会先查询外表，再匹配内表。故当外表数据很大时，查询速度很慢。在Mysql5.6版本中，采用join关联方式对这个sql进行优化，逻辑
处理逻辑等同于如下sql
SELECT t1.* FROM t1 JOIN t2 on t1.id = t2.id
但优化只针对select语句，对upate/delete 子查询不生效。join时Mysql不需要在内存中创建临时表，较子查询效率更高。

2. in 代替 or，between 代替 in

Mysql 对 in做了优化，in 的数组最好是排序好的。但如果in的数组长度较大，效率也会慢。in 连续的数值时，可以用between 代替in。

3. 合理使用 limit 语句

对于 limit m, n 的分页查询，越往后面翻页（即m越大的情况下）SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。因为MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

优化的方法如下：可以取前一页的最大行数的id（将上次遍历到的最末尾的数据ID传给数据库，然后直接定位到该ID处，再往后面遍历数据），然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：
select id,name from table_name where id> 866612 limit 20

4. 禁止不必要的Order By排序

对结果没有排序要求，尽量不用排序
排序字段没有使用索引，尽量不用排序
分组统计查询时可以禁止默认排序
默认情况下，Mysql会对所有的GROUP BT col1,col2…的字段进行排序，也就是说上述会对 goods_id进行排序，如果想要避免排序结果的消耗，可以指定ORDER BY NULL禁止排序：

5. 总和查询可以禁止排重用union all

当union all 的前后两部分确认没有重复数据，可以用union all 提高效率

6. 避免随机取记录

SELECT * FROM t1 WHERE 1 = 1 ORDER BY RAND() LIMIT 4;
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;
这两个语句无法使用索引

7. 多次插入替换成批量插入
8. 只返回必要的列，而不是select *
9. 区分in和exists

select * from 表A where id in (select id from 表B)
相当于
select * from 表A where exists(select * from 表B where 表B.id=表A.id)
区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
另外，in查询在某些情况下有可能会查询返回错误的结果，因此，通常是建议在确定且有限的集合时，可以使用in。如 IN （0，1，2）

10. 优化Group By语句

如果对group by语句的结果没有排序要求，要在语句后面加 order by null（group 默认会排序）；
尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；
如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法（直接用磁盘临时表）得到group by的结果。
使用where子句替换Having子句：避免使用having子句，having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过where子句提前过滤查询的数目，就可以减少这方面的开销。
低效
SELECT JOB, AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’
高效
SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ GROUP by JOB

11. 尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能。引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

12. 优化 join 语句

首先先区分什么是驱动表，什么是被驱动表
1）当连接查询没有where条件时
left join 前面的表是驱动表，后面的表是被驱动表
right join 后面的表是驱动表，前面的表是被驱动表
inner join / join 会自动选择表数据比较少的作为驱动表
straight_join(≈join) 直接选择左边的表作为驱动表（语义上与join类似，但去除了join自动选择小表作为驱动表的特性）
2) 当连接查询有where条件时，带where条件的表是驱动表，否则是被驱动表
假设有表如右边：t1与t2表完全一样，a字段有索引，b无索引，t1有100条数据，t2有1000条数据
若被驱动表有索引，那么其执行算法为：Index Nested-Loop Join（NLJ），示例如下：
①执行语句：select * from t1 straight_join t2 on (t1.a=t2.a)；由于被驱动表t2.a是有索引的，其执行逻辑如下：

从表t1中读入一行数据 R
从数据行R中，取出a字段到表t2里去查找
取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分
重复执行步骤1到3，直到表t1的末尾循环结束

如果一条join语句的Extra字段什么都没写的话，就表示使用的是NLJ算法

若被驱动表无索引，那么其执行算法为：Block Nested-Loop Join（BLJ）（Block 块，每次都会取一块数据到内存以减少I/O的开销），示例如下：
②执行语句：select * from t1 straight_join t2 on (t1.a=t2.b)；由于被驱动表t2.b是没有索引的，其执行逻辑如下：
把驱动表t1的数据读入线程内存join_buffer（无序数组）中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；
顺序遍历表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

③另外还有一种算法为Simple Nested-Loop Join（SLJ），其逻辑为：顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分返回。
另外，Innodb会为每个数据表分配一个存储在磁盘的 表名.ibd 文件，若关联的表过多，将会导致查询的时候磁盘的磁头移动次数过多，从而影响性能
所以实践中，尽可能减少Join语句中的NestedLoop的循环次数：“永远用小结果集驱动大的结果集”
a. 用小结果集驱动大结果集，将筛选结果小的表（在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”）首先连接，再去连接结果集比较大的表，尽量减少join语句中的Nested Loop的循环总次数
b. 优先优化Nested Loop的内层循环（也就是最外层的Join连接），因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；
c. 对被驱动表的join字段上建立索引
d. 当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size
e. 尽量用inner join(因为其会自动选择小表去驱动大表).避免 LEFT JOIN (一般我们使用Left Join的场景是大表驱动小表)和NULL，那么如何优化Left Join呢？
条件中尽量能够过滤一些行将驱动表变得小一点，用小表去驱动大表
右表的条件列一定要加上索引（主键、唯一索引、前缀索引等），最好能够使type达到range及以上（ref,eq_ref,const,system）
适当地在表里面添加冗余信息来减少join的次数
Tips：Join左连接在右边建立索引；组合索引则尽量将数据量大的放在左边，在左边建立索引。

在 MySQL 的执行计划（EXPLAIN 输出）中，“ref”、“eq_ref”、“const” 和 “system”
是表示连接类型（Join Type）的标识。它们描述了 MySQL 在执行查询时使用的连接算法和访问方式。以下是它们的含义：

ref（普通索引查找）： 含义： “ref”
表示非唯一性索引的查找，通常是通过普通索引（非唯一索引）进行查找。这种类型的连接通常会导致在索引树中进行范围查找，找到匹配条件的行。
eq_ref（唯一性索引查找）： 含义： “eq_ref”
表示连接是通过唯一性索引（如主键或唯一索引）进行查找。这种类型的连接通常发生在连接条件是唯一性索引的全部部分时。 const（常量连接）：
含义： “const” 表示 MySQL 在查询中使用了常量连接，这通常发生在对常量进行匹配时。这种连接类型是最优化的，因为它表示
MySQL 只需访问一行数据即可满足查询条件。 system（系统表连接）： 含义： “system” 表示 MySQL
使用了系统表连接。这种连接类型通常发生在查询涉及到系统表，例如，使用 information_schema 数据库的查询。