MySQL——索引、事务与存储引擎

索引

什么是索引？

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址.

索引的作用

索引的作用：

• 加快表的查询速度，还可以对字段排序
• 可以加快表与表之间的连接。

索引的副作用：

• 会额外占用磁盘空间，更新包含索引的表会花费更多时间，效率会更慢。

索引是如何工作的？

没有索引的情况下，要根据条件查询某行数据时，需要先扫描全表，再定位某行数据的位置

有了索引后，会先通过查询条件的字段值找到其索引对应的行记录的数据的物理地址，然后根据物理地址访问相应的行记录的数据，就像根据书目录的也没快速找到所需的内容

在什么场景要适合创建索引呢？

1. 表中的记录行数较多时，一般超过300行的表建议要有索引
2. 建议在表中的主键字段，外键字段，多表连接使用的公共关键字段，唯一性较好的字段，不经常更新的字段，经常出现在where，group by，order by子语句的字段，短小的字段上面创建索引
3. 不建议在唯一性较差的字段，更新太频繁的字段，大文本字段上面创建索引

索引的类型

普通索引：最基本的索引类型，没有唯一性之类的限制

create index 索引名 on 表名 （字段/列名（指定字符长度））；

alter table  表名 add index 索引名 （字段）；

唯一索引：与普通索引类似，但区别是唯一索引列的每个值都唯一。

唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

create unique index 索引名 on 表名 （字段）；

alter table 表名 add unique 索引名 （字段）；

主键索引：是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。

一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。

create table 表名 (，primary key （字段）)；

alter table 表名 add primary key （字段）；

 组合索引:可以是单列上创建的索引，也可以是在多列上创建的索引。

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

create index 索引名 on 表名 （字段1，字段2，..）；

create unique index 索引名 on 表名 （字段1，字段2，...）；

alter table 表名 index 索引名 （字段1，字段2）；

alter table 表名 unique 索引名 （字段1，字段2）；

select ... from 表名 where 字段1=xx and 字段2=xx..；
#用and做逻辑运算符多字段查询时，要创建组合索引且要满足最左原则
#用or做逻辑运算符多字段查询时，所有字段都要单独创建单索引

全文索引：适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。

create fulltext index 索引名 on 表名 （字段）

#支持模糊查询  

select ... from 表名 where match （字段） against（'单词'）；

 

如何查看索引？

show create table 表名；

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：备注。

删除索引：

drop index 索引名 on 表名；

alter table 表名 drop index 索引名；

alter  table  表名 drop primary key；#删除主键

 

遇到select语句查询速度慢该怎么办

1）先使用explain 分析select 语句，看key字段，确定select语句使用索引或者索引使用是否正确

2）再根据select 语句中where 子语句使用的条件字段创建相应的单列索引或组合索引，组合索引要满足最左原则

 

事务

什么是事务？

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

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

原子性：事务管理的基础

把事务中的所有操作看作是一个不可分割的工作单元要么都执行，要么都不执行 指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

事务是一个完整的操作，事务的各元素是不可分的。

事务中的所有元素必须作为一个整体提交或回滚。

如果事务中的任何元素失败，则整个事务将失败。

一致性：事务管理的目的

保证事务开始前和事务结束后数据的完整和一致 指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

当事务完成时，数据必须处于一致状态。

在事务开始前，数据库中存储的数据处于一致状态。

在正在进行的事务中，数据可能处于不一致的状态。

当事务成功完成时，数据必须再次回到已知的一致状态。

隔离性：事务隔离的手段

使多个事务并发操作同一个表数据时，每个事务都有各自独立的数据空间，事务的执行不会受到其他事务的干扰。可通过设置隔离级别解决不同的一致性问题

持久性：事务管理的结果

当事务被提交以后，事务中的命令操作修改的结果会被持久保存，且不会被回滚

 

隔离级别：

未提交读 read uncommitted： 允许 脏读 不可重复读 幻读

提交读 read committed： 允许 不可重复读 幻读，不允许 脏读

可重复读 repeatable read： 有条件的允许 幻读（innodb存储引擎可以不允许） ， 不允许 脏读 不可重复读

串行读 serializable： 都不允许，相当于表级锁定，但是会影响数据库的读写效率和性能

隔离级别	脏读	不可重复读	幻读
未提交读	√	√	√
提交读	×	√	√
可重复读	×	×	InnoDB不可以
串行读	×	×	×

脏读：

当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据

不可重复读：

指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）

幻读：

一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有一个没有修改的数据行，就好象发生了幻觉一样。

设置隔离级别：

set global transaction isolation level  隔离级别名称；
#全局级别的设置，可在所有会话有效，需要重新登录才可生效

set session transaction isolation level  隔离级别名称；
#会话级别的设置，在当前会话中立即生效

 查看隔离级别：

show global variables like '%isolation%'；
#查看全局级别的

show session variables like '%isolation%'；
#查看会话级别的

 事务管理的操作：

begin；      
#开启一个事务
.....    insert into  updata xx set  delete from    
#事务性操作
savepoint xx；    #在事务中创建回滚点
rollback ro xx；   #在事务中回滚操作到指定的回滚点位置
commit；或 rollback；  #提交或回滚结束事务

事务的自动提交：

set global/session autocommit=0；  #关闭自动提交
set global/session autocommit=1；  #开启自动提交，mysql默认为1
show global/session variables like 'autocommit'；  #查看mysql中的autocommit值

 存储引擎

什么是存储引擎？

存储引擎是mysql数据库中的组件，负载执行实际的数据I/O操作（数据的存储和提取），工作在文件系统之上，数据库的数据会先传到存储引擎，在按照存储引擎的格式保存到文件系统

常用的存储引擎：InnoDB MyISAM

MyISAM：

不支持事务，不支持外键约束，只支持全文索引

访问速度快，对事务完整性没有要求

适合查询、插入为主的应用

在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为：

• .frm 文件存储表结构的定义 ​
• 数据文件的扩展名为.MYD（MYData） ​
• 索引文件的扩展名是.MYI（MYIndex）

MyISAM 表支持 3 种不同的存储格式：

（1）静态(固定长度)表 静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。

（2）动态表 动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。

（3）压缩表 压缩表由 myisamchk 工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

myisam适用的生产场景：

公司业务不需要事务的支持

单方面读取或写入数据比较多的业务

myisam存储引擎数据读写都比较频繁的场景不适合

使用读写并发访问相对较低的业务

数据修改相对较少的业务

对数据业务一致性要求不是非常高的业务

服务器硬件资源相对比较差

InnoDB

支持事务，支持4个事务隔离级别

MySQL从5.5.5版本开始，默认的存储引擎为InnoDB

读写阻塞与事务隔离级别相关

能非常高效的缓存索引和数据

表与主键以族的方式存储

支持分区、表空间，类似oracle数据库

支持外键约束，5.5前不支持全文索引，5.5后支持全文索引

对硬件资源要求还是比较高的场合

 

InnoDB 中不保存表的行数，如 select count(*) from table; 时InnoDB 需要扫描一遍整个表来计算有多少行，但是MyISAM 只要简单的读出保存好的行数即可。

需要注意的是，当count()语句包含 where 条件时 MyISAM 也需要扫描整个表 对于自增长的字段，InnoDB 中必须包含只有该字段的索引但是在 MyISAM表中可以和其他字段一起建立组合索引清空整个表时，InnoDB 是一行一行的删除，效率非常慢MyISAM 则会重建表。

innodb适用生产场景：

业务需要事务的支持 行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成业务数据更新较为频繁的场景 如:论坛，微博等 业务数据一致性要求较高。 如:银行业务 硬件设备内存较大，利用nnoDB较好的缓存能力来提高内存利用率，减少磁盘IO的压力

 

innoDB与MyISAM的区别？

MyISAM：

不支持事务、外键约束，只支持表级锁定，适合单独的查询和插入的操作，读写会相互阻塞，支持全文索引，硬件资源占用较小，数据文件和索引文件是分开存储的（存储成三个文件：表结构文件.frm、数据文件.MYD、索引文件.MYI）

使用场景：适用于不需要事务支持，单独的查询或插入数据的业务场景

InnoDB：

支持事务、外键约束，支持行级锁定（在全表扫描时仍然会表级锁定），读写并发能力较好，支持全文索引（5..5版本之后），缓存能力较好可以减少磁盘io的压力，数据文件也是索引文件（存储成两个文件：表结构文件.frm、数据文件.ibd）

使用场景：适用于需要事务的支持，一致性要求较高，数据会频繁更新，读写并发高的业务场景

 

查看表的存储引擎

show create table 表名；

show table status [from 库名] where name='表名' \G

show engines；

 修改表的存储引擎

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

create table 表名（...）engine=innodb/myisam；  #新建表时指定存储引擎

vim /etc/my.cnf
[mysqld]
default-storage-engine=INNODB/myisam    #设置新建表的默认存储引擎

 什么是死锁？

死锁是指两个或多个事务在同一个资源上相互占用，并请求对方的锁定资源，从而导致恶性循环的现象

如何避免死锁

1）设置事务超时等待时间 innodb_lock_wait_timeout

2）设置开启死锁检测 innodb _deadlock_detect

3）为表添加合理的索引，减少表锁发生的概率

4）如果业务允许，可以降低隔离级别，比如使用 “提交读” 隔离级别

5）建议开发人员尽量使用更合理的业务逻辑

6）建议开发人员尽量保持事务简短

7）建议开发人员在读多写少的场景下使用乐观锁机制