mysql视图为什么不用 mysql 视图优缺点

一、数据库隔离级别有哪些，各自的含义是什么，MYSQL默认的隔离级别是是什么

【1】Read Uncommitted(读取未提交内容)：出现脏读，也就是可能读取到其他会话中未提交事务修改的数据。

【2】Read Committed(读取已提交内容)：不可重复读，只能读取到已经提交的数据。Oracle 等数据库默认的隔离级别。

【3】Repeatable Read(可重复读)：出现幻读。在同一个事务内的查询都和事务开始时刻一致。InnoDB默认级别。

【4】Serializable(串行读)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

MySQL 默认的隔离级别：Repeatable Read(可重读)级别。

Oracle 默认的隔离级别：Read Committed(读取已提交的)级别。

【可重复读实现原理】：使用MVCC(多版本并发控制)。InnoDB为每行记录添加了一个版本号(系统版本号)，每当修改数据时，版本号加一。在读取事务开始时，系统会给事务一个当前版本号，事务会读取版本号<=当前版本号的数据，这时就算另一个事务插入一个数据，并立马提交，新插入这条数据的版本号会比读取事务的版本号高，因此读取事务读的数据还是不会变。

如果数据库并发控制引擎是单纯的封锁协议机制，则应该在读取数据的时候，判断数据项是不是其他事务更新过的。可是InnoDB没有这么做，而是通过如下方式，在RR隔离级别下为事务设置了一个“一致性读视图(即快照)”，之后读取数据，就是根据这个快照来获取，这样，就不能看到他晚于本事务的事务对已有记录的更新(更新生成新版本，必然不在旧的快照所限定的范围内)。

static my_bool snapshot_handlerton(THD *thd, plugin_ref plugin, void *arg){  handlerton *hton= plugin_data(plugin);  if (hton->state == SHOW_OPTION_YES && hton->start_consistent_snapshot){ //隔离级别是RR时start_consistent_snapshot才被赋值    hton->start_consistent_snapshot(hton, thd); //对于InnoDB，实际执行innobase_start_trx_and_assign_read_view()函数    *((bool *)arg)= false;  }  return FALSE;}

在事务开始的时候 trans_begin() 会调用 snapshot_handlerton() 函数指针即使用 innobase_start_trx_and_assign_read_view() 函数在可重复读隔离级别下创建一个快照，其他隔离级别则不创建快照。如下图：

innobase_start_trx_and_assign_read_view(  //在可重复读隔离级别下创建一个快照，其他隔离级别则不创建快照  handlerton*    hton,    /*!< in: InnoDB handlerton */  THD*           thd)     /*!< in: MySQL thread handle of the user for whom the transaction should be committed */{...  if(trx->isolation_level == TRX_ISO_REPEATABLE_READ) { //如果是RR隔离级别，则给read view赋值，即构建一致性视图    trx_assign_read_view(trx);  //为读一致性视图(快照)赋一个值，注意在store_lock()中应隔离级别小于RR才关闭快照  }else{    push_warning_printf(thd, Sql_condition::SL_WARNING,          HA_ERR_UNSUPPORTED,          "InnoDB: WITH CONSISTENT SNAPSHOT"          " was ignored because this phrase"          " can only be used with"          " REPEATABLE READ isolation level.");  }...}

之后，在每条SQL语句执行的时候，根据隔离级别判断是不是要使用一个新的快照，如果是可重复读，则不使用新快照，沿用老的快照，这样就能保证所有的读操作看到的是同一个数据状态；同时也确保了读已提交隔离级别下一个事务块内的不同语句的读操作看到的不是同一个数据状态。 

ha_innobase::store_lock(...){...  if (lock_type != TL_IGNORE && trx->n_mysql_tables_in_use == 0) {    trx->isolation_level = innobase_map_isolation_level((enum_tx_isolation) thd_tx_isolation(thd));    if (trx->isolation_level <= TRX_ISO_READ_COMMITTED  //隔离级别小于等于读已提交，关闭老的快照。可重复读不关闭老快照所以可以沿用      && MVCC::is_view_active(trx->read_view)) {      /* At low transaction isolation levels we let each consistent read set its own snapshot */      mutex_enter(&trx_sys->mutex);      trx_sys->mvcc->view_close(trx->read_view, true);  //隔离级别小，关闭快照，这样下一条SQL执行时，将获取新快照      mutex_exit(&trx_sys->mutex);    }  }...}

从上面的分析可以看出，InnoDB的可重复读的实现，利用了实现 MVCC技术的快照技术。这是 MVCC 和基于封锁技术这两个并非控制技术的结合之处。

二、什么是幻读

官方：幻读是指当事务不是独立执行时发生的一种现象，例如：第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，向表中插入“一行新数据”。随后就会发现操作第一个事务的用户发现表中还存在没有修改的数据行，就好象发生了幻觉一样。一般解决幻读的方法是增加范围锁RangeS，锁定检索范围为只读，这样就避免了幻读。

个人解读：举个栗子，A查询ID(唯一索引)>6 的数据，查询结果为空，此时B插入一条ID=6 的数据，因为当前A的隔离级别是可重复读，那么当A第二次查询 ID>6 时，还是空，此时A插入 ID=6 的数据，会出现ID冲突不能插入。A就是不明白为什么ID=6不存在但是自己就是插入不了，但我们知道为什么。因为A出现了幻读。

三、MYSQL 有哪些存储引擎，各自优缺点

MYSQL存储引擎种类(了解)：MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。在 Oracle 和 SQL Server 等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而 MySql 数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。简单的说下什么是存储引擎，存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(即存储和操作此表的类型)。

重点介绍三个：MyISAM、InnoDB、MEMORY：

【1】MyISAM：①、拥有较高的插入，查询速度。②、不支持事务，行级锁和外键约束的功能。③、使用表级锁，并发性能差。④、主机宕机后，MyISAM表易损坏，灾难恢复性不佳。⑤、可以配合锁，实现操作系统下数据的复制备份、迁移。⑥、只缓存索引，数据的缓存是通过操作系统缓存区来实现的，可能引发过多的系统调用且效率不佳。⑦、数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能。

【2】InnoDB：5.5版本后 Mysql 事务型数据库的首选引擎，①、支持ACID事务。②、支持行级锁定。③、灾难恢复性好。④、支持外键关联。⑤、支持热备份。⑥、对于InnoDB引擎中的表，其数据的物理组织是簇表(Cluster Table)，主键索引和数据是在一起的，数据按主键的顺序物理分布。⑦、实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且能够自动创建散列索引以加快数据的获取。

【3】MEMORY：①、所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。②、其内容会在 Mysql 重新启动时丢失，复制维护时需要小心。③、使用表级锁，虽然内存访问速度快，但是频繁的读写，表级锁会成为瓶颈。④、只支持固定大小的行，varchar 类型的字段会存储为固定长度的 Char 类型，浪费空间。⑤、不支持TEXT、BLOB 字段，当有些查询需要使用临时表时(因为是存在于内存中，所以这种类型常应用于临时表中)，如果表中有TEXT、BLOB 字段，那么会转换为基于磁盘的 MyISAM 表，严重降低性能。⑥、由于内存资源成本比较昂贵，一般不建议设置过大的内存表，如果内存表满了，可通过清除数据或调整内存表参数来避免报错。⑦、MEMORY 表在所有客户端之间共享。

四、高并发下，如何做到安全的修改同一行数据

【1】 使用悲观锁：悲观锁本质是当前只有一个线程执行操作，排斥外部请求的修改。遇到加锁的状态，就必须等待。结束后唤醒其他线程进行处理。虽然此方案的确解决了数据安全的问题，但是，我们的场景是“高并发”。也就是说，会有很多这样的修改请求，每个请求都需要等待“锁”，某些线程可能永远都没有机会抢到这个“锁”，这种请求就会死在那里。同时，这种请求会很多，瞬间增大系统的平均响应时间，结果是可用的连接数被耗尽，系统陷入异常。(细节参考5)

【2】也是通过 FIFO(First Input First Output，先进先出)缓存队列思路：直接将请求放入队列中，就不会导致某些请求永远获取不到锁。看到这里，是不是有点强行将多线程变成单线程的感觉哈。

然后，我们现在解决了锁的问题，全部请求采用“先进先出”的队列方式来处理。那么新的问题来了，高并发的场景下，因为请求很多，很可能一瞬间将队列内存“撑爆”，然后系统又陷入到了异常状态。或者设计一个极大的内存队列，也是一种方案，但是，系统处理完一个队列内请求的速度根本无法和疯狂涌入队列中的数目相比。也就是说，队列内的请求会越积越多，最终Web系统平均响应时候还是会大幅下降，系统还是陷入异常。

【3】使用乐观锁：这个时候，我们就可以讨论一下“乐观锁”的思路了。乐观锁，是相对于“悲观锁”采用更为宽松的加锁机制，大都是采用带版本号(Version)更新。实现就是，这个数据所有请求都有资格去修改，但会获得一个该数据的版本号，只有版本号符合的才能更新成功，否则返回失败。这样的话，我们就不需要考虑队列的问题，不过，它会增大 CPU 的计算开销。但是，综合来说，这是一个比较好的解决方案。(具体参考5)

五、乐观锁和悲观锁是什么，InnoDB的标准行级锁有哪2种，解释其含义

  ♪ 乐观锁(Optimistic Concurrency Control，缩写”OCC”)：是一种并发控制的方法。乐观的认为多用户并发的事务在处理时不会彼此互相影响，各事务能够在使用锁的情况下处理各自的数据。在提交更新数据之前，每个事务会先检查该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。不过，当需求多为更新数据时，就会增大数据之间的冲突，也就增大 CPU 的计算开销，此时不建议使用。数据是否修改的标准是：对表中的数据进行操作时，先给表中最新的数据加一个版本(version)字段，每操作一次，将该记录的版本号加1。也就是先查询出该记录，获取 version 字段，修改完数据后准备提交之前，先判断此刻 version 的值是否与刚刚查询出来时的 version 的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，并将 version 字段的值加1；如果更新时发现此刻的 version 值与刚刚获取出来的 version 的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。

--1.查询出商品信息select (status,version) from t_goods where id=#{id}--2.根据商品信息生成订单--3.修改商品status为2update t_goodsset status=2,version=version+1where id=#{id} and version=#{version};

  ♪ 悲观锁(Pessimistic Concurrency Control，缩写”PCC”)：与乐观锁相对应的就是悲观锁。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟 java 中的 synchronized 很相似，所以悲观锁需要耗费较多的时间。所以悲观锁并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

注意：要使用悲观锁，就必须关闭 Mysql 数据库的自动提交属性，因为 MySQL 默认使用 autocommit 模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。关闭设置：set autocommit=0;

--0.开始事务begin;/begin work;/start transaction; (三者选一就可以)--1.查询出商品信息select status from t_goods where id=1 for update;--2.根据商品信息生成订单insert into t_orders (id,goods_id) values (null,1);--3.修改商品status为2update t_goods set status=2;--4.提交事务commit;/commit work;

InnoDB的标准行级锁有哪2种：

   ♬共享锁：共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁，在执行语句后面加上 lock in share mode 就代表对某些资源加上共享锁。

   ♬排它锁：排它锁与共享锁相对应，就是指对于多个不同的事务，对同一个资源只能有一把锁。在需要执行的语句后面加上 for update 就可以了。对于 update、insert、delete 语句会自动加排它锁。

总结：乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于 write_condition 机制，其实都是提供的乐观锁。如果经常发生冲突，上层应用会不断进行 retry，这样反而降低了性能，所以这种情况下用悲观锁比较合适

六、SQL 优化的一般步骤是什么，怎么看执行计划，如何理解其中各个字段的含义

【1】通过 show status命令了解各种 sql 的执行频率。mysql 客户端连接成功后，通过show [session|global] status 命令可以提供服务状态信息，也可以使用 mysqladmin extend-status 命令获取这些消息。

通常比较关心的是以下几个统计参数：

1)、Com_select：执行 select 操作的次数，一次查询只累加1。

2)、Com_insert：执行 insert 操作的次数，对于批量插入的 insert 操作，只累加一次。

3)、Com_update：执行 update 操作的次数。   

4)、Com_delete：执行 delete 操作的次数。

上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对 innodb 的，累加的算法也略有不同：

1)、Innodb_rows_read : select 查询返回的行数。  

2)、Innodb_rows_inserted : 执行 insert 操作插入的行数。

3)、Innodb_rows_updated : 执行 update 操作更新的行数。

4)、Innodb_rows_deleted : 执行 delete 操作删除的行数。 

通过以上几个参数，可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的 sql 大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

此外，以下几个参数便于用户了解数据库的基本情况：

1)、Connections ：试图连接 mysql 服务器的次数；

2)、Uptime ：服务器工作时间；

3)、Slow_queries：慢查询次数；

【2】查询执行效率较低的 sql 语句：

  ■ 通过慢查询日志定位那些执行效率较低的 sql 语句，用 --log-slow-queries[=file_name] 选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 sql 语句的日志文件。

  ■ 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 mysql 在进行的线程，包括线程的状态、是否锁表等，可以实时的查看 sql 的执行情况，同时对一些锁表操作进行优化。

【3】通过 explain 分析低效 SQL 的执行计划：查询到效率低的 SQL 语句后，可以通过 explain 或者 desc 命令获取 MySQL 如何执行 select 语句的信息，包括在 select 语句执行过程中表如何连接和连接的顺序。以下是 explain 语句返回参数： 

1)、 id：select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。三种情况：

   ①、id相同：执行顺序由上而下

   ②、id不同：如果是子查询，id 序号会递增，id 越大优先级越高，越先被执行

   ③、id既有相同的也有不同的，两者同时存在：d 如果相同，可以认为是一组，由上往下执行；在所有组里id越大，优先级越高，越先执行。

2)、select_type：类型主要用于区别普通查询、联合查询、子查询等的复杂程度。

  ■  SIMPLE：简单的 select 查询，查询中不包含子查询或者UNION；

  ■  PRIMARY：查询中若包含任何复杂的子查询，最外层查询为 PRIMARY；

  ■  SUBQUERY：在 SELECT 或 WHERE 中包含子查询；

  ■  DERIVED：在 FROM 列表中的子查询被标记为 DERIVED(衍生)MySQL会递归执行这些子查询，把结果放进临时表；

  ■  UNION：若第二个 SELECT出现在 UNION之后，则被标记为 UNION，若 UNION包含在 FROM子句的子查询，则外层SELECT将被标记为DERIVED；

  ■  UNION RESULT：从UNION表中获取结果的SELECT；

3)、table：显示这行数据是关于那张表的。

4)、type：主要类型如下：

从最好到最差：system > const > eq_ref > ref > range > index > ALL，一般达到 rang 级别，最好达到 ref 级别。

5)、possible_keys ：显示可能应用到这张表中的索引，查询字段上若存在索引则列出来，但不一定被查询实际使用。

6)、keys：实际使用的索引。如果未null，则没有使用索引。若查询中出现了覆盖索引(覆盖索引：查询的字段和创建的索引的字段和个数完全一样时)，则该索引只出现 key。

7)、key_len：表示索引中使用的字节数，找出使用索引的长度。在不损坏精准性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际长度，即 key_len 是根据表定义实际计算出来的，不是通过表内检出来的。

8)、ref：显示索引的那一列被使用，如果可能的话，是一个常数。那些列或常量被用于查找索引上的值。

9)、rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录的行数。

10)、Extra：包含不适合在其他列中显示，但十分重要的信息。【Using Where 表示 MySQL 将通过 Where 条件来筛选存储引擎返回的记录】

一般 MySQL 能够使用如下三种方式应用 WHERE 条件，从好到坏以此是：

    【1】在索引中使用 Where 条件来过滤不匹配的记录。这是在存储引擎层完成的。

    【2】使用索引覆盖扫描(Extra 列中出现了 Using index)来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL 服务器层完成的，但无须再回表查询记录。

    【3】从数据表中返回数据，然后过滤不满足条件的记录(Extra 列中出现 Using Where)这在 MySQL 服务器层完成，MySQL 需要先从数据表读取记录然后过滤。

    体现了索引的重要性，好的索引可以让查询使用合适的访问类型，尽可能地只扫描需要的数据行。

七、数据库会死锁吗，举一个死锁的例子，mysql 怎么解决死锁

【1】会产生死锁，具体举个栗子，如下：

-- 创建表test1 CREATE TABLE test1 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(10) NOT NULL, PRIMARY KEY (id) ); insert into test1 values('hello');   -- 创建表test2 CREATE TABLE test2 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(10) NOT NULL, PRIMARY KEY (id) );   -- Transcation 1 begin; delete from test1 where id = 1; delete from test2 where id = 2;   -- Transcation 2 begin; delete from test2 where id = 2; delete from test1 where id = 1;

Transcation1	Transcation2
这条 sql 得到 test1 表主键索引排它S(id=1) delete from test1 where id = 1;
	这条 sql 得到 test2 表主键索引排它S(id=2) delete from test2 where id = 2;
这条 sql 试图获取 test2 中的锁，但是事务2已经获取，只能排队等待。
	这条 sql 试图获取 test1 中的锁，但是事务1已经获取，只能排队等待。此时死锁产生。mysql根据两个事务的权重，事务2的权重更小，被选为死锁的牺牲者，rollback。
T2 rollback 之后T1成功获取了锁执行成功

【2】要解决死锁首先要了解产生死锁的原因：①、系统资源不足。②、进程运行推进的顺序不合适。③、资源分配不当等。

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁

【3】产生死锁的四个必要条件：①、互斥条件：一个资源每次只能被一个进程使用。

   ②、请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。

   ③、不剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺。

   ④、循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

   这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。

【4】这里提供两个解决数据库死锁的方法：①、重启数据库。

   ②、杀掉抢资源的进程：先查哪些进程在抢资源：

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

  ☎、杀掉抢资源的进程：

Kill trx_mysql_thread_id;

八、MySql 的索引原理，索引的类型有哪些，如何创建合理的索引，索引如何优化

【MySql索引的原理】：1)、通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总用同一种查找方式来锁定数据。

   2)、索引是通过复杂的算法，提高数据查询性能的手段。从磁盘 io 到内存 io 的转变。

【MySql索引的类型】：1)、普通索引 index：加速查找

   2)、唯一索引：①、主键索引：primary key：加速查找+主键唯一约束且不为空。

     ②、唯一索引：unique：加速查找+主键唯一约束。

   3)、联合索引：①、primary key(id,name)：联合主键索引。

     ②、unique(id,name)：联合唯一索引。

     ③、unique(id,name)：联合普通索引。

   4)、全文索引 fulltext：用于搜索很长一篇文章的时候，效果最好。

   5)、空间索引 spatial：了解就好，几乎不用。

【创建索引的原则】：1)、最左前缀匹配原则：非常重要的原则，mysql 会一直向右匹配直到遇到范围查询(>、 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2)、= 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。

3)、尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4)、索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5)、尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

九、聚集索引和非聚集索引的区别

“聚簇”：就是索引和记录紧密在一起。

“非聚簇索引”：索引文件和数据文件分开存放，索引文件的叶子页只保存了主键值，要定位记录还要去查找相应的数据块。

聚簇索引：【1】有主键时，根据主键创建聚簇索引。

【2】没有主键时，会用一个唯一且不为空的索引列作为主键，称为此表的聚簇索引。

【3】如果两项都不满足时，innodb自己创建一个虚拟的聚集索引。

十、select for update 是什么含义，会锁表还是锁行或是其他

select for update 语句是我们经常使用手工加锁语句。借助 for update 子句，我们可以在应用程序的层面手工实现数据加锁保护操作。属于并发行锁，这个我们上面在悲观锁的时候也有介绍。

十一、为什么要用 Btree 实现，它是怎么分裂的，什么时候分裂，为什么是平衡的

Key 超过1024才分裂，因为随着数据的增多，一个结点的 key 满了，为了保持 B 树的特性，就会产生分裂，就向红黑树和 AVL树为了保持树的性质需要进行旋转一样！

十二、数据库的ACID是什么

A(atomic)：原子性，要么都提交，要么都失败，不能一部分成功，一部分失败。

C(consistent)：一致性，事务开始及结束后，数据的一致性约束没有被破坏

 I(isolation)：隔离性，并发事务间相互不影响，互不干扰。

D(durabilit)：持久性，已经提交的事务对数据库所做的更新必须永久保存。即便发生崩溃，也不能被回滚或数据丢失。

十三、某个表有近千万数据，CRUD 比较慢，如何优化

数据千万级别之多，占用的存储空间也比较大，可想而知它不会存储在一块连续的物理空间上，而是链式存储在多个碎片的物理空间上。可能对于长字符串的比较，就用更多的时间查找与比较，这就导致用更多的时间。

1)、作为关系型数据库，是什么原因出现了这种大表？是否可以做表拆分，减少单表字段数量，优化表结构。

2)、在保证主键有效的情况下，检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致。

3)、在程序逻辑中采用手动事务控制，不要每插入一条数据就自动提交，而是定义一个计数器，进行批量手动提交，能够有效提高运行速度。

十四、Mysql 怎么优化 table scan 的

■ 避免在 where 子句中对字段进行 is null 判断。

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

 ■ 避免在 where 子句中使用 or 来连接条件。

 ■ in 和not in 也要慎用。

 ■ Like 查询(非左开头)。

 ■ 使用 NUM=@num 参数这种。

 ■ where 子句中对字段进行表达式操作 num/2=XX。

 ■ 在where子句中对字段进行函数操作。

十五、如何写 sql 能够有效的使用到复合索引

由于复合索引=组合索引，类似多个木板拼接在一起，如果中间断了就无法用了，所以要能用到复合索引，首先开头(第一列)要用上，比如index(a,b) 这种，我们可以select table tname where a=XX 用到第一列索引 如果想用第二列 可以 and b=XX 或者and b like ‘TTT%’。

十六、mysql 中 in 和 exists 区别

mysql 中的 in 语句是把外表和内表作 hash 连接，而 exists 语句是对外表作 loop 循环，每次 loop 循环再对内表进行查询。一直大家都认为 exists 比 in 语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

㊤、如果查询的两个表大小相当，那么用 in 和 exists 差别不大。

㊥、如果两个表中一个较小，一个是大表，则子查询表大的用 exists，子查询表小的用 in。

㊦、not in 和 not exists 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而 not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用 not exists 都比 not in 要快。

EXISTS 只返回 TRUE 或 FALSE，不会返回 UNKNOWN

IN 当遇到包含 NULL的情况，那么就会返回 UNKNOWN

十七、数据库自增主键可能的问题

【1】使用自增主键对数据库做分库分表，可能出现一些诸如主键重复等的问题。

【2】数据库导入的时候，可能会因为主键出现一些问题。

十八、MVCC 的含义，如何实现的

MVCC：Multi-Version Concurrency Control 多版本并发控制，MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存。MVCC 最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的 OLTP 应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的 RDBMS，都支持了 MVCC。

十九、项目里遇到分库分表了吗？怎么做的，有用到中间件么，比如 sharding jdbc等，原理是什么。

垂直分表：垂直分表在日常开发和设计中比较常见，通俗的说法叫做“大表拆小表”，拆分是基于关系型数据库中的“列”(字段)进行的。通常情况，某个表中的字段比较多，可以新建立一张“扩展表”，将不经常使用或者长度较大的字段拆分出去放到“扩展表”中，如下图所示：

 

垂直分库：垂直分库在“微服务”盛行的今天已经非常普及了，基本思路是按照业务模块划分不同的数据库，而不是将所有的数据库表都放到同一个库中。

水平分表：水平分表也称为横向分表，比较容易理解，就是将表中不同的数据按照一定规律分布到不通的数据库表中，这样来降低单表的数据量，优化查询性能，水平分表能降低单表数据量，一定程度上可以缓解查询性能的瓶颈，但本质上这些表保存在同一个库中，所以库级别还是会有io瓶颈。

水平分库分表：水平分库分表与上面讲到的水平分表思路相同，唯一不同就是将这些拆分出来的表保存在不同的数据库中。

二十、MySQL 的主从延迟怎么解决

实际上主从同步延迟根本没有什么一招制敌的办法，因为所有的 SQL 必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入， 那么一旦有延迟产生，那么延迟加重的可能性就会越来越大。当然我们可以做一些缓解的措施。

a)、最简单的减少 slave 同步延时的方案就是在架构上做优化，尽量让主库的 DDL 快速执行。还有就是主库是写，对数据安全性较高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而 slave 则不需要这么高的数据安全，完全可以将 sync_binlog 设置为 0 或者关闭 binlog，innodb_flushlog 也可以设置为 0 来提高 sql 的执行效率。另外就是使用比主库更好的硬件设备作为 slave。

b)、把一台从服务器当作备份使用， 而不提供查询， 这样他的负载就下来了， 执行 relay log 里面的 SQL 效率自然就高了。 

c)、增加从服务器，这个目的还是分散读的压力， 从而降低服务器负载。

二十一、数据表结构设计

【1】数据类型：最小的数据类型的通常更好，更快、占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期更少。简单就好，通常需要更少的 CPU 周期，例如，整型比字符操作代价更低，因为字符集和校对规则使字符比较比整型比较更复杂。尽量避免 NULL，通常最好指定为 NOT NULL 除非真的需要存储 NULL 值。如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。

【2】根据业务表的特点进行范式或者反范式设计。一般我们都是混用范式和反范式。

【3】一般在设计表的时候，不要有太多的关联，根据竟然就是不要超过16个表之间的关联，虽然 MySql 支持的最大关联的表是 64 张表。

【4】在设计表的时候，必须添加表的说明和表属性的说明。方便日后的维护和使用。common 字段可以添加说明。

【5】表名的长度也是有限制的 Oracle 中比较容易遇到这个问题，因为它的长度时30字节，MySQL的长度限制时64字节。

【6】在设计表的时候，我们一般都会根据业务预留3-6个字段，防止后期业务添加功能等。

【7】每个表都需要有自己的主键。

【8】数据库字段统一小写，单词之间使用下划线分隔。

【9】可以使用 varhchar的字段尽可能不使用TEXT、BLOB类型。

【10】表字符集选择UTF8。

二十二、MySQL innodb 的事务与日志实现方式

【1】错误日志：记录出错信息，也记录一些警告信息或者正确的信息。

【2】查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。

【3】慢查询日志：设置一个阈值，将运行时间超过该值的所有 SQL 语句都记录到慢查询的日志文件中。

【4】二进制日志：记录对数据库执行更改的所有操作。

【5】中继日志：中继日志也是二进制日志，用来给 slave 库恢复。

【6】事务日志：重做日志 redo 和回滚日志 undo。

事务日志是通过 redo 和 innodb 的存储引擎日志缓冲(innodb log buffer)来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号；当事务执行时，会往 InnoDB 存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘(通过 innodb_flush_log_at_trx_commit 来控制)，也就是写数据前，需要先写日志。这种方式称为 “预写日志方式”。

二十三、MySQL binlog 的几种日志录入格式以及区别

【1】Statement：每一条会修改数据的 sql 都会记录在 binlog 中。优点：不需要记录每一行的变化，减少 binlog 日志量 ，节约了 IO，提高了性能。缺点：由于记录的只是执行语句，为了这些语句能在 salve 上正确运行，因此还必须记录每条语句在执行时候的一些相关信息，以保证所有语句能在 slave 得到和 master 端执行时候相同的结果。

【2】row：不记录 sql 语句上下文相关信息，仅保存那条记录被修改。优点：binlog 中可以不记录执行的 sql 语句的上下文相关的信息，仅需要记录那一条记录别修改成什么了。所以rowlevel 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或 function，以及 trigger 的调用和触发无法被正确复制的问题。缺点：所有的执行语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。比如一条 update 语句，修改多条记录，则binlog 中每一条修改都会记录，这样会造成binlog 量会很大，特别是当执行 alter table 之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

【3】Mixedlevel：以上两种 level 的混合使用。一般的语句修改使用 statement 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog，MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式，也就是在 Statement 和 Row 之间选择一种。新版本的 MySQL 中对 row level 模式也被做了优化，并不是所有的修改都会以 row level 来记录，像遇到表结构变更的时候就会以 statement 模式来记录。至于 update 或者 delete 等修改数据的语句，还是会记录所有行的变更。

二十四、同时出现 usingfilesort 和 usingindex 的场景

表示可通过覆盖索引获取全部信息，但有排序；

mysql> explain select first_name, last_name, birth_date from employees where first_name = ‘Tomofumi‘ order by birth_date desc;+----+-------------+-----------+------+----------------------+----------------------+---------+-------+------+------------------------------------------+| id | select_type | table     | type | possible_keys        | key                  | key_len | ref   | rows | Extra                                    |+----+-------------+-----------+------+----------------------+----------------------+---------+-------+------+------------------------------------------+|  1 | SIMPLE      | employees | ref  | idx_first_last_birth | idx_first_last_birth | 44      | const |  244 | Using where; Using index; Using filesort |

二十五、in 与 exist 生成的执行计划

MySQL执行计划是 sql语句经过查询优化器后，查询优化器会根据用户的sql语句所包含的字段和内容数量等统计信息，选择出一个执行效率最优(MySQL系统认为最优)的执行计划，然后根据执行计划，调用存储引擎提供的接口，获取数据。但是，在MySQL执行的时候，到底使用了一个什么样的执行计划，有没有用到索引。当数据规模比较大的时候，sql执行的时候，执行计划不同，会直接影响sql的执行速度。这个时候，就需要对sql语句执行进行调试。MySQL我们在调试 sql语句的时候，不会像我们写 Java或者其他语言代码那样通过打断点的方式进行代码调试。这个时候，我们就需要通过查看执行计划来调试我们的sql了。MySQL通过 EXPLAIN来查看执行计划，我们写 sql语句的时候，在语句之前加一个 EXPLAIN就可以了。EXPLAIN可以用在SELECT、DELETE、INSERT、PEPLACE以及UPDATE等语句中，如：【字段属性值不清楚查看问题六】

【1】in 后面只有1个值：

   ①、对于主键或者唯一索引，那么 type=const，这种性能最高，表示表中只有1个记录能满足查询 ；

   ② 、对于普通索引、或者联合主键，type=ref；  

   ③、对于普通字段，type=all，这种性能最差；

【2】in 后面有多个值时：

   ①、不管是主键，还是唯一索引，还是普通索引，type=range；

   ②、但是在这里需要注意的一个特例是：当你的索引的 Cardinality属性比较低时，type=all，意思就是这个索引的区分度很低，建立的意义不大，这时他的执行计划 type=all，mysql 认为走这个索引还不如全表扫描：

到底 Cardinality处于什么水平时，性能最好？一般认为这个值越接近count(*)，性能最好。而对于像性别这种字段，就没必要加索引了。

用 exists 和 in  生成的执行计划大都一样

二十六、 MyBatis 是如何防止 SQL 注入的

MyBatis 是如何做到SQL预编译的呢？其实在框架底层，是 JDBC中的 PreparedStatement类在起作用，PreparedStatement是我们很熟悉的 Statement的子类，它的对象包含了编译好的 SQL语句。这种“准备好”的方式不仅能提高安全性，而且在多次执行同一个SQL时，能够提高效率。原因是 SQL已编译好，再次执行时无需再编译。

二十七、索引都有什么类型

Mysql 目前主要有以下几种索引类型：BTREE，FULLTEXT，HASH，RTREE。

【1】BTREE：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中(二叉树)，每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

【2】FULLTEXT：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和 MyISAM一起诞生的，它的出现是为了解决 WHERE name LIKE “%word%" 这类针对文本的模糊查询效率较低的问题。

【3】HASH：由于 HASH的唯一(几乎100%的唯一)及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

【4】RTREE：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找

二十八、建了索引，什么时候会不命中索引

【1】如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引；

【2】对于多列索引，不是使用的第一部分(第一个)，即不遵守最左前缀原则。则不会使用索引；

【3】like查询是以%开头；

【4】如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引；

【5】如果 Mysql估计使用全表扫描要比使用索引快，则不使用索引；

二十九、主从复制的延迟

MySQL 数据库从库同步的延迟问题：复制延迟是一个很普遍的问题，最好在设计应用程序时能够让其容忍备库出现延迟。如果系统在备库出现延迟时就无法很好地工作，那么应用程序也许就不应该用到复制。但也有办法可以让备库跟上主库。

MySQL 单线程复制的设计导致备库通常只会有效地使用一个 CPU和磁盘，效率相当底下。而事实上，备库通常都会和主库使用相同配置的机器。同时备库上的锁也是问题，运行的查询可能会阻塞复制线程。因为复制是单线程的，复制线程在等待时将会无法做别的事。

复制一般有两种产生延迟的方式：突然产生延迟然后再跟上，或者稳定的延迟增长。前一种通常是由于一条运行很长时间的查询导致，而后者即使在没有长时间运行的查询时也会出现。当备库无法跟上时，可以记录备库上的查询并使用一个日志分析工具找出哪里慢了。最好的分析办法是暂时在备库上打开慢查询日志记录，然后使用 pt-query-digest 工具来分析。如果打开了 log_slow_slave_statements 选项，在标准的 MySQL 慢查询日志能够记录 MySQL5.1 及更新的版本中复制线程执行的语句，这样就可以找到在复制时那些语句执行慢了。

【解决方案一】：除了购买更快的磁盘和CPU(固态磁盘能够提供极大的帮组)备库没有太多的调优空间。大部分选项都是禁止某些额外的工作以减少备库的负载。一个简单的办法时配置 InnoDB，使其不要频繁地刷新磁盘，这样事务会提交得更快些。如下：

--默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘，这是很费时的。--特别是使用电池供电缓存(Battery backed up cache)时。--设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。--日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。--设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。--而值2只会在整个操作系统挂了时才可能丢数据。innodb_flush_log_at_try_commit=2

还可以在备库上禁止二进制日志记录，如下：但这些设置都是牺牲安全获取速度。如果需要将备库提升为主库，记得将这些设置还原成安全的值。

innodb_locks_unsafe_for_binlog=1

【解决方案二】：不要重复写操作中代价较高的部分：重构应用程序或者优化查询通常是最好的保持备库同步的方法。任何主库上昂贵的写操作都会在每一个备库上重放。如果可以把工作转移到备库，那么就只有一个备库需要执行，然后我们可以把写的结果回传到主库，例如，通过执行 LOAD DATA INFILE。举个栗子：

--replace into 主要作用类似insert插入操作。--主要的区别是replace会根据主键或者唯一索引检查数据是否存在，如果存在就先删除在更新。REPLACE INTO table_min(col1,col2)SELECT col1,SUM(col2)FROM table_maxGROUP BY col1;

如上在主库上执行查询，每个备库将同样需要执行庞大的 GROUP BY 查询。当进行太多这样操作时，备库将被拉开差距。如果将查询转移到一个备库上也许会有帮组。在备库上创建一个特别保留的数据库，用于避免和从主库上复制的数据产生冲突。可以执行如下操作：

REPLACE INTO back.People(col1,col2)SELECT col1,SUM(col2)FROM main.PeopleGROUP BY col1;

现在可以执行 SELECT INTO OUTFILE，然后执行 LOAD DATA INFILE 将结果集加载到主库中。如果有 N个备库，就节约了 N-1 次庞大的 GROUP BY 操作。该策略的问题是备库中的数据和写入主库的数据很难保持一致。

SELECT * INTO OUTFILE "/data/mysql/e.sql" FROM e;--load DATA 需要有处理文件的权限， GRANT FILE ON *.* TO USER@host;--因为我们前面指定的分隔符是 ','，LOAD DATA 时也要指定分隔符，否则也会报错：LOAD DATA INFILE "/data/mysql/e.sql" INTO TABLE e FIELDS TERMINATED BY ',';

我们还可以通过分离 REPLACE 和 SELECT 部分，把结果返回给应用程序，然后将其插入到主库中。这种方法再次避免了在备库上执行 GROUP BY 部分。将SELECT 与 REPLACE 分离后意味着查询的 SELECT 操作不会在每一个备库上重放。节约了备库上昂贵的写入操作部分。

--先获取需要插入的数据集SELECT col1,SUM(col2) FROM main.table_max GROUP BY col1;--在插入数据REPLACE INTO  main.table_min(col1,col2) VALUES(?,?);

【解决方案三】：在复制之外并行写入：另一种避免备库严重延迟的办法是绕过复制。自己复制数据到另外一台服务器，而不是通过复制。特别是复核的瓶颈通常集中在一些小部分表上。如果能在复制之外单独处理这些，就能够显著地加快复制。

【解决方案四】：并行复制：MySQL 5.7才可称为真正的并行复制，这其中最为主要的原因就是 slave服务器的回放与 master是一致的，即 master服务器上是怎么并行执行的，那么 slave上就怎样进行并行回放。不再有库的并行复制限制，对于二进制日志格式也无特殊的要求(基于库的并行复制也没有要求)。

三十、查询优化

后期会专门出一篇查询优化的文章，这里记录下文章目录：

【1】是否请求了不需要的数据

【2】是否扫描了额外的记录

【3】一个复杂查询 OR 多个简单查询

【4】切分查询

【5】分解关联查询

【6】UNION 的限制

【7】优化 COUNT() 查询

【8】优化 LIMIT 分页

【9】排序优化

【10】查询状态

三十一、数据库查询语句如何执行

先看下 MySQL 的基本架构图：大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

【Server 层】：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数(如日期、时间、数学和加密函数等)，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

【存储引擎层】：负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL5.5.5 版本开始成为了默认存储引擎。

现在我们来详细看一下查询sql的执行流程：【1】连接器：第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

【2】查询缓存：连接建立完成后，你就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存。MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

查询缓存在mysql中需要手动开启。通过set session query_cache_type=on进行开启。同理，如需关闭，只需要将该参数设置为off即可。还需设置数据库分配给缓存空间的大小，默认值为0。即缓存不生效。通过 set  @@global.query_cache_size=具体值进行设置。如需永久生效，则修改my.cnf文件，添加query_cache_size=具体值。查询缓存虽然能够提升查询效率，但是也会造成一定的系统资源损耗。首先，mysql在查询的时候，首先会在查询缓存中判断当前是否存在相同的结果。其次，在进行查询的时候，如果缓存中没有相同的查询结果，会将结果先放入缓存。最后，但是查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

【3】分析器：如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

【4】优化器：经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联(join)的时候，决定各个表的连接顺序。

【5】执行器：MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

end