MySQL笔记

事务

四个特性：

**原子性**：
    一个事务（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语句的执行计划

索引使用

联合索引
最左前缀法则 ，最左边的列必须存在
索引失效

1. 函数运算
2. 字符串不加引号
3. 模糊匹配：%xxx;
4. mysql自动评估，如果全盘扫描更快，mysql会自动不适用索引

SQL提示
如果有很多索引，mysql会自动选择索引，如果想要认为的选择使用哪一个索引，就可以使用SQL提示
use
ignore
force

覆盖索引
前缀索引
字符串长度较长的情况用
单列/联合索引

索引设计原则

SQL优化

插入数据

inser优化

1. 批量插入

insert into tb_test values(1,'Tom'),(2,'Jack'),(3,'Cat');

2. 手动提交事务

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;

3. 主键顺序插入

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
主键顺序插入的效率高于主键乱序插入

4. 大批量插入数据
   如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

主键优化

order by 优化

group by 优化

limit 优化

count 优化

update 优化

视图

锁

InnoDB 引擎

MySQL管理