Java复习笔记之mysql_java mysql复习-CSDN博客

本文链接：https://blog.csdn.net/weixin_39287069/article/details/98844493

怎样设计一个数据库？

RDBMS

要设计一个关系型数据库，首先要将其划分成两大部分，一个是存储部分，该部分类似于一个文件系统，将数据持久化到存储设备之中；另一部分是程序实例模块，来对数据进行逻辑上的管理，该部分包含，将数据的逻辑关系转换成物理存储关系的存储管理模块，优化效率的缓存模块，将SQL语句进行解析的SQL解析模块，记录操作的日志管理模块，进行多用户管理的权限划分模块，灾难恢复模块，优化数据查询效率的索引模块，以及支持数据库进行并发操作的锁模块共8个模块

索引模块：

为什么要使用索引？

避免全表扫描查询数据，加快查询速度

什么样的信息能成为索引？

主键，唯一键，以及普通键等

索引的数据结构

生成索引，建立二叉查找树进行二分查找

生成索引，建立B-Tree结构进行查找

生成索引，建立B+-Tree结构进行查找

生成索引，建立Hash结构进行查找

Tree

定义：

根节点至少包含两个孩子

树中每个节点至多包含有m个孩子（m>=2)

除根节点和叶子节点外，其他每个节点至少有ceil(m/2)个孩子

所有叶子节点都位于同一层

假设每个非终端节点中包含有n个关键字信息，其中：

Ki (i=1..n)为关键字，且关键字按顺序升序排序K (i-1) < Ki
关键字的个数n必须满足：[ceil(m/2)-1] <=n <=m-1
非叶子节点的指针：P[1], P[2], ..., P[M]; 其中P[1]指向关键字小于K1的子树，P[M]指向关键字大于K[M-1]的子树，其他P[I]指向关键字（K[i-1], K[i])的子树

B+ -Tree

B+树是B树的变体，其定义基本与B树相同，除了：

非叶子节点的子树指针与关键字个数相同

非叶子节点的子树指针P[i], 指向关键字值[K[i], K[i+1])的子树

非叶子节点仅用来索引，数据都保存在叶子节点中

所有叶子节点均有一个链指针指向下一个叶子节点（方便统计）

结论：

B+Tree更适合用来做存储索引

B+树的磁盘读写代价更低（内部节点相对于B树更小）

B+树的查询效率更加稳定（非叶子节点并不存储关键字）

B+树更有利于对数据库的扫描（叶子节点的链指针）

Hash索引

缺点：

仅仅能满足“=”, “IN”, 不能使用范围查询

无法被用来避免数据的排序操作

不能利用部分索引键查询

不能避免表扫描

遇到大量Hash值相等的情况后性能不一定就会比B-Tree索引高

密集索引和稀疏索引的区别：

密集索引文件中的每个搜索码值都对应一个索引值（1，2，3，4，5）索引项和数据项一对一）

稀疏索引文件只为索引码的某些值建立索引项（1，3，5）索引项和数据项一对多

InnoDB

若一个主键被定义，该主键则作为密集索引

若没有主键被定义，该表的第一个非空索引则作为密集索引

若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）

非主键索引存储相关键位和其对应的主键值，包含两次查找

InnoDB索引和数据是存储在一块的，而MyISAM是分开的

衍生出来的问题，以mysql为例

如何定位并优化慢查询SQL

1.根据慢日志定位慢查询sql

Show variables like ‘%quer%’;

Show status like ‘%slow_queries%’; 慢查询的条数，每次重启mysql会被清零

Set global slow_query_log = on; //打开日志

Set global long_query_time = 1; 设置超过一秒钟就会被记录进日志中（默认10秒）

2.使用Explain等工具分析sql

Explain关键字段

Type: 查询性能由大到小

System > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

Extra:

Extra出现以下2项意味着MYSQL根本不能使用索引，效率会受到重大影响，应尽可能对此进行优化

Extra项

说明

Using filesort

表示MYSQL会结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序，MySQL中无法利用索引完成的排序操作称为“文件排序”

Using temporary

表示MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by.

3.修改sql或者尽量让sql走索引

联合索引的最左匹配原则的成因

MySQL创建复合索引的原则是首先会对复合索引的最左边，及复合索引的第一个字段进行排序，在第一个字段排序的基础上，在对后面第二个字段进行排序，所以第一个字段绝对有序，而第二个字段无序，通常情况下利用第二个字段进行条件判断是用不到索引的。

最左匹配原则：非常重要的原则，mysql会一直向右匹配直到遇到范围查询（> 、<、between、like）就停止匹配，比如a=3 and b =4 and c>5 and d = 6, 如果建立（a, b, c, d)顺序的索引，d是用不到索引的，如果建立（a,b,d,c)的索引则都可以用到，a, b, d的顺序可以任意调整。
= 和 in可以乱序，比如a = 1 and b =2 and c =3 建立（a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

索引是建立得越多越好吗？

答：不是。

数据量小的表不需要建立索引，建立会增加额外的索引开销

数据变更需要维护索引，因此更多的索引意味着更多的维护成本

更多的索引意味着也需要更多的空间

锁模块：

MyISAM与InnoDB关于锁方面的区别是什么？

上共享读锁（select * from xxx where id = x lock in share mode;)

上排他锁（select * from xxx where id = x for update;)

MyISAM默认用的是表级锁，不支持行级锁（读锁（共享锁），写锁（排他锁)，读锁不释放不能写操作，lock tables person xxxx read | write; unlock tables;)

InnoDB默认用的是行级锁，也支持表级锁。（二段锁，自动提交 set autocommit = 0; 关闭自动提交）

在没有用到索引查询时用的是表级锁，用到索引，则用行级锁。

共享锁和排斥锁的兼容性：

Session1 /Session2	X	S
X	冲突	冲突
S	冲突	兼容

MyISAM适合的场景：

频繁执行全表count语句

对数据进行增删改的频率不高，查询非常频繁

没有事务

InnoDB适合的场景：

数据增删改查都相当频繁

可靠性要求比较高，要求支持事务

数据库锁的分类：

按锁的粒度划分，可分为表级锁，行级锁，页级锁

按锁级别划分，可分为共享锁，排它锁

按加锁方式划分，可分为自动锁，显示锁

按操作划分，可分为DML锁(对数据进行操作），DDL锁（对表结构进行操作）

按使用方式划分，可分为乐观锁，悲观锁

数据库事务的四大特性：

ACID

原子性（Atomic): 事务是最小的执行的单位，不允许分割，事务的原子性确保动作要么全部完成，要么完全不起作用。

一致性（Consistency): 执行事务前后，数据保持一致，多个事务对同一个数据的读取的结果是相同的，确保完整性约束（例：转账前两人的钱是2000，转账后也是2000）

隔离性（Isolation):并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的，

持久性（Durability): 一个事务被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务隔离级别以及各级别下的并发访问问题

事务并发访问引起的问题以及如何避免

更新丢失—— mysql 所有事务隔离级别在数据库层面上均可避免

脏读——READ-COMMITTED事务隔离级别以上可避免

不可重复读——REPEATABLE-READ事务隔离级别以上可避免

幻读——SERIALIZABLE事务隔离级别可避免

事务隔离级别	更新丢失	脏读	不可重复读	幻读
未提交读	避免	发生	发生	发生
已提交读	避免	避免	发生	发生
可重复读	避免	避免	避免	发生
串行化	避免	避免	避免	避免