面试系列 | MySQL一些知识点

有这么一张表, 表的数据量为5千万。

-- MySQL version 5.7.17DROP TABLE IF EXISTS student;CREATE TABLE student (    s_id int(11) NOT NULL AUTO_INCREMENT,  sno int(11) COMMENT '学号',   sname varchar(50) COMMENT '姓名',   sage int(11) COMMENT '年龄',   ssex varchar(8) COMMENT '性别',  father_id int(11),  mather_id int(11),  note varchar(500) COMMENT '备注',  primary key (s_id),  unique key uk_sno (sno)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

(造数脚本在最后)

1. 什么是最左前缀原则？

假设我们创建（col1，col2，col3）这样的一个组合索引，那么相当于对col1列进行排序，也就是我们创建组合索引，以最左边的为准，只要查询条件中带有最左边的列，那么查询就会使用到索引; 组合索引最左边的那一列，不管该列在查询条件中的位置，都会使用索引进行查询。

减少开销：假如对col1、col2、col3创建组合索引，相当于创建了（col1）、（col1，col2）、（col1，col2，col3）3个索引

查询条件：

（col1）---会使用索引

（col1，col2）---会使用索引

（col1，col2，col3）---会使用索引

（col2，col3）---不会使用索引

（col3）---不会使用索引

覆盖索引：假如查询SELECT col1, col2, col3 FROM 表名，由于查询的字段存在索引页中，那么可以从索引中直接获取，而不需要回表查询

 

2. 为什么用 B+ 树做索引而不用哈希表做索引?

1、hash表只能匹配是否相等，不能实现范围查找

2、当需要按照索引进行order by时，hash值没办法支持排序(hash是散列值)

3、组合索引可以支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了a和b也可以查询的，如果使用hash表，组合索引会将几个字段合并hash，没办法支持部分索引

4、当数据量很大时，hash冲突的概率也会非常大

B+树的特性：

(1)B+树单节点能存储更多数据，磁盘的IO次数更少。

(2)叶子节点形成有序链表，便于执行范围操作。

(3)聚集索引中，叶子节点的data直接包含数据；非聚集索引中，叶子节点存储数据地址的指针。

 

3. 聚簇索引和非聚簇索引有什么区别？

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，在innodb中，在聚簇索引之上创建的索引称之为辅助索引（Secondary Index， 也就是非聚簇索引），像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。MyISAM通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key_buffer命中时，速度慢的原因。

 

最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的索引顺序与数据物理排列顺序无关

聚簇索引具有唯一性，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键（类似oracle中的RowId）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

 

1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where s_id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

2. 若对sname列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索sname，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

                                                InnoDB检索过程与MyISAM检索过程对比

 

4. 为什么建议使用主键自增的索引？

1、InnoDB引擎表是基于B+树的索引组织表

B+ 树的特点:

（1）所有关键字都出现在叶子结点的链表中(稠密索引)，且链表中的关键字恰好是有序的;

（2）不可能在非叶子结点命中;

（3）非叶子结点相当于是叶子结点的索引(稀疏索引)，叶子结点相当于是存储(关键字)数据的数据层;

2、如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

3、数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

4、如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页

5、如果使用非自增主键（如果×××号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

 

5. count(*) 和 count(1) 有没有差别?

对于InnoDB表, 以相同的方式处理SELECT COUNT(*)和SELECT COUNT(1)操作。没有性能差异。

对于MyISAM表，COUNT(*)如果SELECT从一个表中进行检索，没有其他列被检索，并且没有 WHERE子句， 则优化后可以非常快速地返回。

因为该存储引擎存储了准确的行数，并且可以非常快速地对其进行访问。仅当第一列定义为非空时，计数（1）才受相同优化的约束

 

6. 简单说说ICP?

Index Condition Pushdown(ICP)概念(了解更多, 请移步MySQL官网):

https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

ICP使用的限制, 大概有以下几点:

1、MySQL 5.6版本的不支持分表的ICP功能，5.7版本的开始支持。

2、当sql需要全表访问时，ICP的优化策略可用于range, ref, eq_ref, ref_or_null类型的访问数据方法。

3、ICP可用于InnoDB 和MyISAM表，包括分区表InnoDB和 MyISAM表。

4、对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取的次数，从而减少I/O操作。对于 InnoDB聚集索引，完整的记录已被读入InnoDB 缓冲区。在这种情况下使用ICP不会减少I/O。

5、并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。

6、ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

7、当sql使用覆盖索引时，不支持ICP优化方法。

8、在虚拟生成的列上创建的二级索引不支持ICP。InnoDB 支持虚拟生成的列上的二级索引。

9、不能将引用子查询的条件下推。

ICP图解

mysql server和storage engine是两个组件，server负责sql的parse，执行；storage engine去真正的做数据/index的读取/写入。以前是这样：server命令storage engine按index key把相应的数据从数据表读出，传给server，然后server来按where条件（index filter和table filter）做选择。5.7版本后Index Filter与Table Filter分离，Index Filter下降到InnoDB的索引层面进行过滤，如果不符合条件则无须读数据表，减少了回表与返回MySQL Server层的记录交互开销，节省了disk IO，提高了SQL的执行效率。

 

7. 简单聊聊explain?

详细解释请移步官网:

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

explain需要在实际的开发中多查看, 这是一个调优的手段。

Using where：表示优化器需要通过索引回表查询数据；

Using index：表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；

Using index condition：在5.6版本后加入的新特性索引下推（Index Condition Pushdown）;会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行;

Select tables optimized away: SELECT操作已经优化到不能再优化了, 在查询执行期间无需读取任何表的数据(例: explain select count(*) from student)。

-- explain SELECT sno FROM `student` where sno = 8999629;-- 1  SIMPLE  student    const  uk_sno  uk_sno  5  const  1  100  Using index-- explain SELECT s_id FROM `student` where s_id > 9999629;-- 1  SIMPLE  student    range  PRIMARY  PRIMARY  4    370  100  Using where; Using index-- explain SELECT * FROM `student` where s_id > 9999629;-- 1  SIMPLE  student    range  PRIMARY  PRIMARY  4    24342184  100  Using where-- 查询的列被索引列覆盖，where筛选条件非索引的前导列，Extra中为Using where​​-- explain SELECT * FROM `student` where sno > 8999629 and sname like '%name%';-- 1  SIMPLE  student    ALL  uk_sno        48684368  5.56  Using where-- explain SELECT * FROM `student` where sname like '%name%' and sno > 8999629;-- 1  SIMPLE  student    ALL  uk_sno        48684368  5.56  Using where-- explain SELECT * FROM `student` where sno > 48999629 and sname like '%name%';-- 1  SIMPLE  student    range  uk_sno  uk_sno  5    2035364  11.11  Using index condition; Using where-- explain SELECT * FROM `student` where sname like '%name%' and sno > 48999629;-- 1  SIMPLE  student    range  uk_sno  uk_sno  5    2035364  11.11  Using index condition; Using where-- 组合索引最左边的那一列，不管该列在查询条件中的位置，都会使用索引进行查询 sno > 48999629 and sname like '%name%'谁前谁后无关-- 优化器会在索引存在的情况下，通过符合 RANGE 范围的条数和总数的比例来选择是使用索引还是进行全表遍历-- 结论：当需要读取的数据超过一个临界值时，优化器会放弃从索引中读取而改为进行全表扫描，这是为了避免过多的 random disk.-- type都是ALL，说明MySQL认为全表扫描是一种比较低的代价。-- WHERE子句优化: https://dev.mysql.com/doc/refman/5.7/en/where-optimization.html

 

 

有一篇MySQL优化原理讲的很清楚 : https://www.jianshu.com/p/d7665192aaaf

 

建表脚本

use stu;-- student tabledrop table if exists student;create table student   (  s_id int(11) not null auto_increment ,      sno    int(11),       sname varchar(50),       sage  int(11),       ssex  varchar(8) ,      father_id int(11),      mather_id int(11),      note varchar(500),      primary key (s_id),   unique key uk_sno (sno)  ) engine=innodb default charset=utf8mb4;truncate table student;  delimiter $$drop function if exists insert_student_data $$create function insert_student_data() returns  int deterministic    begin    declare  i int;      set i=1;      while  i<=50000000 do       insert into student  values(i ,i, concat('name',i),i,case when floor(rand()*10)%2=0 then 'f' else 'm' end,floor(rand()*100000),floor(rand()*1000000),concat('note',i) );      set i=i+1;      end while;      return 1;    end$$delimiter ;    select  insert_student_data();select count(*) from student;​