数据库加强

磁盘读取原理

局部性原理:因为磁盘在查找数据的时候耗时过长(寻道,读取),通常需要提前将一部分的数据都一次性读取出来放入内存,什么需要什么时候读取

1索引

1.1.什么是索引？

索引是一种数据结构。索引中是包含一个表中列的值和它的物理地址的值,并且这些值存储在一个数据结构中
常见的索引存储的数据结构中，二叉树和哈希表
索引存储的数据中：其中只存在一个表中的一列或者多列，单索引就是一个列，复合索引就是多个列
索引创建的位置在my.ini的path路径下
正常的表创建时.frm文件
聚簇索引:就是指索引的叶子节点放着的是本身数据
非聚餐索引:就是指索引的叶子节点放着的主键索引(其他索引)

1.2.索引可以用来干什么？索引的作用

创建索引后，会增加查询效率,多数情况下会比全表扫描要快
例:正常的检索(select)会全局搜索一遍,最终将结果返回(无论这个数据在第一条还是中间都是全文检索最后返回结果,这个原因即使是搜索主键,也是同理),所有搜索的时间变长,扫描的结果变慢
如果索引底层的数据结构是二叉树的情况下,正常的情况下如果数据为总数为11,正常的检索会检索所有,然后返回,而二叉树结构,通过索引文件保存的物理地址值，只需要最多四次就可以找到

1.3.索引分类

1.聚簇索引:就是指索引的叶子节点放着的是本身数据
一个表中只有一个聚簇索引
默认是主键,如果没有定义主键,则选择一个唯一且非空的作为主键
(MyISAM没有聚簇索引)
2.非聚餐索引:就是指索引的叶子节点放着的主键索引(其他索引)
3.主键索引(聚簇索引):
主键索引的添加:
方法一: 在创建表的时候直接创建
create table table_name(id int unsiged primary key auto_increment),
name varchar(20) not null default’’;
方法二:先创建，后创建index
alter table table_name add primary key ( column_name );

注:主键为何和主键索引创建没有区别？
主键是字段是不能为空和唯一的约束,主键索引是一种特殊的索引
4.全文索引(不常用,ES solar软件使用)
5.普通索引(非聚餐索引)
普通索引针对不同的列
创建方式就是先创建表，后根据需要选择创建什么样的索引
create index index_name on table_name(conlumn_name);
6.唯一性检索(非聚餐索引)
7.空间索引

1.4.索引的查询

方法一: desc table_name 缺点:不能查到名字
方法二: show index(es) from table_name\G

1.5.索引的注意事项,使用场景

当一个语句被频繁用作where条件的查询时,最好创建索引
该字段的列的值很丰富的时候,不是唯一几个值时可创建索引比如唯一性太差不适合
更新频率不是十分频繁的时候(如果这个列的数据的变化十分的繁琐，那么频繁更新索引会增加消耗)
使用索引有时候是需要代价的:
占用磁盘代价,对dml(增删改)不友好(有影响),所以索引是针对与select,对dml也有副作用
对于复合索引来说,知识查询条件使用最左面的列,索引才会有作用(复合索引指多列)
create index dept add index my_ind(dname,loc);
dname为左面的列,loc为右面的列
对于模糊查询 %aaa不会使用索引 aaa%会使用索引

1.6.索引内部的二叉树实现以及原理

二叉树具有以下性质:左子树的键值小于根的键值,右子数的键值大于根的键值

二叉树本身可以随意构造,但是下列图的查询性能不如上图,所以出现了新的概念:平衡二叉树(AVT Tree)

平航二叉树在符合二叉查找数的条件下,还满足接单的两个字数高度最大差为1
在任何节点的两个子树的高度差<=1

如果在AVL数中进行插入或删除节点，可能会出现失衡

LL、RR、LR、RL
这四种失去平衡的姿态都有各自的定义:

1. LL:LeftLeft,也称“左左”。插入或删除一个节点后，根节点的左孩子的左孩子，还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡
2. RR:RightRight，也称“右右”。插入或删除一个节点后，根节点的右孩子的右孩子，还有非空节点，导致根节点的右子数高度比左字数高2，AVK数失去平衡
3. LR:LeftRight，也称“左右”。插入或删除一个节点后，根节点的左孩子的右孩子还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡
4. RL：RightLeft，也称“右左”。插入或删除一个节点后，根节点的右孩子的左孩子还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡

失衡状态——>平衡状态
AVL失去平衡之后,可以通过旋转使其恢复平衡
LL旋转,

RR旋转,

LR旋转

RL旋转

1.7.平衡多路查找树(B-Tree)

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。
系统从磁盘读取数据到内存时以磁盘块为单位的，位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么
InnoDB存储引擎有页(Page)的概念,页是其磁盘管理的最小单位
B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块
举例:描述数据如何使用B-Tree，首先定义一条记录为一个二元组[key,data]，key为记录的键值,对应表中的主键值,data记录为一行记录除主键外的数据。对于不同的记录，key值互不相同。
特性:
1.在每一个磁盘块中共有大致3个部分:1.键值 2.数值 3.指针(指向的子节点)
2.每个阶段最多有m个孩子
3.除了根节点和叶子节点外,其他每个节点至少要有ceil(m/2)个孩子
4.如果根节点不是叶子节点则至少要有两个孩子
5.所有叶子节点都在同一层，且不包括其他关键字信息
6.每个非终端节点包括n个关键字信息
7.关键字的个数n满足: (m/2)-1<=n<=m-1
8.ki为关键字, 且关键字升序排序
9.pi为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki,但是都大于k-1

每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是自己点所在的磁盘块的地址。关键词划分成三个范围与对应三个指针指向子树的数据的范围域
查找数据过程
通过关键字从根节点进行比较I/O操作第一次
通过相应指针到子节点,再通过比较I/O操作第二次
通过相应指针找到叶子节点，找到对应的数据I/O第三次
通过有序表结构，利用二分查找法提升了查找的效率,减少了I/O操作的次数

1.8.B+Tree

B+Tree是在B-Tree基础上的一种优化,使其更适合实现外存储索引结构InnoDB存储引擎就是用B+Tree实现其索引结构
B+Tree上的优化:
B-Tree所构成的索引结构有一种缺点,那就是在根节点和叶子节点都存储了k对应的data值,这回导致当data数据过大时,因为磁盘块的空间有限就会增加深度,降低质量，影响查询效率
B+Tree中取消了根节点和叶子节点存储的对应的data数据,而是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上k对应存储的data数据,这样大大加大了每个节点存储key值得数量，降低了高度
指针中添加了对应的地址值信息,对应着key主键值(链指针)
所有的记录放在了叶子节点中
举例:
B+Tree上通常有两个指针 一个纸箱根节点,一个指向关键字最小的节点
所有叶子节点是一种链式环结构
因此出现了两种查询运算:
1.对应主键的范围和分页查找
2.从根节点开始，进行随机查找
数据库中B+Tree的高度一般在2~4层 在查找的时候也就需要1-3次查找,提升了效率

1.9.聚集索引&辅助索引

数据库中的B+Tree索引可以分为聚集索引和辅助索引
聚集索引的B+Tree的叶子节点存放的是整张表的行记录数据
辅助索引与聚集索引的区别在于
辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键
当通过辅助索引来查询数据时,InnoDB存储引擎会遍历辅助索引找到主键,然后通过主键在聚集索引中找到完整的行记录数据

1.10.复合索引

添加索引时,添加多个字段即为符合索引
优点:开销小,索引覆盖,效率高
缺点:删除,修改难度高,占据磁盘空间高
重点:最左前缀匹配原则,mysql会一直向右匹配直到遇见范围匹配则会索引失效
mysql的优化:会将索引条件的范围条件排在最后
例如: A=1 B = 2 C<4 D=5
优化:ABDC
复合索引优化:把范围索引条件放在后面
索引不会存储Null值

1.11.Hash索引

优点:快!
缺点:无序
InnoDB MyISAM都不支持

1.12.索引失效条件

在搜索null值时,索引会失效,因为索引不存储null值
参与列计算,索引失效
有or索引必失效,如果想生效需要把索引字段都设置为索引
以like查询%开头索引失效
复合索引不满足最左原则就不能使用索引
如果mysql估计使用全盘比索引快,则不使用索引

1.13.索引失效原因

复合索引在排序的规则是左边的数值排序,b在左边数值相等的情况下进行排序,而b本身是不进行排序的
1.不遵循最左原则,b的排序条件是基于a相等的情况下排序的,那么在a被干掉的话,b是无序的,只能进行全盘扫描
2.范围查找在前的情况下,因为b+tree没办法使用二分查找法来查询数据,因为b是无序的,所以失效
3.以like “%”开头失效,同2
4.字符也是按照顺序排序的a-z

1.14.explain关键字

模拟mysql优化器执行SQL语句,可以很好的分析执行过程

2.存储引擎—-InnoDB

2.1.InnoDB是事务性数据库的首选引擎

InnoDB支持事务安全表,有MVCC实现
支持外键,有聚簇索引,锁为行锁
事务的ACID属性:即原子性，隔离性，一致性，持久性
原子性:即事务中所有的操作都是不可分割的部分,要么全部执行,要么全部不执行
一致性:事务开始前和事务开始后,数据库的完整性没有被破坏
隔离性:同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰
持久性:事务完成后,事务对数据库的所有更新都将保存数据库中
针对于隔离性考虑不周会出现如下情况
脏读:即读取了一个未提交的事务
不可重复读:读取了前一个事务提交的数据,查询的都是同一个数据项
幻读:读取了前一个事务提交数据,针对一批数据整体

2.2.隔离级别

InnoDB是mysql默认的存储引擎,默认的隔离级别是RR,并且在RR的隔离级别下更进一步,通过多版本并发控制解决不可重复读问题,加上间隙锁(也就是并发控制)解决幻读问题。因此InnoDB的RR级别其实实现了串行化级别的效果,而保留了比较好的并发性能
Serializable(希瑞来死 播哦)串行化,安全性最高,性能最低
Repeatable read(瑞屁特 播哦)可重复读,避免脏读、不可重复读的发生 MySQL默认
Read committed(氪没 特哎的)读已提交 可免脏读的发生 Oracle默认
Read uncommitted(昂氪没 特哎的)最低级别，任何情况都无法保证

2.3.锁机制

锁的作用是保持数据的一致性
InnoDB支持行级锁。行级锁可以最大程度的支持并发,行级锁是由存储引擎层实现的。
锁:锁的主要作用是管理共性资源的并发访问,用于实现事务的隔离性(这一点跟java同步锁相似)。
Mysql锁的力度:表级锁(开销小、并发性低)。通常在服务器层实现
行级锁(开销大、并发性高)。只会在存储引擎进行实现

2.3.1.InnoDB行锁的种类

1.记录锁
行级锁
分为2种情况:
有索引的情况下力度为行级锁
没有索引的情况下力度为表级锁
2.间隙锁
在RR级别下,为了避免幻读,引入间隙锁,他锁定的是记录范围,不记录本身,也就是不允许规定的范围内插入数据,在接近检索条件记录值作为左监区为a,向上寻找最靠近的检索条件为b (a,b)为间隙
唯一索引在等值判断只会产生行级锁(记录锁)
3.临建锁: 记录+间隙锁

2.3.2.InnoDB表锁的种类

针对于大部分的数据进行修改,行锁就并不好用
InnoDB需要关闭自动事务的自动提交,set autocommit = 0;否则不会上锁
当开启表锁时,则不会让其他的session进行任何操作

2.4.锁的分类

1.读锁: where 条件 lock in share mode; 能读不能写

写锁(排他锁,独占锁,行锁): for update 开启事务时其他session不能修改(不能读和写,mysql运用MVCC则可以)

MDL锁(元数据锁):开启查询事务时(meta data lock)自动开启,在操作的时候不可以修改表结构

意向锁:不允许操作表结构

2.悲观锁:以上正常的种类锁为悲观锁

乐观锁:通过代码实现,不加锁实现数据一致,(通过每次对数据库的任何操作来进行一个版本字段的添加来实现数据一致)

死锁的产生和解决办法

死锁出现在2个事务以上
产生原因:A需要改B,B需要改A,互不放手造成死锁(Mysql会识别死锁并对所以事务进行回滚)
1.顺序访问
2.采用小事务,避免大事务
3.尽量做到锁定所需要的所有资源
4.业务容易出现死锁概率过大的话,提升锁力度

MVCC拓展

多版本并发控制系统,用于读写冲突,实现原理注意是以来记录中的3个隐式字段,undolog,read View来实现
隐式字段
1.最后一次修改记录的事务ID
2.数据库的回滚指针指向记录的上一个版本
3.隐含的自增id
undolog存储旧数据用于回滚使用
read view读视图 在事务中查询select时立刻回产生一个读视图(快照读)
(当前读,快照读)

Redo log

说明:1.针对于以外界因素使数据丢失的优化方案日志
2.Redo log属于物理日志
3.针对于事务回滚的方案
4.日志是顺序写,所以对于直接操作数据库,要执行更快
checkpoint其中的一种功能就是将脏页刷到磁盘,从而当DB重新启动时,只需要恢复到checkpoint之后的数据,这样就能大大缩短恢复时间
解析:Redo log分为两部分,分别重做日志缓冲和重做日志文件.写入磁盘策略可以通过一个专门的属性进行配置(innodb_flush_log_at_trx_commit)
1.innodb_flush_log_at_trx_commit=1:代表每次事务提交都必须刷入刷入重做日志文件 性能最差,安全最高
2.innodb_flush_log_at_trx_commi=0:代表事务提交不写入磁盘而是交给重做日志缓冲,在缓冲中发现数据满了调用刷入重做日志文件的方法 性能较好
3.innodb_flush_log_at_trx_commit=2:代表每次事务提交只提交到重做日志缓冲 性能最佳 丢失数据大

2.5.InnoDB存储引擎的的其他总结

InnoDB是为了处理巨大数据量的最大性能设计。他的CPU效率可能是任何基于磁盘关系数据库所不能匹敌的
InnoDB存储引擎完全与MySQL服务器整合,和索引在一个逻辑表空间中
InnoDB支持外键完整性约束,存储表中数据时,每张表的存储都按照主键顺序存放，如果没有在表定义时指定主键。InnoDB会为每一个生成一个6字节的ROWID并作为主键
InnoDB被用在需要高性能的大型数据库站点上

3.MyISAM存储引擎

MyISAM和InnoDB区别为不是事务安全的,并且不支持外键,不完全回滚,不具有原子性,
应用场景为执行大量select的时候
MyISAM索引结构也是B+Tree组成,MyISAM存储引擎的为非聚集结构索引,辅助索引通过辅助键直接找到数据地址,不用再访问主键
InnoDB必须有主键值(没有指定的情况下,自动创建一个长整型的一个主键),MySAM可以没有
MyISAM索引问键和数据文件是分离的,索引文件仅保存数据记录的地址,而在InnoDB中,表数据文件本身就是B+Tree组织的一个索引结构,这颗树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引
数据容易损坏,难恢复
count(*) 统计很快

4.数据库视图

MySQL视图(View)是一种虚拟存在的表,同真是表一样，视图也由列和行构成，单视频并不实际存在于数据库,行和列的数据来自于定义视图查询中所用的表,并且还是在使用视图时动态产生的。
视图的数据是依赖真是表中的数据的,如果数据库中的表数据有所改变,视图中的数据也会进行改变
视图也可以理解为筛选,屏蔽对用户没用或没用权限的信息，即保证了简单化又提升了安全性

4.1.视图优点

1.定制用户数据，聚焦特定的数据:
在实际的应用过程中国,每个用户可能对不同的数据有不同的要求
2.简化数据操作:
在用来查询时,很多时候要聚合函数,同时还要显示其他字段信息,可能还需要关联到其他表,语句可能会很长,这个动作频繁操作可以创建视图简化操作
3.提高数据安全性:
视图时虚拟的,物理是不存在的,可以只授予用户视图的权限,而不具体制定使用表的权限，来保护基础数据的安全
4.共享所需数据
通过使用视图,每个用户不必都定义和存储字节所需的数据,可以共享数据库中的数据,同样的数据只需要存储一次
5.更改数据格式
通过使用视图,可以重写格式化检索出的数据,并组织输出到其他应用程序中。
6.重用SQL语句
视图提供的是对查询操作的封装,本身不包含数据,所呈现的数据是根据视图定义基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后,编写完所需的查询,可以方便的重用该视图

4.2.视图注意事项

1.创建视图需要有足够的访问权限
2.创建视图的数目没有限制
3.视图可以嵌套,即从其他视图中检索数据的查询来创建视图
4.视图不能索引,也不能有关联的触发器、默认值或规则(因为本身不是表)
5.视图可以和表一起使用
6.视图不包含数据,每次使用视图时,都必须执行查询中所需的任何一个检索操作,如果用多个连接和过滤条件创建了复杂的视图或嵌套了视图,可能会让系统运行性能下降十分严重。因此部署大量视图应用时,应该进行系统测试

5.SQL优化

1.SQL语句中in包含的值不应该过多(数据较多时,消耗较大)
2.SELECT语句务必指明字段名称(避免增加不必要的消耗品)
3.只需要一条数据的时候,使用limit 1(为了是EXPLAIN达到const类型)
4.如果限制条件中其他字段没有索引,尽量少用or(or会造成查询不走索引的情况)
5.如果排序字段没有用到索引，就少尽量排序
6.尽量用union all代替union(untion将结果集合并后在进行唯一性过滤,增加消耗,union all使用前提是两个结果集没有重复数据)
7.不使用order by rand()
8.区分in和exists，not in和not exists(exists是外层为驱动表,先被访问,如果是IN，那么先执行子查询)
9.使用合理的分页方式以提高分页的效率
10.分段查询(在选择时间范围过大,会造成查询缓慢，主要原因是扫描行数过多,这时进行分段查询，循环遍历，结果合并返回)
11.避免在where字句中对应字段进行null值判断(对于null判断会导致引擎放弃使用索引而进行全表扫描)
12.不建议使用%前缀模糊查询(这种查询会导致索引失效而进行全盘扫描，可使用全文索引)
13.避免在where子句中对字段进行表达式操作(where age*2=36 替换 age = 36/2)
14.避免隐式类型转换(where子句中出现column字段的类型和出阿奴的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型)
15.对于联合索引来说,要遵守最左前缀法则(常用的搜索类型优先放在左边)
16.必要时可以使用force index来强制查询走某个索引(有的时候MySQL优化器采取它任务何时的索引来检索sql语句,但是可能它所采用的索引并不是我们想要的，这时就可以采取forceindex来强制优化器使用我们制定的索引)
17.注意范围查询语句(对于联合索引来说,如果存在范围查询比如between,<>等条件时,会造成后面的索引字段失效)
18.关于JOIN优化
…….