一、执行引擎
1、查询设置引擎
-- 查询当前数据库支持的存储引擎:默认的执行引擎是innoDB 支持事务,行级锁定和外键
show engines;
-- 查看当前的默认存储引擎:
show variables like ‘%storage_engine%’;
-- 查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
show create table student;
-- 创建新表时指定存储引擎:
create table(...) engine=MyISAM;
-- 修改数据库引擎
alter table student engine = INNODB;
alter table student engine = MyISAM;
永久修改MySQL默认存储引擎方法
- 关闭mysql服务
- 找到mysql安装目录下的my.ini文件:
- 找到default-storage-engine=INNODB 改为目标引擎。如:default-storage-engine=MYISAM
- 启动mysql服务
2、功能
功能 | MylSAM | MylSAM | lnnoDB |
---|---|---|---|
存储限制 | 256TB | RAM | 64TB |
支持事务 | No | No | Yes |
支持全文索引 | Yes | No | No |
支持B树索引 | Yes | Yes | Yes |
支持哈希索引 | No | Yes | No |
支持集群索引 | No | No | Yes |
支持数据索引 | No | Yes | Yes |
支持数据压缩 | Yes | No | No |
空间使用率 | 低 | N/A | 高 |
支持外键 | No | No | Yes |
InnoDB 与 MyISAM 的最大不同有两点:一是支持事务;二是 采用了行级锁。
二、事务
1、理解事务
- 在MySQL中的事务(Transaction)是由存储引擎实现的,在MySQL中,只有InnoDB存储引擎才支持事务。
- 事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。
- 事务用来管理 DDL、DML、DCL 操作,比如 insert,update,delete 语句,默认是自动提交的。
在银行转账时,必须保证转账绝对安全,这时需要事务参与,示例如下:
update account set money = money - 200 where id = 1;
update account set money = money + 200 where id = 2;
-- 假如在第一次update之后,出现了意外、异常,没有执行第二次update,这时转账是否会出现异常?
-- 所以这两行命令要么全部执行,要么全部不执行
2、事务操作
正常SQL操作其实也有事务,只是MySQL自动帮我们完成的,每执行一条SQL时MySQL就帮我们自动提交事务,因此如果想要手动控制事务,则必须关闭MySQL的事务自动提交。
在MySQL中直接用 SET 来改变 MySQL 的自动提交模式:
set autocommit=0 -- 禁止自动提交
set autocommit=1 -- 开启自动提交
样例:
-- 创建账户表
create table account(
id int primary key, -- 账户id
name varchar(20), -- 账户名
money double -- 金额
);
-- 插入数据
insert into account values(1,'zhangsan',1000);
insert into account values(2,'lisi',1000);
-- 设置MySQL的事务为手动提交(关闭自动提交)
select @@autocommit; -- 查看
set autocommit = 0;
-- 模拟账户转账
-- 开启事务
begin;
update account set money = money - 200 where name = 'zhangsan';
update account set money = money + 200 where name = 'lisi';
-- 提交事务
commit;
-- 回滚事务
rollback; -- 没提交事务之前将内存中的数据还原
3、事务的特性
4、事务的隔离级别
Isolate,顾名思义就是将事务与另一个事务隔离开,为什么要隔离呢?如果一个事务正在操作的数据被另一个事务修改或删除了,最后的执行结果可能无法达到预期。如果没有隔离性还会导致其他问题。
-- 设置read uncommitted
set session transaction isolation level read uncommitted;
-- 这种隔离级别会引起脏读,A事务读取到B事务没有提交的数据
-- 设置read committed
set session transaction isolation level read committed;
-- 这种隔离级别会引起不可重复读,A事务在没有提交事务之前,可看到数据不一致
-- 设置repeatable read (MySQ默认的)
set session transaction isolation level repeatable read;
-- 这种隔离级别会引起幻读,A事务在提交之前和提交之后看到的数据不一致
-- 设置serializable
set session transaction isolation level serializable;
-- 这种隔离级别比较安全,但是效率低,A事务操作表时,表会被锁起,B事务不能操作。
三、锁
1、概述
锁类型 | 特点 |
---|---|
表级锁 | 偏向Mylsam存储引擎,开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低. |
行级锁 | 偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低, 并发度也最高 |
表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web 应用;
行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并查询的应用,如一些在线事务处理(OLTP)系统。
2、MyISAM 表锁
MyISAM 存储引擎只支持表锁
MyISAM 在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT 等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。
lock table table_name read; -- 加读锁
lock table table_name write; -- 加写锁
特点:
- 对MyISAM 表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
- 对MyISAM 表的写操作,则会阻塞其他用户对同一表的读和写操作;
3、InnoDB行锁
行锁特点: 偏向InnoDB 存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
行锁模式:
InnoDB 实现了以下两种类型的行锁。
- 共享锁(S):又称为读锁,简称S锁,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。
- 排他锁(X):又称为写锁,简称X锁,排他锁就是不能与其他锁并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行读取和修改。
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);
对于普通SELECT语句,InnoDB不会加任何锁;
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE -- 共享锁(S)
SELECT * FROM table_name WHERE ... FOR UPDATE -- 排他锁(X)
四、日志
1、binlog日志
-- 查看错误日志位置指令
show variables like 'log_error%';
2、二进制日志(binlog)
-- 查看MySQL是否开启了binlog日志
show variables like 'log_bin';
-- 查看binlog日志的格式
show variables like 'binlog_format';
-- 查看所有日志
show binlog events;
-- 查看最新的日志
show master status;
-- 查询指定的binlog日志
show binlog events in 'binlog.000010';
-- 从指定的位置开始,查看指定的Binlog日志
show binlog events in 'binlog.000010' from 156;
-- 从指定的位置开始,查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from 156 limit 2;
--从指定的位置开始,带有偏移,查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from 666 limit 1, 2;
-- 清空所有的 binlog 日志文件
reset master
3、查询日志
-- 查看MySQL是否开启了查询日志
show variables like 'general_log';
-- 开启查询日志
set global general_log = 1;
4、慢日志查询
-- 查看慢查询日志是否开启
show variables like 'slow_query_log%';
-- 开启慢查询日志
set global slow_query_log = 1;
-- 查看慢查询的超时时间
show variables like 'long_query_time%';
select sleep(12);