目录
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中使用函数,表达式,导致索引失效