MySQL 数据库规范--开发篇

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 1.sql语句编写  1.执行大的delete、update、insert操作要慎重,特别是对业务繁忙的系统,要尽量避免对线上业务产生影响。 解决办法是:大操作切割为小操作,使用limit子句限制每次操作的记录数,也可以利用一些日期字段基于更小粒度的时间范围进行操作。  2.避免使用select * 语句,select语句之用于获取需要的字段。  3.使用预编译语句,可以提高性能

1.sql语句编写

 1.执行大的delete、update、insert操作要慎重,特别是对业务繁忙的系统,要尽量避免对线上业务产生影响。 解决办法是:大操作切割为小操作,使用limit子句限制每次操作的记录数,也可以利用一些日期字段基于更小粒度的时间范围进行操作。

 2.避免使用select * 语句,select语句之用于获取需要的字段。

 3.使用预编译语句,可以提高性能并且防范 sql注入 攻击。

 4.一般情况下update,delete 语句中不要使用limit。

 5.where 条件语句中必须使用合适的类型,避免mysql进行隐式转换。

 6.insert into 必须显式指明字段名称,不要使用insert into table()。 

 7.避免在sql 语句中进行数学运算或函数运算,避免将业务逻辑和数据存储耦合在一起。 

 8.insert 语句如果使用批量提交,如insert into table values(),()...那么values 的个数不应过多。一次性提交过多记录,会导致I/O紧张,出现慢查询。

 9.避免使用存储过程、触发器、函数等,这些特性会将业务逻辑与数据库耦合在一起,并且MySQL的存储过程,触发器,函数中可能存在bug。

 10.尽量避免使用子查询,连接。尽量将子查询转化为连接查询,mysql 查询优化器会优化连接查询,但连接的表要尽可能的少,如果很多,可以考虑反范式设计。即对设计阶段做一些改造。 

 11.使用合理的sql语句以减少与数据库的交互次数。 

 12.建议使用合理的分页技术以提高操作效率。


2.explain 工具的使用

1.使用 explain 工具可以确认执行计划是否良好,查询是否走了合理的索引。 

2.不同版本MySQL 优化器各有不同,一些优化规则随着版本的发展可能有变化, 查询的执行计划随着数据的变化也可能发生变化,这类情况就需要使用explain 来验证自己的判断。
 

explain 工具实操

执行如下脚本,观察控制台输出

explain select name from test where id = 32;

注意数据表使用如下脚本:
 
CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;
 


table name = test、column1 = id、column2 = name.

执行结果如下所示:

 


下面详细阐述explain 输出的各项内容:

id:  包含一组数字,表示查询中执行 select子句 或操作表的顺序。如果 id 相同,则执行顺序由上到下。


select_type:

表示查询中每个 select 子句的类型(是简单还是复杂)输出结果类似如下:
1.simple
查询中不包含子查询或者union
2.primary
查询中若包含任何复杂子查询,最外层查询被标记为primary
3.subquery
在select 或 where 列表中包含了子查询,则该查询被标记为subquery
4.derived
在from列表中包含的子查询被标记为derived(衍生)
5.union
若第二个select出现在union之后,则被标记为derived。
6.union result
从union表中获取结果的select将被标记为 union result。
select_type 只需要了解分类即可,这个信息并不是最有价值的。

type:最有价值信息之一

type表示 MySQL 在表中找到所需行的方式,又称为“访问类型”,常见的类型如下所示:
all、index、range、ref、eq_ref、const,system,null
以上类型,由左至右,由最差到最好。
all: Full Table Scan,MySQL 将遍历全表以找到匹配的行。
index:Full Index Scan,index 与 all 区别为index类型只遍历索引树。假设表中有主键字段id,则select id from table_name;type即为Full Index Scan。
range:索引扫描范围,对索引的扫描开始于某一点,返回匹配的域或行,常见于between、<、>等的查询。
ref:非唯一性索引扫描,将返回匹配某个单独值得所有行。常见于使用非唯一索引或唯一索引的非唯一前缀的查找。
eq_ref:唯一性索引扫描,对于每个索引键表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
const、system:当MySQL对查询的某部分进行优化,并转化为一个常量时,可使用这些类型进行访问。如果主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const
的一个特例,当查询的表只有一行的情况下,即可使用system。
null:MySQL 在优化过程中分解语句,执行时甚至不用访问表或索引,举例如下:
explain select from (select from t1 where id = 1)d1;

possible_keys

possible_keys 将指出MySQL能使用哪个索引在表中找到行,查询涉及的字段上若存在索引,则该索引将被列出,但不一定会被查询使用。

key:最有价值信息之二

key 将显示MySQL在查询中实际使用到的索引,若没有使用索引,则显示为null。查询中若使用到了覆盖索引,则该索引仅仅出现在 key 列表中,possible_keys中并不显示。

key_len

key_len表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。

ref

ref表示上述表的连接匹配条件,即哪些列或常亮被用于查找索引列上的值。

rows:最有价值信息之三

rows 表示MySQL根据表统计信息及索引选用的情况, 估算查找所需记录需要读取的行数。使用到索引一般情况下会使得rows的值降低。

Extra:最有价值信息之四

Extra 包含不适合在其他列中显示但十分重要的额外信息。可能包如下4种信息。
1.Using index
该值表示相应的select操作中使用到了覆盖索引,包含满足查询需要的数据的索引称为覆盖索引。
2.Using where
如果查询未能使用索引,则Using where 的作用只是提醒我们 MySQL 将用where 子句来过滤结果集。
3.Using temporary
表示MySQL需要使用临时表来存储结果集,常见于order by 与 group by,事实上group by会进行隐式的order by。
如果我们在group by 时利用索引分组(其实包含排序的过程)排序,则可以提高性能,因为不会此时查询输出里没有了Using temporary,Using filesort。

4.Using filesort
Using filesort 即文件排序,MySQL 中将无法使用索引完成的排序操作,称为文件排序。

上文篇幅有点长,但都是必须了解的概念。最有价值信息是我们判断sql语句执行是否高效的基准,了解四个最有价值信息是最重要的。

继续演示explain的使用,使用上文的评判标准来看下语句的执行效率:
1.主键查询

主键查询

上述为主键查询的explain信息
type = const 效率很高
key = primary 实际使用的索引为主键
rows = 1 查找的记录数为1
extra = null ,没有任何额外信息
总体来说,性能是极高。

2.主键范围查询
主键范围查询

上述为主键查询的explain信息
type =range 范围查询,效率不是最低
key = primary 实际使用的索引为主键
rows = 7 查找的记录数为7
extra = Using where ,最终使用where 做结果集过滤,未使用到覆盖索引。
总体来说,性能是很高。

3.未带索引查询

未带索引查询

如上图所示,name 并未做索引。
type =ALL Full Table Scan 全表查询
key =NULL 未使用索引
rows = 7 10数据库中所有记录
extra = Using where ,最终使用where 做结果集过滤,未使用到覆盖索引。
总体来说,性能极差。(这也是我司内部deviceId接口出问题的终极原因)。

4.未带索引的分组查询

未带索引的分组查询

如上图所示,name 并未做索引。
type =ALL Full Table Scan 全表查询
key =NULL 未使用索引
rows = 7 10数据库中所有记录
extra = Using where ,最终使用where 做结果集过滤,未使用到覆盖索引。并使用到了temporary,filesort 临时表与文件查询。
总体来说,性能极差。

5.带索引的分组查询

我们现在为4与3中 name 创建索引,再来看看分析结果
创建索引脚本如下
alter table test add index idx_name(name);
name
再运行3 和 4中的查询语句
结果如下图所示:
非分组带索引查询
type =ref 非唯一索引扫描,效率不是最低
key =name 实际使用的索引name索引(注意:idx_name与name都是在name字段上建立的索引)。
rows = 1 查找的记录数为1
extra = Using index ,最终使用到覆盖索引。
总体来说,查询性能是极高的。
索引分组查询
type =range 范围扫描,效率不是最低
key =name 实际使用的索引name索引(注意:idx_name与name都是在name字段上建立的索引)。
rows = 1 查找的记录数为1
extra = Using index ,最终使用where 做结果集过滤,使用到覆盖索引。
总体来说,查询性能是极高的。

当然上述演示比较简单,也不是非常具备实战色彩,对于explain的使用,我们还应在更多的数据库操作场景中多多使用,这是sql调优的利器。为我们后期的调优减轻了负担,可以说如果在这一步做好了sql脚本的设计,那么后期关于 sql调优 问题会非常少。


作者: mark_rock 
链接:http://www.imooc.com/article/details/id/17287
来源:慕课网
 






相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
18天前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
20天前
|
存储 SQL 关系型数据库
使用MySQL Workbench进行数据库备份
【9月更文挑战第13天】以下是使用MySQL Workbench进行数据库备份的步骤:启动软件后,通过“Database”菜单中的“管理连接”选项配置并选择要备份的数据库。随后,选择“数据导出”,确认导出的数据库及格式(推荐SQL格式),设置存储路径,点击“开始导出”。完成后,可在指定路径找到备份文件,建议定期备份并存储于安全位置。
160 11
|
16天前
|
存储 SQL 关系型数据库
MySQL的安装&数据库的简单操作
本文介绍了数据库的基本概念及MySQL的安装配置。首先解释了数据库、数据库管理系统和SQL的概念,接着详细描述了MySQL的安装步骤及其全局配置文件my.ini的调整方法。文章还介绍了如何启动MySQL服务,包括配置环境变量和使用命令行的方法。最后,详细说明了数据库的各种操作,如创建、选择和删除数据库的SQL语句,并提供了实际操作示例。
58 13
MySQL的安装&数据库的简单操作
|
21天前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
|
3天前
|
关系型数据库 Unix MySQL
MySQL是一种关系型数据库管理系统
MySQL是一种关系型数据库管理系统
11 2
|
6天前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
17 2
|
11天前
|
SQL 关系型数据库 MySQL
创建包含MySQL和SQLServer数据库所有字段类型的表的方法
创建一个既包含MySQL又包含SQL Server所有字段类型的表是一个复杂的任务,需要仔细地比较和转换数据类型。通过上述方法,可以在两个数据库系统之间建立起相互兼容的数据结构,为数据迁移和同步提供便利。这一过程不仅要考虑数据类型的直接对应,还要注意特定数据类型在不同系统中的表现差异,确保数据的一致性和完整性。
22 4
|
20天前
|
SQL 监控 关系型数据库
MySQL数据库中如何检查一条SQL语句是否被回滚
检查MySQL中的SQL语句是否被回滚需要综合使用日志分析、事务状态监控和事务控制语句。理解和应用这些工具和命令,可以有效地管理和验证数据库事务的执行情况,确保数据的一致性和系统的稳定性。此外,熟悉事务的ACID属性和正确设置事务隔离级别对于预防数据问题和解决事务冲突同样重要。
30 2
|
23天前
|
存储 缓存 关系型数据库
MySQL 视图:数据库中的灵活利器
视图是数据库中的虚拟表,由一个或多个表的数据经筛选、聚合等操作生成。它不实际存储数据,而是动态从基础表中获取。视图可简化数据访问、增强安全性、提供数据独立性、实现可重用性并提高性能,是管理数据库数据的有效工具。
|
23天前
|
SQL 关系型数据库 MySQL
MySQL技术安装配置、数据库与表的设计、数据操作解析
MySQL,作为最流行的关系型数据库管理系统之一,在WEB应用领域中占据着举足轻重的地位。本文将从MySQL的基本概念、安装配置、数据库与表的设计、数据操作解析,并通过具体的代码示例展示如何在实际项目中应用MySQL。
65 0
下一篇
无影云桌面