1.数字类型:
①如果一个数字是unsigned,它们相减的值如果是负数会报错out of range。解决办法:
SET sql_mode='NO_UNSIGNED_SUBTRACTION';
②不要用float和double类型,使用decimal表示小数,而如果表示金额最好用分。因为decimal最大(8,2),只能表示到百万级别。
③自增主键要用big int,因为int最大值是42亿,到达上限后插入会报错。自增主键在5.7及以下版本不会持久化当前主键指针,重启后接着最大值往后增加,这样在关联表的时候可能出问题。
2.字符类型:
①CHAR还是VARCHAR?CHAR最大长度255字符,VARCHAR最大65535长度字符。由于一般会用UTF8MB4,每个字符占用4字节,用CHAR也是变长的。所以推荐用VARCHAR。
②排序规则。设置表的时候要设置排序规则,一般为utf8_general_ci,作用是设置数据库比较字符串的方法,ci结尾表示不区分大小写,cs结尾区分大小写,bin是按二进制比较。
③数据库字符枚举。如性别字段只能写M或F。
Create Table: CREATE TABLE `User` (
`id` bigint NOT NULL AUTO_INCREMENT,
`sex` enum('M','F') COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
或者
Create Table: CREATE TABLE `User` (
`id` bigint NOT NULL AUTO_INCREMENT,
`sex` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `user_chk_1` CHECK (((`sex` = _utf8mb4'M') or (`sex` = _utf8mb4'F')))
) ENGINE=InnoDB
3.日期类型:
用timestamp、datetime还是int?timestamp最大值2038年1月19日,有隐患。int可维护性差。推荐datetime。
4.JSON格式数据:
MySQL5.7之后已经支持直接存JSON数据,适合存静态数据。例子如下:
CREATE TABLE UserLogin (
userId BIGINT NOT NULL,
loginInfo JSON,
PRIMARY KEY(userId)
);
--查询方式:
SELECT
userId,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchat
FROM UserLogin;
--等义于
SELECT
userId,
loginInfo->>"$.cellphone" cellphone,
loginInfo->>"$.wxchat" wxchat
FROM UserLogin;
对JSON设置索引的办法是先产生虚拟字段,再对虚拟字段设置索引,语句如下:
ALTER TABLE UserLogin ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone");
ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);
5.主键设置:
①自增主键?只能用于非核心表。问题有:5.7及以下的重启回溯;并发性能;分库分表不能全局唯一
②UUID?UUID = 时间低(4字节)- 时间中高+版本(4字节)- 时钟序列 - MAC地址,所以时间是逆序的,8.0提供UUID_TO_BIN解决时间逆序的问题。
6.表压缩:
主要是页压缩,有两种,一种是COMPRESS,一种是TPC。
COMPRESS页压缩内存缓冲池存在压缩和解压的两个页,会严重影响性能,一般用于日志,监控等性能要求低的表。比如按时间分库,旧库基本不修改,用这种方式存储,空间消耗小。
TPC是需要按操作系统的文件系统的最小空间对齐压缩存储数据,需要刷新到磁盘的时候压缩一次,通过减少IO提高性能。
7.索引:
索引是提升查询速度的一种数据结构,MYSQL中使用B+树存储索引。B+树对无序索引进行增删时,性能开销较大,所以要求主键必须是有序的,所以不推荐UUID,推荐排序UUID。
SELECT * FROM schema_unused_indexes
WHERE object_schema != 'performance_schema';
以上是查看没有用到的索引的语句,用于索引优化。
8.索引的组织(MVCC机制):
①聚集与非聚集:对于INNODB引擎,分为聚集索引和非聚集索引,主键排序后组织数据按B+树存储就是聚集索引。其他的索引排序后只保存一个主键,按B+树存储,就是非聚集索引。对于堆表,全都是非聚集索引。
②通过虚拟列实现索引优化:虚拟列不占存储空间。前面介绍过对JSON数据产生虚拟列,然后加索引的办法。这个办法同样适用于where条件中有函数的情况,可以将函数作为虚拟列并加索引。
③索引覆盖:当非聚集索引中的列已经覆盖了要查询的列,就不会再回表查询主键的索引数据。这样可以很大的提高性能。
9.JOIN连接:
有两种实现,Nested Loop Join和Hash Join。Nested Loop Join先查驱动表,再逐条查关联表。Hash Join先对驱动表的查出来的记录建立哈希表,关联表去关联哈希表中的记录。
10.子查询:
在8.0中,使用子查询不用有太多担心。5.7及以下版本需要注意,执行计划标识为DEPENDENT SUBQUERY的是依赖子查询,执行效率很低,一般需要手动改sql,转化为多表连接,只要执行计划写SUBQUERY,就转换为独立子查询。或者也可以转化为派生表关联查询。
SELECT
*
FROM
orders
WHERE
(o_clerk , o_orderdate) IN (
SELECT
o_clerk, MAX(o_orderdate)
FROM
orders
GROUP BY o_clerk);
这个例子是每个收银员最后一次查订单的记录,对于5.7版本是依赖子查询。
可改为派生表关联:
SELECT * FROM orders o1,
(
SELECT
o_clerk, MAX(o_orderdate)
FROM
orders
GROUP BY o_clerk
) o2
WHERE
o1.o_clerk = o2.o_clerk
AND o1.o_orderdate = o2.orderdate;
left join也是同理,略去。
11.分区表:
分区表就是把物理表结构相同的几张表,通过一定算法,组成一张逻辑大表。作为分区条件的列是分区列,主键必须包含分区列,否则报错。分区之后数据存在多个文件中,但是逻辑上仍然按一张表操作。
唯一索引:唯一索引必须包含所有分区列,且只在分区唯一,不能保证全局唯一。
性能提升:分区不是用于解决性能问题的技术手段,而是用于方便数据迁移和管理。
12.主从复制:
①基本原理:master写本地日志的同时,一个异步线程写中继日志,slave有额外的worker线程负责读取新增的记录并回放。
②类型:异步。半同步(master要求若干的slave返回成功才算sql执行成功,如果master的事物commit之后等待ack,就是有损的半同步;如果提交之前等待ack就是无损的半同步)。多源(一个slave对应多个master)。延迟(slave接收日志后延时回放)。
③主从的延时的优化:把 DELETE 删除操作转化为 DROP TABLE/PARTITION 操作;大事务打散成小事务。从机并行复制日志(MTS机制)。
④延时时间监控:通过创建心跳表监控。SHOW SLAVE STATUS的属性Seconds_Behind_Master不准确,不应该采用。
13.高可用方案:
①VIP方案:master故障时需要连slave,VIP会随着漂移(DNS服务器控制),客户端只需要连接VIP
②业务的高可用:业务配一系列数据库列表,主库挂了就用备库,访问数据库的逻辑隐藏在业务逻辑的状态中,其他服务能通过状态判断出访问哪个数据库。
③硬件高可用与业务高可用结合:业务高可用解决延时和失败的问题,硬件多活高可用解决数据同步问题。
14.备份方案:
①自带命令全量dump:mysqldump -A --single-transaction > backup.sql
②开源工具mydumper进行全量dump:mydumper -o /bak -r 100000 --trx-consistency-only -t 8
参数 -r 表示每张表导出 100000 条记录后保存到一张表;
参数 —trx-consistency-only 表示一致性备份;
参数 -t 表示 8 个线程并行备份。
③开源工具Xtrabackup进行全量物理备份
④binlog增量备份:mysqlbinlog --read-from-remote-server --host=host_name --raw --stop-never binlog.000001
15.二阶段提交(2PC/XA)和柔性事物:
可以利用MySQL的XA二阶段锁机制,通过中间件或者业务代码驱动数据库实现二阶段提交。如果是中间件驱动,则可以像调用本地事物一样调用分布式事物。
这种机制能保证强一致性,但是有明显缺点。一个是性能差,需要两个prepare和两个commit。一个是产生行锁,一旦协调者挂了就不能自动释放锁。两个分布式挂了,要额外处理悬挂事务。
柔性事务是指分布式事务由业务层实现,通过最终一致性完成分布式事务的工作。这个办法主要是出于性能和可用性考虑,放弃了强一致性。
柔性事物框架有 TCC、SAGA、SEATA ,或者消息队列自行实现。自行实现的基本方案是三阶段提交,第一个库记录数据和未完成状态 -> 第二个库记录数据和消息 -> 第一个库修改状态。三个定时任务扫描状态作为补偿机制。