Mysql常见面试题大全（二）

什么是聚簇索引？

        Mysql索引根据物理储存的形式分为聚簇索引和非聚簇索引(也叫做二级索引、辅助索引)。主要区别是聚簇索引叶子节点存放表的行记录数据，非聚簇索引叶子节点存的还是指针，指向对应的数据块，也就是说聚簇索引的叶节点就是数据节点，非聚簇索引的叶节点仍然是索引节点

        聚簇索引我的理解就是按照每张表的主键构造一颗B+树，同时叶子节点中存放了表的行记录数据，同B+树数据结构一样，每个数据页都通过一个双向链表来进行连接，通过聚簇索引查找数据时理论上比非聚簇索引要快，因为聚簇索引能够在B+树索引的叶子节点上直接找到数据，二非聚簇索引查找目标数据需要多一次索引查询，类似一次回表

        同时聚簇索引的存储便不是物理上的连续而是逻辑上的连续，数据页之间通过双向链表连接，按照主键的顺序进行排序，而数据页内的记录是通过单向链表进行维护的，物理存储上可以不按照主键的顺序存储，因为都是链表

---

什么是最左优化原则，为什么会出现不走索引的现象

        当出现多个列构成联合索引时，查找某个数据，只有条件中包含联合索引最左侧索引时，该查询才会走索引，否则会全表扫描，这就是最左优化原则

        在联合索引构成的B+树中，和主键索引类似，不同之处在与索引元素放置了是哪个，依次递增进行排序，优先级是按照联合索引设置使得优先级，也就是第一个索引排序优先级最高，依次类推(按照数字递增排列，或者字母顺序排序)。由第一个字段能够区分，后面就不会再次查看。

        所以没有联合索引第一个元素参与的情况下，按照其他索引进行查找并不能保证存储的元素是从左到右依次递增排序的，所以要进行全表扫描，防止漏掉数据的情况，而带有第一个索引元素时，虽然其他索引元素不能保证递增排序，但是第一个是可以保证的，出了第一个元素的范围，都不是有效数据，可以忽略

        存在最左元素时，也不一定会走索引，例如select * from table where xxx > 1，这样的SQL虽然可以使用xxx=1定位到位置，但是因为是辅助索引而不是主键索引，涉及到回表的问题，当数据量小(例如8条，定位到第一条)时，索引查找再加上7次回表定位主键索引，实际效率不如直接全表扫描，还是取决于MYSQL的底层优化器选择，例如条件是xxx > 6查看explain就会走索引；而SQL改为select b,c,d from table where xxx > 1就会走索引(bcd为联合索引)，因为辅助索引的B+树上有相应数据，不用回表查找主键索引，当查找内容带上主键索引时也是一样，因为非叶子节点存储了主键索引，也就是主键的值，当然此时必须要符合最左原则。

        在联合索引中执行select b from table 也会走索引，为什么不会全表扫描呢？因为全表扫描时，每个叶子结点会存储每一行的全部数据，在MySQL的数据结构中，使用页作为基础数据结构，如果存储索引数据，每一页存储的b数据较少，而辅助联合索引中，不会存储全部的数据，每一页存储的b数据会更多，执行速度会更快，所以会走辅助联合索引。这里的b是联合索引的最左索引，这时使用explain关键字：explain select b from table，得到的type显示为index，执行速度仅仅快于全表扫描。

        如下的SQL也可能不会走索引：select * from table order by b,c,d；其中bcd为联合索引，是因为即使有了联合索引，因为是非主键索引，涉及到回表的问题，可能导致执行时间过长，不如全表扫描，所以MySQL的优化器选择了全表扫描

MySQL索引失效的场景？根据什么去创建索引

• 列类型是字符串，查询条件未加引号

        card_code列是身份证号，数据类型是varchar,在没有将证件号码用引号括起时不会使用索引，此时索引失效。

• 未使用该列作为查询条件

        索引建在card_code列上，使用tel列作为查询条件，此时该索引未被使用到，也可以说是失效的

• 使用like时通配符在前

        我们可以看到通配符在后面时效率不受影响，说明此时索引未失效，通配符在前时索引失效

• 在查询条件中使用OR

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

• 对索引列进行函数运算
• 联合索引ABC问题

        Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是index (a,b,c)，可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c或c进行查找

---

MySQL的组成和整体结构说一下

连接层

        与客户端打交道，上面已经写明了能支持的的语言。客户端的链接支持的协议很多，比如我们在 Java 开发中的 JDBC

连接池

        主要是负责存储和管理客户端与数据库的链接，一个线程负责管理一个连接。自从引入了连接池以后，官方报道：当数据库的连接数达到128后，使用连接池与没有连接池的性能是提升了n倍（反正就是性能大大的提升了）。

连接建立完成后，就可以执行select语句了。执行逻辑就会先来到缓存模块。

缓存

        MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果会以key-value对的形式存储在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key（命中），那么这个value就会被直接返回给客户端。

        如果在缓存中未命中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。这里可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

        但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。

        查询缓存的失效非常频繁，只要有对一个表的某一条数据更新，这个表上所有的查询缓存都会被清空。

        因此可能很费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。

        MySQL 8.0版本直接将查询缓存的整块功能删掉了，标志着MySQL8.0开始彻底没有缓存这个功能了。

解析器

        如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。

        分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。

        做完了词法分析以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

        如果我们在拼写SQL时候，少了或者写错了某个字母，，就会收到“You have an error in your SQL syntax”的错误提醒。

        经过分析器对SQL进行了分析，并且没有报错。那么此时就进入优化器中，对SQL进行优化。

优化器

        优化器主要是在我们的数据库表中，如果存在多个多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

        SELECT a.id, b.id FROM t_user a join t_user_detail b WHERE a.id=b.user_id and a.user_name='田维常' and b.id=10001

        它会在条件查询上进行优化处理。

        优化器处理完成过后，此时就已经确定了SQL的执行方案。然后继续进入执行器中。

执行器

        首先，肯定是要判断权限，就是有没有权限执行这条SQL。工作中可能会对某些客户端进行权限控制。

        比如说：生产环境中，对于大部分开发人员都只开查询权限，没有增删改权限（部分小公司除外）。

        如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

---

批量往MySQL数据库导入1000万条数据有什么思路

       使用LOAD DATA语句要比INSERT语句效率高，因为它批量插入数据行。服务器只需要对一个语句（而不是多个语句）进行语法分析和解释。索引只有在所有数据行处理完之后才需要刷新，而不是每处理一行都刷新。

---

MySQL查询过程

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