1. 数据库的设计指南

1. 数据库的设计指南

1.1 数据库设计三大范式

• 第一范式(确保每列保持原子性)
• 第二范式(确保表中的每列都和主键相关)
• 第三范式(确保每列都和主键列直接相关,而不是间接相关)

1.2 数据设计指南

• 对地址采用国家,省,市,区,甚至街道,详细地址等多个字段存储方便后续使用

• 选择数据类型和文本类型尽量充足

• 每张表最好增加删除标记字段

• 表的索引数量要适中

• 设计表的时候,适用冗余字段,减少join操作

• 对于一些比较频繁的作为查询条件的字段建立索引

• 设计表的时候,所有表和字段都添加相应的注释

• 设计数据库表时加上创建,修改人和时间

• 尽量把所有列定义为NOT NULL 减少非空判断

• 数据库过大时可以进行分库分表操作

1.3 有效的mysql优化方法

1. select语句务必指明字段名称,杜绝使用select *
   select * 增加很多不必要的消耗(CPU,IO,内存,网络带宽),任何场景都不要使用select * from table,要使用具体的字段列表代替 “*”,不要返回用不到的任何字段

2. 选择合适的字段属性
   一般来说,数据库中的表越小,执行的查询速度也就越快,因此,我们在创建表的时候,为了性能可以更好,也不浪费存储空间,设计表中字段的长度够用就行,不要太长,要尽可能小一些

   2.1 不要所有的字符串类型的都是varchar(255),之前就看到过一个同事这样操作,很不友好
   2.2 身份证号码,性别,年龄等字段都是定长的,不要一股脑的都是varchar(255),bigint(20)之类的

3. 不使用ORDER BY RAND()
   在有随机返回需求的时候,很多新手都喜欢这样使用,但是仔细了解后就会知道这又非常可怕的性能问题
   mysql会不得不执行RAND()函数很耗cpu时间,而且这是为了每一行去记行,然后再对其排序,会让数据库的性能呈指数级下降

4. 选择合适的字段类型
   字符串长度是固定的,就用char类型的,char是定长的,不要用varchar

5. 杜绝使用select count(*)

6. count(*/1/字段)的区别

   • count(1)和count( * )的作用都是检索表中所有记录行的数目,不论其是否包含null值.但是count(1)的效率比count( * )高一些
   • count(字段)的作用是检索表中的这个字段的非空行数,不统计这个字段值为null的记录
   • 如果表中没有主键(Primary key),那么count(1)比count(*)块;
   • 如果表中有主键的话,那主键作为count的条件时候count(主键)最快

7. 优化分页方式提高分页查询效率
   select id from 表名 limit 1000,20
   随着表数据量的增加,直接使用limit分页,页数越大查询速度越慢.这个时候呢,优化的方式如下:让客户端记录下每次查询的最大主键id,然后传递给服务端,服务端根据这个最大的id来限制下一页的起点位置.优化sql如下,将大大提高查询效率
   select id from bas_activity_02_202212 where id>10000 limit 20

8. update语句优化
   如果只更改1,2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志

9. 避免在where子句中对字段进行null值判断
   对于null的判断会导致引擎放弃使用索引而进行全表扫描

10. 字符串类型尽量使用默认值,不要有null值

11. 尽量不要使用%前缀模糊查询 会导致索引失效

12. 对慢sql查询进行优化对慢sql查询进行优化
    要尽量避免全表扫描,首先应考虑在where及order by 涉及的列上建立索引

13. 尽量避免在where子句中对字段进行表达式操作

14. 表的索引数量要适中

15. 避免导致索引失效的场景发生

16. 尽量避免隐式类型转换

17. 关注范围查询语句-导致部分索引失效的情况

18. 大数量表的关联查询 表关联时,关联前尽量过滤数据量,可以减少关联后的集合量级,提高查询速度

19. 大数据量插入和更新时,使用批处理 大量数据的更新操作,要尽量使用批量数据,而不能foreach一条一条处理

1.4 常见mysql数据库面试题

1. 导致mysql索引失效的场景

   • like 以%开头,索引无效,当like前缀没有%,后缀有%时,索引有效

     EXPLAIN select * from 表名 where activity 字段 like '%读书'
     EXPLAIN select * from 表名 where activity 字段 like '读书%'
     

   • 查询条件使用or关键字,其中一个字段没有创建索引,会导致整个查询语句索引失效

     EXPLAIN select * from 表名 where 字段='读书活动' or 字段='河北省邢台市'
     

   • 查询条件使用or关键字,or语句两边为’>‘和’<'范围查询时,索引失效

   • 联合索引不满足最左前缀原则

   • 联合索引遵循从最左原则-想要让查询条件走索引,则需满足:最左边的字段要出现在查询条件中

   • (隐式类型转换)字符串类型查询一定要将条件中的数据用引号括起来,否则不使用索引(字符串中如果存的是纯数字,不加引号数据类型出现隐士转化,转换为int)

   • 查询条件使用is null时正常走索引,使用is not null时,不走索引

   • 在非主键索引的其他索引字段上使用not,><,!= 操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描.优化方法:key<>0 改为key>0 or key<0

   • 对索引字段进行计算操作或字段上使用函数

     EXPLAIN select * from 表名 where id=12982349322; //索引有效
     EXPLAIN select * from 表名 where id*1=12982349322;//索引失效
     EXPLAIN select * from 表名 where id=12982349322*1;//索引有效
     

   • order by 主键 走索引 order by 非主键(且查询内容包含非索引列)走索引

2. 有哪些慢sql的优化经验?

   • 分析语句,是否加载了不必要的字段/数据,禁止select *,只查询需要的字段即可
   • 善用explain查询sql执行计划
   • 如果sql很复杂,则看是否优化空间,去优化sql结构,能走索引的走所以i你,能缩小范围
   • 如果表数据量太大,考虑分表,比如使用shardingjdbc中间件分库分表,按分片键,时间等拆分数据
   • 对于热点数据,利用缓存,减少数据库查询次数,比如数据字典,菜单,权限等