1.说一说MySQL中的索引

​ MySQL数据库的索引是用B+树实现的。在B+树中非叶节点只存储关键字和指针（可以存储更多的关键字，降低树高），叶子节点存储关键字和数据。相邻的叶子节点间有指针连接，范围查找更为快速。通过B+树索引进行查找的时间复杂度为树高，一般是2~4次。（数据结构）

​ MySQL数据库中的索引有两种：聚集索引、稀疏索引。其中聚集索引的叶子节点会存储实际表中真实的数据，一个表只能有一个聚集索引，而稀疏索引的叶子节点存储的是主键的值。也就是说，通过稀疏索引进行查找，首先会查询稀疏索引树，找到符合条件的聚集索引的关键字，然后用该关键字在聚集索引树中进行查找，找到最终的数据。

​ MySQL数据库中InnoDB引擎主索引是聚集索引，叶子的value存储真实数据，辅助索引是稀疏索引，叶子节点value存储主键的值；而Myisam引擎主索引和辅助索引都是稀疏索引，两者没有任何区别（value都存储数据的地址）。

2.为什么MySQL索引使用B+树，不是B树

​ B+树是B树的变种，B+树中非叶节点只用来保存索引（关键字和指针），不储存数据，所有的数据存储在叶子节点上；而B树中的非叶节点会保存数据，这样使得B+树的查询效率更加稳定，均是从根节点到叶子节点。

​ B+树非叶节点不存储数据，所以可以存储更多的关键字，使得B+树查找的磁盘IO数更少。

​ B+树相邻的叶子节点间有指针连接，范围查询更为有利。

3.为什么推荐采用自增主键

​ InnoDB推荐使用自增列作为主键，这样可以提高存取速度。因为如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的。

4.MySQL常见的存储引擎有哪些

​ MySQL中常见的存储引擎有InnoDB和MyISAM，主要区别是：

  • MyISAM不支持事务；InnoDB是事务类型的存储引擎。
  • MyISAM只支持表级锁；InnoDB支持行级锁和表级锁，默认为行级锁。
  • MyISAM引擎不支持外键；InnoDB支持外键。
  • MyISAM支持全文索引（FULLTEXT）；InnoDB不支持。
  • MyISAM索引都是是稀疏索引，叶子节点存储真实数据的指针，数据和索引分离；InnoDB采用聚集索引，真实数据在聚集的叶子节点上，辅助索引是稀疏索引，叶子节点存储主键值。

5.MySQL中where、group by、having关键字

  • where子句用来筛选from子句中指定的操作所产生的的行
  • group by 子句用来分组where子句的输出
  • having子句用来从分组的结果中筛选组

where和having的区别：where 是group by之前进行筛选，筛选的是行，having是group by 之后进行统计的筛选，筛选的是组。

执行顺序：执行where子句查找符合条件的数据 > 使用group by 子句对数据进行分组 >对group by 子句形成的组运行聚集函数计算每一组的值>最后用having 子句去掉不符合条件的组

6.索引的最左前缀原则

​ 在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀，就可以利用索引来加快查询速度。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段。

​ 如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

1
2
3
select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引            
 这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 `city= xx and name ＝xx`，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

7.那些列上适合建立索引，对性能有何开销

​ 主键、外键必须有索引。

​ 经常需要作为（where）条件查询的列上适合创建索引，并且该列上也必须有一定的区分度。创建索引需要维护，在插入数据的时候会重新维护各个索引树（数据页的分裂与合并），对性能造成影响。

8.“行级锁什么时候会锁住整个表？“

​ InnoDB行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。

9.MySQL中建表的约束

  • 主键约束：唯一性，非空性
  • 唯一约束：唯一性，可以空，但只能有一个
  • 检查约束：对该列数据的范围、格式的限制
  • 默认约束：该数据的默认值
  • 外键约束：需要建立两表间的关系并引用主表的列

10.SQL语句的优化有哪些？

  • 为经常使用的字段（排序、搜索）建立索引
  • 字段的种类尽可能用int 或者tinyint类型。另外字段尽可能用NOT NULL。
  • select * 尽量少用，你想要什么字段 就select 什么字段出来 不要老是用* 号！同理，只要一行数据时尽量使用 LIMIT 1

11.order by是怎么工作的？

​ ORDER BY 语句用于对结果集进行排序。以下面的语句进行分析：

1
select city,name,age from t where city='杭州' order by name limit 1000 ;

city字段建立了索引，过程如下：

  • 初始化 sort_buffer, 确认放入 name, city, age 这三个字段。

  • 从索引 city 找到第一个满足 city=’杭州’条件的主键 id。

  • 回表取到 name, city, age 三个字段值，存入 sort_buffer 中。

  • 从索引 city 取下一个主键 id 记录。

  • 重复 3-4 步骤，直到 city 不满足条件。

  • 对 sort_buffer 中的数据按照字段 name 做快速排序。

  • 排序结果取前 1000 行返回给客户端。