mysql知识总结

知识列表

1. mysql的隔离级别有哪几种
2. 什么是脏读、不可重复读、幻读
3. MVCC的实现原理
4. 如何保证没有幻读发生的
5. 聚簇索引和非聚簇索引的区别
6. MyISAM和InnoDB引擎的区别及各自的优缺点
7. 常见的sql优化都有哪些点可以参考
8. 如何进行大表的分页查询优化
9. 一条查询sql的执行过程

• mysql的隔离级别有哪几种?分别有什么优缺点

  mysql共有四种事务隔离级别，分别是未提交读(READ UNCOMMITTED)、已提交读(READ COMMITTED)、可重复读(REPEATABLE READ)、可串行化(SERIALIZABLE),mysql的默认事务隔离级别是可重复读。
  
  未提交读:
  	一个事务中做的内容修改,即使没有进行事务提交,对其他事务也是可见的。这个级别会造成脏读，不可重复读,幻读。
  已提交读:
  	一个事务开始时，只能"看到"在事务开始之前的已经提交的事务所做的修改。也就是说，在这个隔离级别下，一个事务从开始到结束之间所做的修改，对其他事务是不可见的。这个级别会造成不可重复读，幻读。
  可重复读:
  	可重复读，在已提交读的前提下，保证了同一个事务多次读取同样的记录结果是一致的。
  可串行化:
  	可串行化是最高的隔离级别,它通过强制事务串行执行，避免了脏读，不可重复读，幻读的发生。实现原理就是在读取的每一行数据上进行加锁，这也就意味着会导致大量的超时和锁竞争问题。
  	
  四种隔离级别的安全性是从低 --> 高的，相反的是并发性从 高 --> 低。
  

• 什么是脏读、不可重复读、幻读

  脏读:
  	一个事务能够"看到"到其他未提交事务的所做的修改，即称为脏读。
  不可重复读:
  	一个事务读取了某条记录后，另一个事务对该记录进行了修改，则当第一个事务再次读取该条记录，将会发现两次读取的结果不一致，即称为不可重复读。
  幻读:
  	所谓幻读，是指一个事务在读取某个范围的数据时，另外一个事务又在该范围之间插入了新的记录(或删除了几条记录)，这将导致之前的事务再次读取该范围的记录时，会多出一些记录(或少了一些记录)。即称为幻读。
  

• MVCC的实现原理

  MVCC，即多版本并发控制,是行级锁的一个变种。
  简单来说,MVCC的实现是通过保存数据在某个时间点的快照来实现的，也就是不管一个事务执行多长的时间，每个事务看到的数据是一致的。根据每个事务开始的时间不同，每个事务对同一张表，同一个时刻看到的数据是不一样的。
  
  InnoDB的MVCC实现，是通过在每个行记录增加两个隐藏的列来实现的，这两个列，一个保存了该行的创建时间，一个保存了该行的过期时间(即删除时间)，存储的值是事务Id而不是时间，事务Id在InnoDB里是全局自增的，每当开始一个新的事务，事务Id都会加1。
  
  
  当 InnoDB 执行 SELECT 时:
  	a. InnoDB 只会查询版本号<= 当前事务Id的数据行,这样可以确保事务读取的行，要么是事务开始之前就已经存在的，要么是事务本身进行的插入或修改。
  	b. 行的删除版本号要么是未定义,要么是大于当前事务Id，这可以确保事务读取的行，在事务开始之前是未被删除的。
  	只有满足上述两个条件，才能作为查询结果进行返回。
  当 InnoDB 执行 INSERT 时:
  	InnoDB为新插入的每一行保存当前事务Id作为行的版本号。
  当 InnoDB 执行 UPDATE 时:
  	InnoDB会插入一条新的记录,并保证当前事务Id作为行的版本号,同时在原来行的过期列保存当前事务Id当作版本号，作为删除标识。
  当 InnoDB 执行 DELETE 时:
  	InnoDB为删除的每一行保存当前事务Id作为行的删除标识。
   
  注: InnoDB的MVCC只在已提交读，不可重复读两个隔离级别下工作。
  

• 如何保证没有幻读发生的

  在当前读的前提下(当前读是指加锁的 select,update,delete语句),可以通过next-key locks 算法来消除幻读的发生。
  	即锁住索引记录之间的范围,避免范围内插入记录,从而防止幻影行记录。
  	
  在普通读的前提下,可以通过MVCC来解决幻读。
  	因为MVCC的读取需要同时满足行创建版本号<=当前事务Id，同时行过期版本号不存在或大于当前事务Id。这样的话即使其他事务在同样的范围内有新的记录插入,也不会被该事务查询到。	
  

• 聚簇索引和非聚簇索引的区别

  聚簇索引即主键索引,索引树的叶子节点保存了完整的数据行。
  非聚簇索引则是指除了主键索引外的其他索引，即普通索引和唯一索引，索引树的叶子节点保存的是主键索引的值。
  
  所以一般而言，主键索引最好是自增并且是整数型的，这既可以减少非聚簇索引树的存储数据量，又防止了分页的发生。
  
  查询效率最高的当然是聚簇索引，因为普通索引查询的话一般会涉及到回表，即两次索引树的查询，而聚簇索引只需要一次索引树查询。除非所有查询的字段都在普通索引树上，这种查询效率也很好,也称为覆盖索引,在explain中表现为Extra字段中显示 Using index。
  

• MyISAM和InnoDB引擎的区别及各自的优缺点

  1. InnoDB支持事务,MyISAM不支持事务
  2. InnoDB数据存储在共享表空间,MyISAM数据存储在文件中
  3. InnoDB支持行级锁,MyISAM只支持表锁
  4. InnoDB支持崩溃后恢复,MyISAM不支持
  5. InnoDB支持外键,MyISAM不支持
  6. InnoDB不支持全文索引, MyISAM支持
  

• 常见的sql优化都有哪些点可以参考

  1.能走主键索引的尽量走主键索引
  2.能走覆盖索引的走覆盖索引
  3.复杂SQL最好优化成多个小SQL,这样查询缓存能充分利用
  4.可以通过前缀索引来减少索引的大小,如email字段,可以用email(n)来做前缀索引,具体n是多少，可以用count(email(n)) / count(email)这个值来比较选择度大小,如果增大n，选择度不再提高,此时n为合适的值
  

• 如何进行大表的分页查询优化

  1. 页面中限制分页的数量
  	 如果一张表的数据量在上百万，但是查询的数据基本是最新的那些，那么就可以限制页面的翻页最多是100页，京东商品的分页目前就是这样实现的。	
  	 
  2. 优化大偏移量的性能
  	 分页查询基本是OFFSET的问题，导致MySQL扫描大量不需要的行然后再抛弃。如果可以记录上次取数据的位置，就可以避免OFFSET。
  	 在实现上就是只能点击上一页或者下一页,这样的话就能根据当前页的数据优化分页查询SQL。
     如 SELECT * FROM rental ORDER BY rental_id DESC LIMIT 20;
     假设查询返回的主键为16049到16030，那么下一页的查询就可以从16030这个点开始:
     SELECT * FROM rental WHERE rental_id < 16030 ORDER BY rental_id DESC LIMIT 20;
  	
  3. 优化大表LIMIT查询SQL,使用索引覆盖扫描
  	 	如 SELECT * FROM user ORDER BY id DESC LIMIT 100000,10;
  	  在 user表的数据库量达到百万之后(id为主键)
  	  优化成: SELECT u.* FROM user u INNER JOIN 	(SELECT id from user ORDER BY id DESC LIMIT 100000,10) AS uer USING(id);
  	  性能能提升3倍左右
  4. 表分区
  	如果大表查询的数据基本都是最新的,则可以在表分成几个区,如100万一个分区,那么查询的时候就可以只扫描一个分区的数据。具体的执行计划，扫描了几个分区可以用 EXPLAIN PARTITIONS 查看
  

• 一条查询sql的执行过程

  1. 客户端发送一条查询给服务器
  2. 服务器先检查查询缓存，如果命中率缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
  3. 服务器端对SQL进行解析,预处理,在优化器生成对应的执行计划
  4. mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询
  5. 将结果返回给客户端