前一阵系统的学习了一下MySQL,也有一些实际操作履历,有时看到一篇和MySQL干系的口试文章,创造个中的一些问题自己也回答不好,虽然知识点大部分都知道,但是无法将知识串联起来.~文章较长须要耐心阅读
因此决定搞一个MySQL灵魂100问,试着用回答问题的办法,让自己对知识点的理解更加深入一点.
此文不会事无年夜小的从select的用法开始讲解mysql,紧张针对的是开拓职员须要知道的一些MySQL的知识点,紧张包括索引,事务,优化等方面,以在口试中高频的问句形式给出答案.如果您有其他的MySQL口试题,以为题目尚有意思或者难度,可以留言
1、什么是索引?
索引是一种数据构造,可以帮助我们快速的进行数据的查找.
2、索引是个什么样的数据构造呢?
索引的数据构造和详细存储引擎的实现有关, 在MySQL中利用较多的索引有Hash索引,B+树索引等,而我们常常利用的InnoDB存储引擎的默认索引实现为:B+树索引.
3、Hash索引和B+树所有有什么差异或者说利害呢?
首先要知道Hash索引和B+树索引的底层实现事理:
hash索引底层便是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询得到实际数据.B+树底层实现是多路平衡查找树.对付每一次的查询都是从根节点出发,查找到叶子节点方可以得到所查键值,然后根据查询判断是否须要回表查询数据.
那么可以看出他们有以下的不同:
hash索引进行等值查询更快(一样平常情形下),但是却无法进行范围查询.由于在hash索引中经由hash函数建立索引之后,索引的顺序与原顺序无法保持同等,不能支持范围查询.而B+树的的所有节点皆遵照(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围.
hash索引不支持利用索引进行排序,事理同上.hash索引不支持模糊查询以及多列索引的最左前缀匹配.事理也是由于hash函数的不可预测.AAAA和AAAAB的索引没有干系性.hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对付所有的查询都是从根节点到叶子节点,且树的高度较低.因此,在大多数情形下,直接选择B+树索引可以得到稳定且较好的查询速率.而不须要利用hash索引.
4、上面提到了B+树在知足聚簇索引和覆盖索引的时候不须要回表查询数据,什么是聚簇索引?
在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这便是聚簇索引和非聚簇索引. 在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引.如果没有唯一键,则隐式的天生一个键来建立聚簇索引.
当查询利用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询.
5、非聚簇索引一定会回表查询吗?
不一定,这涉及到查询语句所哀求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询.
举个大略的例子,假设我们在员工表的年事上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询.
6、在建立索引的时候,都有哪些须要考虑的成分呢?
建立索引的时候一样平常要考虑到字段的利用频率,常常作为条件进行查询的字段比较适宜.如果须要建立联合索引的话,还须要考虑联合索引中的顺序.此外也要考虑其他方面,比如防止过多的所有对表造成太大的压力.这些都和实际的表构造以及查询办法有关.
7。联合索引是什么?为什么须要把稳联合索引中的顺序?
MySQL可以利用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,须要按照建立索引时的字段顺序挨个利用,否则无法命中索引.
详细缘故原由为:
MySQL利用索引时须要索引有序,假设现在建立了\"大众name,age,school\"大众的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序.
当进行查询时,此时索引仅仅按照name严格有序,因此必须首先利用name字段进行等值查询,之后对付匹配到的列而言,其按照age字段严格有序,此时可以利用age字段用做索引查找,,,以此类推.因此在建立联合索引的时候该当把稳索引列的顺序,一样平常情形下,将查询需求频繁或者字段选择性高的列放在前面.此外可以根据特例的查询或者表构造进行单独的调度.
8、创建的索引有没有被利用到?或者说怎么才可以知道这条语句运行很慢的缘故原由?
MySQL供应了explain命令来查看语句的实行操持,MySQL在实行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的剖析,也便是实行操持,个中包含了许多信息. 可以通过个中和索引有关的信息来剖析是否命中了索引,例如possilbe_key,key,key_len等字段,分别解释了此语句可能会利用的索引,实际利用的索引以及利用的索引长度.
9、那么在哪些情形下会发生针对该列创建了索引但是在查询的时候并没有利用呢?
利用不即是查询,列参与了数学运算或者函数在字符串like时左边是通配符.类似于'%aaa'.当mysql剖析全表扫描比利用索引快的时候不该用索引.当利用联合索引,前面一个条件为范围查询,后面的纵然符合最左前缀原则,也无法利用索引.以上情形,MySQL无法利用索引.
事务干系
1、什么是事务?
理解什么是事务最经典的便是转账的栗子,相信大家也都理解,这里就不再说一边了.
事务是一系列的操作,他们要符合ACID特性.最常见的理解便是:事务中的操作要么全部成功,要么全部失落败.但是只是这样还不足的.
2、ACID是什么?可以详细说一下吗?
A=Atomicity
原子性,便是上面说的,要么全部成功,要么全部失落败.不可能只实行一部分操作.
C=Consistency
系统(数据库)总是从一个同等性的状态转移到另一个同等性的状态,不会存在中间状态.
I=Isolation
隔离性: 常日来说:一个事务在完备提交之前,对其他事务是不可见的.把稳前面的常日来说加了赤色,意味着有例外情形.
D=Durability
持久性,一旦事务提交,那么就永久是这样子了,哪怕系统崩溃也不会影响到这个事务的结果.
3、同时有多个事务在进行会怎么样呢?
多事务的并发进行一样平常会造成以下几个问题:
脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.不可重复读: 当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询,结果竟然不一样,由于在此期间B事务进行了提交操作.幻读: A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成\公众幻觉\"大众.4、怎么办理这些问题呢?MySQL的事务隔离级别理解吗?
MySQL的四种隔离级别如下:
未提交读(READ UNCOMMITTED)这便是上面所说的例外情形了,这个隔离级别下,其他事务可以看到本事务没有提交的部分修正.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).
这个级别的性能没有足够大的上风,但是又有很多的问题,因此很少利用.
已提交读(READ COMMITTED)其他事务只能读取到本事务已经提交的部分.这个隔离级别有 不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,由于其余一个事务对数据进行了修正.
REPEATABLE READ(可重复读)可重复读隔离级别办理了上面不可重复读的问题(看名字也知道),但是仍旧有一个新问题,便是 幻读,当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条id=11的数据,由于是新插入的,以是不会触发上面的锁的排斥,那么进行本事务进行下一次的查询时会创造有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.
SERIALIZABLE(可串行化)这是最高的隔离级别,可以办理上面提到的所有问题,由于他逼迫将以是的操作串行实行,这会导致并发性能极速低落,因此也不是很常用.
5、Innodb利用的是哪种隔离级别呢?
InnoDB默认利用的是可重复读隔离级别.
6、对MySQL的锁理解吗?
当数据库有并发事务的时候,可能会产生数据的不一致,这时候须要一些机制来担保访问的次序,锁机制便是这样的一个机制.
就像酒店的房间,如果大家随意进出,就会涌现多人打劫同一个房间的情形,而在房间上装上锁,申请到钥匙的人才可以入住并且将房间锁起来,其他人只有等他利用完毕才可以再次利用.
7、MySQL都有哪些锁呢?像上面那样子进行锁定岂不是有点阻碍并发效率了?
从锁的种别上来讲,有共享锁和排他锁.
共享锁: 又叫做读锁. 当用户要进行数据的读取时,对数据加上共享锁.共享锁可以同时加上多个.
排他锁: 又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁.排他锁只可以加一个,他和其他的排他锁,共享锁都相斥.
用上面的例子来说便是用户的行为有两种,一种是来看房,多个用户一起看房是可以接管的. 一种是真正的入住一晚,在这期间,无论是想入住的还是想看房的都不可以.
锁的粒度取决于详细的存储引擎,InnoDB实现了行级锁,页级锁,表级锁.
他们的加锁开销从大大小,并发能力也是从大到小.
表构造设计
1、为什么要只管即便设定一个主键?
主键是数据库确保数据行在整张表唯一性的保障,纵然业务上本张表没有主键,也建议添加一个自增长的ID列作为主键.设定了主键之后,在后续的编削查的时候可能更加快速以及确保操作数据范围安全.
2、主键利用自增ID还是UUID?
推举利用自增ID,不要利用UUID.
由于在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也便是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只须要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不愿定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的低落.
总之,在数据量大一些的情形下,用自增主键性能会好一些.
图片来源于《高性能MySQL》: 个中默认后缀为利用自增ID,_uuid为利用UUID为主键的测试,测试了插入100w行和300w行的性能.
关于主键是聚簇索引,如果没有主键,InnoDB会选择一个唯一键来作为聚簇索引,如果没有唯一键,会天生一个隐式的主键.
If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.
If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.
3、字段为什么哀求定义为not null?
MySQL官网这样先容:
NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.
null值会占用更多的字节,且会在程序中造成很多与预期不符的情形.
4、如果要存储用户的密码散列,该当利用什么字段进行存储?
密码散列,盐,用户身份证号等固定长度的字符串该当利用char而不是varchar来存储,这样可以节省空间且提高检索效率.
存储引擎干系
1、MySQL支持哪些存储引擎?
MySQL支持多种存储引擎,比如InnoDB,MyISAM,Memory,Archive等等.在大多数的情形下,直接选择利用InnoDB引擎都是最得当的,InnoDB也是MySQL的默认存储引擎.
InnoDB和MyISAM有什么差异?InnoDB支持事物，而MyISAM不支持事物InnoDB支持行级锁，而MyISAM支持表级锁InnoDB支持MVCC, 而MyISAM不支持InnoDB支持外键，而MyISAM不支持InnoDB不支持全文索引，而MyISAM支持。零散问题
1、MySQL中的varchar和char有什么差异.
char是一个定长字段,如果申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用10个字符,而varchar是变长的,也便是说申请的只是最大长度,占用的空间为实际字符长度+1,末了一个字符存储利用了多长的空间.
在检索效率上来讲,char > varchar,因此在利用中,如果确定某个字段的值的长度,可以利用char,否则该当只管即便利用varchar.例如存储用户MD5加密后的密码,则该当利用char.
2、varchar(10)和int(10)代表什么含义?
varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度,而int的10只是代表了展示的长度,不敷10位以0添补.也便是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示.
3、MySQL的binlog有有几种录入格式?分别有什么差异?
有三种格式,statement,row和mixed.
statement模式下,记录单元为语句.即每一个sql造成的影响会记录.由于sql的实行是有高下文的,因此在保存的时候须要保存干系的信息,同时还有一些利用了函数之类的语句无法被记录复制.row级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大.mixed. 一种折中的方案,普通操作利用statement记录,当无法利用statement的时候利用row.此外,新版的MySQL中对row级别也做了一些优化,当表构造发生变革的时候,会记录语句而不是逐行记录.
4、超大分页怎么处理?
超大的分页一样平常从两个方向上来办理.
数据库层面,这也是我们紧张集中关注的(虽然奏效没那么大),类似于select from table where age > 20 limit 1000000,10这种查询实在也是有可以优化的余地的. 这条语句须要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修正为select from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,以是速率会很快. 同时如果ID连续的好,我们还可以select from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,便是减少load的数据.从需求的角度减少这种要求….紧张是不做类似的需求(直接跳转到几百万页之后的详细某一页.只许可逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄露且连续被人恶意攻击.办理超大分页,实在紧张是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.
在阿里巴巴《Java开拓手册》中,对超大分页的办理办法是类似于上面提到的第一种.
5、关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?
在业务系统中,除了利用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计紧张由运维在做,会定期将业务中的慢查询反馈给我们.
慢查询的优化首先要搞明白慢的缘故原由是什么? 是查询条件没有命中索引?是load了不须要的数据列?还是数据量太大?
以是优化也是针对这三个方向来的,
首先剖析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不须要的列,对语句进行剖析以及重写.剖析语句的实行操持,然后得到其利用索引的情形,之后修正语句或者修正索引,使得语句可以尽可能的命中索引.如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.6、上面提到横向分表和纵向分表,可以分别举一个适宜他们的例子吗?
横向分表是按行分表.假设我们有一张用户表,主键是自增ID且同时是用户的ID.数据量较大,有1亿多条,那么此时放在一张表里的查询效果就不太空想.我们可以根据主键ID进行分表,无论是按尾号分,或者按ID的区间分都是可以的. 假设按照尾号0-99分为100个表,那么每张表中的数据就仅有100w.这时的查询效率无疑是可以知足哀求的.
纵向分表是按列分表.假设我们现在有一张文章表.包含字段id-择要-内容.而系统中的展示形式是刷新出一个列表,列表中仅包含标题和择要,当用户点击某篇文章进入详情时才须要正文内容.此时,如果数据量大,将内容这个很大且不常常利用的列放在一起会拖慢原表的查询速率.我们可以将上面的表分为两张.id-择要,id-内容.当用户点击详情,那主键再来取一次内容即可.而增加的存储量只是很小的主键字段.代价很小.
当然,分表实在和业务的关联度很高,在分表之前一定要做好调研以及benchmark.不要按照自己的猜想盲目操作.
7、什么是存储过程？有哪些优缺陷？
存储过程是一些预编译的SQL语句。1、更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增编削查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。2、存储过程是一个预编译的代码块，实行效率比较高,一个存储过程替代大量T_SQL语句 ，可以降落网络通信量，提高通信速率,可以一定程度上确保数据安全
但是,在互联网项目中,实在是不太推举存储过程的,比较出名的便是阿里的《Java开拓手册》中禁止利用存储过程,我个人的理解是,在互联网项目中,迭代太快,项目的生命周期也比较短,职员流动比较于传统的项目也更加频繁,在这样的情形下,存储过程的管理确实是没有那么方便,同时,复用性也没有写在做事层那么好.
8、说一说三个范式
第一范式: 每个列都不可以再拆分. 第二范式: 非主键列完备依赖于主键,而不能是依赖于主键的一部分. 第三范式: 非主键列只依赖于主键,不依赖于其他非主键.
在设计数据库构造的时候,要只管即便遵守三范式,如果不遵守,必须有足够的情由.比如性能. 事实上我们常常会为了性能而妥协数据库的设计.
9、MyBatis中的#
乱入了一个奇怪的问题…..我只是想单独记录一下这个问题,由于涌现频率太高了.
# 会将传入的内容当做字符串,而有什么差异?∗∗乱入了一个奇怪的问题.....我只是想单独记录一下这个问题,由于涌现频率太高了.#会将传入的内容当做字符串,而会直接将传入值拼接在sql语句中.
以是#可以在一定程度上预防sql注入攻击.
