1. 数据库的设计指南

Love The Way You Lie 2024-03-16 11:55 5阅读 0赞

### 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中间件分库分表,按分片键,时间等拆分数据
     *  对于热点数据,利用缓存,减少数据库查询次数,比如数据字典,菜单,权限等