Mysql面试题：

数据库的三大范式：

第一范式：1NF是对属性的原子性约束，要求字段具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

第二范式：2NF是在满足第一范式的前提下，非主键字段不能出现部分依赖主键；解决：消除复合主键就可避免出现部分以来，可增加单列关键字。

第三范式：3NF是在满足第二范式的前提下，非主键字段不能出现传递依赖，比如某个字段a依赖于主键，而一些字段依赖字段a，这就是传递依赖。解决：将一个实体信息的数据放在一个表内实现。

Mysql中的MyISAM与InnoDB的区别（数据引擎）Memory：

（1）InnoDB存储引擎支持事务，而MyISAM不支持事务；

（2）InnoDB支持行级锁，而MyISAM只支持表级锁；

（InnoDB行锁是通过给索引加锁实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表级锁！行级锁在每次获取锁和释放锁的操作需要比表级锁消耗更多的资源。

MySQL表级锁有两种模式：表共享读锁和表独占写锁。就是说对MyIASM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，会阻塞其他用户对同一表的读和写操作。）

（3）InnoDB支持外键，而MyISAM不支持外键；

（4）InnoDB不保存数据库表中表的具体行数，而MyISAM会保存；

（也就是说，执行 select count(*) from table，

InnoDB要扫描一遍整个表来计算有多少行，而MyISAM只需要读出保存好的行数即可（内部维护了一个计算器，可以直接调取）。【注】：当count(*)语句包含where条件时，两种表的操作是一样的。也就是上述介绍到的InnoDB使用表锁的一种情况。）

对于select ,update ,insert ,delete 操作：

　　如果执行大量的SELECT，MyISAM是更好的选择（因为MyISAM不支持事务，使得MySQL可以提供高速存储和检索，以及全文搜索能力）；

　　如果执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表（因为InnoDB支持事务，在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了）。

什么是事务：

事务就是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

数据库事务的四大特征：

原子性、一致性、隔离性、持久性  （ACID）

原子性：

是指整个数据库事务是不可分割的单位。只有使事务中的所有数据库操作都成功，才算整个事务成功。如果事务中任何一个sql语句执行失败，那么已经执行的sql语句也必须撤销，事务状态退回到执行事务之前的状态。

一致性：

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态。在事务开始之前和事务结束之后，事务的完整性约束没有被破坏。

隔离性：
一个事务的影响在该事务提交前对其他事物都不可见。——这通过锁来实现

持久性：

事务一旦提交，其结果就是永久性的。

Mysql提供的四种隔离级别：

read uncommitted（读未提交）

read committed（读已提交）

repeatable read（可重复读）：InnoDB的默认隔离级别

serializable（串行化）

 

脏读：

一个事务读取了另一个事务未提交的数据，那这个读取就是脏读。

解决方法 ：把数据库的事务隔离级别调整到read commited。

 

不可重复读：

不可重复读是指在一个事务内多次读同一数据，在这个事务还没有结束时，另外一个事务也访问并修改该同一数据，那么在第一个事务的两次读数据之间，由于第二个事务的修改，第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读。

如何避免：

InnoDB存储引擎中，通过使用Next-Key Lock算法来避免不可重复读的问题。在Next-Key Lock算法下，对于索引的扫描，不仅仅是锁住扫描到的索引，而且还能锁住这些索引覆盖的范围。因此对于这个范围内的插入都是不允许的。InnoDB存储引擎的默认事务隔离级别是READ REPEATABLE，采用Next-Key Lock算法，就避免了不可重复读的现象。

解决办法：

把数据库的事务隔离级别调整到 REPEATABLE READ ， 读取时候不允许其他事务修改该数据，不管数据在事务过程中读取多少次，数据都是一致的。

幻读：

是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

如何避免：Repeatable read及以上级别通过间隙锁来防止幻读的出现，即锁定特定数据的前后间隙让数据无法被插入。

 

InnoDB如何保证事务的四大特性：

MySQL的存储引擎InnoDB使用重做日志(redo log)保证一致性与持久性，

回滚日志(undo log)保证原子性，使用各种锁来保证隔离性。

Mysql的日志文件：

重做日志（redo log）

回滚日志（undo log）

二进制日志（binlog）

错误日志（errorlog）

慢查询日志（slow query log）

一般查询日志（general log）

中继日志（relay log）
其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系。

重做日志（redo log）：

redo log在事务没有提交前，会记录每一个修改操作变更后的数据。主要是防止在发生故障的时间点，尚有脏页未写入磁盘。在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。（作用）

　　在事务提交前，只要将 Redo Log 持久化即可，不需要将数据持久化。当系统崩溃时，系统可以根据redo Log的内容，将所有数据恢复到最新的状态。（持久化：先将重做日志写入缓存，再刷新(fsync)到磁盘）

　　重做日志是物理日志，记录的是对于每个页的修改。事务开始后Innodb存储引擎先将重做日志写入缓存（innodb_log_buffer）中。然后会通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘。

1. Master Thread每秒一次执行刷新Innodb_log_buffer到重做日志文件。
2. 每个事务提交时会将重做日志刷新到重做日志文件。
3. 当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件

　　当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化。

1、内容：

  物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

2、redo log是什么时候写盘的？

　　是在事务开始之后逐步写盘的。

　　事务开始之后就产生redo log，redo log的写盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。（先将重做日志写入缓存，将日志缓冲区的日志刷新到磁盘，写入磁盘的方式有上面3种）

【注】即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。这一点是必须要知道的，因为这可以很好地解释再大的事务的提交（commit）的时间也是很短暂的。

3、什么时候释放：

  当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

回滚日志（undo log）：

　　保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。（作用）

　　事务发生异常需要回滚，这时就需要回滚日志。回滚日志不同于重做日志，它是逻辑日志，对数据库的修改都逻辑的取消了。当事务回滚时，它实际上做的是与先前相反的工作。对于每个INSERT，InnoDB存储引擎都会完成一个DELETE；对于每个UPDATE，InnoDB存储引擎都会执行一个相反的UPDATE。

　　未提交的事务和回滚了的事务也会产生重做日志。InnoDB存储引擎会重做所有事务包括未提交的事务和回滚了的事务，然后通过回滚日志回滚那些未提交的事务。使用这种策略需要回滚日志在重做日志之前写入磁盘，使得持久化变得复杂起来。为了降低复杂度，InnoDB存储引擎将回滚日志作数据，记录回滚日志的操作也会记录到重做日志中。这样回滚日志就可以像数据一样缓存起来，而不用在重写日志之前写入磁盘了。

1、内容：

  逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

2、什么时候产生？

  事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

3、什么时候释放？

当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

二进制日志（bin log）：

1、作用：

  用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 用于数据库的基于时间点的还原。

2、内容：

逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。

在使用mysqlbinlog解析binlog之后一些都会真相大白。
因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

3、什么时候产生：

  事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。

  因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

4、什么时候释放：

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

binlog与redolog的区别？

在MySQL数据库中还有一种二进制日志，其用来基于时间点的还原及主从复制。从表面上来看其和重做日志非常相似，都是记录了对于数据库操作的日志。但是，从本质上来看有着非常大的不同。 首先重做日志是在InnoDB存储引擎层产生的，而二进制日志是在MySQL数据库的上层产生的。其次，两种日志记录的内容形式不同。二进制日志是一种逻辑日志，其记录的是对应的SQL语句。而重做日志是物理日志，记录的是每个页的修改。此外，两种日志记录写入磁盘的时间点不同，二进制日志只在事务提交完成后进行一次写入，重做日志在事务进行时不断地写入。

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

Undo 记录某 数据 被修改 前 的值，可以用来在事务失败时进行 rollback；
Redo 记录某 数据块 被修改 后 的值，可以用来恢复未写入 data file 的已成功事务更新的数据。
即，

Redo Log 保证事务的持久性

Undo Log 保证事务的原子性（在 InnoDB 引擎中，还用 Undo Log 来实现 MVCC）（这个MVCC可以自己了解一下）

比如某一时刻数据库down 机了，有两个事务，一个事务已经提交，另一个事务正在处理。数据库重启的时候就要根据日志进行前滚及回滚，把已提交事务的更改写到数据文件，未提交事务的更改恢复到事务开始前的状态。即通过 redo log 将所有已经在存储引擎内部提交的事务应用 redo log 恢复，所有已经 prepared 但是没有 commit 的事务将会应用 undo log 做回滚。

数据库的乐观锁和悲观锁：

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽掉一切可能违反数据完整性的操作，在读取的时候就对数据进行加锁， 在该用户读取数据的期间，其他任何用户都不能来修改该数据，但是其他用户是可以读取该数据的， 只有当自己读取完毕才释放锁。

乐观锁：假定不会发生并发冲突，只在提交的时候检查是否发生并发冲突。

事务和锁的存在都是为了更好地解决并发访问造成的数据不一致性问题。乐观锁和悲观锁都是为了解决并发控制问题，乐观锁可以看做一种在最后提交时检测冲突的手段，而悲观锁是一种避免冲突的手段。

（1）乐观锁：假设不会发生并发冲突，只在提交的时候检查是否发生并发冲突。可以使用版本号机制和CAS算法实现。

版本号机制：一般在数据表中加一个数据版本号version字段，表示数据被修改的次数，当数据被修改时version值加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若当前读取到的version值与第一次读取到的数据库version值相等时才更新，否则重试更新操作，直到更新成功。

　　例子：

假设数据库中帐户信息表中有一个 version 字段，当前值为 1 ；而当前帐户余额字段（ balance ）为 100 。

操作员A此时将其读出（ version=1 ），并从其帐户余额中扣除 50（100-50 =50）；

在操作员A操作的过程中，操作员B也读入此用户信息（ version=1 ），并从其帐户余额中扣除20 （100-20=80 ）。

操作员A完成了修改工作，将数据版本号加一（ version=2 ），连同帐户扣除后余额（ balance=50 ），提交至数据库更新，此时由于提交数据版本大于数据库记录当前版本，数据被更新，数据库记录 version 更新为 2 ；

操作员B完成了操作，也将版本号加一（ version=2 ）试图向数据库提交数据（ balance=80 ），但此时比对数据库记录版本时发现，操作员 B 提交的数据版本号为 2 ，数据库记录当前版本也为 2 ，不满足 “ 当前最后更新的version与操作员第一次的版本号相等 “ 的乐观锁策略，因此，操作员B的提交被驳回。

CAS机制：即compare and swap（比较与交换），无锁编程，在不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，因此也叫非阻塞同步。

CAS过程是这样：它包含3个参数：内存值V（要更新变量的值），旧的预期值A，要修改的值B。当且仅当预期值A的值等于内存值V时，才会将内存值V修改为B，否则不会执行任何操作（V值和A值不同，则说明已经有其他线程做了更新）。一般情况下是一个自旋操作，即不断的重试。

　　例子：

假设 t1，t2 线程同时更新同一变量56的值。

因为t1和t2线程都同时去访问同一变量56，所以他们会把主内存的值完全拷贝一份到自己的工作内存空间，所以t1和t2线程的预期值都为56。

假设t1在与t2线程竞争中线程t1能去更新变量的值，而其他线程都失败。（失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次发起尝试）。t1线程去更新变量值改为57，然后写到内存中。此时对于t2来说，内存值变为了57，与预期值56不一致，就操作失败了（想改的值不再是原来的值）。

乐观锁的优势和劣势 ：
优势：如果数据库记录始终处于悲观锁加锁状态，可以想见，如果面对几百上千个并发，那么要不断的加锁减锁，而且用户等待的时间会非常的长， 乐观锁机制避免了长事务中的数据库加锁解锁开销，大大提升了大并发量下的系统整体性能表现。所以如果系统的并发非常大的话，悲观锁定会带来非常大的性能问题，所以建议就要选择乐观锁定的方法， 而如果并发量不大，完全可以使用悲观锁定的方法。乐观锁也适合于读比较多的场景。
劣势：乐观锁只能在提交数据时才发现业务事务将要失败，如果系统的冲突非常的多，而且一旦冲突就要因为重新计算提交而造成较大的代价的话，乐观锁也会带来很大的问题。而且乐观锁也无法解决脏读的问题 。

（2）悲观锁：假定会发生并发冲突，在读取的时候就对数据进行加锁， 在该用户读取数据的期间，其他任何用户都不能来修改该数据，但是其他用户是可以读取该数据的， 只有当自己读取完毕才释放锁。

在数据库中可以使用Repeatable Read的隔离级别（可重复读）来实现悲观锁，它完全满足悲观锁的要求（加锁）。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

悲观锁的优势和劣势 ：
优势：能避免冲突的发生 。
劣势：开销较大，而且加锁时间较长，对于并发的访问性支持不好。

两种锁的使用场景：

如果冲突很少，或者冲突的后果不会很严重，那么通常情况下应该选择乐观锁，因为它能得到更好的并发性；

如果冲突太多或者冲突的结果对于用户来说痛苦的，那么就需要使用悲观策略，它能避免冲突的发生。

一般乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候；悲观锁适用于多写的情况，多写的情况一般会经常产生冲突。

谈谈mongodb,mysql的区别和具体应用场景：

MongoDB是非关系型数据库，是一个基于分布式文件存储的数据库。（文档型数据库：可以存放xml、json、bson类型的数据。）同时MongoDB是由C++语言编写。旨在为WEB应用提供可扩展的高性能数据存储解决方案。 是非关系数据库当中功能最丰富，最像关系数据库的。

MongoDB 将数据存储为一个文档，数据结构由键值(key=>value)对组成。MongoDB 文档类似于 JSON 对象。字段值可以包含其他文档，数组及文档数组。

它可以存储比较复杂的数据类型。Mongo最大的特点是它支持的查询语言非常强大，其语法有点类似于面向对象的查询语言，几乎可以实现类似关系数据库单表查询的绝大部分功能，而且还支持对数据建立索引。

mongodb与mysql不同，mysql的每一次更新操作都会直接写入硬盘，但是mongo不会，作为内存型数据库，数据操作会先写入内存，然后再会持久化到硬盘中去 ，但MongoDB采用的预分配空间的方式来防止文件碎片，所以MongoDB的数据文件很大。

MongoDB的特点是：

（1）面向文档（2）高性能（3）高可用（4）易扩展（5）丰富的查询语言

MongoDB 缺点：

• MongoDB 不支持事务操作(最主要的缺点)
• MongoDB 占用空间过大
  ③ MongoDB 没有如 MySQL 那样成熟的维护工具，这对于开发和IT运营都是个值得注意的地方

存储方式：虚拟内存+持久化。

　　持久化方式：MongoDB 的所有数据实际上是存放在硬盘的，所有要操作的数据通过 mmap 的方式映射到内存某个区域内。然后，MongoDB 就在这块区域里面进行数据修改，避免了零碎的硬盘操作。

mongodb,mysql的区别？

（1）MongoDB 非关系型数据库，MySql是关系型数据库

（2）MongoDB存储方式：虚拟内存+持久化； MySql在不同的引擎上有不同 的存储方式。

（3）MongoDB查询语句：是独特的Mongodb的查询方式； MySql查询语句是使用传统的sql语句，拥有较为成熟的体系，成熟度很高。

（4）mysql的每一次更新操作都会直接写入硬盘，但是mongo的数据操作会先写入内存，然后再会持久化到硬盘中去 。（MongoDB数据是存储在硬盘上的，只不过需要经常读取的数据会被加载到内存中，将数据存储在物理内存中，从而达到高速读写。）

（5）mysql缺点就是在海量数据处理的时候效率会显著变慢。在适量级的内存的Mongodb的性能是非常迅速的。

（6）MongoDB 不支持事务操作，mysql的innodb和bdb存储引擎支持事务。（注：myisam不支持事务）

什么是索引:

索引是一种数据结构,可以帮助我们快速的进行数据的查找.

索引是个什么样的数据结构:

索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引.

mysql 的索引有哪几种：

一、按表列属性分类

1.单列索引

以表的单个列字段创建的索引

2.联合索引

以表的多个列字段组合创建的索引，在查询条件使用索引的从左字段顺序才会生效，遵循最左匹配原则。

单列索引和联合索引又包括：

普通索引

非主键，非唯一列的索引

主键索引

基于该表主键自动生成成的索引，如果未给表定义主键，会查找该表中是否存在非空、整形、唯一索引作为其主键(可通过select _rowid from 表名查看)，若都不满足会隐式生成一个rowid作为主键(无法直接查到)

唯一索引

基于表的唯一列生成的索引，允许为空值

全文索引

将存储于数据库中的整本书或整篇文章中任意内容信息查找出来，如大量级的文字中如like %关键字%，普通索引的效率与全文索引相比是非常低的。

二、按数据结构分类

1.B+tree索引

b+tree基于平衡二叉树的一种多路平衡查找树，所有记录都按照顺序存放在叶子节点中，各个叶子节点直接通过链表相连。与b树不同的是：

非叶子节点只存储键值信息。

所有叶子节点之间都有一个链指针。

数据记录都存放在叶子节点中。

2.hash索引

基于hash表结构实现的索引，mysql中只有MEMORY/HEAP和NDB存储引擎支持;

对比：

由于hash索引是比较其hash值，hash索引只能进行等值查找而不能进行范围查找

hash索引无法进行排序：原因同上

不支持最左匹配原则，复合索引时合并一起计算hash值

hash索引的检索效率很高可以一次定位，但是当发生大量hash碰撞的时候，链表变长，hash索引效率上是不如b+tree的

由于存在hash碰撞的问题，当需要获得总数时候，hash 索引在任何时候都不能避免表扫描。

三、按存储结构分类

1.聚簇索引(聚集索引)

InnoDB的聚簇索引实际上是在同一个BTree结构中同时存储了索引和整行数据，通过该索引查询可以直接获取查询数据行。

聚簇索引不是一种单独的索引类型，而是一种数据的存储方式，聚簇索引的顺序，就是数据在硬盘上的物理顺序。

在mysql通常聚簇索引是主键的同义词，每张表只包含一个聚簇索引(其他数据库不一定)。

2.辅助索引(非聚集索引，次级索引，二级索引)

非聚集索引在BTree的叶子节点中保存了索引列和主键。如果查询列不在该索引内，只能查到其主键值，还需要回表操作查询聚簇索引进行查询。

聚簇索引的优点：

可以把相关数据保存在一起，如：实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少量的数据页就能获取某个用户全部邮件，如果没有使用聚集索引，则每封邮件都可能导致一次磁盘IO。

数据访问更快，聚集索引将索引和数据保存在同一个btree中，因此从聚集索引中获取数据通常比在非聚集索引中查找要快。

使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点：

聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没有那么重要了，聚集索引也没有什么优势了

插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到innodb表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表。

更新聚集索引列的代价很高，因为会强制innodb将每个被更新的行移动到新的位置。

基于聚集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题，当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间。

聚集索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

二级索引可能比想象的更大，因为在二级索引的叶子节点包含了引用行的主键列。

二级索引访问需要两次索引查找，而不是一次。

什么情况下该使用索引:

在经常需要搜索的列上，可以加快搜索的速度；

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

什么情况下不该不使用索引:

对于那些在查询中很少使用或者参考的列不应该创建索引。因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

对于那些只有很少数据值的列也不应该增加索引。例如性别，只有两种可能数据。意味着索引的二叉树级别少，这样的二叉树查询无异于全表扫描。

频繁更新的字段不要使用索引。

什么情况下索引会失效：

1、如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

要想使用or，又想让索引生效，只能将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;

2、like查询是以%开头

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

4、如果mysql估计使用全表扫描要比使用索引快,则不使用索引

5、对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

错误的例子：select*from test where id-1=9;

正确的例子：select* fromtest where id=10;

6、使用 <> 、not in、not exist、!=

比如：select id from t where num in(1,2,3);

7、索引字段进行判空查询时。也就是对索引字段判断是否为NULL时。语句为is null 或is not null。

select id from twhere num is null;

#可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0;  

Explain关键字：(属于sql优化方面，工作里很管用)

Explain关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈。

explain的用途

1. 表的读取顺序如何

2. 数据读取操作有哪些操作类型

3. 哪些索引可以使用

4. 哪些索引被实际使用

5. 表之间是如何引用

6. 每张表有多少行被优化器查询

explain的执行效果

explain包含的字段（标红的）

1. id ：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

①id相同

执行顺序从上至下

例子：

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

读取顺序：subject > teacher > student_score

②id不同

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

例子：

explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

读取顺序：teacher > subject > student_score

③id相同又不同

id如果相同，可以认为是一组，从上往下顺序执行

在所有组中，id值越大，优先级越高，越先执行

例子：

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id

 -> union 

 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

 读取顺序：2.teacher > 2.subject > 1.subject > 1.teacher

 

===========================一道乏力的分割线=======================

1. select_type: 查询类型

①SIMPLE

简单查询，不包含子查询或Union查询

例子：

explain select subject.* from  where subject.id = student_id and subject.teacher_id = teacher.id;

 

②PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为主查询

例子：

explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

 

③SUBQUERY

在select或where中包含子查询

例子：

explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

 

*④DERIVED

在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL

会递归执行这些子查询，把结果放在临时表中

备注：

MySQL5.7+ 进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率

⑤UNION

若第二个select出现在uion之后，则被标记为UNION

例子：

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id

 -> union 

 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

 

⑥UNION RESULT

从UNION表获取结果的select

例子：

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id

 -> union 

 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

 

==========================一道乏力的分割线=======================

1. table: 正在访问哪个表

4. partitions:匹配的分区

5. type: 访问的类型

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL //最好到最差

备注：掌握以下10种常见的即可

NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

①NULL

MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引

例子：

explain select min(id) from subject;

 

②system

表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略

③const

表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量

例子：

explain select * from teacher where teacher_no = 'T2010001';

 

④eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

例子：

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id;

 

⑤ref

非唯一性索引扫描，返回匹配某个单独值的所有行

本质上也是一种索引访问，返回所有匹配某个单独值的行

然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体

例子：

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

 

⑥ref_or_null

类似ref，但是可以搜索值为NULL的行

例子：

explain select * from teacher where name = 'wangsi' or name is null;

 

⑦index_merge

表示使用了索引合并的优化方法

例子：

explain select * from teacher where id = 1 or teacher_no = 'T2010001' .

 

⑧range

只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引

一般就是在你的where语句中出现between、<>、in等的查询。

例子：

explain select * from subject where id between 1 and 3;

 

⑨index

Full index Scan，Index与All区别：index只遍历索引树，通常比All快

因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。

例子：

explain select id from subject;

 

⑩ALL

Full Table Scan，将遍历全表以找到匹配行

例子：

explain select * from subject;

 

1. possible_keys: 显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到

1. key: 实际使用到的索引，如果为NULL，则没有使用索引

1. key_len: 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

1. ref: 显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

1. rows: 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

1. filtered: 查询的表行占表的百分比

1. Extra: 包含不适合在其它列中显示但十分重要的额外信息

①Using filesort

说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取

MySQL中无法利用索引完成的排序操作称为“文件排序”

例子：

explain select * from subject order by name;

 

②Using temporary

使用了临时表保存中间结果，MySQL在对结果排序时使用临时表，常见于排序order by 和分组查询group by

例子：

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id

 -> union 

 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

 

③Using index

表示相应的select操作中使用了覆盖索引（Covering Index）,避免访问了表的数据行，效率不错！

如果同时出现using where，表明索引被用来执行索引键值的查找

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作

例子：

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

备注：

覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，即查询列要被所建的索引覆盖

 

④Using where

使用了where条件

例子：

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

 

⑤Using join buffer

使用了连接缓存

例子：

explain select student.*,teacher.*,subject.* from student,teacher,subject;

 

⑥impossible where

where子句的值总是false，不能用来获取任何元组

例子：

explain select * from teacher where name = 'wangsi' and name = 'lisi';

 

⑦distinct

一旦mysql找到了与行相联合匹配的行，就不再搜索了

例子：

explain select distinct teacher.name from teacher left join subject on teacher.id = subject.teacher_id;

 

⑧Select tables optimized away

SELECT操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了）

例子：

explain select min(id) from subject;

 

==========