MySQL之事务、存储引擎、索引

---

前言

记录MySQL中事务、存储引擎、索引、视图等概念以及用法。

---

一、事务

1.概念

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
默认MySQL的事务是自动提交的。

2.操作

（1）开启事务

START TRABNSACTION 或 BEGIN;
1

（2）提交事务

COMMIT;
1

（3）回滚事务

ROLLBACK;
1

3.四大特性ACID

（1）原子性（Atomicity）

事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

（2）一致性（Consistency）

事务完成时，必须使所有的数据都保持一致状态。

（3）隔离性（Isolation）

数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

（4）持久性（Durability）

事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

4.并发事务问题

（1）脏读

一个事务读到另一个事务还没有提交的数据。

（2）不可重复读

一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

（3）幻读

一个事务按照条件查询数据时，没有对应得数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻读”。

5.隔离级别

（1）事务隔离级别

Repeatable Read 是MySQL得默认隔离级别。

（2）查看事务隔离级别

select @@global.tx_isolation; //查看系统隔离级别
select @@tx_isolation; //查看会话隔离级别（5.0以上版本）
select @@transaction_isolation; //查看会话隔离级别（8.0以上版本）
1
2
3

（3）设置事务隔离级别

set session transaction isolation level repeatable read; //设置会话隔离级别为而重复读
set session transaction isolation level read uncommitted; //设置会话隔离级别为读未提交
set session transaction isolation level read committed; //设置会话隔离级别为读已提交
1
2
3

（4）隔离级别演示

隔离级别为Read uncommitted，出现的脏读问题示例。

隔离级别为Read committed，出现的不可重复读问题示例。

隔离级别为Repeatable Read，出现的幻读问题示例。

注意：事务隔离级别越高，数据越安全，但是性能越低。

二、存储引擎

1.MySQL体系结构

（1）连接层：最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
（2）服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能在这一层实现，如过程、函数等。
（3）引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，选取合适的存储引擎。
（4）存储层：主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

2.简介

（1）在创建表时，指定存储引擎。

CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	......
	字段n 字段n类型 [COMMENT 字段n注释]
)ENGINE = INNODB [COMMENT 表注释]
1
2
3
4
5

（2）查看当前数据库支持的存储引擎。

SHOW ENGINES;
1

3.InnoDB

（1）概念
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎。
（2）特点
a.DML操作遵循ACID模型，支持事务；
b.行级锁，提高并发访问性能；
c.支持外键FOREIGN KEY约束，保证数据的完整性和正确性。
（3）文件
xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
参数：innodb_file_per_table
（4）逻辑存储结构

4.MyISAM和Memory

MyISAM
（1）介绍
MyISAM是MySQL早期的默认存储引擎。
（2）特点
a.不支持事务，不支持外键；
b.支持表锁，不支持行锁；
c.访问速度快。
（3）文件
xxx.sdi：存储结构信息；
xxx.MYD：存储数据；
xxx.MYI：存储索引。
Memory
（1）介绍
Memory引擎的表数据存储在内存中，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。
（2）特点
a.内存存放；
b.hash索引（默认）。
（3）文件
xxx.sdi：存储表结构信息。

5.区别

6.选择

（1）InnoDB：是Mysql的默认存储引擎。支持事务、外键。（适合要求数据完整性、一致性、增删改查操作）
（2）MyISAM：适合以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高。
（3）MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。（对表的大小有限制，表过大无法缓存在内存中，且无法保障安全性。）

三、索引

1.概述

（1）概念

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

（2）优缺点

2.结构

MySQL的索引是在存储引擎层实现的，不同存储引擎有不同的结构，主要包含以下几种：

不同的引擎对索引的支持情况：

B-tree

B+tree
相对于B-Tree区别：
a.所有的数据都会出现在叶子节点；
b.叶子节点形成一个单向链表。

MySQL中的B+tree
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

Hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
hash索引特点：
1.hash索引只能用于对等比较(=，in),不支持范围查询(between，>，<，…)
2.无法利用索引完成排序操作
3.查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

3.分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则：
如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

4.语法

（1）创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);
1

（2）查看索引

SHOW INDEX FROM table_name;
1

（3）删除索引

DROP INDEX index_name ON table_name;
1

5.性能分析

（1）查看执行频次

show [session|global] status
1

（2）慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认0秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
1
2
3
4

（3）show profiles

（4）explain
EXPLAIN执行计划各字段含义：
Id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
select_type：表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等。
type：表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。
possible_key：显示可能应用在这张表上的索引，一个或多个。
Key：实际使用的索引。如果为NULL，则没有使用索引。
Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确度的前提下，长度越短越好。
rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

6.使用规则

（1）索引列运算
不要在索引列上进行运算操作，索引将失效。
（2）模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
（3）or连接的条件
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
（4）数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
（5）SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
use index：建议使用哪个索引。
ignore index：忽略使用哪个索引。
force index：强制使用哪个索引。
（6）覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。
（7）前缀索引
当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法：

create index idx_xxxx on table_name(column(n));
1

前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
（8）单列索引与联合索引
单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

7.设计原则

（1）针对于数据量大，且查询比较频繁的表建立索引。
（2）针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
（3）尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
（4）如果是字符串类型的字段，字段的长度越长，可以针对于字段的特点，建立前缀索引。
（5）尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
（6）要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
（7）如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

四、SQL优化

1.插入数据

（1）insert优化

· 批量插入
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。
· 手动提交事务
· 主键顺序插入
· List item

2.主键优化

（1）数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）。
（2）页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据过大，会行益出），根据主键排列。
（3）页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
（4）主键设计原则
· 满足业务需求的情况下，尽量降低主键的长度。
· 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
· 尽量不要使用UUID做主键或者其他自然主键，如身份证号。

（5）order by优化
using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

**·**根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
**·**尽量使用覆盖索引。
**·**多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
**·**如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256k）。

（6）group by优化
**·**在分组操作时，可以通过 索引来提高效率。
**·**分组操作时，索引的使用也是满足最左前缀法则的。

（7）limit优化
优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

（8）count优化
· MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count()的时候会直接返回这个数，效率很高；
· InnoDB引擎就麻烦了，它执行count()的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
优化思路：自己计数。

---