数据库

JAVA 同时被 2 个专栏收录
37 篇文章 0 订阅
23 篇文章 0 订阅

1.1数据库设计:

1.存储(文件系统) : 将数据最终磁盘化存入硬盘中,如机械硬盘,或固态硬盘

2.程序实例(用逻辑结构映射出物理结构来,并且在程序中,提供、获取、以及管理数据的方式,还有必要的问题追踪机制):频繁的IO,会使数据库的执行效率慢如蜗牛,因为一次IO读取多条数据,和单条数据花费的时间并没有多大的区别,我们可  以一次性的区读取多行,提升IO的效能,所以行就失去了它的意义。实际,数据库也是把存储单位,用块或者叶来表示,每个块,或者叶中,会存放多行数据。读取的时候,可以将多个块一起加载进内存当中

3.存储管理模块

4.缓存机制:缓存机制不宜过大,且算法里需要有淘汰机制,淘汰掉一些之后不常用的数据

5.SQL解析模块

6.日志管理:此外咱们做的SQL操作需要记录下来,方便做数据库的主从同步

7.权限划分

8.容灾机制

9.索引管理

10.锁管理

1.2索引模块常见问题:

1.为什么使用索引?

因为全表扫描,在数据量很大的时候效率很低,所以需要引入索引机制,它的灵感来自于字典,只要我们把一些关键信息组织起来,查询的时候依据这些信息就能查到我们需要的页面。通过索引大幅提升查询速度。

2.什么样的信息能成为索引
        主键、唯一键以及普通键等

3.索引的数据结构

平衡二叉树、红黑树、以及B-Tree、B+-Tree、哈希Hash结构。

通常索引的数据结构是B+树,比较小众的还有Hash结构还有BitMap等。其中Mysql数据库不支持BitMap索引,同时基于innoDB和MyISAM引擎的Mysql不显式支持Hash。

B+树更适合来用作存储索引,B+树的查询效率更加稳定,B+树更有利于对数据库的扫描( B+树在做范围查询有着更高的性能。

Hash索引也可以考虑一下,就是根据Hash函数的运算,只需要一次定位,便可以找到查询数据所在的头,不像B+索引,需要从根节点到非叶子节点,最后才能访问到我们需要的数据,可能会经过多次的IO访问,Hash索引的效率理论上要高于B+索引。

Hash索引的缺点:

1.仅仅能满足"=","in"不能使用范围查询(由于hash索引比较的是经过hash运算的Hash值,所以它只能用于等值的过滤,不能用于基于范围的查询,因为经过相应hash算法处理过后的Hash值的大小关系,并不能保证和Hash运算前的完全一样)。

2.无法被用来避免数据的排序操作(由于Hash索引中存储的是经过Hash运算的Hash值,而且Hash值的大小并不和经过Hash运算之前的Hash值一样,所以数据库无法利用索引的数据来避免任何排序运算)

3.不能利用部分索引键查询。(对于组合索引,Hash索引在计算Hash值的时候,是组合键,就是将组合索引键合并之后再一起进行运算的Hash的值,而不是单独计算hash值的,所以通过组合索引前面一个或者几个进行查询的时候,Hash索引也无法被利用,而B+树是支持利用组合索引中的部分索引的)

4.不能避免表扫描。(hash索引是将索引键通过Hash运算之后,将运算结果的Hash值和所对应的行指针信息,存放再一个bucket当中的,由于不同的索引键存在相同的Hash值,所以即使取出满足某个Hash键值的那些数据,也无法从Hash索引中完成查询,也是要通过访问Bucket中的实际数据,进行相应的比较,所以这是不能避免表扫描的原因。 )

5.遇到大量Hash值相等的情况后性能并不一定就会比B+Tree索引高。(对于选择性比较低的索引键,如果创建Hash索引,那么将存在大量记录指针信息存放同一个Bucket情况,从而造成整体性能非常低下,就跟我们之前用到的二叉树一样,有可能变成线性的存储结构,有可能在一个很极端的情况下,所有的键计算出来的hash值都是相同的,也就是都放在同一个bucket当中,那我们查询bucket里面最后一条数据的时候,就会变成线性的了,这也是Hash索引不能成为主流索引的原因,因为它比较不稳定,不支持一些范围的查询)

一般数据库除了用B+树,Hash作为其索引以外,还有没有别的数据结构呢?

答案:BitMap,即位图索引,当表中的某个字段只有几种值的时候,就比如说我们要表示性别的时候,只有男女两种情况,如果仅仅是为了在这个字段上实现高效的统计,此时用位图索引是一个最佳的选择,不过要注意的是,目前只有很少的数据库支持位图索引,已知比较主流的是Oracle数据库,位图索引的结构类似于B+树,就像这样子,B+树用来定位叶子节点,这些节点包含指定键值的位图,位图位于叶子节点上,它的段信息就在这里,该索引不是主流索引,只作简单讲解。由于数据的值种类固定了,只有blue、green、red、yellow这几种形态,在存储方式上会先按照状态值进行分开,每一种值的空间会存储每一个实际的数据行,是否是这个值,我们可以看到这里有0101这样的一个位图,第一行是blue,那就用1来表示,第二行不是blue那就用0来表示,它是red,那red的第二个,就是1,那么在这种情况下,由于只需要存储是与否,通常只需要一个Bit位来存放,理论上一个叶子块可以存放非常多的bit位来表示不同的行,因此用它来统计时,是非常快的 。下载到内存中后,几乎是存CPU的叠加操作,位图索引虽然好,但是它只适用于某个字段的值只有固定的几种情况,同时需要注意的是,位图索引有一个很大的缺陷,它的锁的力度非常的慢,尝试新增或者修改一条数据的时候,通常与它在同一个位图的数据都会被锁住,因为某行所在的位置有可能因为数据的添加或删除而发生改变,像这里我们可以看到它的01的顺序,和行的顺序是一一对应的,如果发生增删改查的情况,它的逻辑顺序可能发生变化,因此在发生改变的时候,必须得将其锁定,以防止取错数据。所以它并不适合高并发的联机事务处理系统,即咱们常见的OLPP系统,而适合并发较少,且统计运算较多的OLAP类系统。

4.密集索引和稀疏索引的区别

1. 密集索引文件中每个搜索码值都对应一个索引值

2. 稀疏索引文件只位索引码的某些值建立索引项。

左边是密集索引,右边是稀疏索引,密集索引这个定义是什么意思呢?

密集索引:可以理解为,叶子节点不仅仅是保留键值,还保存了同一行里位于其他列的信息,由于密集索引决定了表的物理排列顺序,一个表只能有一个物理排列顺序,一个表只能创建一个密集索引,

稀疏索引:叶子节点仅保留了键位信息,以及该行数据的地址,有的稀疏索引是仅保留了键位信息,及其主键,那么定位到叶子节点之后呢,任然需要地址或者主键信息定位到数据。

上面是一般数据库的情况,我们呢来对Mysql做具体分析:
            它主要由两种存储引擎,MyISAM,另一种是innoDB,还有其他种,但这两种是主流,前者,不管是主键索引,唯一键索引,或者普通索引,其索引均属于稀疏索引,InnoDB呢,它必须有,且仅有一个密集索引,那么这一个密集索引,它的选取规则是如何的呢,1. 如果一个主键被定义,该主键则作为密集索引, 2.如果没有主键被定义,那么该表的第一个唯一非空索引则作为密集索引,3.如果没有主键,也没有合适的唯一索引,InnoDB内会生成一个隐藏的主键,作为密集索引,这个隐藏的主键是一个六字节的列,该列的值会随着数据的插入而自增,也就是说我们的innoDB呢必须有一个主键,而该主键就必须作为唯一的密集索引而存在,那为什么它一定要有主键索引呢?,因为有一条需要注意的规定,即 :非主键索引, 就是我们的稀疏索引的叶子节点并不存储行数据的物理地址,而是存储的该行的主键值。所以非主键索引包含了两次查找,一次是查找次级索引自身,然后呢再查找主键。

innoDB使用的是密集索引:将主键组织到一棵B+树中,而行数据就存储在叶子节点上,因为innoDB的主键索引和对应的数据是保存在同一个文件当中的,所以检索的时候,在加载叶子节点的主键进入内存的同时,也加载了对应的数据,即   若使用Where ID=14 的条件查询主键,则按照B+树的检索算法呢,即可查找到对应的叶子节点,并获得对应的行数据,那么对稀疏索引进行条件筛选,那么要经历两个步骤,第一步,在稀疏索引的B+树中检索该键,比如说检索Ellison,那么就定位到主键信息了,获取到主键信息了,还需要进行第二步,第二步就是使用主键Where=14在B+树中再执行一遍我们的检索操作,最终到达叶子节点,获取整行的数据。

MyISAM使用的是稀疏索引:稀疏索引的两棵B+树看上去没什么不同,节点的结构完全一致,只是存储的内容不一样而已,主键B+树索引存储了主键,辅助键B+树索引存储了辅助键,表数据存储在独立的地方,跟它的数据是分开存储的,这两棵B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别,由于索引树是独立的,通过辅助键检索,无需访问主键的检索树,这下大家对Mysql的两种存储结构是比较熟悉了。

InnoDB的索引和数据是存在一块的,MyISAM它的索引和数据是分开的

衍生出来的问题,以Mysql为例:

1.如何定位并优化慢查询sql:

1.根据慢日志定位慢查询SQL(慢日志就是用来记录执行的比较慢的一些sql)

命令:show variables like '%quer%';
           slow_query_log:慢日志   OFF关闭(需要 将它打开)
           slow_query_log_file:慢日志的全地址
           long_query_time:10.00000 (表示每次执行sql花费十秒钟,就会记录进慢日志)
           命令:show status like '%slow_querie%';
           Slow_queries:即慢查询的数量
           命令:打开慢日志记录  set global slow_query_log = on;
           命令: 设置慢查询时间为1秒  set global long_query_time = 1; (需要重新连接数据库才能刷新)
                
           制造慢查询:
           先查看有多少条数据(非必须操作):select count(id) from person_info_large;
                    select name from person_info_large order by name desc; 执行完这条sql,慢日志会增加一条记录 ,打开慢日志地址log,里面会记录这条sql

实际项目运行当中,可以通过慢日志,去捕获一些慢sql,去分析这个sql为什么这么慢,然后再对它进行调优。

2.2.使用explain等工具分析sql


在分析性能的时候explain非常管用,这个关键字一般放在select name from person_info_large order by name desc; select前面,用于描述mysql如何执行查询操作,以及msql成功返回及结果需要查询的函数,expain可以帮我们分析select语句,让我们知道查询效率低下的原因,从而改进我们的查询,让查询优化器能够更好的工作。
                例:explain select name from person_info_large order by name desc;
其中有两列对于调优sql非常重要,

id:标明了sql的执行顺序,越大的越先执行,比如我们的复合查询,里面的语句是最先执行的,所以里面的语句是比较大的。

type:表示mysql找到数据行的方式,方式有很多,不需要去一一记忆,性能从最优到最差会按照system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquey>index_subquery>range>index>all;

index和all表明本次查询走的是全表扫描,因此我们看到语句是慢查询语句,并且type是这两个值中的一个,证明语句是需要优化的。
Extra:虽然extra没有type直观,但可以从它这里获取到更为详细的信息,辅助我们了解语句的执行方式,它的类型也非常多,咱们主要关注两种极有可能需要优化的方式。

extra项:Using filesort:表示Mysql会对结果使用一个外部索引排序,而不是从表里按索引依次序读到相关内容。可能在内存或者磁盘上进行排序,Mysql中无法利用索引完成的排序操作称为“文件排序”。
            Using temporary:表示Mysql在对查询结果排序时使用临时表,常见于排序order by和分组查询group by。

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


将select name from person_info_large order by name desc;改为由account索引的查询语句 select account from person_info_large order by account desc;
有时候业务就是要按照名字来排序,此时就需要加索引,name是没有索引的,就需要执行命令:

alter table person_info_large add index idx_name(name); 
explain select count(id) from person _info_large;            
type:index
key:account
type用的是预期的index,key呢居然用的是account,用的是唯一键,并不是用的主键,为什么用的是account而不用ID呢,iD才是它的主键啊,之所以去走这个索引,是由我们这个Mysql的查询优化器来做决定的,Mysql的查询优化器最终的目标是尽可能的使用索引,并使用最严格的索引,来消除尽可能多的数据行,最终目标是提交select语句查找数据行,而不是排除数据行,优化器排除数据行的原因在于它排除数据行的速度越快,那么找到于条件匹配的数据行就越快。因此,查询优化器会根据它的判断和分析来决定走哪个索引。它没选择主键索引,大致原因应该是由于密集索引的叶子节点,把其他列的数据也存放到了叶子节点当中。因为数据都放到了一起,所以查询速度比稀疏索引要低,因为毕竟稀疏索引只存储了关键字以及主键的值,这样我们在内存里就能加载更多的关键字和主键的值。所以查询优化器选择了它。
                我们可以强制语句走主键索引,对比一下两者的耗时:explain select count(id) from person _info_large force index(primary);

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

什么是最左匹配原则呢?
            select * from person_info_large where area = ' ' AND title = ''
                possible_keys:index_area_title
                key:index_area_title
如果将title删除了呢?
            select * from person_info_large where area = ' ' ;
                possible_keys:index_area_title
                key:index_area_title 依然走的是Area和title索引

如果将area删除了呢?
            select * from person_info_large where  title = '';
                possible_keys:NULL
                key:NULL
                type:ALL    走的是全表扫描,这是最差的性能
1.最左前缀匹配原则:非常重要的原则,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的顺序可以任意调整。


2. = 和 in 可以乱序:比如 a =1 and b=2 and c= 3建立(a、b、c)索引可以任意顺序,mysql查询优化器会帮你优化成索引可以实别的形式。

最左匹配原则的成因:mysql创建复合索引的规则是首先,会对复合索引的最左边,也就是第一个索引字段的数据进行排序,在第一个字段的排序基础上,再对后面第二个字段进行排序,其实就相当于实现了 order by 字段1   再order by 字段2 这样一种排序规则。所以第一个字段是绝对有序的,第二个字段就是无序的了,因此通常情况下直接使用第二个字段进行条件判断使用不到索引的,这就是mysql为什么要强调联合索引最左匹配原则的原因。

索引是建立的越多越好吗?
        数据量小的表不需要建立索引,建立会增加额外的索引开销。
        数据表更需要维护索引,因此,更多的索引需要更多的维护成本。
        更多的索引意味着需要更多的空间。(好比一本一百页的书却有五十页的目录)

锁模块常见问题:

1.MyISAM与InnoDB关于锁方面的区别是什么?
            MyISAM默认用的是表级锁,不支持行级锁。
            InnoDB默认用的是行级锁,也支持表级锁。
            例:多个session操作同一张表或者表里的同一个数据。
                两张表,person_info_large 基于innoDB引擎,person_info_MyISAM基于MyISAM引擎的。
                select * from person_info_myisam where id between 1 and 2000000;
                updata person_info_myisam set account where id =2000001;
                当表进行查询的时候MyIsam会自动给表上一个表锁,等待更新完成后,才会执行添加操作。
                MyISAM执行select的时候会自动会表加上一个读锁,
                执行增删改的时候会自动给表加上一个写锁。当读锁未被释放,另外一个session想给表加上一个写锁,表就会被堵塞。直到所有的读锁都被释放为止。
2.如何显式给表加上读锁?
                lock tables person_info_myisam read;加上读锁
                unlock tables ; 释放读锁

3.读锁有另外一个名字叫做共享锁,为什么叫做共享锁呢?
            session1给表加上读锁的同时,session2也给表加上一个读锁。
            session2模拟取出第两百万和两百万零一的数据:
                select * from person_info_myisam where id in (2000000,2000001)
                执行很快!再执行范围查询的时候,依然能对表里的数据执行读操作,所以读锁也叫共享锁。
            反过来,先上写锁,后上读锁:
                updata  person_info_myisam set account = account  where id between 1 and 200000; 
                写锁的别名排他锁。
                除了可以对增删改上排他锁,还可以对select上排他锁,
                select * from person_info_myisam where id  between 1 and 2000000 for updata;

总结:myisam引擎默认支持表级锁,它不支持行级锁,表级锁会锁住整张表,像这里咱们对第一条和第两百万条数据进行查询的时候,第两百万另一条数据进行更新,它同样会被锁住,如果是行级锁,他只会对1到两百万条数据锁住,第两百万零一条数据是不会被锁住的。

锁分两种:共享锁和排他锁,上了共享锁依然可以再上共享锁,但是不支持上排他锁,要是先上了排他锁,另外的读或者写都是不允许的。共享锁和排他锁这种情况,也同样支持行级锁的innoDB引擎mysql是默认自动提交事务的。innoDB用的是二段锁,二段锁是加锁和解锁是分为两个步骤来进行的。全程用排他锁锁定,就是悲观锁。

悲观锁:悲观锁并发控制实际上是先取锁,再访问的保守策略,为数据处理的安全提供了保证。但是在效率方面,处理加锁的机制,让数据库产生了额外的开销。增加产生死锁的机会,另外,在只读型事务处理中,由于不会产生冲突也没必要使用锁,上锁会增加系统负担,同时还会降低并行性,如果锁住了某行数据,其他数据就 必须等待该数据处理完。

乐观锁:乐观锁认为一般数据一般情况下不会造成冲突,所以在提交更新的时候,才会正式对数据的冲突与否进行检测,如果发现冲突了,则返回用户错误的信息,让用户决定如何去做,相对于悲观锁在对数据库进行处理的时候,乐观锁并不会使用数据库提供的锁机制,一般乐观锁的方式就是记录数据版本,实现数据版本有两种方式,一种是使用版本号,另外一种是使用时间戳。

4.数据库事务的四大特性。
            原子性:要么全部成功。要么全部失败。要么做,要么不做。
            一致性:数据库中的数据应满足完整性约束。用户的钱有两千,不管转账几次,转账的钱加上转账后的钱要是2000
            隔离性:多个事务并发执行,一个事务的执行不能影响其他事务的执行。
            持久性:一个事务一旦提交,它对数据库的修改应该永久保存在数据库中。持久性意味着当系统或介质发生故障时,确保已提交事务的更新不能丢失,即对已提交事务的更新能恢复。所以持久性主要在DBMS的恢复性能,就拿innoDB来讲,它会将所有对页面的修改写入一个专门的文件,当数据库启动时,从此文件进行恢复操作。

5.事务隔离级别以及各级别下的并发访问问题。
Mysql会利用锁机制创建出不同的事务隔离级别,咱们会按照事务隔离级别,从低到高的顺序讲解。首先我们来看看事务并发引起的问题:
1.更新丢失---mysql所有事务隔离级别在数据库层面上均可避免:

更新丢失,即一个事务的更新覆盖了另一个事务的更新。由于现在主流数据库都会为我们加锁来避免这种更新丢失的情况,所以我们在数据库层面上不好模拟。


2.脏读----READ_COMMITTED事务隔离级别以上可以避免:

一个事务读到另一个事务未提交的数据。而该问题可以在已提交读事务隔离级别上去避免

解决方法:

1.查看当前事务隔离级别:select @@tx_isolation;  REPEATABLE--READ

2.设置当前session的隔离级别 : set session transaction isolation level read uncommitted;  即:读未提交(此隔离级别容易引发脏读)

3.开启事务:start transaction:如何避免脏读:只需要将隔离级别往上调一个等级,READ--COMMITED 就行
set session transaction isolation level read committed ;


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

事务A多次读取同一数据, 事务B在事务A读取数据的同时对数据进行修改,导致事务A多次读取同一数据,数据不一致。

解决:将事务再调高一个级别: set session transaction isolation level repeatable read;
            
 4.幻读--SERIALIZABLE事务隔离级别可避免
                他的含义是事务A读取于搜索条件相匹配的若干行,事务B以插入或者删除行的方式来修改事务A的结果集,导致事务A看起来像出现幻觉一样。
                解决:将事务隔离级别调整至最高。
                set session transaction isolation level serializeble;

为什么不直接将事务级别设置为最高?

因为事务级别越高,安全性越高,串行化就越严重,这样就降低了数据库的并发度,因此可以依赖业务的需要设置隔离级别。

4.InnoDB可重复读隔离级别下如何避免幻读。
            表象:快照都(非阻塞读)---伪MVCC
            内在:next-key锁(行锁+gap锁)

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值