MYSQL 架构优化和索引之列设计篇

标签: mysql 架构 优化 | 发表时间:2012-05-23 16:52 | 作者:川山甲
出处:http://www.cnblogs.com/
 
思维导图
 
点击图片,可查看大图。
 
 介绍
 
情况:如果你的表结构设计不良或你的索引设计不佳,那么请你优化你的表结构设计和给予合适的索引,这样你的查询性能就能提高几个数量级。——数据越大,索引的价值越能体现出来。
 
我们要提高性能,需要考虑的因素:
1、设计架构
2、设计索引
3、评估查询性能
 

今天要讲的是表列的设计,暂不谈索引设计。我会在下一章讲索引设计。
 
准备数据
 
以下是数据储备脚本:主要是做表的建立和数据的插入——你也可以视情况修改表结构。 源代码下载

            

           

 

选择数据类型
 
选择正确的数据类型,对于提高性能至关重要。
下面给出几种 原则,有利于帮助你选择何种类型。
 
1、更小通常更好。
使用最小的数据类型。——更少的磁盘空间,内存和CPU缓存。而且需要的CPU的周期也更少。
 
2、简单就好。
整数代价小于字符。——因为字符集和排序规则使字符比较更复杂。
1>mysql内建类型(如timestamp,date)优于使用字符串保存。
2>使用整数保存ip地址。
 
3、尽量避免NULL——如果计划对列进行索引,尽量避免把列设置为NULL
尽可能把字段定义为NOT NULL。——可以放置一个默认值,如‘’,0,特殊字符串。
原因:
(1)MYSQL难以优化NULL列。NULL列会使索引,索引统计和值更加复杂。
(2)NULL列需要更多的存储空间,还需要在MYSQL内部进行特殊处理。
(3)NULL列加索引,每条记录都需要一个额外的字节,还导致MyISAM中固定大小的索引变成可变大小的索引。
 

决定列的数据类型,我们应该遵循下面两步。

第一步、大致决定数据类型。——判断是数字,字符串还是时间等。这通常很直观。

第二步、确定特定的类型。

很多数据类型能够保存同类型的数据,但是我们要发现,他们在 存储的范围, 精度和物理空间之间的差别(磁盘或内存空间)。如:datetime和timestamp能保存同样类型的数据:日期和时间。——timestamp使用datetime一半的空间;能保存时区;拥有特殊的自动更新能力。 

品味数据类型
 
整数
 
1、存储类型——数据范围为-2^(n-1)到2^(n-1)-1,这里的n是所需存储空间的位数。
 类型名称 占用位数   数据范围
TINYINT  8  -2^7 ~ 2^7-1
 SMALLINT  16  -2^15~2^15-1
 MEDIUMINT  24  -2^23~2^23-1
 INT  32  -2^31~2^23-1
 BIGINT  64   -2^63~2^63-1

 2、unsigned属性表示不允许负数,并大致把正上限提高了一倍。如TINYINT UNSIGNED保存的范围为0到255而不是-127到128

 

3、MYSQL对整数类型定义宽度,比如int(1)和int(22)对于存储和计算是一样的。只规定了MYSQL的交互工具(如命令行客户端)用来显示字符的个数。

 

实数
 
实数有分数部分(小数部分)。
存储类型:FLOAT和DOUBLE,DECIMAL。
占用大小:FLOAT 4个字节,DOUBLE 8个字节。DECIMAL受到MYSQL版本影响,早期版本254个数字,5.0以上65个数字。
区别:1、FLOAT和DOUBLE支持标准浮点运算进行近似计算。
 2、DECIMAL进行DECIMAL运算,CPU并不支持对它进行直接计算。浮点运算会快一点,因为计算直接在CPU上进行。
  3、DECIMAL只是一个存储格式,在计算时会被转换为DOUBLE类型。
 4、DECIMAL(18,9)使用9个字节,小数点前4个字节,小数点1个字节,小数点后4个字节。
 5、DECIMAL只有对小数进行精确计算的时候才使用它,如保存金融数据。
 

 
字符串类型
 
1、varchar
(1)保存可变长字符串。
理解:比固定长度占用更少的存储空间,因为它只占用自己需要的空间。例外情况:使用ROW_FORMAT=FIXED创建的MyISAM表,它为每行使用固定长度的空间,可能会造成浪费。
(2)存储长度信息。如果定义的列小于或等于255,则使用1个字节存储长度值,假设使用latin1字符集,如varchar(10)将占用11个字节的存储空间。反过来,varchar(1000),则占用1002个字节的存储空间。
(3)节约空间,对性能有帮助。
(4)5.0版本以上,无论是取值还是保存,MySQL都会保留字符串末尾的空格。
只分配真正需要的空间
使用varchar(5)和varchar(200)保存'hello'占用空间是一样的。——这里应该指的是磁盘上的空间。
那么使用较短列有何优势?——巨大的优势
较大的列会使用更多的内存,因为MySQL通常会分配固定大小的内存块(如varchar(200)会用200个字符大小的内存空间)来保存值(然后对值进行trim操作,最后放入磁盘)或取值。——这对排序或使用基于内存的临时表尤其不好。

2、char
(1)固定长度。
(2)保存值时,去掉末尾的空格。

              咱们再看看varchar

(3)char常用于很短字符串或长度近似相同的字符串的时候很有用。如存储用户密码的MD5哈希值,它的长度总是一样的。
char优于varchar的地方?
1>> 对于经常改变的值,char优于varchar,因为固定长度行不容易产生碎片。——当最长长度远大于平均长度,并且很少发生更新的时候,通常适合使用varchar。
2>>对于很短的列,char的效率也是高于varchar的。如对于单字节字符集(如latin1),char(1)只会占用1个字节,而varchar(2)会占用2个字节(有一个字节用来存储长度的信息)。
 

 
 3、text
用于保存大量数据。
(1)InnoDB在它们较大的时候会使用“外部”存储区域来进行保存。——所以需要足够的外部存储空间来保存实际的值。
(2)排序方式不同于其他字符类型,不会按照完整长度进行排序,而只是按照max_sort_length规定的前若干个字节进行排序。
 

 
4、使用ENUM代替字符串类型
(1)ENUM列可以存储65 535个不同的字符串。
(2)以紧凑方式保存。根据列表中值的数量,把它们压缩到1到2个字节中。
(3)MySQL在内部把每个值都保存为整数,以表示值在列表中的位置。
(4)保留了一份“查找表”,来表示整数和字符串在表的.frm文件中的映射关系。
(5)ENUM字符列是固定的,添加、删除字符串须使用ALTER TABLE。
(6)使用案例:权限表中使用ENUM来保存Y值和N值。
 使用方法:

              

 

在对enum列使用order by的时候,是按数字排序的,而不是字符串排序。

 

日期和时间类型
 

DATETIME:保存大范围的值。封装格式:YYYYMMDDHHMMSS。——与时区无关,使用8字节存储空间。

TIMESTAMP:保存自1970年1月1日午夜(格林尼治标准时间)以来的秒数。——使用4字节存储空间。

通常使用TIMESTAMP,它比DATETIME更节约空间。有时人们把Unix的时间戳保存为整数值,但是这通常没有任何好处。——这种格式处理起来不太方便,我们并不推荐它。

 
 
 经验交谈
 
1、我们在为列选择数据类型的时候,不仅要考虑存储类型大小,还要考虑MySQL如何对它们进行计算和比较。例如:MySQL在内部把ENUM和SET类型保存为整数,但是在比较的时候把它们转换为字符串。
 
2、我们要在相关表中使用同样的类型,类型之间要精确匹配,包括诸如UNSIGNED这样的属性。
 
3、混合不同的数据类型会导致性能问题,即使没有性能问题,隐式的类型转换也能导致难以察觉的错误。
 
4、选择最小的数据类型要考虑将来留出的增长空间。如,中国的省份,我们知道不会有成千上万个,因此不必用INT。TINYINT就足够了,它比INT小3字节。
 
5、整数通常是最佳的数据类型,因为它速度快,并且能使用AUTO_INCREMENT。
 
6、要尽可能避免字符串做为列的数据类型,因为它们占用了很多空间并且通常必整数类型要慢。MyISAM默认情况下为字符串使用了压缩索引,这使查找更为缓慢。
 
总结
 
若有错误,望请大侠指教一、二,不胜感激!
 
参考文件:《高性能MYSQL》
 
推荐
 
 

本文链接

相关 [mysql 架构 优化] 推荐:

MYSQL 架构优化和索引之列设计篇

- - 博客园_首页
情况:如果你的表结构设计不良或你的索引设计不佳,那么请你优化你的表结构设计和给予合适的索引,这样你的查询性能就能提高几个数量级. ——数据越大,索引的价值越能体现出来. 我们要提高性能,需要考虑的因素:. 今天要讲的是表列的设计,暂不谈索引设计. 以下是数据储备脚本:主要是做表的建立和数据的插入——你也可以视情况修改表结构.

mysql优化

- - 数据库 - ITeye博客
公司网站访问量越来越大,MySQL自然成为瓶颈,因此最近我一直在研究 MySQL  的优化,第一步自然想到的是 MySQL 系统参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL  默认的系统参数能够让 MySQL运行得非常顺畅. 在Apache, PHP,  MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.

mysql优化

- - 数据库 - ITeye博客
      1.通过 show (session 或者 global) status 来查看( 当前连接 或者 数据库上次开机以来 )的服务器状态信息,默认是session.         例如:show status like '%com_%' : com_XXX表示XXX语句执行的总次数,这总次数是针对所有引擎的总和.

MySQL性能优化

- sun - IT程序员面试网
在笔试面试中,尤其是像百度,淘宝这些数据量非常大,而且用LAMP架构的公司,数据库优化方面就显得特别重要了. 此外,除了数据库索引之外,在LAMP结果如此流行的今天,数据库(尤其是MySQL)性能优化也是海量数据处理的一个热点. 下面就结合自己的经验,聊一聊MySQL数据库优化的几个方面. 首先,在数据库设计的时候,要能够充分的利用索引带来的性能提升,至于如何建立索引,建立什么样的索引,在哪些字段上建立索引,上面已经讲的很清楚了,这里不在赘述.

mysql 引擎优化

- - CSDN博客推荐文章
MySQL数 据库引擎取决于MySQL在安装的时候是如何被编译的. 要添加一个新的引擎,就必须重新编译MYSQL. 在缺省情况下,MYSQL支持三个引擎:ISAM、MYISAM和HEAP. 另外两种类型INNODB和BERKLEY(BDB),也常常可以使用. 如果技术高超,还可以使用MySQL++ API自己做一个引擎.

mysql参数优化

- - CSDN博客推荐文章
### 用来存放InnoDB的内部目录,对于大数据设置16M足够用. ### InnoDB 缓存总大小设置,一般设置为系统内存的70%-80%. ### 指定所有InnoDB数据文件的路径和大小分配. ### 文件读写io数设置:. ### InnoDB内核的并发线程数设置. ### 设置日值的大小.

Zabbix 的 MySQL 优化

- - SegmentFault 最新的文章
为 Zabbix 优化 MySQL. 标签(空格分隔): Zabbix MySQL Optimizing 优化. Aurimas Mikalauskas,原文是. Zabbix 和 MySQL. 在大型的 Zabbix 环境中,遇到的挑战大部分是 MySQL 以及更具体的说是 MySQL 磁盘 IO.

mysql优化方法

- - 数据库 - ITeye博客
通过show status和应用特点了解各种SQL的执行频率. 通过SHOW STATUS可以提供服务器状态信息,也可以使用mysqladmin extended-status命令获得. SHOW STATUS可以根据需要显示session级别的统计结果和global级别的统计结果. 以下几个参数对Myisam和Innodb存储引擎都计数:.

Mysql性能优化

- - 数据库 - ITeye博客
MySQL性能优化.   性能优化是通过某些有效的方法来提高MySQL的运行速度,减少占用的磁盘空间. 性能优化包含很多方面,例如优化查询速度,优化更新速度和优化MySQL服务器等.   数据库管理人员可以使用SHOW STATUS语句来查询MySQL数据库的性能. 语法:SHOW STATUE LIKE ‘value’;其中value参数是常用的几个统计参数.

MYSQL设计优化

- - CSDN博客推荐文章
本文将从各方面介绍优化mysql设计的一些方式. (1)定位需要优化的sql语句. 1)show status统计SQL语句频率. 对Myisam和Innodb存储引擎都计数的参数:. SHOW STATUS可以根据需要显示session级别的统计结果和global级别的统计结果. 1.Com_select  执行select操作的次数,一次查询只累加1;.