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

一：事务：
概念：把一些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 这两种组合