MYSQL数据库表设计优化

这段时间研究数据库知识比较多,所以所查阅的专业资料也比较多,而且本人重点推荐把触发器学好,有时间多练习一下,至少要把格式写会吧!经过这些天面试的经验来讲,应聘数据库岗位,触发器和存储调用是必问题目,当然不乏开发岗位。这也是我为什么要反复推荐自学这块知识的理由呀,数据库内容涵盖甚广,当然对于企业更多的感觉还是oracle数据库应用更广泛,以下是Mysql的知识点,以备应急之用。


1.选择优化的数据类型

      MySQL支持很多种不同的数据类型,并且选择正确的数据类型对于获得高性能至关重要。不管选择何种类型,下面的简单原则都会有助于做出更好的选择:

 (1).更小通常更好

       一般来说,要试着使用正确地存储和表示数据的最小类型。更小的数据类型通常更快,因为它们使用了更少的磁盘空间、内存和CPU缓存,而且需要的CPU周期也更少。

      但是要确保不人低估需要保存的值,在架构中的多个地方增加数据类型的范围是一件极其费力的工作。如果不确实需要什么数据类型,就选择你认为不会超出范围的最小类型。

(2).简单就好

    越简单的数据类型,需要的CPU周期就越少。例如:比较整数的代价小于比较字符,因为字符集和排序规则使字符比较更复杂。

 (3).尽量避免空(NULL

       要尽可地把字段定义为NOT NULL 。即使应用程序无须保存NULL,也有许多表包含了可为空的列,这仅仅是因为它为默认选项,除非真的要保存NULL,否则就把列定义为NOT NULL

      MySQL难以优化了使用了可空列的查询,它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间,还需要在MySQL内部进行特殊处理。当可空列被索引的时候,每条记录都需要一个额外的字节,还能导致MyISAM中固定大小的索引(例如:一个整数列上的索引)变成可变大小的索引。

       即使要在表中存储可为空的字段,也是有办法不使用NULL的,可以考虑使用0,特殊值或字符串来代替它。

NULL列改为NOT NULL 带来的性能提升很小,所以除非确定它引入了问题,否则就不要把它当成优先的优化措施。如果计划对列进行索引,就要尽量避免把它设置为可为空(NULL

2.整数

      数字有两种类型:整数和实数,如果存储整数,就可以使用这几种整数类型:tinyint, smallint, mediumint, int, bigint ,它们分别需要816243264位存储空间。

      整数类型有可选的unsigned(无符号)属性,它表示不允许为负数,并大致把正上限提高了一倍,例如:tinyint unsigned保存的翻围为0255,而不是-127128

       Signed(有符号)unsigned(无符号)类型占用的存储空间是一样的,性能也一样。因此可以根据实际情况采用合适的类型。

你的选择将会决定MySQL把数据保存在内存中还是磁盘上,然而,整数运算通常使用64位的bingint整数。

       MySQL还允许你对整数类型定义宽度,比如int(11)。这对于大在多数应用程序是没有意义的,它不限制值的范围,只规定了mysql的交互工具(例如命令客户端)用来显示字符的个数。对于存储计算,int(1)int(20)是一样的。

3.实数

     实数有分数部分,然而,它们并不仅仅是分数。可以使用decimal保存比出bigint还大的整数。MySQL同时支持精确与非精确类型。

Floatdouble类型支持使用标准的浮点运算进行近似计算。如果想知道浮点运算到底如何进行,则要研究生平台浮点数的具体实现。

比较起decimal类型,浮点类型保存同样大小的值使用的空间通常更小,float类型占用4个字节,double占用8个字节,而且精度更大,范围更广。和整数一样,你选择的仅仅是存储类型。mysql在内部对浮点类型使用double进行计算。

由于需要额外的空间和计算开销,只有在需要对小数进行精确的时候才使用decimal,比如保存金融数据。

4.字符串类型

     Varcharchar类型

varchar:保存了可变长度的字符串,是使用得最多的字符串类型,它能比固定类型占用更少的存储空间,因为它只占用了自已需要的空间(也就是说较短的值占用的空间更小)。它使用额外的1-2个字节来存储值的长度Varchar能节约空间,所以对性能有帮助。然而,由于行的长度是可变的,它们在更新的时候可能会发生变化,这会引起额外的工作。当最大长度远大于平均长度,并且很少发生更新的时候,通常适合用varchar。这时候碎片就不会成为问题,还有你使用复杂的字符集,如utf-8时,它的每个字符都可能会占用不同的存储空间。Varchar存取值时候,MySQL不会去掉字符串末尾的空格。

char:固定长度,char存取值时候,MySQL会去掉末尾的空格。Char在存储很短的字符串或长度近似相同的字符的时候很有用。例如,char适用于存储密码的MD5哈希值,它的长度总是一样的。对于经常改变的值,char也好于varchar,因为固定长度的行不容易产生碎片,对于很短的列,char的效率也高于varcharChar(1)字符串对于单字节字符集只会占用1个字节,而varchar(1)则会占用2个字节,因为有一个字节用来存储其长度。

        Charvarchar的兄弟类型为binaryvarbinary,它们用于保存二进制的字符串,二进制字符串的传统的字符串很类似,但是它们保存的是字节而不是字符。填充也有所不同,MySQL使用\00字节)填充binary值,而不是空格,并且不会在获取数据的时候把填充的值截掉。

        使用varchar(5)varchar(200)保存“hello”占用的空间是一样的,但是使用较短的列有很大的优势,较大的列会使用更多的内存,因为MySQL通常会分配固定大小的内存块来保存值。这对排序或使用基于内存的临时表尤其不好。同样的事情也会发生在使用文件排序或基于磁盘的临时表的时候。

5.BLOBTEXT类型

   BLOBTEXT分别用二进制和字符形式保存大量数据。

   事实在,它们各有自的数据类型家族:字符类型有tinytext, smalltext, text, mediumtextlongtext, 二进制类型有tinyblob, smallblob, blob, medicmblob, longblobBLOB 等同于smallblob, TEXT等同于smalltext

       和其它类型不同,MySQLblob, text当成有实体的对象来处理,存储引擎通常会特别地保存它们。InnoDB在它们较大的时候会使用单独的“外部”存储来进行保存,每个值在行里面都需要1-4字节,并且还需要足够的外部存储空间来保存实际的值。

BLOBTEXT唯一的区别就是BLOB保存的是二进制数据,没有字符集和排序规则,TEXT保存的是字符数据,有字符集和排序规则。

      MySQLBLOBTEXT列的排序方式和其它类型不同,它不会按照字符串的长度进行排序,而只是按照max_sort_length规定的前若干个字节进行排序,如果只按照开始的几个字符排序,就可以减少max_sort_length的值或使用ORDER BY SUBSTRING(column, length)MySQL不能索引这些数据类型的完整长度,也不能为排序而使用索引。

6.使用ENUM代替固定字符串类型

      ENUM列可以存储65535个不同的字符串,MySQL以非常紧凑的方式保存了它们,根据列表中值的数量,MySQL会把它们压缩到1-2个字节中,MySQL在内部会把每个值都保存为整数,以表示值在列表中的位置,并且还保留了一份“查找表”来表示整数和字符串在表的.frm文件中的映射关系。

       Enum最不好的一面是字符串是固定的,如果需要添加或者删除字符串必须使用ALTER TABLE,因此,对于一系列未知可能会改变的字符串,使用enum就不是一个好主意,MySQL在内部的权限表中使用enum来保存Y值和N值。

      由于MySQL把每个值保存为整数,并且须进行查找才能把它转换成字符串形式,所以enum有一些开销。这通常可以由它们较小的大小进行弥补,但不总是这样,在特定情况下,把charvarchar列和enum列进行联接,可能会比联接另一个charavarchar列慢。

7.日期和时间类型

        MySQL可以使用多种类型来保存各种日期和时间值,比中yeardateMySQL能存储的最细的时间粒度是秒,然而,它可以用毫秒的粒度进行暂时的运算。

       MySQL提供两种相似的数据类型:DATETIME TIMESTAMP,对于很多应用程序,它们都能正常工作,但是在某些情况下,一种会好于另外一种。

       DATETIME:能够保存大范围的值,从1001年到9999年,精度为秒,它把日期和时间封装到一个格式为yyyyMMddHHmmss的整数当中,与时区无关。它使用了8个字节存储空间。

       TIMESTAMP:保持了自197011日午夜(格林尼治标准时间)以来的秒数,它和Unix的时间戳相同。它只使用了4个字节存储空间。因此它比DATETIME的范围小得多。它表示自能从1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期,并提供UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。

        TIMESTAMP显示的值依赖于时区,MySQL服务器、操作系统及客户端连接都有时区设置。因此,保存0值的TIMESTAMP实际显示的时间是美国东部的时间1969-12-31 19:00:00,与格林尼治标准时间(GMT)相差5小时。

       TIMESTAMP也有DATETIME没有的特殊性质,在默认情况下,如果插入的行没有定义TIMESTAMP列的值,MySQL就会把它设置为当前时间。在更新的时候,如果没有显示地定义TIMESTAMP列的值,MySQL也会自动更新它。可以配置TIMESTAMP列的插入和更新行为。最后,TIMESTAMP默认是NOT NULL,这也和其它的数据类型不一样!

8.选择标识符

        为标识列选择好的数据类型非常重要,你可能会更多地用它们和其他列做比较,还可能把它们用作其它表的外键,因为选择标识符列选择数据类型的时候,你也可能是在为相关的表选择数据类型。

       当为标识符列选择数据类型的时候,不仅要考虑存储类型,还要考虑MySQL如何对它们进行计算和比较。例如:mysql会在内部把enumset类型保存为整数,但是在比较的时候把它们转换为字符串。

      一旦选择了数据类型,要确保在相关表中使用同样的类型。类型之前要精确匹配,包括诸如unsigned这样的属性。混合不同的数据类型会导致性能问题,即使没有性能问题,隐式的类型转换也能导致难以察觉的错误,在你已经忘记了自己是在对不同类型做比较的时候,这些错误就会突然出现。

       选择最小的数据类型能表明所需值的范围,并且为将来留出增长的空间。例如,如果用porvince_id来表示中国的省份,那么我们知道它不会产成千上万个值,因类就没有必要使用int,用tinyint就足够了,它比int3个节字,如果把一个表的主键是tinyint,而另一个表以int作为外键,那么就会造成较大的性能差距。

        整数通常是标识符的最佳选择,因为它速度快,并且能使用auto_increment

       Enumset通常不合适用作标识符,尽管它适合用来做静态的,包含了状态和“类型”和值的“定义表”。

       Enumset列适合用来性别、国家、省份这些固定不变的信息。

       要尽可能的避免使用字符串来做标识符,因为它们占用了很多空间并且通常比整数类型要慢,特别注意不要在myisam表上使用字符串标识符。myisam默认情况下为字符串使用了压缩索引,这使查找更为缓慢。

        MyISAM使用前缀压缩来减小索引大小,默认情况下会压缩字符串,也可以压缩整数

可以使用create table时用PACK_KEYS控制索引压缩的方式。

       PACK_KEYSMySQL手册中如下描述:

       如果您希望索引更小,则把此选项设置为1。这样做通常使更新速度变慢,同时阅读速度加快。把选项设置为0可以取消所有的关键字压缩。把此选项设置为DEFAULT时,存储引擎只压缩长的CHARVARCHAR列(仅限于MyISAM)。

如果您不使用PACK_KEYS,则默认操作是只压缩字符串,但不压缩数字。如果您使用PACK_KEYS=1,则对数字也进行压缩。

9.特殊类型的数据

      一些数据类型没有直接对应的内建数据类型,精度低于秒的时间戳就是一个例子,另一个例子就是IP地址,人们通常使用varchar(15)来保存IP地址。但是,IP地址实际上是无符号的32位整数,而不是字符串。使用小数点来进行分纯粹是为了增加它的可读性。在实际使用时应用用无符号整数来存储IP地址。

MySQL提供了INET_ATON()INET_NTOA()函数在IP地址和整数之前转换。

更多相关文章
  • MySQL数据库如何做好优化(碰到过好几次这样的题目,可每次都不能答的完全正确,现在大概列出如下:(望各位补充)(1.数据库的设计((尽量把数据库设计的更小的占磁盘空间.(1).尽可能使用更小的整数类型.(mediumint就比int更合适).(2).尽可能的定义字段为not null,除非这个字段 ...
  • 经常为了方便和直观,我们会首先直接在数据库中设计出表,但是接下来又要将表的结构和设计编写在设计文档中,以便编码的时候可以直观的查询,一旦数据库表非常多,字段非常多的时候,这无疑是件非常郁闷的工作.这是一个漂亮的PHP页面,可以自动输出MySql数据库所有表结构,大大方便了文档的编写工作,也同时非常方 ...
  • mysql数据库表操作及授权表操作:增删改查把/etc/passwd文件的内容导入 passwd表里.mysql>load data infile"/etc/passwd" into table passwd fields terminated by ":&quo ...
  • mysql数据库表的自增主键ID乱了,需要重新排序. 原理:删除原有的自增ID,重新建立新的自增ID. 1.删除原有主键: ALTER TABLE `table_name` DROP `id`; 2.添加新主键字段: ALTER TABLE `table_name` ADD `id` MEDIUMI ...
  • 一个简单的用户登录系统 用户有账号密码,登录ip,登录时间 打开登录页面,输入用户名密码 登录日志,可以记录登陆的时间,登陆的ip 成功登陆了的话,就更新用户的最后登入时间和ip,同时记录一条登录记录 大致就是这样子 ----------------------- 创建数据库 navicat在loc ...
  • 数据库表设计
     数据库表设计 分析上述各实体的属性集,从中找出关系的主键,然后用关系式来表示实体(其中下划线的属性为主键).表4-1至表4-5分别给出了主要表结构各实体的属性如下: 表4-1  admin 表名称  主要字段 数据类型 长度 属性 输入方式 描述 备注 管理员表 Id 字符串 10 NN 输入 用 ...
  • [51CTO独家特稿]公司网站访问量越来越大, MySQL数据库服务器的压力越来越大了,第一步自然想到的是 MySQL系统参数的优化,我们不可能指望 MySQL 默认的系统参数能够让MySQL运行得非常顺畅.在Apache(Nginx).PHP. MySQL的体系架构中,MySQL对于性能的影响最大 ...
  • Mysql 数据库的优化  最近一直在搞Mysql数据库的配置和优化,下面记录一下这些天研究的结果,以方便以后查看. 一.服务器的硬件的优化 对mysql服务器来说主要从以下几个方面来考虑:1.磁盘寻道能力(磁盘I/O)  因为Mysql每一秒都在进行着大量的.复杂的查询操作,对磁盘的读写可想而知, ...
一周排行
  • iOS开发必备指南合集一申请IDP/真机调试/GameCenter指南/OpenFeint指南
     这里Himi给出对于开发iOS的朋友们整理一个指南集合,其中主要包括申请IDP需要注意的 ...
  • Cisco 专利每 VLAN 生成树协议 (PVST) - 为网络中配置的每个 VLAN 维护一个生成树实例.其使用 Cisco 专有的 ISL 中继协议,该协议允许 VLAN 中继为某些 VLAN 转发流量,对其它 ...
  •     生成二维码的开源项目可谓是琳琅满目,SwetakeQRCode.BarCode4j.Zxing......     前端有JQuery-qrcode,同样能实现生成二维码.     选择Zxing的原因可能是 ...
  • Linux网络设备驱动架构Linux网络设备驱动数据流程NON-NAPI模式数据接收流程NAPI模式数据接收流程数据发送流程Linux网络协议栈的实现TCP/UDP/IP/MAC各层数据传递网络系统调 ...
  • 惆怅啊没办法,总监让优化整个架构的内核参数,没办法就到处搜集了一些整理到下面了.看下面参数我还是建议先看下:http://blog.sina.com.cn/s/blog_6b92dc3b0100n3uy.html减少 ...
  • 最近进了个新项目,用到数据库就是DB2,遇到些蛋疼的问题,好在都解决了.恩恩……现在记录下来首先是导入数据源的问题,项目里面的先头部队给了我一个他备份出来的数据源(对,是备份出来的的,不是导出来的,为什么给个备份出来 ...
  • 腾讯的微信平台从问世就大手笔的抢夺了不少用户.微信公众平台来势汹汹,突然间不少企业和个人甚至微博上的营销帐号们都开始入驻微信公众平台了,这里有很早就在微信认证的i黑马.冷兔等.今天互联网的那点事,美剧狂人,当时我就震 ...
  •     雨辰今天早上来的早,但一上线就看到老杜在那报怨,说昨天刚跟领导'吵了一架'主要是针对产品需求分析看法和观点不一致,分歧比较大,谁也没说服谁,最后领导一拍桌子说:"我是这个团队的头,这点事都决定不下来 ...
  • MicrosoftSQLServer2012安装体验
    因为项目需要,安装和测试了SQL Server 2012,将过程做了简单记录,后期如果时间 ...
  • Java运算符 (1)赋值操作符 赋值操作符(=)表示:取右边的值(即右值),把它复制给左边(即左值). 右值可以是任意的常量.变量或表达式(只要可以生成一个值). 左值必须是一个明确的.已命名的变量. public ...