Written by arstercz - 2014-07-18

MySQL开发规范

开发规范–MySQL

摘要

这篇文章旨在规范开发人员对线上、线下MySQL数据库的操作行为, 包括表设计、索引使用和sql查询三个方面。一为避免字符集、索引等的滥用给后端的迁移和排错带来难度; 二为减少sql的不合理使用给后端带来额外的性能消耗和调优的复杂性; 三为尽量剥离后端服务与开发人员的耦合度, 方便后端的扩展、迁移和故障处理。规范相关条目随版本的不同略有变化, 部分规范随开发人员的使用而进行调整。

管理规范见: 管理规范

参考

optimization
data-types
replication

表设计

原则上讲表设计是一个很大的话题, 字段及类型的选取、字符集、存储引擎、范式、是否拆分表等都需要做更细致的划分以避免以后可能会引起的隐含问题。当然没有产品DBA参与项目的开发设计, 很多表设计及创建工作都由开发人员来完成, 这也造成了开发人员更多的以个人喜好来完成表设计相关的工作, 对后期的扩展及维护并没有做太多的考虑。所以这里从后端维护的角度以存储引擎、数据类型、和表创建三个方面来描述开发阶段需要遵守的一些规范。

1.存储引擎

由于服务器端发展的原因， MySQL的性能也随之大幅提升, 存储引擎的功能也越来越完善, 比如以前MyISAM的fulltext功能在5.6版本的InnoDB实现, buffer的充分利用提升了性能而减少了数据丢失的风险。这种进步促进了线上表存储引擎的统一, 更便于表对象的处理, 系统调优的实现, 也能避免混合存储引擎带来的诸多弊端(如事务处理，buffer限制等)。线上MySQL主要包括5.1.xx, 5.5.xx两种版本, 对于表存储引擎的使用规范如下:

1. 使用InnoDB存储引擎;

Oracle官方5.5版本后默认存储引擎改为InnoDB，比起MyISAM，支持更细粒度的行级锁, 支持事务(4中隔离级别), 高并发(MVCC)的支持, buffer的充分利用以及更好的数据崩溃恢复机制。所以对于需要持久存储的表，需采用InnoDB引擎;

2. 使用Memory引擎;

是否使用该引擎由开发人员决定, Memory引擎受变量(max_heap_size)的限制, 对于HASH查找有很好的性能提升, 但它是易失的(重启即丢失);

3. 不要参照已有库的引擎;

很大程度上由于历史开发的原因，已存在库中的表引擎是混用的(Memory+MyISAM+InnoDB),这在性能调整、更新结构和备份及恢复方面都带来较大的开销, 比如吃掉有限的内存资源,MyISAM表的不能使用事务镜像备份而造成长时间的锁表, 更新表结构引起的较大的IO触发等。开发人员依旧遵守上面2条规则, 对于非必要的其它引擎的表由DBA在后端统一处理。

2.数据类型及长度

相应的数据选择相应的类型及长度, 本质上是为了节约系统资源; 在短期来看长度一项是无关紧要的,过长的字段可能额外消耗一点空间资源,过短在后期也可以做相应调整(也可能会掉入长度截取的坑里),开发人员不用在长度方面过度纠结，选好相应的数据类型很重要, MySQL支持的数据类型可参见: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

对常用的数据类型做如下规范:

1. 整形: tinyint, smallint, mediumint, int, bigint

开发人员按照值存在的范围选取相应的类型, 可以过大不可过小。定义中不用再定义长度, int(10)与int(15)本质上没有区别，只是client端显示可能会出现长度的不同，但是存到Server端的数据没有不同。UNSIGNED由开发人员确定是否使用, 使用该选项要明确没有负数的可能,以防数据的溢出。另:ipv4地址不要保存为字符类型,使用INET_ATON转换为数字保存到整形,int型的范围是 -2147483648 to 2147483647, 最大的255.255.255.255是4294967295 超出了范围, 可以用unsigned或bigint指定；使用auto_increment作为唯一值生成器是个很好的选择。

2. decimal

存储精确浮点数，用decimal类型取代float和double类型。SQL中的float和大多数编程语言中的float一样, 使用二进制编码表示实数数据, 但是十进制的无限小数在二进制中的表示是不同的, 比如59.95，在二进制表示中为无限小数。float无法表示二进制的无限小数，进而使用59.950000762939来表示近似值。如果精度要求很高float或double类型并不能满足需求, 可以使用decimal代替,比如以下测试:

Create Table: CREATE TABLE `f` (
  `hourly` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql root@[localhost:s3306 test] > select * from f;
+--------+
| hourly |
+--------+
|  59.95 |
+--------+

mysql root@[localhost:s3306 test] > select hourly*1000000000 from f;
+-------------------+
| hourly*1000000000 |
+-------------------+
| 59950000762.93945 |
+-------------------+
1 row in set (0.00 sec)

alter table f modify hourly decimal(5,2);   改为decimal类型

mysql root@[localhost:s3306 test] > select hourly*1000000000 from f;
+-------------------+
| hourly*1000000000 |
+-------------------+
|    59950000000.00 |
+-------------------+
1 row in set (0.00 sec)

可以看到decimal和float在放大10亿倍之后，差距还是蛮大的。

3. enum和set

开发人员需要格外注意,需要数字枚举的话可以选取tinyint类型。enum以数字序列的索引的方式映射出现的类型,比如enum(‘M’,’F’)实际保存enum(0,1), 如果开发人员指定enum(‘0’,’1’,’2’)的话可能出现查询2为空的结果集, 这种方式也会带来排错的难度，另外NULL对应索引值NULL, “” 对应索引值 0 , 比如以下示例:

percona] > create table enumtest(id int auto_increment primary key, Y enum('0','1','2'),name char(10));
Query OK, 0 rows affected (0.28 sec)

mysql root@[localhost:s3321 percona] > insert into enumtest(Y,name) values(2,'xxxx');
Query OK, 1 row affected (0.01 sec)

mysql root@[localhost:s3321 percona] > insert into enumtest(Y,name) values(0,'xxxx');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql root@[localhost:s3321 percona] > insert into enumtest(Y,name) values(1,'chen');
Query OK, 1 row affected (0.00 sec)

mysql root@[localhost:s3321 percona] > insert into enumtest(Y,name) values(2,'lI');
Query OK, 1 row affected (0.01 sec)

mysql root@[localhost:s3321 percona] > select * from enumtest;
+----+------+------+
| id | Y    | name |
+----+------+------+
|  3 | 1    | xxxx |
|  6 |      | xxxx |
|  9 | 0    | chen |
| 12 | 1    | lI   |
+----+------+------+
4 rows in set (0.00 sec)

这种情况就会出现找不到Y列为2的记录。 set为集合类型,一个大集合里，不同行可能存储不同子集; set应用相对较少, 使用它可能为建立索引和更改表结构带来麻烦。

4. VARCHAR(N) 和 CHAR(N)

VARCHAR为变长字符类型，CHAR为固定长度类型， N为字符的个数( http://dev.mysql.com/doc/refman/5.5/en/char.html ),不论中文还是英文,都可以存N个, 比如utf8编码下一个汉字的长度为3,一个字母的长度为1, 指定的N都是可以存储的个数N(见下测试), 开发人员指定长度时需要多注意，避免出现长度不够而出现字符截取的现象。另外对于InnoDB引擎而言, 长度小于255，表格式中使用1字节存储长度，大于255使用2字节存储长度, 记录过多可能意味着浪费越多。

mysql root@[localhost:s3306 test] > create table chart(id int auto_increment primary key, name varchar(2));
Query OK, 0 rows affected (0.00 sec)

mysql root@[localhost:s3306 test] > set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql root@[localhost:s3306 test] > insert into chart(name) values ('我看看');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql root@[localhost:s3306 test] > show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'name' at row 1
1 row in set (0.00 sec)

mysql root@[localhost:s3306 test] > select * from chart;
+----+--------+
| id | name   |
+----+--------+
|  1 | 我看   |
+----+--------+
1 row in set (0.00 sec)

5. 如果可以，请避免使用TEXT, BLOB类型

存储引擎划出额外的空间来存储这两种数据类型, 对于索引的创建带来更大的开销(比如通过substring截取前多少字节作为索引), MySQL优化器也不能为此类型做更好的自适应哈希索引优化,SQL查询也会因此缓慢。

6. date

日期类型

7. datetime和timestamp

精确到秒的时间类型, datetime占用8字节长度,存储的时间范围也更大; timestamp占用4字节，存储范围较小(到 2038年 ….)，当然timestamp支持存储不同时区的时间, 也支持为表中第一个timestamp列指定一个current time默认值, 第二列及之后不能指定。 timestamp 类型中, 默认情况下不允许为NULL, 指定的default 关键字必须是一个常量值, 以便该字段的初始化, 要指定为NULL, 就指定NULL 关键字, 或改为datetime类型. 如下的方式创建NULL 默认值

`ts` timestamp NULL DEFAULT NULL

timestamp-initialization

The TIMESTAMP data type offers automatic initialization and updating to the current date and time.

另外对于时区相关的问题 , 参见 mysql-time-zone-support 默认情况下, MySQL 的时区为系统时区, 如果手动更改了时区, 需要重新启动 MySQL server 以使其生效, 不过也可以使用 set time_zone 参数临时改变会话的时区. 不过 timestamp 类型默认采用 UTC 时区存储, 不受时区参数的限制, 读取的时候 timestamp 会自动转换到会话的时区. 另外如果系统的时区有自动的回调操作, 比如闰秒问题或以下的纽约时间, 隔一段时间就会自动回调系统时间:

[root@cz ~]# timedatectl 
      Local time: Thu 2018-03-22 05:45:19 EDT
  Universal time: Thu 2018-03-22 09:45:19 UTC
        RTC time: Thu 2018-03-22 09:45:18
       Time zone: Asia/Shanghai (EDT, -0400)
     NTP enabled: yes
NTP synchronized: yes
 RTC in local TZ: no
      DST active: yes
 Last DST change: DST began at
                  Sun 2018-03-11 01:59:59 EST
                  Sun 2018-03-11 03:00:00 EDT
 Next DST change: DST ends (the clock jumps one hour backwards) at
                  Sun 2018-11-04 01:59:59 EDT
                  Sun 2018-11-04 01:00:00 EST

这种情况下, 如果 MySQL server 的时区为默认的 SYSTEM, 则会自动进行回调, 遵循系统的当前时间. 如果是手动修改会话的时区参数, 则不会自动生效, 如下所示, 执行 set time_zone = 'America/New_York' 之后, 手动修改系统时间到到达 2018-11-04 01:59:59 的时候没有自动回调:

mysql root@[localhost:s3308 percona] > select now();
+---------------------+
| now()               |
+---------------------+
| 2018-11-04 13:59:58 |
+---------------------+
1 row in set (0.00 sec)

mysql root@[localhost:s3308 percona] > select now();
+---------------------+
| now()               |
+---------------------+
| 2018-11-04 14:00:03 |
+---------------------+
1 row in set (0.00 sec)

8. 不要使用binary, blob类型存储图片或文件

除了增加维护的开销，没觉得有其它好处, 可以改为图片数据库或者硬盘存储。

9. 使用 json 类型存储 json 格式的数据

json 类型在 MySQL 5.7.8 之后开始支持, 支持对 json 对象单个的 key 值进行查找和更新, 不过不支持对 json 类型直接进行索引, 需要通过生成列间接完成索引.

详见: json type 和 json index

3.表设计

有很多好的或不好的准则来指导我们设计表结构，这意味着我们可能犯一些MySQL特定的错误。这部分描述应该使用哪些，不应该使用哪些的规范。

1. charset

线上和线下统一指定为utf8编码。字符集是历史开发中问题最为严重的一个，一个实例中有多个库, 每个库有很多表，这些表可能由多位开发人员设计，如果掺杂了个人喜好, 维护人员就可以看到各种各样的字符集, 有gbk, utf8, latin1存在不同表，甚至同一个表中。影响最严重的是数据库的扩展和迁移受到了很大的阻力,因为需要开发人员做相关的编码测试。有特殊需求的字段，可以和维护人员协商。

2. 名称

不管是库名，表明还是字段名不要取一些毫无意义的名称,如a,x表明或字段名，当做测试还可以，放到线上只能让人更迷惑。较长名称的表或字段可以使用”_“分隔, 比如 project_app_user,库名可以使项目相关的名称。名称不要使用大小写敏感的字符, 以防应用程序在不同操作系统里表现出不同的响应，这种错误时比较明显的，如果不注意排查起来还是相当困惑的。

3. NOT NULL

NULL从SQL语法角度来讲是Unkown的意思, NULL和NULL比较仍是Unkown，其它类型和NULL比较仍是Unkown，从业务逻辑上考虑应该避免NULL作为字段的默认值。另外NULL使得row记录首部额外增加一个字节来保存改行该列是否为空, 从效率来看也应该避免使用NULL作为默认值。

4. too many columns

没有哪一个关系对象是一定要用很多列才能描述清楚的，行的大小限制65535字节，使用的列越多意味着触发限制的概率越大，越容易出现问题，更何况那么多列看起来也不会舒服。可以将大属性集合拆分为多个小的子集，线上表列数建议不要超过50。

5. 表分区及表拆分

开发人员首先要明确需要的数据存到表里合适还是存到磁盘文件合适, 其次存到表里的数据可否定期清理, 如果可清理,则由维护人员操作，最后再考虑分区或拆分表的方式。分区表的分区键要考虑清楚，采用哪种分区方式能避免维护上的操作成本, 详见: http://dev.mysql.com/doc/refman/5.5/en/partitioning-types.html , 表拆分方面，开源软件还没有成熟的方案，这点比较遗憾的，应用端拆分需要做好足够全面的逻辑处理，中枢(路由表)如何实现, 拆分后会不会带来数据完整性的影响，拆分后的子表会不会有大小不均等等都需要考虑。具体细节可同DBA协商。

6. 范式和反范式

不论是第二、第三范式还是第五范式，都是关系型数据库所追求的，但是纯粹的范式和纯粹的反范式都只存在于实验室中，范式越强意味着表之间的join查询可能越多, 应用业务更多的需求是二者的结合，比如第三范式可以适当增加一个字段的冗余以方便业务的处理。举例如下:

volume表有字段: volume_id, volume_name, create_time
book表有字段:   id, volume_id, create_time
chapter表有字段：chapter_id, chapter_name, volume_id, create_time, chapter_content 

应用查询中更多的可能是按照book.id查找对应的chapter_name信息, 上面的范式增加应用逻辑,也增加了代码长度,我们可以为chapter表增加一个book_id字段(反范式，因为冗余)就很容易满足逻辑需求。

索引

从开发人员的角度看索引的创建及使用不是开发者的重心，不过在没有sql审核机制或产品DBA的情况下索引的正确使用对线上业务的正常访问还是很重要的，这需要开发人员对索引的使用有个大致的把握度，明确这样使用不会对现有业务产生大的影响。维护人员仅仅是事后处理, 症状明显则当下就可以处理, 不明显则堆积到后期，可能未来某天就会中奖, 远达不到预防的目的。该部分描述索引的创建规范(清理由DBA完成)、常用索引类型以及一些易出问题的查询。

索引创建

1. 命名规范

索引的命名同表设计命名，关联表字段进行命名, 不要出现大写字母, 第二索引(非唯一键)以 idx_filed1_filed2 命名, 多个字段关联多项, 字段名过长可以截取前4~8个字符长度的字段名, 不要选用毫无意义的名称,如 add key a (filed1,filed2), 没有任何好处; 唯一键(id自增类主键除外)以idx_uniq_filed1_filed2或uniq_filed1_filed2这两种方式命名, 多个字段关联多项。

2. 选取字段

如何选取待建索引的字段? 先来看看索引有哪些作用, 以一本英汉词典为例: (1) 可以减少Server端需要检查的数据量, 想想词典中的目录,查找到单词只需找到目录页，翻到对应的页数即可; (2) 可以避免Server端做过多的排序或创建临时表, 索引已经排好序了，类似词典目录页(顺序的), 查找下一单词很方便；(3) 将查找的随机I/O转换为顺序I/O,同样类似于顺序的目录页。从这三个作用不难看出要建索引的字段得满足以下几点: (1) 过滤条件中出现的字段，如 where id = ?, group by ?, order by ? 等; (2) 过滤条件中字段的基数(所有记录里字段不重复的数量)要大, 想想性别字段，不是M就是F, 建了索引也没意义; (3) 集合索引(多字段), 过滤条件中多字段的顺序很集合索引建立的字段的顺序匹配,详见 http://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html 查看索引命中使用 : explain extended sql, 查看输出中的possible keys 和keys, extra列尽量避免出现Using File Sort和Using Temporary，索引的坐标值(基数)可以查看 show index from table 输出中的Cardinality列值(理论上越大越好)。

3. 字段数量

理论上看，索引的字段越多， SQL就可能越复杂, 开发人员应该尽量避免复杂SQL的应用, 确定是否可以创建多字段的索引，能否拆分为多条简单语句, 建议不要建立超过5个字段的索引。

4. 一张表该有多少索引

索引随着应用程序的需求而建，不是给每个字段建立索引，也不是建的越多越好，索引的建立意味空间资源和索引维护方面的开销(想想存储引擎的索引组织方式)，开发人员可能并不理解前缀索引、覆盖索引这些概念而创建了过多的冗余索引, 导致维护人员show create table 的时候分外目眩神离。对于这一点，开发人员在创建新索引前可以参考2中的描述查看是否命中索引，不太确定的情况可找DBA协商。

索引类型说明

该部分描述常见索引的类型，旨在让开发人员在创建索引时避免出现常见的误区，分三部分描述:

全文索引

fulltext索引在应用程序中算是滥用比较多的，不管是MyISAM还是5.6版本的InnoDB，由于中文字节长度(utf8一个中文3字节，gbk一个中文2字节)的原因, fulltext在分词长度和相关分词实现中并没有对中文做很好的支持, 再加上中英混用,在分词查找中可能更令人失望,也容易引起MyISAM表的crash。有的应用将中文转为二进制数字串再存入到fulltext表中，这也能缓解分词的查找,但不易于维护。好点的解决方式可以使用第三方的全文搜索插件: php应用: sphinx , 或者 coreseek 中文分词, 但已停止更新 java应用: lucene

使用第三方插件有两个目的: 1. 不依赖于MySQL存储引擎，避免线上MyISAM, InnoDB混用的窘境, 方便DBA的调优、扩展; 2. 插件生成的索引文件可独立存在于磁盘空间中, 同样能满足分词查找的目的。

HASH索引

Memory存储引擎的默认索引, 不论用什么算法实现的, HASH的目的便是快速查找, 在时间和空间复杂度方面较BTREE等索引要高效许多, 适用于1对1查找，如 select … from session where id = ?,范围查找不要使用HASH索引。另: 自适应哈希索引, 该索引由MySQL自身创建并维护, 在BTREE索引的基础上,在叶子节点额外增加的HASH索引,以加快SQL的响应, 开发人员和DBA无法手工干预。

BTREE索引

BTREE索引即开发人员或DBA常说的索引, 类型较多，以MyISAM和InnoDB为例描述:

1. 主键

唯一标识表中的记录, 不可重复, 并没有明确的定义，是唯一键的一种,不能为NULL。开发人员用的最多便是自增作为主键, 如 id auto_increment primary key, 但不是每个表都需要创建id自增主键, 字段组合构成的唯一键也可作为主键。 MySQL表在没有显示指定主键时，选取表中的唯一键作为主键，没有唯一键则选举隐含的 _rowid作为主键.

2. 唯一索引

同上面，主键是唯一索引的一种，唯一的标识表中的记录，可以单列组成唯一索引(唯一后，表中不会有重复的记录，但是有重复的记录则不能创建唯一索引)，也可以多列组成索引。

3. 一般索引

一般索引是比较通用的项，比如我们经常建立的单列或多列索引，但不是唯一的，可重复插入。这种索引在建立前需要关注列的基数(非重复值的数量)。

4. 覆盖索引

从索引的组织方式来看，覆盖索引减少了磁盘的I/O读取，加快了对应用的响应。举个例子: tag1表存在id,和name两个索引, 回想索引的组织方式, 以name为例, BTREE树中的叶子即保存name的字段信息, 叶子节点保存主键id信息(这是innodb存储方式, 所有行记录保存在主键中), 如果应用程序仅查找name列的信息, 因为已经有了name列索引, 则MySQL优化器从BTREE树中的叶子中就可以获取name列的信息，而不用再经过叶子节点和主键来获取name列,相当于节省了两次磁盘I/O, explain的extra列也显示了优化器使用index就获取了需要的信息。

Create Table: CREATE TABLE `tag1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  `tag` char(20) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8

mysql root@[localhost:s3306 test] > explain select name from tag1 where name = 'cm'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tag1
         type: const
possible_keys: name
          key: name
      key_len: 30
          ref: const
         rows: 1
        Extra: Using index
1 row in set (0.00 sec)

mysql root@[localhost:s3306 test] > explain select tag from tag1 where name = 'cm'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tag1
         type: const
possible_keys: name
          key: name
      key_len: 30
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

当然，同样的条件查询tag列，就需要经过name索引的叶子节点，找到对应的主键id，再到主键id中找到对应的tag列信息，如上所示, Extra为空(因为name是唯一键，都是一对一的查找, type列就显示const)。

5. 前缀索引

前缀索引是个相对迷惑人的概念, 很多开发人员正是不理解这点而创建了过多的前缀索引。为什么称为前缀索引, 首先它肯定是多列索引, 再者，BTREE是顺序组织索引的，则对于多列索引而言, 先排序filed1,再排序filed2，以此类推;如下示例: 索引 idx_bookid_volumeid(`book_id`,`volume_id`) 索引 idx_bookid(`book_id`)

Create Table: CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book_id` int(11) NOT NULL,
  `voulme_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bookid_volumeid` (`book_id`,`voulme_id`),
  KEY `idx_bookid` (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

+----+---------+-----------+
| id | book_id | voulme_id |
+----+---------+-----------+
|  1 |   10001 |        10 |
|  2 |   10001 |         2 |
|  3 |    9999 |         5 |
|  4 |   10001 |         4 |
|  5 |    9999 |         4 |
|  6 |   10001 |        11 |
|  7 |   20001 |         3 |
|  8 |   20001 |         1 |
+----+---------+-----------+

简单罗列出索引组织数据的方式，如下: 两者的顺序组织方式反别为:

idx_bookid_volumeid          idx_bookid
    9999, 4                    9999
    9999, 5                    9999
    10001, 2                   10001
    10001, 4                   10001
    10001, 5                   10001
    10001, 10                  10001
    10001, 11                  10001
    20001, 1                   20001
    20001, 3                   20001

现在我们可以看到idx_bookid_volumeid索引的bookid顺序和idx_bookid的顺序是相同的,但是volume_id列却是乱序的(见下),从这里可以理解到下面的SQL语句不管表中有没有idx_bookid索引，效果都是等同的:

mysql root@[localhost:s3306 test] > explain select * from book where book_id = 10001\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
         type: ref
possible_keys: idx_bookid_volumeid,idx_bookid
          key: idx_bookid_volumeid
      key_len: 4
          ref: const
         rows: 4
        Extra: Using index
1 row in set (0.00 sec)

mysql root@[localhost:s3306 test] > alter table book drop key idx_bookid;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql root@[localhost:s3306 test] > explain select * from book where book_id = 10001\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
         type: ref
possible_keys: idx_bookid_volumeid
          key: idx_bookid_volumeid
      key_len: 4
          ref: const
         rows: 4
        Extra: Using index
1 row in set (0.00 sec)

这也从侧面验证了idx_bookid是一个多余的索引, 除了增加索引组织和系统的开销,没有其它的好处。这就是为什么称为前缀索引，因为book_id字段命中idx_bookid_volumeid索引的book_id前部分(有的资料也称前缀索引为最左前缀 left-most), 如果where 条件中为 volume_id(见上面的索引组织方式), volume_id为乱序, 对于idx_bookid_volumeid来讲毫无意义，也就不会命中索引。所以开发人员需要注意以最左前缀的方式来命中索引(散列索引等同)。

SQL查询

SQL的正确使用从两方面来衡量, 包括性能和安全两方面；性能方面表现在避免过多的在MySQL Server端处理,比如复杂的SQL能否拆分为多条简单的SQL以减少临时表、系统资源(磁盘I/O和CPU)等方面的消耗, Server端的计算能否移到应用程序端减少Server的响应时间；安全方面是出于主从架构而考虑到的, 要使得主从数据相对一致达到高可用的目的(比如主从切换),需要开发人员在SQL的使用方面格外注意。该部分分别从这两方面来描述SQL查询的使用。

1. 性能

1. 使用隔离列

该点强调了在SQL使用中，不要在过滤条件(或排序等)中做过多的运算，不要使用函数与字段值比较等, 对于MySQL优化器而言，函数等的使用为代数法则优化带来了很大的阻碍，优化器不得不为此做额外的计算, 同时基于buffer和cache等性能提升的手段也难以运用, 比如下面: <code class="SQL"> SELECT username FROM user WHERE signup_date >= CURDATE() #不要使用函数在过滤条件中 SELECT username FROM user ORDER BY RAND() </code></pre> 优化器没有想象中的智能，会把curdate函数当作常量来使用，相反需要开发人员在程序逻辑中先计算curdate的值，再应用到SQL查询中，这样既减少了优化器的操作，也能充分利用buffer或query cache的特性: $today = date("Y-m-d") -- 对$today进行验证 SELECT username FROM user WHERE signup_date >= $today</pre> 这里顺便提及下, 应用程序开发中应该始终对来源数据进行验证，以减少SQL注入的风险, 比如上面的例子，如果$today的值为 “2014-07-18 ‘;drop table user;”, 则SQL语句成为 SELECT username FROM user WHERE signup_date >= ‘2014-07-18’；drop table user;可以想象这样做的后果。

2. 使用explain

explain提供了一个详细的SQL执行计划，使得开发相对清晰的了解到MySQL在执行指定SQL时到底做了什么,用到了索引还是用了全表扫、亦或使用了临时表，排序等, 增加extended选项可以看到优化器如何改写我们指定的SQL, 详见: http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html, MySQL 5.6增加了对update等更新语句的explain支持。

3. 拆分SQL

越复杂的SQL,优化器需要做更多的准备工作, 就像上面提到的, 在每条SQL实际执行前，MySQL都需要计算出最佳的执行计划，最后再将执行计划返回给执行器, 以join语句为例, n张表有n!种执行计划,优化器需要选出最优执行计划(当然可以强制指定join表的顺序,但实际中更多的是由优化器决定),当然择优过程并不是无限的(想象100!该有多少种可能),受参数optimizer_search_depth的限制。比如下面的示例: ```SELECT * FROM tag -> JOIN tag_post ON tag_post.tag_id=tag.id -> JOIN post ON tag_post.post_id=post.id -> WHERE tag.tag='mysql';

三张表联接有3!=6种可能, 当然这些对优化器来讲是微不足道的， 我们仅举例子说明, 可以拆分为以下SQL:
```<code class="SQL">
mysql> SELECT * FROM tag WHERE tag='mysql';
mysql> SELECT * FROM tag_post WHERE tag_id=1234;
mysql> SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);
</code></pre>
从这方面看, 拆分的子句显得更复杂些，需要开发人员在程序逻辑上进行控制, 但是每条子句都可以很容易的命中索引,相比而言只是程序逻辑稍微增加, 但三条子句却更高效。

拆分大更新语句

我们回收了线上数据库用户的create, alter权限, 保留SIUD(select, insert, update, delete)权限, 创建表，更改表结构由维护人员完成。 对于一条语句 delete from table, 如果表很大(几十G等), truncate和delete对表来说都是耗时耗力的操作, 首先引起磁盘I/O, 其次对于InnoDB而言，MySQL后台线程需要检索buffer数据，查找到相关表的信息进行清理,这部分是比较耗时的, 对buffer影响也较大。对于这种操作要求开发人员两点 1. Innodb表启用事务, delete或update操作按照要删除的范围拆分为多个字表, 比如要删除前1kw记录, 可以分1k次操作1w行记录。MyISAM表不支持事务，但更需要分多次拆分,因为操作会表锁; 2. insert 操作, 对于InnoDB表, 开启事务多次插入后再提交,不应过大以免冲击redo, undo日志文件,建议以1w为单元(也可以和MyISAM表一样拼出长语句)， MyISAM表拼出 insert into ... values(xxx),(xxx),(xxx) ....免去多次插入带来的权限检查，磁盘io消耗。

不要使用 select * ….

这条规范争议是比较大的， 程序中如果是以散列方式处理数据,这条语句也就无关痛痒， 但现实中更多的可能是数组方式获取整行信息, 这种方式在增加一个字段的时候遗留问题会比较多,引起列错位的错误, 在insert into ... select * ...语句中则更明显。当然select * 也有其方面的一面，就是SQL语句更短小, 更容易查看。 开发人员可参考 <a href="http://gertjans.home.xs4all.nl/sql/dont-use-select-star-in-production-code.html">http://gertjans.home.xs4all.nl/sql/dont-use-select-star-in-production-code.html</a> 查看使用select * 的利弊。

读取需要的列

不要做一些无谓的牺牲, 需要哪些列就在SQL中指定哪些， 该条规范也是为了杜绝出现规范5中的现象, 比如只需要id列，就指定SQL   select id from ... ,不要做一些过多的查询如: select id, name ... 等, 除了增加网络的吞吐量, 还减缓了Server对程序的响应, 也降低了命中覆盖索引的几率。

预处理

Prepared Statements, 对于重复性高的SQL， 如 select id,name.... from book where name = ?, 每次传递的仅是name的值, 使用一次预处理即可,减少每次连接数据库带来的消耗,即减少交互次数, 如下php代码示例,一次连接，多次执行:

// create a prepared statement if ($stmt = $mysqli->prepare(“SELECT username FROM user WHERE state=?”)) {

// bind parameters $stmt->bind_param(“s”, $state);

// execute $stmt->execute();

// bind result variables $stmt->bind_result($username);

// fetch value $stmt->fetch();

printf(“%s is from %s\n”, $username, $state);

$stmt->close(); }

当然， 预处理可能并不适合短链接的应用, 比如很多的discuz论坛, 长连接则很适合，比如java应用的连接池。 

IN (inlist) ``` 从词法和性能角度讲, in 是替换 union和or 的好方法, 但是在使用过程中,开发人员需要注意几点:
inlist中的元素为同一数据类型, 是整数就全部整数，字符也一样; 不同类型的元素在MySQL优化器处理过程中需要做额外的转换比较, 也可能因此导致SQL语句没有使用正确的索引；
inlist中不要出现NULL元素, 单SQL中出现NULL，影响或许不大，但多个SQL或者有多个子查询可能会出现无法预估的错误, 假设1,2,3是a表tag列的记录, 1, NULL, 2是b表tag列的记录，我们有以下查询：
```
SELECT a.tag FROM a where a.tag NOT IN (SELECT tag FROM b)
```
我们对此SQL做下转换:
```
where a.tag NOT IN (1, NULL, 2) --> where a.tag NOT IN (<a.tag = 1>, <a.tag = NULL>, <a.tag = 2>) --> where a.tag <> 1 ADN a.tag <> NULL AND a.tag <> 2) --> where a.tag <> 1 AND UNKOWN AND a.tag <> 2  --> where UNKOWN
```
得到最后的结果为空,这不是我们想要的。
inlist列表元素不要过多,不要超过300,过多就进行分组多次执行, Server端接收数据的长度也是有限制的,超过长度会进行分组，这也就造成了延迟的出现, 过多多列表也对优化的处理造成了负担. ```

分页

不要使用select id, name ..... limit m,n进行分页, m为1kw的时候意味着优化器需要过滤1kw行, 至少也可以改用id主键(select id from 覆盖索引,吃的buffer也较小)来增强查询速度, 如 select b.id, b.name .... from table b inner join table a on (a.id = b.id ) where a.id < .... > ...
或者采用标签标记的方式也可以加快速度, 比如第一个的范围为 <1,50>,第二页则为<51,100>依次类推, 分页采用懒加载的方式, 用户不请求则只显示按钮, 请求了再进行查询操作。开发人员也可以使用memcache, mongodb等作为缓冲剂, 但如果还使用limit m,n ，那么并没有从本质上缓解分页。

like ``` 模糊查询的使用，回想上面前缀索引的实现, like ‘string%’类似于匹配前缀索引, like操作本质上是正则匹配, ‘string%’方式很好的利用了索引，查找速度更快, ‘%string%’则需要做更多的正则匹配操作。线上避免使用’%string%’的方式匹配。

11. select count(*)

该查询查找满足条件的记录，存储引擎索引组织的格式不同, 性能可能也会相差很多, 见:　http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/ 对于　’select count()’语句, MyISAM索引组织中包含了行的计数器, 在没有where条件时, MyISAM表只需要简单读取最右边(BTREE是排序的)的的计数即可, InnoDB由于没有计数器的原因，需要做一个索引扫描或全表扫描才能获取到信息；对于有where 条件的”select count()”操作，过滤方式在两个存储引擎中并没有什么不同，　都需要做扫描操作。所以开发需求中有很多没有where条件的”count(*)”操作, 需要开发人员指定表为MyISAM引擎。

2. 安全

我们从主从数据完整性方面做以下规范:

1. 禁止直连DB更新

以discuz为例说明, ucenter中的配置文件中存在配置 define(‘UC_DBTABLEPRE’, ‘bbs_project.pre_ucenter_’), 该配置决定了数据以insert into bbs_project.pre_ucenter_xxx values(…)的方式插入带库中, 在复制规则( http://dev.mysql.com/doc/refman/5.5/en/replication-rules-db-options.html )中，该种方式插入的SQL语句不会记录到binlog日志中, 这意味着slave 端不能接收该SQL，造成数据在slave中的缺失, 如果slave对外服务, 也会造成用户查看不到记录而重复插入的结果，待master出现故障,维护人员进行主从切换后, 缺失的问题会更明显。在5.1版本中，这种直连方式会记录到binlog中(不过根据过滤规则来看，并没有发现和5.5有什么不同,所以到目前为止我也没搞清这个功能在5.1中是过滤的bug还是过滤的正常规则,亦或者是5.5版本完善了此bug或去掉该正常规则 ),不会造成数据的缺失。线上业务5.1,5.5都有使用，但是禁止开发人员使用直连的方式更新数据。

2. 禁止使用结果集不确定的SQL

什么是不确定, 是指在master或slave中分别执行的语句可能得到不同结果集的情况, 比如 insert into a select * from b limit 100; limit 100是建立在主从数据严格一致的情况下, 可惜由于很多原因(比如磁盘扇区的不同, master和slave表中同样记录的显示顺序可能有所不同, 或者由于管理原因偶尔关闭了binlog记录引起两边结果集差几条),select … limit 100在主从中不一定得到一致的结果集, 这种情况可能会引起两边数据的错位。开发人员可以通过以下2种方式处理这种不确定性:

拆分: 程度得到select 结果集, 再拼出确定的数据,比如for循环插入指定的记录, 或者insert .. value (…),(…)..插入确定的数据；
排序使结果集确定: 如上面的语句改成 insert into a select * from b order by id limit 100,这种情况如果slave有缺失, 也在可控范围内。

3. 禁止LOAD DATA 操作

除了可能触发bug( http://dev.mysql.com/doc/refman/5.5/en/replication-features-load-data.html ), 也可能造成流量突增引起slave的延迟, 数据量大的FILE使用脚本操作。

4. 避免使用不安全函数

同不确定SQL一样，不安全函数也是指主从两边执行却出现不同结果的函数。查看不安全函数 replication-rbr-safe-unsafe, 这些函数在不同的复制格式中存在不同的表现, 比如大部分不安全函数在row或mixed格式中是安全的(因为复制以记录的变更为基准), 在statement格式中不安全函数(因为两边是以sql语句为基准的)的表现就是两次执行得到的结果不一样, 比如以下的uuid函数和时间函数(now为安全函数, sysdate不安全):

mysql root@[localhost:s3306 (none)] > select uuid(), sleep(1), uuid();
+--------------------------------------+----------+--------------------------------------+
| uuid()                               | sleep(1) | uuid()                               |
+--------------------------------------+----------+--------------------------------------+
| 17c91519-0e50-11e4-b554-d6d4a6c2dafd |        0 | 1861b09b-0e50-11e4-b554-d6d4a6c2dafd |
+--------------------------------------+----------+--------------------------------------+

mysql root@[localhost:s3306 (none)] > select sysdate(), sleep(2),sysdate();
+---------------------+----------+---------------------+
| sysdate()           | sleep(2) | sysdate()           |
+---------------------+----------+---------------------+
| 2014-07-18 15:53:32 |        0 | 2014-07-18 15:53:34 |
+---------------------+----------+---------------------+
1 row in set (2.01 sec)

mysql root@[localhost:s3306 (none)] > select now(), sleep(2), now();
+---------------------+----------+---------------------+
| now()               | sleep(2) | now()               |
+---------------------+----------+---------------------+
| 2014-07-18 15:53:42 |        0 | 2014-07-18 15:53:42 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

两次uuid得到不同的值; now作为安全函数是以整条语句的执行时间为准的, sysdate则是以函数开始执行的时间为准, uuid和sysdate的情况都能产生不一致的结果。处理的方式包括以下两种:

DBA更改复制格式为row或mixed

从资源上考虑, 这两种方式会产生大量的binlog, 系统空间有限的话难以设置。mixed可以考虑使用, 但如果表设计不合理(比如没有唯一键), 可能造成Server的崩溃。

变不确定为确定

比如上面的uuid函数, 开发人员可以将生成的信息保存到临时变量里，在做 insert … values ($tmp)的操作, 这样binlog是以常量值记录的。所以从开发人员的角度来看, 稍微增加程序的复杂性而换来主从数据的一致性是很划算的。

所有程序需要的DDL语句和drop, create, alter等操作，由DBA在后端操作, 测试环境权限都比较足，开发人员可以自行操作。

5. 禁止truncate操作

truncate table 等同于 drop table + create table , truncate为DDL语句, 需要额外的create和drop权限, 开发人员应用SQL语句的时候需要注意该点, 线上应该不会给应用账户分配drop, create权限；

6. 表结构不使用外键

innodb-and-mysql-replication impact-of-foreign-keys-absence-on-replicating-slaves 要严格保证主从一致才能用外键, 应用中用了事务就不要再用外键了, 失败回滚或意外断电都会让slave丢数据, 如果测试环境是单主, 则观察不到风险 , 线上数据一致可以通过应用程序实现, 不做外键。

其它

关于存储过程,触发器和视图等这些方面，不在该规范里定义, 同上述的安全选项一样, 复制格式的不同造成主从的影响也不同, 详见: http://dev.mysql.com/doc/refman/5.5/en/replication-features.html

从项目的后期维护来看, 设计上没有大的问题(不确定可以和DBA协商)即可以上线使用, 更多的操作需要DBA进行修枝剪叶, 上述的规范尽量统一了业务使用数据库的规则, 减少出现故障时的交互时间。

← → Top