黑马程序员---事务与索引

落日映苍穹つ 2022-11-28 10:49 146阅读 0赞

一：事务：  
概念：把一些sql语句打包一起，要么完全执行，要么不执行。

使用场景：在日常生活中，有时我们需要进行银行转账，这个银行转账操作背后就是需要执行多个SQL语句，假如这些SQL执行到一半突然停电了，那么就会导致这个功能只完成了一半，这种情况是不允许出现，要想解决这个问题就需要通过事务来完成。

事务的四大特征：

1：原子性(Atomicity)：一系列sql语句要么执行，要么不执行。  
2：一致性(Consistency)：要么是完全执行的状态，要么是完全不执行的状态。  
3：隔离性(Isolation)：@1：一个事务在执行过程中其他的事务是不可见的 @2：一个事务在修改的时候另外一个事务不允许对这个数据进行修改。晚的事务会阻塞状态，等第一个事务执行完，第二个事务解阻塞。  
4：持久性(Durability)：一旦事务提交数据就保存在数据库中，持久保存。使用事务的条件：引擎必须是InnoDB 。

查看数据库的引擎： show engines;
    修改引擎：alter table 表名 engine= "引擎类型"

开启事务后执行修改命令，变更数据会保存到MySQL服务端的缓存文件中，而不维护到物理表中  
MySQL数据库默认采用自动提交(autocommit)模式，如果没有显示的开启一个事务,那么每条sql语句都会被当作一个事务执行提交的操作。当设置autocommit=0就是取消了自动提交事务模式，直到显示的执行commit和rollback表示该事务结束。

事务执行流程：  
1：开启事务： start transaction 或者 begin  
2：取消自动提交事务： set autocommit = 0  
3：提交事务 ：commit  
4:回滚事务：rollback

二：索引：  
1：索引在MySQL中也叫做“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，更通俗的来说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。  
2:索引适合：数据量大，不经常修改。  
索引会花费大量的空间存储，有可能比源文件还大，索引一旦建立，如果修改一个数据，后面的也要跟着修改，就需要重新建立索引，所以经常修改的也不适合建索引。  
3：查看表中已经存在的索引：`show index from 表名;`  
4：建立索引：`alter table 表名 add index 索引名（列名）`  
5：删除索引：`alter table 表名 drop index 索引名`  
案例：验证索引查询性能:  
1:创建测试表：`create table test_index(title varchar(10));`  
2：向表中插入10万测试数据：

import pymysql
    
    def main():
        # 1: 获取连接
        conn = pymysql.connect(host="localhost", port=3306, user="root", password="123456", database="jing_dong",
                               charset="utf8")
        # 2:获取游标对象
        cur = conn.cursor()
    
        # 3:利用游标循环插入数据
    
        for i in range(100000):
            cur.execute("insert into test_index values('ha-%d')" % i)
    
        # 4:提交事务
        conn.commit()
    
    if __name__ == '__main__':
        main()

开启运行时间检测
    mysql> set profiling =1;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> select * from test_index where title="ha-99999";
    +----------+
    | title    |
    +----------+
    | ha-99999 |
    +----------+
    1 row in set (0.03 sec)
    查看执行时间
    mysql> show profiles;
    +----------+------------+-------------------------------------------------+
    | Query_ID | Duration   | Query                                           |
    +----------+------------+-------------------------------------------------+
    |        1 | 0.02914900 | select * from test_index where title="ha-99999" |
    +----------+------------+-------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> alter table test_index add index (title);
    Query OK, 0 rows affected (1.26 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> select * from test_index where title='ha-99999';
    +----------+
    | title    |
    +----------+
    | ha-99999 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> show profiles;
    +----------+------------+-------------------------------------------------+
    | Query_ID | Duration   | Query                                           |
    +----------+------------+-------------------------------------------------+
    |        1 | 0.02914900 | select * from test_index where title="ha-99999" |
    |        2 | 1.25564900 | alter table test_index add index (title)        |
    |        3 | 0.00043400 | select * from test_index where title='ha-99999' |
    +----------+------------+-------------------------------------------------+
    3 rows in set, 1 warning (0.00 sec)

我们可以发现：创建索引用的时间最长，其次是正常查询，创建完索引再次查询，效率提高近百倍。  
三：联合索引：  
联合索引又叫复合索引，即一个索引覆盖表中两个或者多个字段，一般用在多个字段一起查询的时候。

-- 创建teacher表
    create table teacher
    (
        id int not null primary key auto_increment,
        name varchar(10),
        age int
    );
    
    -- 创建联合索引
    alter table teacher add index (name,age);

联合索引的好处:  
减少磁盘空间开销，因为每创建一个索引，其实就是创建了一个索引文件，那么会增加磁盘空间的开销。  
在使用联合索引的时候，我们要遵守一个最左原则,即index(name,age)支持 name 、name 和 age 组合查询,而不支持单独 age 查询，因为没有用到创建的联合索引。

-- 下面的查询使用到了联合索引
    select * from stu where name='张三' -- 这里使用了联合索引的name部分
    select * from stu where name='李四' and age=10 -- 这里完整的使用联合索引，包括 name 和 age 部分 
    -- 下面的查询没有使用到联合索引
    select * from stu where age=10 -- 因为联合索引里面没有这个组合，只有 name | name age 这两种组合