Mysql数据库之索引与事务
目录
一.索引
1.索引基本概述
2.索引类型
3.索引结构
4.查看索引
5.建立索引
6.删除索引
7.EXPLAIN 工具
二.事务
1.ACID特性
2.管理事务
3.事务隔离级别
一.索引
1.索引基本概述
索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现
优点：
-
索引可以降低服务需要扫描的数据量，减少了IO次数
-
索引可以帮助服务器避免排序和使用临时表
-
索引可以帮助将随机I/O转为顺序I/O
-
加快查询速度，提高数据库性能
-
设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。
-
当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。避免排序和使用临时表
-
可以降低数据库的IO成本（减少io次数），并且索引还可以降低数据库的排序成本。将随机I/O转为顺序I/O
-
通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。
-
可以加快表与表之间的连接
-
在使用分组和排序时，可大大减少分组和排序的时间。
-
建立索引在搜索和恢复数据库中的数据时能显著提高性能
缺点：
-
占用额外空间，影响插入速度
2.索引类型
-
B+ TREE、HASH、R TREE、FULL TEXT
-
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
-
主键索引、二级（辅助）索引
-
稠密索引、稀疏索引：是否索引了每一个数据项
-
简单索引、组合索引: 是否是多个字段的索引
-
左前缀索引：取前面的字符做索引
-
覆盖索引：从索引中即可取出要查询的数据，性能高
3.索引结构
二叉树
类似于二分法， 生成了一个0 - 100的随机数， 猜大猜小会有提示， 大部分先从50 开始猜，继续2分法不断去猜。
最多两个个分支
缺点： 数据偏移， 不够平衡
红黑树
B-Tree索引（blance tree）
缺点：
1.连续范围查找都要从头开始，效率不稳定，快的很快，慢的就比较慢
2.所存数据量越大，查找次数越多
B+Tree索引
B+Tree索引：按顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据
4.查看索引
show index from 表名;
show keys from 表名;
例子：
show index from hellodb.students;
5.建立索引
创建索引的原则依据
●表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是主表的主键，查询时可以快速定位。
●记录数超过300行的表应该有索引。如果没有索引，每次查询都需要把表遍历一遍，会严重影响数据库的性能。
●经常与其他表进行连接的表，在连接字段上应该建立索引。
●唯一性太差的字段不适合建立索引。
●更新太频繁地字段不适合创建索引。
●经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。
●在经常进行 GROUP BY、ORDER BY 的字段上建立索引；
●索引应该建在选择性高的字段上。
●索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。
格式：
CREATE INDEX 索引名 ON 表名 (列名[(length)]);
例子：
select * from students where name='xu xian'; #找许仙
create index idx_name on students(name(5)); #以name 为字段建立索引 只取 前5个字符
show index from students; #查看 索引 会多一条
explain select * from students where name='xu xian'; #可以看到使用了索引
6.删除索引
DROP INDEX 索引名 ON 表名;
7.EXPLAIN 工具
语法：
EXPLAIN SELECT clause
例子：
explain select * from stundets where stuid=20;
说明：
| 列名 | 说明 |
|---|---|
| id | 执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置 |
| select_type | 简单查询：SIMPLE|复杂查询：PRIMARY（最外面的SELECT）、DERIVED（用于FROM中的子查询）、UNION（UNION语句的第一个之后的SELECT语句）、UNIONRESUlT（匿名临时表）、SUBQUERY（简单子查询） |
| table | 访问引用哪个表（引用某个查询，如“derived3”） |
| type | 关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式 |
| possible_keys | 查询可能会用到的索引 |
| key | 显示mysql决定采用哪个索引来优化查询 |
| key_len | 显示mysql在索引里使用的字节数 |
| ref | 当使用索引列等值查询时,与索引列进行等值匹配的对象信息 |
| rows | 为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值 |
| Extra | 额外信息 Using index：MySQL将会使用覆盖索引，以避免访问表 Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary：MySQL对结果排序时会使用临时表 Using filesort：对结果使用一个外部索引排序 |
说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：NULL> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref
| 类型 | 说明 |
|---|---|
| All | 最坏的情况,全表扫描 |
| index | 和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，扫描索引的数据，它比按索引次序全表扫描的开销要小很多 |
| range | 范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range |
| ref | 一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。 |
| eq_ref | 最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 （高效） |
| const | 当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效） |
| system | 这是const连接类型的一种特例，表仅有一行满足条件。 |
| Null | 意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效） |
使用 profile工具监控
#打开后，会显示语句执行详细的过程
set profiling = ON;
#查看语句,注意结果中的query_id值
show profiles ;
#显示语句的详细执行步骤和时长
Show profile for query #
show profile for query 1;
二.事务
事务 Transactions：一组原子性的 SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo,redo等故障恢复功能
1.ACID特性
-
A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，只要有一个失败后回滚
-
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于质量守恒定律(A1wB 0 A1w 给 B转1w 始终保持A+B=1w)
-
I：Isolation隔离性；一个事务所做出的操作在提交之前，能不能为其它事务所见；和隔离级别有关系 4个隔离级别
-
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库
2.管理事务
显示启动事务
BEGIN
BEGIN WORK
START TRANSACTION
begin #开启事务
begin work
start transaction
结束事务
#提交,相当于vi中的wq保存退出
COMMIT
commit
#回滚,相当于vi中的q!不保存退出
ROLLBACK
rollback
注意：只有事务型存储引擎中的DML语句方能支持此类操作
自动提交：
set autocommit={1|0}
默认为1，为0时设为非自动提交
建议：显式请求和提交事务，而不要使用"自动提交"功能
3.事务隔离级别
| 隔离级别 | 脏读 | 可重复读 | 幻读 | 加读锁 |
|---|---|---|---|---|
| 读未提交 | 可以出现 | 可以出现 | 可以出现 | 否 |
| 读提交 | 不允许出现 | 可以出现 | 可以出现 | 否 |
| 可重复读 | 不允许出现 | 不允许出现 | 可以出现 | 否 |
| 序列化 | 不允许出现 | 不允许出现 | 不允许出现 | 是 |
-
READ UNCOMMITTED（未提交可读 脏读） 可读取到未提交数据，产生脏读
-
READ COMMITTED（提交可读，每次读取数据不一致） 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
-
REPEATABLE READ 可重复读 幻读 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
-
SERIALIZABLE 串读 可串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞其它事务的读写（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差
演示实例：
可重复读 默认是此隔离级别
#1.打开两个终端的 数据库，都开启事务
use hellodb;
begin;
#在其中一个终端上插入数据在未提交前，另一个终端的事务无法看到修改的结果，但是自己可以看见
insert teachers values(null,'wuyazi',79,'M');
select * from teachers;
#终端1 提交后 终端2只要不结束事务还是看不到，需要提交事务后才可以看到。
commit
演示第一隔离级别 可看见脏读 READ UNCOMMITTED
vim /etc/my.cnf
[mysqld]
transaction-isolation=READ-UNCOMMITTED
systemctl restart mysqld
select @@tx_isolation; #查看隔离级别
begin;
#在其中一个终端上插入数据在未提交前,另一终端也可以看见
insert teachers values(6,'c',80,'M');
select * from teachers;
演示串行化 最严格的隔离级别 串行影响并发性
#MySQL8.0之前版本
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'
#MySQL8.0
SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE
systemctl restart mysqld
select @@tx_isolation; #查看隔离级别
begin;
#开启事务两边可以同时读表，会互相锁
select * from teachers;
delete from teachers where tid=5;
#无法删除加锁 并发性较差