2. 事务场景学工部整个部门解散了该部门及部门下的员工都需要删除了。操作-- 删除学工部 delete from dept where id 1; -- 删除成功 -- 删除学工部的员工 delete from emp where dept_id 1; -- 删除失败操作过程中出现错误造成删除没有成功问题如果删除部门成功了而删除该部门的员工时失败了此时就造成了数据的不一致。要解决上述的问题就需要通过数据库中的事务来解决。2.1 介绍在实际的业务开发中有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败就进行事务的回滚所有的SQL语句全部执行失败。简而言之事务是一组操作的集合它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。事务作用保证在一个事务中多次操作数据库表中数据时要么全都成功,要么全都失败。2.2 操作MYSQL中有两种方式进行事务的操作自动提交事务即执行一条sql语句提交一次事务。默认MySQL的事务是自动提交手动提交事务先开启再提交事务操作有关的SQL语句SQL语句描述start transaction; / begin ;开启手动控制事务commit;提交事务rollback;回滚事务手动提交事务使用步骤第1种情况开启事务 执行SQL语句 成功 提交事务第2种情况开启事务 执行SQL语句 失败 回滚事务使用事务控制删除部门和删除该部门下的员工的操作-- 开启事务 start transaction ; -- 删除学工部 delete from tb_dept where id 1; -- 删除学工部的员工 delete from tb_emp where dept_id 1;上述的这组SQL语句如果如果执行成功则提交事务-- 提交事务 (成功时执行) commit ; 上述的这组SQL语句如果如果执行失败则回滚事务 -- 回滚事务 (出错时执行) rollback ;2.3 四大特性面试题事务有哪些特性原子性Atomicity事务是不可分割的最小单元要么全部成功要么全部失败。一致性Consistency事务完成时必须使所有的数据都保持一致状态。隔离性Isolation数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。持久性Durability事务一旦提交或回滚它对数据库中的数据的改变就是永久的。事务的四大特性简称为ACID原子性Atomicity原子性是指事务包装的一组sql是一个不可分割的工作单元事务中的操作要么全部成功要么全部失败。一致性Consistency一个事务完成之后数据都必须处于一致性状态。如果事务成功的完成那么数据库的所有变化将生效。如果事务执行出现错误那么数据库的所有变化将会被回滚(撤销)返回到原始状态。隔离性Isolation多个用户并发的访问数据库时一个用户的事务不能被其他用户的事务干扰多个并发的事务之间要相互隔离。一个事务的成功或者失败对于其他的事务是没有影响。持久性Durability一个事务一旦被提交或回滚它对数据库的改变将是永久性的哪怕数据库发生异常重启之后数据亦然存在。3. 索引3.1 介绍索引(index)是帮助数据库高效获取数据的数据结构 。简单来讲就是使用索引可以提高查询的效率。测试没有使用索引的查询添加索引后查询-- 添加索引 create index idx_sku_sn on tb_sku (sn); #在添加索引时也需要消耗时间 -- 查询数据使用了索引 select * from tb_sku where sn 100000003145008;优点提高数据查询的效率降低数据库的IO成本。通过索引列对数据进行排序降低数据排序的成本降低CPU消耗。缺点索引会占用存储空间。索引大大提高了查询效率同时却也降低了insert、update、delete的效率。3.2 结构MySQL数据库支持的索引结构有很多如Hash索引、BTree索引、Full-Text索引等。我们平常所说的索引如果没有特别指明都是指默认的 BTree 结构组织的索引。在没有了解BTree结构前我们先回顾下之前所学习的树结构二叉查找树左边的子节点比父节点小右边的子节点比父节点大当我们向二叉查找树保存数据时是按照从大到小(或从小到大)的顺序保存的此时就会形成一个单向链表搜索性能会打折扣。可以选择平衡二叉树或者是红黑树来解决上述问题。红黑树也是一棵平衡的二叉树但是在Mysql数据库中并没有使用二叉搜索数或二叉平衡数或红黑树来作为索引的结构。思考采用二叉搜索树或者是红黑树来作为索引的结构有什么问题答案说明如果数据结构是红黑树那么查询1000万条数据根据计算树的高度大概是23左右这样确实比之前的方式快了很多但是如果高并发访问那么一个用户有可能需要23次磁盘IO那么100万用户那么会造成效率极其低下。所以为了减少红黑树的高度那么就得增加树的宽度就是不再像红黑树一样每个节点只能保存一个数据可以引入另外一种数据结构一个节点可以保存多个数据这样宽度就会增加从而降低树的高度。这种数据结构例如BTree就满足。下面我们来看看BTree(多路平衡搜索树)结构中如何避免这个问题BTree结构每一个节点可以存储多个key有n个key就有n个指针节点分为叶子节点、非叶子节点叶子节点就是最后一层子节点所有的数据都存储在叶子节点上非叶子节点不是树结构最下面的节点用于索引数据存储的的是key指针为了提高范围查询效率叶子节点形成了一个双向链表便于数据的排序及区间范围查询拓展非叶子节点都是由key指针域组成的一个key占8字节一个指针占6字节而一个节点总共容量是16KB那么可以计算出一个节点可以存储的元素个数16*1024字节 / (86)1170个元素。查看mysql索引节点大小show global status like innodb_page_size; -- 节点大小16384当根节点中可以存储1170个元素那么根据每个元素的地址值又会找到下面的子节点每个子节点也会存储1170个元素那么第二层即第二次IO的时候就会找到数据大概是1170*1170135W。也就是说BTree数据结构中只需要经历两次磁盘IO就可以找到135W条数据。对于第二层每个元素有指针那么会找到第三层第三层由key数据组成假设key数据总大小是1KB而每个节点一共能存储16KB所以一个第三层一个节点大概可以存储16个元素(即16条记录)。那么结合第二层每个元素通过指针域找到第三层的节点第二层一共是135W个元素那么第三层总元素大小就是135W*16结果就是2000W的元素个数。结合上述分析BTree有如下优点千万条数据BTree可以控制在小于等于3的高度所有的数据都存储在叶子节点上并且底层已经实现了按照索引进行排序还可以支持范围查询叶子节点是一个双向链表支持从小到大或者从大到小查找3.3 语法创建索引create [ unique ] index 索引名 on 表名 (字段名,... ) ;案例为tb_emp表的name字段建立一个索引create index idx_emp_name on tb_emp(name);在创建表时如果添加了主键和唯一约束就会默认创建主键索引、唯一约束查看索引show index from 表名;案例查询 tb_emp 表的索引信息show index from tb_emp;删除索引drop index 索引名 on 表名;案例删除 tb_emp 表中name字段的索引drop index idx_emp_name on tb_emp;注意事项主键字段在建表时会自动创建主键索引添加唯一约束时数据库实际上会添加唯一索引