mysql

mysql

数据库设计规范

• 所有数据库对象（库/表）名称必须用小写字母并用下划线分割。

• 数据库对象（库/表）名称禁止使用mysql关键字，如 from/in 等等。

• 数据库对象（库/表）名称要见名识意。

• 临时表必须以 tmp_ 为前缀，并以日期为后缀。

• 备份表必须以 bak_ 为前缀，并以日期为后缀。

• 所有存储相同数据的列名和列类型必须一致。（如果不一致，会因关联查询时隐式转换导致索引失效）

• 除特殊需求外，所有的表都要使用 Innodb 存储引擎（5.6以后Innodb是默认存储引擎）

  Innodb : 支持事务，行级锁，更好的恢复性，高并发下性能更好
  

• 数据库和表的字符集统一使用 UTF8，可以避免由于字符集转换产生的乱码。

• 尽量控制单表的大小，建议尽量控制在 500w，可以使用历史数据归档，分库分表等手段控制大小。

• 所有的表和字段都要加注释，从一开始就维护数据字典。

• 谨慎使用 Mysql 分区表，分区表在物理上表现为多个文件，逻辑上表现为一个表，跨分区查询效率可能更低。建议采用物理分表方式管理大数据。

• 尽量做到冷热数据分离，减小表宽度。可以减少磁盘IO，保证热数据的内存缓存命中率。

• 禁止在表中建立预留字段。

• 禁止存储图片/文件。

• 禁止在生产环境中进行压力测试。

• 禁止从开发环境，测试环境直接连接生产环境数据库。

索引设计规范

• 不要滥用索引，建议单表索引不超过 5 个。

• 每个Innodb 表中必须有一个主键。不使用频繁更新的列作为主键 & 不使用 md5/uuid/hash/字符串 作为主键。因为索引排序需要消耗 IO，每一次插入非顺序的主键值，就要进行一次全盘排序操作。建议使用自增id作为主键。

• 建议在 select/update/delete 的 where 从句中的列，以及 order by/group by/distinct 中的字段上建立索引

• 避免建立重复/冗余索引。

• 避免使用外键约束。外键会影响父子表的写操作性能。

单列索引与联合索引

为 test表的 a,b,c 三列设置联合索引

KEY `a_b_c` (`a`,`b`,`c`)

• 只查 a，索引生效 √

SELECT * FROM `test` WHERE a='1'

• 只查 b，索引无效 X

SELECT * FROM `test` WHERE b='1'

• 只查 c，索引无效 X

SELECT * FROM `test` WHERE c='1'

• 查 a,b，索引生效 √

SELECT * FROM `test` WHERE a='1' and b='1'

• 查 b,a，索引生效 √ （自动优化）

SELECT * FROM `test` WHERE b='1' and a='1'

• 查 a,c，索引生效 √

SELECT * FROM `test` WHERE a='1' and c='1'

• 查 b,c，索引无效 X

SELECT * FROM `test` WHERE b='1' and c='1'

• 查 a,b,c，索引生效 √

SELECT * FROM `test` WHERE a='1' and b='1' and c='1'

• 查 or 语句 a,b，索引无效 X （所以要用 in 来优化 or）

SELECT * FROM `test` WHERE a='1' or b='1'

单列索引在多条件查询时，仅能用到一列索引。为 test表的 a,b,c 三列设置单列索引：

KEY `a` (a)
KEY `b` (b)

• 查 a,b，仅用到索引a √ ，b未用到索引 X

SELECT * FROM `test` WHERE a='1' and b='1'

• 查 a or b，全部索引生效 √

SELECT * FROM `test` WHERE a='1' or b='1'

数据库字段设计规范

• 优先选择符合存储需要的最小数字类型，如将字符串转化为数字类型存储。

  # 存储 ip 若存为字符串类型需要15字节，数字类型仅需4字节
  SELECT INET_ATON('127.0.0.1');      // 2130706433
  SELECT INET_NTOA(2130706433);       // '127.0.0.1'
  

• 对于非负整数采用无符号整型进行存储。

• varchar(n) 中的 n 代表的是字符数，而不是字节数。比如 varchar(255) 是可以保存 255 个中文的，而使用 utf8 存储1个汉字占用 3 个字节，所以 varchar(255) 存储255个汉字需要765字节。

• 尽量选择合适的长度，过大的长度会消耗更多的内存。如 varchar(255)，在物理存储时是占用了可变的实际空间，但会按定义的长度来分配内存。

• 避免使用 text，blog 数据类型。如果一定要用，建议将该列分到单独的扩展表中，谨慎使用 select * ，仅查询必要字段。text，blog类型只能使用前缀索引。

• 避免使用 enum 类型。

  1. 修改 enum 的值需要使用 alter 语句；会产生大量的元数据组，造成数据阻塞，存在一定风险 
  2. order by 操作效率低下，会先将数字类型转换成字符串，然后进行排序，这导致不会使用索引
  

• 尽可能将所有列定义为 not null。因为如果不定义，在有 null 的列上增加索引，需要额外的空间存储。在列的比较和计算时要对 null 值做特殊处理，可能导致索引失效。

• 避免使用字符串存储日期/时间，因无法使用日期函数进行计算和比较，且占用更多的空间。timestamp (4字节) > datetime (8字节)。

  timestamp 实际上是用 int 类型来存储的，只是显示友好可读性高。
  timestamp 的存储范围在 1970-01-01 00:00:01 ～ 2038-01-19 03:14:07（与 int 存储范围一致）超过这个范围的时间要用 datetime 存储
  

• 财务相关的金额类数据，必须使用 decimal 来存储，不会丢失精度，占用的空间是定义的宽度决定的， 每4个字节可以存储9个小树，小数点也占用一个字节。可用于存储比 bigint 更大的整数数据。

SQL 开发规范

• 使用预编译语句进行数据库操作，可以重复使用执行计划，减少 sql 编译时间，也可以有效避免动态sql注入问题。

• 尽量避免数据类型的隐式转换，隐式转换会导致索引失效。如 select * from a where id = "11" 会导致 id 字段字符串->int 的隐式转换。

• 充分利用表上已经存在的索引，避免全表扫描，如 like '%123%'，利用复合索引的最左前缀，将不能用到索引的范围查询条件列放到符合索引最左边。使用 left join, not exists 来代替 not in 操作，用 id<1 or id>3 来代替 id != 3操作。复合索引详解博客

• 禁止跨库查询，为数据库迁移和分库分表留出余地。

• 禁止使用 select * ，必须使用 select <列> 查询。优点是可以使用覆盖索引。

  如果一个索引包含所有需要查询的字段的值，称为覆盖索引，只需要扫描索引，无需回表。
  

• 禁止使用子查询，用关联查询代替。 如 select a from (select b from c)。子查询的结果集无法使用索引，子查询会产生临时表的操作，如果子查询的数据量大则严重影响效率。

• 避免使用 join 关联太多的表，每一个join 会占用一部分内存。

• 减少数据库交互次数，批量操作。

• 使用 in 代替 or 语句。or 很少能使用到索引，in 能更有效的利用索引，也不要超过 500 个

• 禁止使用 order by rand() 进行随机排序。

• where 从句中禁止使用函数转换和计算，将导致无法使用索引。

  where date(createtime) = "20200910"
  可以使用 where createtime >= "20200910" and createtime < "20200911"
  

• 拆分复杂的 sql 为多个小 sql

sql 操作规范

• 超过 100w 行的批量写操作，要分多次执行。可能会造成严重的主从延迟；binlog 日志为 row 格式时会产生大量的日志。

• 避免产生大事务操作。

• 对于大表，修改表结构时一定要用 pt-online-schema-change 。该工具修改表结构时，会先创建出一张新表，再批量复制内容到新表，并在老表创建触发器处理新增数据的同步，并进行加锁等操作，最后删除老表。

  避免大表产生的主从延迟
  避免在对表字段进行修改时进行表锁
  

• 禁止对程序使用的账号赋予 super 权限，当达到最大连接数时，仅允许有 super 用户权限的用户连接，该权限只能留给 DBA 处理问题时使用。

• 程序使用的账号赋予最小权限。

三范式

• 1 无重复的列
• 2 属性完全依赖于主键
• 3 一个数据库表中不包含已在其它表中 已包含的非主关键字信息，非PK的字段间不能有从属关系

事务

InnoDB 支持事务，Myisam 不支持

简单的事务操作

mysql> start transaction;                           #手动开启事务
mysql> insert into t_user(name) values('pp');
mysql> commit;                                      #commit之后即可改变底层数据库数据
mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | jay  |
|  2 | man  |
|  3 | pp   |
+----+------+
3 rows in set (0.00 sec)
mysql> start transaction;
mysql> insert into t_user(name) values('yy');
mysql> rollback;
mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | jay  |
|  2 | man  |
|  3 | pp   |
+----+------+
3 rows in set (0.00 sec)

四大特性

• 原子性(A)：事务是最小单位，不可再分
• 一致性(C)：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
• 隔离性(I)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性(D)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

四种隔离级别

• 读未提交：

（在事务中）领导给 a 发了 1000 块工资，还没有提交，这时 a 读取了账户余额，发现有 1000 块。领导又撤销了操作，a 再读发现没发工资，白高兴一场。

并发的事务中，B可以读A未提交的操作，称为脏读。

• 读已提交：（Oracle 默认级别）

解决了读未提交的问题，在领导确定并提交了工资后，a 才能读到改变后的工资。

a 拿着新发的 1000 块去消费，买东西时看了一眼余额有 1000 块，同时， a 的老婆把1000块转到了自己账户里，然后 a 付款时发现余额不足。

在同一事务中，对同一条数据的多次查询结果不同，称为不可重复读

• 可重复读：

解决了读已提交中的问题，a 开始读余额的时候，其他事务就不可以进行修改（写锁）。

a 在消费前，a的老婆就在查工资单了，发现有 1条 +1000 块的数据，这时 a 消费了 800， a的老婆再查发现有两条数据， +1000 与 -800。

前后多次读取，数据总量不一致，称为幻读，多是插入或删除操作引起。

• 串行化：

事务A和事务B，事务A在操作数据库时，事务B只能排队等待，这种隔离级别很少使用，并发量太低，用户体验差。

Innodb 与 Myisam

• 区别

  1. myisam 不支持事务，innodb 支持事务。
  2. myisam 支持表级锁，innodb 支持行级锁，行级锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
  3. InnoDB 支持外键，而 myisam 不支持

• 场景选择

  MyISAM：
      数据一致性要求不是非常高（不支持事务）；
      以读为主，较少修改；
      并发量相对较低；
  
  InnoDB：
      需要事务支持，数据一致性要求较高
      并发量较高
      数据更新较为频繁
      避免全表扫描，因为会使用表锁
  

字段类型

整型

int(4) 其中的宽度 4 是配合 zerofill 使用的，当数据小于这个宽度时会自动填充0。比如插入数字 1，会显示 0001，与存储空间没有关系。

varchar

varchar 用于存储变长字符串，只占用必要的存储空间，但会按定义的最大长度来分配内存。如果最大长度小于255则只占用一个额外字节记录最大长度，否则占用两个额外字节。适用于保存很少被更新的数据。

char

char 用于存储定长字符串，最大宽度为 255，字符串存储在 char 类型列中会被删除末尾空格。
char 类型适合存储长度近似的值，如 md5 的密码，身份证号等等

注意， varchar(n) 与 char(n) 中的宽度 n 都是字符，根据编码方式不同占用字节数也不同。

explain

无法展示触发器，存储过程，UDF对查询的影响

id

id 表示执行顺序，值越大优先级越高，图上代码会先执行 id:3 的语句，即最内层 select。

若 id 值相等，则从上而下执行。

select_type

table

table 表示该语句用到的表，也可能是临时表。

图上例子，先在 product_info 表中查询，然后在 b（product_comment） 表中查询，<derivedN>表示使用 id 为 N 的结果集，所以 <derived2> 是使用了 id:2 的查询结果。

partitions

对于分区表，显示查询的分区ID，对没有分区的表显示 NULL。

如果用分区键进行查询，partitions 仅显示所在分区，即会在所在分区查询，如 partitions : p1。

如果不使用分区键进行查询，会在全分区扫描，如 partitions : p1,p2,p3 。

type

mysql 查询数据的方式

key

查询优化器实际使用到的索引，如果没有可用索引，显示 NULL

rows

查询语句大致需要读取的行数

filtered

表示返回结果的行数占读取行数的百分比，值越大越好。

常见业务处理

删除重复数据

场景：删除同一订单，同一商品的多条评论，只保留最早一条。

• 查看是否有重复评论

SELECT order_id,product_id,COUNT(*) 
FROM product_comment
GROUP BY order_id,product_id HAVING COUNT(*)>1

• 备份数据
• 删除重复数据

DELETE a
FROM product_comment a JOIN(
    SELECT order_id,product_id,MIN(comment_id) as comment_id
    FROM product_comment
    GROUP BY order_id,product_id 
    HAVING COUNT(*)>1
) b ON a.order_id = b.order_id AND a.product_id = b.product_id AND a.comment_id>b.comment_id

捕获有问题的 sql

慢日志查询

• 启动慢日志

# 设置慢查询日志文件位置
SET GLOBAL slow_query_log_file = /sql_log/slow_log.log;
# 设置未使用索引查询时记录
SET GLOBAL log_queries_not_using_indexes = ON;
# 设置语句执行时间超过0.001秒时记录
SET GLOBAL long_query_time = 0.001;
# 开启
SET GLOBAL low_query_log = ON;

• 查看设置

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/lib/mysql/44cba697f41b-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)
mysql> show variables like "long_query_time";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.010000 |
+-----------------+----------+
1 row in set (0.00 sec)

分析慢查询日志

由于慢查询日志可能会生成大量且重复的内容，逐条查看不现实，可以使用 mysqldumpslow 工具，合并查看日志。

mysqldumpslow /var/lib/mysql/44cba697f41b-slow.log

备份

备份可以分为逻辑备份和物理备份。

• 逻辑备份

逻辑备份的结果为sql语句，适用于所有存储引擎，但比较慢

• 物理备份

物理备份是对数据库目录的拷贝，对于内存表，只能备份结构

mysqldump

xtrabackup

集群

目的：增加额外的服务器，同一集群中的服务器有相同的数据，任一服务器宕机后其他服务器可以取代。

主从复制

1. 主库db的更新事件(update、insert、delete)被写到binlog
2. 主库创建一个binlog dump thread，把binlog的内容发送到从库
3. 从库启动并发起连接，连接到主库
4. 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
5. 从库启动之后，创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

注意有的版本可能不会默认开启 binlog 二进制日志，需要手动开启。如果没有在一开始就配置，则需要关掉 mysql 服务重启。

配置步骤：

• 1.配置主从服务器参数

master：

# 开启二进制日志
# vi /etc/my.cnf
[mysqld]
log-bin = /data/mysq/log/mysql-bin  # 日志路径和前缀，配置这项就会开启二进制日志。
server-id = 1　　　　                 # 必需

slave:

# vi /etc/my.cnf
[mysqld]
relay-log = /data/mysq/log/relay-bin
read_only = on                                  # 禁止写操作
server-id = 2
skip_slave_start = on                           # 禁止自动启动复制链路，当服务器启动后，检查无误，再手动启动。

• 2.在 master 服务器上创建用于复制的数据库账号

用于 IO 进程连接 master 服务器获取 binlog 日志，仅需要 replication slave 权限即可。

create user 'repl'@'ip' identified by 'password';
grant replication slave on *.* to 'repl'@'ip';

• 3.备份 master 服务器上的数据并初始化 slave 数据

• 4.启动复制链路

mysql> change master to master_host='ip', master_user='repl', master_password='password', master_log_file='mysql-bin.00000x', master_log_pos=xxx;
mysql> start slave;
mysql> show slave status;