事务，MySQL函数和索引详解

事务简介

是一个不可分割的逻辑单元，可以在一个事务中书写多行SQL语句，这些SQL语句会做一个整体，要么同时成功，要么同时失败
MySQL数据库默认：一行SQL语句就会自动提交(表中数据持久更新)

提交方式

1. 手动提交事务：先开启，再提交
2. 自动提交事务(默认的)：在执行一条sql语句时自动开启及提交一次事务 mysql是默认
   

手动提交事务

开启事务：start transaction
提交事务：commit
回滚事务：rollback

事务执行流程

修改事务的默认提交方式

查看事务的默认提交方式
select @@autocommit;
0代表手动提交，1代表自动提交
修改默认提交方式
set @@autocommit = 0;
注意：如果修改了手动提交，增删改之后需要commit

事务原理

四大特性

1. 原子性 A

原子是不可分割的最小操作单位，要么同时成功，要么同时失败

2. 一致性 C

事务操作前后，数据总量不变

3. 隔离性 I

多个用户并发访问数据库，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离

4. 持久性 D

当事务提交或回滚后，数据库会持久化的保存数据

隔离级别

MySQL函数

常见的日期函数

判断函数case when

格式一

格式二

通常和select语句一起搭配使用

字符串函数

数字函数

MySQL性能(了解)

索引

概念

就是帮助MySQL高效获取数据的数据结构
本质：是数据结构(B+Tree)
从1开始

分类

1. 主键索引

主键约束(非空+唯一)+提高查询效率

2. 唯一索引

唯一约束+提高效率

3. 普通索引

仅提高效率

4. 组合索引

多个字段组成索引

MySQL索引语法

1. 创建索引

create index 索引名 on 表名 (字段)； 创建普通索引
create unique index 索引名 on 表名(字段)；唯一索引
create index 索引名 on 表名(字段1,字段2,…); 普通组合
create unique index 索引名 on 表名(字段1,字段2,…); 唯一组合索引
注意：
如果在同一张表中创建多个索引，要保证索引名不能重复

2. 在已有表的字段上修改表时指定

添加一个主键，索引值唯一非null，默认索引名primary
alter table 表名 add primary key (字段);
添加唯一索引，(null出现多次)，默认索引名为字段名
alter table 表名 add unique (字段);
添加普通索引，默认索引名为字段名
alter table 表名 add index (字段);

3. 创建表时指定

create table student{
id int primary key auto_increment, – 主键索引
name varchar(20),
telephone varchar(11) unique, – 唯一索引
birthday date,
index (name) – 普通索引
};

数据结构(了解)

B+Tree

B+Tree好处

优缺点

优势

1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。IO次数越多，效率越低。
2） 索引底层就是排序，通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

• 在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
• 在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间。
• 在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦。
  

创建原则

1. 字段内容可识别度不能低于70%，字段内数据唯一值的个数不能低于70%
   例如：一个表数据只有50行，那么性别和年龄哪个字段适合创建索引，明显是年龄，因为年龄的唯一值个数比较多，性别只有两个选项 。性别的识别度是50%。
   2. 经常使用where条件搜索的字段，例如user表的id name等字段。
   3. 经常使用表连接的字段（内连接、外连接），可以加快连接的速度。
   4. 经常排序的字段 order by，因为索引已经是排过序的，这样一来可以利用索引的排序，加快排序查 询速度。
   注意: 那是不是在数据库表字段中尽量多建索引呢?
   肯定是不是的。因为索引的建立和维护都是需要耗时的 创建表时需要通过数据库去维护索引，添加记录、更新、修改时，也需要更新索引，会间接影响数据库的效率。