使用 pt-online-schema-change 做在线ddl最添加普通索引、列，修改列类型、添加默认值等使用比较常规，但涉及到要修改的是主键时就有点棘手。在我修改线上实例过程中，有这样的需求，不妨先思考一下怎么做才好： 原表上有个复合主键，现在要添加一个自增id作为主键，如何进行 会涉及到以下修改动作： 删除复合主键定义 添加新的自增主键 原复合主键字段，修改成唯一索引 如果你够聪明，应该会把这三个操作放在同一个 alter table 命令执行。percona手册里有两个地方对修改主键进行了特殊注解： –alter A notable exception is when a PRIMARY KEY or UNIQUE INDEX is being created from existing columns as part of the ALTER clause; in that case it will use these column(s) for the DELETE trigger. –[no]check-alter DROP PRIMARY KEY If –alter contain DROP PRIMARY KEY (case- and space-insensitive), a warning is printed and the tool exits unless –dry-run is specified....

做MySQL的都知道，数据库操作里面，DDL操作（比如CREATE,DROP,ALTER等）代价是非常高的，特别是在单表上千万的情况下，加个索引或改个列类型，就有可能堵塞整个表的读写。 然后 mysql 5.6 开始，大家期待的Online DDL出现了，可以实现修改表结构的同时，依然允许DML操作(select,insert,update,delete)。在这个特性出现以前，用的比较多的工具是pt-online-schema-change，比较请参考pt-online-schema-change使用说明、限制与比较或 ONLINE DDL VS PT-ONLINE-SCHEMA-CHANGE 。 1. Online DDL 在 MySQL 5.1 （带InnoDB Plugin）和5.5中，有个新特性叫 Fast Index Creation（下称 FIC），就是在添加或者删除二级索引的时候，可以不用复制原表。对于之前的版本对于索引的添加删除这类DDL操作，MySQL数据库的操作过程为如下： 首先新建Temp table，表结构是 ALTAR TABLE 新定义的结构 然后把原表中数据导入到这个Temp table 删除原表 最后把临时表rename为原来的表名 为了保持数据的一致性，中间复制数据（Copy Table）全程锁表只读，如果有写请求进来将无法提供服务，连接数爆张。 引入FIC之后，创建二级索引时会对原表加上一个S锁，创建过程不需要重建表（no-rebuild）；删除InnoDB二级索引只需要更新内部视图，并标记这个索引的空间可用，去掉数据库元数据上该索引的定义即可。这个过程也只允许读操作，不能写入，但大大加快了修改索引的速度（不含主键索引，InnoDB IOT的特性决定了修改主键依然需要 Copy Table ）。 FIC只对索引的创建删除有效，MySQL 5.6 Online DDL把这种特性扩展到了添加列、删除列、修改列类型、列重命名、设置默认值等等，实际效果要看所使用的选项和操作类别来定。 1.1 Online DDL选项 MySQL 在线DDL分为 INPLACE 和 COPY 两种方式，通过在ALTER语句的ALGORITHM参数指定。 ALGORITHM=INPLACE，可以避免重建表带来的IO和CPU消耗，保证ddl期间依然有良好的性能和并发。 ALGORITHM=COPY，需要拷贝原始表，所以不允许并发DML写操作，可读。这种copy方式的效率还是不如 inplace ，因为前者需要记录undo和redo log，而且因为临时占用buffer pool引起短时间内性能受影响。 上面只是 Online DDL 内部的实现方式，此外还有 LOCK 选项控制是否锁表，根据不同的DDL操作类型有不同的表现：默认mysql尽可能不去锁表，但是像修改主键这样的昂贵操作不得不选择锁表。 LOCK=NONE，即DDL期间允许并发读写涉及的表，比如为了保证 ALTER TABLE 时不影响用户注册或支付，可以明确指定，好处是如果不幸该 alter语句不支持对该表的继续写入，则会提示失败，而不会直接发到库上执行。ALGORITHM=COPY默认LOCK级别 LOCK=SHARED，即DDL期间表上的写操作会被阻塞，但不影响读取。 LOCK=DEFAULT，让mysql自己去判断lock的模式，原则是mysql尽可能不去锁表 LOCK=EXCLUSIVE，即DDL期间该表不可用，堵塞任何读写请求。如果你想alter操作在最短的时间内完成，或者表短时间内不可用能接受，可以手动指定。 但是有一点需要说明，无论任何模式下，online ddl开始之前都需要一个短时间排它锁(exclusive)来准备环境，所以alter命令发出后，会首先等待该表上的其它操作完成，在alter命令之后的请求会出现等待waiting meta data lock。同样在ddl结束之前，也要等待alter期间所有的事务完成，也会堵塞一小段时间。所以尽量在ALTER TABLE之前确保没有大事务在执行，否则一样出现连环锁表。...

最近在排查现网Text与Blob类型，发现有不少，在《高性能MySQL(第3版)》看到对这两种变长数据类型的处理会涉及到在磁盘上创建临时表，性能开销比较大。于是把影响blob型数据存储方式了解了一下：row_format。 1. InnoDB的Antelop与Barracuda文件格式 Innodb存储引擎保存记录，是以行的形式存放的（与之对应的是像Google BigTable这种列数据库）。在InnoDB 1.0.x版本之前，InnoDB 存储引擎提供了 Compact 和 Redundant 两种格式来存放行记录数据，这也是目前使用最多的一种格式。Redundant 格式是为兼容之前版本而保留的。 MySQL 5.1 中的 innodb_plugin 引入了新的文件格式：Barracuda（将以前的行格式 compact 和 redundant 合称为Antelope），该文件格式拥有新的两种行格式：compressed和dynamic。 在 MySQL 5.6 版本中，默认还是 Compact 行格式，也是目前使用最多的一种 ROW FORMAT。用户可以通过命令 SHOW TABLE STATUS LIKE'table_name' 来查看当前表使用的行格式，其中 row_format 列表示当前所使用的行记录结构类型。 mysql> show variables like "innodb_file_format"; +--------------------+-----------+ | Variable_name | Value | +--------------------+-----------+ | innodb_file_format | Barracuda | +--------------------+-----------+ 1 row in set mysql> show table status like "tablename%"\G *************************** 1. row *************************** Name: t_rf_compact Engine: InnoDB Version: 10 Row_format: Compact Rows: 4 Avg_row_length: 36864 Data_length: 147456 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 7 Create_time: 2016-05-14 20:52:58 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0....

关于MySQL InnoDB表的主键设计，有必要从开发规范 http://xgknight.com/2016/05/11/mysql-dev-principle-ec/ 里拿出来，单独展开说一下。 InnoDB是一个聚集索引组织表，即行数据是按照聚集索引在物理磁盘上存储的，并且是块状结构，默认一个block是16kB。 图片来《高性能MySQL》 首先在设计表结构时，表一定要显式定义主键，自增主键，或者联合主键，或全局ID。 （所有与主键，包括其它索引，相关的字段，都要定义为NOT NULL，这是因为如果允许NULL，那么在索引的每条记录上，都要多用一个标记去记录这个列是否是NULL，占用多余的存储空间） 1. 自增主键特性 对于高并发的插入速度较快，因为每次插入新记录，都是在之前记录的右边顺序插入，不需要频繁的分裂。 表上要建立多个二级索引时，索引记录都会带上主键，根据主键去定位行数据。自增主键一般是int或bigint型，多个二级索引上面占用的空间较小。 2. 联合主键特性 每次新记录插入，都要寻找到合适的“缝隙”，插入，当插入位置空间不够时，需要做页分裂，这个需要维护成本。 二级索引带上的主键值，是联合主键的总长度，所以一个单列索引占用的空间里面，主键部分占了大部分，空间利用率不高，而且这种是 optimize table 解决不了的。 （提示：聚集索引叶子节点，就是行数据本身，所以，不需要另外的空间存储主键） 但是联合主键有一个好处：逻辑上一批数据，在物理上很有可能相邻存储，有可能检索的数据，在一个block里面，减少了读取并缓存磁盘块的数量，一个是速度的提升，一个是减小内存的消耗。 比如 (f_c_id,f_m_id,f_type) 作为联合主键，f_c_id=22299有20w条记录，每条记录平均160bytes，一个页能存16kB，即100条记录（不考虑预留），那么f_c_id=22299需要2000个page，而且是相邻的page。 举例，应用检索数据 f_c_id=22299, f_m_id IN(12345,23456) ，假设数据在块1和块10，缓存到内存。不多久检索f_c_id=22299, f_m_id=12399，刚好在块1。 如果是自增id，那么就没有这个顺序， 而是根据插入数据时间来的，那么这两条记录可能在物理上很远的地方，要多读取磁盘。 3. 全局ID 全局ID跟自增ID特性基本相同，但是它的值是从另外的服务获取的数字增长类型，不要UUID。 只在有分库（一般有全局统计需求），或其它可能需要全局唯一性的情况下，才使用，否则没必要引入多余的服务依赖。 另外，定义全局ID时，注意字段范围要满足要求，小心溢出；不要加上多余的 AUTO_INCREMENT 定义。 使用全局id还有一个好处：在做数据迁移或拆库时，可以无缝切换，因为新旧数据id不用担心重复。 4. 设计原则：自增主键 VS 联合主键 所有索引字段，特别主键，无论自增或联合主键，一定定义为 not null 表没有特殊情况下，都使用自增主键，尽量不用联合主键 特别是“可能作为联合主键”里面有的字段，会频繁update的情况，更不能做联合主键 在业务层具有唯一性的属性，如果不依赖于数据库的唯一索引来编码，也不用使用Unique Key。如果需要数据库维护唯一性，可使用Unique Key，比如将上面的联合主键定义为联合索引，再另外定义一个自增主键 如果表上有一个单列字段，已具有唯一性，可直接定义成主键，不必设置自增id 尽量用int或bigint型，如果不能，也要控制主键varchar列的长度在30以内。不要用带汉字或url类似的字段作为主键 根据上面的顺序走完，还是想用联合主键的，以下任意条件满足，可用： 表上的插入数据并发量不高 有明显的上文【联合主键】部分说到的，热点数据相邻存储的场景 出了联合主键外，其它索引的只有1-2个 与DBA协商后同意 参考： http://imysql.com/2015/10/29/mysql-faq-clustered-index.shtml 原文连接地址：http://xgknight.com/2016/05/13/mysql-innodb-primary_key/

updated: 2017-11-12 本文所提规范，在我博客上可以找到多篇案例。 最近一段时间一边在线上抓取SQL来优化，一边在整理这个开发规范，尽量减少新的问题SQL进入生产库。今天也是对公司的开发做了一次培训，PPT就不放上来了，里面有十来个生产SQL的案例。因为规范大部分还是具有通用性，所以也借鉴了像去哪儿和赶集的规范，但实际在撰写本文的过程中，每一条规范的背后无不是在工作中有参照的反面例子的。如果时间可以的话，会抽出一部分或分析其原理，或用案例证明。 1. 命名规范 库名、表名、字段名必须使用小写字母，并采用下划线分割 MySQL有配置参数lower_case_table_names=1，即库表名以小写存储，大小写不敏感。如果是0，则库表名以实际情况存储，大小写敏感；如果是2，以实际情况存储，但以小写比较。 如果大小写混合使用，可能存在abc，Abc，ABC等多个表共存，容易导致混乱。 字段名显示区分大小写，但实际使⽤时不区分，即不可以建立两个名字一样但大小写不一样的字段。 为了统一规范， 库名、表名、字段名使用小写字母，不允许 - 号。 库名以 d_ 开头，表名以 t_ 开头，字段名以 f_ 开头 比如表 t_crm_relation，中间的 crm 代表业务模块名 库名，如果不是分库，两个不同db实例里面的db名，不能相同，以免混淆 视图以view_开头，事件以event_开头，触发器以trig_开头，存储过程以proc_开头，函数以func_开头 普通索引以idx_col1_col2命名，唯一索引以uk_col1_col2命名（可去掉f_公共部分）。如 idx_companyid_corpid_contacttime(f_company_id,f_corp_id,f_contact_time) 如果某些特殊情况需要在sql里面指定索引，select * from t_test using index(idx_i_abc)，这种所以如果可以，命名的时候加上 i 分隔，如idx_i_corpid, uk_i_user，方便DBA在修改索引的时候会注意到这个 i 标识，不能随意修改这个索引(名称)，否则查询会出错。当然这种情况尽量不要出现。 库名、表名、字段名禁止超过32个字符，需见名知意 库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符 临时用的库、表名须以tmp位前缀，日期为后缀 如 tmp_t_crm_relation_0425。备份表也类似，形如 bak_t_xxxx_20160425 ，这样便于查找和知道有效期。 正常业务里用的临时表、中间表，后缀尽量不要包含 tmp 命名，以免造成歧义。 按日期时间分表须符合_YYYY[MM][DD]格式 这也是为将来有可能分表做准备的，比如t_crm_ec_record_201403，但像 t_crm_contact_at201506就打破了这种规范。 不具有时间特性的，直接以 t_tbname_001 这样的方式命名。 2. 库表基础规范 使用Innodb存储引擎 5.5版本开始mysql默认存储引擎就是InnoDB，5.7版本开始，系统表都放弃MyISAM了。 表字符集统一使用UTF8MB4 UTF8字符集存储汉字占用3个字节，存储英文字符占用一个字节 校对字符集使用默认的 utf8mb4_general_ci。特别对于使用GUI设计表结构时，要检查它生成的sql定义 连接的客户端也使用utf8，建立连接时指定charset或SET NAMES UTF8;。（对于已经在项目中长期使用latin1的，救不了了） 如果遇到EMOJ等表情符号的存储需求，可申请使用UTF8MB4字符集 所有表都要添加注释 尽量给字段也添加注释 类status型需指明主要值的含义，如"0-离线，1-在线" 控制单表字段数量 单表字段数上限30左右，再多的话考虑垂直分表，一是冷热数据分离，二是大字段分离，三是常在一起做条件和返回列的不分离。 表字段控制少而精，可以提高IO效率，内存缓存更多有效数据，从而提高响应速度和并发能力，后续 alter table 也更快。 所有表都必须要显式指定主键 主键尽量采用自增方式，InnoDB表实际是一棵索引组织表，顺序存储可以提高存取效率，充分利用磁盘空间。还有对一些复杂查询可能需要自连接来优化时需要用到。 只有需要全局唯一主键时，使用外部自增id服务 如果没有主键或唯一索引，update/delete是通过所有字段来定位操作的行，相当于每行就是一次全表扫描 少数情况可以使用联合唯一主键，需与DBA协商 对于主键字段值是从其它地方插入（非自己使用AUTO_INCREMENT生产），去掉auto_increment定义。比如一些31天表、历史月份表上，不要auto_increment属性；再必须全局id服务获取的主键。 不强制使用外键参考 即使2个表的字段有明确的外键参考关系，也不使用 FOREIGN KEY ，因为新纪录会去主键表做校验，影响性能。...

1. 隐式类型转换实例 今天生产库上突然出现MySQL线程数告警，IOPS很高，实例会话里面出现许多类似下面的sql：(修改了相关字段和值) SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 and f_col2_id=1244378 and f_qq1_id in (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233) 用 explain 看了下扫描行数和索引选择情况： mysql>explain SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 and f_col2_id=1244378 and f_qq1_id in (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233); +------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+ | 1 | SIMPLE | t_tb1 | ref | uid_type_frid,idx_corpid_qq1id | uid_type_frid | 8 | const | 1386 | Using index condition; Using where | +------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+ 共返回 1 行记录,花费 11....

最近在准备给开发做一个mysql数据库开发规范方面培训，一步一步来，结合在生产环境发现的数据库方面的问题，从几个常用的数据类型说起。 int、tinyint与bigint 它们都是（精确）整型数据类型，但是占用字节数和表达的范围不同。首先没有这个表就说不过去了： Type Storage Minimum Value Maximum Value (Bytes) (Signed/Unsigned) (Signed/Unsigned) TINYINT 1 -128 127 0 255 SMALLINT 2 -32768 32767 0 65535 MEDIUMINT 3 -8388608 8388607 0 16777215 INT 4 -2147483648 2147483647 0 4294967295 BIGINT 8 -9223372036854775808 9223372036854775807 0 18446744073709551615 只需要知道对应类型占多少字节就能推算出范围了，比如int占 4 bytes,即4*8=32bits，大约10位数字，也能理解为什么int默认显示位数是11。 遇到比较多的是tinyint和bigint，tinyint一般用于存放status,type这种数值小的数据，不够用时可能会用smallint。bigint一般用于自增主键。 为了避免数据库被过度设计，布尔、枚举类型也采用tinyint。 还有一点也是经常被提到的关于 int(M) 中M的理解，int型数据无论是int(4)还是int(11)，都已经占用了 4 bytes 存储空间，M表示的只是显示宽度(display width, max value 255)，并不是定义int的长度。 例如： mysql> CREATE TABLE `tc_integer` ( `f_id` bigint(20) PRIMARY KEY AUTO_INCREMENT, `f_type` tinyint, `f_flag` tinyint(1), `f_num` smallint(5) unsigned ZEROFILL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> desc tc_integer; +----------------+-------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------------------------+------+-----+---------+----------------+ | f_id | bigint(20) | NO | PRI | NULL | auto_increment | | f_type | tinyint(4) | YES | | NULL | | | f_flag | tinyint(1) | YES | | NULL | | | f_num | smallint(5) unsigned zerofill | YES | | NULL | | +----------------+-------------------------------+------+-----+---------+----------------+ 4 rows in set (0....

数据类型差不多是接触mysql一开始就了解的内容，最近遇到几个现象如varchar自动转mediumtext，blob存储性能的问题，不得不回头明确一下关于MySQL常用数据类型的选择。 mysql手册这里 已经讲的很清楚了。它们都是定义字符串型字段时常用的类型，但它们存储和检索的方式有不同，最大长度和尾部的空格是否保留也有差别。 char类型是使用固定长度空间进行存储，范围0-255。比如CHAR(30)能放30个字符，存放abcd时，尾部会以空格补齐，实际占用空间 30 * 3bytes (utf8)。检索它的时候尾部空格会被去除。 char善于存储经常改变的值，或者长度相对固定的值，比如type、ip地址或md5之类的数据，不容易产生碎片。关于它的效率可以参考这里。 varchar类型保存可变长度字符串，范围0-65535（但受到单行最大64kb的限制）。比如用 varchar(30) 去存放abcd，实际使用5个字节，因为还需要使用额外1个字节来标识字串长度（0-255使用1个字节，超过255需要2个字节）。 varchar善于存储值的长短不一的列，也是用的最多的一种类型，节省磁盘空间。update时varchar列时，如果新数据比原数据大，数据库需要重新开辟空间，这一点会有性能略有损耗，但innodb引擎下查询效率比char高一点。这也是innodb官方推荐的类型。 如果存储时真实长度超过了char或者varchar定义的最大长度呢？ 在SQL严格模式下，无论char还是varchar，如果尾部要被截断的是非空格，会提示错误，即插入失败 在SQL非严格模式下，无论char还是varchar，如果尾部要被截断的是非空格，会提示warning，但可以成功 如果尾部要被截断的是空格，无论SQL所处模式，varchar都可以插入成功但提示warning；char也可以插入成功，并且无任何提示 这里特意提到SQL的严格模式，是因为在工作中也遇到过一些坑，参考MySQL的sql_mode严格模式注意点。 贴上官方的一个表格： Value CHAR(4) Storage Required VARCHAR(4) Storage Required '' ’ ' 4 bytes '' 1 byte ‘ab’ ‘ab ' 4 bytes ‘ab’ 3 bytes ‘abcd’ ‘abcd’ 4 bytes ‘abcd’ 5 bytes ‘abcdefgh’ ‘abcd’ 4 bytes ‘abcd’ 5 bytes 另外，mysql字段值比较时默认是不区分大小写的，这是由于他们的校对规则（一般是 utf8_general_ci）决定的，按字符比较，所以查询时 值尾部 的空格也是被忽略的，除非建表时对列指定 BINARY （校对字符集变成utf8_bin）或者select * from vc where binary v='ab ';，就会按字节比较，即比较时区分大小写和尾部空格。 需要注意的是，使用varchar不能因为长度可变就随意分大空间，比如90个字节能放够的列定义成varchar(200)，因为开辟内存时是以200字节进行的，遇到需要filesort或tmp table作业可能会带来不利影响。...

1. MySQL莫名变成了 Strict SQL Mode 最近测试组那边反应数据库部分写入失败，app层提示是插入成功，但表里面里面没有产生数据，而两个写入操作的另外一个表有数据。因为 insert 失败在数据库层面是看不出来的，于是找php的同事看下错误信息： [Err] 1364 - Field `f_company_id` doesn't have a default value 很明显2个 insert 操作，第一条成功，第二条失败了，但因为没有控制在一个事务当中，导致app里面依然提示成功，这是客户入库操作，心想如果线上也有这个问题得是多大的代价。 不说开发的问题，好端端的mysql怎么突然就部分表写入失败呢？根据上面的问题很快能猜到是 sql_mode 问题： NOT NULL 列没有默认值但代码里也没给值，在非严格模式下，int列默认为0，string列默认为’‘了，所以不成问题；但在严格模式下，是直接返回失败的。 一看，果然： mysql> show variables like "sql_mode"; +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +---------------+--------------------------------------------+ 但是一直是没问题的的，就突然出现了，有谁会去改 sql_mode 呢，生产环境产生这个问题的风险有多大？所以必须揪出来。 先 set global sql_mode='' ，让他们用着先（文后会给解决问题根本的办法），同时打开general_log看是哪一个用户有类似设置 sql_mode 命令： 1134456 Query SET autocommit=1 1134456 Query Set sql_mode='NO_ENGINE_SUBSITUTION,STRICT_TRANS_TABLES' 1134457 Connect ecuser@10.0.200.173 on 1134457 Query /* mysql-connector-java-5....

这个亏已经吃过很多次了，在开发以前的sql代码里面，许多以 or 作为where条件的查询，甚至更新。这里举例来说明使用 or 的弊端，以及改进办法。 select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and (f_mobile ='1234567891' or f_phone ='1234567891' ) limit 1 从查询语句很容易看出，f_mobile和f_phone两个字段都有可能存电话号码，一般思路都是用 or 去一条sql解决，但表数据量一大简直是灾难： t_tbanme1上有索引idx_id_mobile(f_xxx_id,f_mobile), idx_phone(f_phone),idx_id_email(f_id,f_email)，explain 的结果却使用了 idx_id_email 索引，有时候运气好可能走 idx_id_mobile f_xxx_id 因为mysql的每条查询，每个表上只能选择一个索引。如果使用了 idx_id_mobile 索引，恰好有一条数据，因为有 limit 1 ，那么恭喜很快得到结果；但如果 f_mobile 没有数据，那 f_phone 字段只能在f_id条件下挨个查找，扫描12w行。 or 跟 and 不一样，甚至有开发认为添加 (f_xxx_id,f_mobile,f_phone)不就完美了吗，要吐血了~ 那么优化sql呢，很简单（注意f_mobile,f_phone上都要有相应的索引），方法一： (select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 900000 and f_mobile ='1234567891' limit 1 ) UNION ALL (select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 900000 and f_phone ='1234567891' limit 1 ) 两条独立的sql都能用上索引，分查询各自limit，如果都有结果集返回，随便取一条就行。...