事务
概述
操作
流程
begin 手动开启事务
start transaction 这两个指令都是开启事务
select * from xxx
update 表名 set 字段=xxx where=xxx
insert into 表名 （字段1，字段2）values（值1，值2）
delete from 表名 where=xxx
批量执行SQL语句，在commit之前都是临时修改，并没有实际修改到数据库
commit提交事务
一旦执行失败了
rollback回滚事务
事务的四大特性（ACID）
并发事务问题
脏读：一个事务读取到另一个事务没有提交的数据
不可重复读：一个事务先后读取同一条记录，但是两次读到的数据不同，这种就是不可重复读
幻读：一个事务按条件查询数据时，没有对应数据行，但是在插入数据时，又发现这行数据已经存在。好像出现了幻影。
针对以上问题，有事务的隔离级别来专门解决上述这些问题
事务隔离级别
读未提交：A事务读到了B事务还没提交上来的数据，也就是读到了不该读的数据
读已提交：两个事务各干各的不提交的话谁也读不到对方的数据
可重复读：在同一个事务中，执行两个相同的SQL得到的结果是一样的，读到的数据是一样的。提交之后再执行才会看到更新出来的数据
串行化：A与B两个事务并发，一个次只允许运行一个事务。A运行了B就得等待，B阻塞，等待A提交了之后才会继续运行。
一般来说，就用默认的隔离级别，不会更改
小结
存储引擎
MySQL体系结构
存储引擎简介
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可以被称为表类型。
那么，既然说存储引擎是基于表的，那么建表的时候，怎么没看见我指定哪个存储引擎呢？
建表语法：CREATE TABLE 表名 (字段名 字段类型);
这里可以看一下当时的建表语句show create table 表名
单独拿出来看看结果
CREATE TABLE `admin` (
`a_id` int(11) NOT NULL AUTO_INCREMENT,
`a_name` varchar(20) DEFAULT NULL,
`a_pass` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
可以看到，最后一句ENGINE=InnoDB，也就是默认的存储引擎是InnoDB
查询当前数据库支持的引擎
SHOW ENGINES
得到结果
那么我如何指定某张表使用指定存储引擎呢？
指定创建的表为MyISAM引擎
举例：
CREATE TABLE `admin` (
`a_id` int(11) NOT NULL AUTO_INCREMENT,
...各种字段信息
PRIMARY KEY (`a_id`) USING BTREE
) ENGINE=MyISAMULT
存储引擎特点以及区别
InoDB，MySQL 5.5之后，成为默认引擎
DML语句：通常指增删改语句
补充：
ACID特性：是指事务的四大特性
原子性（Atomicity）：化学中的原子指不可再分的基本微粒，数据库中原子性强调事务是一个不可分割的整体，事务开始后所有操作要么全部成功，要么全部失败，不可能停滞在中间某个环节。如果事务执行过程中出错就会回滚到事务开始前的状态，所有的操作就像没有发生一样不会对数据库有任何影响。
一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态，即一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还是5000，这就是事务的一致性。
隔离性（Isolation）：当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离，比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转入钱。
持久性（Durability）：一个事务一旦被提交，则对数据库的所有更新将被保存到数据库中，不能回滚。
MyISAM MySQL5.5之前的存储引擎
Memory 存储在内存中的表结构
总结以上三个表
面试题：InnoDB与MyISAM的区别
- InnoDB支持事务，但MyISAM不支持事务
- InnoDB是支持行锁的，MyISAM只支持表锁
- InnoDB是支持外键的，MyISAM不支持外键
不同业务下怎么选存储引擎？
总结
索引
索引是帮助MySQL高效获取数据的一种数据结构（有序）
有无索引的查询比较
这里的索引并不是真实的二叉树结构，类似可视化的结构
当然，索引也不是完全的完美，他也有缺点，索引优缺点比较
索引结构
我们知道，索引是在第三层（存储引擎层）实现的。不同的存储引擎实现的索引也是不同的
一般面试问的时候，都是说B+树
不同存储引擎对于索引结构的支持情况
B Tree（B树）
普通二叉树Tree
这里就可以思考，影响二叉树搜索效率的直接因素就是节点的深度，某一节点下他的层级越多深度越深，搜索效率也就越低。最直接提升查询效率的办法，就是降低节点深度，提高度数（一个节点上的子节点个数），让一个节点下有多个单节点，不再局限于之前的左小右大的树结构。引出B-Tree
B Tree（多路平衡查找树）
5阶代表5个子节点（区间指针）永远比key多一个，n-1 一共4个key（节点元素）
从底层开始插入，中间元素向上分裂，一个节点下的n-1个key满了的话，排在中间的那个key就会向上分裂出来，提取到当前节点的上一层
B树可视化展示：https://www.cs.usfca.edu/~galles/visualization/BTree.html
B+Tree
在B+树中，所有的元素都会出现在叶子节点，非叶子节点作为叶子节点的索引。
所有的叶子节点组成了一个单向链表
MySQL中的B+树是经过优化的，在叶子节点的部分（也就是单向链表）的每一个节点上加了一个指针，指向一个相邻节点，形成一个双向链表，提高访问性能。
B+树可视化展示：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
Hash索引
思考题
索引分类
InnoDB中的索引分类
聚簇索引（clustered index）也称为聚集索引，聚类索引，簇集索引，聚簇索引确定表中数据的物理顺序。
这么说太抽象了，用图来标示聚集索引和二级索引
演示一下查询语句的执行流程
两个索引搭配，二级索引通过名字abcd的排序先后顺序来确定走左还是走右，筛出id再走聚簇索引，聚簇索引来把主键id里行内信息查出来
这个过程专业术语叫回表查询
思考题
第一题
显然答案是第一个，因为第二个会触发回表查询（先触发二级索引，再触发聚簇索引，查两次，显然不高效），第一个SQL直接聚簇索引一查美滋滋
第二题
索引语法
创建索引
最后提到的一个字段可以关联多个字段的情况，就是联合索引
关于索引名怎么确定：idx_表名_字段名（idx为index的缩写）
测试一下
现在有这么个表tbl_user
先看看他当前的索引结构
show index from tbl_user
运行一下得到结果
需求1：
查看索引
需求2：
注意，非空的这个要在设计表的环节完成
CREATE UNIQUE index idx_tbl_user_phone on tbl_user(phone)
需求3：
联合索引就是把多个字段同时放进表后面的大括号里面
联合索引的名称取名字就简写即可
CREATE INDEX idx_tbl_user_pro_age_sta ON tbl_user(profession,age,status)
联合索引中，字段的顺序是有讲究的
需求4：
普通索引即可
CREATE index idx_tbl_user_age on tbl_user(age)
查看索引
删除索引
SQL性能分析
如果是要做SQL的优化，那么就需要定位出运行速度慢的SQL语句
主要优化的是查询语句（select）
执行频次统计
在连接到这个数据库之后，通过查询语句来看这个数据库的哪一类语句执行的次数多，也就是执行频次
连接一个数据库进行测试
可以看到Select语句执行次数比较多
慢SQL定位
那么要对一些慢Select语句进行定位
通过查询慢SQL日志就可以定位出哪些SQL的运行速度拖了后腿
profile详解
这是另外一种统计方式，针对于接近最大查询时间的语句
打个比方，设置的时间为2s，部分查询语句1.99s，就可以用这种方式进行查询
执行结果（支持profiling）
MySQL中是默认不打开的，打开方式
set profiling=1
打开之后就可以用了
show profiles
也可以根据Query_ID来进行精确的查看各个资源的耗费情况
explain执行计划
索引使用规则
索引就是一种空间换时间的取舍
都知道索引快，那么创建完索引后，使用原则是什么
最左前缀法则（针对于联合索引也就是索引了多列）
最左前缀原则：顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上。
（1）如果第一个字段是范围查询需要单独建一个索引；
（2）在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边；
当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！
打个比方：我有个表info有三个字段，profession,phone,address。我为这三个字段创建了联合索引，假设语句是这样的
CREATE INDEX idx_info_pro_pho_add on info(profession,phone,address)
建完索引后，执行查询语句
select * from info where profession='xx' and phone='xx' and address='xx'
可以看到三个字段按联合索引的建立查询，这种语句就会触发索引
只要是按最左顺序来的，只要最左边的那个字段依次存在。哪怕没有后面的两个都可以触发，这样会使索引部分失效而不会全部失效，部分失效的索引仍然可用。
以下语句都可以触发刚刚建立的联合索引，符合最左前缀法则
select * from info where address='xx' and profession='xx' and phone='xx'
select * from info where phone='xx' and profession='xx'//这个也是可以用的，只不过profession之后的就失效了
select * from info where profession='xx'
以下语句不符合最左前缀法则的都触发不了，缺失最左边的字段会使索引全部失效。
select * from info where address='xx'
select * from info where phone='xx' and address='xx'
select * from info where phone='xx'
范围查询
索引失效情况
失效情况一
-
不要在索引列上进行运算操作，索引会失效，这里的运算操作包含各种函数的运算，类似substring也会导致索引的失效
-
查询的时候字符串不加单引号也会使索引失效
-
模糊查询的%，在关键词之后就不会影响触发索引，在关键词之前就会使索引失效
'%关键词'这种无法触发索引
'关键词%'这种可以触发索引
'%关键词%'这种更没法触发了
所以在大数据量的情况下更要去避免模糊查询的情况，防止性能下降
失效情况二
or连接的前后条件，一侧有索引一侧没有索引，那么两个索引都会失效。
如果想让or触发索引的话，两侧的条件都有索引就可以触发了
数据分布影响
当MySQL评估索引的速度不如全表扫描的时候，MySQL就会改用全表扫描的情况，来替代相对慢了的索引。具体走不走索引取决于当前这个字段的数据分布情况（数据都是些什么数据）
SQL提示（规定MySQL使用某一索引）
如果我想指定使用某个SQL怎么办呢
在from表名和where中间加入use index（索引名称）
那我想换个玩法，不想用某个索引
在from表名和where中间加入ignore index（索引名称）
强制MySQL使用我指定的索引
在from表名和where中间加入force index（索引名称）
总结
覆盖索引
尽量避免使用select * 的查询方式，会导致性能下降
因为select*很容易就触发了之前说过的回表查询
案例
前缀索引
情景描述，一个字段来存储长文本，如果为这个长文本建索引，就会占用大量空间，可以截取一定的长度，来为这个短字符串建立索引，语法和普通的索引建立差不太多
关于前缀索引的取名idx_表名_取前几个索引
前缀索引执行流程
单例&联合索引
联合索引结构
索引设计原则
索引小结
SQL优化
插入数据
主键顺序插入的性能要高于乱序插入
主键优化
业务操作时，尽量避免对主键进行修改
order by排序操作优化
使用这种优化的前提是你不能用select *
如果用了索引就自动失效了，要用覆盖索引精确查询
排序也可以走索引，排完序返回的数据是用索引返回的
查看索引show index from 表名
group by分组操作优化
主要研究索引对于分组操作的影响
limit分页操作优化
起始位置越大，耗时越长，耗费十几秒都很正常
大数据量的limit，越往后的分页性能越低
优化一下
count优化
update优化
更新字段时，一定要根据索引字段进行更新
打个比方
begin开启事务 commit提交事务，
update 表名 set 字段=xxx ，
假设条件这个字段没有索引，在执行事务更新操作commit之前就会把整张表都锁住，后面的操作就会被迫停下等待放锁。如果这个字段有了索引，就只锁那一行，不会锁住整个表，也就不会导致后面的操作停下来。
SQL优化小结
针对操作的字段加上适当的索引就是优化操作
视图
视图概述
视图是一种虚拟表，数据来源于后续的Select语句，这个Select语句中from的表就是视图的基表 ，视图本身是一种虚拟表，数据是基于它的基表的，因此视图本身不存储数据。因此，如果对视图进行insert一类的操作，实际上这些操作最后是命中到它所对应的基表上的。
也就是相当于每次执行增删改之后，都是命中在基表上，然后再通过视图的条件更新出来的。
虚拟表毕竟是一种表，所以是可以对虚拟表进行操作的。正常去执行一些SQL语句都是可以的
视图语法
创建一个视图
修改一个视图
注意，修改操作基本上就是对之前的视图进行重新查询并且完成生成新视图的操作
查询视图就直接select xxx from 视图名就行 where 条件...
视图-检查选项CASCADED
所谓的检查选项，主要是集中在这个位置上：
现在，我对一个视图进行插入操作
对视图进行增删改的问题最终是命中到基表上的，再通过之前的视图语句查询出来的。
但是有这么个问题，假设我视图创建时加入了条件，类似where id<10 这种视图条件。但这个时候我插入了id=11的数据，这种其实是可以插入的，只不过在视图中不显示罢了。如果我希望插入的数据不符合视图条件的时候会报错，这时可以看看视图的检查选项WITH CASCADED CHECK OPTION
还有一种情况，和图上检查视图相反的玩法，就是v1有检查，但是v2视图没有检查，v2是基于v1视图的。这种情况依旧会走一下v1视图的检查条件，因为v2是基于v1的，v1是有检查条件的
也就是，视图检查会检查当前视图，以及当前视图所依赖的所有视图的条件是否满足。
视图-检查选项Local
WITH LOCAL CHECK OPTION
local会递归的去找当前视图依赖的视图，如果有依赖检查选项就筛查一下，如果没有检查选项就不会再进行检查。
就是上面CASCADED的不传递检查版本，如果有就查，没有就不查，不传递检查条件
视图的更新及作用
更新
打个比方，我创建一个视图
create view count_view as select count(*) from xx表
这种情况就无法更新，因为没有与基表数据一一对应，因此无法进行更新。
作用
可以把一些复杂操作定义好在视图中，查询出来一些不机密的数据，把基表中的机密数据屏蔽掉，屏蔽掉数据变化等等。
视图实战
- 第一个题
无论表里有啥，不带手机号和邮箱就行了
create view tbl_user_view AS select id,name,age from tb_user
再用这个表我们查询视图就可以了，就不需要查询tb_user这个表了，这样就间接屏蔽了隐私数据
select xxx from tbl_user_view
- 第二个题
先把三个表联查
select a.name,b.age,c.info from a,b,c where a.id=b.stuId and b.id=c.classId
创建视图进行封装
create view tbl_stu_view as select a.name,b.age,c.info from a,b,c where a.id=b.stuId and b.id=c.classId
存储过程
概述
本质上是为了减少网络的IO开销，将SQL提前存好
锁
概述
全局锁
加全局锁flush tables with read lock
备份指令mysqldump -u用户名 -p密码 库名>位置/库名.sql(这个是在win命令行执行的)
解锁语法unlock tables
表级锁
读锁不会阻塞其他客户端的读，但是会阻塞写。写锁会阻塞其他客户端的读和写。
元数据锁
引擎自动给你上的，了解一下事务开启的时候是这么保证语句之间不冲突的
意向锁
行级锁
锁住对应数据行
共享锁Share（S）
排它锁Xclusive（X）
共享锁与共享锁之间可以共存
排他锁和排它锁之间互斥
共享锁与排它锁之间互斥
想要拿到排它锁就得等当前排它锁释放了才能拿到
总结
228
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
