MySQL索引、事务与存储引擎

一.索引

1.定义

• 索引是一个排序的列表，包含索引字段的值和其对应的行记录数据所在的物理地址
• 作用：加快表的查询速度（主要作用），还可以对字段排序
• 副作用：会额外占用磁盘空间；更新包含索引的表会花费更多的时间

工作方式

• 没有索引的情况下，要查询某行记录数据时，需要先扫描全表，再定位某行记录数据的位置
• 有了索引后，会先通过索引查询到行记录数据所在的物理地址，即可直接访问相应的行记录数据，就像通过书目录的页码快速查找书内容一样。

创建索引的依据

1）表的记录行数较多时（一般超过三五百行时），且读操作多的情况下应该要创建索引
2）建议在表的 主键字段、外键字段、多表连接使用的公共字段、唯一性较好的字段、不经常更新的字段、where条件字段、分组(group by)字段、排序(order by)字段、短小的字段 上创建索引
3）不建议在 唯一性较差的字段、更新太频繁的字段、大文本字段 上创建索引
 

2.索引的分类

1）普通索引

• 最基本的索引类型，没有唯一性之类的限制
• 创建普通索引的方式

创建普通索引
create index 索引名 on 表名(字段(长度));

修改表结构的方式创建索引
alter table 表名 add index 索引名(字段);

创建表时指定索引
create table 表名 (.... , index 索引名(字段));

2）唯一索引

• 与普通索引类似，但区别是唯一索引列的每个值都唯一。唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。
• 创建唯一索引的方式

直接创建唯一索引：
CREATE UNIQUE INDEX 索引名 ON 表名(列名);

修改表方式创建
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

创建表的时候指定
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

3）主键索引

• 是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。 
• 创建主键索引的方式

创建表的时候指定
CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

修改表方式创建
ALTER TABLE 表名 ADD PRIMARY KEY (列名); 

4）组合索引（单列索引与多列索引）

• 可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为 select 语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。
• 创建组合索引的方式

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';
#查询语句使用 and 做逻辑运算符时，字段顺序要与创建多列索引的字段顺序一致（要满足最左原则）

5）全文索引

• 适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。 
• 创建全文索引的方式

直接创建全文索引
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

修改表方式创建
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

创建表的时候指定索引
CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
#数据类型可以为 CHAR、VARCHAR 或者 TEXT

使用全文索引查询
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

3.查看索引 

show index from 表名;
show keys from 表名;

字段	含义
Table	表的名称
Non_unique	如果索引不能包括重复词，则为 0；如果可以，则为 1
Key_name	索引的名称
Seq_in_index	索引中的列序号，从 1 开始
Column_name	列名称
Collation	列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）
Cardinality	索引中唯一值数目的估计值
Sub_part	如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL
Packed	指示关键字如何被压缩。如果没有被压缩，则为 NULL
Null	如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO
Index_type	用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）
Comment	备注

4.删除索引 

• 删除索引的方式

直接删除索引
DROP INDEX 索引名 ON 表名;

修改表方式删除索引
ALTER TABLE 表名 DROP INDEX 索引名;

删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;

补充 

• 遇到 select 查询语句执行速度慢该怎么办？

升级 CPU 内存 硬盘 硬件性能；
对 MySQL 配置进行优化；
对查询语句的结构进行优化，比如将嵌套子查询优化成表连接查询；或连接表时，可以先用where条件对表进行过滤，然后做表连接；
进行索引优化：先使用 explain 分析 select 语句（看 key rows type 字段），判断这个查询语句是否正确的使用了索引；
再根据查询语句中的 where 条件字段建立相应的单列索引或者多列组合索引（多列组合索引要满足最左原则）

二.事务 

1.定义

• 事务就是一组数据库操作序列（包含一个或多个SQL操作命令），事务会把所有操作看作是一个不可分割的整体向数据库系统提交或撤销操作，所有操作要么都执行，要么都不执行。

 事务的 ACID 特性：原子性、一致性、隔离性、持久性

• 原子性：事务管理的基础。把事务中的所有操作看作是一个不可分割的工作单元，要么都执行，要么都不执行。
• 一致性：事务管理的目的。保证事务开始前和事务结束后数据的完整和一致
• 隔离性：事务管理的手段。使多个事务并发操作同一个表数据时，每个事务都有各自独立的数据空间，事务的执行不会受到其它事务的干扰。可通过设置隔离级别来解决不同的一致性问题。
• 持久性：事务管理的结果。当事务被提交以后，事务中的命令操作修改的结果会被持久化保存，且不会被回滚。

 事务的隔离级别

未提交读	Read Uncommitted	允许脏读，不可重复读 幻读
提交读	Read Committed	不允许脏读，允许不可重复读幻读（一般生产环境使用的隔离级别）
可重复读	Repeatable Read	不允许脏读，不可重复读，有条件的允许幻读（InnoDB存储引擎可以通过多版本并发控制MVCC解决幻读问题）
串行读	Serializable	都不允许，相当于表级锁定，但是会影响数据库的读写效率性能

• 脏读：当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
• 不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）
• 幻读：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有一个没有修改的数据行，就好象发生了幻觉一样。
• 丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。
• 未提交读：允许脏读，即允许一个事务可以看到其他事务未提交的修改。
• 提交读：允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。防止脏读。
• 可重复读：---mysql默认的隔离级别确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。可以防止脏读和不可重复读。
• 串行读(Serializable)：---相当于锁表完全串行化的读，将一个事务与其他

2.设置隔离级别   

set global transaction isolation level 隔离级别名称;      #全局级隔离级别，可在所有会话有效，当前会话需要重新登录方可有效
set session transaction isolation level 隔离级别名称;     #会话级隔离级别，仅在当前会话中立即有效

3.查看隔离级别 

show global variables like '%isolation%';
show session variables like '%isolation%';

4.事务管理操作 

begin;               #显式的开启一个事务
.... insert into   update 表 set    delete from           #事务性操作
savepoint XX;        #在事务中创建回滚点
rollback to XX;      #在事务中回滚到指定的回滚点位置
commit; 或 rollback;    #提交或回滚 结束事务

5.自动提交事务 

set global/session autocommit = 0/1       #global全局级别，session会话级别，0关闭自动提交，1开启自动提交

show global/session variables like 'autocommit';

三.存储引擎 

1.定义

• 存储引擎是MySQL数据库的组件，负责执行实际的数据IO操作（数据的存储和提取）。工作在文件系统之上，数据库的数据会先传输到存储引擎，再按照存储引擎的存储格式保存到文件系统。
• 常用的存储引擎：InnoDB   MyISAM

InnoDB 和 MyISAM 的区别：

• MyISAM：不支持事务、外键约束；支持全文索引；锁定类型只支持表级锁定；适合单独的查询和插入的操作；读写会相互阻塞；硬件资源占用较小；数据文件和索引文件是分开存储的，存储成三个文件：表结构文件.frm、数据文件.MYD、索引文件.MYI
• 使用场景：适用于不需要事务支持，单独的查询或插入数据的业务场景
• InnoDB：支持事务、外键约束；也支持全文索引；锁定类型支持行级锁定（在全表扫描时仍会表级锁定）；读写并发能力较好；缓存能力较好可以减少磁盘IO的压力；数据文件也是索引文件，存储成：表结构文件.frm、表空间文件.ibd
• 使用场景：适用于需要事务支持，数据一致性要求较高，数据会频繁更新，读写并发高的业务场景

2.MySQL 查询数据的执行过程

1. 客户端向 MySQL 服务器发送一条查询请求，连接器负责处理连接，并进行身份验证和权限控制。
2. MySQL 先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果；否则使用查询解析器进行SQL语句解析、预处理，再由优化器生成对应的执行计划。
3. MySQL 根据执行计划，调用存储引擎来执行查询。
4. 将结果返回给客户端，同时缓存查询结果。

3.存储引擎管理操作

alter table 表名 engine=innodb/myisam;            #针对已存在的表修改存储引擎

create table 表名 (....) engine=innodb/myisam;    #新建表时指定存储引擎

set global/session default_storage_engine=innodb/myisam;     #设置默认存储引擎

vim /etc/my.cnf                                   #修改配置文件
default_storage_engine=INnoDB/MyISAM

查看存储引擎
show create table 表名;
show table status [from 库名] where name = '表名';

4.死锁 

• 定义：死锁是指两个或多个事务在同一个资源上相互占用，并请求对方锁定的资源，从而导致相互阻塞的现象。

如何避免死锁？
1）设置事务的锁等待超时时间 innodb_lock_wait_timeout
2）设置开启死锁检测功能 innodb_deadlock_detect
3）为表建立合理的索引，减少表锁发生的概率
4）如果业务允许，可以降低隔离级别，比如选用 提交读 Read Committed 隔离级别，从而避免间隙锁导致死锁
5）建议开发人员尽量使用更合理的业务逻辑，比如多表操作时以固定顺序访问表，尽量避免同时锁定多个资源
6）建议开发人员尽量保持事务简短，减少对资源的占用时间和占用范围
7）建议开发人员在读多血少的场景下采用乐观锁机制