事务
四个特性:
**原子性**:
一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。 。事务在执行过程中发生错误,会被恢复(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
**一致性**:
事务开始前和结束后,数据库的完整性约束没有被破坏。比如A向B转账,不可能A扣了钱,B却没收到。
**隔离性**:
隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔 离。
**持久性**:
指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
事务并发带来的问题
脏读:一个事务读到另外一个事务还没有提交的数据。
不可重复读 : 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读
幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了幻影。
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
READ-UNCOMMITTED 未提交读 | 可 | 可 | 可 |
READ-COMMITTED 提交读 | 不可 | 可 | 可 |
REPEATABLE-READ 重复读 | 不 可 | 不可 | 可 |
SERIALIZABLE 可串行化读 | 不可 | 不 可 | 不可 |
MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读) | |||
查看事务隔离级别的语句: |
select @@transaction_isolation;
设置事务隔离级别:
set [session|global] transaction isolation level {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE};
存储引擎
MySQL体系结构
连接层:
服务层:
引擎层:
索引是在存储引擎层实现的;可插拔存储引擎
存储层:
存储引擎默认时是InnoDB
1.创建表的时候,指定存储引擎
create table name(
字段1 字段1类型,
...
)engine=InnoDb; //默认的引擎
2.查看有哪些存储引擎
show engines;
InnoDB
是一种兼顾高可靠性和高性能的通用存储引擎。
DML操作遵循ACID模型,支持事务;原子性,一致性,隔离性,永久性
行级锁,提高并发访问性能;
支持外键 FOREIGN KEY约束,保证数据的完整性和正确性;
存储空间:表空间、段、区、页、行
MyISAM
早期的默认引擎
不支持事务,不支持外键,
支持表锁,不支持行锁
访问速度快
Memory
Memory引擎的表数据是存储在内存中的,由于受到硬件问题或断电问题的影响,只能将这些表作为临时表或缓存使用。无法保证数据的安全性
内存存放,HASH索引(默认)
索引
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
优势 | 劣势 |
---|---|
提高数据检索的效率,降低数据库的IO成本 | 索引列也是要占用空间的 |
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗 | 索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE时,效率降低 |
创建索引
create index 索引名 on 表名(字段名);
索引结构
B+Tree结构
Hash结构
索引分类
主键索引、唯一索引、常规索引、全文索引、聚集索引、二级索引
索引语法
create [unique] index XXX on xxx(xxx); // 创建索引
show index from xxx; //显示表中的索引
drop indexxxx on xxx; // 删除索引
SQL性能分析
执行频次
慢查询日志
profile
explain 查看sql语句的执行计划
索引使用
联合索引
最左前缀法则 ,最左边的列必须存在
索引失效
- 函数运算
- 字符串不加引号
- 模糊匹配:%xxx;
- mysql自动评估,如果全盘扫描更快,mysql会自动不适用索引
SQL提示
如果有很多索引,mysql会自动选择索引,如果想要认为的选择使用哪一个索引,就可以使用SQL提示
use
ignore
force
覆盖索引
前缀索引
字符串长度较长的情况用
单列/联合索引
索引设计原则
SQL优化
插入数据
inser优化
- 批量插入
insert into tb_test values(1,'Tom'),(2,'Jack'),(3,'Cat');
- 手动提交事务
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(6,'Tom'),(8,'Cat'),(9,'Jerry');
commit;
- 主键顺序插入
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
主键顺序插入的效率高于主键乱序插入
- 大批量插入数据
如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入。