mysql面试题

mysql面试题

1. mysql执行顺序

   from表
   on过滤
   添加外部表
   where过滤
   group by
   having 过滤
   select字段
   distinct子句
   order by 排序
   

2. sql执行过程

   一：把sql交给数据库服务器
   二：服务器解析sql
     1：检查是否有执行计划，有则调用执行计划
     2：检查返回数据是否已经存在数据缓存储存区中，有则从缓存中取数据
     3：进行语法校验
     4：进行语义检验，例如表名是否存在
     5：获得对象的解析锁，为了保证数据的一致性，在读取数据的时候会给这个对象加锁，如果不加锁，当有数据写入时，这个时候数据已经读入，但是数据可能因为事物失败回滚，造成脏读的现象
     6：校验用户权限
     7：对sql优化
     8：把执行计划保存到sql计划缓存中
   三：执行sql
   

3. explain

   在sql语句加成explain关键字运行会把sql的执行情况打印出来。
   介绍几个常用信息
   id：执行顺序，从大到小执行，相同是从上往下，子查询序号增加
   select_type:每个select的查询类型，simple（简单sql，没有union或者子查询），primary（子查询最外层查询），union（union中的第二个或者后面的select）
   table：数据库表名称
   type：访问方式，表示在表中找到所需行的方式。all（遍历便利），index（遍历索引数），range（只检索给定范围的行，使用一个索引来选择行），ref(表示上述表的链接匹配条件，即那些列或常量被用于查找索引列上的值)
   possible_keys:列出使用那些索引能找到记录
   key：实际使用的索引
   key_len:索引使用的字节数
   

4. mysql常用引擎

   一.innodb：
   1）经常更新的表，适合处理多重并发的更新请求。
   2）支持事务。
   3）可以从灾难中恢复（通过bin-log日志等）。
   4）外键约束。只有他支持外键。
   5）支持自动增加列属性auto_increment。
   二.myisam：
   定义：
   MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。
   MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。
   适用场景：
   1）不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。
   2）不支持外键的表设计。
   3）查询速度很快，如果数据库insert和update的操作比较多的话比较适用。
   4）整天 对表进行加锁的场景。
   5）MyISAM极度强调快速读取操作。
   6）MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。
   缺点：
   就是不能在表损坏后恢复数据。（是不能主动恢复）
   三.Memory（也叫HEAP）堆内存：
   定义：
   使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。
   但是一旦服务关闭，表中的数据就会丢失掉。 HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。
   适用场景：
   1）那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果。
   2）目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。
   3）数据是临时的，而且必须立即可用得到，那么就可以放在内存中。
   4）存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。
   注意： Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多。
   特性要求：
   1）要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用（长度不固定的）。
   2）要记住，在用完表格之后就删除表格。
   

5. SQL标准的四种隔离级别

    一：READ UNCOMMITED(未提交读)
   　　在RERAD UNCOMMITED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。**事务可以读取未提交的数据，这也成为脏读（Dirty Read）**。这个级别会导致很多问题，从性能上说READ UNCOMMITED 不会比其他的级别好太多，但缺乏其他级别的好多好处，除非有非常必要的理由，在实际的应用中一般很少使用READ UNCOMMITED.
   二：READ COMMITED (提交读)
   　 　大多数数据库系统的默认隔离级别都是READ COMMITED （但是MYSQL不是）。READ COMMITED 满足前面提到的隔离性的简单定义：一个事务开始时，只能看到已经提交的事务所做的修改。换句话说，一个事务从开始到提交之前，所做的任何修改对其他事务都 是不可见的。这个级别有时候也叫做不可重复的（nonerepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。
   三：REPEATABLE READ (可重复读)
   　 　REPEATABLE READ (可重复读) 解决了脏读问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是，理论上，**可重复读隔离级别还是无法解决另一个幻读 （PhantomRead）的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读 取该范围的记录时，会产生幻读**（Phantom Row）。**InnoDB和XtraDB 存储引擎通过多版并发控制（MVCC ,Multivesion Concurrency Control ）解决了幻读问题**。
   四：可重复读(REPEATABLE READ)是Mysql 默认的事务隔离级别，其中InnoDB主要通过使用MVVC获得高并发，使用一种被称为next-key-locking的策略来避免幻读。
   五：SERIALIZABLE(可串行化)
   　 　SERIALIZABLE是最高的隔离级别。它通过强制事务串行，避免了前面说的幻读问题。简单的来说，**SERIALIZABLE会在读的每一行数据上 都加上锁，所以可能导致大量的超时和锁征用问题**。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况，才可考 虑用该级别。
   

6. bin-log日志

   binlog,即二进制日志,它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中；
   它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制（主从数据库的复制）
   

7. mysql索引失效

   1、最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。
   前提条件：表中已添加复合索引（username,password,age）
   2、不在索引列上做任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描
   3、存储引擎不能使用索引中范围条件右边的列，范围之后索引失效。（< ,> between and）
   4、mysql使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效
   5、mysql中使用is not null 或者 is null会导致无法使用索引
   6、mysql中like查询是以%开头，索引会失效变成全表扫描，覆盖索引。
   7、mysql中，字符串不加单引号索引会失效。正确写法：select * from t_user where username = 'lujin';
   8、mysql中，如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
   9、如果mysql使用全表扫描要比使用索引快,则不会使用到索引