[大数据]mysql复习

目录

mysql架构

mysql引擎

索引

事务

锁

sql优化

mysql架构

mysql架构分为四层：

1.连接层：负责接收客户端的连接，授权，认证

2.服务层：接收sql负责调用函数，存储过程，触发器等，对sql的执行顺序，排序，优化，如果是查询操作，还可以从缓存中先查询数据，提高性能。

3.引擎层：引擎是真正具体执行者，mysql提供不同的执行引擎，不同的引擎特点不同，根据需要选择即可

4.物理文件存储层：在硬盘上存储系统文件，表数据，各种日志文件等。

mysql引擎

引擎就是实际存储数据的机制，不同的引擎，机制不同，（例如索引，锁等） 根据不同的需要，选择不同的引擎。

mysql中常用的存储引擎；innodb：默认的存储引擎，支持事务，支持行锁，支持外键，支持缓存，不存储表的总行数。

myisam：不支持事务，不支持行锁，只支持表锁，不支持外键，不支持缓存，存储表的总行数。

索引

索引可以理解为书的目录

主键默认是添加索引的，索引是帮助mysql高效获取数据的数据结构。B+

主键，维护在一个b+树中，保存这条记录的物理地址。

索引优点：提高了查询效率，（有b+树排序，把索引数据加载到内存中）减少io次数

索引使用了b+树结构，是有序的，排序时比较快捷方便，减少cpu消耗

索引缺点：索引也是需要空间来存储维护的

执行新增，修改，删除操作时，需要对索引结构进行更新。

添加索引的原则：那些情况适合建立索引：

主键：默认添加唯一索引，作为查询条件的列。

作为外键关联的列，排序的列，分组的列

那些情况不适合建立索引：表记录很少（例如系统参数设置表），不作为查询条件的列，增删改较为频繁的表，最好为需要的列来添加，数据重复较高的比如说性别。

索引分类：主键索引：primary key，主键列自动添加索引。

单值或者单列索引：一个索引对应一个列

唯一索引：索引列的值必须唯一

组合索引；一个索引中包含多个列

例如a,b,c 3列，a，b列创建了组合索引，在查询的时候，必须要满足最左侧索引原则，否则索引失效

全文索引：mysql8之后，innodb引擎开始支持全文索引，使用全文索引可以代替like，实现模糊查询，索引不会失效。

mysql索引的数据结构：

mysql使用的是b+树结构存储索引，b+树是有序的，每个节点可以存储多个数据（横向拓展）用宽度代替高度，非叶子节点不存储数据，只存储索引，一共节点中就可以存储多个索引，所有数据存储在叶子节点中，每个叶子节点之间有指针指向，有序节省排序时间，一个节点存储多个元素，树的高度降低了，叶子节点之间有指针，便于区间范围查询，例如a>10

聚簇索引和非聚簇索引：

聚簇索引：找到了索引就找到了数据，例如innodb索引和数据在同一个文件中，找到了索引就找到了数据，使用主键作为条件查询，使用其他的列查询，查询结果只有自己。

非聚簇索引：找到了索引，还需要回表查询，例如myisam引擎中索引和数据在不同的两个文件中，找到了索引，就知道了位置，需要去存数据的表中查询数据。使用其他列查询，查询结果除了本列，还存在其他列，这种情况需要通过该列先找到主键，再通过主键再次回表查询。

事务

事务就是一次数据库操作中的若干单元的管理，事务管理的目标是完整性。一次中的若干操作要么都成功，要么都失败。

事务管理的特征ACID

原子性atomic A 一致性 consistency C 隔离性Islation I 持久性 Durability D

原子性：就是要保持一次操作中的多条语句要么都执行成功，要么都执行失败。

隔离性；控制多个事务在并发读写时，保证互不影响，隔离级别有四种：读 未提交；读 已提交；可重复读；串行化；

持久性：持久性就是将数据持久化到硬盘上，不可回滚

一致性；保证数据操作的完整性

1.读 未提交：一个事务可以读到另一个事务还未提交的数据

问题：可能会出现脏读，另一个事务可能会回滚，这样读到的数据称为脏数据，

设置隔离级别：set global 。。。。

2.读 已提交；一个事务能读到另一个事务已经提交的数据

读已提交 解决了脏读问题

读已提交 会带来不可重复读问题，事务开始的时候读到一共数据，例如为10，在此期间另一个事务将数据修改为了20，也提交了，此时事务再次查询结果发生了变化，没有读到上一次的数据，这种情况称为不可重复读。

3.可重复读：一个事务开始时，读到了一个数据，在事务中继续执行再次读数据时，读到的数据与第一次是一致的。

解决了不可重复读的问题

为什么用隔离级别？解决了什么问题？

4.串行化：对表进行操作时，只能是一个事务执行，如果有一个事务在执行中，即时是读操作，其他事务也必须等待。

事务的实现原理：

1.原子性

执行了一个修改操作，后来又回滚了，那么数据需要直到执行前是什么样子的，执行增删改操作的时候，使用undolog记录一个相反的操作，insert《---》delete update b---》a 《---》update a--》b

这样在事务回滚时，可以借助日志信息进行还原。

2.隔离界别

3.持久化（了解）

mysql对数据操作时，并不是立即将数据写入磁盘，这样io多，效率低。

数据又不能一直存在缓存中，万一服务器宕机了，那么数据就不存在了。

mysql提供了redolog日志，可以将数据先暂时保存在日志中，记录那些数据发生了修改，定期将日志数据写到磁盘。

锁

可以按照粒度分为：行锁，间隙锁，表锁

行锁：一个事务在对某行数据进行操作（写操作），其他事务不能对此行进行操作，把这行锁住。

MySQL中innodb引擎支持行锁，myisam不支持

好处：并发性好，安全

不足：加锁次数多，效率会低

表锁：一个事务对某一行操作时，会将整个表锁定，其他事务就不能操作了。innodb表锁默认没开，myisam支持表锁，不支持行锁

并发性低，加锁频率少。

间隙锁：对于某个区间去给他加锁。在查询一个范围的时候，会对这个范围进行加锁。

例如 select name from user where age>10 and age<20

排他锁：真正意义上加锁，有事务进行写操作时，其他事务不能执行。

在执行新增，修改，删除的时候，自动加锁。

查询语句如果有需要的话，也可以加排他锁， select * from user where id=5 for update

共享锁：也称读锁，为查询语句添加共享锁后，其他事务也可以添加共享锁，但是其他事务就不能再添加排他锁了

sql优化

与数据库优化有区别，数据库优化，库的设计，分库分表等。

优化sql可以提高查询效率，

几种优化方案：

1.添加适当索引，添加索引的原则：

2.应尽量避免索引失效，组合索引下，不满足最左前缀原则

like 模糊查询

以null为条件的查询， where num is null

避免使用！=否则索引失效

使用or进行连接查询，导致索引失效

在where中使用函数，表达式，导致索引失效

3.状态字段，流程，尽量使用整数类型 0 通过，1不通过，0 男，1女

4.定长用char，变长用varchar

5.尽量少用select * 想要啥就查啥。

6.尽量避免一次性查询过多的数据

7.尽量避免值为null，可以赋默认值，null是会占空间的，在count（）统计的时候，不计数

执行计划：sql发送给服务器，在服务器内部如何执行，执行流程是怎么样的，限制性谁，有没有用到索引等等。

使用explain关键字，添加到查询语句前面，输出sql执行过程的参数

id sql执行顺序，例如有嵌套的子查询，

主查询id=1,子查询id=2，表名，子查询是先执行的。

select type表示查询的结构，simple简单查询，primary主查询，subquery子查询

type查询性能指标

system>const>eq_ref>ref>range>index>all

system表中只有一条记录，const通过索引一次性可以找到

ref使用了索引，例如查姓名，查询出来可能有多条数据，

range使用了索引，范围查询

index类型只遍历索引树

all全表查询，索引失效，查询了所有数据

innodb中的索引策略?，覆盖索引，最左前缀原则，索引下推

索引覆盖：是指在普通索引中可以得到查询的结果，不需要再回到主键索引树中再次搜索。覆盖索引是最常使用点数据查询技术，可以极大提供数据库性能，1.减少树的搜索次数，提升性能2.索引的数量远小于数据的数量，在索引树上的读取会极大减小数据库的访问量。
最左前缀原则：最左前缀原则是建立在联合索引之上的，不需要全部定义索引，只要用到索引最左边的那个字段就可以使用索引。维护索引需要代价，有时候我们可以利用最左前缀原则减少索引数量。
索引下推，是在索引遍历过程中，对索引中包含的字段先做判断，直接过滤调不满足条件的记录，较少回表次数。
sql语句在MySQL中如何执行

这里可以扯3个日志，都是干啥的，MySQL基本架构。
查询语句执行过程
1，首先察看该语句是否有权限，无权限则返回报错?
2.有权限则先去看缓存里面有没有数据，有直接走缓存，
3.没有再去?通过分析器进行词法分析，提取sql语句的关键字，select是查询，user是表名?。再去判断有没有语法错误，没有的话就会去查询，
4.比如说查询?18岁的张三，他回去查询张三是否是18岁，或者查询18岁的人有没有张三。根据自己的优化算法来选择。
5.然后进行权限校验，如果没问题就调用引擎接口，返回执行结果
更新语句的执行，有一点不一样，会引入日志来进行辅助操作。
例如说要改张三的年龄
1.首先拿到这一条数据，有缓存走缓存，
2.然后拿到查询的语句?吧年龄更改，然后再写入这个数据，
3.innodb吧数据存在内存中，同时记录redolog?此时redolog进入准备状态，告诉执行器?执行完成了，随时可以提交。
4.执行器接收通知后记录binlog，调用引擎，提交redolog为提交状态。
5.更新完成

sql优化的一般步骤?MySQL执行计划怎么看?字段含义

show?status命令了解各种sql的执行频率，
通过慢查询日志定位那些执行效率较低的sql语句。
MySQL提供explain/desc命令输出执行计划?，分析低效的sql的执行计划
?

b树和b+树的区别，MySQL为什么使用b+树，为什么不用二叉树

b树，节点排序，一个节点可以存多个元素，这一个节点内的多个节点也是排序的。
b+树，MySQL是给b树做了一个改进，他原本下面的节点都是没有相互指向的，MySQL给他连上了，就成了b+树了。
b+树有b树的功能，他的叶子之间结点有指针，除了最左边的一个数据，其他的数据都会出现两次。并且都是有序的。
为什么使用?
MySQL索引使用的是b+树，索引是用来加快查询速度的?，b+树里面的数据都是有序的，可以提高查询效率，通过一个节点可以存储多个元素，可以试使b+树的高度不会太高，在MySQL中一个innodb页就是一个b+树节点，一页默认16kb的话，2层的b+树可以存很多的数据，数据都在叶子结点上存储的，并且都是排序的，叶子结点之间都有指针，可以很好的支持全表扫描，范围查询等。
平衡二叉树每个节点只存储一个键值和数据，而b+树可以存多个数据?树的高度降下来了，查询速度就快了

外链接，左外连接，右外链接，内链接。等值连接
内链接是通过inner?join?来实现，返回两张表中满足连接条件的数据，不满足条件的数据不会查询出来?
外链接是分为左外连接和有外连接。
左外连接是left?join?返回左表中的所有记录和右表中满足连接条件的记录。
右外连接是right?join?他会返回右表中的所有记录和左表中满足连接条件的记录。
等值连接是使用=将表连接起来而查询，其查询结果中列出被连接表中的所有列，包括其中的重复列。

什么是引擎?innodb和myisam区别

引擎就是存储数据的机制，不同的引擎，机制不同。
innodb，默认的存储引擎，支持事务，支持行锁，支持外键，支持缓存，不存储表的总行数。
myisam不支持事务，不支持行锁，只支持表锁，不支持外键，不支持缓存，存储表的总行数。

数据库设计的三大范式
1.任何一张表都应该有自己的主键，并且每一个字段的原子性都是不可再分的。
2.在第一点的基础上，要求所有的非主键字段完全依赖主键，不能产生部分依赖。
3.在第二范式的基础上，所有非主键只能依赖于主键，不能产生循环依赖

redolog??undolog?binlog

binlog是二进制日志文件，记录了MySQL所有修改数据库的操作，然后以二进制的形式存记录在日志文件中，其中还包括每条语句执行的时间所消耗的资源等，以及相关的事物信息。
redolog重做日志用来实现事物的持久性，即acid中的d持久性，有两部分构成，一是内存中的重做日志缓冲，还有一个是重做日志文件，前者是易丢失的，后者是持久的。
undolog记录了事物的行为，
sql注入?如何解决

sql注入的原理是将sql代码伪装到输入参数中，传递到服务器解析并执行的一种攻击手法。
解决办法，1.严格的参数校验
2.sql预编译

MySQL锁有哪些

按锁的粒度分为行锁，锁住某行数据，粒度小，并发度高
表锁，锁住整个表，粒度大，并发度低
间隙锁，锁的是一个区间
还可以是共享锁，也就是读锁，加了读锁之后其它线程都可以读，不能写
排他锁，也就是写锁，其它线程不能读也不能写
还可以是乐观锁，不会真正的去上锁，而是通过版本号来实现
悲观锁，真正的锁，行所，表锁都是悲观锁

事物的基本特性和隔离级别

什么是事物
MySQL中的事物可以由一条sql语句构成，也可以由多条sql语句构成，在事务中的操作，要么都执行成功，要么都执行失败。
基本特性
事物要遵循acid四个特性
原子性?atomic?
一致性consistency
隔离性islation
持久性durability
原子性就是要保持一次操作中的多条语句，要么都执行成功，要么都执行失败。
隔离性，控制多个事务在并发读写时，保证互不影响，隔离级别有4种，读未提交，读已提交，可重复读，串行化
持久性，就是讲数据持久化到硬盘上，不可回滚。
一致性，保持数据操作的完整性。
怎么实现的

原子性的实现，执行了一个修改操作，后来又回滚了，那么数据需要执行前是什么样子，执行增删改操作的时候，使用undolog记录一个相反的操作，比如说insert?a?相反的就是delete?a??update?a?-b?相反的就是update?b-a，这样在事务回滚的时候，可以借助日志信息进行还原。
持久性，MySQL在对数据进行操作时，并不是讲数据立即写入磁盘，这样io操作多，效率低。数据又不能一直放在缓存中，万一服务器宕机了，数据就不存在了。MySQL提供了redolog日志，可以讲数据先暂时保存在日志中?记录那些数据发生了修改，定期将日志数据写到磁盘。
隔离性，隔离性的实现包括两个方面，第一个是写操作对写操作的影响，通过锁机制来保证隔离性，事物在修改数据前，需要先获得响应的锁，获得锁之后，事务便可以修改数据，改事物操作期间，这部分数据是锁定的，其他事物如果需要修改数据，需要等待当前事物提交或者回滚后释放锁。
按照锁的粒度可以分为，行锁，表锁，间隙锁。
行锁，锁住某行数据，粒度小，并发度高
表锁，锁住整个表，粒度大，并发度低
间隙锁，锁的是一个区间
myisam只支持表锁，innodb同时支持行锁和表锁。出于性能考虑，一般我们使用的都是行锁。
第二部分是读操作对写操作的影响，通过mvcc来实现的。
innodb默认的隔离级别是repeater?read?可重复读，可重复读可以解决脏读，不可重复读，幻读问题。mvcc全称是multi?version?concureency?control?多版本并发控制协议，最大的优点是读不加锁，因此读写不冲突，并发性能好，innodb实现mvcc主要是通过隐藏列，版本链和readview来实现。
隐藏列是在innodb中每行数据都有隐藏列，隐藏列包含本行数据的事务id，指向undolog指针等。
基于undolog的版本链，每行数据的隐藏列中包含指向undolog的指针，而每条undolog也会指向更早版本的undolog，从而形成一条版本链。
readview，通过隐藏列和版本链，MySQL可以讲数据恢复到指定版本，但是具体要恢复到哪个版本，则需要readview来确认。readview是指事物a在某一时刻给整个事务系统打快照，之后再进行读操作时，会将读取到的事物id与快照对比，从而判断数据对该readview是否可见，即对事物a是否可见。
一致性，只有前面3个属性都实现，才能保证一致性。在数据库本身也有比如说不允许向整形插入字符串，字符串长度不能超过限制等。

事务分类
事物类型
扁平事务
锁事物
嵌套事务
隔离级别
读?未提交read?uncommitted，使用record?lock算法实现了行锁，允许读取未提交的数据?存在脏读问题，
读?已提交?read?committed，允许读取提交的数据，存在不可重复读问题
可重复读?repeatable?read，使用next?key?lock算法实现了行锁，并不允许读取已提交的数据，解决了不可重复读问题，还包含间隙锁?，会锁定一个范围，因此也解决了幻读问题。
串行化?serializable，如果有两个事务同时进行，第一个事物已经开始了，第二个事物就会卡着等待第一个事物结束，第二个才能执行。第一个事物就算是读操作也会让第二个事物阻塞。
脏堵，不可重复读，幻读
脏读就是读到了没有提交的数据，如果人家回滚了的话，就会导致第一个事物读到的数据是无效的。
不可重复读就是，一次事物中两次查询出来的数据是不一样的。
幻读就是一个事物在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
当前读，读取到是最新版本的数据，并且对读取到记录加锁，阻塞其他事物同时改动相同记录，避免出现线程安全问题。
快照读，读取快照中的数据，不需要进行加锁

索引
索引是一个单独的，存储在磁盘上的数据库结构，包含着对数据库表里的所有记录的引用指针。可以理解为一本书的目录。
优点
通过创建唯一索引，可以保证表中数据的唯一性。
大大加快数据的查询速度，b+树排序，把索引数据加载到内存中，减少io次数。
在使用分组查询和排序子句进行数据查询时，可以显著减少查询中分组和排序而时间。
缺点
创建索引和维护索引需要消耗，并且随着数据量的增加消耗增大。
索引需要占据磁盘空间。
维护数据的时候，索引也需要动态维护，降低了维护速度
索引的数据结构
mysql使用的是b+树结构存储索引，b+树是有序的，每个节点可以存储多个数据（横向拓展）用宽度代替高度，非叶子节点不存储数据，只存储索引，一共节点中就可以存储多个索引，所有数据存储在叶子节点中，每个叶子节点之间有指针指向，有序节省排序时间，一个节点存储多个元素，树的高度降低了，叶子节点之间有指针，便于区间范围查询，例如a>10
分类
主键索引，主键自动添加索引，不允许为空
唯一索引，。唯一索引是值必须唯一
单列索引和组合索引，单列索引是一个索引只包含一个列，组合索引是指在表的多个字段上组合创建的索引，在查询条件时使用了这些字段的左边字段，索引才会被使用，需要遵守最左前缀原则。
全文索引，类型为fulltext，mysql8之后，innodb开始支持全文索引，使用全文索引可以代替like，实现模糊查询，索引不会失效。


聚簇索引，非聚簇索引
聚簇索引，找到了索引就找到了数据，例如在innodb引擎中索引和数据在同一个文件里存储，找到了索引就找到了数据，使用主键作为条件查询，使用其他的列查询，查询结果只有自己。
非聚簇索引，找到了索引，还要回表查询?，例如myisam引擎中索引和数据没有存在同一个文件，找到了索引，找到了数据的位置，还要去存数据的表中查找对应的数据。
使用其他列，查询结果除了本列，还存在其他列，这种情况需要通过该列先找到主键，再回表查询。
加索引原则
避免对经常更新到表进行更多的索引，并且索引中的列要尽可能少?，给经常用于查询的字段添加索引。
数据量小的表最好不要用索引，索引自身还有开销，数据量小的话，索引可能不会起到加快查询速度的效率。
不要给数据经常重复的数据添加索引，例如性别中的男女等。
当某种数据需要出现唯一一次的时候，添加唯一索引?。
主键默认添加索引。
最左前缀原则
在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
例如a,b,c?3列，a，b列创建了组合索引，在查询的时候，必须要满足最左侧索引原则，否则索引失效
索引失效情况
使用like做模糊查询导致索引失效
以null为条件的查询，?where?num?is?null
避免使用！=否则索引失效
使用or进行连接查询，导致索引失效
在where中使用函数，表达式，导致索引失效

?