mysql学习07（ 事务，索引 ）

mysql学习07

• 事务ACID原则，脏读，不可重复读，幻读

  • 什么是事务：

    • 核心：将一组SQL放在一个批次中去执行；

    • 要么都成功，要么都失败；

• InnoDB：支持事务；

 

• 事务原则：ACID

  • 原子性（Atomicity）：要么都成功，要么都失败

    • 原子性是指事物是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生；

  • 一致性（Consistency）：事务前后的数据的完整性必须保持一致

    • 事务前后的数据的完整性必须保持一致；

  • 隔离性（Isolation）：互不干扰

    • 事务的隔离性是多个用户并发访问数据库时，数据库为每个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要互相隔离；

  • 持久性（Durability）：事务一旦提交则不可逆，被持久化到数据库中；

    • 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响；

 

• 隔离所导致的一些问题：

  • 脏读

  • 不可重复读；

  • 幻读；

 

• 事务的隔离级别：

  • 脏读：指一个事务读取了另外一个事务未提交的数据；

  • 不可重复读：在一个事务内，读取表中的某一行数据，多次读取结果不同（这个不一定是错误，只是某些场合不对）

  • 幻读：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致；（一般是行影响，多了一行）

 

 

• 执行事务：

  • mysql是默认开启事务自动提交的；

    -- mysql是默认开启事务自动提交的
    SET autocommit=0 ; -- 0关闭；1开启(默认)
    ​
    --事务的完整流程：
    ​
    -- 1，手动处理事务
    SET autocommit=0; -- 关闭自动提交；
    -- 2，事务开启
    START TRANSACTION   -- 标记一个事务的开始，从这个之后的SQL都在同一个事务内
    -- 3，
    INSERT INTO 
    ​
    -- 4，提交：持久化(成功)
    COMMIT
    -- 5，回滚：回到到原来的样子(失败)
    ROLLBACK
    -- 6，事务结束
    SET autocommit=1 ; -- 开启自动提交
    ​
    ​
    ​
    SAVEPOINT  -- 设置一个事务的保存点    
    ROLLBACK TO SAVEPOINT -- 回滚到保存点
    RELEASE SAVEPOINT -- 撤销保存点
    ​
    ​

  • 模拟场景：转账

    ​
    CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
    ​
    USE shop ;
    ​
    CREATE TABLE `account`(
    `id` INT(3) NOT NULL AUTO_INCREMENT,
    `name`  VARCHAR(30) NOT NULL,
    `money` DECIMAL (9,2) NOT NULL,
    PRIMARY KEY(`id`)
    ​
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
    ​
    ​
    INSERT INTO ACCOUNT (`name`,`money`) VALUES('A',2000.00),('B',10000.00);
    ​
    ​
    -- 模拟转账：事务
    ​
    SET autocommit=0; -- 关闭自动提交
    ​
    START TRANSACTION -- 开启事务
    ​
    UPDATE ACCOUNT SET money=money-500 WHERE `name`='A';-- A减500
    UPDATE ACCOUNT SET money=money+500 WHERE `name`='B';-- B加500
    ​
    ​
    COMMIT ; -- 提交事务：一旦提交就被持续化了
    ​
    ROLLBACK ; -- 回滚
    ​
    SET autocommit=1; -- 开启自动提交
    ​
    ​

     

 

• 索引介绍及索引的分类：

  • 索引：

    • mysql官方对索引的定义为：索引(index)是帮助MYSQL高效获取数据的数据结构；

    • 提取句子主干，就可以得到索引的本质：索引就是数据结构；

    • 在一个表中，主键索引只能有一个，但是唯一索引可以有多个；

  • 索引分类：

    • 主键索引（PRIMARY KEY ）：唯一的标识，主键不可重复，只能有一个列作为主键；

    • 唯一索引（UNIQUE KEY）：避免重复的列出现；唯一索引可以重复，多个列都可以标识为唯一索引

    • 常规索引（KEY / index）：默认的，index , key来设置；

    • 全文索引（FullText）：在特定的数据库引擎下才有，MYISAM，快速定位数据；

  • 索引的使用：

    • 在创建表的时候，给字段增加索引；

    • 创建完毕后，增加索引；

      -- 显示所有的索引信息
      SHOW INDEX FROM student ;
      -- 增加一个索引：索引名  列名
      ALTER TABLE student ADD FULLTEXT INDEX `studentName` (`studentName` );
      ​
      -- explain：分析SQL执行的状况
      EXPLAIN SELECT * FROM student ; -- 常规索引，非全文索引
      EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘'); -- 全文索引
      ​
      ​

    • 测试索引：查询100万条数据中的某几条

      ​
      CREATE TABLE `app_user` (
      `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
      `email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
      `phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
      `gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
      `password` VARCHAR(100) NOT NULL COMMENT '密码',
      `age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
      `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
      `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`)
      ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
      ​
      ​
      ​
      -- 插入100万条数据
      DELIMITER $$   -- 写函数之前，必须要写，标志
      -- set global log_bin_trust_function_creators=TRUE;
      CREATE FUNCTION mock_data()
      RETURNS INT DETERMINISTIC
      BEGIN 
        DECLARE num INT DEFAULT 1000000;
        DECLARE i INT DEFAULT 0;
        WHILE i<num DO
             -- 插入语句
             INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'2525252@qq.com',FLOOR(CONCAT('181',RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
          SET i=i+1;
        END WHILE;
        RETURN i;
      END ;
      SELECT mock_data() ;
      ​
      ​
      --
      ​
      SELECT * FROM app_user WHERE `name`='用户9999' ; --  0.809 sec    ;  0.008 sec
      ​
      EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' ;
      ​
      SELECT * FROM student ;  -- 0.001 sec
      ​
      -- 创建索引：id_表名_字段名
      -- create index 索引名 on  表 (字段)
      CREATE INDEX id_app_user_name ON app_user(`name`);
      ​

       

  • 索引原则：

    • 索引不是约多越好；

    • 不要对经常变动的数据加索引；

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

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

       

  • 总结：

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

    • 索引的数据结构；

    • Hash类型的索引

    • Btree：InnoDB索引的默认类型；

    • MySQL索引背后的数据结构及算法原理