1.数据类型选择

2.存储引擎选择

绝大多数没有特殊需求下选择官方建议的InnoDB

InnoDB

1. 聚焦索引
   2. 锁粒度是行锁
   3. InnoDB 支持事务

ToKuDB/MySql.Achive

1. 高压缩比，尤其适用于压缩和归档(1:12)
   2. 在线添加索引，不影响读写操作
   3. 支持完整的 ACID 特性和事务机制

【参考基本理论中的优化实践】

3.参考DB设计规范文档/dbaprinciples

可能问题

1.隐式转换

MySQL跟JavaScript一样有数值类型和字符串类型之间的隐式转换。
但隐式转换不走索引，效率较低。隐式转换之后的条件可能变成固定的值true/false，不能正确判断条件。
因此写SQL文的时候一定要注意字段类型避免隐式转换。

2.DBA告警CPU升高查询过慢

1)排查方法

慢查询日志

• Rank
• Response time
• Rows examine

• mie

  看应用和运维监控

  2)解决方法

• 加索引

• 修改索引冗余，有时候几个G的表会有几十个G的索引

长的包括短的，形成冗余，把短的删掉
有唯一约束的组合冗余可以删掉

3.修改表结构的危害

• 索引重建：表结构变了，DDL变了，对应的索引也要重新建立。
• 锁表：改变DDL会锁全表
• 抢占资源：
• 主从延时：因为锁表了，所以主从表可能会不同步

4.数据量对表影响

1. 业务初期考虑不周，字段类型使用不合理，需要变更数据类型
   比如之前定义的流水号为int型，结果几年后业务量太大int型不够用了。
   2. 随着业务的发展，需要增加新的字段
   这种情况下建立增加从表，而不是字段。
   3. 在无索引字段增加新的业务查询，需要增加索引
   尽量在晚上业务量少或者停机维护的时候执行。

优化

1.大批量写入优化

• 使用PreparedStatement减少SQL解析，先发送一个SQL语句，中间的参数使用问号做占位符，数据库只解析一次SQL，再发送多次的参数提交给数据库。大大减少解析SQL成本。
• Multiple Values/Add Batch 减少交互
• 不用insert，使用Load Data，类似于csv的文件，直接导入
• 提前把索引和约束都删掉，减少索引插位的成本。数据导入完后，再一次性建立索引和约束。

2.数据更新

使用范围操作时容易引起GAP锁，导致并发执行的其他事务出错。
尽量不使用范围更新，而是指定具体记录。比如把更新对象记录先查询出来，再根据查询出来的主键更新具体记录。

3.模糊查询

• 遵循最左前缀原则，LIKE默认只走前缀匹配，如果是”a%”，可以走索引，”%a”则不走索引效率低下。
• 如果需要全文检索，建议建立索引的倒排检索
• SOLR/ElasticSearch：专门用来做全文检索的技术。如果查询结果太多buffer中放不下，会把数据放在磁盘中的临时文件中，降低效率。

4.连接查询

注意选择驱动表，驱动表越小，数据越明确
注意避免笛卡尔积，A表B表C表，下来是个非常可怕的数据量。

5.索引失效

失效情况：NULL，not，not in(可以用in)，函数(如日期函数)，or(可以用union，但不是union all)，like ‘%a’
注意：union去重，union all不去重
force index：强制走索引

6.设计查询SQL

• 查询数据量和查询次数的平衡：如果页面显示内容的SQL为主表从表连接查询，可以拆开查，根据主表查到ID，再根据页面显示行数查询对应ID的从表。
• 避免不必须的大量重复数据传输：如求和求平均值，在SQL中处理只用跟数据库交互一条数据，如果查询出来用JAVA代码处理则要交互多条数据。
• 避免使用临时文件排序或临时表：如果数据量过大buffer放不下，会把数据放在磁盘中的临时文件中，这样效率降低。
• 分析类需求，可以用汇总表：例如汇总一年的数据，可以做个汇总表，每天用批处理跑一下存起来，降低粒度，这样年底只用处理这365条数据。

设计优化实际案例

1.如何恰当选择引擎?

MyISAM：不需要强事务，数据操作量较大，例如偏向内容的CMS内容管理系统，动态网站的内容渲染，一些论坛。
InnoDB：需要强事务，例如电商金融支付
Memory：临时表，速度快，数量小，不需要持久化
Achive/Toku：归档数据，其中Toku压缩效率非常高，可以压缩到原始数据的几十分之一。
另：建议大家只要是B/S系统有WebServer，比如springboot或者网络前端挂ngnix，都开启glib压缩一下，可以把数据压缩到1/10以下，从而减少网络传输数据流量，提升网站加载速度。

2.库表如何命名?

不可用拼音缩写，多使用英文前缀，具体字段就用具体业务名称。

3.如何合理拆分宽表?

列数过多的表使用起来效率低。可以使用主表明细子表的结构，降低每个表的列数

4.如何选择恰当数据类型:明确、尽量小

4-1.char、varchar 的选择
4-2.(text/blob/clob)的使用问题? 文件、图片是否要存入到数据库?
text不建议使用。因为太长导致数据库容量降低。
建议把大的String存到文件里，以及图片作为文件，存在当前应用服务器的磁盘里，或者分布式的文件系统上。
数据库的字段中存文件的路径或者URL。
页面需要使用的时候，把路径取出来拼成真实的路径再返回到前端。或者把分布式文件存储的访问路径返回给调用方。
4-4.时间日期的存储问题?
如果对时间要求不是很严格，使用应用程序的时间。
如果对时间要求较严格，使用数据库端的时间，以long时间戳来存储。这样避免时区问题，以及方便比较大小。
4-5.数值的精度问题?
对精度有要求的字段可以用字符串的形式来表示，
也可以把内容的科学计数法拆成两个字段，比如0.000001=1*10的负6次方，拆分成1和-6两个字段。
另，int(3)和int(8)并没有什么区别，只是显示问题。

5.是否使用外键、触发器?

建议尽量不要使用外键和触发器

6.唯一约束和索引的关系?

只要定义了唯一约束，不管是一个列还是多个列，都默认给这个唯一约束上了索引，不用重复手动再为唯一约束的字段上索引。

7.是否可以冗余字段?

适当的冗余是有利的，减少连接子表进行查询的消耗。

8.是否使用游标、变量、视图、自定义函数、存储过程?

8-1.很多数据库的游标视图存储过程都是不一样的，很难做移植，比如从MySql换成PresentSQL，自定义函数就无法复用。
8-2.业务逻辑放在自定义函数存储过程中，数据库对其的处理能力并不如JAVA代码实现的效率高。

9.自增主键的使用问题?

数据量不大的时候建议使用。
数据量较大，特别是考虑到分布式场景，以及数据库拆分时，自增主键不适用。

10.能否在线修改表结构(DDL 操作)?

尽量不要做，DDL会锁表。包括用dump操作导出数据，也会锁表。
建议在半夜业务系统压力较小的时候执行。或者系统维护重启的阶段执行。

11.逻辑删除还是物理删除?

建议尽量逻辑删除

12.要不要加 create_time,update_time 时间戳?

建议加上，特别是数据迁移的时候会非常有用。

13.数据库碎片问题?

需要隔一段时间对数据库碎片执行压缩，但此操作也会锁表，建议在系统压力较小时执行。

14.如何快速导入导出、备份数据?

• 导出1：先锁表，后查询导出。
• 导出2：主库从库中内容是一致的，从库中的数据也是对齐的，直接拿从库的数据出来即可。
• 导出3：数据binlog订阅，模拟从库。
• 导入1：批量执行insert。先把索引全部删掉，再进行导入，导入完成后再设置索引。因为有索引的时候，每插入一条数据，都要根据索引判断位置挪一挪，效率太低。

常见场景分析

1.怎么实现主键

• 自增auto-increment：缺点是分布式情况下会冲突
• sequence：商用数据库Oracle/DB2使用，原子计数AtomicInteger，多个表使用一个sequence，不会造成跨表重复。
• 模拟seq：可惜MySQL没有，只能自己手动建个表来实现此逻辑。步长为1使用方便但不利于并发，步长1000效率高但容易丢数据。

以上三种顺序自增容易泄露商业机密，比如淘宝相隔24小时的订单号增量，就暴露商业数据。可以用以下三种方式解决：

• UUID：随机数，但比较占空间
• 时间戳+(随机数)：分布式时多个机器算出来的数字可能有重复
• 雪花算法snowflake：解决[时间戳+(随机数)]的问题，格式为：机器码+时间戳+内存自增。

2.高效分页

• 1.分页:count/pageSize/pageNum, 带条件的查询语句
• 2.常见实现-分页插件：如MyBatis，使用查询 SQL，嵌套一个 count，性能的坑?
• 改进1：如果count只需要查询主表，则手动重写 count从主表中查。
• 3.大数量级分页的问题：如limit 100000,20是真的从头到后走到第10w条索引上，可以用反序改进。
• 改进3：技术向:带当前页面的id查询对应下一页的下一拨
• 改进4：需求向:非精确分页，从第11页跳到第15页时，可以查出12~15的4页数据，页面只显示第4页。
• 需求方要求所有条件组合精确检索：走全文检索技术SOLR/ElasticSearch