MySQL索引，事务与存储引擎

目录

索引

定义:

作用:

工作方式:

索引的分类和创建

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

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

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

（4）组合索引（单列索引与多列索引）：可以是单列上创建的索引，也可以是在多列上创建的索引。

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

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

事务的 ACID 特性:

原子性:

一致性:

隔离性：

隔离级别:

设置隔离级别:

隔离级别名称:

查看隔离级别:

持久性:

事务管理操作：

存储引擎

MyISAM与InnoDB的区别？

查看表的存储引擎:

死锁:

如何避免死锁?

---

索引

定义:

索引是一个排序的列表，包含索引字段的值和其对应的行记录的数据所在的物理地址

作用:

加快表的查询速度，还可以对字段排序

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

工作方式:

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

有了索引后，会先通过查询条件的字段值找到其索引对应的行记录的数据的物理地址，然后根据物理地址直接访问相应的行记录的数据，就像根据书目录的页码快速找到的内容 创建索引的依据:

1) 表中的记录行数较多时，一般超过300行的表建议要有索引

2）建议在表中的主键字段、外键字段、多表连接使用的公共关键字段、唯一性较好的字段、不经常更新的字段、经常出现在 mere,gromp by,order by 子语句的字段、短小的字段上面创建索引

3) 不建议在唯一性较差的字段、更新太频繁的字段、大文本字段上面创建索引

索引的分类和创建

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

●直接创建索引 CREATE INDEX 索引名 ON 表名 (列名[(length)]); #(列名(length))：length是可选项，下同。如果忽略 length 的值，则使用整个列的值作为索引。如果指定，使用列的前 length 个字符来创建索引，这样有利于减小索引文件的大小。在不损失精确性的情况下，长度越短越好。 #索引名建议以“_index”结尾。

create index name_index on member (name);

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

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

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

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

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

create unique index cardid_index on member(cardid);

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

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

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

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

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

alter table member add primary key (id);

（4）组合索引（单列索引与多列索引）：可以是单列上创建的索引，也可以是在多列上创建的索引。

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

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

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';

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

在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。

查看索引: show create table 表红; show index from 表子; show keys from 表名; 删除索引: drop index 索引名 on 表名; alter table 表名 drop index 索引名; alter table 表名 drop primary key;

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

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

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

事务定义:事务就是一组数据库操作序列(包含一个或者多个SL操作命令)，事务会把所有操作看作是一个不可分制的整体向数据库系统提交或徽消操作，所有操作要么都执行要么都不执行。

事务的 ACID 特性:

原子性、一致性、隔离性、持久性

原子性:

事务管理的基础。把事务中的所有操作看作是一个不可分割的工作单元，要么都执行，要么都不执行。

一致性:

事务管理的目的。保证事务开始前和事务结束后数据的完整和一致

隔离性：

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

隔离级别:

未提交读 read uncommitted 允许 脏读 不可重复读 幻读 提交读 read committed 不允许 脏读，允许 不可重复读 幻读 可重复度 repeatable read 不允许 脏读 不可重复读，有条件的允许 幻读 (InnoDB存储引擎可以不允许) 串行读 Serializable 都不允许，相当于表级锁定，但是会影响数据库的读写效率和性能

设置隔离级别:

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

隔离级别名称:

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

当多个客户端并发地访问同一个表时，可能出现下面的一致性问题： （1）脏读：当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。 （2）不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容） （3）幻读：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有一个没有修改的数据行，就好象发生了幻觉一样。 （4）丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。

事务的隔离级别决定了事务之间可见的级别。 MySQL事务支持如下四种隔离，用以控制事务所做的修改，并将修改通告至其它并发的事务： （1）未提交读(Read Uncommitted（RU）)： 允许脏读，即允许一个事务可以看到其他事务未提交的修改。

（2）提交读(Read Committed（RC）)： 允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。防止脏读。

（3）可重复读(Repeatable Read（RR）)：---mysql默认的隔离级别 确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。可以防止脏读和不可重复读。

（4）串行读(Serializable)：---相当于锁表 完全串行化的读，将一个事务与其他事务完全地隔离。每次读都需要获得表级共享锁，读写相互都会阻塞。可以防止脏读，不可重复读取和幻读，(事务串行化)会降低数据库的效率。

查看隔离级别:

show qlobal variables like 'isolation%';

show session variables like 'isolation%'

持久性:

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

事务管理操作：

begin #开启一个事务

... create database/table insert into update XXX set delete from #事务性操作

savepoint XX #在事务中创建回滚点

rollback to XX #在事务中回滚操作到指定的回滚点位置

commit或rollback #提交或回滚结束事务

自动提交事务

set [global/session] autocommit = 0/1； #0关闭自动提交，1开启自动提交

show [global/session]variables like 'autocommit';

存储引擎

定义:存储引擎是MySQL数据库中的组件，负责执行实际的数据I/0操作（数据的存储和提取)，工作在文件系统之上，数据库的数据会先传到存储引擎，再按照存储引擎的存储格式保存到文件系统。

常用的存储引擎：InnoDB MyISAM

MyISAM与InnoDB的区别？

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)如果业务允许，可以降低隔离级别，比如采用 提交读 隔离级别