【MYSQL】

事务

四大特性（ACID）

• 原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态。
• 隔离性(isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

• 脏读：一个事务读到另外一个事务还没有提交的数据。
• 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
• 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影”。

事务隔离级别

存储引擎：InnoDB

描述：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL5.5之后，InnoDB是默认的 MySQL 存储引擎。
特性：DML操作遵循ACID模型，支持事务;行级锁，提高并发访问性能;支持 外键 FOREIGN KEY约束，保证数据的完整性和正确性;

索引

结构

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

分类

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

聚集索引选取规则:

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

SQL性能

EXPLAIN 执行计划：EXPLAIN 或者 DESC命令获取 MySQL如何执行 SELECT语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

EXPLAIN 执行计划各字段含义：

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

最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

索引失效情况

• 联合索引中，出现范围查询(>，<)，范围查询右侧的列索引失效。
• 在索引列上进行运算操作，索引将失效。
• 字符串类型字段使用时，不加引号，索引将失效。
• 模糊查询中，如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
• 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select*，避免回表。

前缀索引

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘!0，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

• 前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高,唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
  

单列索引与联合索引

• 单列索引:即一个索引只包含单个列。
• 联合索引:即一个索引包含了多个列。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

单列索引情况：根据下图可以看到，查询条件中有phone和name，他们都有单列索引，但是执行explain后发现真正只走了phone的索引，因为phone索引中没有name值，故还需回表走一次聚簇索引。

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

联合索引情况：

SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

索引设计原则

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

SQL优化

insert优化

• 批量插入
  

• 手动提交事务
  

• 主键顺序插入
  

• 大批量插入数据：如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MVSQL数据库提供的load指令进行插入。操作如下:
  

order by优化

①.Using fileson:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort bufer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
②.Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为 usina index，不需要额外排序，操作效率高。

group by优化

①.在分组操作时，可以通过索引来提高效率。
②.分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

一个常见又非常头疼的问题就是 limit 2000000,10，此时需要MySQL排序前2000010 记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。

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

count优化

• count(主键)：InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为nul)。
• count(字段)：没有notnul约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为nul，不为nul，计数累加有not null约束:InnoD8 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
• count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
• count(*)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count()。

update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁。

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU、RAM、I/0)的争用以外，数据也是-种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

分类

MySQL中的锁，按照锁的粒度分，分为以下三类:

• 全局锁:锁定数据库中的所有表。

• 表级锁:每次操作锁住整张表。

• 行级锁:每次操作锁住对应的行数据。

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类:

• 表锁：分为表共享读锁和表独占写锁
  

• 元数据锁(meta datalock，MDL)：

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享);当对表结构进行变更操作的时候，加MDL写锁(排他)。

• 意向锁：

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

• 意向共享锁(IS):由语句 select… lock in share mode添加。
• 意向排他锁(IX):由insert、update、delete、select … for update 添加。
  

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类:

• 行锁(Record Lock):锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
  
  
  默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key锁进行搜索和索引扫描，以防止幻读。
  1.针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
  2.InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么!nnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

• 间隙锁(GapLock):锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

• 临键锁(Next-Key Lock):行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
  默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。
  1.索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁。
  2.索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
  3.索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。