MySQL数据类型整理和索引介绍

问:数据库有没有建过索引?
答:没有。
问:居然没有建过索引,查询难道不用索引么!!!
答:下一题~~~

  数据库索引对于程序员来说是透明的,数据库建立索引之前后,你的SQL语句都可以正常运行得到正确的结果,索引的运用只是数据库引擎工作时候的优化手段而已。但是,这不是意味着数据库索引的相关工作仅仅是数据库设计或者DBA的事情,如果一个程序员如果对数据库索引有所了解,还是可以大大优化程序员增删查改语句的执行效率的,以免低效查询语句成为拖累整个系统性能的Black Sheep。
  本文对MySQL支持的数据类型,以及索引类型、建立和优化的知识进行整理,现在数据库引擎默认都是InnoDB的,目前MySQL应用于生产环境时候,应该都是用的这个引擎吧。

一、MySQL的数据类型

1.1 数字类型和时间类型

  数字类型和通常的编程语言一样算是最简单明了了,主要差异在于各个类型的取值范围大小限制,和对存储空间字节数的需求。数字类型当然是在满足业务需求的情况下越短越好:一方面MySQL每行有着每行最多65535字节长度的总限制,同时更宽的数据类型意味着对CPU、内存、磁盘I/O带来压力。

1.1.1 MySQL支持的定点数字类型和占用字节数分别是

类型 长度
TINYINT 1
SMALLINT 2
MEDIUMINT 3
INTEGER 4
BIGINT 5

  在数据库设计的时候,常常看到这些整形有个前缀长度,其实这对其类型本身的存储长度和精度没有影响,只会关系到某些交互式工具显示出来的字符个数,主要体现在设置了zerofill描述符后的显示宽度。

1.1.2 MySQL支持的浮点(实数)类型和占用字节数为

类型 长度
FLOAT 4
FLOAT(p) [0,24] 4
FLOAT(p) [25,53] 8
DOUBLE,REAL 8

  计算机的浮点运算都是不精确的,如果要实现精确浮点运算,就需要使用DECIMAL类型。

1.1.3 时间类型

  常被使用的是DATE、DATETIME和TIMESTAMP类型,其表示的范围为:

DATE (3字节):’1000-01-01’ to ‘9999-12-31’
DATETIME (8字节):’1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
TIMESTAMP (4字节):’1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC

  虽然TIMESTAMP存储的范围比DATETIME要小,但是空间利用率也最高,如果不考虑时间的存储范围的话优先推荐使用TIMESTAMP。TIMESTAMP内部是将时间转化为UTC时间存储的,所以客户端读取到的结果跟和时区有一些关系,而DATETIME的读取结果和写入内容完全一致的。
  DATETIME的默认值是NULL,而TIMESTAMP的可以设置默认值为DEFAULT CURRENT_TIMESTAMP,而且可以选择某一个TIMESTAMP的列通过ON UPDATE CURRENT_TIMESTAMP使其在该行记录其他任何一个字段发生更新的时候自动将其更新为当前时间。不过在我们的业务中也发生过数据库UPDATE字段的值和之前的值没有变化,记录没有实质发生改变导致TIMESTAMP没有自动更新的情况,这个时候应该不依赖于自动更新机制而将该列值手动更新为CURRENT_TIMESTAMP。
  对于时间上MySQL支持的时间精度最高为1s,如果更精确的存储,就必须自己定义存储格式了。

1.2 字符串类型

  MySQL中的字符串类型比较的复杂,各个字符串类型的差别不仅体现在存储时候的空间占用上,对存取时候字段某位的strip和padding还有差异。
  对于类型中的CHAR/VARCHAR/TEXT是跟本地字符集相关的,这会影响到字段实际占用的字节数、以及字符的比较排序等操作。

1.2.1 CHAR(M)/VARCHAR(M)

  长度限制参数M表示的是本地字符集的本地字符个数而不是bytes数目,比如对于UTF8编码,每个本地字符其实际占用的byte长度可能是3或4倍的本地字符长度。比如VARCHAR(255)类型,如果每个本地字符占用两个字节,那么其需要的存储空间最大为255x2+2。
  CHAR的M被限制在最大255,而VARCHAR的M理论上受限于Row Size的长度(65,535bytes),且实际存储时候会附加1~2字节的前缀表示数据实际长度。如果strict SQL模式没有被打开,那么当插入数据超过声明长度限制的时候,数据将会被截断并产生警告信息,在strict SQL模式下将会出错。
  CHAR类型在存储的时候,会在右端padding SPACE到指定的M长度,当取该字段的时候,所有末尾的SPACE都将会被strip掉然后返回;VARCHAR不会对进行SPACE进行padding以及strip操作,存储什么样的数据就会返回什么样的数据。
  对于CHAR/VARCHAR/TEXT类型,在进行字符串比较的时候,(SQL语句参数中的字符串)结尾的空格都是不参与比较的,但是对于LIKE语句,检索的时候结尾的空格是考虑在内的。

1.2.2 BINARY(M)/VARBINARY(M)

  BINARY/VARBINARY在操作的时候,参考的是byte streaming而不是charaset streaming,所以其长度限制参数M表示的是byte数目,在比较的时候也是直接的数字大小比较(而非本地字符集方式比较)。
  BINARY在插入的时候,会使用0x00(而非SPACE)padding到长度M,取值的时候不会进行strip尾部空字符的操作(意味着取出来的长度一定是M);VARBINARY则是保证原样存取的。

1.2.3 BLOB/TEXT

  分别有TINY/MEDIUM/LONG类型的衍生长度,BLOB是bytes streaming类型的,而TEXT是基于character streaming本地字符集类型的,两者在存取的时候都不会进行padding和strip操作。
  BLOB/TEXT的关系和之前的VARBINARY/VARCHAR是比较相似的,除了:BLOB/TEXT不能够有DEFAULT值;BLOB/TEXT在创建索引的时候必须要有prefix length,而前者是可选的;给予TEXT索引需要有前缀长度,而且建立索引会自动padding SPACE到索引长度,所以如果插入的字符前面一样,只是尾部空字符长度不同,也是会产生相同的索引值。

1.2.4 字符串各个类型占用的空间长度

类型 长度
CHAR(M) Mxw bytes
BINARY(M) M bytes
VARCHAR(M), VARBINARY(M) L+1/L+2 bytes
TINYBLOB, TINYTEXT L+1 bytes
LOB, TEXT L+2 bytes
MEDIUMBLOB, MEDIUMTEXT L+3 bytes
LONGBLOB, LONGTEXT L+4 bytes

  根据官方手册,CHAR/BINARY及其衍生的类型的数据是存储在表的行内部(inline)的,而对于BLOB和TEXT类型,每一个字段只占用该行9-12(1~4+8)个字节(用于数据的地址和长度),实际的数据是存储在Row Buffer之外位置的。所以对于经常访问的字符串类型,而长度又不是特别的大,还是建议用VARCHAR(M)的数据类型,性能会比TEXT快不少。

1.3 数据表设计其它

1.3.1 不建议使用enum类型?

  现在数据库是原生支持enum类型的,不过很多公司禁止使用enum,而是使用tinyint来标示有限值的情况,因为业务层写入的时候需要加额外的单引号觉得麻烦,增加枚举类型的时候需要做表结构修改。
  不过从自己的经验看来,使用tinyint代替enum很容易造成业务混乱和使用的不便:有些表结构的tinyint的COMMENT是空(或者没有及时更新)的,使用起来需要额外的确认工作;业务代码和数据库操作语句中都是数字,跟踪日志、查看代码的时候都不直观明了,一堆magic code让人看着抓狂。

###1.3.2 字段都要设置为NOT NULL的
  所有的字段都应该是NOT NULL DEFAULT 0/‘’/‘1970-01-01 00:00:00’的,因为(至少)字符串的空值是不占存储空间的;NULL的明确计算只能是IS NULL/IS NOT NULL/IFNULL,和其他任何操作数的计算都是NULL的,这用在筛选条件中是十分危险的;COUNT() MAX()这类聚合计算是忽略NULL的,但是COUNT(*)统计是包含NULL值的,需要注意其中的差异。

二、MySQL数据库索引

  数据库索引可以用来快速找到需要的行,否则的话MySQL就需要一行一行的遍历,查询效率自然相当的低。
  MySQL支持的索引包括PRIMARY KEY、UNIQUE、INDEX、FULLTEXT类型的索引。前面说过,FULLTEXT类型的全文索引在中文下基本是报废的,在此就不予讨论了。
  特别注意的是,对于索引列只能使用单纯的列名,而不能是表达式或者函数的一部分,比如age+2、TO_DAYS(date_col),引擎在检索的时候才能使用索引。

2.1 索引的类型

2.1.1 PRIMARY KEY

  在InnoDB内部,表数据是优化主键快速查询而排列分布的,其查找速度是最快的(相当于聚簇索引:该索引中键值的逻辑顺序决定了表中相应行的物理顺序)。即使表中没有适合做主键的列,也推荐采用一个自动增长的整数主键(代理键),那么这个表在增加数据的时候是顺序存放的,而且后续在别的表参考该外键查询的时候也会得到优化。本身在设计表的时候,也建议常用的数据额不常用的数据分表存放以增加效率。

2.1.2 INDEX

  普通索引,对数据没有约束要求,多行记录可以包含相同值。无论对于字符串索引,还是多列组合索引,都以及在查询语句中,都有个最左前缀的原则:

  • 对于字符串类型,可以指定索引前缀长度(且对于BLOB/TEXT前缀长度参数是必须的),在InnoDB表中其前缀长度最长是767 bytes,且参数M是用bytes计量的。所以太长的字符串,建立BTree索引浪费比较大,这时候用手动模拟HASH索引是个方法,不过这种方式对字符串无法灵活的使用前缀方式查询(例如LIKE这类的操作)。
  • 在建立多列索引的时候,必须按照从左到右的顺序使用全部或部分的索引列,才能充分的使用组合索引,比如:(col1, col2, col3)使用(col1)、(col1, col2)、(col1, col2, col3)有效。在查询语句中会一直向右匹配直到遇到范围查询(>,<,BETWEEN,LIKE)就停止匹配,其后的索引列将不会使用索引来优化查找了。
  • 索引不是建立的越多、越长越好,因为索引除了占用空间之外,对后续数据库的增加、删除、修改都有额外的操作来更新索引,所以对索引列和字符串前缀长度,都参考选择性(Selectivity)这个指标来确定:选择性定义为不重复的索引值和数据总记录条数的比值,其选择性越高,那么索引的查询效率也越高,对于性别这种参数,建立索引根本没有意义。

    2.1.3 UNIQUE

      UNIQUE索引要求索引是唯一的。对于单列索引,要求该列所有数据都不相同,但允许有NULL值;对于多列的组合索引,要求这些列的组合是唯一的。UNIQUE索引其本身既可以作为索引,实际中也可以用以产生数据约束,防止增加或者修改后产生相同数据,从而保证数据的完整性。

2.2 B+Tree和HASH

2.2.1 B+Tree

  该类型的索引中,列记录都是按照顺序排列的,可以优化用于比较或者范围查找操作(=, >, >=, <, <=, BETWEEN, IN),以及用于(GROUP BY, ORDER BY)操作,而且对于字符串类型的索引,最左前缀字符串也可以充分利用索引,比如LIKE ‘Patrick%’会解释成 ‘Patrick’ <= key_col < ‘Patricl’。

2.2.2 HASH

  速度更快,不过只能用于 =、<=>、IN操作符;优化器不能用于ORDER BY操作;任何查找操作必须是索引的完整列。

  PS:其实MySQL在数据类型上面也比较智能了,比如对于一个VARCHAR类型的字段你直接传递给它一个整形的话,MySQL也会接受并正确处理,只不过这之间多了一个数据类型转换。不过还是建议根据数据类型实打实的操作,因为一旦涉及到数据类型转换的话MySQL就不能使用该字段上的索引了,前段时间其他组的数据库服务器CPU暴增,最终排查出来发现是一个字段数据类型发生转换导致索引不能使用所致!

本文完!

参考