Mysql开发规范

置顶 已于 2024-08-15 10:51:15 修改
阅读量730 收藏 2
点赞数 1
分类专栏： 数据库 文章标签： mysql
于 2021-11-18 15:09:44 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/amunamuna/article/details/121401136
版权

文章目录

一、规范

数据库对象全局全名规范

1、命名使用具有意义的英文词汇，词汇中间以下划线分隔

2、命名只能使用英文字母、数字、下划线，以英文字母开头

3、避免用MySQL的保留字如：backup、call、group等

4、所有数据库对象使用小写字母，实际上MySQL中是可以设置大小写是否敏感的，为了保证统一性，我们这边规范全部小写表示。

数据库命名规范

1、数据库命名尽量不超过30个字符。

2、数据库命名一般为项目名称+代表库含义的简写，比如IM项目的工作流数据库，可以是 im_flow。

3、数据库创建时必须添加默认字符集和校对规则子句。默认字符集为UTF8（已迁移dumbo的使用utf8mb4）

4、命名应使用小写。

表命名规范：

1、常规表名以t_开头，命名规则即 t + 模块（包含模块含义的简写）+ 表（包含表含义的简写），比如用户模块的教育信息表：t_user_eduinfo。

2、临时表，命名规则：temp前缀+模块+表+日期后缀：temp_user_eduinfo_20210719

3、备份表，命名规则，bak前缀+模块+表+日期后缀：bak_user_eduinfo_20210719

4、同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义

5、多个单词以下划线 _ 分隔

6、常规表表名尽量不超过30个字符，temp表和bak表视情况而定，也尽量简短为宜，命名应使用小写

索引设计规范

1、索引区分度

索引必须创建在索引选择性（区分度）较高的列上，选择性的计算方式为：selecttivity = count(distinct c_name)/count(*) ; 如果区分度结果小于0.2，则不建议在此列上创建索引，否则大概率会拖慢SQL执行

2、遵循最左前缀

对于确定需要组成组合索引的多个字段，设计时建议将选择性高的字段靠前放。使用时，组合索引的首字段，必须在where条件中，且需要按照最左前缀规则去匹配。

3、禁止使用外键，可以在程序级别来约束完整性

4、ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引

5、联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。

6、应需而取策略，查询记录的时候，不要一上来就使用，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。*

7、避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。

8、模糊查询’%value%'会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是’value%'是可以有效利用索引。

**9、尽量的扩展索引，非必要不新建索引。**比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

select 检索的规范性

1、尽量避免使用select *，join语句使用select *可能导致只需要访问索引即可完成的查询需要回表取数。

一种是可能取出很多不需要的数据，对于宽表来说，这是灾难；一种是尽可能避免回表，因为取一些根本不需要的数据而回表导致性能低下，是很不合算。

2、避免使用子查询，可以把子查询优化为join操作

通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：

  • 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能 会受到一定的影响；
  • 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；
  • 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

操作的规范性

1、禁止使用不含字段列表的INSERT语句

如：insert into values (‘a’,‘b’,‘c’); 应使用 insert into t_name(c1,c2,c3) values (‘a’,‘b’,‘c’); 。

2、大批量写操作（UPDATE、DELETE、INSERT），需要分批多次进行操作

  • 大批量操作可能会造成严重的主从延迟，特别是主从模式下，大批量操作可能会造成严重的主从延迟，因为需要slave从master的binlog中读取日志来进行数据同步。
  • binlog日志为row格式时会产生大量的日志

二、字段类型介绍

字段类型长度描述
VARCHAR0-255变长字符串
TINYBLOB0-255不超过255个字符的二进制字符串
TINYTEXT0-255短文本字符串
BLOB0-65535 (64k)二进制形式的长文本数据
TEXT0-65535 (64k)长文本数据
MEDIUMBLOB0-16777215 (16M)二进制形式的中等长度文本数据
MEDIUMTEXT0-16777215(16M)中等长度文本数据
LONGBLOB0-4294967295 (4G)二进制形式的极大文本数据
LONGTEXT0-4294967295(4G)极大文本数据

三、sql优化技巧

避免使用select *

耗费数据库资源，增加网络数据传输时间，select * 不会走覆盖索引，会出现大量的回表操作，从而影响查询sql的性能。
在实际业务场景中，真正需要使用的可能只有一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。
此外，多查出来的数据，在网络IO传输的过程中，也会增加数据传输时间。
select * 不会走覆盖索引，会出现大量的回表操作，从而导致查询sql的性能很低。

用union all代替union

使用union关键字后，可以获取排重后的数据。
而如果使用union all关键字，可以获取所有数据，包含重复的数据。
排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。

小表驱动大表

--in关键字
select * from order
where user_id in (select id from user where status=1)
--exists关键字
select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)

总结一下：
in 适用于左边大表，右边小表。
exists 适用于左边小表，右边大表。

批量操作

建议每批数据尽量控制在500以内。如果数据多于500，则分批进行处理。

多用limit

只需要查询第一条数据

有时候，我们需要查询某些数据中的第一条，比如：查询某个用户下的第一个订单，想看看他第一次的首单时间。
反例：

select id, create_date 
 from order 
where user_id=123 
order by create_date asc;

根据用户id查询订单，按下单时间排序，先查出该用户所有的订单数据，得到一个订单集合。然后在代码中，获取第一个元素的数据，即首单的数据，就能获取首单时间。

List<Order> list = orderMapper.getOrderList();
Order order = list.get(0);

虽说这种做法在功能上没有问题，但它的效率非常不高，需要先查询出所有的数据，有点浪费资源。
那么，如何优化呢？
正例：

select id, create_date 
 from order 
where user_id=123 
order by create_date asc 
limit 1;

使用limit 1，只返回该用户下单时间最小的那一条数据即可。
此外，在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在sql语句最后加上limit。
例如：

update order set status=0,edit_time=now(3) 
where id>=100 and id<200 limit 100;

这样即使误操作，比如把id搞错了，也不会对太多的数据造成影响。

in中值太多

对于批量查询接口，我们通常会使用in关键字过滤出数据。比如：想通过指定的一些id，批量查询出用户信息。
sql语句如下：
select id,name from category
where id in (1,2,3…100000000);
如果我们不做任何限制，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。
这时该怎么办呢？
select id,name from category
where id in (1,2,3…100)
limit 500;
可以在sql中对数据用limit做限制。
不过我们更多的是要在业务代码中加限制，伪代码如下：
public List getCategory(List ids) {
if(CollectionUtils.isEmpty(ids)) {
return null;
}
if(ids.size() > 500) {
throw new BusinessException(“一次最多允许查询500条记录”)
}
return mapper.getCategoryList(ids);
}
还有一个方案就是：如果ids超过500条记录，可以分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起返回。
不过这只是一个临时方案，不适合于ids实在太多的场景。因为ids太多，即使能快速查出数据，但如果返回的数据量太大了，网络传输也是非常消耗性能的，接口性能始终好不到哪里去。

增量查询

有时候，我们需要通过远程接口查询数据，然后同步到另外一个数据库。
反例：
select * from user;
如果直接获取所有的数据，然后同步过去。这样虽说非常方便，但是带来了一个非常大的问题，就是如果数据很多的话，查询性能会非常差。
这时该怎么办呢？
正例：
select * from user
where id>#{lastId} and create_time >= #{lastCreateTime}
limit 100;
按id和时间升序，每次只同步一批数据，这一批数据只有100条记录。每次同步完成之后，保存这100条数据中最大的id和时间，给同步下一批数据的时候用。
通过这种增量查询的方式，能够提升单次查询的效率。

高效的分页

有时候，列表页在查询数据时，为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理。
在mysql中分页一般用的limit关键字：
select id,name,age
from user limit 10,20;
如果表中数据量少，用limit关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题。
比如现在分页参数变成了：
select id,name,age
from user limit 1000000,20;
mysql会查到1000020条数据，然后丢弃前面的1000000条，只查后面的20条数据，这个是非常浪费资源的。
那么，这种海量数据该怎么分页呢？
优化sql：
select id,name,age
from user where id > 1000000 limit 20;
先找到上次分页最大的id，然后利用id上的索引查询。不过该方案，要求id是连续的，并且有序的。
还能使用between优化分页。
select id,name,age
from user where id between 1000000 and 1000020;
需要注意的是between要在唯一索引上分页，不然会出现每页大小不一致的问题。

用连接查询代替子查询

join

join的表不宜过多

根据阿里巴巴开发者手册的规定，join表的数量不应该超过3个。

join时要注意：能用inner join的地方，尽量少用left join

  • left join：求两个表的交集外加左表剩下的数据。
  • inner join：求两个表交集的数据。

如果两张表使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。

如果两张表使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。

要特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。

控制索引的数量

阿里巴巴的开发者手册中规定，单表的索引数量应该尽量控制在5个以内，并且单个索引中的字段数不超过5个。

mysql使用的B+树的结构来保存索引的，在insert、update和delete操作时，需要更新B+树索引。如果索引过多，会消耗很多额外的性能。

高并发系统如何优化索引数量？
能够建联合索引，就别建单个索引，可以删除无用的单个索引。

选择合理的字段类型

char表示固定字符串类型，该类型的字段存储空间的固定的，会浪费存储空间。

varchar表示变长字符串类型，该类型的字段存储空间会根据实际数据的长度调整，不会浪费存储空间。

一般遵循的原则：

  1. 能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
  2. 尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
  3. 长度固定的字符串字段，用char类型。
  4. 长度可变的字符串字段，用varchar类型。
  5. 金额字段用decimal，避免精度丢失问题。

提升group by 的效率

我们有很多业务场景需要使用group by关键字，它主要的功能是去重和分组。
通常它会跟having一起配合使用，表示分组后再根据一定的条件过滤数据。
反例：

select user_id,user_name from order
group by user_id
having user_id <= 200;

这种写法性能不好，它先把所有的订单根据用户id分组之后，再去过滤用户id大于等于200的用户。
分组是一个相对耗时的操作，为什么我们不先缩小数据的范围之后，再分组呢？
正例：

select user_id,user_name from order
where user_id <= 200
group by user_id

使用where条件在分组前，就把多余的数据过滤掉了，这样分组时效率就会更高一些。

索引优化

explain命令，查看mysql的执行计划

重点：
　　type：访问类型，查看SQL到底是以何种类型访问数据的。
　　key：使用的索引，MySQL用了哪个索引，有时候MySQL用的索引不是最好的，需要force index()。
　　rows：最大扫描的列数。
　　extra：重要的额外信息，特别注意损耗性能的两个情况，using filesort和using temporary。
　　
参数含义

id代表执行select子句或操作表的顺序
  • id相同,执行顺序由上至下
  • id不同,如果是子查询,id的序号会递增,id值越大,优先级越高,越先被执行
  • id相同和不同,同时存在,遵从优先级高的优先执行,优先级相同的按照由上至下的顺序执行
type

查询类型从最好到最差依次是:system>const>eq_ref>ref>range>index>All,一般情况下,得至少保证达到range级别,最好能达到ref

  • system:表只有一行记录,这是const类型的特例,平时不会出现
  • const:表示通过索引一次就找到了,const即常量,它用于比较primary key或unique索引,因为只匹配一行数据,所以效率很快,如将主键置于where条件中,mysql就能将该查询转换为一个常量
  • eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描
  • ref:非唯一性索引扫描,返回匹配某个单独值的行,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
  • range:只检索给定范围的行,使用一个索引来选择行,如where语句中出现了between,<,>,in等查询,这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
  • index:index类型只遍历索引树,这通常比All快,因为索引文件通常比数据文件小,index是从索引中读取,all从硬盘中读取
  • all:全表扫描,是最差的一种查询类型
key

实际使用的索引,如果为null,则没有使用索引,因此会出现possible_keys列有可能被用到的索引,但是key列为null,表示实际没用索引。

rows 最大扫描的列数

根据表统计信息及索引选用情况,大只估算出找到所需的记录所需要读取的行数

Extra 重要的额外信息，特别注意损耗性能的两个情况，using filesort和using temporary
  • Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为"文件排序"
  • Using temporary :使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于order by和分组查询group by
  • Using index:表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。 其中的覆盖索引含义是所查询的列是和建立的索引字段和个数是一一对应的
  • Using where:表明使用了where过滤
  • Using join buffer:表明使用了连接缓存,如在查询的时候会有多次join,则可能会产生临时表
    impossible where:表示where子句的值总是false,不能用来获取任何元祖。如下例：
    select * from t1 where id=‘1’ and id=‘2’;
    select tables optimized away
    在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作，即一旦MySQL找到了与行相联合匹配的行，就不再搜索了。

索引失效

索引失效的常见原因：

  • 使用组合索引时，不满足最左前缀原则
  • 范围索引列没有放最后
  • 使用了select *
  • 索引列上有计算
  • 索引列上使用了函数
  • 字符类型没加引号
  • 用is null 和is not null 没注意字段是否允许为空
  • like查询左边有%
  • 使用or关键字时没有注意

有时候mysql会选错索引，可以使用force index来强制查询sql走某个索引

amunamuna
关注
专栏目录
阿里巴巴MySQL开发规范
02-21
### 阿里巴巴MySQL开发规范详解 #### 一、概述 阿里巴巴MySQL开发规范是一套针对MySQL数据库设计、实现及优化的最佳实践指南。这套规范旨在提高数据库应用的稳定性、性能和可维护性，同时降低潜在的风险。规范主要...
select * 会走索引吗？索引覆盖呢？及为何不建议使用它。
ystzy521的博客
09-18 4423
select * 会走索引吗？索引覆盖呢？及为何不建议使用它。
一次由 select * 走了索引而引发的思考
吉衣の秋先生的博客
08-27 1686
select * 走索引
MySQL数据库开发规范总结【建议收藏】
最新发布
专注Java后端技术干货、项目源码总结分享，期待您的关注。
07-16 937
MySQL数据库开发规范总结【建议收藏】
SQL的优化
qq_39879126的博客
11-17 869
1.避免使用 select * 不会走覆盖索引，会出现大量的回表操作,多查出来的数据，通过网络 IO 传输的过程中，也会增加数据传输的时间. 2.用 union all 代替 union union关键字后，可以获取排重后的数据,排重的过程需要遍历、排序和比较，它更耗时，更消耗 cpu 资源。 3.小表驱动大表 in 适用于左边大表，右边小表。 exists 适用于左边小表，右边大表。 4.批量操作 5.用 limit返回需要的数据行 6.避免in 中值太多 7.用连接查询代替子查询，join 的表不宜过多
MYSQL索引
qq_40743080的博客
03-28 1662
索引类型 1、普通索引 普通索引查出出来的是主键值，然后再到主键索引去查找对应的值 聚集索引是索引跟数据放在一起(Innodb)，非聚集索引是数据跟索引分开的(MyIsam) 聚合会比较快 B+树的索引数据结构，也有hash索引的数据结构 B树与B+树最大的区别是 B树在跟和叶子节点都会存储数据 B+树在叶子节点才存储数据 possible_key 可能用到的索引 key 实际用到的索引 设置ID为主键 为什么要自增 尽量减少页合并和...
Select * 一定不走索引是否正确？
weixin_34080903的博客
06-12 881
走索引指的是：SQL语句的运行计划用到了1、聚集索引查找  2、索引查找  ，而且查询语句中须要有where子句 依据where子句的过滤条件。去聚集索引或非聚集索引那里查找记录 一张表仅仅有一列的情况： 聚集索引   USE [tempdb] GO CREATE TABLE t1 ( id INT ) GO CREATE CLUSTERED INDEX CIX...
mysql开发规范
03-27
mysql的开发规范文档，这里可能记录的并不是很全也算是给自己一个提醒
MySQL开发规范
07-23
资源名称：MySQL开发规范资源截图： 资源太大，传百度网盘了，链接在附件中，有需要的同学自取。
mysql 开发规范.docx
11-09
### MySQL开发规范详解 #### 一、概述 MySQL作为一种广泛使用的开源关系型数据库管理系统，在企业级应用中扮演着至关重要的角色。为了确保系统的稳定性和可维护性，制定一套科学合理的MySQL开发规范至关重要。本文...
Mysql 索引失效有哪些场景
菜鸡的博客
11-18 1379
MYSQ索引失效
SQL语句建立索引不走索引的几种情况
热门推荐
Fuzz
09-12 1万+
1、查询谓词没有使用索引的主要边界,换句话说就是select *，可能会导致不走索引。 比如，你查询的是SELECT * FROM T WHERE Y=XXX;假如你的T表上有一个包含Y值的组合索引，但是优化器会认为需要一行行的扫描会更有效，这个时候，优化器可能会选择TABLE ACCESS FULL，但是如果换成了SELECT Y FROM T WHERE Y = XXX，优化器会直接去索引中...
【MySQL性能优化系列】select count(*)走二级索引比主键索引快几百倍，你敢信？
7me
11-03 7002
在MySQL版本5.7数据测试过程中，一张百万数据的表用 select count(*)查询特别慢需要20s并且是走了主键索引，为什么查询还需要这么久？如何优化？下面我们将请到当事SQL进行发言
MySQL在有索引列情况下 和 无索引情况下 select *的输出结果顺序
大树叶 技术专栏
05-07 3083
创建一个表格，一个是主键列，一个是索引列。然后插入一批数据，调用select * from test_b，可以发现输出结果并没有按照Id有序，而是按照Type有序。 如果希望按照Id有序，可以使用force index (primary)这一hint语句。 mysql> CREATE TABLE `test_b` ( -> `Id` int(11) NOT NULL,
为什么不建议使用 select(*)
C_x_330的博客
10-21 1103
为什么不建议使用 select(*)
MySQL数据库索引
m0_54065725的博客
03-13 469
提到数据库索引，很多人应该都不陌生，查询SQL慢的时候，一般的做法就是加索引。索引的出现其实就是为了提高数据查询的效率，就像书的目录一样，让我们可以很快的定位到具体哪一页，而不是一页一页的查找。 索引模型 常见的索引模型有哈希表、有序数组、B-Tree、B+Tree。 哈希表 跟数据结构讲的HashMap差不多，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。当有多个值哈希之后的值相等时，数组上的值会形成链表。 有个缺点就是因为链表不是有序的，所以哈希
技术分享 | 为什么 SELECT 查询选择全表扫描，而不走索引？
ActionTech的博客
10-12 1645
作者：Charizard 爱可生服务团队成员，主要负责公司数据库运维产品问题诊断；努力在数据库和 IT 领域里摸爬滚打中。 本文来源：原创投稿 *爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。 1、基本结论 SQL的执行成本（cost）是 MySQL 优化器选择 SQL 执行计划时一个重要考量因素。当优化器认为使用索引的成本高于全表扫描的时候，优化器将会选择全表扫描，而不是使用索引。 下面通过一个实验来说明。 2、问题现象 如下结构的一张表，表中约有104w行数据： CR.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值