Mysql高级

事务

事务会把所有操作作为一个整体一起向系统提交或撤销操作星球 这些操作要么同时成功要么同时失败

事务操作

查看 设置事务

SELECT @@autocommit;

SET @@AUTOCOMMIT=0 设置为手动提交

提交事务COMMIT

回滚事务 ROLLBACK

开启事务 start transction或 begin

事务正常运行 提交事务 commit

事务中途失败 回滚事务 rollback

事务四大特性

  • 原子性 事务是不可分割的最小单元 要么全部成功 要么全部失败
  • 一致性 事务完成时 必须使所有数据都保持一致
  • 隔离性 数据库提供隔离机制 保证事务在不收外部并发操作影响的独立环境
  • 持久性 事务一旦提交或回滚 数据库中数据改变持久性

并发事务问题

赃读 一个事务读取到另一个事务还没有提交的数据 （隔离解决方法：A事务并不会读取到）

不可重复读 一个事务先后读取同一条记录 但两次读取的数据不同 （隔离解决方法：A事务读取到的数据相同 并没有受到改变）

幻读 一个事务按照条件查询数据时 没有对应的数据行 但是在插入数据时 又发现了这条数据的存在（隔离解决方法：B事务阻塞无法无法新插入要查询的数据）

事务隔离级别

隔离级别 赃读 不可重复读 幻读
read uncommitted 读未提交 √ √ √
read committed 读已提交（orical数据库默认） × √ √
repeatable read（mysql中默认） 可重复读 × × √
serializable 串行化 × × ×

查看事务隔离级别 SELECT @@transacyion_isolation;

设置事务隔离级别

set [session | global] transaction isolation level { 四种级别 };

锁

计算机协调多个进程或线程并发访问某一种资源的机制

粒度

  • 全局锁 锁住数据库中所有表
  • 表级锁 锁住整张表
  • 行级锁 锁住对应行数

全局锁

锁后 只能进行读取 全库的数据备份 从而获取一致性视图

全局锁 flush tables with read lock;

备份 mysqldump -u root -p 12345 database_name > xxx.sql;(在windows命令行执行)

解锁 unlock tables ;

在innodb引擎中 备份添加参数 --sing-transaction完成不加锁的一致性备份

表级锁

每次操作锁住整张表

  • 表锁
  • 元数据锁
  • 意向锁

表锁

  • 表共享读锁 （读锁）所用客户端只能读
  • 表独占写锁 （写锁）其他客户端读写都被阻塞

加锁 lock tables table_name read/write

释放锁 unlock tables

元数据锁

系统自动控制加锁 维护表元数据的数据一致性 在表上有活动事务的时候 不可以对元数据进行写入操作

当对表进行增删改查的时候 自动加共享锁

​ 增删改 自动加写锁

​ 查看 自动加读锁

当对表结构进行变更的时候 自动加排他写锁

排他锁与其他所有锁都互斥

意向锁

解决在执行dml语句中 加的行锁与表锁的冲突 在innodb引擎加入了意向锁 使得表锁不用检查每行数据是否加锁 使用意向锁来减少表锁的检查

  • 意向共享锁（IS） select…lock in share mode（添加行锁共享锁同时添加意向共享锁）
  • 意向排他锁 （IX）insert update delete select…for update

兼容问题

意向共享锁：与表锁共享锁（read）兼容 与表锁排他锁（write）互斥

意向排他锁：与表锁共享锁（read）以及排他锁（write）都互斥 意向锁之间不会互斥

行级锁

锁住对应数据行的数据

行锁是通过对索引上的索引行加锁来实现的而不是对记录加的锁

  • 行锁 锁定单行数据 防止其他事务对此进行update和delete
  • 间隙锁 锁定索引记录的间隙（不含该记录）确保索引记录间隙不变 防止其他事务在这个间隙进行insert 产生幻读 在RR隔离级别下都支持
  • 临建锁 行锁和间隙锁组合 同时锁住数据并锁住数据前面的间隙

行锁

  • 共享锁（S）允许一个事务读取一行 阻止其他事务获得相同数据集的排他锁
  • 排他锁（X）允许获取排他锁的事务更新数据 阻止其他事务获取相同数据集的共享锁和排他锁
sql 行锁类型 说明
insert 排他锁 自动
update 排他锁 自动
delete 排他锁 自动
select 不加锁
select..lock in share mode 共享锁 手动
select..for update 排他锁 手动

间隙锁

默认情况下 innodb在repeatable read事务隔离级别运行 innodb使用next-key进行搜索和索引扫描 以防止幻读

  1. 索引上的等值查询（唯一索引）给不存在的记录加锁时 优化为间隙锁
  2. 索引上的等值查询（普通索引） 向右遍历时最后一个值不满足查询需求时 临建锁退化为间隙锁
  3. 索引上的范围查询（唯一索引）会访问到不满足条件的第一个值为止

间隙锁唯一目的是防止其他事务插入间隙 间隙锁可以共存 一个事务采用的间隙锁不会组织两一个事务在同一个间隙上采用间隙锁

MySQL管理

系统数据库

mysql

存储正常运行的各种信息

information_schema

元数据体系 数据库 表 字段类型及访问权限

performance_schema

为服务器运行时提供了一个底层监控的功能 主要用于收集服务器性能参数

sys

包含了一系列方便dba和开发人员利用performance_schema 性能数据库进行性能调优和诊断

常用工具

-u 指定用户名
-p 指定密码
-h 指定服务器ip或域名
-P 指定连接端口号
-e 执行sql语句并退出（可以在客户端执行sql语句 而不用连接数据库）

mysqladmin

执行管理操作的客户端程序 可以用它检查服务器的配置和当前状态 创建并删除数据库等

不指定主机名和端口号 默认本主机3306

mysqlbinlog

服务器生成的二进制文件 需要用到mysqlbinlog管理工具

mysqlbinlog [options] log-files...

-d 指定数据库名称
-o 忽略日志中前n行的命令
-r 将输出的文本格式日志输出到指定文件
-s 显示简单个事 省略一些信息

SQL优化

插入数据

insert优化

批量插入

insert into table-name values(1,'tom),(2,'cat)...;

手动事务提交

start transction;

commit

主键顺序插入

大批量数据插入

load指令

  1. 客户端连接服务端时 添加参数 --local-infile
  2. 设置全局参数 set global local_infile=1
  3. 执行load指令 将准备好的数据加载到表结构中去 load data local infile 'filename.log' into table 'table_name' fields terminated by ',' lines terminated by '\n';

主键优化

主键设置原则

  • 满足业务需求的情况下 尽量降低主键的长度
  • 插入数据时 尽量选择顺序插入 使用AUTO_INCREMENT自增主键
  • 尽量不要使用UUID做主键或者是其他自然主键 如身份证号
  • 业务操作时避免对主键的修改

order by优化

  1. using filesort：通过表的索引或全表扫描 读取满足条件的数据行 然后再排序缓冲区sort buffer中完成排序操作 所有不是通过索引直接返回排序结果的牌徐都叫 filesort排序
  2. using idex 通过有序索引顺序扫描直接返回有序数据 这种情况即为using index 不需要额外排序 操作效率高

需要为排序的字段创建索引

create index index_name on table_name(字段1,字段2)

注意创建字段索引的顺序要与排顺时字段顺序相同 （最左前缀法则）

默认升序排序 asc 降序排序 desc

创建字段索引时可以指定升序降序

如果全部字段遵循一致的排列顺序 则不需要额外指定字段排序

group by优化

出现 using temporary 临时表 效率极低

创建联合索引 注意最左前缀法则

where 先出现group by后出现 依旧可以满足最左前缀法则

limit优化

分页查询

大数据量分页 越往后 耗时越长

优化 覆盖索引 子查询

//例子
select * from table_name limit 9000000,10;
//优化
select *from table_name where id in(select id from table_name order by id limit 9000000,10);
//或者
select name1.* from table_name1 name1,(select * from table_name order by id limit 9000000,10) name2 where name1.id=name2.id;

count 优化

myisam 引擎会把一个表的总行数存在磁盘上 执行count *效率高

innodb 执行count* 时 需要把数据一行一行地从引擎里读出来 然后累计计数

优化思路：自己计数 自己维护

count几种方式

  • count 主键

    • innodb会遍历整张表 每一行的主键提取出来 返回给服务层 服务层进行累加

  • count 字段

    • 没有not null 约束 遍历整张表的每一行的字段返回给服务层 判断是否为null
    • 有约束 每一行提取出来返回给服务层 直接按行累加

  • count *

    • 不取值直接累加

  • count 1

    • 遍历整张表 不取值 放数字进去后直接按行进行累加

count *效率最高 count 1 count主键 count字段

update 优化

update 条件中没有使用索引的话 会锁住整张表

innodb是针对索引加的锁 不是针对记录加锁 并且索引不能失效 否则行锁升级为表锁