MySQL索引、事务与存储引擎
1.索引介绍
定义：索引是一个排序的列表，包含索引字段的值和其对应的行记录数据所在的物理地址
作用：加快表的查询速度（主要作用），还可以对字段排序
副作用：会额外占用磁盘空间；更新包含索引的表会花费更多的时间
（1）工作方式
没有索引的情况下，要查询某行记录数据时，需要先扫描全表，再定位某行记录数据的位置
有了索引后，会先通过索引查询到行记录数据所在的物理地址，即可直接访问相应的行记录数据，就像通过书目录的页码快速查找书内容一样。
（2）创建索引的依据
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 表名 (.... , unique 索引名(字段)); #创建表的时候指定
（3）主键索引
是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。
alter table 表名 add primary key(字段); #修改表方式创建
create table 表名 (.... , primary key(字段)); #创建表的时候指定
（4）多列组合索引（单列索引与多列索引）
可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为 select 语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。
create index 索引名 on 表名(字段1, 字段2, ....);
alter table 表名 add index 索引名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ;
#查询语句使用 and 做逻辑运算符时，字段顺序要与创建多列索引的字段顺序一致（要满足最左原则）
（5）全文索引 （FULLTEXT）
适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。在 MySQL5.6 版本以前
FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。
create fulltext index 索引名 on 表名(字段); #直接创建索引
alter table 表名 add fulltext 索引名(字段); #修改表方式创建
create table 表名 (.... , fulltext 索引名(字段)); #创建表的时候指定索引
全文索引可用作于模糊查询 select 字段列表 from 表名 where match(字段) against('单词');
全文索引只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引，只支持InnoDB和MyISAM引擎。
全文索引只支持英文全文索引，不支持中文全文索引，需要使用ngram全文解析器，用来支持中文全文索引
vim /etc/my.cnf
[mysqld]
ngram_token_size=2 #指定查询的单词的最小字数
create fulltext index 索引名 on 表名(字段) WITH PARSER ngram;
alter table 表名 add fulltext 索引名(字段) WITH PARSER ngram;
create table 表名 (.... , fulltext 索引名(字段) WITH PARSER ngram);
select 字段列表 from 表名 where match(字段) against('单词');
#默认使用自然语言模式
select 字段列表 from 表名 where match(字段) against('+单词1 -单词2' IN BOOLEAN MODE);
#使用BOOLEAN模式，必须包含"单词1"，且不能包含"单词2"
select 字段列表 from 表名 where match(字段) against('+单词1 +单词2' IN BOOLEAN MODE);
#使用BOOLEAN模式，必须同时包含"单词1"和"单词2"
select 字段列表 from 表名 where match(字段) against('单词1 单词2' IN BOOLEAN MODE);
#使用BOOLEAN模式，要么包含"单词1"，要么包含"单词2"
（6）删除和查看索引以及各字段的含义
（1）删除索引
#这两种方法都可以用来删除普通索引、唯一索引、全文索引、组合索引
drop index 索引名 on 表名; #直接删除索引
alter table 表名 drop index 索引名; #修改表方式删除索引
alter table 表名 drop primary key; #删除主键索引
（2）查看索引
show create table 表名;
show index from 表名;
show keys from 表名;
（3）各字段的含义
| 字段的含义 | |
|---|---|
| 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 | 备注 |
3.遇到 select 查询语句执行速度慢该怎么办？
1）升级 CPU 内存 硬盘 硬件性能
2）对 MySQL 配置进行优化
3）对查询语句的结构进行优化，比如将嵌套子查询优化成表连接查询；或连接表时，可以先用where条件对表进行过滤，然后做表连接
4）进行索引优化：
先使用 explain 分析 select 语句，判断这个查询语句是否正确的使用了索引
再根据查询语句中的 where 条件字段建立相应的单列索引或者多列组合索引（多列组合索引要满足最左原则）
4.事务介绍
定义：事务就是一组数据库操作序列（包含一个或多个SQL操作命令），事务会把所有操作看作是一个不可分割的整体向数据库系统提交或撤销操作，所有操作要么都执行，要么都不执行。
（1）事务的 ACID 特性
事务的ACID特点：ACID，是指在可靠8数据库管理系统（DBMS）中，事务(transaction)应该具有的四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。这是可靠数据库所应具备的几个特性。
事务的 ACID 特性
原子性：事务管理的基础。把事务中的所有操作看作是一个不可分割的工作单元，要么都执行，要么都不执行。
一致性：事务管理的目的。保证事务开始前和事务结束后数据的完整和一致
隔离性：事务管理的手段。使多个事务并发操作同一个表数据时，每个事务都有各自独立的数据空间，事务的执行不会受到其它事务的干扰。可通过设置隔离级别来解决不同的一致性问题。
持久性：事务管理的结果。当事务被提交以后，事务中的命令操作修改的结果会被持久化保存，且不会吧被回滚。
（2）隔离级别
| 隔离级别 | |
|---|---|
| 未提交读 Read Uncommitted | 允许 脏读 不可重复读 幻读 |
| 提交读 Read Committed | 不允许 脏读，允许 不可重复读 幻读 （一般生产环境使用的隔离级别） |
| 可重复读 Repeatable Read | 不允许 脏读 不可重复读，有条件的允许 幻读（InnoDB存储引擎可以通过多版本并发控制MVCC解决幻读问题） |
| 串行读 Serializable | 都不允许，相当于表级锁定，但是会影响数据库的读写效率性能 |
（1）设置隔离级别
事务隔离级别的作用范围分为两种：
全局级：对所有的会话有效
会话级：只对当前的会话有效
set global transaction isolation level 隔离级别名称; #全局级隔离级别，可在所有会话有效，当前会话需要重新登录方可有效
set session transaction isolation level 隔离级别名称; #会话级隔离级别，仅在当前会话中立即有效
（2）查看隔离级别
show global variables like '%isolation%'; #查询全局事务隔离级别
show session variables like '%isolation%'; #查询会话事务隔离级别
（3）事务管理操作：
begin; #显式的开启一个事务
#事务性操作
insert into #增
update 表 set #改
delete from #删
savepoint XX; #在事务中创建回滚点
rollback to XX; #在事务中回滚到指定的回滚点位置
commit; 或 rollback; #提交或回滚 结束事务
（4）自动提交事务
set global/session autocommit = 0/1
#global全局级别，session会话级别，0关闭自动提交，1开启自动提交
show global/session variables like 'autocommit';
5.存储引擎介绍
定义：存储引擎是MySQL数据库的组件，负责执行实际的数据IO操作（数据的存储和提取）。
工作在文件系统之上，数据库的数据会先传输到存储引擎，再按照存储引擎的存储格式保存到文件系统。
常用的存储引擎：InnoDB 、 MyISAM
（1）MyISAM 和 InnoDB 的区别？
（1）MyISAM：不支持事务、外键约束；支持全文索引；锁定类型只支持表级锁定；适合单独的查询和插入的操作；读写会相互阻塞；硬件资源占用较小；数据文件和索引文件是分开存储的，存储成三个文件：表结构文件.frm、数据文件.MYD、索引文件.MYI
使用场景：适用于不需要事务支持，单独的查询或插入数据的业务场景
MyIAM支持的存储格式：静态表、动态表、压缩表
（2）InnoDB：支持事务、外键约束；也支持全文索引；锁定类型支持行级锁定（在全表扫描时仍会表级锁定）；读写并发能力较好；缓存能力较好可以减少磁盘IO的压力；数据文件也是索引文件，存储成：表结构文件.frm、表空间文件.ibd
使用场景：适用于需要事务支持，数据一致性要求较高，数据会频繁更新，读写并发高的业务场景
InnoDB行锁与索引的关系：
InnoDB的行级锁是通过给索引项加锁来实现的。如果对没有索引的字段进行操作会使用全表扫描并表级锁定。
（2）存储引擎管理操作
alter table 表名 engine=innodb/myisam; #针对已存在的表修改存储引擎
create table 表名 (....) engine=innodb/myisam; #新建表时指定存储引擎
set global/session default_storage_engine=innodb/myisam; #设置默认存储引擎
#通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务
vim /etc/my.cnf
default_storage_engine=INnoDB/MyISAM
设置默认存储引擎
查看存储引擎
use 库名;
show create table 表名; #获取数据表的表结构、索引等信息
show table status [from 库名] where name = '表名'; #查看表使用的存储引擎
（3）死锁
定义：死锁是指两个或多个事务在同一个资源上相互占用，并请求对方锁定的资源，从而导致相互阻塞的现象。
如何避免死锁？
1）设置事务的锁等待超时时间 innodb_lock_wait_timeout
2）设置开启死锁检测功能 innodb_deadlock_detect
3）为表建立合理的索引，减少表锁发生的概率
4）如果业务允许，可以降低隔离级别，比如选用 提交读 Read Committed 隔离级别，从而避免间隙锁导致死锁
5）建议开发人员尽量使用更合理的业务逻辑，比如多表操作时以固定顺序访问表，尽量避免同时锁定多个资源
6）建议开发人员尽量保持事务简短，减少对资源的占用时间和占用范围
7）建议开发人员在读多写少的场景下采用乐观锁机制
