高级Mysql索引、事务和锁

一、索引

1.索引简介

1）什么是索引？

    一般的应用系统，读写比例在 10:1 左右，而且插入操作和一般的更新操作很少出现性能问题，
在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句
的优化显然是重中之重。说起加速查询，就不得不提到索引了。

2) 为什么要有索引？

    1.索引在 MySQL 中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关
键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
    2.索引优化应该是对查询性能优化最有效的手段了。善用索引能够轻易将查询性能提高好几个数量级。
    3.索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

3) 索引的优劣势：

索引的优势：可以快速检索，减少 I/O 次数，加快检索速度；根据索引分组和排序，可以加快分组和排序。
索引的劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间是数据表的 1.5 倍；索
           引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修
           改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表。

2.MySQL 中索引的使用

1) 创建索引

索引可以在创建表时创建，也可以在创建表之后添加。

CREATE TABLE mytable(
ID INT NOT NULL, 
username VARCHAR(16) NOT NULL, 
INDEX myindex (username(16))
);

语法解析：代码中的 index 表示索引，myindex 表示定义的索引名称。

在创建表之后创建索引

CREATE INDEX myindex ON mytable(username(16));
或者
ALTER table mytable ADD INDEX myindex(username

查看索引

SHOW INDEX FROM mytable

删除索引

删除索引可以使用 drop 或者 alter 命令来完成。

DROP INDEX myindex ON mytable;
或者
ALTER TABLE mytable DROP INDEX myindex

语法分析：代码中的 myindex 是创建索引时定义的索引名称。

3.索引的使用规则

1)索引分类
常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引。

• 主键索引：即主索引，根据主键 pk_clolum（length）建立索引，不允许重复，不允许空值；
• 唯一索引：用来建立索引的列的值必须是唯一的，允许空值；
• 普通索引：用表中的普通列构建的索引，没有任何限制；
• 全文索引：用大文本对象的列构建的索引；
• 组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值。

2)适合建立索引的情况

• 主键自动建立唯一索引；
• 经常作为查询条件在 WHERE 或者 ORDER BY 语句中出现的列要建立索引；
• 作为排序的列要建立索引；
• 查询中与其他表关联的字段，外键关系建立索引；
• 高并发条件下倾向组合索引；
• 用于聚合函数的列可以建立索引，例如使用了 max(column_1)或者 count(column_1)时的 column_1 就需要建立索引。

3)不适合建立索引的情况

• 经常增删改的列不要建立索引；
• 有大量重复的列不建立索引；
• 表记录太少不要建立索引。

4)索引失效的情况

• 在组合索引中不能有列的值为 NULL，如果有，那么这一列对组合索引就是无效的；
• 在一个 SELECT 语句中，索引只能使用一次，如果在 WHERE 中使用了，那么在 ORDER BY 中就不要用了；
• LIKE 操作中，’%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引；
• 在索引的列上使用表达式或者函数会使索引失效；
• 在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效；
• 在查询条件中使用 IS NULL 或者 IS NOT NULL 会导致索引失效；
• 字符串不加单引号会导致索引失效；
• 在查询条件中使用 OR 连接多个条件会导致索引失效，除非 OR 链接的每个条件都加上索引；
• 如果排序的字段使用了索引，那么 select 的字段也要是索引字段，否则索引失效；
• 尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引。

二、事务

1、事务简介

1) 什么是事务？

事务（Transaction）：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转
账业务，该业务就是一个最小的工作单元)。

事务只和 DML 语句有关，或者说 DML

2 、MySQL 中事务的使用

1) 开启事务

start transaction;
或者
begin transaction

2) 提交事务

commit;

3) 回滚事务

rollback;

3、事务的特征

事务四大特征(ACID)：

1. 原子性(A)：事务是最小单位，不可再分；
2. 一致性©：事务要求所有的 DML 语句操作的时候，必须保证同时成功或者同时失败；
3. 隔离性(I)：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰；
4. 持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)。

4、事务的隔离级别

事务的隔离级别有 4 个：
5. 读未提交（read uncommitted）：事务 A 和事务 B，事务 A 未提交的数据，事务 B 可以读取到，这里读取到的数据叫做“脏数据”。这种隔离级别最低，一般是在理论上存在，数据库隔离级别一般都高于该级别；
6. 读已提交（read committed）：事务 A 和事务 B，事务 A 提交的数据，事务 B 才能读取到。这种隔离级别高于读未提交，可以避免“脏数据”。但如果事务 A 多次读取同一数据，而事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致，这种现象称为“不可重复读”；
7. 读已提交（read committed）：事务 A 和事务 B，事务 A 提交的数据，事务 B 才能读取到。这种隔离级别高于读未提交，可以避免“脏数据”。但如果事务 A 多次读取同一数据，而事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致，这种现象称为“不可重复读”；
8. 串行化（serializable）：事务 A 和事务 B，事务 A 在操作数据库时，事务 B 只能排队等待。这种级别可以避免“幻读”，每一次读取的都是数据库中真实存在数据，事务 A 与事务 B 串行，而不并发。

事务隔离级别与一致性的关系也可以用下表表示：

三、锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。

1、乐观锁

乐观锁不是数据库自带的，需要我们自己去实现。

2、悲观锁

1)共享锁与排它锁

1. 共享锁（S）：也称为读锁，允许事务读取一行数据。
2. 排它锁（X）：也称写锁，允许事务删除或更新一行数据。
   注意：共享锁之间是兼容的，而排它锁与其他任意锁都不兼容。因此我们在数据库中可以并行读，但只能串行写，只有这样才不会发生线程竞争。

2)锁的粒度
锁的粒度就是你要锁定的范围是多大
锁的粒度分类：

• 表级锁：开销小，加锁快，不会出现死锁。锁定粒度最大，适合查询为主的，只有少量按索引条件更新数据的操作。缺点是资源争用概率高；
• 行级锁：开销大，加锁慢，会出现死锁。锁定粒度最小，适合大量按索引条件并发更新少量不同数据，同时又有并发查询的操作。使用行级锁定的主要是 InnoDB 存储引擎；
• 页面锁：开销介于表锁与行锁之间，会出现死锁。