【MySQL】索引&事务

目录

1.索引

1.1查看索引

1.2创建索引

1.3删除索引

1.4索引底层的数据结构

2.事务

2.1使用事务

2.2事务的特性

// 隔离性

前情提要：一张表里有很多数据，基本查找就是遍历表，一条一条筛选，效率十分低下，因此可以给这个表建立一个索引，提高查找的速度。

1.索引

优点：提高查找的速度.

缺点：1> 消耗额外的空间（索引数据存储在硬盘上）；

           2> 可能拖慢增删改的速度（增删改后需要修改索引）

1.1查看索引

格式：show index from 表名;

注意：当表中存在主键primary key或unique约束或外键约束时，内部会自动给表创建索引。

mysql> create table student(id int primary key, name varchar(20));
Query OK, 0 rows affected (0.03 sec)
 
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
 
mysql> create table student1(id int unique, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
 
mysql> show index from student1;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student1 |          0 | id       |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

1.2创建索引

格式：create index 索引名 on 表名(列名)；

注意：此操作可能会 非常危险！！若表非空且包含巨量数据，创建索引会引起非常大规模的硬盘IO操作。所以在设计表的时候就要考虑哪些列需要索引。

mysql> create index idx_student_name on student(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show index from student;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY          |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | idx_student_name |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

1.3删除索引

格式：drop index 索引名 on 表名;

注意：无法删除建表时系统自动生成的索引，只能删除自己手动创建的索引。

删除索引这个操作也可能 非常危险！！原因同创建索引。

mysql> drop index idx_student_name on student;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

1.4索引底层的数据结构

MySQL索引的数据结构取决于MySQL使用哪个存储引擎。数据库这块组织数据使用的数据结构是在硬盘上的。读写一次硬盘，开销远远大于内存的。读写一次硬盘大约相当于读写1w次内存。

2.事务

概念：逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部执行失败。

           在不同的环境中都可以有事务，对应在数据库中就是数据库事务。

2.1使用事务

步骤：

1> 开启事务：start transaction;

2> 执行多条SQL语句；

3> 回滚或提交：rollback / commit;

     说明：rollback是全部失败，commit是全部成功。

mysql> start transaction;
-- 阿里巴巴账户减少2000
mysql> update accout set money=money-2000 where name = '张三';
-- 四十大盗账户增加2000
mysql> update accout set money=money+2000 where name = '李四';
 
commit;

2.2事务的特性

* 原子性：保证多个操作被打包成一个整体，要么能够全部执行正确，要么一个都不执行；

* 一致性：事务执行之前，和事务执行之后，数据能够对得上；

* 持久性：事务中的各种操作都是持久生效的，一旦执行成功，产生的修改都会写到硬盘中；

* 隔离性：并发执行事务的时候，隔离性会在执行效率和数据可靠之间做出权衡，隔离性越高，并发性越低，数据越可靠，性能就越低。  

// 隔离性

1）脏读：A在写事务，同时B在读事务，B在多次读的过程中可能会读到不同的值.

解决方法：给写操作加锁，直到A写完并提交事务后，B才可以查看该事务.

2）不可重复读：A在读事务，同时B在写事务，B在多次读的过程中可能会读到不同的值.

解决方法：给读操作也加锁，A在读事务的时候，B不能写.

3）幻读：一次事务在多次读的时候，虽然读到的数据的值是一样的，但是结果集不同.

解决方法：串行化，彻底放弃并发执行事务，所有的事务都是一个一个串行执行.

                  此时并发性最低，隔离性最高，效率最低，数据最可靠.

MySQL提供了四种事务的隔离级别，可以自己配置隔离级别：

read uncommitted (RU)：允许读未提交的数据. 

read committed (RC)：允许读已经提交的数据.（给写操作加锁了）

repeatable read (RR)：可以重复读取数据.（给写操作和读操作加锁了），这是默认隔离级别.

serializable：事务彻底串行执行.

结束！