目录
一、索引
-
索引定义
在数据之外,数据库还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用指向数据,这样就可以在这些数据结构上实现高效查找,这些数据结构就是索引。
-
索引的分类
单值索引:一个索引只包含单个列,一个表中可以有多个单值索引
唯一索引:索引列的值必须唯一,可为空
复合索引:一个索引包括多个列
-
索引优劣势
优势:提高数据检索效率,降低磁盘IO成本
劣势:1)降低了更新、修改、删除的效率,Mysql不仅要保存数据,还要维护数据和索引的关系
2)需要成本去维护索引
-
索引的数据结构
B树:B树的每个节点可以包含多个子节点,可以减少树的高度,减少磁盘IO次数,提高检索雄效率,适合范围查询,但效率没有B+树快。
B+树:B+树是对B树的优化,B+树的非叶子节点只存储索引信息,数据都存储在叶子节点上,叶子节点之间通过指针连接形成有序链表,适合范围查询和顺序遍历。
哈希索引:哈希索引使用哈希函数将索引键值映射到哈希表中,通过哈希表的查找来快速定位数据。哈希索引适合等值查询,但不适合范围查询。
问题:为什么Mysql选择B+树做索引?
1)B+树的磁盘读写代价更低
B+树的内部节点并没有指向关键字具体信息的指针,因此其内部节点相对于B树更小,如果把所有同一内部节点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多,一次性读入内存的需要查找的关键字也就越多,相对IO读写次数就降低了。
2)B+树的查询效率更加稳定
由于非叶子节点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根节点到叶子节点的路,所有关键字查询的路径长度相同,故每一个数据的查询效率稳定。
3)B+树更便于遍历
B+树的数据都存储在叶子节点中,非叶子节点均为索引,方便扫库,只需要扫一遍叶子节点即可。
4)B+树更适合基于范围的查询
B树在提高IO性能的同时并没有解决元素遍历效率低下的问题。而B+树只需要去遍历叶子节点就可以实现整棵树的遍历。
-
索引失效
1)在索引列上做任何操作(计算、函数、类型转换),会导致索引失效
2)字符串不加单引号,索引会失效
3)用or连接时会导致索引失效
4)like以通配符开头('%abc')会导致索引失效
5)MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描,因为B+树叶子节点用指针相连且是排好序的,这种数据结构只能解决有序的定值查询,像不等于这种无法利用索引查询。
二、存储过程
存储过程:是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的雄安率是很有好处的。
总的来说就是数据库SQL语言层面的代码封装与重用。
-
格式
-- 创建存储过程
delimiter 自定义结束符号 || $$
create procedure `数据库名`.`存储过程名`([in , out , inout] 参数名 数据类型。。。)
begin
sql语句
//select name from emp where id = ?;
end 自定义结束符号 || $$
delimiter ;
-- 调用存储过程
call 存储过程名;
-
参数
IN类型的参数表示接受调用者传入的数据;
OUT类型的参数表示向调用者返回数据;
INOUT类型的参数即可以接受调用者传入的参数,也可以向调用者返回数据。
三、触发器
-
定义
触发器是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作(insert,delete,update)时就会激活它执行。
-
作用
1)触发器经常用于加强数据的完整性约束和业务规则
2)可以在写入数据前,强制检验或者转换数据,来保证安全性
3)触发器发生错误时,前面用户已经执行成功的操作会被撤销,类似于事务的回滚
四、事务
-
概念
事务是一种机制、一个操作序列,包含了一组数据库操作命令,并且把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么都执行,要么都不执行。
事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态。如果单元中的所有SQL语句均执行成功,则事物被顺利执行。
-
事务的四大特性
(1)原子性
原子性是指事务是一个不可分割的工作单元,事务中的操作要么都发生,要么都不发生。事务中的任何一个数据库操作失败,已经执行的任何操作都必须被撤销,让数据库返回初始状态。
(2)一致性
一致性是指在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。
(3)隔离性
隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
(4)持久性
持久性是指在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。
-
事务之间的相互影响
(1)脏读:指的是读到了其他事务未提交的数据,未提交意味着这些数据可能会回滚,可能最终不会存到数据库中。
(2)不可重复读:一个事务在读取数据时,另一个事务修改了数据,导致前后两次读取的数据不一致。
(3)幻读:一个事务在读取数据时,另一个事务插入了新的数据,导致前后两次读取的数据量不一致。
-
事务的隔离机制(默认是可重复读)
(1)读未提交:这是事务最低的隔离级别,允许当前事务读取未被其他事务提交的变更
(2)读已提交:保证一个事务修改的数据提交后才能被另外一个事务读取,可以解决脏读。
(3)可重复读:确保事务可以多次从某行记录的一个字段中读取相同的值,在这个事务持续期间,禁止其他事务对这个字段进行更新。这种事务隔离级别可以防止脏读,不可重复读,但是可能出现幻读。
(4)可串行化:相当于锁表,完全串行化的读,每次读都需要获得表级享锁,读写相互都会阻塞,并且花费成本高。可以解决脏读、不可重复读和幻读。
五、锁
MySQL的InnoDB锁机制分为表级锁和行级锁。
(1)行级锁(锁定某行、某几行,有索引)
行级锁中有共享锁和排他锁。共享锁又称为读锁,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改;排他锁又称为写锁,不能与其他锁并存。
(2)表级锁(作用于表)
意向共享锁(IS):表示事务准备给数据行加入共享锁,也就是说一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX):表示事务准备给数据行加入排他锁,说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
优劣势:
表锁的优势:开销小,加锁快,无死锁
表锁的劣势:锁粒度大,并发处理能力低
行锁的优势:锁的粒度小,处理并发的能力强
行锁的劣势:开销大,加锁慢,会出现死锁