分类目录归档:MySQL

MySQL-锁总结

MySQL锁总结

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性;锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。

加锁是消耗资源的,锁的各种操作,包括获得锁、检测锁是否是否已解除、释放锁等。

1、锁机制

共享锁与排他锁

  • 共享锁(读锁):其他事务可以读,但不能写。
  • 排他锁(写锁) :其他事务不能读取,也不能写。

粒度锁

MySQL 不同的存储引擎支持不同的锁机制,所有的存储引擎都以自己的方式显现了锁机制,服务器层完全不了解存储引擎中的锁实现:

  • MyISAM 和 MEMORY 存储引擎采用的是表级锁(table-level locking)
  • BDB 存储引擎采用的是页面锁(page-level locking),但也支持表级锁
  • InnoDB 存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。

默认情况下,表锁和行锁都是自动获得的, 不需要额外的命令。

但是在有的情况下, 用户需要明确地进行锁表或者进行事务的控制, 以便确保整个事务的完整性,这样就需要使用事务控制和锁定语句来完成。

不同粒度锁的比较:
  • 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
    1. 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
    2. 表级锁更适合于以查询为主,并发用户少,只有少量按索引条件更新数据的应用,如Web 应用
  • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
    1. 最大程度的支持并发,同时也带来了最大的锁开销。
    2. 在 InnoDB 中,除单个 SQL 组成的事务外,锁是逐步获得的,这就决定了在 InnoDB 中发生死锁是可能的。
    3. 行级锁只在存储引擎层实现,而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统
  • 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

2、MyISAM 表锁

MyISAM表级锁模式

  • 表共享读锁 (Table Read Lock):不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
  • 表独占写锁 (Table Write Lock):会阻塞其他用户对同一表的读和写操作;

MyISAM 表的读操作与写操作之间,以及写操作之间是串行的。当一个线程获得对一个表的写锁后, 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待,直到锁被释放为止。

默认情况下,写锁比读锁具有更高的优先级:当一个锁释放时,这个锁会优先给写锁队列中等候的获取锁请求,然后再给读锁队列中等候的获取锁请求。 (This ensures that updates to a table are not “starved” even when there is heavy SELECT activity for the table. However, if there are many updates for a table, SELECT statements wait until there are no more updates.)。

这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。同时,一些需要长时间运行的查询操作,也会使写线程“饿死” ,应用中应尽量避免出现长时间运行的查询操作(在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行)。

可以设置改变读锁和写锁的优先级:

  • 通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
  • 通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。
  • 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。
  • 给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获得锁的机会。

MyISAM加表锁方法

MyISAM 在执行查询语句(SELECT)前,会自动给涉及的表加读锁,

在执行更新操作 (UPDATE、DELETE、INSERT 等)前,会自动给涉及的表加写锁,

这个过程并不需要用户干预,因此,用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

在自动加锁的情况下,MyISAM 总是一次获得 SQL 语句所需要的全部锁,这也正是 MyISAM 表不会出现死锁(Deadlock Free)的原因。

MyISAM存储引擎支持并发插入,以减少给定表的读和写操作之间的争用:

如果MyISAM表在数据文件中间没有空闲块,则行始终插入数据文件的末尾。 在这种情况下,你可以自由混合并发使用MyISAM表的INSERT和SELECT语句而不需要加锁——你可以在其他线程进行读操作的时候,同时将行插入到MyISAM表中。 文件中间的空闲快可能是从表格中间删除或更新的行而产生的。 如果文件中间有空闲快,则并发插入会被禁用,但是当所有空闲块都填充有新数据时,它又会自动重新启用。 要控制此行为,可以使用MySQL的concurrent_insert系统变量。

如果你使用LOCK TABLES显式获取表锁,则可以请求READ LOCAL锁而不是READ锁,以便在锁定表时,其他会话可以使用并发插入。

  • 当concurrent_insert设置为0时,不允许并发插入。
  • 当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个线程读表的同时,另一个线程从表尾插入记录。这也是MySQL的默认设置。
  • 当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。
查询表级锁争用情况:

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁的争夺,如果 Table_locks_waited 的值比较高,则说明存在着较严重的表级锁争用情况:

3、InnoDB行级锁和表级锁

InnoDB锁模式

InnoDB 实现了以下两种类型的行锁

  • 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
  • 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁

  • 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

锁模式的兼容情况:

(如果一个事务请求的锁模式与当前的锁兼容, InnoDB 就将请求的锁授予该事务; 反之, 如果两者不兼容,该事务就要等待锁释放。)

InnoDB加锁方法
  • 意向锁是 InnoDB 自动加的, 不需用户干预。
  • 对于 UPDATE、 DELETE 和 INSERT 语句, InnoDB 会自动给涉及数据集加排他锁(X);
  • 对于普通 SELECT 语句,InnoDB 不会加任何锁;
  • 事务可以通过以下语句显式给记录集加共享锁或排他锁:
    1. 共享锁(S):SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他 session 仍然可以查询记录,并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作,则很有可能造成死锁。
    2. 排他锁(X):SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录,但是不能对该记录加共享锁或排他锁,而是等待获得锁
  • 隐式锁定:

InnoDB在事务执行过程中,使用两阶段锁协议:

随时都可以执行锁定,InnoDB会根据隔离级别在需要的时候自动加锁;

锁只有在执行commit或者rollback的时候才会释放,并且所有的锁都是在同一时刻被释放。

  • 显式锁定 :

select for update:

在执行这个 select 查询语句的时候,会将对应的索引访问条目进行上排他锁(X 锁),也就是说这个语句对应的锁就相当于update带来的效果。

select *** for update 的使用场景:为了让自己查到的数据确保是最新数据,并且查到后的数据只允许自己来修改的时候,需要用到 for update 子句。

select lock in share mode :

in share mode 子句的作用就是将查找到的数据加上一个 share 锁,这个就是表示其他的事务只能对这些数据进行简单的select 操作,并不能够进行 DML 操作。

select *** lock in share mode 使用场景:为了确保自己查到的数据没有被其他的事务正在修改,也就是说确保查到的数据是最新的数据,并且不允许其他人来修改数据。但是自己不一定能够修改数据,因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。

性能影响:
select for update 语句,相当于一个 update 语句。在业务繁忙的情况下,如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待,从而影响数据库的并发使用效率。
select lock in share mode 语句是一个给查找的数据上一个共享锁(S 锁)的功能,它允许其他的事务也对该数据上S锁,但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

for update 和 lock in share mode 的区别:

前一个上的是排他锁(X 锁),一旦一个事务获取了这个锁,其他的事务是没法在这些数据上执行 for update ;后一个是共享锁,多个事务可以同时的对相同数据执行 lock in share mode。

InnoDB 行锁实现方式
  • InnoDB 行锁是通过给索引上的索引项加锁来实现的,这一点 MySQL 与 Oracle 不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB 才使用行级锁,否则,InnoDB 将使用表锁!
  • 不论是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
  • 只有执行计划真正使用了索引,才能使用行锁:即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的,如果 MySQL 认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划(可以通过 explain 检查 SQL 的执行计划),以确认是否真正使用了索引。
  • 由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然多个session是访问不同行的记录, 但是如果是使用相同的索引键, 是会出现锁冲突的(后使用这些索引的session需要等待先使用索引的session释放锁后,才能获取锁)。 应用设计的时候要注意这一点。
InnoDB的间隙锁

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。

InnoDB使用间隙锁的目的:

  1. 防止幻读,以满足相关隔离级别的要求;
  2. 满足恢复和复制的需要:

MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句,并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制(复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复)有以下特点:
一是 MySQL 的恢复是 SQL 语句级的,也就是重新执行 BINLOG 中的 SQL 语句。
二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的, 恢复也是按这个顺序进行的。
由此可见,MySQL 的恢复机制要求:在一个事务未提交前,其他并发事务不能插入满足其锁定条件的任何记录,也就是不允许出现幻读。

InnoDB 在不同隔离级别下的一致性读及锁的差异

锁和多版本数据(MVCC)是 InnoDB 实现一致性读和 ISO/ANSI SQL92 隔离级别的手段。

因此,在不同的隔离级别下,InnoDB 处理 SQL 时采用的一致性读策略和需要的锁是不同的:


对于许多 SQL,隔离级别越高,InnoDB 给记录集加的锁就越严格(尤其是使用范围条件的时候),产生锁冲突的可能性也就越高,从而对并发性事务处理性能的 影响也就越大。

因此, 我们在应用中, 应该尽量使用较低的隔离级别, 以减少锁争用的机率。实际上,通过优化事务逻辑,大部分应用使用 Read Commited 隔离级别就足够了。对于一些确实需要更高隔离级别的事务, 可以通过在程序中执行 SET SESSION TRANSACTION ISOLATION
LEVEL REPEATABLE READ 或 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 动态改变隔离级别的方式满足需求。

获取 InnoDB 行锁争用情况

可以通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况:

LOCK TABLES 和 UNLOCK TABLES

Mysql也支持lock tables和unlock tables,这都是在服务器层(MySQL Server层)实现的,和存储引擎无关,它们有自己的用途,并不能替代事务处理。 (除了禁用了autocommint后可以使用,其他情况不建议使用):

  • LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定,则当前线程会等待,直到可以获取所有锁定为止。
  • UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时, 或当与服务器的连接被关闭时,所有由当前线程锁定的表被隐含地解锁

LOCK TABLES语法:

  • 在用 LOCK TABLES 对 InnoDB 表加锁时要注意,要将 AUTOCOMMIT 设为 0,否则MySQL 不会给表加锁;
  • 事务结束前,不要用 UNLOCK TABLES 释放表锁,因为 UNLOCK TABLES会隐含地提交事务;
  • COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁,必须用UNLOCK TABLES 释放表锁。

正确的方式见如下语句: 例如,如果需要写表 t1 并从表 t 读,可以按如下做:

使用LOCK TABLES的场景:

给表显示加表级锁(InnoDB表和MyISAM都可以),一般是为了在一定程度模拟事务操作,实现对某一时间点多个表的一致性读取。(与MyISAM默认的表锁行为类似)

在用 LOCK TABLES 给表显式加表锁时,必须同时取得所有涉及到表的锁,并且 MySQL 不支持锁升级。也就是说,在执行 LOCK TABLES 后,只能访问显式加锁的这些表,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。

其实,在MyISAM自动加锁(表锁)的情况下也大致如此,MyISAM 总是一次获得 SQL 语句所需要的全部锁,这也正是 MyISAM 表不会出现死锁(Deadlock Free)的原因。

例如,有一个订单表 orders,其中记录有各订单的总金额 total,同时还有一个 订单明细表 order_detail,其中记录有各订单每一产品的金额小计 subtotal,假设我们需要检 查这两个表的金额合计是否相符,可能就需要执行如下两条 SQL:

这时,如果不先给两个表加锁,就可能产生错误的结果,因为第一条语句执行过程中, order_detail 表可能已经发生了改变。因此,正确的方法应该是:

(在 LOCK TABLES 时加了“local”选项,其作用就是允许当你持有表的读锁时,其他用户可以在满足 MyISAM 表并发插入条件的情况下,在表尾并发插入记录(MyISAM 存储引擎支持“并发插入”))

4、死锁(Deadlock Free)

  • 死锁产生:
    • 死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环。
    • 当事务试图以不同的顺序锁定资源时,就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
    • 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句,有些存储引擎会产生死锁有些不会——死锁有双重原因:真正的数据冲突;存储引擎的实现方式。
  • 检测死锁:数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

  • 死锁恢复:死锁发生以后,只有部分或完全回滚其中一个事务,才能打破死锁,InnoDB目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁,多数情况下只需要重新执行因死锁回滚的事务即可。

  • 外部锁的死锁检测:发生死锁后,InnoDB 一般都能自动检测到,并使一个事务释放锁并回退,另一个事务获得锁,继续完成事务。但在涉及外部锁,或涉及表锁的情况下,InnoDB 并不能完全自动检测到死锁, 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

  • 死锁影响性能:死锁会影响性能而不是会产生严重错误,因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上,当许多线程等待同一个锁时,死锁检测可能导致速度变慢。 有时当发生死锁时,禁用死锁检测(使用innodb_deadlock_detect配置选项)可能会更有效,这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

MyISAM避免死锁

  • 在自动加锁的情况下,MyISAM 总是一次获得 SQL 语句所需要的全部锁,所以 MyISAM 表不会出现死锁。

InnoDB避免死锁

  • 为了在单个InnoDB表上执行多个并发写入操作时避免死锁,可以在事务开始时通过为预期要修改的每个元祖(行)使用SELECT … FOR UPDATE语句来获取必要的锁,即使这些行的更改语句是在之后才执行的。
  • 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁、更新时再申请排他锁,因为这时候当用户再申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁
  • 如果事务需要修改或锁定多个表,则应在每个事务中以相同的顺序使用加锁语句。 在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会
  • 通过SELECT … LOCK IN SHARE MODE获取行的写锁后,如果当前事务再需要对该记录进行更新操作,则很有可能造成死锁。
  • 改变事务隔离级别

如果出现死锁,可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息,如引发死锁的 SQL 语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

一些优化锁性能的建议

  • 尽量使用较低的隔离级别;
  • 精心设计索引, 并尽量使用索引访问数据, 使加锁更精确, 从而减少锁冲突的机会
  • 选择合理的事务大小,小事务发生锁冲突的几率也更小
  • 给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁
  • 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
  • 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响
  • 不要申请超过实际需要的锁级别
  • 除非必须,查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁,优化事务性能;MVCC只在COMMITTED READ(读提交)和REPEATABLE READ(可重复读)两种隔离级别下工作
  • 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能

乐观锁、悲观锁

  • 乐观锁(Optimistic Lock):假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。

乐观锁, 顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号等机制。乐观锁适用于多读的应用类型,这样可以提高吞吐量,像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

  • 悲观锁(Pessimistic Lock):假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作。

悲观锁,顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁。


参考资源:

《高性能MySQL》

《深入浅出MySQL》

http://www.cnblogs.com/liushuiwuqing/p/3966898.html

https://dev.mysql.com/doc/refman/5.7/en/internal-locking.html

http://www.cnblogs.com/0201zcr/p/4782283.html

MySQL-索引总结

MySQL索引总结

索引原理

索引的优缺点

优点

  • 索引大大减小了服务器需要扫描的数据量。
  • 索引可以帮助服务器避免排序和临时表。
  • 索引可以将随机IO变成顺序IO。
  • 索引对于InnoDB(对索引支持行级锁)非常重要,因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中,InnoDB可以在服务器端过滤掉行后就释放锁,但在早期的MySQL版本中,InnoDB直到事务提交时才会解锁。对不需要的元组的加锁,会增加锁的开销,降低并发性。 InnoDB仅对需要访问的元组加锁,而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做(即索引达不到过滤的目的),MySQL服务器只能对InnoDB返回的数据进行WHERE操作,此时,已经无法避免对那些元组加锁了。如果查询不能使用索引,MySQL会进行全表扫描,并锁住每一个元组,不管是否真正需要。

关于InnoDB、索引和锁:InnoDB在二级索引上使用共享锁(读锁),但访问主键索引需要排他锁(写锁)。

缺点

  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存索引文件。
  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。
  • 如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
  • 对于非常小的表,大部分情况下简单的全表扫描更高效;

索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
因此应该只为最经常查询和最经常排序的数据列建立索引。

MySQL里同一个数据表里的索引总数限制为16个。

索引存储类型

B-Tree索引

InnoDB使用的是B+Tree
B+Tree:每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历。
B+Tree通常意味着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同,很适合查找范围数据。
B-Tree可以对<,<=,=,>,>=,BETWEEN,IN,以及不以通配符开始的LIKE使用索引。

索引查询

可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询,但必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。

假设有如下一个表:

其组合索引包含表中每一行的last_name、first_name和dob列。其结构大致如下:

按索引的最左边前缀(leftmost prefix of the index)来进行查询:
1. 查询必须从索引的最左边的列开始,否则无法使用索引。例如,你不能直接利用索引查找在某一天出生的人。
2. 不能跳过某一索引列。例如,你不能利用索引查找last name为Smith且出生于某一天的人。
3. 存储引擎不能使用索引中范围条件右边的列。例如,如果你的查询语句为WHERE last_name=”Smith” AND first_name LIKE ‘J%’ AND dob=’1976-12-23’,则该查询只会使用索引中的前两列,因为LIKE是范围查询。
4. 匹配全值(Match the full value):对索引中的所有列都指定具体的值。例如,上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen。
5. 匹配最左前缀(Match a leftmost prefix):你可以利用索引查找last name为Allen的人,仅仅使用索引中的第1列。
6. 匹配列前缀(Match a column prefix):例如,你可以利用索引查找last name以J开始的人,这仅仅使用索引中的第1列。
7. 匹配值的范围查询(Match a range of values):可以利用索引查找last name在Allen和Barrymore之间的人,仅仅使用索引中第1列。
8. 匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part):可以利用索引查找last name为Allen,而first name以字母K开始的人。
9. 仅对索引进行查询(Index-only queries):如果查询的列都位于索引中,则不需要再多一次I/O回读元组。(覆盖索引:索引的叶子节点中已经包含要查询的数据,那么就没有必要再回表查询了,如果索引包含满足查询的所有数据,就称为覆盖索引。)

索引排序

也可以利用B-Tree索引进行索引排序(对查询结果进行ORDER BY),必须保证ORDER BY按索引的最左边前缀(leftmost prefix of the index)来进行。

MySQL中,有两种方式生成有序结果集:

  • 按索引顺序扫描
  • 使用filesort

如果explain出来的type列的值为“index”,则说明MYSQL使用了索引扫描来做排序。

按索引顺序扫描

可以利用同一索引同时进行查找和排序操作:

  • 当索引的顺序与ORDER BY中的列顺序相同,且所有的列是同一方向(全部升序或者全部降序)时,可以使用索引来排序。
  • ORDER BY子句和查询型子句的限制是一样的:需要满足索引的最左前缀的要求,有一种情况下ORDER BY子句可以不满足索引的最左前缀要求,那就是前导列为常量时:WHERE子句或者JOIN子句中对前导列指定了常量。
  • 如果查询是连接多个表,仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort文件排序。
使用filesort

当MySQL不能使用索引进行排序时,就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序;如果内存装载不下,它会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个块合并成有序的结果集(实际上就是外排序,使用临时表)。

对于filesort,MySQL有两种排序算法:

  • 两次扫描算法(Two passes)
    先将需要排序的字段和可以直接定位到相关行数据的指针信息取出,然后在设定的内存(通过参数sort_buffer_size设定)中进行排序,完成排序之后再次通过行指针信息取出所需的Columns。

该算法是MySQL4.1之前采用的算法,它需要两次访问数据,尤其是第二次读取操作会导致大量的随机I/O操作。另一方面,内存开销较小。

  • 一次扫描算法(single pass)
    该算法一次性将所需的Columns全部取出,在内存中排序后直接将结果输出。
    从MySQL4.1版本开始使用该算法。它减少了I/O的次数,效率较高,但是内存开销也较大。如果我们将并不需要的Columns也取出来,就会极大地浪费排序过程所需要的内存。

在 MySQL 4.1 之后的版本中,可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种:当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时,MySQL 就会选择使用第一种排序算法,反之,则会选择第二种。

当对连接操作进行排序时,如果ORDER BY仅仅引用第一个表的列,MySQL对该表进行filesort操作,然后进行连接处理,此时,EXPLAIN输出“Using filesort”;否则,MySQL必须将查询的结果集生成一个临时表,在连接完成之后进行filesort操作,此时,EXPLAIN输出“Using temporary;Using filesort”。

为了尽可能地提高排序性能,我们自然更希望使用第二种排序算法,所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。

聚簇索引(cluster index)

一个表只能有一个聚簇索引。

目前,只有solidDB和InnoDB支持聚簇索引,MyISAM不支持聚簇索引。一些DBMS允许用户指定聚簇索引,但是MySQL的存储引擎到目前为止都不支持。

InnoDB的聚簇索引
  1. InnoDB对主键建立聚簇索引。
  2. 如果你不指定主键,InnoDB会用一个具有唯一且非空值的索引来代替。
  3. 如果不存在这样的索引,InnoDB会定义一个隐藏的主键,然后对其建立聚簇索引。

InnoDB默认使用聚簇索引来组织数据,如果你用InnoDB,而且不需要特殊的聚簇索引,一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据。最简单的做法就是使用一个AUTO_INCREMENT的列,这会保证记录按照顺序插入,而且能提高使用primary key进行连接的查询的性能。应该尽量避免随机的聚簇主键,例如字符串主键就是一个不好的选择,它使得插入操作变得随机。

一般来说,DBMS都会以聚簇索引的形式来存储实际的数据,它是其它二级索引的基础:

  • 聚簇索引(primary索引):主键索引
  • 非聚簇索引(second索引):二级索引
聚簇索引结构

聚簇索引的结构大致如下:

  • 聚簇索引:节点页只包含了索引列,叶子页包含了行的全部数据。聚簇索引“就是表”,因此可以不需要独立的行存储。

聚簇索引保证关键字的值相近的元组存储的物理位置也相近(所以字符串类型不宜建立聚簇索引,特别是随机字符串,会使得系统进行大量的移动操作)。

  • 二级索引:叶子节点保存的不是指行的物理位置的指针,而是行的主键值。

这意味着通过二级索引查找行,存储引擎需要:1、找到二级索引的叶子节点获取对应的主键值,2、根据这个主键值去聚簇索引中查找到对应的行。这里需要两次B-Tree查找而不是一次。

覆盖索引对于InnoDB表尤其有用,因为InnoDB使用聚簇索引组织数据,如果二级索引中包含查询所需的数据,就不再需要在聚集索引中查找了。

聚簇索引(InnoDB)和二级索引(MyISAM)数据布局比较:

  • MyISAM
    MyISAM按照插入的顺序在磁盘上存储数据:
    左边为行号(row number),从0开始。因为元组的大小固定,所以MyISAM可以很容易的从表的开始位置找到某一字节的位置。

MyISAM建立的索引结构大致如下:
col1主键索引:
MyISAM不支持聚簇索引,索引中每一个叶子节点仅仅包含行号(row number),且叶子节点按照col1的顺序存储。

col2非主键索引:
在MyISAM中,primary key和其它索引没有什么区别。Primary key仅仅只是一个叫做PRIMARY的唯一,非空的索引而已,叶子节点按照col2的顺序存储。

  • InnoDB
    col1主键索引,即聚簇索引:
    聚簇索引中的每个叶子节点包含主键的值,事务ID,用于事务和MVCC的回滚指针,和余下的列(如col2)。

col2非主键索引,即二级索引:
InnoDB的二级索引的叶子包含主键的值,而不是行指针(row pointers),这样的策略减小了移动数据或者数据页面分裂时维护二级索引的开销,因为InnoDB不需要更新索引的行指针。

聚簇索引+二级索引表 与 非聚簇索引表 的对比

Hash索引

哈希索引基于哈希表实现,只有精确索引所有列的查询才有效。

对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据的指针。

MySQL中,只有Memory存储引擎显示支持hash索引,是Memory表的默认索引类型,尽管Memory表也可以使用B-Tree索引。

Memory存储引擎支持非唯一hash索引,这在数据库领域是罕见的:如果多个值有相同的hash code,索引把它们的行指针用链表保存到同一个hash表项中。

假设创建如下一个表:

包含的数据如下:

假设索引使用hash函数f( ),如下:

此时,索引的结构大概如下:

哈希索引中存储的是:哈希值+数据行指针
当你执行 SELECT lname FROM testhash WHERE fname=’Peter’; MySQL会计算’Peter’的hash值,然后通过它来查询索引的行指针。因为f(‘Peter’) = 8784,MySQL会在索引中查找8784,得到指向记录3的指针。

Hash索引有以下一些限制
  • 由于索引仅包含hash code和记录指针,所以,MySQL不能通过使用索引避免读取记录,即每次使用哈希索引查询到记录指针后都要回读元祖查取数据。
  • 不能使用hash索引排序。
  • Hash索引不支持键的部分匹配,因为是通过整个索引值来计算hash值的。
  • Hash索引只支持等值比较,例如使用=,IN( )和<=>。对于WHERE price>100并不能加速查询。
  • 访问Hash索引的速度非常快,除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候,存储引擎必须遍历链表中所有的行指针,逐行进行比较,直到找到所有符合条件的行。
  • 如果哈希冲突很多的话,一些索引维护操作的代价也会很高。当从表中删除一行时,存储引擎要遍历对应哈希值的链表中的每一行,找到并删除对应行的引用,冲突越多,代价越大。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”,由Mysql自动管理,不需要DBA人为干预。默认情况下为开启,我们可以通过参数innodb_adaptive_hash_index来禁用此特性。

当InnoDB注意到某些索引值被使用得非常频繁时,它会在内存中基于缓冲池中的B+ Tree索引上再创建一个哈希索引,这样就上B-Tree索引也具有哈希索引的一些优点,比如快速的哈希查找。

  • 只能用于等值比较,例如=, <=>,in ;
  • 无法用于排序

InnoDB官方文档显示,启用自适应哈希索引后,读和写性能可以提高2倍,对于辅助索引的连接操作,性能可以提高5倍

空间(R-Tree)索引

  • MyISAM支持空间索引,主要用于地理空间数据类型,例如GEOMETRY。

全文(Full-text)索引

  • 全文索引是MyISAM的一个特殊索引类型,它查找的是文本中的关键词,主要用于全文检索。

索引使用

MySQL建立索引类型

  • 单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。
  • 组合索引,即一个索包含多个列。

索引是在存储引擎中实现的,而不是在服务器层中实现的。所以,每种存储引擎的索引都不一定完全相同,并不是所有的存储引擎都支持所有的索引类型。

普通索引

这是最基本的索引,它没有任何限制。普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。
它有以下几种创建方式:

  • 创建索引
CREATE INDEX indexName ON mytable(username(length)); 

如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。

  • 修改表结构
ALTER mytable ADD INDEX [indexName] ON (username(length));
  • 创建表的时候直接指定
CREATE TABLE mytable(  
  ID INT NOT NULL,   
  username VARCHAR(16) NOT NULL,  
  INDEX [indexName] (username(length))  
);
  • 删除索引的语法:
DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似,不同的就是:普通索引允许被索引的数据列包含重复的值。而唯一索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

它有以下几种创建方式:

  • 创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length));
  • 修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length));
  • 创建表的时候直接指定
CREATE TABLE mytable(  
  ID INT NOT NULL,   
  username VARCHAR(16) NOT NULL,  
  UNIQUE [indexName] (username(length))  
);

主键索引

它是一种特殊的唯一索引,不允许有空值。一个表只能有一个主键。

一般是在建表的时候同时创建主键索引:

CREATE TABLE mytable(  
  ID INT NOT NULL,   
  username VARCHAR(16) NOT NULL,
  PRIMARY KEY(ID)
);

当然也可以用 ALTER 命令。

外键索引

如果为某个外键字段定义了一个外键约束条件,MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

组合索引

为了形象地对比单列索引和组合索引,为表添加多个字段:

CREATE TABLE mytable(
  ID INT NOT NULL,
  username VARCHAR(16) NOT NULL,
  city VARCHAR(50) NOT NULL,
  age INT NOT NULL
);

为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。

建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:

  • usernname,city,age
  • usernname,city
  • usernname

为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引。下面的几个SQL就会用到这个组合索引:

SELECT * FROM mytable WHREE username="admin" AND city="郑州" 
SELECT * FROM mytable WHREE username="admin"

而下面几个则不会用到:

SELECT * FROM mytable WHREE age=20 AND city="郑州" 
SELECT * FROM mytable WHREE city="郑州"

如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。因为虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立索引的时机

一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL的B-Tree只对<,<=,=,>,>=,BETWEEN,IN,以及不以通配符开始的LIKE才会使用索引。

例如:

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州';

此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

正确使用索引

使用(B-Tree)索引时,有以下一些技巧和注意事项:

索引设计

  • 索引字段尽量使用数字型(简单的数据类型)

若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了

  • 尽量不要让字段的默认值为NULL
    在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。

索引不会包含有NULL值的列,只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。

所以我们在数据库设计时尽量不要让字段的默认值为NULL,应该指定列为NOT NULL,除非你想存储NULL。你应该用0、一个特殊的值或者一个空串代替空值。

  • 前缀索引和索引选择性
    对串列进行索引,如果可能应该指定一个前缀长度。

对于BLOB、TEXT或者很长的VARCHAR类型的列,必须使用前缀索引,因为MYSQL不允许索引这些列的完整长度。

前缀索引是一种能使索引更小、更快的有效办法,但另一方面也有其缺点:MySQL无法使用前缀索引做order by和group by,也无法使用前缀索引做覆盖扫描。

一般情况下某个前缀的选择性也是足够高的,足以满足查询性能。

例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。

短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。在绝大多数应用里,数据库中的字符串数据大都以各种各样的名字为主,把索引的长度设置为10~15个字符已经足以把搜索范围缩小到很少的几条数据记录了。

通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。

索引的选择性是指,不重复的索引值(基数)和数据表中的记录总数的比值。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MYSQL在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
决窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长(以便节约空间)。前缀应该足够长,以使得前缀索引的选择性接近于索引整个列。换句话说,前缀的“基数”应该接近于完整列的“基数”。

为了决定前缀的合适长度,需要找到最常见的值的列表,然后和最常见的前缀列表进行比较。例如以下查询:

select count(*) as cnt,city from sakila.city_demo group by city order by cnt desc limit 10;
select count(*) as cnt,left(city,7) as perf  from sakila.city_demo group by city order by cnt desc limit 10;

直到这个前缀的选择性接近完整列的选择性。
计算合适的前缀长度的另一个方法就是计算完整列的选择性,并使前缀的选择性接近于完整列的选择性,如下:

select count(distinct city)/count(*) from sakila.city_demo;
select count(distinct left(city,7))/count(*) from sakila.city_demo;
  • 使用唯一索引
    考虑某列中值的分布。索引的列的基数越大,索引的效果越好。

例如,存放出生日期的列具有不同值,很容易区分各行。而用来记录性别的列,只含有“ M” 和“F”,则对此列进行索引没有多大用处,因为不管搜索哪个值,都会得出大约一半的行。

  • 使用组合索引代替多个列索引
    一个多列索引(组合索引)与多个列索引MySQL在解析执行上是不一样的,如果在explain中看到有索引合并(即MySQL为多个列索引合并优化),应该好好检查一下查询的表和结构是不是已经最优。

  • 注意重复/冗余的索引、不使用的索引
    MySQL允许在相同的列上创建多个索引,无论是有意还是无意的。大多数情况下不需要使用冗余索引。

对于重复/冗余、不使用的索引:可以直接删除这些索引。因为这些索引需要占用物理空间,并且也会影响更新表的性能。

索引使用

  • 如果对大的文本进行搜索,使用全文索引而不要用使用 like ‘%…%’
  • like语句不要以通配符开头

对于LIKE:在以通配符%和_开头作查询时,MySQL不会使用索引。like操作一般在全文索引中会用到(InnoDB数据表不支持全文索引)。
例如下句会使用索引:

SELECT * FROM mytable WHERE username like 'admin%'

而下句就不会使用:

SELECT * FROM mytable WHEREt Name like '%admin' 
  • 不要在列上进行运算
    索引列不能是表达式的一部分,也不是是函数的参数。

例如以下两个查询无法使用索引:
1)表达式:

select actor_id from sakila.actor where actor_id+1=5;

2)函数参数:select … where TO_DAYS(CURRENT_DATE) – TO_DAYS(date_col)<=10;
– 尽量不要使用NOT IN、<>、!= 操作

应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

对于not in,可以用not exists或者(外联结+判断为空)来代替;很多时候用 exists 代替 in 是一个好的选择:

select num from a where num in(select num from b) 

用下面的语句替换:

select num from a where exists(select 1 from b where num=a.num)

对于<>,用其它相同功能的操作运算代替,如a<>0 改为 a>0 or a<0

  • or条件

用 or 分割开的条件, 如果 or 前的条件中的列有索引, 而后面的列中没有索引, 那么涉及到的索引都不会被用到

应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num=10 or num=20 

可以这样查询:

select id from t where num=10 union all select id from t where num=20
  • 组合索引的使用要遵守“最左前缀”原则’

组合索引:当不需要考虑排序和分组时,将选择性最高的列放在前面通常是最好的。

例子:

  1. 查询必须从索引的最左边的列开始,否则无法使用索引。例如,你不能直接利用索引查找在某一天出生的人。
  2. 不能跳过某一索引列。例如,你不能利用索引查找last name为Smith且出生于某一天的人。
  3. 存储引擎不能使用索引中范围条件右边的列。例如,如果你的查询语句为WHERE last_name=”Smith” AND first_name LIKE ‘J%’ AND dob=’1976-12-23’,则该查询只会使用索引中的前两列,因为LIKE是范围查询。
  • 使用索引排序时,ORDER BY也要遵守“最左前缀”原则
  1. 当索引的顺序与ORDER BY中的列顺序相同,且所有的列是同一方向(全部升序或者全部降序)时,可以使用索引来排序。
  2. ORDER BY子句和查询型子句的限制是一样的:需要满足索引的最左前缀的要求,有一种情况下ORDER BY子句可以不满足索引的最左前缀要求,那就是前导列为常量时:WHERE子句或者JOIN子句中对前导列指定了常量。
  3. 如果查询是连接多个表,仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort文件排序。
  • 如果列类型是字符串,那么一定记得在 where 条件中把字符常量值用引号引起来,否则的话即便这个列上有索引,MySQL 也不会用到的,因为MySQL 默认把输入的常量值进行转换以后才进行检索。 例如:


  • 任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段
  • 如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引。当索引列有大量数据重复时,查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

索引性能测试与索引优化

只有当数据库里已经有了足够多的测试数据时,它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录,它们往往在执行完第一条查询命令之后就被全部加载到内存里,这将使后续的查询命令都执行得非常快——不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了 MySQL服务器上的内存总量时,数据库的性能测试结果才有意义。

在不确定应该在哪些数据列上创建索引的时候,人们从EXPLAIN SELECT命令那里往往可以获得一些帮助。这其实只是简单地给一条普通的SELECT命令加一个EXPLAIN关键字作为前缀而已。有了这个关键字,MySQL将不是去执行那条SELECT命令,而是去对它进行分析。MySQL将以表格的形式把查询的执行过程和用到的索引(如果有的话)等信息列出来。

查看索引使用情况

  • 如果索引正在工作,Handler_read_key 的值将很高,这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用。
  • Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描, Handler_read_rnd_next 的值较高,则通常说明表索引不正确或写入的查询没有利用索引。

具体如下:

从上面的例子中可以看出,目前使用的 MySQL 数据库的索引情况并不理想。


参考资料:

《深入浅出MySQL》

《高性能MySQL》

http://blog.csdn.net/xifeijian/article/details/20557921

http://blog.csdn.net/xlgen157387/article/details/44156679

MySQL-事务总结

数据库事务与MySQL事务总结

事务特点:ACID

从业务角度出发,对数据库的一组操作要求保持4个特征:

  • Atomicity(原子性):一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作。
  • Consistency(一致性):数据库总是从一个一致性状态转换到另一个一致状态。下面的银行列子会说到。
  • Isolation(隔离性):通常来说,一个事务所做的修改在最终提交以前,对其他事务是不可见的。注意这里的“通常来说”,后面的事务隔离级级别会说到。
  • Durability(持久性):一旦事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,修改的数据也不会丢失。(持久性的安全性与刷新日志级别也存在一定关系,不同的级别对应不同的数据安全级别。)

为了更好地理解ACID,以银行账户转账为例:

START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233276;
UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
COMMIT;
  • 原子性:要么完全提交(10233276的checking余额减少200,savings 的余额增加200),要么完全回滚(两个表的余额都不发生变化)
  • 一致性:这个例子的一致性体现在 200元不会因为数据库系统运行到第3行之后,第4行之前时崩溃而不翼而飞,因为事务还没有提交。
  • 隔离性:允许在一个事务中的操作语句会与其他事务的语句隔离开,比如事务A运行到第3行之后,第4行之前,此时事务B去查询checking余额时,它仍然能够看到在事务A中被减去的200元(账户钱不变),因为事务A和B是彼此隔离的。在事务A提交之前,事务B观察不到数据的改变。
  • 持久性:这个很好理解。
  • 事务的隔离性是通过锁、MVCC等实现 (MySQL锁总结
  • 事务的原子性、一致性和持久性则是通过事务日志实现(见下)

事务的隔离级别

并发事务带来的问题

  • 更新丢失(Lost Update):当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题 --最后的更新覆盖了由其他事务所做的更新。例如,两个编辑人员制作了同一 文档的电子副本。每个编辑人员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。 最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前,另一个编辑人员不能访问同 一文件,则可避免此问题。
  • 脏读(Dirty Reads):一个事务正在对一条记录做修改,在这个事务完成并提交前, 这条记录的数据就处于不一致状态; 这时, 另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
  • 不可重复读(Non-Repeatable Reads):一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读” 。
  • 幻读 (Phantom Reads): 一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读” 。

幻读和不可重复读的区别:

  • 不可重复读的重点是修改:在同一事务中,同样的条件,第一次读的数据和第二次读的数据不一样。(因为中间有其他事务提交了修改)
  • 幻读的重点在于新增或者删除:在同一事务中,同样的条件,,第一次和第二次读出来的记录数不一样。(因为中间有其他事务提交了插入/删除)

并发事务处理带来的问题的解决办法:

  • “更新丢失”通常是应该完全避免的。但防止更新丢失,并不能单靠数据库事务控制器来解决,需要应用程序对要更新的数据加必要的锁来解决,因此,防止更新丢失应该是应用的责任。

  • “脏读” 、 “不可重复读”和“幻读” ,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决:

    • 一种是加锁:在读取数据前,对其加锁,阻止其他事务对数据进行修改。
    • 另一种是数据多版本并发控制(MultiVersion Concurrency Control,简称 MVCC 或 MCC),也称为多版本数据库:不用加任何锁, 通过一定机制生成一个数据请求时间点的一致性数据快照 (Snapshot), 并用这个快照来提供一定级别 (语句级或事务级) 的一致性读取。从用户的角度来看,好象是数据库可以提供同一数据的多个版本。

SQL标准定义了4类隔离级别,每一种级别都规定了一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的。低级别的隔离级一般支持更高的并发处理,并拥有更低的系统开销。

第1级别:Read Uncommitted(读取未提交内容)

  • 所有事务都可以看到其他未提交事务的执行结果
  • 本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少
  • 该级别引发的问题是——脏读(Dirty Read):读取到了未提交的数据

第2级别:Read Committed(读取提交内容)

  • 这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)
  • 它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变
  • 这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read):不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。导致这种情况的原因可能有:
    • 有一个交叉的事务有新的commit,导致了数据的改变;
    • 一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit

第3级别:Repeatable Read(可重读)

  • 这是MySQL的默认事务隔离级别
  • 它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行
  • 此级别可能出现的问题——幻读(Phantom Read):当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行
  • InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决幻读问题;InnoDB还通过间隙锁解决幻读问题
多版本并发控制 :

Mysql的大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性考虑,一般都同时实现了多版本并发控制(MVCC),包括Oracle、PostgreSQL。不过实现各不相同。

MVCC的实现是通过保存数据在某一个时间点快照来实现的。也就是说不管实现时间多长,每个事物看到的数据都是一致的。

分为乐观(optimistic)并发控制和悲观(pressimistic)并发控制。

MVCC是如何工作的:

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现。这两个列一个保存了行的创建时间,一个保存行的过期时间(删除时间)。当然存储的并不是真实的时间而是系统版本号(system version number)。每开始一个新的事务,系统版本号都会自动新增。事务开始时刻的系统版本号会作为事务的版本号,用来查询到每行记录的版本号进行比较。

REPEATABLE READ(可重读)隔离级别下MVCC如何工作:

  • SELECT

InnoDB会根据以下条件检查每一行记录:

  1. InnoDB只查找版本早于当前事务版本的数据行,这样可以确保事务读取的行要么是在开始事务之前已经存在要么是事务自身插入或者修改过的
  2. 行的删除版本号要么未定义,要么大于当前事务版本号,这样可以确保事务读取到的行在事务开始之前未被删除

只有符合上述两个条件的才会被查询出来

  • INSERT

InnoDB为新插入的每一行保存当前系统版本号作为行版本号

  • DELETE

InnoDB为删除的每一行保存当前系统版本号作为行删除标识

  • UPDATE

InnoDB为插入的一行新纪录保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为删除标识,保存这两个版本号,使大多数操作都不用加锁。使数据操作简单,性能很好,并且能保证只会读取到复合要求的行。不足之处是每行记录都需要额外的存储空间,需要做更多的行检查工作和一些额外的维护工作。

MVCC只在COMMITTED READ(读提交)和REPEATABLE READ(可重复读)两种隔离级别下工作。

可以认为MVCC是行级锁一个变种,但是他很多情况下避免了加锁操作,开销更低。虽然不同数据库的实现机制有所不同,但大都实现了非阻塞的读操作(读不用加锁,且能避免出现不可重复读和幻读),写操作也只锁定必要的行(写必须加锁,否则不同事务并发写会导致数据不一致)。

第4级别:Serializable(可串行化)

  • 这是最高的隔离级别
  • 它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。MySQL锁总结
  • 在这个级别,可能导致大量的超时现象和锁竞争

隔离级别比较

各具体数据库并不一定完全实现了上述 4 个隔离级别,例如:

  • Oracle 只提供 Read committed 和 Serializable 两个标准隔离级别,另外还提供自己定义的 Read only 隔离级别;

  • SQL Server 除支持上述 ISO/ANSI SQL92 定义的 4 个隔离级别外,还支持一个叫做“快照”的隔离级别,但严格来说它是一个用 MVCC 实现的 Serializable 隔离级别。

  • MySQL 支持全部 4 个隔离级别,但在具体实现时,有一些特点,比如在一些隔离级别下是采用 MVCC一致性读,但某些情况下又不是。

    • Mysql可以通过执行 set transaction isolation level命令来设置隔离级别,新的隔离级别会在下一个事务开始的时候生效。 例如:set session transaction isolation level read committed;

事务日志

事务日志可以帮助提高事务效率:

  • 使用事务日志,存储引擎在修改表的数据时只需要修改其内存拷贝,再把该修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁盘。
  • 事务日志采用的是追加的方式,因此写日志的操作是磁盘上一小块区域内的顺序I/O,而不像随机I/O需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多。
  • 事务日志持久以后,内存中被修改的数据在后台可以慢慢刷回到磁盘。
  • 如果数据的修改已经记录到事务日志并持久化,但数据本身没有写回到磁盘,此时系统崩溃,存储引擎在重启时能够自动恢复这一部分修改的数据。

目前来说,大多数存储引擎都是这样实现的,我们通常称之为预写式日志(Write-Ahead Logging),修改数据需要写两次磁盘。

Mysql中的事务实现原理

事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。mysql中支持事务的存储引擎有innoDB和NDB。

innoDB是mysql默认的存储引擎,默认的隔离级别是RR(Repeatable Read),并且在RR的隔离级别下更进一步,通过多版本并发控制(MVCC,Multiversion Concurrency Control )解决不可重复读问题,加上间隙锁(也就是并发控制)解决幻读问题。因此innoDB的RR隔离级别其实实现了串行化级别的效果,而且保留了比较好的并发性能。

事务的隔离性是通过锁实现,而事务的原子性、一致性和持久性则是通过事务日志实现。说到事务日志,不得不说的就是redo和undo。

1.redo log

在innoDB的存储引擎中,事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时,事务中的操作,都会先写入存储引擎的日志缓冲中,在事务提交之前,这些缓冲的日志都需要提前刷新到磁盘上持久化,这就是DBA们口中常说的“日志先行”(Write-Ahead Logging)。当事务提交之后,在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机,那么当系统重启进行恢复时,就可以根据redo log中记录的日志,把数据库恢复到崩溃前的一个状态。未完成的事务,可以继续提交,也可以选择回滚,这基于恢复的策略而定。

在系统启动的时候,就已经为redo log分配了一块连续的存储空间,以顺序追加的方式记录Redo Log,通过顺序IO来改善性能。所有的事务共享redo log的存储空间,它们的Redo Log按语句的执行顺序,依次交替的记录在一起。如下一个简单示例:

记录1:<trx1, insert…>

记录2:<trx2, delete…>

记录3:<trx3, update…>

记录4:<trx1, update…>

记录5:<trx3, insert…>

2.undo log

undo log主要为事务的回滚服务。在事务执行的过程中,除了记录redo log,还会记录一定量的undo log。undo log记录了数据在每个操作前的状态,如果事务执行过程中需要回滚,就可以根据undo log进行回滚操作。单个事务的回滚,只会回滚当前事务做的操作,并不会影响到其他的事务做的操作。

以下是undo+redo事务的简化过程

假设有2个数值,分别为A和B,值为1,2

1. start transaction;
\2. 记录 A=1 到undo log;
\3. update A = 3;
\4. 记录 A=3 到redo log;
\5. 记录 B=2 到undo log;
\6. update B = 4;
\7. 记录B = 4 到redo log;
\8. 将redo log刷新到磁盘
\9. commit

在1-8的任意一步系统宕机,事务未提交,该事务就不会对磁盘上的数据做任何影响。如果在8-9之间宕机,恢复之后可以选择回滚,也可以选择继续完成事务提交,因为此时redo log已经持久化。若在9之后系统宕机,内存映射中变更的数据还来不及刷回磁盘,那么系统恢复之后,可以根据redo log把数据刷回磁盘。

所以,redo log其实保障的是事务的持久性和一致性,而undo log则保障了事务的原子性。

Mysql中的事务使用

MySQL的服务层不管理事务,而是由下层的存储引擎实现。比如InnoDB。

MySQL支持本地事务的语句:

START TRANSACTION | BEGIN [WORK] 
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] 
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] 
SET AUTOCOMMIT = {0 | 1}
  • START TRANSACTION 或 BEGIN 语句:开始一项新的事务。
  • COMMIT 和 ROLLBACK:用来提交或者回滚事务。
  • CHAIN 和 RELEASE 子句:分别用来定义在事务提交或者回滚之后的操作,CHAIN 会立即启动一个新事物,并且和刚才的事务具有相同的隔离级别,RELEASE 则会断开和客户端的连接。
  • SET AUTOCOMMIT 可以修改当前连接的提交方式, 如果设置了 SET AUTOCOMMIT=0,则设置之后的所有事务都需要通过明确的命令进行提交或者回滚

事务使用注意点:

  • 如果在锁表期间,用 start transaction 命令开始一个新事务,会造成一个隐含的 unlock
    tables 被执行。
  • 在同一个事务中,最好不使用不同存储引擎的表,否则 ROLLBACK 时需要对非事
    务类型的表进行特别的处理,因为 COMMIT、ROLLBACK 只能对事务类型的表进行提交和回滚。
  • 和 Oracle 的事务管理相同,所有的 DDL 语句是不能回滚的,并且部分的 DDL 语句会造成隐式的提交。
  • 在事务中可以通过定义 SAVEPOINT(例如:mysql> savepoint test; 定义 savepoint,名称为 test),指定回滚事务的一个部分,但是不能指定提交事务的一个部分。对于复杂的应用,可以定义多个不同的 SAVEPOINT,满足不同的条件时,回滚
    不同的 SAVEPOINT。需要注意的是,如果定义了相同名字的 SAVEPOINT,则后面定义的SAVEPOINT 会覆盖之前的定义。对于不再需要使用的 SAVEPOINT,可以通过 RELEASE SAVEPOINT 命令删除 SAVEPOINT, 删除后的 SAVEPOINT, 不能再执行 ROLLBACK TO SAVEPOINT命令。

自动提交(autocommit):
Mysql默认采用自动提交模式,可以通过设置autocommit变量来启用或禁用自动提交模式

  • 隐式锁定

InnoDB在事务执行过程中,使用两阶段锁协议:

随时都可以执行锁定,InnoDB会根据隔离级别在需要的时候自动加锁;

锁只有在执行commit或者rollback的时候才会释放,并且所有的锁都是在同一时刻被释放。

  • 显式锁定

InnoDB也支持通过特定的语句进行显示锁定(存储引擎层):

select ... lock in share mode //共享锁 
select ... for update //排他锁 

MySQL Server层的显示锁定:

lock table和unlock table

(更多阅读:MySQL锁总结

MySQL对分布式事务的支持

分布式事务的实现方式有很多,既可以采用innoDB提供的原生的事务支持,也可以采用消息队列来实现分布式事务的最终一致性。这里我们主要聊一下innoDB对分布式事务的支持。

MySQL 从 5.0.3 开始支持分布式事务,当前分布式事务只支持 InnoDB 存储引擎。一个分布式事务会涉及多个行动,这些行动本身是事务性的。所有行动都必须一起成功完成,或者一起被回滚。

如图,mysql的分布式事务模型。模型中分三块:应用程序(AP)、资源管理器(RM)、事务管理器(TM):

  • 应用程序:定义了事务的边界,指定需要做哪些事务;
  • 资源管理器:提供了访问事务的方法,通常一个数据库就是一个资源管理器;
  • 事务管理器:协调参与了全局事务中的各个事务。

分布式事务采用两段式提交(two-phase commit)的方式:

  • 第一阶段所有的事务节点开始准备,告诉事务管理器ready。
  • 第二阶段事务管理器告诉每个节点是commit还是rollback。如果有一个节点失败,就需要全局的节点全部rollback,以此保障事务的原子性。

分布式事务(XA 事务)的 SQL 语法主要包括:

XA {START|BEGIN} xid [JOIN|RESUME]

虽然 MySQL 支持分布式事务,但是在测试过程中,还是发现存在一些问题:
如果分支事务在达到 prepare 状态时,数据库异常重新启动,服务器重新启动以后,可以继续对分支事务进行提交或者回滚得操作,但是提交的事务没有写 binlog,存在一定的隐患,可能导致使用 binlog 恢复丢失部分数据。如果存在复制的数据库,则有可能导致主从数据库的数据不一致。

如果分支事务在执行到 prepare 状态时,数据库异常,且不能再正常启动,需要使用备份和 binlog 来恢复数据,那么那些在 prepare 状态的分支事务因为并没有记录到 binlog,所以不能通过 binlog 进行恢复,在数据库恢复后,将丢失这部分的数据。

如果分支事务的客户端连接异常中止,那么数据库会自动回滚未完成的分支事务,如果此时分支事务已经执行到 prepare 状态, 那么这个分布式事务的其他分支可能已经成功提交,如果这个分支回滚,可能导致分布式事务的不完整,丢失部分分支事务的内容。
总之, MySQL 的分布式事务还存在比较严重的缺陷, 在数据库或者应用异常的情况下,
可能会导致分布式事务的不完整。如果应用对于数据的完整性要求不是很高,则可以考虑使
用。如果应用对事务的完整性有比较高的要求,那么对于当前的版本,则不推荐使用分布式
事务。

参考来源:
《高性能MySQL》
《深入浅出MySQL》
http://www.cnblogs.com/maypattis/p/5628355.html http://www.cnblogs.com/snsdzjlz320/p/5761387.html http://blog.csdn.net/maybenever/article/details/77891834

MySQL数据库经典面试题解析

MySQL数据库经典面试题解析

1. MySQL 索引使用有哪些注意事项呢?

可以从三个维度回答这个问题:索引哪些情况会失效,索引不适合哪些场景,索引规则

索引哪些情况会失效

  • 查询条件包含or,可能导致索引失效
  • 如何字段类型是字符串,where时一定用引号括起来,否则索引失效
  • like通配符可能导致索引失效。
  • 联合索引,查询时的条件列不是联合索引中的第一个列,索引失效。
  • 在索引列上使用mysql的内置函数,索引失效。
  • 对索引列运算(如,+、-、*、/),索引失效。
  • 索引字段上使用(!= 或者 < >,not in)时,可能会导致索引失效。
  • 索引字段上使用is null, is not null,可能导致索引失效。
  • 左连接查询或者右连接查询查询关联的字段编码格式不一样,可能导致索引失效。
  • mysql估计使用全表扫描要比使用索引快,则不使用索引。

索引不适合哪些场景

  • 数据量少的不适合加索引
  • 更新比较频繁的也不适合加索引
  • 区分度低的字段不适合加索引(如性别)

索引的一些潜规则

  • 覆盖索引
  • 回表
  • 索引数据结构(B+树)
  • 最左前缀原则
  • 索引下推

2. MySQL 遇到过死锁问题吗,你是如何解决的?

我排查死锁的一般步骤是酱紫的:

  • 查看死锁日志show engine innodb status;
  • 找出死锁Sql
  • 分析sql加锁情况
  • 模拟死锁案发
  • 分析死锁日志
  • 分析死锁结果

3. 日常工作中你是怎么优化SQL的?

可以从这几个维度回答这个问题:

  • 加索引
  • 避免返回不必要的数据
  • 适当分批量进行
  • 优化sql结构
  • 分库分表
  • 读写分离

4. 说说分库与分表的设计

分库分表方案,分库分表中间件,分库分表可能遇到的问题

分库分表方案:

  • 水平分库:以字段为依据,按照一定策略(hash、range等),将一个库中的数据拆分到多个库中。
  • 水平分表:以字段为依据,按照一定策略(hash、range等),将一个表中的数据拆分到多个表中。
  • 垂直分库:以表为依据,按照业务归属不同,将不同的表拆分到不同的库中。
  • 垂直分表:以字段为依据,按照字段的活跃性,将表中字段拆到不同的表(主表和扩展表)中。

常用的分库分表中间件:

  • sharding-jdbc(当当)
  • Mycat
  • TDDL(淘宝)
  • Oceanus(58同城数据库中间件)
  • vitess(谷歌开发的数据库中间件)
  • Atlas(Qihoo 360)

分库分表可能遇到的问题

  • 事务问题:需要用分布式事务啦
  • 跨节点Join的问题:解决这一问题可以分两次查询实现
  • 跨节点的count,order by,group by以及聚合函数问题:分别在各个节点上得到结果后在应用程序端进行合并。
  • 数据迁移,容量规划,扩容等问题
  • ID问题:数据库被切分后,不能再依赖数据库自身的主键生成机制啦,最简单可以考虑UUID
  • 跨分片的排序分页问题(后台加大pagesize处理?)

5. InnoDB与MyISAM的区别

  • InnoDB支持事务,MyISAM不支持事务
  • InnoDB支持外键,MyISAM不支持外键
  • InnoDB 支持 MVCC(多版本并发控制),MyISAM 不支持
  • select count(*) from table时,MyISAM更快,因为它有一个变量保存了整个表的总行数,可以直接读取,InnoDB就需要全表扫描。
  • Innodb不支持全文索引,而MyISAM支持全文索引(5.7以后的InnoDB也支持全文索引)
  • InnoDB支持表、行级锁,而MyISAM支持表级锁。
  • InnoDB表必须有主键,而MyISAM可以没有主键
  • Innodb表需要更多的内存和存储,而MyISAM可被压缩,存储空间较小,。
  • Innodb按主键大小有序插入,MyISAM记录插入顺序是,按记录插入顺序保存。
  • InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全,与 MyISAM 比 InnoDB 写的效率差一些,并且会占用更多的磁盘空间以保留数据和索引
  • InnoDB 属于索引组织表,使用共享表空间和多表空间储存数据。MyISAM用.frm.MYD.MTI来储存表定义,数据和索引。

6. 数据库索引的原理,为什么要用 B+树,为什么不用二叉树?

可以从几个维度去看这个问题,查询是否够快,效率是否稳定,存储数据多少,以及查找磁盘次数,为什么不是二叉树,为什么不是平衡二叉树,为什么不是B树,而偏偏是B+树呢?

为什么不是一般二叉树?

如果二叉树特殊化为一个链表,相当于全表扫描。平衡二叉树相比于二叉查找树来说,查找效率更稳定,总体的查找速度也更快。

为什么不是平衡二叉树呢?

我们知道,在内存比在磁盘的数据,查询效率快得多。如果树这种数据结构作为索引,那我们每查找一次数据就需要从磁盘中读取一个节点,也就是我们说的一个磁盘块,但是平衡二叉树可是每个节点只存储一个键值和数据的,如果是B树,可以存储更多的节点数据,树的高度也会降低,因此读取磁盘的次数就降下来啦,查询效率就快啦。

那为什么不是B树而是B+树呢?

  • 1)B+树非叶子节点上是不存储数据的,仅存储键值,而B树节点中不仅存储键值,也会存储数据。innodb中页的默认大小是16KB,如果不存储数据,那么就会存储更多的键值,相应的树的阶数(节点的子节点树)就会更大,树就会更矮更胖,如此一来我们查找数据进行磁盘的IO次数有会再次减少,数据查询的效率也会更快。
  • 2)B+树索引的所有数据均存储在叶子节点,而且数据是按照顺序排列的,链表连着的。那么B+树使得范围查找,排序查找,分组查找以及去重查找变得异常简单。

7. 聚集索引与非聚集索引的区别

  • 一个表中只能拥有一个聚集索引,而非聚集索引一个表可以存在多个。
  • 聚集索引,索引中键值的逻辑顺序决定了表中相应行的物理顺序;非聚集索引,索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
  • 索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块。
  • 聚集索引:物理存储按照索引排序;非聚集索引:物理存储不按照索引排序;

何时使用聚集索引或非聚集索引?

8. limit 1000000 加载很慢的话,你是怎么解决的呢?

方案一:如果id是连续的,可以这样,返回上次查询的最大记录(偏移量),再往下limit

select id,name from employee where id>1000000 limit 10.

方案二:在业务允许的情况下限制页数:

建议跟业务讨论,有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。

方案三:order by + 索引(id为索引)

select id,name from employee order by id  limit 1000000,10
SELECT a.* FROM employee a, (select id from employee where 条件 LIMIT 1000000,10 ) b where a.id=b.id

方案四:利用延迟关联或者子查询优化超多分页场景。(先快速定位需要获取的id段,然后再关联)

9. 如何选择合适的分布式主键方案呢?

  • 数据库自增长序列或字段。
  • UUID。
  • Redis生成ID
  • Twitter的snowflake算法
  • 利用zookeeper生成唯一ID
  • MongoDB的ObjectId

10. 事务的隔离级别有哪些?MySQL的默认隔离级别是什么?

  • 读未提交(Read Uncommitted)
  • 读已提交(Read Committed)
  • 可重复读(Repeatable Read)
  • 串行化(Serializable)

Mysql默认的事务隔离级别是可重复读(Repeatable Read)

11. 什么是幻读,脏读,不可重复读呢?

  • 事务A、B交替执行,事务A被事务B干扰到了,因为事务A读取到事务B未提交的数据,这就是脏读
  • 在一个事务范围内,两个相同的查询,读取同一条记录,却返回了不同的数据,这就是不可重复读。
  • 事务A查询一个范围的结果集,另一个并发事务B往这个范围中插入/删除了数据,并静悄悄地提交,然后事务A再次查询相同的范围,两次读取得到的结果集不一样了,这就是幻读。

12. 在高并发情况下,如何做到安全的修改同一行数据?

要安全的修改同一行数据,就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和乐观锁两种方案~

使用悲观锁

悲观锁思想就是,当前线程要进来修改数据时,别的线程都得拒之门外~
比如,可以使用select…for update ~

select * from User where name=‘jay’ for update

以上这条sql语句会锁定了User表中所有符合检索条件(name=‘jay’)的记录。本次事务提交之前,别的线程都无法修改这些记录。

使用乐观锁

乐观锁思想就是,有线程过来,先放过去修改,如果看到别的线程没修改过,就可以修改成功,如果别的线程修改过,就修改失败或者重试。实现方式:乐观锁一般会使用版本号机制或CAS算法实现。

13. 数据库的乐观锁和悲观锁。

悲观锁:

悲观锁她专一且缺乏安全感了,她的心只属于当前事务,每时每刻都担心着它心爱的数据可能被别的事务修改,所以一个事务拥有(获得)悲观锁后,其他任何事务都不能对数据进行修改啦,只能等待锁被释放才可以执行。

乐观锁:

乐观锁的“乐观情绪”体现在,它认为数据的变动不会太频繁。因此,它允许多个事务同时对数据进行变动。实现方式:乐观锁一般会使用版本号机制或CAS算法实现。

14. SQL优化的一般步骤是什么,怎么看执行计划(explain),如何理解其中各个字段的含义

  • show status 命令了解各种 sql 的执行频率
  • 通过慢查询日志定位那些执行效率较低的 sql 语句
  • explain 分析低效 sql 的执行计划(这点非常重要,日常开发中用它分析Sql,会大大降低Sql导致的线上事故)

15. select for update有什么含义,会锁表还是锁行还是其他

select查询语句是不会加锁的,但是select for update除了有查询的作用外,还会加锁呢,而且它是悲观锁哦。至于加了是行锁还是表锁,这就要看是不是用了索引/主键啦。
没用索引/主键的话就是表锁,否则就是是行锁。

16. MySQL事务得四大特性以及实现原理

  • 原子性: 事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行。
  • 一致性: 指在事务开始之前和事务结束以后,数据不会被破坏,假如A账户给B账户转10块钱,不管成功与否,A和B的总金额是不变的。
  • 隔离性: 多个事务并发访问时,事务之间是相互隔离的,即一个事务不影响其它事务运行效果。简言之,就是事务之间是进水不犯河水的。
  • 持久性: 表示事务完成以后,该事务对数据库所作的操作更改,将持久地保存在数据库之中。

事务ACID特性的实现思想

  • 原子性:是使用 undo log来实现的,如果事务执行过程中出错或者用户执行了rollback,系统通过undo log日志返回事务开始的状态。
  • 持久性:使用 redo log来实现,只要redo log日志持久化了,当系统崩溃,即可通过redo log把数据恢复。
  • 隔离性:通过锁以及MVCC,使事务相互隔离开。
  • 一致性:通过回滚、恢复,以及并发情况下的隔离性,从而实现一致性。

17. 如果某个表有近千万数据,CRUD比较慢,如何优化。

分库分表

某个表有近千万数据,可以考虑优化表结构,分表(水平分表,垂直分表),当然,你这样回答,需要准备好面试官问你的分库分表相关问题呀,如

  • 分表方案(水平分表,垂直分表,切分规则hash等)
  • 分库分表中间件(Mycat,sharding-jdbc等)
  • 分库分表一些问题(事务问题?跨节点Join的问题)
  • 解决方案(分布式事务等)

索引优化

除了分库分表,优化表结构,当然还有所以索引优化等方案~

18. 如何写sql能够有效的使用到复合索引。

复合索引,也叫组合索引,用户可以在多个列上建立索引,这种索引叫做复合索引。

当我们创建一个组合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。

select * from table where k1=A AND k2=B AND k3=D 

有关于复合索引,我们需要关注查询Sql条件的顺序,确保最左匹配原则有效,同时可以删除不必要的冗余索引。

19. mysql中in 和exists的区别。

这个,跟一下demo来看更刺激吧,啊哈哈

假设表A表示某企业的员工表,表B表示部门表,查询所有部门的所有员工,很容易有以下SQL:

select * from A where deptId in (select deptId from B);

这样写等价于:

先查询部门表B
select deptId from B
再由部门deptId,查询A的员工
select * from A where A.deptId = B.deptId

可以抽象成这样的一个循环:

   List<> resultSet ;
    for(int i=0;i<B.length;i++) {
          for(int j=0;j<A.length;j++) {
          if(A[i].id==B[j].id) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

显然,除了使用in,我们也可以用exists实现一样的查询功能,如下:

select * from A where exists (select 1 from B where A.deptId = B.deptId); 

因为exists查询的理解就是,先执行主查询,获得数据后,再放到子查询中做条件验证,根据验证结果(true或者false),来决定主查询的数据结果是否保留。

那么,这样写就等价于:

select * from A,先从A表做循环
select * from B where A.deptId = B.deptId,再从B表做循环.

同理,可以抽象成这样一个循环:

 List<> resultSet ;
    for(int i=0;i<A.length;i++) {
          for(int j=0;j<B.length;j++) {
          if(A[i].deptId==B[j].deptId) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

数据库最费劲的就是跟程序链接释放。假设链接了两次,每次做上百万次的数据集查询,查完就走,这样就只做了两次;相反建立了上百万次链接,申请链接释放反复重复,这样系统就受不了了。即mysql优化原则,就是小表驱动大表,小的数据集驱动大的数据集,从而让性能更优。
因此,我们要选择最外层循环小的,也就是,如果B的数据量小于A,适合使用in,如果B的数据量大于A,即适合选择exists,这就是in和exists的区别。

20. 数据库自增主键可能遇到什么问题。

使用自增主键对数据库做分库分表,可能出现诸如主键重复等的问题。解决方案的话,简单点的话可以考虑使用UUID哈
自增主键会产生表锁,从而引发问题
自增主键可能用完问题。

21. MVCC熟悉吗,它的底层原理?

MVCC,多版本并发控制,它是通过读取历史版本的数据,来降低并发事务冲突,从而提高并发性能的一种机制。

MVCC需要关注这几个知识点:

  • 事务版本号
  • 表的隐藏列
  • undo log
  • read view

22. 数据库中间件了解过吗,sharding jdbc,mycat?

sharding-jdbc目前是基于jdbc驱动,无需额外的proxy,因此也无需关注proxy本身的高可用。
Mycat 是基于 Proxy,它复写了 MySQL 协议,将 Mycat Server 伪装成一个 MySQL 数据库,而 Sharding-JDBC 是基于 JDBC 接口的扩展,是以 jar 包的形式提供轻量级服务的。

23. MYSQL的主从延迟,你怎么解决?

嘻嘻,先复习一下主从复制原理吧,如图:

主从复制分了五个步骤进行:

  • 步骤一:主库的更新事件(update、insert、delete)被写到binlog
  • 步骤二:从库发起连接,连接到主库。
  • 步骤三:此时主库创建一个binlog dump thread,把binlog的内容发送到从库。
  • 步骤四:从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log
  • 步骤五:还会创建一个SQL线程,从relay log里面读取内容,从Exec_Master_Log_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db

主从同步延迟的原因

一个服务器开放N个链接给客户端来连接的,这样有会有大并发的更新操作, 但是从服务器的里面读取binlog的线程仅有一个,当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致,主服务器的SQL大量积压,未被同步到从服务器里。这就导致了主从不一致, 也就是主从延迟。

主从同步延迟的解决办法

  • 主服务器要负责更新操作,对安全性的要求比从服务器要高,所以有些设置参数可以修改,比如sync_binlog=1,innodb_flush_log_at_trx_commit = 1 之类的设置等。
  • 选择更好的硬件设备作为slave。
  • 把一台从服务器当度作为备份使用, 而不提供查询, 那边他的负载下来了, 执行relay log 里面的SQL效率自然就高了。
  • 增加从服务器喽,这个目的还是分散读的压力,从而降低服务器负载。

24. 说一下大表查询的优化方案

  • 优化shema、sql语句+索引;
  • 可以考虑加缓存,memcached, redis,或者JVM本地缓存;
  • 主从复制,读写分离;
  • 分库分表;

25. 什么是数据库连接池?为什么需要数据库连接池呢?

连接池基本原理:

数据库连接池原理:在内部对象池中,维护一定数量的数据库连接,并对外暴露数据库连接的获取和返回方法。

应用程序和数据库建立连接的过程:

  • 通过TCP协议的三次握手和数据库服务器建立连接
  • 发送数据库用户账号密码,等待数据库验证用户身份
  • 完成身份验证后,系统可以提交SQL语句到数据库执行
  • 把连接关闭,TCP四次挥手告别。

数据库连接池好处:

  • 资源重用 (连接复用)
  • 更快的系统响应速度
  • 新的资源分配手段
    统一的连接管理,避免数据库连接泄漏

26. 一条SQL语句在MySQL中如何执行的?

先看一下Mysql的逻辑架构图吧~

查询语句:

  • 先检查该语句是否有权限
  • 如果没有权限,直接返回错误信息
  • 如果有权限,在 MySQL8.0 版本以前,会先查询缓存。
  • 如果没有缓存,分析器进行词法分析,提取 sql 语句select等的关键元素。然后判断sql 语句是否有语法错误,比如关键词是否正确等等。
  • 优化器进行确定执行方案
  • 进行权限校验,如果没有权限就直接返回错误信息,如果有权限就会调用数据库引擎接口,返回执行结果。

27. InnoDB引擎中的索引策略,了解过吗?

  • 覆盖索引
  • 最左前缀原则
  • 索引下推
  • 索引下推优化是 MySQL 5.6 引入的, 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

28. 数据库存储日期格式时,如何考虑时区转换问题?

  • datetime类型适合用来记录数据的原始的创建时间,修改记录中其他字段的值,datetime字段的值不会改变,除非手动修改它。
  • timestamp类型适合用来记录数据的最后修改时间,只要修改了记录中其他字段的值,timestamp字段的值都会被自动更新。

29. 一条sql执行过长的时间,你如何优化,从哪些方面入手?

  • 查看是否涉及多表和子查询,优化Sql结构,如去除冗余字段,是否可拆表等
  • 优化索引结构,看是否可以适当添加索引
  • 数量大的表,可以考虑进行分离/分表(如交易流水表)
  • 数据库主从分离,读写分离
  • explain分析sql语句,查看执行计划,优化sql
  • 查看mysql执行日志,分析是否有其他方面的问题

30. MYSQL数据库服务器性能分析的方法命令有哪些?

  • Show status, 一些值得监控的变量值:

Bytes_received和Bytes_sent 和服务器之间来往的流量。
Com_服务器正在执行的命令。
Created_
在查询执行期限间创建的临时表和文件。
Handler_存储引擎操作。
Select_
不同类型的联接执行计划。
Sort_*几种排序信息。

  • Show profiles 是MySql用来分析当前会话SQL语句执行的资源消耗情况

31. Blob和text有什么区别?

  • Blob用于存储二进制数据,而Text用于存储大字符串。
  • Blob值被视为二进制字符串(字节字符串),它们没有字符集,并且排序和比较基于列值中的字节的数值。
  • text值被视为非二进制字符串(字符字符串)。它们有一个字符集,并根据字符集的排序规则对值进行排序和比较。

32. mysql里记录货币用什么字段类型比较好?

  • 货币在数据库中MySQL常用Decimal和Numric类型表示,这两种类型被MySQL实现为同样的类型。他们被用于保存与金钱有关的数据。
  • salary DECIMAL(9,2),9(precision)代表将被用于存储值的总的小数位数,而2(scale)代表将被用于存储小数点后的位数。存储在salary列中的值的范围是从-9999999.99到9999999.99。
  • DECIMAL和NUMERIC值作为字符串存储,而不是作为二进制浮点数,以便保存那些值的小数精度。

33. Mysql中有哪几种锁,列举一下?

如果按锁粒度划分,有以下3种:

  • 表锁: 开销小,加锁快;锁定力度大,发生锁冲突概率高,并发度最低;不会出现死锁。
  • 行锁: 开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高。
  • 页锁: 开销和加锁速度介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般

34. Hash索引和B+树区别是什么?你在设计索引是怎么抉择的?

  • B+树可以进行范围查询,Hash索引不能。
  • B+树支持联合索引的最左侧原则,Hash索引不支持。
  • B+树支持order by排序,Hash索引不支持。
  • Hash索引在等值查询上比B+树效率更高。
  • B+树使用like 进行模糊查询的时候,like后面(比如%开头)的话可以起到优化的作用,Hash索引根本无法进行模糊查询。

35. mysql 的内连接、左连接、右连接有什么区别?

  • Inner join 内连接,在两张表进行连接查询时,只保留两张表中完全匹配的结果集
  • left join 在两张表进行连接查询时,会返回左表所有的行,即使在右表中没有匹配的记录。
  • right join 在两张表进行连接查询时,会返回右表所有的行,即使在左表中没有匹配的记录。

36. 说说MySQL 的基础架构图

Mysql逻辑架构图主要分三层:

  • 第一层负责连接处理,授权认证,安全等等
  • 第二层负责编译并优化SQL
  • 第三层是存储引擎。

37. 什么是内连接、外连接、交叉连接、笛卡尔积呢?

  • 内连接(inner join):取得两张表中满足存在连接匹配关系的记录。
  • 外连接(outer join):取得两张表中满足存在连接匹配关系的记录,以及某张表(或两张表)中不满足匹配关系的记录。
  • 交叉连接(cross join):显示两张表所有记录一一对应,没有匹配关系进行筛选,也被称为:笛卡尔积。

38. 说一下数据库的三大范式

  • 第一范式:数据表中的每一列(每个字段)都不可以再拆分。
  • 第二范式:在第一范式的基础上,分主键列完全依赖于主键,而不能是依赖于主键的一部分。
  • 第三范式:在满足第二范式的基础上,表中的非主键只依赖于主键,而不依赖于其他非主键。

39. mysql有关权限的表有哪几个呢?

MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在mysql数据库里,由mysql_install_db脚本初始化。这些权限表分别user,db,table_priv,columns_priv和host。

  • user权限表:记录允许连接到服务器的用户帐号信息,里面的权限是全局级的。
  • db权限表:记录各个帐号在各个数据库上的操作权限。
  • table_priv权限表:记录数据表级的操作权限。
  • columns_priv权限表:记录数据列级的操作权限。
  • host权限表:配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

40. Mysql的binlog有几种录入格式?分别有什么区别?

有三种格式哈,statement,row和mixed。

  • statement,每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制。
  • row,不记录sql语句上下文相关信息,仅保存哪条记录被修改。记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大。
  • mixed,一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row。

41. InnoDB引擎的4大特性,了解过吗

  • 插入缓冲(insert buffer)
  • 二次写(double write)
  • 自适应哈希索引(ahi)
  • 预读(read ahead)

42. 索引有哪些优缺点?

优点:

  • 唯一索引可以保证数据库表中每一行的数据的唯一性
  • 索引可以加快数据查询速度,减少查询时间

缺点:

  • 创建索引和维护索引要耗费时间
  • 索引需要占物理空间,除了数据表占用数据空间之外,每一个索引还要占用一定的物理空间
  • 以表中的数据进行增、删、改的时候,索引也要动态的维护。

43. 索引有哪几种类型?

  • 主键索引: 数据列不允许重复,不允许为NULL,一个表只能有一个主键。
  • 唯一索引: 数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引。
  • 普通索引: 基本的索引类型,没有唯一性的限制,允许为NULL值。
  • 全文索引:是目前搜索引擎使用的一种关键技术,对文本的内容进行分词、搜索。
  • 覆盖索引:查询列要被所建的索引覆盖,不必读取数据行
  • 组合索引:多列值组成一个索引,用于组合搜索,效率大于索引合并

44. 创建索引有什么原则呢?

  • 最左前缀匹配原则
  • 频繁作为查询条件的字段才去创建索引
  • 频繁更新的字段不适合创建索引
  • 索引列不能参与计算,不能有函数操作
  • 优先考虑扩展索引,而不是新建索引,避免不必要的索引
  • 在order by或者group by子句中,创建索引需要注意顺序
  • 区分度低的数据列不适合做索引列(如性别)
  • 定义有外键的数据列一定要建立索引。
  • 对于定义为text、image数据类型的列不要建立索引。
  • 删除不再使用或者很少使用的索引

45. 创建索引的三种方式

在执行CREATE TABLE时创建索引

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用ALTER TABLE命令添加索引

ALTER TABLE table_name ADD INDEX index_name (column);

使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column);

46. 百万级别或以上的数据,你是如何删除的?

  • 我们想要删除百万数据的时候可以先删除索引
  • 然后批量删除其中无用数据
  • 删除完成后重新创建索引。

47. 什么是最左前缀原则?什么是最左匹配原则?

  • 最左前缀原则,就是最左优先,在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。
  • 当我们创建一个组合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。。

48. B树和B+树的区别,数据库为什么使用B+树而不是B树?

  • 在B树中,键和值即存放在内部节点又存放在叶子节点;在B+树中,内部节点只存键,叶子节点则同时存放键和值。
  • B+树的叶子节点有一条链相连,而B树的叶子节点各自独立的。

  • B+树索引的所有数据均存储在叶子节点,而且数据是按照顺序排列的,链表连着的。那么B+树使得范围查找,排序查找,分组查找以及去重查找变得异常简单。.

  • B+树非叶子节点上是不存储数据的,仅存储键值,而B树节点中不仅存储键值,也会存储数据。innodb中页的默认大小是16KB,如果不存储数据,那么就会存储更多的键值,相应的树的阶数(节点的子节点树)就会更大,树就会更矮更胖,如此一来我们查找数据进行磁盘的IO次数有会再次减少,数据查询的效率也会更快.

49. 覆盖索引、回表等这些,了解过吗?

  • 覆盖索引: 查询列要被所建的索引覆盖,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。
  • 回表:二级索引无法直接查询所有列的数据,所以通过二级索引查询到聚簇索引后,再查询到想要的数据,这种通过二级索引查询出来的过程,就叫做回表。

50. B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据?

  • 在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这就是聚簇索引和非聚簇索引。 在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引。如果没有唯一键,则隐式的生成一个键来建立聚簇索引。
  • 当查询使用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询。

51. 何时使用聚簇索引与非聚簇索引

动作描述 使用聚集索引 使用非聚集索引
列经常被分组排序
返回某范围内的数据 不应
一个或极少不同值 不应 不应
小数目的不同值 不应
大数目的不同值 不应
频繁更新的列 不应
外键列
主键列
频繁修改索引列 不应

52. 非聚簇索引一定会回表查询吗?

不一定,如果查询语句的字段全部命中了索引,那么就不必再进行回表查询(哈哈,覆盖索引就是这么回事)。

举个简单的例子,假设我们在学生表的上建立了索引,那么当进行select age from student where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询。

53. 组合索引是什么?为什么需要注意组合索引中的顺序?

组合索引,用户可以在多个列上建立索引,这种索引叫做组合索引。
因为InnoDB引擎中的索引策略的最左原则,所以需要注意组合索引中的顺序。

54. 什么是数据库事务?

数据库事务(简称:事务),是数据库管理系统执行过程中的一个逻辑单位,由一个有限的数据库操作序列构成,这些操作要么全部执行,要么全部不执行,是一个不可分割的工作单位。

55. 隔离级别与锁的关系

回答这个问题,可以先阐述四种隔离级别,再阐述它们的实现原理。隔离级别就是依赖锁和MVCC实现的。

56. 按照锁的粒度分,数据库锁有哪些呢?锁机制与InnoDB锁算法

  • 按锁粒度分有:表锁,页锁,行锁
  • 按锁机制分有:乐观锁,悲观锁

57. 从锁的类别角度讲,MySQL都有哪些锁呢?

从锁的类别上来讲,有共享锁和排他锁。

  • 共享锁: 又叫做读锁。当用户要进行数据的读取时,对数据加上共享锁。共享锁可以同时加上多个。
  • 排他锁: 又叫做写锁。当用户要进行数据的写入时,对数据加上排他锁。排他锁只可以加一个,他和其他的排他锁,共享锁都相斥。

锁兼容性如下:

58. MySQL中InnoDB引擎的行锁是怎么实现的?

基于索引来完成行锁的。

select * from t where id = 666 for update;

for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,如果 id 不是索引键那么InnoDB将实行表锁。

59. 什么是死锁?怎么解决?

死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方的资源,从而导致恶性循环的现象。看图形象一点,如下:

死锁有四个必要条件:互斥条件,请求和保持条件,环路等待条件,不剥夺条件。
解决死锁思路,一般就是切断环路,尽量避免并发形成环路。

  • 如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。
  • 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;
  • 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率;
  • 如果业务处理不好可以用分布式事务锁或者使用乐观锁
  • 死锁与索引密不可分,解决索引问题,需要合理优化你的索引,

60. 为什么要使用视图?什么是视图?

为什么要使用视图?

为了提高复杂SQL语句的复用性和表操作的安全性,MySQL数据库管理系统提供了视图特性。

什么是视图?

视图是一个虚拟的表,是一个表中的数据经过某种筛选后的显示方式,视图由一个预定义的查询select语句组成。

61. 视图有哪些特点?哪些使用场景?

视图特点:

  • 视图的列可以来自不同的表,是表的抽象和在逻辑意义上建立的新关系。
  • 视图是由基本表(实表)产生的表(虚表)。
  • 视图的建立和删除不影响基本表。
  • 对视图内容的更新(添加,删除和修改)直接影响基本表。
  • 当视图来自多个基本表时,不允许添加和删除数据。

视图用途:

  • 简化sql查询,提高开发效率,兼容老的表结构。

视图的常见使用场景:

  • 重用SQL语句;
  • 简化复杂的SQL操作。
  • 使用表的组成部分而不是整个表;
  • 保护数据
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

62. 视图的优点,缺点,讲一下?

  • 查询简单化。视图能简化用户的操作
  • 数据安全性。视图使用户能以多种角度看待同一数据,能够对机密数据提供安全保护
  • 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

63. count(1)、count(*) 与 count(列名) 的区别?

  • count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL
  • count(1)包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL
  • count(列名)只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者0,而是表示null)的计数,即某个字段值为NULL时,不统计。

64. 什么是游标?

游标提供了一种对从表中检索出的数据进行操作的灵活手段,就本质而言,游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

65. 什么是存储过程?有哪些优缺点?

存储过程,就是一些编译好了的SQL语句,这些SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),然后给这些代码块取一个名字,在用到这个功能的时候调用即可。

优点:

  • 存储过程是一个预编译的代码块,执行效率比较高
  • 存储过程在服务器端运行,减少客户端的压力
  • 允许模块化程序设计,只需要创建一次过程,以后在程序中就可以调用该过程任意次,类似方法的复用一个存储过程替代大量T_SQL语句 ,可以降低网络通信量,提高通信速率
  • 可以一定程度上确保数据安全

缺点:

  • 调试麻烦
  • 可移植性不灵活
  • 重新编译问题

66. 什么是触发器?触发器的使用场景有哪些?

触发器,指一段代码,当触发某个事件时,自动执行这些代码。

使用场景:

  • 可以通过数据库中的相关表实现级联更改。
  • 实时监控某张表中的某个字段的更改而需要做出相应的处理。
  • 例如可以生成某些业务的编号。
  • 注意不要滥用,否则会造成数据库及应用程序的维护困难。

67. MySQL中都有哪些触发器?

MySQL 数据库中有六种触发器:

  • Before Insert
  • After Insert
  • Before Update
  • After Update
  • Before Delete
  • After Delete

68. 超键、候选键、主键、外键分别是什么?

  • 超键:在关系模式中,能唯一知标识元组的属性集称为超键。
  • 候选键:是最小超键,即没有冗余元素的超键。
  • 主键:数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键,且主键的取值不能缺失,即不能为空值(Null)。
  • 外键:在一个表中存在的另一个表的主键称此表的外键。

69. SQL 约束有哪几种呢?

  • NOT NULL: 约束字段的内容一定不能为NULL。
  • UNIQUE: 约束字段唯一性,一个表允许有多个 Unique 约束。
  • PRIMARY KEY: 约束字段唯一,不可重复,一个表只允许存在一个。
  • FOREIGN KEY: 用于预防破坏表之间连接的动作,也能防止非法数据插入外键。
  • CHECK: 用于控制字段的值范围。

70. 谈谈六种关联查询,使用场景。

  • 交叉连接
  • 内连接
  • 外连接
  • 联合查询
  • 全连接
  • 交叉连接

71. varchar(50)中50的涵义

字段最多存放 50 个字符
如 varchar(50) 和 varchar(200) 存储 “jay” 字符串所占空间是一样的,后者在排序时会消耗更多内存

72. mysql中int(20)和char(20)以及varchar(20)的区别

  • int(20) 表示字段是int类型,显示长度是 20
  • char(20)表示字段是固定长度字符串,长度为 20
  • varchar(20) 表示字段是可变长度字符串,长度为 20

73. drop、delete与truncate的区别

delete truncate drop
类型 DML DDL DDL
回滚 可回滚 不可回滚 不可回滚
删除内容 表结构还在,删除表的全部或者一部分数据行 表结构还在,删除表中的所有数据 从数据库中删除表,所有的数据行,索引和权限也会被删除
删除速度 删除速度慢,逐行删除 删除速度快 删除速度最快

74. UNION与UNION ALL的区别?

  • Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
  • Union All:对两个结果集进行并集操作,包括重复行,不进行排序;
  • UNION的效率高于 UNION ALL

75. SQL的生命周期?

  • 服务器与数据库建立连接
  • 数据库进程拿到请求sql
  • 解析并生成执行计划,执行
  • 读取数据到内存,并进行逻辑处理
  • 通过步骤一的连接,发送结果到客户端
  • 关掉连接,释放资源

76. 一条Sql的执行顺序?

77. 列值为NULL时,查询是否会用到索引?

列值为NULL也是可以走索引的
计划对列进行索引,应尽量避免把它设置为可空,因为这会让 MySQL 难以优化引用了可空列的查询,同时增加了引擎的复杂度

78. 关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?

  • 我们平时写Sql时,都要养成用explain分析的习惯。
  • 慢查询的统计,运维会定期统计给我们

优化慢查询:

  • 分析语句,是否加载了不必要的字段/数据。
  • 分析SQl执行句话,是否命中索引等。
  • 如果SQL很复杂,优化SQL结构
  • 如果表数据量太大,考虑分表

79. 主键使用自增ID还是UUID,为什么?

如果是单机的话,选择自增ID;如果是分布式系统,优先考虑UUID吧,但还是最好自己公司有一套分布式唯一ID生产方案吧。

  • 自增ID:数据存储空间小,查询效率高。但是如果数据量过大,会超出自增长的值范围,多库合并,也有可能有问题。
  • uuid:适合大量数据的插入和更新操作,但是它无序的,插入数据效率慢,占用空间大。

80. mysql自增主键用完了怎么办?

自增主键一般用int类型,一般达不到最大值,可以考虑提前分库分表的。

自增ID用完后 一直都是最大值 如果标识了主键 则主键冲突

81. 字段为什么要求定义为not null?

null值会占用更多的字节,并且null有很多坑的。

82. 如果要存储用户的密码散列,应该使用什么字段进行存储?

密码散列,盐,用户身份证号等固定长度的字符串,应该使用char而不是varchar来存储,这样可以节省空间且提高检索效率。

83. Mysql驱动程序是什么?

这个jar包: mysql-connector-java-5.1.18.jar
Mysql驱动程序主要帮助编程语言与 MySQL服务端进行通信,如连接、传输数据、关闭等。

84. 如何优化长难的查询语句?有实战过吗?

  • 将一个大的查询分为多个小的相同的查询
  • 减少冗余记录的查询。
  • 一个复杂查询可以考虑拆成多个简单查询
  • 分解关联查询,让缓存的效率更高。

85. 优化特定类型的查询语句

平时积累吧:

  • 比如使用select 具体字段代替 select *
  • 使用count(*) 而不是count(列名)
  • 在不影响业务的情况,使用缓存
  • explain 分析你的SQL

86. MySQL数据库cpu飙升的话,要怎么处理呢?

排查过程:

  • 使用top 命令观察,确定是mysqld导致还是其他原因。
  • 如果是mysqld导致的,show processlist,查看session情况,确定是不是有消耗资源的sql在运行。
  • 找出消耗高的 sql,看看执行计划是否准确, 索引是否缺失,数据量是否太大。

处理:

  • kill 掉这些线程(同时观察 cpu 使用率是否下降),
  • 进行相应的调整(比如说加索引、改 sql、改内存参数)
  • 重新跑这些 SQL。

其他情况:

也有可能是每个 sql 消耗资源并不多,但是突然之间,有大量的 session 连进来导致 cpu 飙升,这种情况就需要跟应用一起来分析为何连接数会激增,再做出相应的调整,比如说限制连接数等

87. 读写分离常见方案?

  • 应用程序根据业务逻辑来判断,增删改等写操作命令发给主库,查询命令发给备库。
  • 利用中间件来做代理,负责对数据库的请求识别出读还是写,并分发到不同的数据库中。(如:amoeba,mysql-proxy)

88. MySQL的复制原理以及流程

主从复制原理,简言之,就三步曲,如下:

  • 主数据库有个bin-log二进制文件,纪录了所有增删改Sql语句。(binlog线程)
  • 从数据库把主数据库的bin-log文件的sql语句复制过来。(io线程)
  • 从数据库的relay-log重做日志文件中再执行一次这些sql语句。(Sql执行线程)

如下图所示:

上图主从复制分了五个步骤进行:

  • 步骤一:主库的更新事件(update、insert、delete)被写到binlog
  • 步骤二:从库发起连接,连接到主库。
  • 步骤三:此时主库创建一个binlog dump thread,把binlog的内容发送到从库。
  • 步骤四:从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log
  • 步骤五:还会创建一个SQL线程,从relay log里面读取内容,从Exec_Master_Log_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db

89. MySQL中DATETIME和TIMESTAMP的区别

存储精度都为秒

区别:

  • DATETIME 的日期范围是 1001——9999 年;TIMESTAMP 的时间范围是 1970——2038 年
  • DATETIME 存储时间与时区无关;TIMESTAMP 存储时间与时区有关,显示的值也依赖于时区
  • DATETIME 的存储空间为 8 字节;TIMESTAMP 的存储空间为 4 字节
  • DATETIME 的默认值为 null;TIMESTAMP 的字段默认不为空(not null),默认值为当前时间(CURRENT_TIMESTAMP)

90. Innodb的事务实现原理?

  • 原子性:是使用 undo log来实现的,如果事务执行过程中出错或者用户执行了rollback,系统通过undo log日志返回事务开始的状态。
  • 持久性:使用 redo log来实现,只要redo log日志持久化了,当系统崩溃,即可通过redo log把数据恢复。
  • 隔离性:通过锁以及MVCC,使事务相互隔离开。
  • 一致性:通过回滚、恢复,以及并发情况下的隔离性,从而实现一致性。

91. 谈谈MySQL的Explain

Explain 执行计划包含字段信息如下:分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 等12个字段。
我们重点关注的是type,它的属性排序如下:

system  > const > eq_ref > ref  > ref_or_null >
index_merge > unique_subquery > index_subquery > 
range > index > ALL

92. Innodb的事务与日志的实现方式

有多少种日志

  • innodb两种日志redo和undo。

日志的存放形式

  • redo:在页修改的时候,先写到 redo log buffer 里面, 然后写到 redo log 的文件系统缓存里面(fwrite),然后再同步到磁盘文件( fsync)。
  • Undo:在 MySQL5.5 之前, undo 只能存放在 ibdata文件里面, 5.6 之后,可以通过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata之外。

事务是如何通过日志来实现的

  • 因为事务在修改页时,要先记 undo,在记 undo 之前要记 undo 的 redo, 然后修改数据页,再记数据页修改的 redo。 Redo(里面包括 undo 的修改) 一定要比数据页先持久化到磁盘。
  • 当事务需要回滚时,因为有 undo,可以把数据页回滚到前镜像的 状态,崩溃恢复时,如果 redo log 中事务没有对应的 commit 记录,那么需要用 undo把该事务的修改回滚到事务开始之前。
  • 如果有 commit 记录,就用 redo 前滚到该事务完成时并提交掉。

93. MySQL中TEXT数据类型的最大长度

  • TINYTEXT:256 bytes
  • TEXT:65,535 bytes(64kb)
  • MEDIUMTEXT:16,777,215 bytes(16MB)
  • LONGTEXT:4,294,967,295 bytes(4GB)

94. 500台db,在最快时间之内重启。

  • 可以使用批量 ssh 工具 pssh 来对需要重启的机器执行重启命令。
  • 也可以使用 salt(前提是客户端有安装 salt)或者 ansible( ansible 只需要 ssh 免登通了就行)等多线程工具同时操作多台服务

95. 你是如何监控你们的数据库的?你们的慢日志都是怎么查询的?

监控的工具有很多,例如zabbix,lepus,我这里用的是lepus

96. 你是否做过主从一致性校验,如果有,怎么做的,如果没有,你打算怎么做?

主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等

97. 你们数据库是否支持emoji表情存储,如果不支持,如何操作?

更换字符集utf8–>utf8mb4

98. MySQL如何获取当前日期?

SELECT CURRENT_DATE();

99. 一个6亿的表a,一个3亿的表b,通过外间tid关联,你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

1、如果A表TID是自增长,并且是连续的,B表的ID为索引

select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。

select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

100. Mysql一条SQL加锁分析

一条SQL加锁,可以分9种情况进行:

  • 组合一:id列是主键,RC隔离级别
  • 组合二:id列是二级唯一索引,RC隔离级别
  • 组合三:id列是二级非唯一索引,RC隔离级别
  • 组合四:id列上没有索引,RC隔离级别
  • 组合五:id列是主键,RR隔离级别
  • 组合六:id列是二级唯一索引,RR隔离级别
  • 组合七:id列是二级非唯一索引,RR隔离级别
  • 组合八:id列上没有索引,RR隔离级别
  • 组合九:Serializable隔离级别

MySQL基础知识

Mysql基础知识

1. Mysql索引用的是什么算法

Mysql 索引选用的是B+树,平衡二叉树的高度太高,查找可能需要较多的磁盘IO。B树索引占用内存较高(非叶子节点存储数据)。

B+树, 主要是查询效率高,O(logN),可以充分利用磁盘预读的特性,多叉树,深度小,叶子结点有序且存储数据.

2. Mysql事务的基本要素

  • 原子性:事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
  • 一致性:事务开始前和结束后,数据库的完整性约束没有被破坏。
  • 隔离性:同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。
  • 持久性:事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。

3. Mysql的存储引擎

  • InnoDB存储引擎

InnoDB存储引擎支持事务,其设计目标主要面向在线事务处理(OLTP)的应用。

其特点是行锁设计,支持外键,并支持非锁定锁,即默认读取操作不会产生锁。从Mysql5.5.8版本开始,InnoDB存储引擎是默认的存储引擎。

  • MyISAM存储引擎

MyISAM存储引擎不支持事务、表锁设计,支持全文索引,主要面向一些OLAP数据库应用。

InnoDB的数据文件本身就是主索引文件,而MyISAM的主索引和数据是分开的。

  • NDB存储引擎

NDB存储引擎是一个集群存储引擎,其结构是share nothing的集群架构,能提供更高的可用性。

NDB的特点是数据全部放在内存中(从MySQL 5.1版本开始,可以将非索引数据放在磁盘上),因此主键查找的速度极快,并且通过添加NDB数据存储节点可以线性地提高数据库性能,是高可用、高性能的集群系统。
NDB存储引擎的连接操作是在MySQL数据库层完成的,而不是在存储引擎层完成的。这意味着,复杂的连接操作需要巨大的网络开销,因此查询速度很慢。如果解决了这个问题,NDB存储引擎的市场应该是非常巨大的。

  • Memory存储引擎

Memory存储引擎(之前称HEAP存储引擎)将表中的数据存放在内存中,如果数据库重启或发生崩溃,表中的数据都将消失。
它非常适合用于存储临时数据的临时表,以及数据仓库中的纬度表。Memory存储引擎默认使用哈希索引,而不是我们熟悉的B+树索引。
虽然Memory存储引擎速度非常快,但在使用上还是有一定的限制。比如,只支持表锁,并发性能较差,并且不支持TEXT和BLOB列类型。最重要的是,存储变长字段时是按照定常字段的方式进行的,因此会浪费内存。

  • Archive存储引擎

Archive存储引擎只支持INSERT和SELECT操作,从MySQL 5.1开始支持索引。
Archive存储引擎使用zlib算法将数据行(row)进行压缩后存储,压缩比一般可达1∶10。正如其名字所示,Archive存储引擎非常适合存储归档数据,如日志信息。
Archive存储引擎使用行锁来实现高并发的插入操作,但是其本身并不是事务安全的存储引擎,其设计目标主要是提供高速的插入和压缩功能。

  • Maria存储引擎

Maria存储引擎是新开发的引擎,设计目标主要是用来取代原有的MyISAM存储引擎,从而成为MySQL的默认存储引擎。它可以看做是MyISAM的后续版本。
Maria存储引擎的特点是:支持缓存数据和索引文件,应用了行锁设计,提供了MVCC功能,支持事务和非事务安全的选项,以及更好的BLOB字符类型的处理性能。

4. Mysql事务隔离级别

Mysql有四种事务隔离级别,默认的是可重复读.

事务隔离级别 脏读 不可重复读 幻读
读未提交
读已提交
可重复读
串行
  • 读未提交(Read uncommitted)

一个事务可以读取另一个未提交事务的数据,最低级别,任何情况都无法保证。

(1)所有事务都可以看到其他未提交事务的执行结果
(2)本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少
(3)该级别引发的问题是——脏读(Dirty Read):读取到了未提交的数据

  • 读已提交(Read committed)

一个事务要等另一个事务提交后才能读取数据,可避免脏读的发生。

(1)这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)
(2)它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变
(3)这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read),不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。

导致这种情况的原因可能有:

(1)有一个交叉的事务有新的commit,导致了数据的改变;
(2)一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit.

  • 可重复读(Repeatable read)

就是在开始读取数据(事务开启)时,不再允许修改操作,可避免脏读、不可重复读的发生。

(1)这是MySQL的默认事务隔离级别
(2)它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行
(3)此级别可能出现的问题——幻读(Phantom Read):当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行
(4)InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题.InnoDB采用MVCC来支持高并发,实现了四个标准隔离级别。默认基本是可重复读,并且提供间隙锁(next-key locks)策略防止幻读出现。

  • 串行(Serializable)

串行(Serializable),是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。
但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。Mysql的默认隔离级别是Repeatable read。

(1)这是最高的隔离级别.
(2)它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。
(3)在这个级别,可能导致大量的超时现象和锁竞争.

5. Mysql高可用方案有哪些

Mysql高可用方案包括:

  • 主从复制方案

这是MySQL自身提供的一种高可用解决方案,数据同步方法采用的是MySQL replication技术。MySQL replication就是从服务器到主服务器拉取二进制日志文件,然后再将日志文件解析成相应的SQL在从服务器上重新执行一遍主服务器的操作,通过这种方式保证数据的一致性。
为了达到更高的可用性,在实际的应用环境中,一般都是采用MySQL replication技术配合高可用集群软件keepalived来实现自动failover,这种方式可以实现95.000%的SLA。

  • MMM/MHA高可用方案

MMM提供了MySQL主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件。在MMM高可用方案中,典型的应用是双主多从架构,通过MySQL replication技术可以实现两个服务器互为主从,且在任何时候只有一个节点可以被写入,避免了多点写入的数据冲突。
同时,当可写的主节点故障时,MMM套件可以立刻监控到,然后将服务自动切换到另一个主节点,继续提供服务,从而实现MySQL的高可用。

  • Heartbeat/SAN高可用方案

在这个方案中,处理failover的方式是高可用集群软件Heartbeat,它监控和管理各个节点间连接的网络,并监控集群服务,当节点出现故障或者服务不可用时,自动在其他节点启动集群服务。
在数据共享方面,通过SAN(Storage Area Network)存储来共享数据,这种方案可以实现99.990%的SLA。

  • Heartbeat/DRBD高可用方案

这个方案处理failover的方式上依旧采用Heartbeat,不同的是,在数据共享方面,采用了基于块级别的数据同步软件DRBD来实现。
DRBD是一个用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。和SAN网络不同,它并不共享存储,而是通过服务器之间的网络复制数据。

  • NDB CLUSTER高可用方案

国内用NDB集群的公司非常少,貌似有些银行有用。NDB集群不需要依赖第三方组件,全部都使用官方组件,能保证数据的一致性,某个数据节点挂掉,其他数据节点依然可以提供服务,管理节点需要做冗余以防挂掉。
缺点是:管理和配置都很复杂,而且某些SQL语句例如join语句需要避免。

6. Mysql中utf8和utf8mb4区别

MySQL在5.5.3之后增加了这个utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。好在utf8mb4utf8的超集,除了将编码改为utf8mb4外不需要做其他转换。当然,为了节省空间,一般情况下使用utf8也就可以了。

Mysql支持的 utf8 编码最大字符长度为 3 字节,如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff,也就是 Unicode 中的基本多文种平面(BMP)。任何不在基本多文本平面的 Unicode字符,都无法使用 Mysql 的 utf8 字符集存储。

包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码,常见于 ios 和 android 手机上),和很多不常用的汉字,以及任何新增的 Unicode 字符等等。

Mysql 中保存 4 字节长度的 UTF-8 字符,需要使用utf8mb4 字符集,但只有5.5.3版本以后的才支持(查看版本: select version();)。因此呢,为了获取更好的兼容性,应该总是使用 utf8mb4 而非 utf8.

对于 CHAR 类型数据,utf8mb4 会多消耗一些空间,根据 Mysql 官方建议,使用 VARCHAR 替代 CHAR。

7. Mysql中乐观锁和悲观锁区别

  • 悲观锁(Pessimistic Lock)

悲观锁顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会block直到它拿到锁。

传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁。

  • 乐观锁(Optimistic Lock)

乐观锁顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号等机制。

乐观锁适用于多读的应用类型,这样可以提高吞吐量,像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

乐观锁的特点先进行业务操作,不到万不得已不去拿锁。即“乐观”的认为拿锁多半是会成功的,因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。

乐观锁在数据库上的实现完全是逻辑的,不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号,或者时间戳,然后按照如下方式实现:

1. SELECT data AS old_data, version AS old_version FROM …;
2. 根据获取的数据进行业务操作,得到new_data和new_version
3. UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
    // 乐观锁获取成功,操作完成
} else {
    // 乐观锁获取失败,回滚并重试
}

乐观锁是否在事务中其实都是无所谓的,其底层机制是这样:在数据库内部update同一行的时候是不允许并发的,即数据库每次执行一条update语句时会获取被update行的写锁,直到这一行被成功更新后才释放。

因此在业务操作进行前获取需要锁的数据的当前版本号,然后实际更新数据时再次对比版本号确认与之前获取的相同,并更新版本号,即可确认这之间没有发生并发的修改。如果更新失败即可认为老版本的数据已经被并发修改掉而不存在了,此时认为获取锁失败,需要回滚整个业务操作并可根据需要重试整个过程。

两种锁各有优缺点,不可认为一种好于另一种,像乐观锁适用于写比较少的情况下,即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。

但如果经常产生冲突,上层应用会不断的进行retry,这样反倒是降低了性能,所以这种情况下用悲观锁就比较合适。 

8. Mysql索引主要是哪些

索引的目的在于提高查询效率.

索引的类型:

  • UNIQUE(唯一索引):不可以出现相同的值,可以有NULL值
  • INDEX(普通索引):允许出现相同的索引内容
  • PROMARY KEY(主键索引):不允许出现相同的值
  • fulltext index(全文索引):可以针对值中的某个单词,但效率确实不敢恭维
  • 组合索引:实质上是将多个字段建到一个索引里,列值的组合必须唯一

索引虽然好处很多,但过多的使用索引可能带来相反的问题,索引也是有缺点的:

  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT,UPDATE和DELETE。因为更新表时,mysql不仅要保存数据,还要保存一下索引文件.

  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在要给大表上建了多种组合索引,索引文件会膨胀很宽, 索引只是提高效率的一个方式,如果mysql有大数据量的表,就要花时间研究建立最优的索引,或优化查询语句。

使用索引时,有一些技巧:

  • 索引不会包含有NULL的列

只要列中包含有NULL值,都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此符合索引就是无效的。

  • 使用短索引

对串列进行索引,如果可以就应该指定一个前缀长度。例如,如果有一个char(255)的列,如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

  • 索引列排序

mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作,尽量不要包含多个列的排序,如果需要最好给这些列建复合索引。

  • like语句操作

一般情况下不鼓励使用like操作,如果非使用不可,注意正确的使用方式。like ‘%aaa%’不会使用索引,而like ‘aaa%’可以使用索引。

  • 不要在列上进行运算
  • 不使用NOT IN<>!=操作,但<,<==>,>=,BETWEEN,IN是可以用到索引的
  • 索引要建立在经常进行select操作的字段上。
    这是因为,如果这些列很少用到,那么有无索引并不能明显改变查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

  • 索引要建立在值比较唯一的字段上。

  • 对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大,要么取值很少。
  • 在where和join中出现的列需要建立索引。

  • where的查询条件里有不等号(where column != …),mysql将无法使用索引。

  • 如果where字句的查询条件里使用了函数(如:where DAY(column)=…),mysql将无法使用索引。

  • 在join操作中(需要从多个数据表提取数据时),mysql只有在主键和外键的数据类型相同时才能使用索引,否则及时建立了索引也不会使用。

组合索引的作用:

  1. 减少开销。

建一个组合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。
对于大量数据的表,使用组合索引会大大的减少开销。

  1. 覆盖索引。

通常指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取。也可以称之为实现了索引覆盖。

对组合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2

那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。

所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。

  1. 效率高。

索引列越多,通过索引筛选出的数据越快。

9. Mysql联合索引最左匹配原则

最左前缀匹配原则:

  1. 在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先.

在检索数据时从联合索引的最左边开始匹配,Mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配了.

就比如 a=3 and b=4 and c>5 and d=6如果建立(abcd)顺序的索引,d就用不到索引了,如果建立(abdc)的索引则都可以用到索引,其中abd的顺序可以任意调整,因为查询优化器会重新编排(即使是c>5 and b=4 and d=6 and a=3也会全部用到 abdc索引 ).

  1. =in可以乱序,比如a=1 and b=2 and c=3 建立(abc)索引可以任意顺序,mysql查询优化器会优化顺序.

这里需要注意下, 比如abc索引 那么只要查询条件有a即可用到abc索引(如abc ab ac a),没有a就用不到。

最左前缀匹配成因:Mysql是创建复合索引的规则是根据索引最左边的字段进行排序,在第一个字段排序的基础上再进行第二个字段排序,类似于order by col1,col2… 所以第一个字段是绝对有序的 第二个字段就是无序的了,所以Mysql 强调最左前缀匹配.

10. 聚簇索引和非聚簇索引区别

聚簇索引与非聚簇索引的区别是:叶子节点是否存放一整行记录.

InnoDB 主键使用的是聚簇索引,MyISAM 不管是主键索引,还是二级索引使用的都是非聚簇索引。

  1. 对于聚簇索引表来说(左图),表数据是和主键一起存储的,主键索引的叶结点存储行数据(包含了主键值),二级索引的叶结点存储行的主键值。
    使用的是B+树作为索引的存储结构,非叶子节点都是索引关键字,但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)。

  2. 对于非聚簇索引表来说(右图),表数据和索引是分成两部分存储的,主键索引和二级索引存储上没有任何区别。使用的是B+树作为索引的存储结构,所有的节点都是索引,叶子节点存储的是索引+索引对应的记录的数据。

因此, 聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针

11. 如何查询一个字段是否命中了索引

通过explain sql可看下SQL是否走了索引,很快对比出来 .

当一个sql中索引字段为int类型时,例如搜索条件where num="111"where num=111都可以使用该字段的索引. 当一个中索引字段为varchar类型时,例如搜索条件where num="111"可以使用索引,where num=111不可以使用索引.

12. Mysql中查询数据什么情况下不会命中索引

通常不命中索引有接种情况:

  • 索引规范不合理,sql解析器不命中索引.
  • 表中索引是以表中数据量字段最多的建立的索引,sql解析器不命中索引.(实际就是索引没用,最后全局查找了)
  • bool的字段做索引,sql选择器不命中索引.
  • 模糊查询 %like
  • 索引列参与计算,使用了函数
  • 非最左前缀顺序
  • where对null判断
  • where不等于
  • or操作有至少一个字段没有索引
  • 需要回表的查询结果集过大(超过配置的范围)

13. Mysql中的MVCC是什么

数据库并发控制——锁, Multiversion (version) concurrency control (MCC or MVCC) 多版本并发控制 ,它是数据库管理系统一种常见的并发控制。

并发控制常用的是锁,当线程要对一个共享资源进行操作的时候,加锁是一种非常简单粗暴的方法(事务开始时给 DQL 加读锁,给 DML 加写锁),这种锁是一种 悲观 的实现方式,也就是说这会给其他事务造成堵塞,从而影响数据库性能。

其中在数据库中最常见的就是悲观锁和乐观锁:

  • 悲观锁

当一个线程需要对共享资源进行操作的时候,首先对共享资源进行加锁,当该线程持有该资源的锁的时候,其他线程对该资源进行操作的时候会被阻塞.

  • 乐观锁

当一个线程需要对一个共享资源进行操作的时候,不对它进行加锁,而是在操作完成之后进行判断。
比如乐观锁会通过一个版本号控制,如果操作完成后通过版本号进行判断在该线程操作过程中是否有其他线程已经对该共享资源进行操作了,如果有则通知操作失败,如果没有则操作成功,当然除了版本号还有CAS,如果不了解的可以去学习一下,这里不做过多涉及。

MVCC的两种读形式:

  • 快照读

读取的只是当前事务的可见版本,不用加锁。而你只要记住 简单的 select操作就是快照读(select * from table where id = xxx)。

  • 当前读

读取的是当前版本,比如 特殊的读操作,更新/插入/删除操作.

比如:

 select * from table where xxx lock in share mode,
 select * from table where xxx for update,
 update table set....
 insert into table (xxx,xxx) values (xxx,xxx)
 delete from table where id = xxx

MVCC的实现原理:

MVCC 使用了“三个隐藏字段”来实现版本并发控制,MySQL在创建建表的时候 innoDB 创建的真正的三个隐藏列吧。

RowID DB_TRX_ID DB_ROLL_PTR id name password
自动创建的id 事务id 回滚指针 id name password
  • RowID:隐藏的自增ID,当建表没有指定主键,InnoDB会使用该RowID创建一个聚簇索引。

  • DB_TRX_ID:最近修改(更新/删除/插入)该记录的事务ID。

  • DB_ROLL_PTR:回滚指针,指向这条记录的上一个版本。

其实还有一个删除的flag字段,用来判断该行记录是否已经被删除。

而 MVCC 使用的是其中的 事务字段,回滚指针字段,是否删除字段。

我们来看一下现在的表格(isDelete是我自己取的,按照官方说法是在一行开头的content里面,这里其实位置无所谓,你只要知道有就行了)。

isDelete DB_TRX_ID DB_ROLL_PTR id name password
true/false 事务id 回滚指针 id name password

14. Mvcc和Redolog和Undolog以及Binlog有什么不同

  • Mvcc

MVCC多版本并发控制是MySQL中基于乐观锁理论实现隔离级别的方式,用于读已提交和可重复读取隔离级别的实现。在MySQL中,会在表中每一条数据后面添加两个字段,最近修改该行数据的事务ID,指向该行(undolog表中)回滚段的指针。

Read View判断行的可见性,创建一个新事务时,copy一份当前系统中的活跃事务列表。意思是,当前不应该被本事务看到的其他事务id列表。

  • UndoLog

UndoLog也就是我们常说的回滚日志文件 主要用于事务中执行失败,进行回滚,以及MVCC中对于数据历史版本的查看。由引擎层的InnoDB引擎实现,是逻辑日志,记录数据修改被修改前的值,比如”把id=’B’ 修改为id = ‘B2’ ,那么undo日志就会用来存放id =’B’的记录”。

当一条数据需要更新前,会先把修改前的记录存储在undolog中,如果这个修改出现异常,,则会使用undo日志来实现回滚操作,保证事务的一致性。当事务提交之后,undo log并不能立马被删除,而是会被放到待清理链表中,待判断没有事物用到该版本的信息时才可以清理相应undolog。它保存了事务发生之前的数据的一个版本,用于回滚,同时可以提供多版本并发控制下的读(MVCC)也即非锁定读。

  • Redolog

Redolog是重做日志文件是记录数据修改之后的值,用于持久化到磁盘中。

Redolog包括两部分:

一. 是内存中的日志缓冲(redo log buffer),该部分日志是易失性的;

二. 是磁盘上的重做日志文件(redo log file),该部分日志是持久的。

由引擎层的InnoDB引擎实现,是物理日志,记录的是物理数据页修改的信息,比如“某个数据页上内容发生了哪些改动”。当一条数据需要更新时,InnoDB会先将数据更新,然后记录redoLog 在内存中,然后找个时间将redoLog的操作执行到磁盘上的文件上。
不管是否提交成功我都记录,你要是回滚了,那我连回滚的修改也记录。它确保了事务的持久性。

  • Binlog

Binlog由Mysql的Server层实现,是逻辑日志,记录的是sql语句的原始逻辑,比如”把id=’B’ 修改为id = ‘B2’。

Binlog会写入指定大小的物理文件中,是追加写入的,当前文件写满则会创建新的文件写入。

产生:事务提交的时候,一次性将事务中的sql语句,按照一定的格式记录到binlog中。

用于复制和恢复在主从复制中,从库利用主库上的binlog进行重播(执行日志中记录的修改逻辑),实现主从同步。业务数据不一致或者错了,用binlog恢复。

15. Mysql读写分离以及主从同步

  • 原理:主库将变更写binlog日志,然后从库连接到主库后,从库有一个IO线程,将主库的binlog日志拷贝到自己本地,写入一个中继日志中,接着从库中有一个sql线程会从中继日志读取binlog,然后执行binlog日志中的内容,也就是在自己本地再执行一遍sql,这样就可以保证自己跟主库的数据一致。

  • 问题:这里有很重要一点,就是从库同步主库数据的过程是串行化的,也就是说主库上并行操作,在从库上会串行化执行,由于从库从主库拷贝日志以及串行化执行sql特点,在高并发情况下,从库数据一定比主库慢一点,是有延时的,所以经常出现,刚写入主库的数据可能读不到了,要过几十毫秒,甚至几百毫秒才能读取到。还有一个问题,如果突然主库宕机了,然后恰巧数据还没有同步到从库,那么有些数据可能在从库上是没有的,有些数据可能就丢失了。所以mysql实际上有两个机制,一个是半同步复制,用来解决主库数据丢失问题,一个是并行复制,用来解决主从同步延时问题。

    • 半同步复制:semi-sync复制,指的就是主库写入binlog日志后,就会将强制此时立即将数据同步到从库,从库将日志写入自己本地的relay log之后,接着会返回一个ack给主库,主库接收到至少一个从库ack之后才会认为写完成。

    • 并发复制:指的是从库开启多个线程,并行读取relay log中不同库的日志,然后并行重放不同库的日志,这样库级别的并行。(将主库分库也可缓解延迟问题)

16. InnoDB的关键特性

  • 插入缓冲:对于非聚集索引的插入或更新操作,不是每一次直接插入到索引页中,而是先判断插入的非聚集索引页是否在缓冲池中,若在,则直接插入;若不在,则先放入到一个Insert Buffer对象中。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge(合并)操作,这时通常能将多个插入合并到一个操作中(因为在一个索引页中),这就大大提高了对于非聚集索引插入的性能。

  • 两次写:两次写带给InnoDB存储引擎的是数据页的可靠性,有经验的DBA也许会想,如果发生写失效,可以通过重做日志进行恢复。这是一个办法。但是必须清楚地认识到,如果这个页本身已经发生了损坏(物理到page页的物理日志成功页内逻辑日志失败),再对其进行重做是没有意义的。这就是说,在应用(apply)重做日志前,用户需要一个页的副本,当写入失效发生时,先通过页的副本来还原该页,再进行重做。在对缓冲池的脏页进行刷新时,并不直接写磁盘,而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer,之后通过doublewrite buffer再分两次,每次1MB顺序地写入共享表空间的物理磁盘上,这就是doublewrite。

  • 自适应哈希索引:InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,称之为自适应哈希索引。

  • 异步IO:为了提高磁盘操作性能,当前的数据库系统都采用异步IO(AIO)的方式来处理磁盘操作。AIO的另一个优势是可以进行IO Merge操作,也就是将多个IO合并为1个IO,这样可以提高IOPS的性能。

  • 刷新邻接页:当刷新一个脏页时,InnoDB存储引擎会检测该页所在区(extent)的所有页,如果是脏页,那么一起进行刷新。这样做的好处显而易见,通过AIO可以将多个IO写入操作合并为一个IO操作,故该工作机制在传统机械磁盘下有着显著的优势。

17. Mysql如何保证一致性和持久性

Mysql为了保证ACID中的一致性和持久性,使用了WAL(Write-Ahead Logging,先写日志再写磁盘)。Redo log就是一种WAL的应用。

当数据库忽然掉电,再重新启动时,Mysql可以通过Redo log还原数据。也就是说,每次事务提交时,不用同步刷新磁盘数据文件,只需要同步刷新Redo log就足够了。

18. 为什么选择B+树作为索引结构

  • Hash索引:Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,就需要全表扫描。所以,哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描

  • 二叉查找树:解决了排序的基本问题,但是由于无法保证平衡,可能退化为链表。

  • 平衡二叉树:通过旋转解决了平衡的问题,但是旋转操作效率太低。

  • 红黑树:通过舍弃严格的平衡和引入红黑节点,解决了AVL旋转效率过低的问题,但是在磁盘等场景下,树仍然太高,IO次数太多。

  • B+树:在B树的基础上,将非叶节点改造为不存储数据纯索引节点,进一步降低了树的高度;此外将叶节点使用指针连接成链表,范围查询更加高效。
    此外, B+树, 主要是查询效率高,O(logN),可以充分利用磁盘预读的特性,多叉树,深度小,叶子结点有序且存储数据.

19. InnoDB的行锁模式

  • 共享锁(S):用法lock in share mode,又称读锁,允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。

若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

  • 排他锁(X):用法for update,又称写锁,允许获取排他锁的事务更新数据,阻止其他事务取得相同的数据集共享读锁和排他写锁。
    若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。在没有索引的情况下,InnoDB只能使用表锁。

20. 哈希(hash)比树(tree)更快,索引结构为什么要设计成树型

加速查找速度的数据结构,常见的有两类:

(1)哈希,例如HashMap,查询/插入/修改/删除的平均时间复杂度都是O(1);
(2)树,例如平衡二叉搜索树,查询/插入/修改/删除的平均时间复杂度都是O(lg(n));

哈希只能满足等值查询, 不满足范围和大小查询, 其次哈希不可以排序.

Mysql是用等值查询,用树的话,等值查询只需要顺序遍历即可.

但是对于排序查询的sql需求:分组:group by ,排序:order by ,比较:<、>等,哈希型的索引,时间复杂度会退化为O(n),而树型的“有序”特性,依然能够保持O(log(n)) 的高效率。

21. 为什么索引的key长度不能太长

key 太长会导致一个页当中能够存放的 key 的数目变少,间接导致索引树的页数目变多,索引层次增加,从而影响整体查询变更的效率。

22. Mysql的数据如何恢复到任意时间点

恢复到任意时间点以定时的做全量备份,以及备份增量的 binlog 日志为前提。恢复到任意时间点首先将全量备份恢复之后,再此基础上回放增加的 binlog 直至指定的时间点。

23. Mysql为什么加了索引可以加快查询

在数据十分庞大的时候,索引可以大大加快查询的速度,这是因为使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

索引的优缺点:

优势:可以快速检索,减少I/O次数,加快检索速度;根据索引分组和排序,可以加快分组和排序;

劣势:索引本身也是表,因此会占用存储空间,一般来说,索引表占用的空间的数据表的1.5倍;索引表的维护和创建需要时间成本,这个成本随着数据量增大而增大;构建索引会降低数据表的修改操作(删除,添加,修改)的效率,因为在修改数据表的同时还需要修改索引表.

24. Explain命令有什么用

在开发的过程中,我们有时会用慢查询去记录一些执行时间比较久的Sql语句,找出这些Sql语句并不意味着完事了,这个时候我们就需要用到explain这个命令来查看一个这些Sql语句的执行计划,查看该Sql语句有没有使用上了索引,有没有做全表扫描,这些都可以通过explain命令来查看。
所以我们深入了解Mysql的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用。

> explain select * from server;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | server  | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

expain出来的信息有10列,分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra.

id: select选择标识符.

select_type: 表示查询的类型.

table: 输出结果集的表.

partitions: 匹配的分区.

type: 表示表的连接类型.

possible_keys: 表示查询时,可能使用的索引.

key: 表示实际使用的索引.

key_len: 索引字段的长度.

ref: 列与索引的比较.

rows: 扫描出的行数(估算的行数).

filtered: 按表条件过滤的行百分比.

Extra: 执行情况的描述和说明.
  • id

id是Sql执行的顺序的标识,Sql从大到小的执行:

  1. id相同时,执行顺序由上至下.

  2. 如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行.

  3. id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行.

  • select_type 查询的类型

示查询中每个select子句的类型:

  1. SIMPLE(简单SELECT,不使用UNION或子查询等)

  2. PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

  3. UNION(UNION中的第二个或后面的SELECT语句)

  4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)

  5. UNION RESULT(UNION的结果)

  6. SUBQUERY(子查询中的第一个SELECT)

  7. DEPENDENT SUBQUERY(子查询中的第一个SELECT,取决于外面的查询)

  8. DERIVED(派生表的SELECT, FROM子句的子查询)

  9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)

  • table

table显示这一行的数据是关于哪张表的,有时不是真实的表名字,看到的是derivedx.

> explain select * from (select * from ( select * from t1 where id=2602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t1         | const  | PRIMARY,idx_t1_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
  • type 表的连接类型

type表示Mysql在表中找到所需行的方式,又称“访问类型”。

常用的类型有: ALL, index, range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好).

  1. ALL:Full Table Scan, Mysql将遍历全表以找到匹配的行.

  2. index: Full Index Scan,index与ALL区别为index类型只遍历索引树.

  3. range:只检索给定范围的行,使用一个索引来选择行.

  4. ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值.

  5. eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件.

  6. const、system: 当Mysql对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,Mysql就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system.

  7. NULL: Mysql在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

  • possible_keys

possible_keys指出Mysql能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用.

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询.

  • Key

key列显示MySql实际决定使用的键(索引).

如果没有选择索引,键是NULL。要想强制Mysql使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX

  • key_len

key_len表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)不损失精确性的情况下,长度越短越好 .

  • ref

ref表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值.

  • rows

rows表示Mysql根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数.

  • Extra执行情况的描述和说明

该列包含Mysql解决查询的详细信息,有以下几种情况:

  1. Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤.

  2. Using temporary:表示Mysql需要使用临时表来存储结果集,常见于排序和分组查询.

  3. Using filesort:Mysql中无法利用索引完成的排序操作称为“文件排序”

  4. Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。

  5. Impossible where:这个值强调了where语句会导致没有符合条件的行。

  6. Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行.

mysql-MVCC 机制的原理及实现

MySQL InnoDB MVCC 机制的原理及实现

MVCC 是什么?

数据库并发控制——锁

Multiversion (version) concurrency control (MCC or MVCC) 多版本并发控制 ,它是数据库管理系统一种常见的并发控制。
我们知道并发控制常用的是锁,当线程要对一个共享资源进行操作的时候,加锁是一种非常简单粗暴的方法(事务开始时给 DQL 加读锁,给 DML 加写锁),这种锁是一种 悲观 的实现方式,也就是说这会给其他事务造成堵塞,从而影响数据库性能。
我来解释一下 乐观锁 和 悲观锁 的概念。我觉得它俩主要是概念的理解。

  • 悲观锁: 当一个线程需要对共享资源进行操作的时候,首先对共享资源进行加锁,当该线程持有该资源的锁的时候,其他线程对该资源进行操作的时候会被 阻塞。比如 Java 中的 Synchronized 关键字。
  • 乐观锁:当一个线程需要对一个共享资源进行操作的时候,不对它进行加锁,而是在操作完成之后进行判断。(比如乐观锁会通过一个版本号控制,如果操作完成后通过版本号进行判断在该线程操作过程中是否有其他线程已经对该共享资源进行操作了,如果有则通知操作失败,如果没有则操作成功),当然除了 版本号 还有 CAS,如果不了解的可以去学习一下,这里不做过多涉及。

数据库并发控制——MVCC

很多人认为 MVCC 就是一种 乐观锁 的实现形式,而我认为 MVCC 只是一种 乐观 的实现形式,它是通过 一种 可见性算法 来实现数据库并发控制。

MVCC 的两种读形式

在讲 MVCC 的实现原理之前,我觉很有必要先去了解一下 MVCC 的两种读形式。

  • 快照读:读取的只是当前事务的可见版本,不用加锁。而你只要记住 简单的 select 操作就是快照读(select * from table where id = xxx)。
  • 当前读:读取的是当前版本,比如 特殊的读操作,更新/插入/删除操作

比如:

 select * from table where xxx lock in share mode,
 select * from table where xxx for update,
 update table set....
 insert into table (xxx,xxx) values (xxx,xxx)
 delete from table where id = xxx

MVCC 的实现原理

MVCC 使用了“三个隐藏字段”来实现版本并发控制,我查了很多资料,看到有很多博客上写的是通过 一个创建事务id字段和一个删除事务id字段 来控制实现的。但后来发现并不是很正确,我们先来看一看 MySQL 在建表的时候 innoDB 创建的真正的三个隐藏列吧。

RowID DB_TRX_ID DB_ROLL_PTR id name password
自动创建的id 事务id 回滚指针 id name password
  • RowID:隐藏的自增ID,当建表没有指定主键,InnoDB会使用该RowID创建一个聚簇索引。
  • DB_TRX_ID:最近修改(更新/删除/插入)该记录的事务ID。
  • DB_ROLL_PTR:回滚指针,指向这条记录的上一个版本。

其实还有一个删除的flag字段,用来判断该行记录是否已经被删除。

而 MVCC 使用的是其中的 事务字段,回滚指针字段,是否删除字段。我们来看一下现在的表格(isDelete是我自己取的,按照官方说法是在一行开头的content里面,这里其实位置无所谓,你只要知道有就行了)。

isDelete DB_TRX_ID DB_ROLL_PTR id name password
true/false 事务id 回滚指针 id name password

那么如何通过这三个字段来实现 MVCC 的 可见性算法 呢?
还差点东西! undoLog(回滚日志) 和 read-view(读视图)。

  • undoLog: 事务的回滚日志,是 可见性算法 的非常重要的部分,分为两类。
    • insert undo log:事务在插入新记录产生的undo log,当事务提交之后可以直接丢弃
    • update undo log:事务在进行 update 或者 delete 的时候产生的 undo log,在快照读的时候还是需要的,所以不能直接删除,只有当系统没有比这个log更早的read-view了的时候才能删除。ps:所以长事务会产生很多老的视图导致undo log无法删除 大量占用存储空间。
  • read-view: 读视图,是MySQL秒级创建视图的必要条件,比如一个事务在进行 select 操作(快照读)的时候会创建一个 read-view ,这个read-view 其实只是三个字段。
    • alive_trx_list(我自己取的):read-view生成时刻系统中正在活跃的事务id。
    • up_limit_id:记录上面的 alive_trx_list 中的最小事务id。
    • low_limit_id:read-view生成时刻,目前已出现的事务ID的最大值 + 1。

这时候,万事俱备,只欠东风了。下面我来介绍一下,最重要的 可见性算法。
其实主要思路就是:当生成read-view的时候如何去拿获取的 DB_TRX_ID 去和 read-view 中的三个属性(上面讲了)去作比较。我来说一下三个步骤,如果不是很理解可以参考着我后面的实践结合着去理解。

  • 首先比较这条记录的 DB_TRX_ID 是否是 小于 up_limit_id 或者 等于当前事务id。如果满足,那么说明当前事务能看到这条记录。如果大于则进入下一轮判断
  • 然后判断这条记录的 DB_TRX_ID 是否 大于等于 low-limit-id。如果大于等于则说明此事务无法看见该条记录,不然就进入下一轮判断。
  • 判断该条记录的 DB_TRX_ID 是否在活跃事务的数组中,如果在则说明这条记录还未提交对于当前操作的事务是不可见的,如果不在则说明已经提交,那么就是可见的。

如果此条记录对于该事务不可见且 ROLL_PTR 不为空那么就会指向回滚指针的地址,通过undolog来查找可见的记录版本。

下面我画了一个可见性的算法的流程图

实践

准备数据

首先我创建了一个非常简单的表,只有id和name的学生表。

id name
学生id 学生姓名

这个时候我们将我们需要的隐藏列也标识出来,就变成了这样

isDelete id name DB_TRX_ID DB_ROLL_PTR
是否被删除 学生id 学生姓名 创建删除更新该记录的事务id 回滚指针

这个时候插入三行数据,将表的数据变成下面这个样子。

|isDelete |id| name| DB_TRX_ID| DB_ROLL_PTR|
|—-|—-|—-|—-|—-|
|false |1 |小明 |1 |null
false |2 |小方 |1| null
false |3| 小张 |1| null

示例一

使用过 MySQL 的都知道,因为隔离性,事务 B 此时获取到的数据肯定是这样的。

|id| name|
|—-|—-|
1 |小明
2 |小方
3 |小张

为什么事务A未提交的修改对于事务B是不可见的,MVCC 是如何做到的?我们用刚刚的可见性算法来实验一下。
首先事务A开启了事务(当然这不算开启,在RR模式下 真正获取read-view的是在进行第一次进行快照读的时候)。我们假设事务A的事务id为2,事务B的id为3。
然后事务A进行了更新操作,如图所示,更新操作创建了一个新的版本并且新版本的回滚指针指向了旧的版本(注意 undo log其实存放的是逻辑日志,这里为了方便我直接写成物理日志)。

最后 事务B 进行了快照读,注意,这是我们分析的重点。

首先,在进行快照读的时候我们会创建一个 read-view (忘记回去看一下那三个字段) 这个时候我们的 read-view 是:

  up-limit-id = 2
  alive-trx-list = [2,3]
  low-limit-id = 4

然后我们获取那两个没有被修改的记录(没有顺序,这里为了一起解释方便), 我们获取到(2,小方)和(3,小张)这两条记录,发现他们两的 DB_TRX_ID = 1, 我们先判断 DB_TRX_ID 是否小于 up-limit-id 或者等于当前事务id , 发现 1<2 小于 up-limit-id ,则可见 直接返回视图。

然后我们获取更改了的数据行:

其实你也发现了这是一个链表,此时链表头的 DB_TRX_ID 为 2,我们进行判断 2 < 2 不符合,进入下一步判断, 判断 DB_TRX_ID >= low_limit_id 发现此时是 2 >= 4 不符合 故再进入下一步,此时判断 Db_TRX_ID 是否在 alive_trx_list 活跃事务列表中,发现这个 DB_TRX_ID 在活跃列表中,所以只能说明该行记录还未提交,不可见。最终判断不可见之后通过回滚指针查看旧版本,发现此时 DB_TRX_ID 为1,故再次进行判断 DB_TRX_ID < up-limit-id ,此时 1 < 2 符合 ,所以可见并返回, 所以最终返回的是

|id |name|
|—-|—-|
1 |小明
2 |小方
3 |小张

我们再来验证一下,这个时候我们将事务A提交,重新创建一个事务C并select。

我们预期的结果应该是这样的

|id |name|
|—-|—-|
1 |小强
2 |小方
3 |小张

这个操作的流程图如下

这个时候我们再来分析一下 事务c产生的 read-view。

这个时候事务A已经提交,所以事务A不在活跃事务数组中,此时 read-view 的三个属性应该是

 up-limit-id = 3
 alive-trx-list = [3,4]
 low-limit-id = 5
  • 跟上面一样,我们首先获取(2,小方)和(3,小张)这两条记录,发现他们两的 DB_TRX_ID = 1,此时 1 < up-limit-id = 3,故符合可见性,则返回。
  • 然后我们获取刚刚被修改的id为1的记录行,发现链表头部的 DB_TRX_ID 为 2, 此时 2 < up-limit-id = 3 故也符合可见性,则返回。

所以最终返回的就是

|id |name|
|—-|—-|
1 |小强
2 |小方
3 |小张

示例二

为了加深理解,我们再使用一个相对来说比较复杂的示例来验证 可见性算法 。

首先我们在事务A中删除一条记录,这个时候就变成了下面的样子。

然后事务B进行了插入,这样就变成了下面这样。

然后事务B进行了 select 操作,我们可以发现 这个时候整张表其实会变成这样让这个 select 操作进行选取。

此时的 read-view 为

 up-limit-id = 2
 alive-trx-list = [2,3,4]
 low-limit-id = 5

这个时候我们进行 快照读,首先对于前面两条小明和小方的记录是一样的,此时 DB_TX_ID 为 1,我们可以判断此时 DB_TX_ID = 1 < up-limit-id = 2 成立故返回。然后判断小张这条记录,首先也是 DB_TX_ID = 2 < up-limit-id = 2 不成立故进入下一轮,DB_TX_ID = 2 >= low-limit-id 不成立再进入最后一轮判断是否在活跃事务列表中,发现 DB_TX_ID = 2 在 alive-trx-list = [2,3,4] 中故不可见(如果可见则会知道前面的删除标志是已经删除,则返回的是空),则根据回滚指针找到上一个版本记录,此时 DB_TX_ID = 1 和上面一样可见则返回该行。
最后一个判断小亮这条记录,因为 DB_TX_ID = current_tx_id(当前事务id) 所以可见并返回。
这个时候返回的表则是这样的

|id |name|
|—-|—-|
1 |小明
2 |小方
3 |小张
4 |小亮

然后是事务A进行了select的操作,我们可以得知现在的 read-view 为

 up-limit-id = 2
 alive-trx-list = [2,3,4]
 low-limit-id = 5

然后此时所见和上面也是一样的

这个时候我们进行 快照读,首先对于前面两条小明和小方的记录是一样的,此时 DB_TX_ID 为 1,我们可以判断此时 DB_TX_ID = 1 < up-limit-id = 2 成立故返回。然后判断小张这条记录,首先 DB_TX_ID = 2 = current_tx_id = 2 成立故返回发现前面的 isDelete 标志为true 则说明已被删除则返回空,对于第四条小亮的也是一样判断 DB_TX_ID = 4 < up-limit-id = 2 不成立进入下一步判断 DB_TX_ID = 4 >= low-limit-id = 5 不成立进入最后一步发现在活跃事务数组中故不可见且此条记录回滚指针为null所以返回空。
那么此时返回的列表应该就是这样了

|id| name|
|—-|—-|
1 |小明
2 |小方

虽然要分析很多,但多多益善嘛,多熟悉熟悉就能更深刻理解这个算法了。

之后是事务C进行 快照读 操作。首先此时视图还是这个样子

然后对于事务C的 read-view 为

 up-limit-id = 2
 alive-trx-list = [2,3,4]
 low-limit-id = 5

小明和小方的两条记录和上面一样是可见的这里我就不重复分析了,然后对于小张这条记录 DB_TX_ID = 2 < up-limit-id = 2 || DB_TX_ID curent_tx_id = 4 不成立故进入下一轮发现 DB_TX_ID >= low-limit-id = 5 更不成立故进入最后一轮发现 DB_TX_ID = 2 在活跃事务数组中故不可见,然后通过回滚指针判断 DB_TX_ID = 1 的小张记录发现可见并返回。最后的小亮也是如此 最后会发现 DB_TX_ID = 3 也在活跃事务数组中故不可见。
所以事务C select 的结果为

|id| name|
|—-|—-|
1 |小明
2 |小方
3 |小张

后面事务A和事务B都进行了提交的动作,并且有一个事务D进行了快照读,此时视图还是如此

但此时的 read-view发生了变化

 up-limit-id = 4
 alive-trx-list = [4,5]
 low-limit-id = 6

我们首先判断小明和小方的记录——可见(不解释了),小张的记录 DB_TX_ID = 2 < up-limit-id = 4 成立故可见,因为前面 isDelete 为 true 则说明删除了返回空,然后小亮的记录 DB_TX_ID = 3 < up-limit-id = 4 成立故可见则返回。所以这次的 select 结果应该是这样的

|id |name|
|—-|—-|
1 |小明
2 |小方
4 |小亮

最后(真的最后了,不容易吧!),事务C有一次进行了 select 操作。因为在 RR 模式下 read-view 是在第一次快照读的时候确定的,所以此时 read-view是不会更改的,然后前面视图也没有进行更改,所以此时即使前面事务A 事务B已经进行了提交,对于这个时候的事务C的select结果是没有影响的。故结果应该为

|id|name|
|—-|—-|
1|小明
2|小方
3|小张

总结
我们来总结一下吧。

其实 MVCC 是通过 “三个” 隐藏字段 (事务id,回滚指针,删除标志) 加上undo log和可见性算法来实现的版本并发控制。

为了你再次深入理解这个算法,我再把这张图挂上来