MySQL索引的查看创建和删除

1.索引作用
在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。
例如,有3个未索引的表t1、t2、t3,分别只包含列c1、c2、c3,每个表分别含有1000行数据组成,指为1~1000的数值,查找对应值相等行的查询如下所示。

SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3
此查询结果应该为1000行,每行包含3个相等的值。在无索引的情况下处理此查询,必须寻找3个表所有的组合,以便得出与WHERE子句相配的那些行。而可能的组合数目为1000×1000×1000(十亿),显然查询将会非常慢。
如果对每个表进行索引,就能极大地加速查询进程。利用索引的查询处理如下。
(1)从表t1中选择第一行,查看此行所包含的数据。
(2)使用表t2上的索引,直接定位t2中与t1的值匹配的行。类似,利用表t3上的索引,直接定位t3中与来自t1的值匹配的行。
(3)扫描表t1的下一行并重复前面的过程,直到遍历t1中所有的行。
在此情形下,仍然对表t1执行了一个完全扫描,但能够在表t2和t3上进行索引查找直接取出这些表中的行,比未用索引时要快一百万倍。
利用索引,MySQL加速了WHERE子句满足条件行的搜索,而在多表连接查询时,在执行连接时加快了与其他表中的行匹配的速度。
2. 创建索引
在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
1.ALTER TABLE
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。
2.CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。
3.索引类型
在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。
PRIMARY KEY索引和UNIQUE索引非常类似。事实上,PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY,因为一个表中不可能具有两个同名的索引。
下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

ALTER TABLE students ADD PRIMARY KEY (sid)

4. 删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

其中,前两条语句是等价的,删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

5.查看索引
mysql> show index from tblname;
mysql> show keys from tblname;
  · Table

  表的名称。

  · Non_unique

  如果索引不能包括重复词,则为0。如果可以,则为1。

  · Key_name

  索引的名称。

  · Seq_in_index

  索引中的列序列号,从1开始。

  · Column_name

  列名称。

  · Collation

  列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。

  · Cardinality

  索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。

  · Sub_part

  如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。

  · Packed

  指示关键字如何被压缩。如果没有被压缩,则为NULL。

  · Null

  如果列含有NULL,则含有YES。如果没有,则该列含有NO。

  · Index_type

  用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

  · Comment

MySQL表设计优化与索引 (十)

Bit-Packed Data Types

MySQL有一些存储类型使用一个值中的一些单个的比特位来紧凑的存储数据。纯技术上将,不管是底层的存储格式还是操作,所有这些类型都是字符串类型。

BIT

MySQL5.0以前, BIT只是TINYINT的同义词而已。但是在MySQL5.0以及之后的版本,BIT是一个完全不同的数据类型了, 有着自己的一些特点, 这里讨论一些新的行为和属性:

可以用BIT字段在单列里面来存储一个或多个true/false值, BIT(1)定义一个了只包含单个比特位的字段, BIT(2)是存储2个比特位的字段, 如此类推;BIT字段的最大长度可以是64个比特。

BIT类型的行为与存储引擎有关。MyISAM把一些列为了存储的目的打包到一起, 所以17个单独的BIT列需要17个比特来存储(假定这些列都不允许NULL),MyISAM会近似算成3个字节来存储。其他的一些存储引擎, 比如Memory和InnoDB, 把每个列都用有足够长度存储这些比特位的最小整数来存储,所以无法节省存储空间。

* TIMESTAMP类型的一些行为的规则比较复杂并随着不同的MySQL版本而变化,所以在使用时应当确认是自己所期望的行为。通常, 在对TIMESTAMP的列做了改变后通过查看SHOW CREATE TABALE的结果来确认是一个的主意。

MySQL把BIT当做字符串类型, 而不是数据类型。当检索BIT(1)列的值, 结果是一个字符串而内容是二进制位0或1, 而不是ASCII值”0″或”1″.然而, 如果在一个数值上下文检索的话, 结果是比特串转化而成的数字。 当需要与另一个值进行比较时, 记住这一点。比如, 如果存储值’00111001′(是57的二进制表示)到一个BIT(8)的字段中然后检索出来,得到的是字符编码值为57字符串, 而这值就是“9”的ASCII编码。但是在数值环境中, 得到的是值57:s

mysql> CREATE TABLE bittest(a bit(8));

mysql> INSERT INTO bittest VALUES(b’00111001′);

mysql> SELECT a, a + 0 FROM bittest;

+——+——-+

| a | a + 0 |

+——+——-+

| 9 | 57 |

+——+——-+

这非常容易引起混淆, 所以我们提醒小心使用BIT类型。对于大多数应用程序来说,避免使用这个类型比较的好。

如果想在单个比特位的存储空间中存储true/false值的另一个选择是使用可以为NULL的CHAR(0)列。这个列能够存储NULL和长度为0的空串。

SET

如果需要存储多个true/false的值, 可以考虑把多个列放到一个MySQL所支持的SET数据类型,而MySQL内部通过一些比特位来表示的。这种类型有效的使用存储空间, MySQL也有一些函数如FIND_IN_SET( )和FIELD( )来方便查询。 最主要的缺点是改变列定义的开销: 需要ALTER TABLE, 而这个操作在一个大表上则是开销非常大的(参考后面有关于替换方法的讨论)。一般来说, 也无法在SET列上使用索引。

Bitwise operations on integer columns

整型列上的位操作

SET类型的一个替代办法是把一个整数当做一些比特位的集合。 比如,可以把一个TINYINT数当做8个比特位, 用位运算来操作其中的比特位, 可以通过在应用程序中位每一个比特位定义命名常数来简化理解。

这种方法相对于SET的主要的好处是可以不需要ALTER TABLE就能改变枚举值。缺点是查询写起来麻烦且不容易理解(当第5个比特位为1是什么意思?)。有些人喜欢使用位操作而有些人不喜欢,因而是否使用这个技巧很大程度上是个人口味的问题。

把比特位打成包的一个例子应用程序是存储权限的访问控制列表(ACL).每个比特或SET元素表示一个CAN_READ, CAN_WRITE或者CAN_DELETE之类的值。 如果使用SET列, 需要在MySQL的列定义中存储比特到值得映射关系; 如果使用整数列, 则需要在应用中存储这个映射关系。下面是一些使用SET列的查询语句:

mysql> CREATE TABLE acl (

-> perms SET(‘CAN_READ’, ‘CAN_WRITE’, ‘CAN_DELETE’) NOT NULL

-> );

mysql> INSERT INTO acl(perms) VALUES (‘CAN_READ,CAN_DELETE’);

mysql> SELECT perms FROM acl WHERE FIND_IN_SET(‘CAN_READ’, perms);

+———————+

| perms |

+———————+

| CAN_READ,CAN_DELETE |

+———————+

如果使用整数, 则大概会以如下的方式处理;

mysql> SET @CAN_READ := 1 << 0,

-> @CAN_WRITE := 1 << 1,

-> @CAN_DELETE := 1 << 2;

mysql> CREATE TABLE acl (

-> perms TINYINT UNSIGNED NOT NULL DEFAULT 0

-> );

mysql> INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);

mysql> SELECT perms FROM acl WHERE perms & @CAN_READ;

+——-+

| perms |

+——-+

| 5 |

+——-+

使用了变量来存储值, 不过在代码中可以使用常数来代替。

 

MySQL表设计优化与索引 (九)

日期和时间类型

MySQL有多个表示各种日期和时间值的数据类型, 比如YEAR和DATE. MySQL存储时间的最精确粒度是秒。 然而, 能做微妙粒度的临时计算, 我们会列出一些绕过存储引擎限制的一些方法。

许多时间类型都没有可替换类型, 因而不存在什么是最佳选择这么一说。 唯一的问题是当需要一起存储日期和时间时如何做?MySQL提供了两个非常类似的数据类型来做这个事情: DATETIME和TIMESTAMP. 多大多数应用来说, 两个都行, 但是在某些场合, 一个可能比另一个更合适:

DATETIME

这个类型能够存储很大范围的值, 从1001年到9999年, 准确到秒的精度。它把日期和时间打包成整数以YYYYMMDDHHMMSS 格式表示, 与时区无关。用8个字节的存储空间。

缺省情况下, MySQL以可排序,无歧义的格式显示DATETIME值, 比如 2008-01-16 22:37:08, 这是ANSI下标准显示日期时间的方式。

TIMESTAMP

如名字所指出的,TIMESTAMP 类型存储从1970年1月1日(格林威治时间)到目前为止经过的秒数 –与UNIX时间戳一样。TIMESTAMP 使用4个字节来存储值, 所能表示的范围以比DATETIME要小: 从1970年到2038年。MySQL提供了 FROM_UNIXTIME( ) 和 UNIX_TIMESTAMP( ) 两个函数来进行Unix时间戳与日期类型的相互转换。

新版本的MySQL中TIMESTAMP 采用了与DATETIME 一样的格式来表示值,但是老版本的MySQL没有在各个部分件显示分隔符。 这只是一个显示格式的区别而已,TIMESTAMP 的存储格式在各个版本与DATETIME一致。

TIMESTAMP 对值的显示与时区相关,MySQL服务器,操作系统和客户端连接都有相应的时区设置。

因此,对于与GMT有5个小时时差的东部时间,一个值为0的TIMESTAMP 类型实际显示的内容可能会是1969-12-31 19:00:00。

TIMESTAMP 也有一些DATETIME所不具备的一些属性。 缺省情况下, 插入记录时,MySQL会把第一个没指定具体值得TIMESTAMP 类型的字段自动设置为当前值,在修改时, 如果没有在修改语句中显式的指定值, 第一个DATESTAMP类型的字段的值也会被更行为当前值。也可以通过配置修改修改和插入记录时对TIMESTAMP 列的处理行为。最后, TIMESTAMP列缺省不为NULL, 这与其他数据类型不一样。

除了一些特殊行为,在一般情况下,如果能够使用TIMESTAMP,就使用它, 因为它比DATETIME的空间效率要高。有时候,有人把Unix时间戳存储成整数, 但是实际上没有任何好处,因为格式转换很不方便,我们不推荐这么做。

如果需要存储比秒精度更高的日期和时间数据怎么办呢?MySQL当前并没有提供合适的数据类型, 但是可以根据需要选择自己的存储格式:可以使用BIGINT类型存储毫秒精度的数据,或者使用DOUBLE类型,把秒后面的数值当做小数点后面的小数部分。 这两种方法都不错。

 

MySQL表设计优化与索引 (八)

使用ENUM代替字符串类型

有时候, 可以通过使用ENUM来代理常规的字符串类型。一个ENUM列能够存储65535个不同的字符串值,MySQL非常紧凑的存储这些值,会根据值列表把这些值存储到1到2个字节中。通过在表的.frm文件中保存一个数字到字符串的对应关系来保存一个”查询表”, 它把每个值存储成一个表示值在字段定义列表中的某个位置的整数。 下面是几个例子;

mysql> CREATE TABLE enum_test(

-> e ENUM(‘fish’, ‘apple’, ‘dog’) NOT NULL

-> );

mysql> INSERT INTO enum_test(e) VALUES(‘fish’), (‘dog’), (‘apple’);

这3列实际上存储的是整数, 而不是字符串。 能够通过在数字检索上下文中查看到这些值的两面属性:

mysql> SELECT e + 0 FROM enum_test;

+——-+

| e + 0 |

+——-+

| 1 |

| 3 |

| 2 |

+——-+

如果指定数字当做ENUM常量的话, 这个两面性太容易引起混淆了。建议不要这么做。

另外一个令人奇怪的是ENUM字段通过整数值来排序, 而不是字符串本省:

mysql> SELECT e FROM enum_test ORDER BY e;

+——-+

| e |

+——-+

| fish |

| apple |

| dog |

+——-+

通过指定期望的ENUM数字的排序顺序来解决这个问题。也可以通过在查询中显式的使用FIELD()来指定排序顺序, 但是这回使得MySQL在排序中无法使用索引:

mysql> SELECT e FROM enum_test ORDER BY FIELD(e, ‘apple’, ‘dog’, ‘fish’);

+——-+

| e |

+——-+

| apple |

| dog |

| fish |

+——-+

ENUM最大的缺点是字符串列表是固定的,增加或者删除一个字符串需要使用ALTER TABLE。因而, 如果列表中允许的字符串值可能会变化的话, 使用ENUM可能不是一个好的主意。MySQL在它自己的权限表中使用了ENUM来表示Y和N.

因为MySQL把每个值存储整数, 不得不在需要的时候做一些查询工作来转化成字符串表示,ENUM有一些开销的。这些有可能会通过他们相对较小的大小来缓冲, 但并不总是这样。特别是, 把CHAR或者VARCHAR列并成一个ENUM列比并成CHAR或者VARCHAR列要慢。

为了演示一下,我们对MySQL在一个应用中的一个表上做join做性能测试. 这个表有一个很宽的主键:

CREATE TABLE webservicecalls (

day date NOT NULL,

account smallint NOT NULL,

service varchar(10) NOT NULL,

method varchar(50) NOT NULL,

calls int NOT NULL,

items int NOT NULL,

time float NOT NULL,

cost decimal(9,5) NOT NULL,

updated datetime,

PRIMARY KEY (day, account, service, method)

) ENGINE=InnoDB;

这个表包含了大概110,000条记录,大小只有10M, 所以能够全部放在内存中。service列包含有5个不同的值, 平均长度是4个字符, method列包含有71个值, 平均长度20个字符。

我们对这个表做了一个拷贝, 把service和method字段转化成ENUM类型,如下:

CREATE TABLE webservicecalls_enum (

… 省略 …

service ENUM(…values omitted…) NOT NULL,

method ENUM(…values omitted…) NOT NULL,

… 省略 …

) ENGINE=InnoDB;

然后测试联合两个表的主键列来测试性能,使用的查询如下:

mysql> SELECT SQL_NO_CACHE COUNT(*)

-> FROM webservicecalls

-> JOIN webservicecalls USING(day, account, service, method);

然后改变一下查询, 以不同的组合来联合VARCHAR和ENUM字段,表3-1中列出了结果。

表3-1. 联合VARCHAR和ENUM列的

Test Queries per second

VARCHAR joined to VARCHAR 2.6

VARCHAR joined to ENUM 1.7

ENUM joined to VARCHAR 1.8

ENUM joined to ENUM 3.5

把列转化成ENUM后, 联合速度变快了。但是联合ENUM和VARCHAR列慢一些。在这个例子中, 只要不需要与VARCHAR列进行联合,看起来转化成ENUM是一个不错的注意。

而且, 做类型转换有另外一个好处: 从SHOW TABLE STATUS结果着那个的Data_length列的值来看,把这两列换成ENUM后, 表只有原来大小的1/3了。 在某些情况下, 即使需要做与VARCHAR列的联合, 也是有好处的。 在转换后,主键的大小也只有原来的的一半。 因为这是一个InnoDB表, 如果还有其他索引的话,减少主键的大小可以使得索引大大减小。

 

MySQL表设计优化与索引 (七)

如何避免磁盘临时表

因为内存储引擎不支持TEXT和BLOB数据类型, 使用到BLOB和TEXT列的查询和使用隐式临时表的查询将不得不使用MyISAM的磁盘临时表, 即使只有很少的几条记录。 这可能导致很严重的性能开销。即使把MySQL配置成在RAM盘上使用临时表, 也会用到一些开销大的系统调用(Maria存储引擎应该能通过把所有的东西, 而不只是索引,缓存在内存中而在一定程度上缓解这个问题)。最好的解决办法是避免使用BLOB和TEXT类型, 除非费用不可。

.

如果无法避免使用BLOB和TEXT类型, 应该能够通过使用ORDER BY SUBSTRING(column, length)

的办法来把这些类型的值转化成字符串,这样不会妨碍使用内存临时表。另外, 确认一下的所使用的子串的长度是否短到不会导致临时表的大小不会超过 max_heap_table_size 或 tmp_table_size, 否则,还是会生成MyISAM的磁盘临时表。

如果EXPLAIN结果中的”Extra”列里面包含”Using temporary”, 那么查询会使用一个隐式的临时表。

 

MySQL表设计优化与索引 (六)

多版本并发控制

Multiversion Concurrency Control

大部分的MySQL的存储 引擎,比如InnoDB,Falcon,以及PBXT并不是简简单单的使用行锁机制。它们都使用了行锁结合一种提高并发的技术,被称为MVCC(多版本并 发控制)。MVCC并不单单应用在MySQL中,其他的数据库如Oracle,PostgreSQL,以及其他数据库也使用这个技术。

MVCC避免了许多需要加锁的情形以及降低消耗。这取决于它实现的方式,它允许非阻塞读取,在写的操作的时候阻塞必要的记录。

MVCC保存了某一时刻数据的一个快照。意思就是无论事物运行了多久,它们都能看到一致的数据。也就是说在相同的时间下,不同的事物看相同表的数据是不同的。如果你从来没有这方面的经验,可能说这些有点令人困惑。但是在以后这个会很容易理解和熟悉的。

每个存储引擎实现MVCC方式都是不同的。有许多种包含了乐观(optimistic)和悲观(pessimistic)的并发控制。我们用简单的InnoDb的行为来举例说明MVCC工作方式。

InnoDB实现MVCC的方法是,它存储了每一行的两个额外的隐藏字段,这两个隐藏字段分别记录了行的创建的时间和删除的时间。在每个事件发生的时 候,每行存储版本号,而不是存储事件实际发生的时间。每次事物的开始这个版本号都会增加。自记录时间开始,每个事物都会保存记录的系统版本号。依照事物的 版本来检查每行的版本号。在事物隔离级别为可重复读的情况下,来看看怎样应用它。

SELECT

InnoDB检查每行,要确定它符合两个标准。

InnoDB必须知道行的版本号,这个行的版本号至少要和事物版本号一样的老。(也就是是说它的版本号可能少于或者和事物版本号相同)。这个既能确定事物开始之前行是存在的,也能确定事物创建或修改了这行。

行的删除操作的版本一定是未定义的或者大于事物的版本号。确定了事物开始之前,行没有被删除。

符合了以上两点。会返回查询结果。

INSERT

InnoDB记录了当前新增行的系统版本号。

DELETE

InnoDB记录的删除行的系统版本号作为行的删除ID。

UPDATE

InnoDB复制了一行。这个新行的版本号使用了系统版本号。它也把系统版本号作为了删除行的版本。

所有其他记录的结果保存是,从未获得锁的查询。这样它们查询的数据就会尽可能的快。要确定查询行要遵循这些标准。缺点是存储引擎要为每一行存储更多的数据,检查行的时候要做更多的处理以及其他内部的一些操作。

MVCC只能在可重复读和可提交读的隔离级别下生效。不可提交读不能使用它的原因是不能读取符合事物版本的行版本。它们总是读取最新的行版本。可序列化不能使用MVCC的原因是,它总是要锁定行。

下面的表说明了在MySQL中不同锁的模式以及并发级别。

 

锁的策略 并发性 开销 引擎
最低 最低 MyISAM,Merge,Memory
NDB Cluster
行和MVCC 最高 最高 InnoDB,Falcon,PBXT,solidD

 

 

MySQL表设计优化与索引 (五)

当存储一个CHAR值时, Mysql会除去尾随空间, 这个行为有点让人困惑, 用一个具体的例子来看一下: 首先 ,创建一个只有一个CHAR(10)字段的表并存储一些值在里面:

mysql> CREATE TABLE char_test( char_col CHAR(10));

mysql> INSERT INTO char_test(char_col) VALUES

-> (’string1′), (‘ string2′), (’string3 ‘);

然后检索这些值, 尾部空间被除掉了:

mysql> SELECT CONCAT(“‘”, char_col, “‘”) FROM char_test;

+—————————-+

| CONCAT(“‘”, char_col, “‘”) |

+—————————-+

| ’string1′ |

| ‘ string2′ |

| ’string3′ |

+—————————-+

如果存储同样的值在VARCHA(10)的字段里, 在检索的时候得到的结果如下:

mysql> SELECT CONCAT(“‘”, varchar_col, “‘”) FROM varchar_test;

+——————————-+

| CONCAT(“‘”, varchar_col, “‘”) |

+——————————-+

| ’string1′ |

| ‘ string2′ |

| ’string3 ‘ |

+——————————-+

数据的存储是存储引擎相关的, 并不是所有的存储引擎用同样的方式来处理定长和变长类型。Memory存储引擎使用定长的行, 因而它分配尽可能多的空间给变长字段来存储数据。 而Falcon则使用变长字段来存储, 即使对于CHAR字段也是如此。 但是填充和去尾是一致de 这是因为这个是 油MySql服务器自己处理的。

CHAR和VCHAR的兄弟类型分别是存储二进制数据的BINARY和VARBINARY, 二进制字符串与普通的字符长相似, 不过他们是以字节存储的, 而不是字符,填充也不一样 , Mysql用\0(0 字节)而不是空格填充二进制数据, 而在检索时不会把后面的填充值去掉。

对于需要存数二进制数据的活着需要Mysql对值进行字节的比较而不是基于字符的比较时, 二进制数据类型非常哟用。基于字节的比较的优势不只是大小写不敏感的好处, Mysql在比较二进制数据时, 通过字节表示的数值比较的方式进行的, 因为二进制数据相对与基于字符的比较更简单, 也更快。

记住长度单位是字符, 而不是字节, 一个多字节字符集要多个字节来存储。

 

MySQL表设计优化与索引 (四)

String Types(字符串类型)

字符串类型

Mysql支持多种字符串类型的变体。 这些数据类型在4.1和5.0版本中有较大的变化, 这使得这些数据类型变得更加复杂。 从Mysql4.1起, 每个字符串类型能有自己的字符集和针对这些字符集的排序规则集 (更多的关于排序规则的主题, 参考第5章), 这对于性能有较大的影响。

VARCHAR和CHAR类型

VARCHAR和CHAR是两种最主要的存储字符串的数据类型, 不幸的是,的确有些困难解释这两种数据类型在磁盘和内存中是如何存储的, 因为这是与存储引擎相关的(比如, Falcon的差不多所有数1是, 请参考相应的存储引擎文档。

先看看VARCHAR和CHAR值的典型的磁盘存储方式。 同一种存储引擎对VARCHAR和CHAR在磁盘和内存中的存储格式可能是不一样的, 当从一个地方读取并存到另一个地方时, 服务器可能需要做一些转换工作。 两种数据类型的一些比较:

VARCHAR存储可变长度的字符串, 它应该是最为常见的数据类型了。 相对于固定长度的数据类型, 它需要的存储恐空间相对较少, 因为它按需分配存储空间 (比如, 存储短字符串的时候就用少的空间). 例外的地方在MyISAM中建立ROW_FORMAT=FIXED的表, 这种表对于每一行都使用相同数量空间来存储, 因而会导致空间浪费。VARCHAR 使用1或2个字节来记录值的长度: 如果字段的长度小于255个字节, 用1个字节, 否则用2个字节。假定使用latin1字符集, 一个VARCHAR(10)的字段值使用11个字节的存储空间, 1个VARCHAR(1000)的字段值使用1002字节的空间, 因为需要2个字节来存储长度信息。

VARCHAR有助于改善性能, 因为能节省空间。 但是, 因为记录的长度是可变的, 当修改的时候因为记录可能变长会导致一些额外的工作。 如果一列增长了而原来的存储空间不能存储修改后的值, 具体的增长策略是与具体的存储引擎相关的。 比如, MyISAM可能导致分行, 而innodb则可能会导致分页来处理。 其他的一些引擎则可能不会在原来的地方修改数据。

一般来说, 当某个字段的最大长度远远大于平均长度时; 或者很少修改字段因为碎片化不会是一个问题; 或者使用一个复杂的字符集合比如UTF-8, 而每个字符使用变长的字节来表示时, 使用VARCHAR来存储时值得的。

CHAR是固定长度的, Mysql总是分配足够的空间来存储指定数目的字符。当存储一个CHAR值时, Mysql会除去尾随空间(在4.1以及之前的版本, VARCHAR也是这么处理的, VARCHAR和CHAR逻辑上市一样的,只是存储格式不一样而已.) 当比较时, 字段后面会根据需要垫加一些空格来进行比较。

当需要存储非常短的字符串时; 或者所有的字段值差不多同样长时, CHAR非常有用。比如, 用CHAR来存储用户密码的md5结果值, 这些结果值是等长的。 当字段值长度变化频繁时, CHAR比VARCHAR要好, 因为固定长度的字段不容易导致碎片化。 对一些字段值很短的字段, CHAR也比VARCHAR高效, 用CHAR(1)来存储Y或者N只需要一个字节长度来存储数据, 而VARCHAR(1)则需要2个字节,因为需要一个字节来表示长度。

 

MySQL表设计优化与索引 (三)

Real Numbers

实数

实数是具有小数部分的数字, 当然, 实数不是专门用来表示小数的, 也可以用DECIMAL来存储那些无法用INTERGER来存储的大整数。 Mysql同时支持准确的和非准确的类型。

FLOAT和DOUBLE类型支持近似的标准浮点数数学计算。 如果需要知道准确的浮点数计算, 参考具体平台的浮点数计算规则。

DECIMAL类型是用来存储准确的小数, 在mysql 5.0以后的版本, DECIMAL支持准确的数学计算。在Mysql4.1 及以前的版本中, MUMERIC类型也是用浮点计算来完成运算的, 因此可能会出现一些由于精度损失而出现的一些不准确的结果, 在这些版本的mysql中, DECIMAL是一种”存储”概念类型。

在5.0以及以后的版本, mysql自己来进行DECIMAl计算, 因为cpu没有对DECIMAL的直接支持。 当然, 浮点计算在某种程度上相对快些, 因为CPU是直接支持浮点计算的。

浮点类型和DECIMAl类型允许指定精度。对于DECIMAL字段, 能够指定小数点前和后允许的最多的数字个数, 这个关系到数值的存储空间的消耗。Mysql5.0以及以后的版本中, 所有的数字位数以二进制字符串的方尺存储的(每4个字节9个数字)。 例如, DECIMAL(18, 9) 数据类型的小数点两边都存储9个数字符号, 因此共用9个字节, 4个字节存储小数点之前的数字符号, 1个字节存储小数点本身, 4个字节存储小数点之后的数字符号。

在MYSQL 5.0及之后的版本, 一个DECIMAL数字能存储65位数字, 早期的mysql版本有254位数字的限制, 并把数字打包存储成字符串(每个数字一个字节)。 在运算中, 这些版本的mysql一般不需要使用到这么多的空间, 因为DECIMAL只是表示存储格式, 在计算中是通过转换成DOUBLE类型的数字来进行的。有多种方式指定浮点类型字段需要的精确度, 这使得Mysql会悄悄的选择另外一种不同的数据类型或近似值来存储。 这些精确度修饰符不是标准的, 所以建议通过选择数据类型而不是精度。

一般情况下, 存储相同范围数值的浮点数类型比DECIMAL类型所使用的空间要小些; 1个FLOAT字段用4个字节的存储,DOUBLE用8个字节的来存储更高精度和更大范围的数值。 其实对于整数类型来说, INTERGER指定的其实是存储类型, mysql其实用的是DOUBLE来进行存储和内部计算的。 因为DECIMAL需要占更多的存储空间和需要更多的计算资源, 因此除非在一些需要准确计算结果的场合–比如存储财务数据外, 尽量不是使用。

 

MYSQL数据库表设计与优化(二)

决定数据类型的第一步是定义所存数数据的分类: 数值型, 字符串型还是临时型等;除了一些特别的并不是那么直观的外, 这通常是很直观的。

接下来是选择具体的数据类型, 许多mysql的数据类型能存储同一种数据, 但是在可存储的数据范围, 准确度或者存储空间有些不同。 有些数据类型可能还有一些特殊的行为属性。比如, DATETIME和TIMESTAMP都能存储日期和日期, 而且都是准确到秒; 然而, TIMESTAMP占的数据空间只有DATETIME的一半, 而且是时区敏感的,同时有一些自动修改的机制。 另一个方面, 它能表示的数据范围要小些, 有些属性在某些时候是比较麻烦的。

这里只讨论了一些基本的数据类型, 实际上, mysql为了考虑兼容性, 一些数据类型是其他数据类型的别名而已, 例如,INTEGER, BOOl和NUMERIC, 他们是一样的, 只是名字不同而已; 这些数据类型有时候容易叫人困惑, 但倒是多性能的影响不大。

整数

数字分为整数和实数, 如果存储整型数,使用正型数类型:TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT, 分别是8, 16, 24, 32, 和64位, 能存储的数值范围是–2(N–1) to 2(N–1)–1, 其中N是位数。

整数有一个可选的属性UNSIGNED, 使用这个属性使得无法表示负数, 而可表示的数值上限大体增加了1倍, 比如, TINYINT UNSIGNED 能存储0 到255之间的值, 而不是TINYINT的–128 到 127.

有符号和无符号数使用的数据存数空间是一样的, 性能也差不多, 所以在使用时根据数据范围选择即可。

数据类型的选择通常会影响mysql数据在磁盘,内存中的存储, 然而,正型数的计算通常是以64为的BIGINT进行的,即使是在32位机器上。 (一些统计函数是例外的情况,这些函数通常使用DECIMAL和DOUBLE进行计算 )

mysql允许在选择整型的时候指定一个宽度,如INT(11), 这对于许多应用来说是没有实际意义的:它并不限制值的范围, 只是简单的用来在交互性工具, 比如命令行上指示数值输出的字符宽度。 从存储和计算的角度来看, INT(1)和INT(20)是一样的。

Falcon存储引擎与其他的引起不一样, MySQL AB按照自己的格式来存储整数, 用户多数据的存储大小和格式没有任何控制。一些第三方的存储引擎, 如Brighthouse, 也有自己的存储格式和压缩方法。

 

MYSQL数据库表设计与优化(一)

优化糟糕设计的表结果或者索引能很大程度改进mysql的性能。 如果需要高性能, 那么就需要根据不同的操作需求精心设计表结构和索引, 这当然需要对各种查询做出性能需求评估, 因为改变一个查询或者一部分表结构设计会在其他地方引的性能。 优化通常需要做出取舍, 例如, 通过增加索引来加速查询速度会减慢修改速度; 类似的, 一个非完全按范式设计的表结构能使某些查询速度提高, 但可能会导致其他的效率降低; 增加计数器或者汇总表能有效的优化一些查询, 但是维护代价也是不菲的。

有时候, 作为开发人员的你需要做超出开发人员职责的事情去弄清楚递到你面前的开发任务, 有时候, 那些不太了解数据库系统原理的人提出的需求可能不会考虑需求的性能影响, 如果你能清楚的告诉他们这些对数据库造成大压力的小功能需要导致硬件成本成倍增加的话, 他们可能会考虑砍掉一些需求。

表设计和索引优化要求设计开发人员了解系统构架和实现细节, 设计人员需要去理解整个系统和可能的各种影响因素。 这一章依次重点讨论数据类型, 索引策略和范式化, 最后对一些数据引擎做一些简单的介绍。

Choosing Optimal Data Types

选择正确的数据类型

Mysql 提供多种数据类型的支持,选择正确的数据类型对能否得到高性能的设计影响很大。 以下一些简单的指导规则能帮助您对各种数据类型的设计决策做出更好的选择:

小的比大的好

一般来说, 在保证正确性的前提下, 尽量使用最小的数据类型来存储和表示数据。 小的数据类型一般比大的更快, 因为小的数据类型占用的磁盘空间, 内存和cup缓存都相对小, 需要的cpu处理也要相对少; 这个原则很重要, 但是设计的时候也不要低估需要存储的数据的数据范围, 毕竟, 如果后来发现需要在多个地方改变某个数据的数据类型的话, 将是一件痛苦耗时的事情。 如果正在多个可选数据类型之间犹豫不绝的话, 就选那个能够满足数据范围需求的最小的数据类型吧(如果系统比较简单, 数据量不大,或者正处于早期设计阶段, 后面是很容易修改的)

简单的就是好的

简单的数据类型需要的cup处理周期更少, 比如, 对整数的处理比字符串处理更容易, 因为字符集和排序规则使得字符串比较复杂化了, 两个典型例子是: 使用mysql的内嵌数据类型来存储日期和时间而不是字符串, 使用整数存储Ip地址而不是字符串。 后面会对更详细的讨论这条规则。

尽量避免NULL数据类型

如果可能, 尽可能把字段定义成NOT NULL。 许多表包含一些字段允许空的字段, 即使应用需求不需要存储null的数据, 这样做的原因知识因为允许字段为NULL是缺省的。您应该注意把字段设计生非NULL的, 除非真的是需要存储NULL值。 在查询优化时, mysql很难优化需要引用到能包含NULL值的字段的查询, 因为允许为null的字段使得索引, 索引统计和值比较更复杂。

允许NULL的字段会占用掉更多的存储空间和花掉更多的cpu处理, 当为一个可为空的字段建立索引时, 需要为每项分配一个额外的字节, 在myisam中, 甚至会使得一个固定大小的索引(比如建立在单个整数的缩影)变成一个变长大小的索引。 即使遇到不需要存储值到某个字段, 也应当考虑不使用NULL, 而是考虑使用0, 一个特殊值或者空串来代替。 把字段从NULL改成NOT NULL的性能改善通常不是很大, 所以, 除非确实证明NULL字段在引起性能问题, 一般不需要把超找并修改NULL字段为NOT NULL字段当做一件大事来对待; 但是, 在做设计的时候, 注意尽量把需要索引的字段设计成不允许为空的。