数据库易忽略知识点

目录

1、在设计数据库表时，字段用于存储金额、余额时，选择的类型

2、SQL查询语句的步骤

3、sql的优化手段

4、分表后的查询处理以及排序

5、数据库字段的选择

6、存储时间数据注意点

7、提升insert的性能

8、MySQL的存储引擎（插件式的表存储引擎）

9、索引的失效

10、索引的优化：

11、B+树与B树的区别:

12、覆盖索引

13、事务可能产生的问题：

14、MVCC（多版本并发控制）

15、可重复读和提交读的MVCC

16、常见的数据库主键自增时就会使用到AUTO-INC锁

17、数据库如何避免发生死锁：

19、常见的日志类型：

---

1、在设计数据库表时，字段用于存储金额、余额时，选择的类型

        一般使用decimal，也可以使用numeric来修饰。

        在java中，decimal对应java中的BigDecimal

        numeric对应java的转换为numeric[1,4]是Short，numeric[5,9]是Integer，numeric[10,18]是Long，numeric[19]及以上都是BigDecimal

2、SQL查询语句的步骤

        连接器--》查询缓存（Mysql8.0被删除）--》解析器--》执行SQL（预处理器-》优化器-》执行计划-》执行器）--》返回记录

3、sql的优化手段

        ·优化查询语句

        ·优化索引

        ·优化表结构

        ·限制结果返回的数量

        ·使用缓存将经常查询的语句缓存其起来

        ·读写分离（主从同步）

        ·分库分表

4、分表后的查询处理以及排序

        处理：

                   ·做一个表的映射，即中间表

                   ·将数据先同步至离线仓库再查询，针对实时性要求不高的数据

                   ·数据量不多的话也可以使用多线程扫表，再聚合查询

        排序：排序字段是唯一索引

        

5、数据库字段的选择

        对于性别这一类的数据类型尽量使用tinyint，char效率高但费空间，使用varchar节省空间

6、存储时间数据注意点

        ·不要使用字符串类型存储时间

        ·首先使用TimeStamp（与时区相关，使用四个字节的空间），而不是DataTime（与时区无关，使用八个字节的空间）

        ·需要存储更小粒度的时间，使用BIGINT存储微秒级别的时间戳，或者使用Double存储秒之后的小数部分

7、提升insert的性能

        ·合并多条insert语句，减少日志

        ·修改参数bulk_insert_buffer_size，调大批量的缓存

        ·设置 innodb_flush_log_at_trx_commit = 0

        ·手动的使用事务

8、MySQL的存储引擎（插件式的表存储引擎）

        ·InnoDB存储引擎：默认的，处理大量短期事务

        ·MyISAM存储引擎：不支持事务和行级锁，使用表锁容易导致典型的性能问题

        ·Memory存储引擎：数据文件是存储在内存中，重启后结构保留，但数据容易丢失

        ·Archive存储引擎：只支持insert和select操作，通过缓存减少I/O，更适合日志和数据采集类

        ·CSV存储引擎：将CSV文件当做表来处理，无索引，只能当做一种数据交换机制

9、索引的失效

        ·where条件下在or左右两边都为索引列，否则失效

        ·使用模糊查询LIKE时，%在前面会失效（因为索引为前缀）

        ·违背最左匹配原则会失效（该原则就是向右匹配直到遇到范围查询）

        ·对索引使用函数会失效

        ·对索引使用表达式会失效

        ·隐藏式的类型转换会失效

10、索引的优化：

        ·前缀索引优化

        ·覆盖索引优化（在索引类中就包含查询值，不需要回表）

        ·主键索引最好是自增的

        ·防止索引失效

11、B+树与B树的区别:

        ·B+树非叶节点不存储数据，所有数据存储在叶节点；B树的key和data是在一起的

        ·B+树可使用范围查询

        ·B+树更适合外部存储，非叶节点不存储数据，固每层可以容纳更多元素，介绍I/O

12、覆盖索引

        索引包含覆盖所有需要查询的字段的值。

13、事务可能产生的问题：

        ·脏读

        ·不可重复读

        ·幻读

        ·丢失修改（两个事务同时修改，第一个修改被第二个覆盖，发生丢失修改现象）

14、MVCC（多版本并发控制）

        读取数据时通过类似快照的方式记录下来，使读锁和写锁不再冲突，不同事务看到自己的特定版本，叫做版本链。

15、可重复读和提交读的MVCC

        可重复读创建一个Read View，而提交读每次读取就创建一个Read View。

16、常见的数据库主键自增时就会使用到AUTO-INC锁

17、数据库如何避免发生死锁：

        ·设置事务等待锁的超时时间

        ·开启主动死锁检测

19、常见的日志类型：

        重做日志（redo log）、回滚日志（undo log）、慢查询日志（slow query log）、错误日志（error log）、普通查询日志（general query log）、二进制日志（binary log）