• 事务
  • 索引
  • 锁
  • 存储引擎
  • 调优
  • 连接
  • 细碎的点

 

目录

事务

1.什么是事务？

2.自动提交

3.事务的特性(ACID)

4.并发事务存在的问题

5.事务的隔离级别

6.为什么MySQL默认隔离级别是RR？

7.那既然说InnoDB通过MVCC解决了幻读，什么是MVCC？

8.next-key locking 算法

9.锁的降级

索引

1.设计索引的原则

2.什么情况不会用到索引？

3.InnoDB的聚簇索引是什么？优点和缺点是什么？

4.二级索引的好处？

5.覆盖索引

6.哈希索引

7.索引的优点

8.索引的缺点

9.为什么MySQL的索引要使用B+树而不是其它树形结构?比如B树？

锁

1.快照读和当前读

2.共享锁和排他锁

3.意向锁

4.表锁和行锁

存储引擎

1.InnoDB

调优

1.慢查询

分析步骤：

典型<检索不需要数据>情况：

2.向MySQL发送一个请求，MySQL到底做了什么？

3.EXPLAIN 分析查询

select_type - 查询类型

type - MySQL找到需要数据行的方式

possible_keys - 可能用到的索引

key - 使用的索引

key_len 使用索引的长度

rows - 查询需要扫描的行数

Extra - 额外信息

连接

1.连接的种类

其他

1.数据库中的范式

2.char 和 varchar的区别

3.分页查询怎么写？

4.数据库字段类型为什么建议尽量避免NULL

事务

1.什么是事务？

满足ACID特性的一组操作。

2.自动提交

MySQL中的事务都是自动提交的，如果不显式地使用START TRANSACTION来开启事务，那么每一个查询都会被当作一个事务。

3.事务的特性(ACID)

原子性：Atomicity，事务是一个不可再分的工作单位，事务中的操作要么都执行，要么都不执行。

一致性：Consistency，事务总是从一个一致状态转移到另一个一致状态。并发执行的事务，其结果和串行执行的结果一致（正确），那么就说这个并发事务符合一致性。

隔离性：Isolation，事务所做的修改在提交之前，对其他事务是不可见的。

持久性：Durability，事务所做的修改一旦提交，就会永久的保存在数据库中。

4.并发事务存在的问题

1.脏读：事务读取到其他事务未提交的数据

2.不可重复读：事务A读取数据x但还没提交，事务B修改了X或删除了X并提交，事务A再次读取数据时，两次得到的结果不一致。针对update 和 delete。

3.幻读：事务A读取x = 100的数据得到了10条结果，事务B增加了一条x = 100的数据并提交，事务A再次读取时会得到11条结果，导致前后不一致。针对insert。

5.事务的隔离级别

1.未提交读

2.提交读

3.可重复读

MySQL通过MVCC（快照读） + next-key locking (当前读）解决了幻读的问题。

4.可串行化

6.为什么MySQL默认隔离级别是RR？

MySQL在5.0之前binlog只有statement一种格式，这个格式在RC下，会出现主从不一致的情况，因此默认隔离级别是RR.

1.什么是binlog

binlog记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。
binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。

2.binlog的几种格式

  • statement：记录的是修改SQL语句
  • row：记录每行实际数据变更
  • mixed：上面两种的混合。

mysql sum 排序_MySQL

当binlog为statement格式，数据库隔离级别在RC情况下，在master节点先delete后insert，binlog里记录的是先insert后delete(按提交顺序,session2先提交)，slave同步的是binlog，因此从机执行的顺序和主机不一致！就会出现主从不一致。

3.如何解决？

1)设置为RR,加入间隙锁，session2插入时会阻塞。

2)使用binlog的row格式，这个格式5.1才有。

因此MySQL的默认隔离级别是RR。

7.那既然说InnoDB通过MVCC解决了幻读，什么是MVCC？

多版本并发控制。

通过为每一行数据保存两个隐藏的字段（创建时间，删除时间），使得大部分读操作都不需要加锁。

实际上保存的是版本号，每个事物开始都有一个版本号，递增。

SELECT

InnoDB会根据以下两个条件检查每一行

  • 行的创建版本号早于当前版本号，这样保证事物读取的数据是在事务开始之前就存在的，或者事务自己创建的。
  • 行的删除版本号大于当前版本号，或者未定义，这样保证事物发生时该行还没有被删除（修改）。

INSERT

为创建的行添加当前事务的版本号到行的创建版本号。

DELETE

为被删除的行添加当前事务的版本号到删除版本号。

UPDATE

为旧的数据添加当前版本号到删除版本号。

创建一行新纪录，保存当前版本号到创建版本号。

8.next-key locking 算法

next-key locking是一个行锁的算法，除他以外InnoDB还有两个行锁的算法，他们分别是：

1.Record Lock：锁直接加在索引记录上，锁住的是key

2.Gap Lock:间隙锁，锁定索引记录的间隙（不包括记录本身），保证间隙不变，它是针对隔离级别为RR及以上的事务来说的。

3.Next-key locking：上面这两个家伙组合起来就是next-key locking。锁定间隙且锁定记录本身。

之前说，InnoDB通过MVCC解决了幻读的问题，但他是在快照读的层面实现的（简单的select），next-key locking是在当前读层面来实现的，而且默认情况下是针对RR级别的。

在使用next-key locking的时候，当InnoDB扫描索引记录时，会先对索引加上行锁（record lock）然后在索引两边的间隙加上间隙锁（gap lock），这样就不能在间隙的位置进行修改或插入，从而防止幻读的发生。

9.锁的降级

当索引含有唯一属性时，Next-key Locking 会自动降级为Record Lock 用来减少锁定的范围，加大并发的处理速度。但是此种情况只存在于查询【所有的唯一索引列】。如果，唯一索引由多个列组成，而查询是查找多个唯一索引列中的其中一个，那么这种查询由于联合索引的特性，查询是一个范围查询，而不是点查询，所以不会降级处理。

索引

1.设计索引的原则

1.最适合的索引列出现在where子句中，或者连接子句指定的列。

2.使用唯一索引。

3.使用短索引。对于varchar类型的字段，使用前缀索引效率会高。可以指定一个前缀长度，在这个长度内是唯一的。

4.利用最左前缀。

5.不要过度索引，不要建立太多的索引。

6.考虑列上进行比较的类型，索引可以用于<、<=、=、>=、>和between运算，模式具有一个直接前缀的时候可以用于like运算。

7.索引列的顺序非常重要！选择性最高的列放在最前面，选择性：不重复的索引值/总的记录数

8.索引列不能是表达式的一部分，也不能是函数的参数。

9.覆盖索引，可以充分利用二级索引中的主键值来覆盖查询

10.索引和锁：

  • 索引可以让查询锁定更少的行
  • 即使使用索引也有可能锁住不需要的数据，例如索引为<id> 查询为where id < 5 and id<>1 for update，虽然不需要id=1的数据，但mysql执行计划是索引范围扫描，因此<5的都锁住了。
  • InnoDB在二级索引上使用共享锁，访问主键需要排他锁，并且使用select for update比lock in share mode或非锁定查询要慢

2.什么情况不会用到索引？

1.当MySQL估计全表比索引快时，不用索引。

2.当跳过索引中的列。 当索引为<a,b,c> 查询where a = 10 and c= 100，就只会用到索引的第一列。

3.违反最左前缀。当索引为<a,b,c>时，where b  = 100 或 where b = 100 and  c = 100 或where c = 100都不会用到索引。

4.查询中有某个列的范围查询，则这个列的右边都无法用到索引。当索引为<a,b,c>时，查询where a = 10 and b like "bbv%" and c = 98，这个查询只用到索引的前两列<a,b>。

5.前导的模糊查询不会用到索引，where a like "%as"不会，但where a like "as%"可以用。

6.使用聚合函数索引会失效。

7.列类型是字符串，查询条件未加引号。

8.在查询条件中使用OR会失效，可以为每个or的字段都加索引（不太好吧）

3.InnoDB的聚簇索引是什么？优点和缺点是什么？

聚簇索引是InnoDB存储数据的方式，在同一个结构中保存了索引和数据行，数据存放在索引叶子页中。

InnoDB通过主键聚集数据。

优点：

1.可以把相关的数据保存在一起。

由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

2.数据访问更快，因为索引和数据是存放在一起的。（只需要一次查询）

3.使用覆盖索引扫描可以直接使用主键值。

缺点：

1.插入速度严重依赖于查询顺序，使用自增的主键是最快的插入方式。

2.更新代价很高，会强制被更新的行移动到新的位置。

3.导致页分裂。当主键值要求必须在已满（16K）的页中插入一条数据，会导致页分裂，页分裂会占用更多的空间。

4.二级索引可能会比想象的更大，因为二级索引中包含了主键，如果主键很大，那么其他索引都会很大。

5.二级索引的查找需要两次索引查找。自适应哈希索引能减少这样的重复工作。

自适应哈希索引：当InnoDB注意到某些索引值被频繁使用时，他会在内存中基于B+Tree索引之上再创建一个哈希索引。这是一个自动的内部行为，用户无法控制或配置，如果有必要也可以关闭这个功能。

4.二级索引的好处？

二级索引存储了主键的值，虽然会消耗更多的空间，但是这样会减少出现页分裂时二级索引的维护，InnoDB在移动行时无需更新二级索引的id。

5.覆盖索引

优秀的索引应该考虑到整个查询而不单单只是where部分。如果一个索引能覆盖所有需要查询的字段（包括select部分的）那么就称之为覆盖索引。覆盖索引能极大提高性能，索引的条目通常远小于数据的数量，如果只需要读取索引那就能极大减少访问量。

由于聚簇索引，覆盖索引对InnoDB的表特别有用。InnoDB的二级索引在叶子结点保存了主键的值，如果二级索引能覆盖查询则可以避免对主键索引的二次访问。

explain的Extra列可以看到Using index，说明这个查询是索引覆盖查询。

6.哈希索引

InnoDB 引擎有一个特殊的功能叫 “自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

哈希索引能以 O(1) 时间进行查找，但是失去了有序性，它具有以下限制：

1.无法用于排序与分组；

2.只支持精确查找，无法用于部分查找和范围查找

7.索引的优点

  • 创建唯一性索引，保证数据库表中每一行数据的唯一性
  • 大大加快数据的检索速度，这是创建索引的最主要的原因
  • 加速数据库表之间的连接，特别是在实现数据的参考完整性方面特别有意义
  • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
  • 通过使用索引，可以在查询中使用优化隐藏器，提高系统的性能

8.索引的缺点

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
  • 索引需要占用物理空间，除了数据表占用数据空间之外，每一个索引还要占一定的物理空间，如果建立聚簇索引，那么需要的空间就会更大
  • 当对表中的数据进行增加、删除和修改的时候，索引也需要维护，降低数据维护的速度

9.为什么MySQL的索引要使用B+树而不是其它树形结构?比如B树？

因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少。

指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；

锁

1.快照读和当前读

快照读：简单的select操作属于快照读，不加锁（不包括select ... for update,select ... lock in share mode)

例如：select * from table where...

当前读：特殊的select，insert/update/delete操作，属于当前读，要加锁。

例如：

select ... for update

select...lock in share mode

insert...

update...

delete...

在这些操作中，都需要加锁，select ... lock in share mode加的是共享锁（S），其他都是排他锁（X）

2.共享锁和排他锁

共享锁：又叫做读锁，读锁是共享的，用户可以并发读，但不能修改，也就是说，事务A对数据加上共享锁后，别的事务也可以对数据加共享锁，但不能加排他锁。

select...lock in share mode

在select语句后面加上lock in share mode，MySQL对结果的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁：又叫写锁，写锁会阻塞其他的读锁和写锁，也就是当数据被加上排他锁后，再也无法对这个数据加任何锁，直到释放。

select ... for update

在select语句后面加上for update时，MySQL会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

3.意向锁

意向锁：意向锁是表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。

解释之前先说一下，意向锁是InnoDB自动加的，不需要用户干预，而共享锁和排他锁可以用户自己加（for update和lock in share mode）

假设事务A对数据表的某一行加了读锁，那么其他事务就只能读，不能写，也就是不能加排他锁。

此时有一个事务B对表申请一个表锁，如果他申请成功，那么也就意味着事务B对整个表拥有了写的权利，那事务A锁定的那一行不是矛盾了吗？

数据库要避免冲突，那么就需要检查这个表是否被其他事务加了表锁，要是没加，就还得检查这个表的每一行是不是被锁住，这样就需要遍历整个表，效率不高，所以就有了意向锁。

有了意向锁，事务A在给表的某一行加锁前，需要先申请一个意向共享锁，成功后，再对这一行加行锁。

那么事务B再加锁前就需要先判断表上是否加了意向共享锁，如果有，就说明有些行被加了共享锁，这时事务B就阻塞了。

所以这么看来，意向锁的意思就可以这么表达：告诉事务这个表上有其他事务加了共享锁或排他锁。

加了意向共享锁（IS）的表，还可以被其他事务加IS，但被共享锁锁定的那些记录是不能被修改的。

加了意向排他锁（IX）的表，就不能被加任何共享锁和排他锁了。

意向锁是个表级的锁，共享锁和排他锁可以是行级也可以是表级，IX和IS只会和表级的X和S冲突，不会和行级的冲突。

意向锁实现了表锁和行锁的共存（举的那个例子就说明行锁和表锁共存时会有冲突）

并存的概念是指数据库同时支持表、行锁，而不是任何情况都支持一个表中同时有一个事务A持有行锁、又有一个事务B持有表锁，因为表一旦被上了一个表级的写锁，肯定不能再上一个行级的锁。

4.表锁和行锁

刚说S和X锁的时候我们提到了表锁和行锁，现在来解释一下：

表锁（table lock）：是MySQL中最基本的锁策略，且开销最小，他会锁定整张表

行锁（row lock）：给某一行加锁，行锁可以最大程度支持并发处理（同时带来最大的锁开销），行锁只在存储引擎层实现，而MySQL服务器层没有实现。

表锁和行锁是从锁的粒度来分的，X和S是从类型来分的，所以我们之前说，可以有行级的S和X锁，也可以有表级的S和X锁。

存储引擎

1.InnoDB

InnoDB是MySQL默认事务型引擎。

InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。默认级别是RR，并通过间隙锁（next-key locking）策略防止幻读的出现。

InnoDB是基于聚簇索引建立的。

调优

1.慢查询

查询性能低下的最基本原因是访问的数据太多。

分析步骤：

1.确认应用程序是否在检索大量不需要的数据。

2.确认MySQL服务器层时候在分析大量超过需要的行。

典型<检索不需要数据>情况：

1.SELECT *

每次看到这个的时候都需要想想，是不是真的需要表里全部的数据。

我们那个查找下载论文学生的查询，就SELECT * FROM STUDENT。

2.重复查询相同的数据

有的查询总是返回相同的结果，可以考虑用缓存。

2.向MySQL发送一个请求，MySQL到底做了什么？

1.客户端发送一条查询给服务器

2.服务器先检查查询缓存，如果命中缓存，则立刻返回缓存中的结果。否则进入下一阶段。
解析SQL语句之前，如果查询缓存开启，则会优先检查这个缓存，是通过对大小写敏感的哈希查找实现的。如果命中，则在返回结果前会再检查一下用户权限，如果权限没有问题，则会跳过后面的阶段直接返回结果。

3.服务器进行SQL解析、预处理，再由查询优化器生成对应的执行计划。
SQL解析：通过关键字进行SQL语句解析，生成一颗“解析树”。
预处理器：根据MySQL规则进一步检查解析树是否合法。（表和列是否存在，名字和别名是否有歧义）
查询优化器：一条查询可以有很多种执行方式，优化器就是找到最好的执行计划。

4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。

5.返回结果导到客户端。

3.EXPLAIN 分析查询

EXPLAIN可以对SELECT查询进行分析，所有字段如下：

mysql sum 排序_MySQL_02

  • id：SELECT查询的标识符，每个SELECT都会分配一个唯一的标识符
  • select_type：SELECT查询的类型
  • table：查询的是哪个表
  • partitions：匹配的分区
  • type：mysql找到需要的数据行的方式
  • possible_keys：可能用到的索引
  • key：用到的索引
  • key_len：用到的索引的长度。
  • ref：哪个字段或常数和key一起使用。
  • rows：查询一共扫描了多少行，估计值。
  • filtered：此查询条件所过滤数据的百分比。
  • Extra：额外的信息。

select_type - 查询类型

常用的取值有：

  • SIMPLE,
  • PRIMARY, 表示此查询是最外层的查询
  • UNION, 表示此查询是 UNION 的第二或随后的查询
  • DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
  • UNION RESULT, UNION 的结果
  • SUBQUERY, 子查询中的第一个 SELECT
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.

type - MySQL找到需要数据行的方式

常取的值：

const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据，const 查询速度非常快, 因为它仅仅读取一次即可。

eq_ref：此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高。

ref：此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询. 

range：表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。

index：表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据。index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index。

ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.

ALL <  index  <  range ~ index_merge  <  ref  <  eq_ref  <  const  <  system 

possible_keys - 可能用到的索引

表示能用的索引，并不代表真的用了，key表示用到的索引。

key - 使用的索引

查询使用的索引

key_len 使用索引的长度

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的计算规则如下:

  • 字符串
  • char(n): n 字节长度
  • varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
  • 数值类型:
  • TINYINT: 1字节
  • SMALLINT: 2字节
  • MEDIUMINT: 3字节
  • INT: 4字节
  • BIGINT: 8字节
  • 时间类型
  • DATE: 3字节
  • TIMESTAMP: 4字节
  • DATETIME: 8字节
  • 字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

rows - 查询需要扫描的行数

MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

Extra - 额外信息

  • Using index
    "覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
  • Using temporary
    查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

连接

1.连接的种类

1.外连接

  • 左连接 left join (left outer join)

结果包括左表的所有行，如果右表有无法匹配的行则显示null。

mysql sum 排序_MySQL_03

  • 右连接 right join (right outer join)

包括右表所有的行，如果左表又无法匹配的行，则显示null。

mysql sum 排序_MySQL_04

  • 全连接 full join (full outer join)

左表和右表其中一个表中存在匹配，则返回行。

mysql sum 排序_MySQL_05

2.内连接：内联接是用比较运算符比较要连接列的值的联接

join 或 inner join

mysql sum 排序_mysql sum 排序_06

其他

1.数据库中的范式

1NF：属性不可分

2NF：属性完全依赖于主键 [消除部分子函数依赖]

3NF：属性不依赖于其它非主属性 [消除传递依赖]

2.char 和 varchar的区别

char是定长的，varchar是变长的。

varchar在修改的时候可能会比原来长，原页面无法容纳时可能会导致分页。

varchar会保留字符串末尾的空格，char不保留。

3.分页查询怎么写？

select * from employee limit 3, 7; // 返回4-11行

select * from employee limit 3,1; // 返回第4行

SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20

每次都要扫描10020行，然后只要20条记录，性能是不好的。

怎么写效率更好呢？

如果只需要上一页和下一页，可以这么写：

select * from message where id  > 10020

select * from messagewhere id < 10000 order by id desc limit 20;//上一页

这样就只需要扫描20行。

上一页 1 2 3 4 5 6 7 8 9 下一页

怎么实现呢？

还是按照SELECT * FROM message ORDER BY id DESC，按id降序分页，每页20条

假设当前是第10页，当前页条目id最大的是2519，最小的是2500;

当前第10页的sql可以这么写:

SELECT * FROM tb_goods_info WHERE auto_id >=2500 ORDER BY auto_id ASC LIMIT 0,20

第9页：

SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 0,20

第8页：

SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 20,20

第11页：

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 0,20

原理还是一样，记录住当前页id的最大值和最小值，计算跳转页面和当前页相对偏移，由于页面相近，这个偏移量不会很大，这样的话m值相对较小，大大减少扫描的行数。其实传统的limit m,n，相对的偏移一直是第一页，这样的话越翻到后面，效率越差，而上面给出的方法就没有这样的问题。

注意SQL语句里面的ASC和DESC，如果是ASC取出来的结果，显示的时候记得倒置一下。

4.数据库字段类型为什么建议尽量避免NULL

高性能MySQL中明确提到“尽量避免NULL”

原因：

  1. NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回非空行的结果集。
  2. Count(*)会统计包括NULL的行，count(colName)不会统计此列为null的行
  3. NULL 列需要更多的存储空间，需要一个额外的字节作为判断是否为 NULL 的标志位。