注：以mysql为例其他类同。mysql测试版本5.7.2，其他版本部分情况可能有出入。
一、数据库基础：
（1）数据库引擎一般情况下使用InnoDB（mysql5.5后默认引擎）
mysql5.5以前默认引擎为：MyISAM；5.5之后默认为InnoDB；
*InnoDB：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高；
MyISAM：不支持事务、不支持外键，表级锁，提供高速存储和检索以及全文搜索能力，适合应用在批量insert和频繁select操作的表中；
InnoDB适用于大多数场景；
MyISAM比较典型的场景：
1、主从分离，从（读）的引擎。
2、需要大批量insert或者导数据时。
（2）字符集使用utf8mb4或utf8
utf8是默认字符集，满足大多数情况；
*utf8mb4是utf8超集，兼容性更好，一些生僻字utf8可能出现乱码，而utf8mb4不会；
utf8mb4会消耗更多空间，且5.5.3 版本后才支持。
（3）索引类型（默认：B-Tree）
*1、B-Tree 索引的特点
1-1、B-tree 索引可以用于使用 =, >, >=, <, <= 或者 BETWEEN 运算符的列比较；
1-2、 LIKE 内容不以%开头索引生效。
2、Hash 索引的特点
支持且性能更优的情况：
2-1、索引只能够用于使用 = 或者 <> 运算符的相等比较(但是速度更快)；
2-2、依赖于这种单值查找的系统（ “键-值存储”），Hash索引性能更优。
不支持的情况：
2-3、Hash 索引不能够用于诸如 < 等用于查找一个范围值的比较运算符；
2-4、优化器不能够使用 hash 索引来加速 ORDER BY 操作
3、复合索引中：
Hash必须进行全键匹配。而 B-tree 索引，任何该键的左前缀都可用以查找记录。
二、语句操作：
（1）or使用注意事项
or分隔开的条件，大多数情况下都会导致其他条件的索引失效，尽量不用；
（2）单一结果查询注意事项
当结果只要一行数据，并且查询条件中没有唯一索引时加 LIMIT 1，告诉数据库我只要一条数据。
（3）负向查询注意事项
1、负向查询包括NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描；
2、使用时，需结合有索引的其他过滤条件。不能使用负向查询作为唯一条件。
（4）limit查询注意事项
使用limit时，条件允许情况下，增加区间条件。如：id > 100
三、字段设计事项：
（1）把字段定义为NOT NULL并且提供默认值
1、null的列需要额外空间来标识，使得空间被占用更多；
2、null需要进行特殊处理，增加数据库处理的复杂性，null很多时处理性能会降低很多；
3、注意区分“空值”（''）和null不是同一个东西；
4、null表示什么也不是， 用“=、>、< ...” 所有的判断，结果都是false，
mane！=‘my’时也不会出现包含null的内容，只能用is not null，is null
（2）使用int类型存储金额、货币等，特别涉及到乘除计算的
1、int可以避免数据入库前和入库后保留小数位不一致问题。
2、int可以避免在前端、后端、数据库之间精度不一致问题。
3、涉及到计算时均在后端完成，特殊情况下需要到前端或者数据库计算时，只能用加减计算。
四、性能注意项：
（1）尽可能不使用存储过程、视图、触发器、Event
1、数据库的专长在于存储及索引，而以上操作涉及到计算、占用临时空间、及占用较长数据库连接时间。
2、将计算、空间的占用放到程序中做，缩短数据库连接时间避免数据库被过多的长占用拖慢。
3、同时通过程序做逻辑运算也有利于提高逻辑代码的维护性。
4、必要时候可以多访问1,2次数据库，几次的短连接比一次较长连接要节省性能，并且优化空间更大。
五、项目中可能出现的坑：
（1）数据表、数据字段必须加入中文注释，且尽量详细
反正我一直被这个问题坑
（2）关联其他表的字段需注明被关联的表及字段
反正我也一直被这个问题坑
六、分布式、大数据下注意项：
（1）in和join语句的选择
1、表拆分时，join语句会使得分片规则失效导致所有分片查询。
所以选择in或者join时需要考虑相关的表是否可能之后会拆分，如果会尽量使用in；
2、当联合查询语句比较复杂时，in优化空间一般更大；
3、代码根据业务模块拆分时，in比起join更容易支持或调整。
（2）幂等性设计的考虑
复杂的系统中，需要在表设计层面考虑数据幂等性问题，避免重复提交带来的数据异常：
1、根据业务在表中对多个业务关联ID做唯一约束，确保数据初始化的唯一性。
2、当表数据无法满足业务关联ID做唯一约束时，则需要增加业务编号列做唯一约束。
3、数据中如状态修改时，需要加入当前业务状态作为修改条件确保幂等性。
4、当表无法满足数据幂等性时，则需要考虑由上游业务产生全局ID，拿到ID的方法才可以修改数据。
（3）分片规则的考虑
某些可能产生大量数据的表在设计初则可能需要考虑通过增加一个冗余字段来考虑后期的分片依据。
如流水表考虑增加“年-月”内容的字段，而之后可以根据“年-月”分表。
七、命名规范：
（1）库名、表名、字段名：小写，下划线风格，禁止拼音英文混用。
（2）表名固定开头t_命名。
八、查询优化注意事项：
（1）无业务需求尽量避免使用order by，通过索引直接返回有序数据。
（2）select时尽量只选择必要字段，提高性能。
某些框架对这个支持不好的当我没说。但是对于字段很多的表，哪怕通过原生态方式也要实现。
九、索引使用注意事项：
（1）使用正确的类型查询索引才会生效
phone为varchar(20)
where phone=18888888888和where phone='18888888888'结果一样，但前者会导致索引失效
（2）索引建立注意项：
1、频繁的字段不要建立索引；
2、数据重复度高的字段不要建立索引（如状态）。
（3）复合索引
1、复合索引中的顺序根据数据维度高到低建立（数据重复度低到高）；
2、使用时需要满足最左前缀。既：聚合索引为（A为字段）A1_A2_A3时，查询条件中A1必须出现，否则会导致该聚合索引失效。
十、场景优化小技巧：
（1）批量插入、初始化环境大批量数据
1、大批量数据插入，可将表设置成为MyISAM，并通过disable keys将唯一索引关闭；
2、大批量数据插入非空Innodb表，可采取如下措施提高效率：
2-1：导入数据时按照主键顺序排列；
2-2：导入数据前使用set UNIQUE_CHECKS=0,关闭唯一性校验，导入后恢复；
2-3：如果使用了自动提交，导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入后恢复；
3、从一个文本文件装入一个表时，使用LOAD DATA INFLIE ,比一般的insert语句快很多倍；
十一、执行计划分析说明及注意事项：
（1）explain命令
通过explain命令获取mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
***（2）explain解析优化目标
1、通过分析结果以及实际需求建立索引；
2、type类型：每个查询都要使用索引以提高查询效率,至少达到range级别，最好能达到ref；
3、追求key_len和rows最小；
4、注意项：
4-1、聚合索引中的顺序根据数据维度高到低建立（数据重复度低到高）；
4-2、内容重复程度高的数据建立索引反而会更慢（如状态字段）；
4-3、所有索引（含聚合索引等）不要超过5个。
（3）explain分析后的结果解析详解
1、select_type查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询：
1-1、SIMPLE：简单的select查询，查询中不包含子查询或者union；
1-2、PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary；
1-3、SUBQUERY：在select 或 where列表中包含了子查询；
1-4、UNION：若第二个select出现在union之后，则被标记为union；
1-5、若union包含在from子句的子查询中，外层select将被标记为derived。
2、type访问类型，sql查询优化中一个很重要的指标：
结果值从好到坏依次是：system > const > eq_ref > ref > range > index > ALL
2-1、system：表只有一行记录（等于系统表），这是const类型特例，平时不会出现，可以忽略；
2-2、const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引；
2-3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配（1对1）；
2-4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行；
2-5、range：索引范围扫描；
2-6、index：索引全扫描；
2-7、ALL：全表扫描；
3、possible_keys：
查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用。
4、key：
实际使用的索引，如果为NULL，则没有使用索引。
5、key_len：
索引中使用的字节数，查询中使用的索引的长度（最大可能长度），非实际使用长度，越短越好;
6、ref：
显示索引的哪些列；
7、rows：
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
8、Extra：
不适合在其他字段中显示，但是十分重要的额外信息。
十二、mysql复制、主从、读写分离
（1）原理
1、主库在数据提交时会把数据变更作为事件记录在二进制日志文件Binlog中；
2、可通过sync_binlog控制binlog日志刷新到磁盘的频率；
3、主库推送二进制日志文件binlog中的事件到从库的中继日志Relay Log，之后从库根据中继日志RelayLog重做数据变更操作，通过逻辑复制达到主从库的数据一致；
4、MySQL通过3个线程来完成主从库之间的数据同步，其中binlog dump线程跑在主库上，I/O线程和sql线程跑在从库上；
5、当从库启动复制时，首先创建I/O线程连接主库，主库随后创建binlog dump线程读取数据库事件并发送给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志replay log中去，之后从库上的sql线程读取中继日志中更新的数据库事件并应用；
同步流程图
中继日志流程图（图片来源互联网侵删）
网友评论