mysql 设计优化浅谈

最新推荐文章于 2021-02-01 14:45:36 发布

kwxyzk

最新推荐文章于 2021-02-01 14:45:36 发布

阅读量185

点赞数

分类专栏：数据库 # 基础学习 MYSQL 文章标签： mysql

本文链接：https://blog.csdn.net/kwxyzk/article/details/107079538

版权

基础学习同时被 3 个专栏收录

7 篇文章 0 订阅

订阅专栏

数据库

1 篇文章 0 订阅

订阅专栏

MYSQL

1 篇文章 0 订阅

订阅专栏

数据库设计

schema表设计

范式和反范式

一般来说我们在开发过程根据业务需求画出适合业务的E-R图，初级的满足业务的功能，但在满足于功能的同时，我们也要考虑设计的合理性和使用的性能，关系型数据库的设计一般来说遵循范式，目前关系性数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

第一范式（1NF）：
满足最基本的条件，每一个分量都是不可分的数据项。
每个实例或记录中的属性都是一个个独立的属性，每一列都是不可分割的，即列中不能是集合，数组等（表中有表）。
第二范式（2NF）：
R属于第一范式，且每一个非主属性完全函数依赖于任何一个候选码，则R属于第二范式。
在第一范式的基础上，如果每个实例或记录都能被唯一的区分（主属性/主键），例如（学生信息表中的学号）。
第三范式（3NF）：
R属于第二范式，若R中不存在码X，属性子集Y，非主属性Z，使得X决定Y，Y不决定X，Y决定Z，则R属于第三范式。
在第二范式的基础上，如果每个实例或记录中，非主属性不依赖于其他的非主属性(即传递依赖，成绩表中既有学科的id，还有学科的名称)，即消除传递依赖。
巴斯-科德范式（BCNF）：
R属于第一范式，若X决定Y，且Y不是X的子集时X必含有码，即每一个决定因素都包含码，则R属于BC范式。
在3NF基础上，任何非主属性不能对主键子集依赖（在3NF基础上消除对主码子集的依赖）
第四范式(4NF）：
R属于第一范式，对应R的每一个非平凡多值依赖，X->->Y，X都含有码，则R属于第四范式。
即不存在多对多的依赖
第五范式（5NF）
第五范式是指关系模式R依赖均由R候选码所隐含。

范式规则避免了大量的数据冗余，节省了存储空间，保持了数据的一致性，当一个库里的数据经常发生变化时，达到3NF的库可以使用户不必在超过两个以上的地方更改同一个值，但我们设计数据库的过程中，范式依赖只是给我们提供一个设计表的准则，有时为了性能方面的考虑，我们可以冗余必要的字段在同一张表内，较少关联查询等，不完全依照范式设计，即反范式。

范式的优点和缺点

为了性能，我们经常被建议对schema进行范式优化设计，尤其是对写密集性的场景。范式化通常给我们带来诸多好处。

范式话化的更新通常比反范式化的要快。
当数据能进行范式化时，就只有很少或者没有重复的数据，所以只要修改更少的数据。
范式化的表通常更小，可以更好的放在内存里，随意执行操作会更快。
很少有多余的数据就意味着检索列表数据时更少使用distinct或者group by语句

范式化设计的schama的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schama上可能需要至少一次关联，也许更多。这不但是昂贵的代价，也可能使一些索引策略无效。

反范式的优点和缺点

反范式的的schema因为所有的数据都在同一张表里，可以很好的避免关联。如果不需要关联表，则对大部分查询最差的情况——即使没有索引——全是表扫描。当数据比内存大时这可能比关联表要快得多，因为这样避免了所以I/O。单独的表也能使用更有效的索引策略。

所以我们在做设计是根据具体情况通常混用范式化和范式化，达到业务和性能上的优化。

schema表的数据类型和字符集选择

数据类型

mysql中提供了很多很多的数据类型和字符集供业务开发人员选择使用，主要有：

整数类型

mysql给我们提供了TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT五种类型。

类型	存储空间(位)	有符号最小值	无符号最小值	有符号最大值	无符号最大值
`TINYINT`	1	`-128`	`0`	`127`	`255`
`SMALLINT`	2	`-32768`	`0`	`32767`	`65535`
`MEDIUMINT`	3	`-8388608`	`0`	`8388607`	`16777215`
`INT`	4	`-2147483648`	`0`	`2147483647`	`4294967295`
`BIGINT`	8	`-`	`0`

实数类型

实数是带有小数部分的数，然而它们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数。mysql既支持精度类型，也支持不精度类型。固定小数类型DECIMAL, NUMERIC(DECIMAL实现NUMERIC)，浮点小数类型FLOAT，DOUBLE，两种类型都可以指定精度。

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。DECIMAl类型由于存储精确的小数。但是CPU不支持对DECIMAL的直接计算，由mysql服务器自身实现DECIMAL的高精度计算（因为DECIMAL只是一种存储格式，在计算中DECIMAL会转化为DOUBLE类型），相对而言，CPU支持原生的浮点数计算，所以浮点运算明显更快。

浮点类型在存储同样范围的值时，通常比DECIMAL使用更小的空间。FLOAT使用4个字节存储，DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围，能选择的只是存储类型，mysql使用DOUBLE作为内部浮点计算的类型(通常当不同精度数字类型进行计算时都是转化以较大的精度类型进行计算)。

因为需要额外的空间和计算开销，所以尽量只在对小数进行精确计算的时候才使用DECIMA——例如存储财务数据。但在数据量比较大的时候我们可以考虑使用BIGINT代替DECIMA，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

字符串类型

字符串类型共有CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET 八种类型。

CHAR, VARCHAR是两种最主要的字符串类型。CHAR为定长的，mysql总是根据定义的字符串长度分配足够的空间，当存储CHAR时，数据库会删除所有的末尾空格；VARCHAR是变长的，需要额外的1~2个字节来记录字符串的长度，小于255的时候使用1个字节来记录长度，否则需要2个字节。所以VARCHAR（10）需要11位的存储空间，VARCHAR（1000）需要1002位。BINARY 和VARBINARY 跟CHAR和 VARCHAR类似。

值	CHAR(4)	存储需要	VARCHAR(4)	存储需要
''	' '	4 bytes	''	1 byte
'ab'	'ab '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

BLOB存储的是二进制的大文件，TEXT是存储很大的字符串，它们都是为了存储很大的数据而设计的字符串类型。BLOB类型有TINYNBLOB,BLOB,MEDIUMBLOB和LONGBLOB,TEXT类型有TINYNTEXT,TEXT,MEDIUMTEXT和LONGTEXT,与其他类型不多，mysql把BLOB和TEXT当成一个独立的对象处理，存储引擎在存储时通常会做特殊处理，如果当值过大时，InnoDB会使用专门的外部存储区域来存储，然后使用记录下存储指针。BLOB和TEXT不同之处在于BLOB是二进制数据，没有拍寻和字符集，而TEXT有排序规则和字符集。

字符串在存储是有字符集设置：

每种字符集都有可能有多种校对规则，并且都有一个默认的校对规则。每个校对规则都是针对某一个特定的字符集的，和其他的字符集没有关系，校对规则和字符集总是一起使用，所以我们也叫做一个字符集。

mysql的字符集设置分为两类：创建对象时的默认值、在服务器和客服端通信时的设置。

创建对象时设置：mysql服务器有默认的字符集和校对规则，每个数据库也有自己的默认值，每个表也有自己的默认值。这是一个逐层继承的默认值，最终底层的默认设置将影响你得创建对象，真正存放数据的时列，所以列上设置的值才是最终使用的字符集，如果列上已经有设置了就用设置的，没有才会继承表上的默认值。

在服务器和客服端通信时的设置：在数据库链接上的设置，例如useUnicode=true&characterEncoding=UTF-8

使用枚举来代替字符串类型

有时候可以使用枚举列代替常用的字符串类型。枚举可以把一些不重复的字符串存储成一个预定义的集合。mysql在存储枚举时会非常的紧凑，会根据列表值的数量压缩到一个或者两个字节中。mysql会在内部将每个值在列表的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”的映射关系的“查找表”。

eg：

CREATE TABLE shirts ( name VARCHAR(40), size ENUM('x-small', 'small', 'medium', 'large', 'x-large') );

值	索引
`NULL`	`NULL`
`''`	0
`'Mercury'`	1
`'Venus'`	2
`'Earth'`	3

set类型

使用set类型存储值SET('one', 'two')可以存储 '' ，'one' ，'two' ，'one,two'

eg：CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

日期和时间类型

mysql可以使用很多类型来保存日期和时间值，例如YEAR和DATE。mysql能存储的最小时间粒度是秒，但是mysql也可以使用微秒级的粒度进行临时计算。

mysql提供两种相似的日期类型：DATETIME和TIMESTAMP，DATETIME能保存大范围的值，从1001年到9999年，精度为秒，他把日期和时间封存到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节的存储空间。TIMESTAMP保存从1970年1月1日凌诚到2038年的时间，使用4个字节的存储空间，与时区有关，效率比DATETIME高。

其他数据格式

mysql还有其他很多的数据类型，比如bit位类型，新的JSON_TYPE类型等。

schema设计中的一些优化点

选择正确合适的数据类型和字符集对性能至关重要，一般我们可以依赖下面一些规则

更小的通常更好

尽量使用更小的数据类型，小的数据类型占用的更少的自盘，内存和CPU缓存，处理时使用CUP的周期更短

简单就好

简单数据类型操作通常需要更少的CPU周期

尽量避免使用null值

除非真的需要使用null值，通常情况下最好使用not null，使用null值时服务器查询会更难优化，也会让索引，索引统计和值都变得比较复杂，如果没有值可以使用""空字符串代替(当然这会是的应用程序中null判断变得复杂，谨慎考虑)

schema设计的缺陷

太多的列

占用更多的资源和查询缓冲

太多的关联

太多的关联会让sql解析和优化变得困难，mysql还有关联表的对多限制。

全能枚举

过度使用枚举

变相枚举

使用set代替枚举，set可以同时出现多个值，但如果列中只有唯一确定值，那么最好使用枚举而不是set

不适时宜的绕开null

为了不出现null而使用空字符串代替，但有时null的值是业务的需要。

索引设计

索引是存储引擎用来快速找到记录的一种数据结构。索引对于提高性能是很关键的，尤其是当表中的数据越来越大的时候，索引对性能的影响更重要。在数据量小且负载较低的时，不恰当的索引对性能的影响还不明显，但当数据量逐渐增大的时，性能则会急剧下降，所以索引的设计对性能的影响很重要，好的所以能提高查询的效率，不恰当的所以只会增加服务的性能消耗。

索引的类型有很多类，可以为不同的场景提供更好的性能。在mysql中，索引是由存储在存储引擎层而不是服务器层实现的。所以并没有统一的标准：不同的存储引擎有不同的工作方式并不一样，也不是所用的存储引擎都支持所有类型的索引。即使不同的存储引擎对统一索引类型的底层实现也可能不一样。

B-Tree索引

大多数mysql数据库引擎都支持B-Tree索引，B-Tree通常意味着所有的值都是按顺序存储的，感兴趣的同学可以自行搜索B-Tree树，学习其数据结构。

可以使用B-Tree索引的查询类型：B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适合根据最左前缀查找。索引对如下类型的查询有效：

全值匹配

与所有列进行匹配。

匹配最左前缀

只使用索引的第一列

匹配列前缀

只匹配索引中某一列的前缀

匹配范围

索引的第一列的范围查找

精确匹配某一列并范围匹配另一列

精确查找某一列的值，返回查找另一个的范围

只访问索引的查询

查询只访问索引，查询条件在索引中，返回结果的也在索引中

关于B_Tree索引的一些限制

如果不是按照索引的最左列开始查找，则无法使用索引。
不能跳过索引中的列
如果查询中有某个列的范围查找，则其右边所有的列都无法使用使用索引优化查询。

哈希索引

哈希索引基于哈希函数实现，只有精确匹配所有列的查询才有效。对于每一行的数据，存储引擎都会对所有的索引列都进计算一个哈希值，哈希值是一个较小的数值，并且不同的键值行计算出来的哈希值也不一样。哈希索引将所有的哈希值存储在索引中，同时在哈希表中保存指向每个数据行的指针。

关于哈希所以因的一些限制

使用的是哈希值，所以没法从索引中直接获取返回结果

哈希值并不是按顺序存储，所以不能用索引来排序

哈希索引也不支持部分索引列查找，因为哈希索引始终是使用哈系列的全部内容来进行计算的哈希值的。

哈希索引只支持等值比较查询，包括=，<>,in(),不支持范围查询(>等)

访问哈希索引的数据非常快，但是如果有很多哈希冲突，则存储引擎必须遍历链表中的所有数据行，逐个进行比较，知道找到查询数据为止。

哈希冲突很多时候，一些索引的维护代价会也会很高。

因为这些限制，所以哈希索引只适用于某些特定的场景，而一旦适合哈希索引，则它带来的性能提升将会非常显著。

其他类型索引

还有其他类型的索引，空间数据索引（R-Tree），全文索引和分形树索引等就不一一介绍了。

索引给我们带来哪些有事呢？

索引大大较少了服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机I/O变成顺序I/O。

高性能的索引策略

独立的列：使用单独的一个列作为索引。

前缀索引：当列内容很长的时候我们可以截取一段合适的前缀作为索引(可以借助计算列的完成性，distince left()/count(*))。

多列索引：选择多列作为一个索引，这个时候列的顺序很重要，根据索引的使用规则（查询和排序的需求)决定列的顺序。

聚簇索引：即索引的叶子页存放数据，是一种存储方式。

覆盖索引：即查询的结果列都包含在索引中，就不需要再通过索引查询数据行了。

使用索引扫描来排序：索引本身按顺序存储。

压缩（前缀）索引：通过压缩索引或者使用前缀索引较少索引使用的空间，让内存中可以存入更多的索引提高性能。

冗余索引和重复索引：重复索引是指在相同的列上按相同的顺序创建的相同类型的索引。冗余索引是指其中一个索引是另个一的前缀索引【索引（A，B）与索引（A）】。索引需要额外的空间和插入更新是的性能消耗，索引应该避免出现重复索引和冗余索引，尽量考虑在原有的索引上进行扩展，当然，如果有其他的性能考虑也可以重新建一个冗余索引，删除重复索引。

未使用的索引：如果不是特殊作用(规范唯一性等)尽量删除未使用的索引。

索引和锁：索引可以让我们查询锁定更少的行，索引减少我们访问的行数，较少了行锁的索引，既提高了我们内存的使用率，也提高了锁的性能效率。

使用B-Tree让我们利用这些索引时查询，一般有如下三个原则：单行单行的访问数据是很慢的；按顺序访问范围数据是很快的(较少I/O访问和直接排序)；索引覆盖查询是很快的。

查询优化

在提高数据库性能的时候，查询优化，索引优化和数据库表结构优化需要齐头并进，那我们在编写查询时应该注意哪些细节呢？一般来说一个查询的响应时间包含网络传输，mysql查询语句解析优化和数据检索等几个大的方面。我们写查询语句时需要注意这些方面的耗时。

优化查询语句我们可以遵循一下两个大的方面考虑：

是否向服务器请求了不需要的数据

我们可以从这些方面考虑：返回不需要的列、关联查询取出所有的列、总是取出全部的列和重复查询相同的数据等。

mysql是否在扫描额外的记录

对于mysql来说可以从这三个方面衡量查询开销：响应时间，扫描的行数和返回的行数（可以使用EXPLAIN观察查询语句的执行情况）。

当然mysql的查询优化器也会帮我们处理一些优化，比如重新定义关联查询表的顺序、将外连接转化为内连接、使用等价变化、优化COUNT(),MIX(),MIN()、预估并转化为常量表达式、覆盖索引扫描、子查询优化、提前终止查询等。当然它也有一些局限，比如关联子查询的实现很糟糕，在同一表上查询和更新，union的limit不能无法下推到内层，松散索引扫描全表等。我们可以通过hint来提示优化，这里不做介绍。

提高查询我们可以重构查询语句：

设计查询时可以考虑是否可以将一个复杂的查询分解成多个简单的查询，比较他们的性能。
切分查询：可以根据某个字段切分需要查询的数据的大小，比如通过切分时间段多次查询完成一个长时间段的查询。
分解关联查询：简单地，可以对每一个表进行一次单表查询，然后把结果集在应用程序中组装进行关联

当然我们在写查询语句时充分利用好索引来提高性能。

kwxyzk

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 设计优化浅谈

数据设计 schema表设计范式和反范式一般来说我们在开发过程根据业务需求画出适合业务的E-R图，初级的满足业务的功能，但在满足于功能的同时，我们也要考虑设计的合理性和使用的性能，关系型数据库的设计一般来说遵循范式，目前关系性数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。第一范式（1NF）：满足最基本的条件，每一个分量都是不可分的数据项。每个实例或记录...
复制链接

扫一扫