数据库心得总结

数据库：

问题1：数据库三范式？反模式？

第一范式：强调属性的原子性约束，要求属性具有原子性，不可再分解。如一个活动表中的活动地址不可再细分为国家、省份、城市、市区、位置；

第二范式：强调记录的唯一性约束，表必须有一个主键，并且没有非主键列必须完全依赖于主键，不能只依赖于主键的一部分。如一张版本表（版本编码、版本名称、产品编码、产品名称），主键是版本id和产品id，产品名称只依赖于产品id，因而不属于第二范式，需要把产品id和产品名称独立于一张表；

第三范式：强调属性冗余性的约束，要求非主键列必须直接依赖于主键。如订单表（订单编码、顾客编码、顾客名称。主键是订单编码，其中顾客编码和顾客名称完全依赖于主键，符合第二范式，但是顾客名称依赖于顾客编码，间接依赖于主键，需要拆分）

反模式：用空间换时间提升性能，采取数据冗余的方式避免表之间的关联查询，至于数据一致性问题，难以满足数据强一致性，一般达到用户一致，系统经过较短时间的自我恢复和修正，数据最终达到一致。

提升性能最好的方式是在同一张表中保存润宇数据，如果能容许少量的脏数据，创建一张完全独立的汇总表或缓存表是非常好的方法。如设计一张“下载次数表”来缓存下载次数信息，可使在海量数据的情况下，提高查询总数信息的速度。或者用text或者blob类型的列存储json格式的数据，这样在将新属性添加在这个字段时，不需要改变表的结构（但需要获取整个字段内容进行解码来获取指定的属性，无法进行索引、排序、聚合等，可考虑文档型数据库MongoDb）

 

问题2：MySQL支持的数据类型？

大致分为三类：数值、日期/时间、字符（串）

MySQL 5.0 以上的版本：

1、一个汉字占多少长度与编码有关：

UTF－8：一个汉字＝3个字节

GBK：一个汉字＝2个字节

2、varchar(n) 表示 n 个字符，无论汉字和英文，Mysql 都能存入 n 个字符，仅是实际字节长度有所区别

3、MySQL 检查长度，可用 SQL 语言来查看：

select LENGTH(fieldname) from tablename

 

问题3：一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，再把 MySQL 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15？

如果表的类型是innodb,不重启mysql的情况下id为18，重启或者optimize后为15，如果是myisam，则为18，myisam会把自增主键的最大值记录在数据文件里面。生产数据，不建议屋里删除，不能恢复。

 

问题4：innodb的行锁怎样实现？

innodb的锁的策略是next-key锁，即record lock + gap lock，通过在index上加lock实现的

1）如果index为unique index，即降级为record lock行锁

2）如果是普通index，则为next-key lock

3）如果没有index，则直接锁住全表，即表锁

innodb是基于索引来完成行锁 如 select * from t where id = 1 for update

for update 可以根据条件来完成行锁锁定，并且id是有索引键的列，如果id不是索引键则innodb将完成表锁，并发无从谈起。

 

共享锁（读锁）：不堵塞，多个用户可以同时读一个资源，互不干扰

排他锁（写锁）：会阻塞其他的读锁和写锁，只允许一个用户进行写入，防止其他用户读取正在写入的资源

 

问题5：死锁？

当多个进程访问统一数据库时，其中每个进程拥有的锁都是其他进程所需的，导致每个进程都无法进行下去，简单来说，进程A等待进程B释放B的资源，B又等待A释放A的资源，互相等待形成死锁。两个线程 不同方向 相同资源

 

四个必备条件：

互斥条件：一段时间内某资源只由一个进程占用，其他进程只能等待其释放

请求和保持条件：进程已经保持至少一个资源，但又提出新的资源请求，而该资源已被其他进程占有，此时请求进程阻塞，但又对自己已获得资源保持不放

不剥夺条件：进程已获得的资源，在未使用完之前不可被剥夺，只能在使用完由自己释放

环路等待条件：死锁发生时，必然存在一个进程-资源的环形链

 

解决和降低死锁：

1）降低隔离级别（少加锁）

2）碰撞检测： 把所有的事务单元和维持的锁、等待的锁记录下来，如果发生碰撞，终止一边，效率最高。

3）等锁超时

4）按同一顺序访问对象

5）避免事务中的用户交互

 

问题6：MySql查询执行的顺序：每一步都产生一个虚拟表VT

1）执行from语句：知道从哪个表开始，如果是两个表会执行笛卡尔积（两表记录数相乘），得到VT1

2）执行on过滤：根据on指定的条件去掉不符合条件的数据,得到VT2

3）添加外部行：如左连接，添加过滤掉的数据，生成VT3

4）执行where过滤：得到VT4

5）执行group by分组：得到VT5

6）执行having过滤：得到VT6

7）select列表：选出需要的内容，得到VT7

8）执行distinct子句：会创建一张临时表，对进行distinct的列增加一个唯一索引，去除重复数据

9）执行order by子句：排序，得到VT8

10）执行limit子句：选出从指定位置开始的指定行数据

 

问题7：MySql sql优化

1）分析查询速度：可用记录慢查询日志、使用show profile、show status、explain

2）优化查询过程中的数据访问

1）避免使用以下sql语句（查询不需要的记录，使用limit解决；使用select *无法使用索引；重新查询相同的数据，可以使用缓存）

2）是否存在扫描额外的记录（使用explain分析，可以改变数据库和表的结构，修改数据库范式，把所有列放在索引中，重写sql）

3）优化长难的查询语句

1）切分查询：将大的查询分成多个小的相同的查询

2）分解关联查询：在代码中进行拼接

4）优化特定类型的查询语句

1）优化count()查询：增加汇总表，使用缓存，使用explain查询近似值

2）优化关联查询：确保on和using子句的列上有索引，确保group by和 order by中只有一个表的列，这样才能使用索引

3）优化子查询：使用关联查询替代

4）优化limit子句：偏移量大时，记录上一次查询的最大ID，下次查询时直接根据ID来查询，只是加一个where条件，id>ID

5）优化union：使用union all的效率高于union

 

问题8：什么是MVCC？

mvcc本质上就是copy on write，是一种用来解决读写冲突的无锁并发控制，为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，这样读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。可以解决读写、写读、读读不冲突，并发度最好。 （从一个队列到多个队列）

实现事务和事务单元之间的并发度尽可能高、顺序又要保证的场景有三种做法： 1）全部顺序 2）读写锁 3）mvcc

逻辑时间戳（版本号）

 

问题9：单机事务 ACID 隔离级别 故障恢复

1）原子性：undo日志回滚到之前的版本

2）一致性：can（happen before）在一个事务前加锁

3）隔离性：以性能为理由，对一致性的破坏

1）序列化读写（排它锁）

2）读写锁（可重复读、读锁不能被写锁升级、读读可并行、读已提交（读读并行、读写并行（导致不可重复读））、读未提交（写读并行））

隔离性扩展：快照读（不用锁，写的时候可以在回滚段里读，适合读多写少、并发性比读未提交高）、mvcc （copy on write 和 无锁编程）

4）持久性：事务完成后，该事务对数据库的更改永久保存在数据库中(RAID)

隔离级别：读未提交、读已提交、可重复读、序列化

事务故障恢复 ：1）回滚 2）系统down机：recovery

事务调优：

1）减少锁的覆盖范围：myisam表锁到innodb的行锁原位锁到MVCC多版本

2）增加锁上可并行的线程数：读所写锁分离、允许并行读取数据、多线程并行读取

3）选择正确锁类型：

悲观锁：使线程到blocking状态

乐观锁：适合并发不太激烈的情况

事务读读写写的时候可能出现死锁，但由于U锁（更新锁），所以不出现死锁

分布式事务：

优点：去中心化理论上无限的扩展能力、数据安全性、服务可用性

缺点：共享数据困难、更多的延迟（消息复制的代价）、确定性丧失