MySQL数据库的数据类型和索引
michael_lc
8年前
<p>数据库的数据库索引对程序员来说是透明的，意味着数据库建立索引之前和之后，你的SQL语句都可以正常运行，索引的运用只是数据库引擎工作时候的优化手段。但是，这不是意味着数据库索引仅仅是数据库设计和运维者的事情，对于一个程序员如果对数据库已有的索引有所了解，还是可以大大优化程序员数据库的查询和修改语句执行效率的，以免你的低效查询语句称为拖累整个系统性能的Black Sheep。</p> <p>本文对MySQL数据类型和索引建立、优化进行整理，现在数据库引擎默认都是InnoDB的，而且目前MySQL/MariaDB应用于生产环境时候，应该都是用的这个引擎吧。</p> <h2><strong>1. MySQL的数据类型</strong></h2> <h3><strong>1.1 数字类型和时间类型</strong></h3> <p>数字类型算是最简单的了，主要差异在于各个类型的取值范围大小限制，和对存储空间字节数的需求。数字类型当然是在满足情况的条件下越短越好，一方面MySQL每行有65535字节长度的限制，同时更宽的数据类型意味着对CPU、内存、磁盘I/O带来压力。</p> <p>MySQL支持的定点数字类型和占用字节数分别是</p> <table align="center"> <thead> <tr> <td>类型</td> <td>长度</td> </tr> </thead> <tbody> <tr> <td> <p>TINYINT</p> </td> <td> <p>1</p> </td> </tr> <tr> <td> <p>SMALLINT</p> </td> <td> <p>2</p> </td> </tr> <tr> <td> <p>MEDIUMINT</p> </td> <td> <p>3</p> </td> </tr> <tr> <td> <p>INTEGER</p> </td> <td> <p>4</p> </td> </tr> <tr> <td> <p>BIGINT</p> </td> <td> <p>5</p> </td> </tr> </tbody> </table> <p>在数据库设计的时候，常常看到这些整形有个前缀长度，其实这对其类型本身的存储长度和精度没有影响，只会关系到某些交互式工具显示出来的字符个数。</p> <p>MySQL支持的浮点（实数）类型和占用字节数为</p> <table> <tbody> <tr> <th>类型</th> <th>长度</th> </tr> <tr> <td> <p>FLOAT</p> </td> <td> <p>4</p> </td> </tr> <tr> <td> <p>FLOAT(p) [0,24]</p> </td> <td> <p>4</p> </td> </tr> <tr> <td> <p>FLOAT(p) [25,53]</p> </td> <td> <p>8</p> </td> </tr> <tr> <td> <p>DOUBLE,REAL</p> </td> <td> <p>8</p> </td> </tr> </tbody> </table> <p>计算机的浮点运算都是不精确的，如果要实现精确浮点运算，就需要使用DECIMAL类型。</p> <p>时间类型</p> <p>常被使用的是DATE、DATETIME和TIMESTAMP类型，其表示的范围为：</p> <p>DATE：’1000-01-01’ to ‘9999-12-31’</p> <p>DATETIME：’1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’</p> <p>TIMESTAMP：’1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC</p> <p>TIMESTAMP存储的范围比DATETIME要小，但是空间利用率也最高。MySQL支持的时间精度最高为1s，如果更精确的存储，就必须自己定义存储格式了。</p> <h3><strong>1.2 字符串类型</strong></h3> <p>MySQL中的字符串类型比较多也比较的复杂，各个字符串类型的差别不仅仅在存储时候的空间占用，对存取时候字段某位的strip和padding还有差异。</p> <p>对于类型CHAR/VARCHAR/TEXT是跟本地字符集相关的，这会影响到实际占用空间的字节数、字符比较等。</p> <p>CHAR(M)/VARCHAR(M)</p> <p>长度限制参数M表示的是本地字符集的字符个数而不是bytes数目，比如对于UTF8编码，每个本地字符其实际占用的byte长度可能是3或4倍的本地字符长度。比如VARCHAR(255)，如果每个本地字符占用两个字节，那么其需要的存储空间最大为255x2+2。</p> <p>CHAR的M被限制在最大255，而VARCHAR的M理论上受限于Row Size的长度(65,535bytes)，且实际存储时候会附加1~2字节的前缀表示数据实际长度。如果strict SQL模式没有被打开，那么当插入数据超过声明长度限制的时候，数据将会被截断并产生警告信息，在strict SQL模式下将会出错。</p> <p>CHAR类型在存储的时候，会在右端padding SPACE到指定的M长度，当取该字段的时候，所有末尾的SPACE都将会被strip掉然后返回；VARCHAR不会对进行SPACE进行padding以及strip操作，存储什么样的数据就会返回什么样的数据。</p> <p>对于CHAR/VARCHAR/TEXT类型，在进行字符串比较的时候，（SQL语句参数中的字符串）结尾的空格都是不参与比较的，但是对于LIKE语句，检索的时候结尾的空格是考虑在内的。</p> <p>BINARY(M)/VARBINARY(M)</p> <p>BINARY/VARBINARY在操作的时候，参考的是byte streaming而不是charaset streaming，所以其长度限制参数M表示的是byte数目，在比较的时候也是直接的数字大小比较（而非本地字符集方式比较）。</p> <p>BINARY在插入的时候，会使用0x00（而非SPACE）padding到长度M，取值的时候不会进行strip尾部空字符的操作（意味着取出来的长度一定是M）；VARBINARY则是保证原样存取的。</p> <p>BLOB/TEXT</p> <p>分别有TINY/MEDIUM/LONG类型的衍生长度，BLOB是bytes streaming类型的，而TEXT是基于character streaming本地字符集类型的，两者在存取的时候都不会进行padding和strip操作。</p> <p>BLOB/TEXT的关系和之前的VARBINARY/VARCHAR是比较相似的，除了：BLOB/TEXT不能够有DEFAULT值；BLOB/TEXT在创建索引的时候必须要有prefix length，而前者是可选的；给予TEXT索引需要有前缀长度，而且建立索引会自动padding SPACE到索引长度，所以如果插入的字符前面一样，只是尾部空字符长度不同，也是会产生相同的索引值。</p> <p>字符串各个类型占用的空间长度</p> <table> <tbody> <tr> <th>长度</th> </tr> <tr> <td> <p>CHAR(M)</p> </td> <td> <p>Mxw bytes</p> </td> </tr> <tr> <td> <p>BINARY(M)</p> </td> <td> <p>M bytes</p> </td> </tr> <tr> <td> <p>VARCHAR(M), VARBINARY(M)</p> </td> <td> <p>L+1/L+2 bytes</p> </td> </tr> <tr> <td> <p>TINYBLOB, TINYTEXT</p> </td> <td> <p>L+1 bytes</p> </td> </tr> <tr> <td> <p>LOB, TEXT</p> </td> <td> <p>L+2 bytes</p> </td> </tr> <tr> <td> <p>MEDIUMBLOB, MEDIUMTEXT</p> </td> <td> <p>L+3 bytes</p> </td> </tr> <tr> <td> <p>LONGBLOB, LONGTEXT</p> </td> <td> <p>L+4 bytes</p> </td> </tr> </tbody> </table> <p>根据官方手册，CHAR/BINARY及其衍生的类型的数据是存储在表的行内部(inline)的，而对于BLOB和TEXT类型，每一个字段只占用该行9-12（1~4+8）个字节（用于数据的地址和长度），实际的数据是存储在Row Buffer之外位置的。所以对于经常访问的字符串类型，而长度又不是特别的大，还是建议用VARCHAR(M)的数据类型，性能会比TEXT快不少。</p> <h2><strong>2. MySQL数据库索引</strong></h2> <p>数据库索引可以用来快速找到需要的行，否则的话MySQL就需要一行一行的遍历，查询效率自然相当的低。</p> <p>MySQL支持的索引包括PRIMARY KEY、UNIQUE、INDEX、FULLTEXT类型的索引。前面说过，FULLTEXT类型的全文索引 在中文下基本是报废的 ，在此就不予讨论了。</p> <p>特别注意的是，对于索引列只能使用单纯的列名，而不能是表达式或者函数的一部分，比如age+2、TO_DAYS(date_col)，引擎在检索的时候才能使用索引。</p> <h3><strong>2.1 索引的类型</strong></h3> <p>PRIMARY KEY</p> <p>在InnoDB内部，表数据是优化主键快速查询而排列分布的，其查找速度是最快的（相当于聚簇索引：该索引中键值的逻辑顺序决定了表中相应行的物理顺序）。即使表中没有适合做主键的列，也推荐采用一个自动增长的整数主键（代理键），那么这个表在增加数据的时候是顺序存放的，而且后续在别的表参考该外键查询的时候也会得到优化。本身在设计表的时候，也建议常用的数据额不常用的数据分表存放以增加效率。</p> <p>INDEX</p> <p>普通索引，对数据没有约束要求，多行记录可以包含相同值。无论对于字符串索引，还是多列组合索引，都以及在查询语句中，都有个最左前缀的原则：</p> <p>(1) 对于字符串类型，可以指定索引前缀长度（且对于BLOB/TEXT前缀长度参数是必须的），在InnoDB表中其前缀长度最长是767 bytes，且参数M是用bytes计量的。所以太长的字符串，建立BTree索引浪费比较大，这时候用 手动模拟HASH索引 是个方法，不过这种方式对字符串无法灵活的使用前缀方式查询（例如LIKE这类的操作）。</p> <p>(2) 在建立多列索引的时候，必须按照从左到右的顺序使用全部或部分的索引列，才能充分的使用组合索引，比如：(col1, col2, col3)使用(col1)、(col1, col2)、(col1, col2, col3)有效。在查询语句中会一直向右匹配直到遇到范围查询(>,<,BETWEEN,LIKE)就停止匹配，其后的索引列将不会使用索引来优化查找了。</p> <p>(3) 索引不是建立的越多、越长越好，因为索引除了占用空间之外，对后续数据库的增加、删除、修改都有额外的操作来更新索引，所以对索引列和字符串前缀长度，都参考选择性（Selectivity）这个指标来确定：选择性定义为不重复的索引值和数据总记录条数的比值，其选择性越高，那么索引的查询效率也越高，对于性别这种参数，建立索引根本没有意义。</p> <ul> <li> <p>UNIQUE</p> </li> <li> <p>UNIQUE索引要求索引是唯一的。对于单列索引，要求该列所有数据都不相同，但允许有NULL值；对于多列的组合索引，要求这些列的组合是唯一的。UNIQUE索引其本身既可以作为索引，实际中也可以用以产生数据约束，防止增加或者修改后产生相同数据。</p> </li> </ul> <h3><strong>2.2 B+Tree和HASH</strong></h3> <ul> <li> <p>B+Tree</p> </li> <li> <p>该类型的索引中，列记录都是按照顺序排列的，可以优化用于比较或者范围查找操作(=, >, >=, <, <=, BETWEEN, IN)，以及用于(GROUP BY, ORDER BY)操作，而且对于字符串类型的索引，最左前缀字符串也可以充分利用索引，比如LIKE ‘Patrick%’会解释成 ‘Patrick’ <= key_col < ‘Patricl’。</p> </li> <li> <p>HASH</p> </li> <li> <p>速度更快，不过只能用于 =、<=>、IN操作符；优化器不能用于ORDER BY操作；任何查找操作必须是索引的完整列。</p> </li> </ul> <p> </p> <p> </p> <p> </p> <p> </p> <p>来自：https://mp.weixin.qq.com/s?__biz=MzAwNjA3NDMyOA==&mid=2659762829&idx=4&sn=b390f4af894806af00c4f862b8ccb613&chksm=806e983ab719112cc265d8f1fd3542b993a26117d089a36e6d98d296952bff501db6e2b5685b&scene=0&key=&ascene=7&uin=&devicetype=android-23&version=26031933&nettype=WIFI</p> <p> </p>