简单概述一下事务和索引

事务（transaction）

事务特性：ACID（原子性，一致性，隔离性，持久性）

A:原子性
- 要么成功要么不成功
C:一致性
- 最终一致性，保证最终结果一致
I:隔离性
- 一个事务的提交不会影响下一个事务。
- 隔离产生的问题
  - 脏读：一个事务读取了另一个事务未提交的数据。
  - 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。
  - 幻读：指在一个事务内读取到别的事务插入的数据，导致前后读取不一致。
D:持久性
- 表示事务结束后的数不会随着外界原因导致数据丢失
- 也就是说，事务没有提交，那么就恢复到原来的未提交状态，如果提交，那么就更新为提交后的状态，持久化到数据，事务一旦提交就不可逆。

 -- 手动开启事务
    start transaction -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
    
    insert xxx
    insert xxx
    
    -- 提交 持久化
    commit ;
    -- 如果提交失败就回滚到原来的样子
    rollback;
    
    -- 事务结束
    set autocommit = 1 ；-- 开启自动提交
    
    savepoint 保存点名 -- 设置一个事务的保持点
    rollback to savepoint 保存点名  -- 回滚到保存点
    release  savepoint 保存点名 -- 撤销保存点

模拟场景

-- 转账
create database shop character set utf8 collate utf_8_general_cli;
use shop;
create table account(
	id int (3) not null primary key auto_increment,
    name varchar(20) not null，
    money decimal(9,2) not null
) engine = innodb default charset=utf8;

-- 插入数据
insert into account(name,money) 
value('A',1024.00),('B',2048.00)

-- 模拟转账：事务
set autocommit = 0; -- 关闭自动提交
start transaction ; -- 开启事务
update account set money - 500 where name = 'A';
update account set money + 500 where name = 'B';

commit ; -- 提交事务
rollback ; -- 回滚
set autocommit = 1; -- 恢复默认值

索引

是帮助mysql高效获取数据的数据结构，提取句子主干，可以得到索引的本质，索引是数据结构。

索引分类：

主键索引：创建主键后会自动建立索引
1. 唯一标识，主键不可重复，只能有一个主键

  #随表一起建索引：
      CREATE TABLE customer (
          id INT(10) UNSIGNED  AUTO_INCREMENT ,
          customer_no VARCHAR(200),
          customer_name VARCHAR(200),
          PRIMARY KEY(id) 
      );
      #使用AUTO_INCREMENT关键字的列必须有索引(只要有索引就行)。
      CREATE TABLE customer2 (
          id INT(10) UNSIGNED,
          customer_no VARCHAR(200),
          customer_name VARCHAR(200),
       	PRIMARY KEY(id) 
      );
      #单独建主键索引：
      ALTER TABLE customer 
      add PRIMARY KEY 
      customer(customer_no);  
      #删除建主键索引：
      ALTER TABLE customer
      drop PRIMARY KEY ;  
      #修改建主键索引：
      #必须先删除掉(drop)原索引，再新建(add)索引

唯一索引:索引列的值必须唯一，但允许有空值
1. 避免重复的列出现，唯一索引可以重复，多个列都可以标识为，唯一索引。

#随表一起建索引：
      CREATE TABLE customer (
          id INT(10) UNSIGNED  AUTO_INCREMENT ,
          customer_no VARCHAR(200),
          customer_name VARCHAR(200),
       	 PRIMARY KEY(id),
       	 KEY (customer_name),
       	 UNIQUE (customer_no)
      );
      #建立 唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。   
      #单独建唯一索引：
      CREATE UNIQUE INDEX 
      idx_customer_no 
      ON customer(customer_no); 
      #删除索引：
      DROP INDEX idx_customer_no
      on customer ;

常规索引
1. 默认的，index，key关键字来设置
2. 单列索引：一个索引只包含单个列，一个表可以有多个单列索引

   #随表一起建索引：
      CREATE TABLE customer (
          id INT(10) UNSIGNED  AUTO_INCREMENT ,
          customer_no VARCHAR(200),
          customer_name VARCHAR(200),
       	 PRIMARY KEY(id),
        	KEY (customer_name)  
      );
      #随表一起建立的索引 索引名同 列名(customer_name)
      #单独建单值索引：
      CREATE INDEX idx_customer_name 
      ON customer(customer_name); 
      #删除索引：
      DROP INDEX idx_customer_name ;

复合索引：一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销更小（对于相同的多个列建索引）

 #随表一起建索引：
  CREATE TABLE customer (
      id INT(10) UNSIGNED  AUTO_INCREMENT ,
      customer_no VARCHAR(200),
      customer_name VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name),
    UNIQUE (customer_name),
    KEY (customer_no,customer_name)
  );
  #单独建索引：
  CREATE INDEX idx_no_name
  ON customer(customer_no,customer_name); 
  #删除索引：
  DROP INDEX idx_no_name  on customer ;

全文索引
1. 在特定的数据库引擎下才有的，myIsam
2. 快速定位数据

-- 索引的使用
  -- 在创建表的时候给字段增加索引
  -- 创建完毕后，增加索引
  
  -- 显示所有的索引信息
  show index from student;
  
  -- 增加一个全文索引（索引名） 列名
  alter table school.student
  add fulltext 
  index studentName(studentName);
  
  -- explain 分析mysql执行的状况
  explain select * from student ; -- 非全文索引
  
  select * from student
  where match(studentName) against('刘')；

聚集索引和非聚集索引
1. 聚集索引：指索引项的排序方式和表中数据记录排序方式一致的索引。
2. 非聚集索引：与聚集索引相反，索引顺序与物理存储顺序不一致

索引测试

-- 我们在创建索引的时候一般使用  primary  key (列名)事实上这就是设置主键，但是这个也就是创建了唯一索引
create table if not exists yyl(
	id int(10) not null primary key auto_increment comment '主键',
    name varchar(20) default null comment  '姓名',
    pwd varchar(50) not null default '123321' comment '密码',
    age int (10) not null default '0' comment '年龄'
)engine = innodb default charset = utf8;

-- 插入100万数据
delimiter $$ -- 写数据之前必须要写，标志
create function mock_data()
returns int
begin
	declare num int default 1000000;
	declare i int default 0;
	while i < num do
	-- 插入语句
		
		insert into yyl (name,pwd,age) 
		value(concat('用户',i),UUID(),floor(rand()*100));
		set i = i + 1;
	end while;
	return i;
end;

-- 执行一下
select mock_data();

/*
select mock_data();
受影响的行: 0
时间: 42.708ms

*/

-- 插入表数据
insert into yyl (name,pwd,age) 
value(concat('用户',i),UUID(),floor(rand()*100));

-- 由于插入了1000000条数据，那么查询一个值
select * from yyl where name = '用户99999';
/*
没创建索引的时候去查询数据
[SQL] select * from yyl where name = '用户99999';
受影响的行: 0
时间: 0.744ms
我们发现时间比较长
*/
explain select * from yyl where name = '用户99999'; -- rows 1000539次

-- 创建索引 id_表名_字段名 on 索引名 on 表（字段）
create index id_yyl_name on yyl(name);

-- 删除索引
drop index id_yyl_name ;
/*
创建索引后再去查询数据非常快
[SQL] select * from yyl where name = '用户99999'; 
受影响的行: 0
时间: 0.009ms
*/
-- 此时explain   rows =1;
explain select * from yyl where name = '用户99999';

创建索引

create index id _ 表名_ 字段名 on 索引名 表(字段)；

索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显。

索引原则

索引不是越多越好

不要对进程变动数据加索引

小数据量的表不需要加索引

索引一般加在常常用来查询的字段上！

索引的数据结构：

Hash 类型的索引

B+树：innodb的默认数据结构

讨论为啥Innodb要使用B+树而不使用其他数据结构呢？

B-树：（不是二分查找，却相似于二分查找）
B-树是一种多路自平衡的搜索树，它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

存在问题：

每个节点中有key，也有data，而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小。
当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率
B+树：

B+Tree是在B-Tree基础上的一种优化，InnoDB存储引擎就是用B+Tree实现其索引结构。

它带来的变化点：

B+树每个节点可以包含更多的节点，这样做有两个原因，一个是降低树的高度。另外一个是将数据范围变为多个区间，区间越多，数据检索越快

非叶子节点存储key，叶子节点存储key和数据叶子节点两两指针相互链接（符合磁盘的预读特性），顺序查询性能更高

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。

因此可以对B+Tree进行两种查找运算：

一种是对于主键的范围查找的分页查找。

另一种是从根节点开始，进行随机查找。