Mysql基础
sql分类
DDL：数据定义语言 create、alter、drop、rename、truncate
DML：数据操作语言 insert、delete、update、select
DCL：数据控制语言 commit、rollback、savepoint、grant、revoke
数据库创建文件
db.opt：存储当前数据库的默认字符集和字符校验规则
独立表空间
test.frm 存放的数据表结构
test.ibd 存放的test表中的数据和索引
系统表空间
[server] innodb_file_per_table = 0 0：代表使用系统表空间，1：代表使用独立表空间
show variables like 'innodb_file_per_table'
MHA高可用架构原理
1、监控节点（通过配置文件获取所有节点信息）
系统，网络，SSH连接性，主从状态，重点是主库
2、主库宕机
2.1选主
（1）如果判断从库（position或者GTID），数据有差异，最接近master的slave成为备选主
（2）如果判断从库（position或者GTID），数据有一致，按照配置文件顺序选主
（3）如果设定有权重（candidate_master=1），按照权重强制指定备选主
默认情况下slave落后master 100M的relay logs的话，即使有权重，也会失效
如果check_repl_delay=0的话，即使落后再多，也会选为备选主
3、数据补偿
（1）当SSH能连接，从库对比主库GTID或者position号，立即将二进制日志保存至各个从节点
4、failover
将备选主进行身份切换，对外提供服务
其余从库和新主库确认新的主从关系
5、应用透明（VIP） MHA自带
6、故切换通知
7、二次数据补偿（binlog_server）
事务
事务操作
1、修改事务的提交方式
select @@autocommit
set @@autocommit = 0 设置为手动提交
commit 提交事务
rollback 回滚事务
2、不修改事务的提交方式
start transaction/begin 开启事务
commit 提交事务
rollback 回滚事务 ------操作异常 表中的数据没有真正发生变化
事务特性ACID
原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
一致性：事务完成时，必须使所有的数据都保持一致的状态
隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性：事务一旦提交或回滚，他对数据库中的数据的改变是永久的
并发事务问题
1、脏读：一个事务读到另外一个事务还没有提交的数据
事物A：1、查询id为1的数据
事物A：2、更新id为1的数据
事物B：3、查询id为1的数据，事物A没有提交，事物B查询到A更改后的数据
2、不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
事务A：1、查询id为1的数据
事务B：2、更新id为1的数据，并提交
事务A：3、查询id为1的值，与第一次查询结果不同
3、幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，由发现这样的数据已经存在，好像出现了“幻影”
事务A：查询id为1的数据,没有查到
事务B：执行insert语句id为1，并提交
事务A：插入id为1的数据------报错主键重复或冲突
事务A：查询id为1的数据，没有
事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| read uncommitted 读未提交 | √ | √ | √ |
| read committed 读已提交 | × | √ | √ |
| repeatable read 默认 可重复读 | x | x | √ |
| serializable 可串行 | x | x | x |
注意：事务的隔离级别越高，数据越安全，但是性能越低 一般采用默认
1、查询事务隔离级别
select @@transaction_isolation;
2、设置事务隔离级别
set [session|global] transaction isolation level [read uncommitted|read committed|repeatable read|serializable]
存储引擎
mysql体系结构
连接层：主要完成一些类似于连接处理、授权认证及相关的安全方案。服务器也会为安全接入的每个客户端验证他所具有的操作权限
服务层：主要完成大多数的核心服务功能，如sql接口、并完成缓存查询，sql的分析和优化，部分内置函数的执行，所有跨存储引擎的功能也是在这一层实现，如过程、函数等
存储引擎层：负责mysql中数据的存储和提取，服务器通过api和存储引擎进行通讯，不同的存储引擎具有不同的功能，这样我们根据自己的需要，来选取合适的存储引擎
存储层：将数据存储在系统文件，并完成存储引擎的交互
存储引擎特点
1、Innodb 5.5之后默认的存储引擎
特点：
DML操作遵循ACID，支持事务
行级锁，提高并发访问性能
支持外键foreign key约束，保证数据的完整性和正确性
文件：
xxx.ibd：xxx代表的是表名，innodb引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引
参数：innodb_file_per_table
文件：
tbbook.ibd：存储表数据
tbbook.frm：存储表结构
逻辑存储结构：
tablespace segment extent page row
2、myisam
特点：
不支持事务 ，不支持外键
支持表锁，不支持行锁
访问速度快
文件：
tbbook.MYD：存储表数据
tbbook.MYI：存储索引
tbbook_448.sdi：存储表结构
3、memory
存储在内存中，由于受到硬件问题或断电的影响，只能将这些表作为临时表或缓存使用
特点：
内存存放
支持hash
访问速度快
文件：
sdi：存储表结构
| 特点 | innodb | myisam | memory |
|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 |
| 事务安全 | 支持 | ||
| 锁机制 | 行锁 | 表锁 | 表锁 |
| B+tree索引 | 支持 | 支持 | 支持 |
| hash索引 | 支持 | ||
| 全文索引 | 支持 5.6版本之后 | 支持 | |
| 空间使用 | 高 | 低 | N/A |
| 内存使用 | 高 | 低 | 中等 |
| 批量插入速度 | 低 | 高 | 高 |
| 支持外键 | 支持 |
存储引擎选择
在选择存储引擎时，应根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合
innodb：是mysql默认的存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么innodb存储引擎是比较合适的
myisam：如果应用以读操作和插入操作为主，只有很少的更新和删除的操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎非常合适的
memory：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。memory的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保证数据的安全性
索引
优缺点
优点：提高查询效率，降低数据库的io成本；通过索引列对数据进行排序，降低数据排序的成本，降低cpu的消耗
缺点：索引列也要占用空间；索引大大提高了查询效率，同事却也降低更新表的速度，如对表进行insert、update、delete时，效率降低
索引结构
| 索引 | innodb | myisam | memory |
|---|---|---|---|
| b+tree | 支持 | 支持 | 支持 |
| hash | 不支持 | 不支持 | 支持 |
| r-tree | 不支持 | 支持 | 不支持 |
| full-tesxt | 5.6版本后支持 | 支持 | 不支持 |
二叉树
一个节点下面最多包含两个子节点 左侧节点比根节点小 右侧节点比根节点大
缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢
红黑树
自平衡的二叉树 解决树的平衡问题
缺点：大数据量情况下，层级较深，检索速度慢
B树
以一颗最大度数为5（5阶）的b-tree为例（每个节点最多存储4个key，5个指针）
树的度数指的是一个节点的子节点个数
根节点，叶子节点，非叶子节点下面挂的是数据
B+树
以一颗最大度数为4（4阶）的b+-tree为例
相对于B-Tree区别：所有的元素都会出现在叶子节点 叶子节点行成一个单项链表
非叶子节点起到索引作用
mysql索引数据结构对经典的B+Tree进行了优化，在原有的基础上，增加了一个指向相邻叶子节点的链表，就形成了带有顺序指针B+Tree，提高区间访问的性能
Hash索引
采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中
如果两个或多个键值，映射到一个相同的槽位上，他们就产生了hash冲突，可以使用链表来解决
特点：
只能用于对等比较（=，in）不支持范围查询（between，>，<）
无法利用索引完成排序
查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引
存储引擎支持：
在Mysql中，支持hash索引的是Memory引擎，而Innodb中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定掉件下自动构建的
1、为什么Inndb存储引擎选择B+Tree索引结构？？？？？
相对于二叉树，层级更少，搜索效率高
对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
相对于hash索引只支持等值匹配，不支持范围匹配
索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在Innodb存储引擎中，根据索引的存储形式，又可以分为以下两种
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引 | 将数据存储与索引放在了一块，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 |
| 二级索引 | 将数据与索引分开存储，索引结构的叶子接地那关联的是对应的主键 | 可以存在多个 |
聚集索引的选取规划：
如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一索引作为聚集索引
如果表没有主键，或没有合适的唯一索引，则Innodb会自动生成一个rowid作为隐藏的聚集索引
select * from user where name="arm" 先走二级索引 在走聚集索引
回表查询：先走二级索引查找id，然后在找聚集索引查询整行数据
索引语法
创建索引：create [unique|fulltext] index index_name on table_name(index_col_name)
查看索引：show index from table_name
删除索引：drop index index_name on table_name
索引性能分析
SQL性能分析
1、sql执行频率
通过show session|global status命令可以提供服务器状态
通过show global status like "com___"查看mysql的insert、select、update、delete等查询频次
2、慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位：秒，默认10秒)的所有sql语句的日志
Mysql的慢查询日志默认是没有开启的，需要在配置文件中配置
show variables like "slow_query_log"
3、profile详情
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了，通过have_profiling参数能看到mysql是否支持
select @@have_profiling 默认是关闭
set profiling = 1 开启profile
show profiles 查看每一条sql的耗时基本情况
show profile for query query_id 查看指定query_id的sql各个阶段的耗时情况
show prifile cpu for query query_id 查看指定query_id的SQL语句cpu的使用情况
4、explain执行计划
查看sql语句的执行计划，查看语句执行过程中表如何连接和连接的顺序
id：sql语句当中表的执行顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
select_type:表示select的类型，常见的取值有simple（简单表，既不使用表连接或者子查询）、primary（主查询，即外层的查询）、union（union中的第二个或者后面的查询语句）、subquery（select/where之后包含的子查询）等
type:表示连接类型，性能由好到差的连接类型为null、system、const、eq_ref、ref、range、index、all
prossible_key:显示可能应用到的索引
key：实际使用的索引，如果为null，则没有使用索引
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，病史实际使用长度，在不损失精确性的前提下，长度越短越好
rows：mysql认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好
索引使用规则
1、最左前缀法则
如果索引了多列（联合索引），要最受最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将部分时效（后面的字段索引失效）
索引必须包含最左边的列，如果不存在，索引失效
explain select * from tb_user where profession="软件工程" and age=31 and status='0'
explain select * from tb_user where profession="软件工程" and age=31
explain select * from tb_user where profession="软件工程"
explain select * from tb_user where age=31 and status='0'
explain select * from tb_user where status='0'
2、范围查询
联合做引中出现（>,<），范围查询右侧的列索引失效
3、索引列运算
不要在索引列上进行运算操作，否则索引会失效
4、字符串不加引号
字符串类型字段不加引号，索引将失效
5、模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引会失效
6、or的连接条件
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么设计的索引都不会被用到
7、数据分布影响
如果mysql评估使用索引比全表扫描更慢，则不适用索引
索引使用
1、SQL提示
是优化数据库的一个重要手段，简单来说，就是在sql语句中加入一些人为的提示来达到优化操作的目的
user index：explain select * from tb_user use index(id_user_pro) where profession="ruanjian" 建议使用
ignore index：explain select * from tb_user use index(id_user_pro) where profession="ruanjian" 忽略使用
force index：explain select * from tb_user use index(id_user_pro) where profession="ruanjian" 强制使用
2、覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *
explain select id,profession from tb_user where profession='test' and age=31 and status='0'
explain select id,profession,age,status from tb_user where profession='test' and age=31 and status='0'
explain select id,profession,age,status,name from tb_user where profession='test' and age=31 and status='0'
explain select * from tb_user where profession='test' and age=31 and status='0'
using index condition：查找使用了索引，但是需要回表查询数据
using where；use index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
回表查询：先去二级索引查找id，在聚集索引根据id查找数据
3、前缀索引
当字段类型为字符串（varchar、text）有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘io，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率
create index id_xxxxx on table_name(column(n));
前缀长度
可以根据索引的选择性来决定，而选择性指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这事最好的索引选择性，性能也是最好的
select count(*) from tb_user
select count(distinct substr(email,1,10))/count(*) from tb_count;
4、单列索引和联合索引
单列索引：一个索引只包含一个列
联合索引：一个索引包含了多个列
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引
索引设计原则
针对数据量较大，且查询比较频繁的表建立索引
针对常作为查询条件、排序、分组操作的字段建立索引
尽量选择区分度高的列所谓索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率
如果索引不能存储null值，请在创建表时使用not null约束它。当优化器知道每列是否包含null值时，他可以更好地确定那个索引最有效的用于查询
SQL优化
insert优化
每次操作insert都需要与数据库建立连接，网络传输，性能比较低
1、批量插入 不建议超过1000条 500-1000条最合适
2、手动事物提交
mysql默认是自动提交的，每次执行insert会开始事物，会多次频繁开启事务
3、主键顺序插入 顺序插入性能高于乱序插入
大批量数据量插入
如果一次性需要插入大量数据，使用insert语句插入性能较低，此时可以使用Mysql数据库提供的load指令进行插入
1,jimingqu,jimingqu,jimingqu,2020-10-13,1 数据模板
2,jiminddfgqu,jimfsdfingqu,jiminsdfsgqu,2020-10-13,1
1、客户端连接服务端时，加上参数--local-infile
mysql --local-infile -uroot -p
2、设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
3、执行load指令将准摆好的数据，加载到表结构中 尽量使用主键顺序插入
load data local infile '/root/sql.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n'
主键优化
1、数据组织方式
在Innodb存储引擎中，表数据都是根据主键顺序组织存放的，这种存放方式的表称为索引组织表
2、页分裂
页可以为空，也可以填充一般，也可以填充100%。每个页包含2-N行数据（如果一行数据多大，会行溢出），根据主键排列
主键乱序插入会出现页分裂
3、页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记为删除并且他的空间变得允许呗其他记录声明使用
当页中删除的记录达到merge_threshold（默认页的50%），innodb会开始寻找最靠近的页看看是否可以将两个页合并以优化空间使用
merge_threshold：页合并的阈值，可以自己设置，在创建表或者创建索引的时候指定
4、主键设计原则
根据业务需求的情况下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键
尽量不要使用uuid或其他的自然主键，如身份证号
业务操作时，避免对主键的修改
order by优化
Using filesort：将返回的结果排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
Using index：通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高
根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时注意联合索引在创建时的规则
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256k）-----如果满了就会在磁盘中排序
group by优化
索引，多字段分组满足最左前缀法则
limit优化
limit 2000000,10
通过覆盖索引+子查询
select a.* from tb_user a,(select id from tb_user order by id limit 2000000,10) b where a.id=b.id
count优化
count()是一个聚合函数，对于返回的结果集，以行行地判断，如果count函数的参数不是null，累计值就加1，否则不加，最后返回累计值
count(*)
并不会取出全部字段，而是专门做了优化，不取值，服务层直接按行进行累加
count(1)
遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加
count(主键)
遍历整张表，把每一行的主键id值取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（不可能为null）
count(字段)
没有not null约束：会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加
有not null约束：会遍历整张表，把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
update优化
在更新数据时，一定根据索引字段更新数据
如果采用的其他字段更新，会导致表锁而不是行锁，数据不提交的话另一条语句会一直执行
innodb的行锁是针对索引加的锁，不是针对记录家的锁，并且该索引不能失效，否则会从行锁升级为表锁。
视图
1、介绍
是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的
with check option：mysql会通过视图检查正在更改的每行，允许基于另一个视图创建视图。还会检查依赖视图中的规则以保持一致性。为了确定检查范围，mysql提供了两个选择：cascaded和local，默认为cascaded
2、视图的更新
使视图更新，视图中的行与基础表中的行之间必须存在一对一关系。如果视图中包含一下任何一项，则视图不可更新：
集合函数或窗口函数sum() min() max() count()
distinct
group by
having
union或者union all
存储过程
1、存储过程基本用法
delimiter ;
create procedure p1() 创建存储过程
begin
select count(*) from students;
end;
call p1(); 调用
show create procedure p1；查询存储过程的定义
select * from information_schema.routines where routine_schema='itcast';查询指定数据库itcast的存储过程及状态信息
drop procedure if exists p1;删除存储过程
2、变量
系统变量是mysql服务器提供，用于服务器层面。分为全局变量、会话变量
查看系统变量
show [global|session] variables; 查看所有系统变量
show [global|session] variables like '----'; 可以通过like模糊匹配查找变量
select @@[global|session] 系统变量名;查看指定变量的值
设置系统变量
set [global|session] 系统变量名=值;
set @@[global|session] 系统变量名=值;
3、传递参数
create procedure p4 (inscore int,out result varchar(10))
begin
if score >=85 then
set result := 'youxiu'
elseif score >=60 then
set result := 'jige'
else
set result := 'bujige'
end if;
end;
call p4(68,@result)
若传入的参数进行换算在返回 ------- inout
create procedure p5(inout score double)
begin
set score := score*0.5;
end
set @score=78;
call p5(@score);
4、case
create procedure p6(in month int)
begin
declare result varchar(10);
case when month >=1 and month<=3 then set result := 'yi'
case when month >=4 and month<=6 then set result := 'ei'
else set result :='error'
end case;
select concot('您输入的月份为',month,'所属的季度为:',result)
end;
call p6(4);
5、while
create procedure p7 (in n int)
begin
declare total int defult 0;
while n >0 do
set total := total +n
set n := n-1
end while;
select total;
end;
call p7(10)
6、repeat 满足条件后退出循环
create procedure p8 (in n int)
begin
declare total int defult 0;
repeat
set total := total +n
set n := n-1
until n<=0;
end repeat;
select total;
end;
call p8(10)
7、loop
不增加退出循环条件，可以用来实现简单的死循环
leave：配合循环使用，退出循环
iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环
计算从1累加到n的值
create procedure p9 (in n int)
begin
declare total int defult 0;
sum:loop
if n<=0 then
leave sum;
end if;
set total := total +n;
set n := n-1;
end loop sum;
select total;
end;
call p9(10)
计算从1累加到偶数n的值
create procedure p10 (in n int)
begin
declare total int defult 0;
sum:loop
if n<=0 then
leave sum;
end if;
if n%2 =1 then
set n := n-1
interate sum;
end if;
set total := total +n;
set n := n-1;
end loop sum;
select total;
end;
call p10(10)
8、游标
用来存储查询结果集的数据类型，在存储过程和含住中可以使用游标对结果集进行循环的处理。游标使用包括游标的声明、open、fetch和close
声明游标 declare name cursor for select语句
打开游标 open name
获取游标 fetch name into 变量
关闭游标 close name
create procedure p11(in uage int)
begin
declare uname varchar(50);
declare upro varchar(50);
declare u_cursor cursor for select name,profession from tb_user where age <= uage;
create table if not exists tb_user_pro(
id int primary auto_increment,
name varchar(50),
profession varchar(10)
);
open u_corsor;
while true do
fetch u_corsor into uname,upro;
insert into tb_user_pro values(null,uname,upro)
end while;
close u_cursor;
end;
9、条件处理程序：可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。
create procedure p11(in uage int)
begin
declare uname varchar(50);
declare upro varchar(50);
declare u_cursor cursor for select name,profession from tb_user where age <= uage;
declare exit handler for sqlstate '02000' close u_cursor
create table if not exists tb_user_pro(
id int primary auto_increment,
name varchar(50),
profession varchar(10)
);
open u_corsor;
while true do
fetch u_corsor into uname,upro;
insert into tb_user_pro values(null,uname,upro)
end while;
close u_cursor;
end;
存储函数
存储函数是有返回值，参数只能是in类型的
create function fun1(n int)
returns int
begin
delcare total int default 0;
while n>0 do
set total := total +n;
set n := n-1;
return total;
end;
触发器
1、insert类型触发器
创建
create trigger name
before/alter/insert/update/delete
on tal_name foreach row
begin
trigger_stmt
end;
查看
show triggers
删除
drop trigger name
锁
全局锁
是对整个数据库实例加锁，加锁后实力处于只读状态，后续的DML语句、DDL语句已经更新操作的事物提交语句都将被阻塞
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
对数据库加一个全局锁----->mysqldump---->可以DOL不可以DDL、DML
只能读不能写
加全局锁：flush tables with read lock；
备份：mysqldump -uroot -p111111 -h127.0.0.1 db01 >db01.sql
解锁：unlock tables
--single-transcation
特点：数据库中加全局锁存在以下问题
1、如果在主库备份，那么在备份期间都不能执行更新，业务基本上就停摆
2、如果在从库上备份，那么在备份期间从库不能直接执行主库同步过来的二级那会日志，会导致主从延迟
在Innodb引擎中，我们可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份
表级锁
分类：
表共享读锁
表独占写锁
语法：
1、加锁：locak tables 表名。。。 read/write
2、释放锁：unlock tables
1、读锁特定：
客户端1
lock tables socre read;
select * from score;
update score set math=100 where id='1'
报错：table score was locked with a read lock and can't be updated
客户端2：
update score set math=100 where id='1' 阻塞状态
客户端1：unlock tables
客户端2：解除阻塞状态,更新成功
读锁不会阻塞读，会阻塞其他客户端的写
2、写锁：
一旦加了写锁当前客户端可以对其读写，其他客户端不可
客户端1：
lock tables score write
select * from score
update score set math='100' where id ='1';
客户端2：
select * from score;
update score set math='100' where id ='1'; 都会被阻塞
客户端1：
unlock tables;
客户端2：
执行的命令不会被阻塞了‘
3、元数据锁
MDL加锁过程是系统自动控制，无须显示使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事物的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性
客户端1：
begin;
select * from score;
客户端2：
alert table......
在Mysql5.5中引入了MDL，当对一张表进行增删改查的时候，加入MDL读锁（共享锁）；当对表结构进行变更操作的时候，加入MDL写锁（排他）
| 对应SQL | 锁类型 | 说明 |
|---|---|---|
| lock tables xxx read/write | shared_read_only/shared_no_read_write | |
| select ...locak in share mode | shared_read | 与sahred_read shared_write兼容,与exclusive互斥 |
| insert update delete select...for update | shared_write | 与shared_read shared_write兼容,与互exclusive斥 |
| alert table... | exclusive | 与其他的MDL都互斥 |
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks
意向锁
意向共享锁IS：由语句select ... lock in share mode添加
与表锁共享锁read兼容，与表锁排它锁write互斥
意向排它锁IX：由insert、update、delete、select ... for update添加
与表锁共享锁read及排它锁write都互斥。意向锁之间不会互斥
查看意向锁及行锁的加锁情况
select index_name,object_schema,object_name,lock_type,lock_mode,lock_duration from performance_schema.data_locks;
客户端1：
begin;
select * from score where id = '1' lock in share mode
客户端2：
select index_name,object_schema,object_name,lock_type,lock_mode,lock_duration from performance_schema.data_locks;
lock tables score read;
lock table score write;阻塞
innodb引擎中加行锁与表锁的冲突问题
行级锁
锁定粒度小，发生冲突的概率高，并发度高。应用在innodb存储引擎中
innodb的数据是基于索引组织的，行锁是通过索引上的索引项加锁类实现的，而不是对记录加的锁。对于行级锁，主要分为一下三类：
行锁：锁定的是单行记录。防止其他事物对此进行update或delete。在RC、RR隔离下支持
间隙锁：锁住索引记录的间隙（不包含该记录）。防止其他事物在这个间隙进行insert，产生幻读，在RR隔离级别下都支持
临键锁：行锁和间隙锁的组合。同时锁住数据，并锁住数据前面的间隙，在RR隔离级别下支持
1、行锁
共享锁：允许一个事物去读一行，组织其他事物获得相同数据集的排它锁
排它锁：允许获取排它锁的事物更新数据，阻止其他事物获得相同数据集的共享锁和排它锁
| s共享锁 | x排它锁 | |
|---|---|---|
| s共享锁 | 兼容 | 冲突 |
| x排它锁 | 冲突 | 冲突 |
insert、update、delete 排它锁 自动加锁
select 不加任何锁
select ... lock in share mode 共享锁 需要手动添加
select .... for update 排它锁 需要手动添加
行锁演示：
默认情况下，innodb在repeatable read事物隔离级别运行，inndb使用next-key锁进行搜索和索引扫描，以防止幻读
针对唯一索引检测时，对已存在的记录进行等值匹配时，将自动优化为行锁
innodb的行锁是针对于索引加的锁，不通过索引条件检索数据，那么innodb将对表中的所有记录加锁，此时将会升级为表锁
客户端1：
beigin;
select * from score where id='1'
select * from score where id='1' lock in share mode
客户端2：
select index_name,object_schema,object_name,lock_type,lock_mode,lock_duration from performance_schema.data_locks;
select * from score where id='1' lock in share mode
2、间隙锁/临键锁-演示
默认情况下，Innodb在repeatable read事物隔离级别运行，inndb使用next-key锁进行搜索和索引扫描，以防止出现幻读
1、索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
2、索引上的普通查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁
3、索引上的范围查询（唯一索引）--会访问到不满足条件的第一个值为止
间隙锁唯一的目的是防止其他事物插入间隙。间隙锁可以共存，一个事物采用的间隙锁不会阻止另一个事物在同一间隙上采用间隙锁
innodb引擎
逻辑存储结构
表空间ibd文件：一个mysql实例可以对应多个表空间，用于存储记录、索引等数据
段：分为数据段、索引段、回滚段、innodb是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。用来管理多个区
区：表空间的单元结构，每个区大小为1M。默认情况下，Innodb存储引擎页大小为16K，即一个区中一共有64个连续的页
页：是innodb存储引擎磁盘管理的最小单元，每个页的带下默认为16kb。为了保证页的连续性，innodb存储引擎每次从磁盘申请4-5个区
行：innodb存储引擎数据是按照行进行存放的
内存架构
1、buffer pool：缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池中没有书，则从磁盘加载并缓存），然后再以一定的频率刷新到磁盘中，从而减少磁盘io，加快处理速度
缓冲池以page为单位，底层采用链表数据结构管理page。根据状态，将page分为三种类型：
free page：未被使用
clean page：被使用page，数据没有被修改过
dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致
2、change buffer：更改缓冲区（针对于非唯一的二级索引页），在执行DML语句时，如果这些数据page没有在buffer pool中，不会直接操作磁盘，而会将数据变量存在更改缓冲区中，在未来数据被读取时，再将数据合并恢复到buffer pool中，再将合并后的数据刷新到磁盘中
change buffer存在的意义是什么？
与聚集索引不同，二级索引通常是非唯一的，并且一向对随机顺序插入二级索引，同样，删除和更新可能绘银箱缩阴术中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘io，有了changebuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘io
3、adaptive hash index：自适应hash索引，用于优化对buffer pool数据的查询。innodb存储引擎会监控对表上各索引页的查询，如果观察到hash碎银可以提升速度，则建立hash索引，称之为自适应hash索引
自适应哈希索引，无须人工干预，是系统根据情况自动完成
参数：adaptive_hash_index
4、log buffer日志缓冲区
用来保存要写入磁盘中的log日志数据（redo log、undo log），默认大小为16M；日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区大小可以节省磁盘io
参数：
innodb_log_buffer_size：缓冲区大小
innodb_flush_log_at_trx_commit：日志刷新到磁盘的时机
1：日志每次事务提交时写入并刷新到磁盘
0：每秒将日志写入并刷新到磁盘一次
2：日志每次事务提交后开始写入，并每秒刷新到磁盘一次
磁盘架构
1、system tablespace
系统表空间是更改缓冲区的存储区域，如果表示在系统表空间而不是每个表文件或通用表空间创建的，他也可能包含表和索引数据（在mysql5.x版本中还包含innodb数据字典，undolog等）
参数：innodb_data_file_path
2、file-per-table tablespace：每个表的文件表空间包含单个innodb表的数据和索引，并存储在文件系统上的单个数据文件中。
参数：innodb_file_per_table
3、general tablespace：适用表空间，需要通过create tablespace语法创建表空间，在创建表空间时，可以指定表空间
4、undo tablespace：撤销表空间，mysql实例在初始化时会自动创建两个默认的undo表空间（初试大小是16M），用于存储undo log日志undo_001 undo_002
5、temporary tablespace：innodb适用会话临时表空间和全局临时表空间，存储用户创建临时表等
6、doublewrite buffer files：双写缓冲区，innodb引擎将数据页从buffer pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据 文件名.dblwr
7、redo log：重做日志，是用来实现事物的持久性。该日志文件由两部分组成，重做日志缓冲redo log buffer以及重做日志文件redo log，前者是在内存中，后者是在磁盘中。当事物提交之后会把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用 ib_logfile0、ib_logfile1
后台线程
1、master thread
核心后台线程，负责调度使用其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，还包括脏页的刷新，合并插入缓存，undo页的回收
2、io thread
在innodb存储引擎中大量使用了AIO来处理io请求，这样可以极大地提高数据库的性能，而IO thread主要负责这些io请求的回调
| 线程类型 | 默认个数 | 职责 |
|---|---|---|
| read thread | 4 | 负责读操作 |
| write thread | 4 | 负责写操作 |
| log thread | 1 | 负责将日志缓冲区刷新到磁盘 |
| insert buffer thread | 1 | 负责将写缓冲区内容刷新到磁盘 |
3、purge thread
主要用于回收事务已经提交的undo log，在事务提交之后，undo log可能不用了，就用它来回收
4、page cleaner thread
协助master thread刷新脏页到磁盘的线程，他可以减轻master thread 的工作压力，减少阻塞
事物原理
1、事务
是一组操作的集合，他是不可分割的工作单位，事务把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么全部成功，要么全部失败
特性：
原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
一致性：事务完成时，必须使所有的数据保持一致的状态
隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性：事务一旦提交或回滚，他对数据库中的数据的改变是永久的
2、redo log保证事物的持久性
重做日志，记录的是事务提交时数据页的物理修改，是用来实现事物的持久性
该日志文件又两部分组成：重做日志缓冲redo log buffer以及重做日志redo log file，前者是在内存中，后者是在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于刷新脏页到磁盘，发生错误时，进行数据恢复使用
3、undo log解决事物的原子性
回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC（多版本并发控制）
undo log和redo log记录物理日志不一样，他是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，他可以记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚
undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment回滚段中，内部包含1024个undo log segment
MVCC
1、当前读
读取的是记录的最新版本，读取时还要保证其他并发事物不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode（共享锁），select ... for update、update、insert、delete（排它锁）都是一种当前读
2、快照读
简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读
read committed：每次select，都生成一个快照读
repeatable read：开始事物后第一个select语句才是快照读的地方
serializable：快照读会退化为当前读
3、MVCC
多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为Mysql实现MVCC提供了一个非阻塞读功能。MVCC具体实现，还要依赖于数据库记录中的三个隐式字段、undo log日志、readview
实现原理
1、隐式字段
| 隐式字段 | 含义 |
|---|---|
| DB_TRX_ID | 最近修改事务id，记录插入这条记录后最后一次修改该记录的事务id |
| DB_ROLL_PIR | 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本 |
| DB_ROW_ID | 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段 |
2、undo log
回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志
当insert的时候，产生的undo log日志只有在回滚时需要，在事务提交后，可被立即删除
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除
undo log版本链：不同事物或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表的尾部是最早的旧记录。
3、readview读视图
是快照读sql执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id
readview中包含的四个核心字段：
| 字段 | 含义 |
|---|---|
| m_ids | 当前活跃的事务id集合 |
| min_trx_id | 最小活跃事务id |
| max_tri_id | 与分配事务id，当前最大事务id+1（因为id是自增的） |
| creator_tri_id | readview创建者的事务id |
trx_id：代表是当前事务ID
版本链数据访问规则：
trx_id == creator_trx_id？可以访问该版本 ---成立，说明数据是当前这个事务更改的
trx_id < min_trx_id 可以访问该版本 ---成立，说明数据已经提交了
trx_id > max_trx_id？不可以访问该版本
min_trx_id <= trx_id <=max_trx_id？如果trx_id不在m_ids中是可以访问该版本的 ---成立，说明数据已提交
不同的隔离级别，生成readview的时机不同：
read committed：在事务中每一次执行快照读时生成readview
repeatable read：仅在事务第一次执行快照读时生成readview，后者服用该readview
RC隔离级别下，在事务中每一次执行快照读时都生成readview
RR隔离级别下，仅在事务中第一次执行快照读时生成readview，后续复用该readview
日志
错误日志
show variable like '%log_error%'
二进制日志
记录了所有的DDL语句和DML语句
作用：灾难时的数据恢复、mysql主从复制
show variables like '%log_bin%'
| 日志格式 | 含义 |
|---|---|
| statement | 基于SQL语句的日志记录，记录的是sql语句，对数据进行修改的sql都会记录在日志文件里 |
| row | 基于行的日志记录，记录的是每一行的数据变更 |
| mixed | 混合了statement和row两种格式，默认采用statement，某些特殊情况下会自动奇幻为row进行记录 |
show variables like '%binlog_format%'
1、查看
mysqlbinlog [参数选项] logfilename
-d 指定数据库名称，只列出指定的数据库相关的操作
-o 忽略掉日志中的前n行命令
-v 将行事件（数据变更）重构为sql语句
-w 将行事件（数据变更）重构为sql语句，并输出注释信息
2、删除
| 指令 | 含义 |
|---|---|
| reset master | 删除全部binlog日志，删除之后，日志编号将从binlog.000001重新开始 |
| purge master logs to 'binlog.xxxx' | 删除xxxxxx编号之前的所有日志 |
| purge master log befor 'yyyy-mm-dd hh24:mi:ss' | 删除日志为'yyyy-mm-dd hh24:mi:ss'之前产生的所有日志 |
二进制日志的过期时间
show variables like '%binlog_expire_logs_seconds%'
查询日志
记录了所有操作语句
show variables like '%general_log%'
慢查询日志
慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的sql日志
show_query_time = 1 慢查询日志
long_query_time = 1 执行时间参数
默认情况下，不会记录管理语句，也不会记录不适用索引进行查找的查询，可以使用log_slow_admin_statements和更改此行为log_queries_not_using_indexes
log_slow_admin_statements = 1 记录执行较慢的管理语句
log_queries_not_using_indexes = 1 记录执行较慢的使用索引的语句
问一：测试环境mysql一直重启
在一个测试环境，mysql5.7部署在centos7.4的系统上，测试人员用LoadRunner进行应用的一个压测测试，压测并发为128，启动LoadRunner过几秒，LoadRunner日志就报错，显示mysql服务断开了，过几秒又恢复，然后又断开，异常诡异。
排查定位
于是就登录到服务器，看一下数据库服务器的内存，内存已经用完了，紧接着就看centos的系统日志（/var/log/messages），在这个日志里发现mysql服务因为OOM，导致进程被系统给kill掉了，然后因为mysql服务有守护进程存在，又被自动启动了。
一个经验丰富的mysql数据库运维人员，应该很快就知道什么原因导致OOM了。 第一：mysql数据库的buffer pool内存参数配置不合理 第二：mysql数据库的session初始化内存参数配置不合理 第三：数据库连接总数配置不合理
mysql数据库占用内存主要有2大块
第一：buffer pool占用，
第二：初始化连接占用的内存
在这里设置mysql的buffer pool为1500M，会话的参数设置如下
read_buffer_size = 32
Mread_rnd_buffer_size = 32M
sort_buffer_size = 32
Mtmp_table_size = 32
Mmax_heap_table_size=32
Mjoin_buffer_size=32M
buffer pool是什么？
-
是一块内存区域，当数据库操作数据的时候，把硬盘上的数据加载到buffer pool，不直接和硬盘打交道，操作的是buffer pool里面的数据
-
数据库的增删改查都是在buffer pool上进行，和undo log/redo log/redo log buffer/binlog一起使用，后续会把数据刷到硬盘上
-
默认大小 128M
mysql的buffer pool使用的算法的空间大小配置：innodb_buffer_pool_size
-
innodb_buffer_pool_size参数是控制buffer pool缓冲池的大小，一般建议大一点！；
-
查看当前的buffer pool大小：show global variables like ‘innodb_buffer_pool_size’;
-
修改当前的buffer pool大小（2G）：SET GLOBAL innodb_buffer_pool_size= 2147483648；
-
数据库只要一启动，就会按照你设置的Buffer Pool大小，稍微再加大一点，去找操作系统申请一块内存区域，作为Buffer Pool的内存区域。
-
当内存区域申请完毕之后，数据库就会按照默认的缓存页的16KB的大小以及对应的800个字节左右的描述数据的大小，在Buffer Pool中划分出来一个一个的缓存页和一个一个的他们对应的描述数据
在配置mysql参数，一定要考虑以下3个因素 1.业务连接预期总数 2.会话初始化内存 3.buffer pool缓冲器大小
下面先用sql查询一下会话的内存总大小和数据库buffer pool
mysql> select (@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@key_buffer_size) / 1024 /1024 AS MEMORY_MB;+--------------+| MEMORY_MB |+--------------+| 584.00000000 |+--------------+1 row in set (0.00 sec)
mysql> select (@@read_buffer_size+@@read_rnd_buffer_size+@@sort_buffer_size+@@tmp_table_size+@@join_buffer_size+@@binlog_cache_size)/1024/1024 as MB;+--------------+| MB |+--------------+| 160.03125000 |+--------------+1 row in set (0.00 sec)
建议
mysql的buffer pool加上会话内存总大小不超过服务器内存总大小的80%。
