伤豆丁文库网站开发,yoast wordpress seo plugin,网站开发怎么挣外快,济南建设工程有限公司面试突击---MYSQL索引 面试表达技巧#xff1a;1、谈一下你对于mysql索引的理解#xff1f;#xff08;为什么mysql要选择B树来存储索引#xff09;2、索引有哪些分类#xff1f;3、聚簇索引与非聚簇索引4、回表、索引覆盖、最左匹配原则、索引下推#xff08;1#xff… 面试突击---MYSQL索引 面试表达技巧1、谈一下你对于mysql索引的理解为什么mysql要选择B树来存储索引2、索引有哪些分类3、聚簇索引与非聚簇索引4、回表、索引覆盖、最左匹配原则、索引下推1回表2索引覆盖3最左匹配原则4索引下推Index Condition Pushdown, ICP 5、如何设计性能优良的索引6、什么情况下会造成索引失效7、主键为什么建议选择自增主键8、如何查看SQL语句是否使用索引idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredextra 面试表达技巧
1、回答任何问题的时候不要着急给出答案先把面试官的问题听清楚
2、听到问题无论是自己会的问题还是不会的问题先在脑海里想一两秒中把要说的话做一个梳理
3、回答任何问题的时候要有条理如果不知道怎么回答就按照总分或者总分总的方式来说
4、面试中遇到自己不会的问题不要慌知道多少说多少实在搞不定的大方承认自己没有接触过不要顾左右而言他
1、谈一下你对于mysql索引的理解为什么mysql要选择B树来存储索引
mysql的索引选择B树作为数据结构来进行存储使用B树的本质原因在于可以减少IO次数提高查询的效率简单点来说就是可以保证在树的高度不变的情况下可以存储更多的数据
1、在MYSQL的数据库中表的真实数据和索引数据都是存储在磁盘中我们在进行数据读写的时候必然涉及到IO的问题IO本质上来说是硬件方面的问题但是我们在做索引设计的时候肯定要尽可能的考虑如何提高IO的效率一般来说提高IO效率主要有两个维度的考虑减少IO次数和减少IO量所以要遵循这两个原则
2、我们在进行数据存储的时候量是没办法预估的当表的数据量非常大的时候我们是没有办法一次性将所有的数据都读取到内存中因此这个时候就要采用分治的思想将数据进行分块读取一旦分块读取的话我们就要考虑设计合理的块大小
3、数据在磁盘存储的时候有时间局部性和空间局部性的特性内存跟磁盘在进行数据交互的时候也不是需要啥就读取啥而是会把相关的数据全部都加载到内存中在进行加载的时候有一个最基本的逻辑单位称之为页页的大小一般是4KB或者8KB跟操作系统相关我们在数据读取的时候一般会选择页的整数倍读取比如innodb存储引擎每次读取16KB的大小。这个特性刚刚好跟我们上述说的分块读取的设计观念吻合起来因此块的大小会选择页的整数倍在MYSQL中一般都是16KB的大小当然也可以通过参数来进行调整比如innodb中的innodb_page_size这个参数当然一般情况下我们不会调整这个参数的大小
4、当块的大小确定了之后我们就要考虑数据格式了我们在使用索引的时候基本是根据某一个或者多个索引列的值来进行整行数据或者部分字段的读取比如select * from table where id 10这个语句就是根据id的值去检索整行记录因此整体的数据格式可以设计为K-V格式的数据K值就是索引列的值V值的设计就需要进一步思考了。
5、正常情况下当需要从磁盘中读取某一行记录的时候需要知道一些信息才能够定位到数据比如文件名称偏移量数据长度当知道这些信息的时候就可以定位到任何一行记录所以我们可以将V的值设计为刚刚的几个字段但是要考虑一件事如果将刚刚的那些信息作为索引信息的话那么在进行数据读取的时候首先要打开一个文件读取到刚刚的那几个字段信息然后再根据那些信息找到对应的数据文件读取具体的行数据如果打开一次文件就是一次IO的话至少需要2次IO操作才可以读取这个跟我们上面所说的减少IO次数有点相违背所以最好的方式是在V中直接将行记录进行存储那么在读取数据的时候就可以直接根据K值读取到行记录只不过此时需要将数据跟索引绑定存储在MYSQL中innodb存储引擎就是这样存储的数据文件和索引文件全部位于后缀名为ibd的文件中
6、当数据格式确定了之后我们就需要思考使用什么数据结构存储了。支持K-V格式的数据结构有很多比如哈希表二叉树BSTAVL红黑树但是MYSQL最终选择了B树下面我们要对比下各个数据结构之间的区别
1使用哈希表可以进行数据存储但是哈希表本质是无序散列表因此在进行范围查询的时候就必须要挨个进行数据的对比此时的效率是比较低的此外哈希表会存在哈希碰撞或者哈希冲突的问题需要设计性能优良的哈希算法因此哈希表并不适用但是在MYSQL中MEMORY存储引擎支持哈希索引innodb存储引擎支持自适应哈希
2二叉树、BST、AVL、红黑树这几种树也可以支持K-V格式的数据存储但是它们有一个共同的特点就是至多只有两个分支那么在进行数据存储的时候一个三层的树至多可以存储7个数据结果值这个数据太少了如果想要存储更多的数据只能把树的高度变高而树的高度变高之后又会导致IO的次数变多影响查询效率那么我们就要思考如何在保证树的高度不变的情况下存储更多的数据上述的这些树存储数据少的原因在于分支至多只有两个那么我们就要思考改变分支的结构了因此有了B-树。
3使用B-树之后存储如下图所示在每一个数据块中包含三种类型的数据分别是key值行记录和指针当需要进行数据读取的时候只要一层一层向下检索即可 在上图中如果要读取28这条记录的话那么只需要读取磁盘块1,3,8就可以把数据取出来如果一个磁盘块大小是16KB的话那么读取48KB的数据就可以获取到要查询的记录此时我们就要思考这样3层的B-树存满的情况下可以存储多少条记录了假设一条记录是1KB的大小那么第一层至多存储15条记录第二层至多存储16第二层的子节点个数*15每个节点可以存储的行记录树240条记录第三层至多存储16*16*153840条记录那么三层的树存满的情况下最多存储1524038404095条记录此时存储的数据量依然不是很大如果想要存储更多的数据的话就只能将树变高变成4层或者5层那么此时又会增加IO的次数会影响查询效率那么此时就要思考为何只能存储这么少的数据经过分析之后发现data占用了大量的空间因此考虑使用B树进行数据的存储
4使用B树之后存储如下图所示将所有的data全部放到了叶子节点中非叶子节点中只存储key值和指针的值在进行检索的时候可以从根节点向下检索也可以在叶子节点中从前向后或者从后向前检索 在上图中所有的data都在叶子节点中也就说第一层和第二层省掉了大量的data的存储空间那么可以存储更多的数据假设一个data还是1KB的大小一个key加上一个指针的大小为10个字节那么我们可以来计算下可以存储多少数据第一层一个数据块第二层16*1024/101638个数据块第三层1638**16*1024\102683044个数据块第三层的每个数据块可以存储16条记录那么最后的总记录数为42928704条记录可以发现跟B-树的存储不是一个量级在相同树高的情况下B树可以存储更多的数据
因此MYSQL最终选择了B树做为数据结构来存储在刚刚上述的计算公式中我们做了一个假设key指针一共占了10个字节如果占用100个字节的话那么整体的数据会缩小2个量级因此在回答索引的树的高度的时候不要说3层或者4层给一个标准说法一般情况下3-4层的B树足以支撑千万级别的数据量存储。
2、索引有哪些分类
索引的分类要按照不同的角度去进行分类
1、从数据结构的角度可以分为B树索引、哈希索引、FULLTEXT索引、R-Tree索引用于对GIS数据创建SPATIAL索引
2、从物理存储的角度可以分为聚簇索引和非聚簇索引
3、从逻辑角度可以分为主键索引、普通索引、唯一索引和组合索引
3、聚簇索引与非聚簇索引 在MYSQL的innodb存储引擎中数据在进行插入的时候必须要跟某一个索引列绑定在一起进行存储如果有主键那么选择主键如果没有主键那么选择唯一键如果没有唯一键那么系统会生成一个6字节的rowid进行存储因此
跟数据绑定存储的索引称之为聚簇索引
没有跟数据绑定存储的索引称之为非聚簇索引
一张表中只有一个聚簇索引其他非聚簇索引的叶子节点中存储的值为聚簇索引的列值
4、回表、索引覆盖、最左匹配原则、索引下推
1回表
回表表示使用非聚簇索引时数据库引擎会先根据普通索引找到匹配的行然后根据叶子节点中存储的聚簇索引的值去聚簇索引的索引树中查找整行记录的过程。例如
有一张表有如下字段idnameagegenderaddress其中id是主键name是普通索引
那么要进行如下SQL语句的查询
select * from table where name zhangsan; 上述SQL语句的查找过程是先根据name的值去name的索引树上进行检索找到匹配的记录之后取出id的值然后再根据id的值去id的B树上检索整行的记录在这个过程中查找了两棵树多进行了棵树的IO因此效率比较低在生产环境中应该尽量避免回表
2索引覆盖
索引覆盖是指一个索引包含了查询所需要的所有数据从而在查询中无需回表从原表中获取数据
假设有一张表表中有以下字段idnameagegenderaddress其中id是主键name是普通索引
那么要进行如下SQL语句的查询
select id,name from table where name zhangsan; 查找过程如下在name的索引树上包含了要查询的所有字段所以直接通过name字段去name的B树上检索对应的记录即可不需要找到id之后再去id的B树上检索数据
索引覆盖可以提高查询的性能所以在生产环境做SQL优化的时候可以考虑索引覆盖
3最左匹配原则
最左匹配原则主要适用于组合索引指的是多个列值进行匹配的时候要严格遵循从左到右的顺序否则会导致索引失效
假设有一张表表中有以下字段idnameagegenderaddress
id是主键(name,age)是组合索引1、Select * from table where name zhangsan and age 10;
2、Select * from table where name zhangsan;
3、Select * from table where age 10;
4、Select * from table where age 10 and name zhangsan;上述的四条语句中1,2,4都可以用到组合索引3用不到但是很多同学会有疑问为什么第四条会用到明明不符合最左匹配原则的顺序这里需要注意如果把第四条SQL语句的条件换一下顺序会影响最终的查询结果吗答案是不会的所以mysql中的优化器会进行优化调整条件的顺序
4索引下推Index Condition Pushdown, ICP
ICP是针对mysql使用索引从表中检索行的情况进行优化如果没有ICP那么存储引擎会根据索引来定位到记录然后将结果返回给mysql的server然后在server上对where条件进行筛选。在启用ICP之后如果where条件的一部分可以通过使用索引中的列来求值那么mysql会把这部分的where条件筛选下推到存储引擎中。
使用索引下推的时候会有以下的条件
1、当需要访问完整的行记录时ICP用于range、ref、eq_ref和ref_or_null访问方法
2、ICP可以用于innodb和myisam表包括分区的innodb表和myisam表
3、对于innodb表ICP仅用于二级索引。ICP的目标是减少整行读取的次数从而减少IO操作
4、在虚拟列上创建的二级索引不支持ICP
5、引用子查询的条件不能下推
6、引用存储函数的条件不能下推
7、触发器条件不能下推
8、不能将条件下推到包含对系统变量引用的派生表中
假设有一张表表中有以下字段idnameagegenderaddress其中id是主键(name,age)是组合索引
select * from table where name zhangsan and age 10; 没有索引下推mysql执行这条SQL语句的时候会首先根据name的值去存储引擎中拉取数据然后将数据返回到mysql server然后在server层对age进行条件过滤把符合条件的结果返回给客户端 有索引下推mysql执行这条SQL语句的时候会直接根据name和age的值去存储引擎中拉取数据而无需在server层对数据进行条件过滤
所谓的下推指的是将条件的筛选从server层下推到存储引擎层
可以通过optizizer_switch中的index_condition_pushdown条件来是否开启默认是开启的
# 关闭
SET optimizer_switch index_condition_pushdownoff;
# 开启
SET optimizer_switch index_condition_pushdownon;5、如何设计性能优良的索引
1、索引列占用的空间越小越好
2、选择索引列的时候尽量选择离散度高的列作为索引列离散度的计算公式count(distinct(column_name)) / count(*)这个值越大那么越适合做索引
3、在where后的order by字段上添加索引如果有where条件是跟where条件一起创建如果没有就只是order by
4、在join on的条件字段上添加索引
5、索引的个数不要过多会增加索引的维护成本
6、频繁更新的字段不要创建索引会增加索引的维护成本
7、随机无序的值不建议作为主键索引如身份证号UUID
8、索引列在设计的时候最好不为NULL
9、可以使用列前缀作为索引列 从上面三张图中可以看出使用列前缀7个字符和8个字符就一样了而且非常接近使用整个字段分组的结果了所以可以使用列前缀7个字符创建索引最合适如下图
6、什么情况下会造成索引失效
1、索引列上使用函数replace\SUBSTR\CONCAT\sum count avg、表达式
2、数据类型不匹配当查询条件的数据类型和索引字段的类型不匹配
3、like 条件中前面带%
4、在组合索引中不满足最左匹配原则
5、使用is not null
6、mysql的优化器在进行分析的时候发现全表扫描比使用索引快的时候
7、使用or关键字会导致索引失效
7、主键为什么建议选择自增主键
如果选择自增主键的话每次新增数据时都是以追加的形式进行存储在本页索引写满之后只需申请一个新页继续写入即可不会产生页分裂问题
如果说采用业务字段作为主键的话新增数据不一定是顺序的需要挪动数据页快满时还要去分裂页保持索引的有序性造成写数据成本较高
8、如何查看SQL语句是否使用索引
通过执行计划可以判断查询中是否用到了索引以便进行SQL优化。
explain语句提供了mysql如何执行语句的信息explain可以跟select、delete、insert、replace、update语句一起工作
ColumnJSON NameMeaningidselect_idThe SELECT identifierselect_typeNoneThe SELECT typetabletable_nameThe table for the output rowpartitionspartitionsThe matching partitionstypeaccess_typeThe join typepossible_keyspossible_keysThe possible indexes to choosekeykeyThe index actually chosenkey_lenkey_lengthThe length of the chosen keyrefrefThe columns compared to the indexrowsrowsEstimate of rows to be examinedfilteredfilteredPercentage of rows filtered by table conditionExtraNoneAdditional information
id
select查询的序列号包含一组数字表示查询中执行select子句或者操作表的顺序
id号分为三种情况
1、如果id相同那么执行顺序从上到下;
2、如果id不同如果是子查询id的序号会递增id值越大优先级越高越先被执行;
3、id相同和不同的同时存在相同的可以认为是一组从上往下顺序执行在所有组中id值越大优先级越高越先执行
select_type
主要用来分辨查询的类型是普通查询还是联合查询还是子查询
select_type ValueJSON NameMeaningSIMPLENoneSimple SELECT (not using UNION or subqueries)PRIMARYNoneOutermost SELECTUNIONNoneSecond or later SELECT statement in a UNIONDEPENDENT UNIONdependent (true)Second or later SELECT statement in a UNION, dependent on outer queryUNION RESULTunion_resultResult of a UNION.SUBQUERYNoneFirst SELECT in subqueryDEPENDENT SUBQUERYdependent (true)First SELECT in subquery, dependent on outer queryDERIVEDNoneDerived tableDEPENDENT DERIVEDdependent (true)Derived table dependent on another tableMATERIALIZEDmaterialized_from_subqueryMaterialized subqueryUNCACHEABLE SUBQUERYcacheable (false)A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer queryUNCACHEABLE UNIONcacheable (false)The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)
1、simple: 简单的查询不包含子查询和union
explain select * from emp;2、primary: 查询中最外层的查询如果查询中有子查询则最外层的查询被标记为primary
explain select * from emp where ename not in (select ename from emp where ename like %S%) ;3、union: 若第二个select出现在union之后则被标记为union
explain select * from emp where deptno 10 union select * from emp where sal 2000;4、dependent union: 跟union类似此处的depentent表示union或union all联合而成的结果会受外部表影响
explain select * from emp e where e.empno in ( select empno from emp where deptno 10 union select empno from emp where sal 2000)5、union result: 表示一个union的结果集作为一个单独的表返回这通常发生在union操作之后并且可能跟其他表进行join操作
explain select * from emp where deptno 10 union select * from emp where sal 2000;6、subquery: 在查询中作为另一个查询的子查询的查询例如在 SELECT ... WHERE column IN (SELECT ...) 结构中的子查询。
explain select * from emp where sal (select avg(sal) from emp) ;7、dependent subquery: 与subquery类似但是这个查询依赖于外部查询的某些部分。
explain select e.empno,e.ename,e.sal from emp e where e.sal (select e2.sal from emp e2 where e2.empno e.mgr)8、derived: 出现在from子句中的子查询MySQL会为这个子查询生成一个临时表。这个值表示该查询是为派生表生成的。
explain select t.job from (select min(sal) min_sal,job from emp group by job) t where t.min_sal 2500 ;9、dependent derived与derived类似但是这个查询依赖于外部查询的某些部分未找到案例
10、materialized表示该子查询的结果被物化即存储在临时表中以供稍后的join使用这种类型的子查询在执行时比常规子查询要慢
EXPLAIN select * from emp where deptno in (select deptno from (select min(sal) min_sal,deptno from emp group by deptno) a where min_sal 2000) ;11、UNCACHEABLE SUBQUERY一个子查询的结果不能被缓存因此每次都会重新计算未找到案例
12、UNCACHEABLE UNION: 一个union的结果不能被缓存因此每次都会重新计算未找到案例
table
对应行正在访问哪一个表表名或者别名可能是临时表或者union合并结果集 1、如果是具体的表名则表明从实际的物理表中获取数据当然也可以是表的别名
2、表名是derivedN的形式表示使用了id为N的查询产生的衍生表
3、当有union result的时候表名是union n1,n2等的形式n1,n2表示参与union的id
type
type显示的是访问类型访问类型表示我是以何种方式去访问我们的数据最容易想的是全表扫描直接暴力的遍历一张表去寻找需要的数据效率非常低下访问的类型有很多效率从最好到最坏依次是
system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
一般情况下得保证查询至少达到range级别最好能达到ref
1、all: 全表扫描一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
explain select * from emp;2、index全索引扫描这个比all的效率要好主要有两种情况一种是当前的查询时覆盖索引即我们需要的数据在索引中就可以索取或者是使用了索引进行排序这样就避免数据的重排序
explain select empno from emp;3、range表示利用索引查询的时候限制了范围在指定范围内进行查询这样避免了index的全索引扫描适用的操作符 , , , , , , IS NULL, BETWEEN, LIKE, or IN()
explain select * from emp where empno between 7000 and 7500;4、index_subquery跟unique_subquery类型使用的是辅助索引
# 测试前先关闭优化器
SET optimizer_switchmaterializationoff;
# 查看执行计划
EXPLAIN select * from emp where ename not in (select dname from dept where dname like %SALES );
# 打开优化器
SET optimizer_switchmaterializationon;5、unique_subquery:子查询的结果由聚簇索引或者唯一索引覆盖
--dept表的deptno字段有主键
SET optimizer_switchmaterializationoff;
EXPLAIN select * from emp where deptno not in (select deptno from dept where deptno 20 );
SET optimizer_switchmaterializationon;6、index_merge索引合并在where条件中使用不同的索引字段
--enamedeptno都创建索引
explain select * from emp where enameSMITH or deptno 10;7、ref_or_null跟ref类似在ref的查询基础上加一个null值的条件查询
explain select * from emp where ename SMITH or ename is null;8、ref使用了非聚集索引进行数据的查找
alter table emp add index idx_name(ename);
explain select * from emp where ename SMITH;9、eq_ref 使用唯一性索引进行数据查找
explain select * from emp e,emp e2 where e.empno e2.empno;10、const这个表至多有一个匹配行
explain select * from emp where empno 7369;11、system表只有一行记录等于系统表这是const类型的特例平时不会出现
possible_keys
显示可能应用在这张表中的索引一个或多个查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用
explain select * from emp where ename SIMTH and deptno 10;key
实际使用的索引如果为null则没有使用索引查询中若使用了覆盖索引则该索引和查询的select字段重叠。
explain select * from emp where ename SIMTH and deptno 10;key_len
表示索引中使用的字节数可以通过key_len计算查询中使用的索引长度在不损失精度的情况下长度越短越好。
explain select * from emp where ename SIMTH and deptno 10;ref
显示了那些列或常量被用于查找索引列这对于非唯一索引查找有效
explain select * from emp,dept where emp.deptno dept.deptno and emp.deptno 10;rows
根据表的统计信息及索引使用情况大致估算出找出所需记录需要读取的行数此参数很重要直接反应的sql找了多少数据在完成目的的情况下越少越好
explain select * from emp;filtered
表示返回行的预估百分比它显示了哪些行被过滤掉了最大的值为100这意味这没有对行进行筛选从100开始递减的值表示过滤量在增加rows表示预估的行数rows*filtered表示与下表连接的行数
extra
提供查询的额外信息
1、using filesort:说明mysql无法利用索引进行排序只能利用排序算法进行排序会消耗额外的位置
explain select * from emp order by sal;2、using temporary:建立临时表来保存中间结果查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno 10 group by ename;3、using index:这个表示当前的查询时覆盖索引的直接从索引中读取数据而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找如果没有表面索引被用来读取数据而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;4、using where:通常是进行全表或者全索引扫描后再用where子句完成结果过滤需要添加索引
explain select * from emp where jobSMITH;5、using join buffer:使用连接缓存
explain select * from t3 join t2 on t3.c1 t2.c1;6、impossible wherewhere语句的结果总是false
explain select * from emp where 10
