MYSQL知识点

1. mysql explain是什么？有什么作用？描述下type

   模拟优化器执行SQL语句

   作用:Explain 提供了 MySQL 如何执行 SQL 语句的信息，通过这些信息，可以对 SQL 语句做相应的优化，提高执行效率。

   type：该列称为关联类型或者访问类型，它指明了 MySQL 决定如何查找表中符合条件的行，同时为判断查询是否高效提供了重要的依据。

   type主要取值及其表示sql的好坏程度（由好到差排序）：NULL>const>eq_ref>ref>range>index>ALL。保证range，最好到ref。

2. mysql中有哪几种锁？表锁、行锁、页锁区别？

   表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

   行锁：开销大，加锁慢；会出现死锁；锁定力度小，发生锁冲突的概率低，并发度高

   页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定力度介于表锁和行锁

3. 悲观锁 for update 乐观锁(version) 的区别

   悲观锁是先获取锁，再进行业务操作，并发度一般。在数据库上的悲观锁需要数据库本身提供支持

   乐观锁先进行业务操作，不到万不得已不去拿锁。乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持

4. 什么是索引？索引的种类有哪些，描述索引的优缺点?

   索引是表中的值以及各值存储位置的列表。

   索引主要有聚集索引、非聚集索引、唯一索引、索引视图、全文索引、xml索引。

   优点:1) 建立索引的列可以保证行的唯一性，生成唯一的rowId；

   2. 建立索引可以有效缩短数据的检索时间；

   3. 建立索引可以加快表与表之间的连接；

   4. 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序。

   缺点:1) 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大；

   2. 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间），从而会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，这样会导致时间变长。

5. 设计索引的原则？(不少于4条)

   (1)避免对经常更新的表进行过多的索引，并且索引中的列尽可能的少。

   (2)数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

   (3)当唯一性时某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

   (4)不要过度索引

   (5)利用最左前缀（在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。）

6. 什么情况下索引会失效？（不少于4条）

   （1）like查询是以%开头，索引失效；以%结尾，索引有效

   （2）列类型是字符串，查询条件未加引号。

   (3)查询条件中使用or会使索引失效，要想是索引生效，需要将or中的每个列都加上索引。

   （4）不匹配数据类型，会造成索引失效

7. Btree和Hash区别

   （1）btree可以用作范围查询，而hash只能用作对等查询

   （2）hash一次定位数据，btree总是从根到叶子节点，所以hash检索效率更高

   (3)hash一定要回表查询，btree聚簇索引可以不用回表查询

8. 数据库优化方案？

   SQL语句优化

   索引优化

   数据表结构优化

   数据库配置优化

   硬件优化

9. 什么是存储过程？有什么优缺点？

   事先经过编译并存储在数据库中的一段SQL语句的集合

   优点：可以重复使用，减少网络流量，防止SQL注入式攻击。

   缺点:程序部分功能移到了数据库内，破坏了cvm三层结构设计.

   可移植性不灵活,开发调试差

10. 什么是视图，视图有什么作用？

    是一种虚拟存在的表，是一个逻辑表，本身并不包含数据

    作用:隐藏了数据的复杂性

    使用户查询变得简单

    对基表中的其它列起到安全和保密的作用，可以限制数据访问。