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 索引名 (字段);

主键索引

alter table 表名 add primary key (字段);

组合索引

create index 索引名 on 表名 (字段1, 字段2, 字段3,....); alter table 表名 add index 索引名 (字段1, 字段2, 字段3,....);
create unique index 索引名 on 表名 (字段1, 字段2, 字段3,....); alter table 表名 add unique 索引名 (字段1, 字段2, 字段3,....);
select ... from 表名 where 字段1=XX and 字段2=XX and 字段3=XX #用 and 做条件逻辑运算符时，要创建组合索引且要满足最左原则
#用 or 做条件逻辑运算符时，所有字段都要单独创建单列索引

全文索引

create fulltext index 索引名 on 表名 (字段); alter table 表名 add fulltext 索引名 (字段);
支持模糊查询 select ... from 表名 where match(字段) against('查询内容');

查看索引

show create table 表名;
show index from 表名;
show keys from 表名;

删除索引

alter table 表名 drop primary key;
alter table 表名 drop index 索引名;
drop index 索引名 on 表名;

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

先使用 explain 分析 select 查询语句，看 key 字段，确定 select 查询语句是否使用了索引或索引使用是否正确。
然后再根据 select 查询语句使用的条件创建相应的单列索引或者组合索引，组合索引要满足最左原则。

事务

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

事务的 ACID 特性

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

原子性

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

一致性

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

隔离性

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

持久性

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

当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：

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

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

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

（4）丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。

隔离级别

未提交读 read uncommitted 允许脏读不可重复读幻读
提交读 read committed 不允许脏读，允许不可重复读幻读
可重复读 repeatable read 不允许脏读不可重复读，有条件的允许（InnoDB不允许）幻读
串行读 serializable 都不允许，相当于表级锁定，但是会影响数据库的读写效率和性能

mysql默认的事务处理级别是 repeatable read ，而Oracle和SQL Server是 read committed

事务隔离级别的作用范围分为两种：

● 全局级：对所有的会话有效
● 会话级：只对当前的会话有效

设置隔离级别

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

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

事务管理操作

begin; #开启一个事务
.... create database/table insert into update XX set delete from #事务中写操作
savepoint XX; #在事务中创建回滚点
rollback to XX; #在事务中回滚到指定的回滚点
commit; rollback; #提交或回滚结束事务

自动提交事务

set [global|session] autocommit=1|0 #1开启自动提交，0关闭自动提交
show [global|session] variables like 'autocommit';

存储引擎

储存引擎是MySQL数据库中的组件，负责执行实际的数据I/O操作，工作在文件系统之上，数据库的数据会先传到存储引擎，再会按照存储引擎的存储格式保存到文件系统中

常用的存储引擎： MyISAM InnoDB

MyISAM InnoDB的区别

MyISAM：不支持事务、外键约束，只支持表级锁定，适合单独的查询或插入的操作，读写并发能力较弱，支持全文索引，硬件资源占用较小，数据文件和索引文件是分开存储的。存储成三个文件：表结构文件.frm、数据文件.MYD、索引文件.MYI
使用场景：适用于不需要事务处理，单独的查询或插入数据的业务场景

InnoDB：支持事务、外键约束，支持行级锁定（在全表扫描时仍然会表级锁定），读写并发能力较好，支持全文索引（5.5版本之后），缓存能力较好可以减少磁盘IO的压力，数据文件也是索引文件。存储成两个文件：表结构文件.frm、数据文件.ibd
使用场景：适用于需要事务的支持，一致性要求高的，数据会频繁更新，读写并发高的业务场景

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

静态(固定长度)表

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

动态表

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

压缩表

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

查看表的存储引擎

show create table 表名;
show table status from 库名 where name='表名'\G

查看支持的存储的引擎

show engines;

修改表的存储引擎

alter table 表名 engine=MyISAM|InnoDB; #针对于已存在的表修改存储引擎

create table 表名 (....) engine=MyISAM|InnoDB; #新建表时指定存储引擎

修改默认的存储引擎

vim /etc/my.cnf #修改mysql的配置文件
[mysqld]
default-storage-engine=MyISAM|InnoDB; #设置新建的默认存储引擎

如何避免死锁

1）使用更合理的业务逻辑，以固定的顺序访问表和行。
2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
5）为表添加合理的索引。如果不使用索引将会为表的每一行记录添加上锁，死锁的概率大大增大。