MySql优化02:MySQL 数据库性能优化之表结构优化
本文可以作为写SQL时的借鉴。
很多人都将数据库设计范式作为数据库表结构设计圣经,认为只要按照这个范式需求设计,就能让设计出来的表结构足够优化,既能保证性能优异同时还能满足扩展性要求。殊不知,在N年前被奉为圣经的数据库设计3范式早就已经不完全适用了。这里我整理了一些比较常见的数据库表结构设计方面的优化技巧,希望对大家有用。
由于MySQL数据库是基于行(Row)存储的数据库,而数据库操作 IO 的时候是以 page(block)的方式,也就是说,如果我们每条记录所占用的空间量减小,就会使每个page中可存放的数据行数增大,那么每次 IO 可访问的行数也就增多了。反过来说,处理相同行数的数据,需要访问的 page 就会减少,也就是 IO 操作次数降低,直接提升性能。此外,由于我们的内存是有限的,增加每个page中存放的数据行数,就等于增加每个内存块的缓存数据量,同时还会提升内存换中数据命中的几率,也就是缓存命中率。
数据类型选择
数据库操作中最为耗时的操作就是 IO 处理,大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量,可以在很大程度上提高数据库操作的性能。
我们无法改变数据库中需要存储的数据,但是我们可以在这些数据的存储方式方面花一些心思。下面的这些关于字段类型的优化建议主要适用于记录条数较多,数据量较大的场景,因为精细化的数据类型设置可能带来维护成本的提高,过度优化也可能会带来其他的问题:
1:数字类型:非万不得已不要使用DOUBLE,不仅仅只是存储长度的问题,同时还会存在精确性的问题。同样,固定精度的小数,也不建议使用DECIMAL,建议乘以固定倍数转换成整数存储,可以大大节省存储空间,且不会带来任何附加维护成本。对于整数的存储,在数据量较大的情况下,建议区分开 TINYINT / INT / BIGINT 的选择,因为三者所占用的存储空间也有很大的差别,能确定不会使用负数的字段,建议添加unsigned定义。当然,如果数据量较小的数据库,也可以不用严格区分三个整数类型。
2:字符类型:非万不得已不要使用 TEXT 数据类型,其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段,建议使用 CHAR 类型,不定长字段尽量使用 VARCHAR,且仅仅设定适当的最大长度,而不是非常随意的给一个很大的最大长度限定,因为不同的长度范围,MySQL也会有不一样的存储处理。
3:时间类型:尽量使用TIMESTAMP类型,因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型,建议使用DATE类型,因为他的存储空间只需要3个字节,比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值,因为这太不直观,会给维护带来不必要的麻烦,同时还不会带来任何好处。
4:ENUM & SET:对于状态字段,可以尝试使用 ENUM 来存放,因为可以极大的降低存储空间,而且即使需要增加新的类型,只要增加于末尾,修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢?可以尝试使用SET类型,即使存在多种属性,同样可以游刃有余,同时还可以节省不小的存储空间。
5:LOB类型:强烈反对在数据库中存放 LOB 类型数据,虽然数据库提供了这样的功能,但这不是他所擅长的,我们更应该让合适的工具做他擅长的事情,才能将其发挥到极致。在数据库中存储 LOB 数据就像让一个多年前在学校学过一点Java的营销专业人员来写 Java 代码一样。
字符编码
字符集直接决定了数据在MySQL中的存储编码方式,由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异,所以通过使用合适的字符集,可以帮助我们尽可能减少数据量,进而减少IO操作次数。
1:纯拉丁字符能表示的内容,没必要选择 latin1 之外的其他字符编码,因为这会节省大量的存储空间
2:如果我们可以确定不需要存放多种语言,就没必要非得使用UTF8或者其他UNICODE字符类型,这回造成大量的存储空间浪费
3:MySQL的数据类型可以精确到字段,所以当我们需要大型数据库中存放多字节数据的时候,可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量,进而降低 IO 操作次数并提高缓存命中率
适当拆分
有些时候,我们可能会希望将一个完整的对象对应于一张数据库表,这对于应用程序开发来说是很有好的,但是有些时候可能会在性能上带来较大的问题。
当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候,如果我们大部分访问这张表的时候都不需要这个字段,我们就该义无反顾的将其拆分到另外的独立表中,以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加,既减少物理 IO 次数,也能大大提高内存中的缓存命中率。
上面几点的优化都是为了减少每条记录的存储空间大小,让每个数据库中能够存储更多的记录条数,以达到减少 IO 操作次数,提高缓存命中率。下面这个优化建议可能很多开发人员都会觉得不太理解,因为这是典型的反范式设计,而且也和上面的几点优化建议的目标相违背。
适度冗余
为什么我们要冗余?这不是增加了每条数据的大小,减少了每个数据块可存放记录条数吗?
确实,这样做是会增大每条记录的大小,降低每条记录中可存放数据的条数,但是在有些场景下我们仍然还是不得不这样做:
被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段
这样的场景由于每次Join仅仅只是为了取得某个小字段的值,Join到的记录又大,会造成大量不必要的 IO,完全可以通过空间换取时间的方式来优化。不过,冗余的同时需要确保数据的一致性不会遭到破坏,确保更新的同时冗余字段也被更新
尽量使用 NOT NULL
NULL 类型比较特殊,SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异,会进入索引中,但如果是一个组合索引,那么这个NULL 类型的字段会极大影响整个索引的效率。此外,NULL 在索引中的处理也是特殊的,也会占用额外的存放空间。
很多人觉得 NULL 会节省一些空间,所以尽量让NULL来达到节省IO的目的,但是大部分时候这会适得其反,虽然空间上可能确实有一定节省,倒是带来了很多其他的优化问题,不但没有将IO量省下来,反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL,也是一个很好的表结构设计优化习惯。
本文出处:http://isky000.com/database/mysql-perfornamce-tuning-schem
相关推荐
表结构优化:合理设计数据库表的结构,避免冗余字段、过度规范化等问题。可以根据业务需求和数据特点,进行适当的表结构优化,提高数据库的性能和可维护性。事务管理:MySQL支持事务操作,可以确保数据库操作的一致...
数据库数据对比工具:可以针对两个数据进行表结构和数据的对比。1、结构对比:如开发库和正式库之间的结构和数据同步,此工具可以告诉您哪个表结构有字段的增加减少,或者字段类型的改变,并生成sql进行结构的同步。...
数据库设计范式>作为数据库表结构设计“圣经”,认为只要按照这个范式需求设计,能让设计出来的表结构足够优化,既能保证性能优异同时还能满足扩展性要求。殊不知,在N年前被奉为“圣经”的数据库设计3范式早已经不...
2. 性能优化:包括 SQL 查询优化、索引优化、数据表分区、缓存机制等。 3. 备份与恢复:包括数据备份和恢复的方法、备份数据的类型、备份与恢复的策略、日志文件管理等。 4. 数据库设计:包括数据库设计范式、数据库...
可以对两个mysql数据库进行表和表字段的对比,反馈缺少的字段和表名。
mysql 数据库结构对比工具 java 1.配置好两个数据库的账号密码数据库名称等信息 DbComparator db1 = new DbComparator("ip", null, "user", "password", "dbname", "47" ); ...
数据库 mysql 表结构导出工具
数据库设计和规范化:确保数据库表结构合理、字段类型正确,并进行适当的规范化,以减少数据冗余和提高查询效率。 索引优化:根据查询需求创建适当的索引,避免过多或不必要的索引,以提高查询速度和减少资源消耗。...
支持导出MySQL数据库表结构!! 运行环境:jdk8+,需要Java运行环境 执行命令:java -jar data_to_word-1.0-SNAPSHOT.jar
数据库优化包含以下三部分,数据库自身的优化,数据库表优化,程序操作优化.此文为第二部分 优化①:设计规范化表,消除数据冗余 数据库范式是确保数据库结构合理,满足各种查询需要、避免数据库操作异常的数据库...
一键生成Oracle、Sqlserver、Mysql 数据库表结构说明文档,文档输出类型支持word、PDF、html。原创作品绿色环保无污染
java 读取表结构 mysql 数据库名: 表名: 字段描述 字段名 数据类型 是否可为空 自增 主键
导出 MySQL数据库表结构word设计文档,一键连接数据库,直接导出库表结构、字段解析、自动排序 导出 MySQL数据库表结构word设计文档,一键连接数据库,直接导出库表结构、字段解析、自动排序 导出 MySQL数据库表结构...
两个MySQL数据库之间同步表结构及索引模式
使用MySQL可视化工具Navicat导出MySQL的表结构脚本的方法。 1、右键Navicat中的数据库→数据传输(Data Transfer)。 2、左边数据库对象(Database Objects)中选择对应的表,右边目标(Target)中选择文件(File)...
MySQL数据库的创建、查看、删除、使用命令。 表结构创建和修改、表约束的创建和修改; 表数据的插入、删除和修改; 表联系的创建和修改。
导出mysql数据库结构到pdf、html
day10: 数据库概念及作用 ... SQL-命令-表结构的创建 SQL-命令-表结构的修改 SQL-命令-表数据操作-查 SQL-命令-表数据操作-插 SQL-命令-表数据操作-改 SQL-命令-表数据操作-删 DQL(数据库查询语言)执行顺序
MySQL优化是指通过调整数据库的配置和优化查询语句等方式来提高MySQL数据库的性能和效率。 以下是一些常见的MySQL优化方法和技巧的描述: 优化数据库结构:设计良好的数据库结构可以提高查询效率。包括正确选择...