江苏龙网

  1. 主页 > 生活百科 > >

MySQL DBA必读：万字归总表设计与SQL编写技巧( 三 )

MySQL DBA


缺少主键：无论对于主从同步还是查询性能，主键发挥的作用都非常重要；建议所有业务表都添加主键 。
【MySQL DBA必读：万字归总表设计与SQL编写技巧】5、SQL语句
多表关联：多表关联容易造成关联数据过大，影响查询效率；建议查询中的关联表数量不超过2个 。
全表扫描：触发全表扫描容易造成大量IO读写，严重降低查询效率；建议在查询条件中加入带索引的过滤条件 。
根据现网环境优化执行的难易度，在优化顺序可以按照：SQL语句->数据库表设计->数据库参数配置->数据库存储引擎->服务器硬件 。
下文我们重点论述上面第四、第五点，通过编写高效的SQL语句，并以合适的方式创建表和索引，使系统始终保持良好的性能 。
表设计建议
以合适的方式建立表，可以提高数据库运行效率，有效降低历史数据清理时的维护工作难度 。
1、选定存储引擎
MySQL支持多种存储引擎，在处理不同类型的应用时，可以通过选择使用不同的存储引擎提高应用的效率，或者提供灵活的存储 。MySQL的存储引擎包括：MyISAM、 InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等 。下面是几种常用的存储引擎的对比和推荐使用方式 。

MySQL DBA必读：万字归总表设计与SQL编写技巧

文章插图
其中，InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全 。其设计目的主要面向在线事务处理（OLTP）及应用 。但是对比 Myisam的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引 。从MySQL5.5版本开始，InnoDB存储引擎是默认的存储引擎 。Myisam存储引擎不支持事务，表锁设计，支持群文索引，主要面向一些OLAP数据库应用及Web应用 。每个MyISAM在磁盘上存储成三个文件 。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD (MYData，存储数据)、.MYI (MYIndex，存储索引) 。数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度 。在移动云生产环境中我们建议所有业务表必须是innodb表 。
2、表命名规范
1）命名大小写规范：在 MySQL 中，数据库对应数据目录中的目录 。数据库中的每个表至少对应数据库目录中的一个文件(也可能是多个，取决于存储引擎) 。因此，所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性 。这说明在大多数 Unix 中数据库名和表名对大小写敏感，而在 windows 中对大小写不敏感 。MySQL有配置参数lower_case_table_names，不可动态更改，linux系统默认为 0，即库表名以实际情况存储，大小写敏感 。如果是1，以小写存储，大小写不敏感 。如果是2，以实际情况存储，但以小写比较 。MySQL5.6默认为0 。若大小写混合使用，易导致使用及管理混乱，且字段名显式区分大小写，但实际使用不区分，即不可以建立两个名字一样但大小写不一样的字段 。因此，建议为了统一规范, 库名、表名、字段名使用小写字母，连接统一用下划线‘_’ 。
2）命名字符长度规范：库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符 。
3）避免使用MySQL保留字：当库名、表名、字段名等属性含有保留字时，SQL语句必须用反引号引用属性名称，这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂 。
3、建立常规表
MySQL常规表对应到文件系统上单个数据文件 。在MySQL5.6中建表时，不指定任何参数，默认会建立存储引擎为innodb的常规表 。常规表使用与大部分应用场景 。默认情况下，由于部分操作系统对文件大小的限制，表大小限制为2G 。
4、建立分区表
MySQL从5.1版本开始支持分区表，从5.6开始MySQL表分区以单个数据文件形式存储于文件系统中，根据所使用的不同分区规则可以分成几大类型：
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区 。比较常用如按照时间字段划分分区，2019年1月的数据放到201901分区，2019年2月的数据放到201902分区以此类推 。范围分区方式适用于应用中频繁对分区键值进行范围查询的场合 。另外针对部监控表随时间不断累积数据，大量的历史数据积压，一方面会降低应用程序的效率，另一方面亦浪费大量的存储空间 。因此需要对历史表进行定期清理，以基本保持当前总数据量 。基于这个原则，建议对所有历史表按清理时间键值进行范围分区，时间范围建议按月进行 。表分区的命名采用以下的规范：<表名>_pYYYYMMDD，其中YYYY为分区数据的年份，MM为分区数据的月份，DD为分区数据的日期 。


推荐阅读


上一篇：利用树莓派搭建个人服务器

下一篇：上海检验检疫局保障进出口茶叶质量安全综述