MySQL

硬件层相关优化 修改服务器BIOS设置 选择DAPC（Performance Per Wait Optimized）模式，发挥CPU最大性能 Memory Frequency（内存频率）选择Maximum Performance（最佳性能） 内存设置菜单中，启用Node Interleaving，避免NUMA问题 磁盘I/O相关 使用SSD盘 如果是磁盘阵列存储，建议阵列卡配备CACHE和BBU模块，可提升IOPS raid级别尽量选择raid10，而不是raid5 文件系统层优化 使用deadline/noop这两种I/O调度器，别用cfq 使用xfs文件系统，别用ext3，ext4勉强可用，如果业务量大的话一定要用xfs 文件系统mount参数中增加noatime/nodiratime/nobarrier几个选项，其中nobarrier是xfs文件系统特有的 内核参数优化 优化vm.swappiness参数，降低swap使用率。RHEL7/centos7以上慎重设置为0，可能导致OOM 调整vm.dirty_background_ratio/vm.dirty_ratio，确保将脏数据持续刷到磁盘中，避免瞬间I/O过高，产生等待 调整net.ipv4.tcp_tw_recycle/net.ipv4.ctp_tw_reuse都设置为1，减少TIME_WAIT，提高TCP效率 MySQL参数优化 建议设置默认引擎为InnoDB，强烈不建议使用MyISAM引擎 调整innodb_buffer_pool_size的大小，如果单实例且绝大多数是InnoDB引擎表的话，可考虑设置为物理内存的50%-70% 设置innodb_file_per_table=1，使用独立表空间 调整innodb_data_file_path = ibdata1:1G:autoextend，不用默认的10M，在高并发场景下性能有很大提升 设置innodb_log_file_size=256M，设置innodb_log_file_in_group=2，可基本满足大部分应用场景 调整max_connection/max_connection_error，根据业务量大小进行设置 open_file_limit/innodb_open_files/table_open_cache/table_definitiion_cache可设置大约为max_connection的10倍左右大小 key_buffer_size/max_heap_table_size不用设置过大 sort_buffer_size/join_buffer_size/read_buffer_size/read_rmd_buffer_size也不要设置过大 MySQL高可用方案 MHA MHA（MySQL-Master-HA）是目前广泛使用的MySQL主从复制的高可用方案。MHA设计目标是自动实现主实例宕机后，从机切换为主，并尽量降低切换时延（通常在10-30s内切换完成）。同时，由MHA保证在切换过程中的数据一致性。MHA对MySQL的主从复制集群非常友好，没有对集群做任何侵入性的修改。 MHA的一个重点特性是：在主实例宕机后，MHA可以自动的判断主从复制集群中哪个从实例的relaylog是最新的，并将最新从实例的差异log“应用”到其余的从实例中，从而保证每个实例的数据一致。通常情况下，MHA需要10s左右检测主实例异常，并将主实例关闭从而避免脑裂。然后再用10s左右将差异的log event同步，并启用新的Master。整个MHA的RTO时间大约在30s。 ...

众所周知，当业务量达到一定程度时，单库单表已经无法hold住压力，这个时候往往需要根据业务进行分库分表。 分表类型 垂直拆分 即大表拆小表，将不经常使用或者长度较大的字段拆分出去放到“副表”中 水平拆分 即横向分表，将表中不同的数据行按照一定规律分布到不同的数据库表中 分表规则 通常根据主键或者业务id等字段进行hash和取模，得到相应的db和table，当然也可以将id和库的Mapping关系记录在一个单独的库中，优点是Mapping算法可以随意更改， 缺点是引入额外的单点。此外，还有根据时间维度进行分表的。 ...

前言 框架用的多了，已经不会写野生的SQL语句了，于是决定系统地阅读一下MySQL的官方文档。 常用类型比较 类型 范围 存储空间 备注 BIT(M) [1, 64]越界后默认值为1 (M+7)/8 bytes TINYINT(M) signed[-128, 127] unsigned[0, 255] 1bytes SMALLINT(M) signed[-32768, 32767] unsigned[0, 65535] 2bytes MEDIUMINTINT(M) signed[-8388608, 8388607] unsigned[0, 16777605] 3bytes INT(M) signed[-2147483648, 2147483647] unsigned[0, 4294967295] 4bytes BIGINT(M) signed[-9223372036854775808, 9223372036854775807] unsigned[0, 18446744073709551615] 8bytes DECIMAL[(M[,D])] [~ -1E+66, ~1E+66] 8bytes M数字的总数（精度），D是小数点后的数字（刻度）, M最大65，溢出后默认值为10，D最大30，溢出后默认值为0 DOUBLE[(M[,D])] [-1.7976931348623157E+308, 1.7976931348623157E+308] 8bytes FLOAT[(p)] [-3.402823466E+38,3.402823466E+38] 4bytes, 0 <= p <= 24;8bytes, 25 <= p <= 53 CHAR(M) 当0 <= M <= 255时为M × w bytes 其中w为指定字符集下每个字符的大小 VARCHAR(M) L + 1 bytes, L < =255;L + 2 bytes, L > 255 TEXT [0, 65535] L + 2 bytes MEDIUMTEXT [0, 167776150] L + 3 bytes LONGTEXT [0, 4294967295] L + 4 bytes YEAR [1901, 2155] 1bytes DATE [1000-01-01，9999-12-31] 3bytes TIME [-838:59:59, -838:59:59] 3bytes 5.6.4开始 5.6.4开始 3 bytes + fractional seconds storage DATETIME [1000-01-01 00:00:00, 9999-12-31 23:59:59] 8bytes 5.6.4开始 5 bytes + fractional seconds storage TIMESTAMP [1970-01-01 08:00:01, 2038-01-19 11:14:07] 4bytes 5.6.4开始 4 bytes + fractional seconds storage 备注：5.6.4开始，时间类型支持微秒(最大6位)，所占的存储空间原来基础上加上微秒部分

MySQL实例 在Unix上，启动一个MySQL实例通常会产生两个进程，mysqld 就是真正的数据库服务守护进程，而mysqld_safe是一个用于检查和设置mysqld启动的控制程序，它负责监控于重启。 ...

多个字段实现状态 以商品为例，通常一个商品拥有多种状态标志，是否是打折商品，是否是推荐商品，是否是秒杀商品，对应表结构： id is_on_sale is_recommend is_seckill 1 1 0 1 2 0 0 1 3 1 1 1 这种方案不利于扩展，后期有新的标志需要再添加新的字段 ...

多个事务并发执行带来的问题 丢失更新：两个事务同时更新一行数据，最后一个事务的更新会覆盖掉第一个事务的更新，从而导致第一个事务更新的数据丢失，这是由于没有加锁造成的； 脏读：一个事务看到了另一个事务未提交的更新数据； 不可重复读：在同一事务中，多次读取同一数据却返回不同的结果，也就是有其他事务更改了这些数据； 幻读：一个事务在执行过程中读取到了另一个事务已提交的插入数据，即在第一个事务开始时读取到一批数据，但此后另一个事务又插入了新数据并提交，此时第一个事务又读取这批数据但发现多了一条，即好像发生幻觉一样。 不可重复读的重点是修改，同样的条件, 你读取过的数据, 再次读取出来发现值不一样了；幻读的重点是新增和删除，同样的条件, 第1次和第2次读出来的记录数不一样 ...

创建表 CREATE TABLE `user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id', `username` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '用户名', `real_name` varchar(10) NOT NULL DEFAULT '' COMMENT '真实姓名', `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), UNIQUE KEY `uni_username` (`username`), KEY `idx_add_time` (`add_time`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 修改表名 alter table user_info rename user; 增加字段 alter table user add column `status` tinyint(2) NOT NULL DEFAULT 0 COMMENT '状态'; 修改字段属性 alter table user modify column `real_name` varchar(50) NOT NULL DEFAULT '' COMMENT '真实姓名'; 修改字段名 alter table user change column add_time create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'; 修改索引 -- 增加索引 alter table user add index idx_update_time (update_time) ; -- 增加唯一索引 alter table user add unique (column_list) ; -- 增加主键 alter table user add primary key (column_list) ; -- 删除索引 alter table user drop index idx_update_time; -- 删除主键 alter table user drop primary key; 批量插入 insert into user (username, create_time, status) values(10000, '2017-03-01 12:12:12', 1), (10001, '2017-03-01 12:13:14', 1); 批量更新 UPDATE user SET username = CASE id WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 5 ELSE username END, status = CASE id WHEN 3 THEN 3 WHEN 4 THEN 4 ELSE status END WHERE id IN (1, 2, 3, 4); 批量插入若存在则更新 insert into user (username, create_time, status) values(10002, '2017-03-01 12:12:12', 1), (10001, '2017-03-01 12:13:14', 2) on duplicate key update status = values(status); 导入导出 -- 导出某个库 mysqldump -uroot -proot db_name > db_name.sql -- 导出某张表 mysqldump -uroot -proot db_name table_name > table_name.sql -- 导出数据库所有表结构 mysqldump -uroot -proot db_name -d --add-drop-table db_name > db.sql -- 导入 mysqldump -uroot -proot < import.sql -- 使用source方式导入，source import.sql 强制使用索引 select username from user force index(idx_update_time) where update_time > '2017-01-01 10:10:11' join语句 INNER JOIN（内连接或等值连接）：产生同时符合左表和右表的记录。 LEFT JOIN（左连接）：左连接从左表产生一套完整的记录，与匹配的记录(右表) ，如果没有匹配，右侧将包含null。 RIGHT JOIN（右连接）：与LEFT JOIN相反 mysql不支持Full join，不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join. 执行顺序 FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1 ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。 JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。 WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。 GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6. HAVING： 对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。 SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。 DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。