上篇文章介绍了如何创建合适的MySQL索引，今天再一块学一下如何更规范、更合理的使用MySQL？
合理规范的使用MySQL，可以大大减少开发工作量和线上问题，并提升SQL查询性能。
我精心总结了这16条MySQL规约，分享给大家，欢迎评论指正。
1. 禁止使用select *
在阿里开发规范中，有一句话：
[En]
In the Ali development specification, there is a sentence:
select * 会查询表中所有字段，如果表中的字段有更改，必须修改SQL语句，不然就会执行错误。
查询出非必要的字段，徒增磁盘IO和网络延迟。
2. 用小表驱动大表
在关联查询时，首先使用小表查找结果，然后使用结果查询大表，这可以大大减少连接数量。
[En]
When associating a query, first use a small table to find the results, and then use the results to query a large table, which can greatly reduce the number of connections.
例如，我们需要查询某个部门下的员工，因为部门的数量远远少于员工的数量。我们可以将部门表视为驱动表，将雇员表视为驱动表。
[En]
For example, we need to inquire about the employees under a certain department, because the number of departments is far less than the number of employees. We can think of the department table as the driving table and the employee table as the driven table.
查询SQL类似这样：
select * from department
inner join employee
on department.id=employee.department_id
where department_name='部门1';
3. join关联表不宜过多
join关联表禁止超过3张，join关联过多，不但会增加查询时间，降低查询性能，还会产生临时表缓存结果数据，推荐拆成多条小SQL执行。
此外，关联字段的类型必须一致，并且必须在每个表中建立关联字段的索引。
[En]
In addition, the types of associated fields must be consistent, and an index of the associated fields must be established in each table.
4. 禁止使用左模糊或者全模糊查询
当我们在SQL查询使用左模糊或者全模糊匹配的时候，类似下面这样：
左模糊查询
select * from user where name='%一灯';
全模糊查询
select * from user where name='%一灯%';
根据B+树的特性，即使我们在name字段上建立了索引，查询的时候也是无法用到索引的。
5. 索引访问类型至少达到range级别
索引访问类型在这些级别上很常见，性能从上到下从好到差。
[En]
Index access types are common at these levels, with performance from good to poor from top to bottom.
要求SQL索引访问类型至少要达到 range级别，最好到 const级别。
6. 更优雅的使用联合索引
由于联邦索引具有最左侧的匹配原则，因此需要优先考虑最左侧第一列中的高度差异化的字段。
[En]
Because the federated index has the leftmost matching principle, it is necessary to give priority to the highly differentiated fields in the leftmost first column.
例如，要计算用户表中生日字段和性别字段之间的区别，可以进行如下统计：
[En]
For example, to count the distinction between birthday fields and gender fields in the user table, you can make statistics like this:
select
count(distinct birthday)/count(*),
count(distinct gender)/count(*)
from user;
值越大，区分度越高。
出道面试题，下面这条SQL该怎么创建联合索引：
select a from table_name where b=1 order by c;
SQL中用到abc三个字段，创建联合索引的顺序是 （b,c,a）。
这道题还涉及到另一个知识点，SQL执行的顺序：
from > on > join > where > group by > having > select > distinct > order by > limit
7. 注意避免深分页
MySQL深分页的时候，查询性能较差。
select * from user where name='一灯' limit 10000,10;
我们可以使用子查询的形式进行优化：
[En]
We can optimize in the form of subqueries:
select * from user
where id in (
select id from user
where name='一灯'
limit 10000,10
);
这减少了非聚集索引返回的表查询数。
[En]
This reduces the number of table queries returned by non-clustered indexes.
8. 单表字段不要超过30个
当单个表中的字段太多时，加载大量数据也会降低查询性能。
[En]
When there are too many fields in a single table, loading a large amount of data will also slow down query performance.
如果字段超过30个，不用看，肯定是表设计的不合理。
此时，可以分成多个表，垂直分隔热场和冷场。
[En]
At this time, it can be divided into multiple tables and separate hot and cold fields in a vertical way.
9. 枚举字段不要使用字符类型
字符类型会占用更多的存储空间，当我们想要存储枚举值或者表示是否的时候，可以采用 tinyint数值类型，最好采用无符号整数 unsigned tinyint。
10. 小数类型禁止使用float和double
在存储和计算的时候， float 和 double 都存在精度损失的问题，无法得到正确的结果。
所以在涉及到存储小数的时候，必须使用 decimal类型。
11. 所有字段必须设置默认值且不允许为null
字段允许为null，会占用额外的存储空间。
索引并不会索引null值，所以查询null值的时候无法用到索引。
当数值类型允许为null，返回给映射实体类的时候还可能会报空指针异常。
12. 必须创建主键，最好是有序数值类型
如果我们自己没有给表设置主键，InnoDB会自动增加一列隐藏的主键，我们无法使用到，并且也占用的更多的存储空间，所以建表的时候，必须设置主键。
有序数值更适合做主键，插入数据的时候，由于是有序的，不会频繁调整B+树结构，性能更好。
13. 快速判断是否存在某条记录
一般我们判断表中是否存在某条记录的时候，会使用count函数，然后判断返回值是否大于1。
select count(*) from user where name='一灯';
InnoDB存储引擎并没有像MyIsAm那样缓存表的总行数，每次查询都是实时计算的，耗时较长。
我们可以采用limit加快查询效率：
select id from user where name='一灯' limit 1;
limit 1表示匹配到一条就返回，查询效率更好，结果集只返回id，还可以用到覆盖索引。
14. in条件中数量不宜过多
in条件中数量不要超过1000个，不然耗时会非常长，可以拆成多批次查询。
15. 禁止创建预留字段
无法通过保留字段的名称来确定该字段的用途。
[En]
There is no way to determine what the field is for by the name of the reserved field.
保留字段的类型不一定合适。
[En]
The type of reserved field is not necessarily appropriate.
无法为保留字段创建适当的索引。
[En]
Unable to create an appropriate index for the reserved field.
16. 单表索引数不要超过5个
创建适当的索引可以提高查询效率，但是过多的索引，不但占用更多存储空间，还会拖慢更新SQL的性能。
因此，该指数使用方便，且适中。
[En]
Therefore, the index is easy to use and moderate.
知识点总结：
文章持续更新，可以微信搜一搜「 一灯架构 」第一时间阅读更多技术干货。
Original: https://www.cnblogs.com/yidengjiagou/p/16545435.html
Author: 一灯架构
Title: 精心整理16条MySQL使用规范，减少80%问题，推荐分享给团队
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505105/
转载文章受原作者版权保护。转载请注明原作者出处！