MySQL之事务 索引 锁_mysql高级 索引、事务、锁

一、索引index

1.什么是索引：索引的做用相当于图书的目录，可以根据目录中的页码快速找到所需要的内容

2.为什么要有索引：索引在mysql中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构

索引优化应该是对查询性能最有效的手段，善用索引能够轻易将查询性能提高好几个数量级

3.索引的优劣势 ：

优势：高效查询

劣势：

索引本身也是表，因此会占用存储空间；

索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大

构建索引会降低数据表的更新效率，因此在修改数据表的同时还需要修4改索引表

4.mysql中索引的使用

在创建表时创建索引代码如下：

CREATE TABLE 表名(

ID INT NOT NULL,

username VARCHAR(16) NOTNULL,

INDEX myindex(username(16))

)

5.查看索引： SHOW INDEX FROM 表名

6.删除索引：DROP INDEX 索引名 表名；  或者 ALTER TABLE 表名 DROP INDEX 索引名；

7.索引的分类：

主键索引：不允许重复，不允许空值

唯一索引：用来建立 索引的列的值必须是唯一的，允许空值

普通索引：用表中的普通列构建的索引，没有任何限制

全文索引：用大文本对象的列构建的索引

组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

适合使用：经常查询条件的列，排序的列，主键外键

不适合使用：经常增删改的列和记录少

索引失效的情况 ：

在组合索引中不能有列的值为 NULL，如果有，那么这一列对组合索引就是无效的

在一个 SELECT 语句中，索引只能使用一次，如果在 WHERE 中使用了，那么在 ORDER BY 中就不要用了

LIKE 操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引

在索引的列上使用表达式或者函数会使索引失效

在查询条件中使用不等于，包括符号和！=会导致索引失效

在查询条件中使用 IS NULL 或者 IS NOT NULL 会导致索引失效

字符串不加单引号会导致索引失效

在查询条件中使用 OR 连接多个条件会导致索引失效，除非 OR 链接的每个条件都加上索引

如果排序的字段使用了索引，那么 select 的字段也要是索引字段，否则索引失效

尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引

事务

什么是事务：一个最小的不可再分的工作单元；

通常一个事务对应一个完整的业务(例如银行账户转 账业务，该业务就是一个最小的工作单元)

转账操作理解事务：在一个转账业务中，以上两条 DML 语句必须同时成功或者同时失败

MySQL 中事务的使用

开启事务：start transaction; begin transaction;

提交事务：commit

回滚事务：rollback;

事务的特征

原子性：事务是最小单位，不可再分

一致性：事务要求所有的 DML 语句操作的时候，必须保证同时成功或者同时失败

隔离性：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰

持久性：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

事务处理不当的现象

脏读：A读取B未提交的数据，如果A用了B没提交的数据那该数据无效(B回滚)，脏读数据。

不可重复读：A读完数据后，B修改数据并提交A再次读取发现数据变了，前后数据不一致，就证明该数据不能重复读取，因为它是可变的。

幻读

A读取后B插入数据A再次读取时发现数量不对，应生幻觉一般。

事务的隔离级别

读未提交(read uncommitted)：一个现象解决不了

读已提交(read committed)：解决脏读

可重复读(repeatable read)：解决脏读，不可重复读(MySQL默认)

串行化(serializable)：解决所有不好的现象：脏读，不可重复读，幻读

事务隔离级别与一致性的关系

锁

1、乐观锁

想法很乐观，认为 这次的操作不会导致冲突。

2、悲观锁

悲观锁就是在操作数据时，认为此操作会出现数据冲突。

3共享锁与排它锁

共享锁

也称为读锁，允许事务读取一行数据。

1.A为某条数据加共享锁，他可以查询修改。其他事物只能查询，直到A释放。

2.A为某条数据加共享锁，B同时也为该数据加共享锁，A和B互相制约都只能查询。

3.加了共享锁以后不能再加排它锁，只能加共享锁。

排它锁

也称写锁，允许事务删除或更新一行数据。

注意！

共享锁之间是兼容的，而排它锁与其他任意锁都不兼容。因此我们在数据库中可以并行读，但只能串行写，只有这样才不会发生线程竞争。

锁的粒度

锁的粒度就是你要锁定的范围是多大。使用锁时，锁定的东西越少，对其他竞争者影响越小，这个东西的大

小就是锁粒度