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+树的索引中，叶子节点可能存储了当前的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在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引。例如possible_key,key,key_len等字段，分别说明了此语句可能会使用的索引，实际使用的索引，以及使用的索引的长度。

9.什么情况下针对该列创建了索引但是查询的时候没有使用？

• 使用了不等于查询
• 列参与了数学运算或者函数
• 在字符串like时左边是通配符，类似于”%aaa“
• 当全表分析扫描比使用索引快的时候不使用索引
• 当使用联合查询，前面一个条件为范围查询，后面的即使符合最左前缀原则，也不使用索引

事务

10.什么是事务？

事务是一系列的操作，要符合ACID特性，经典的事务就是转账的那个例子。

11.ACID特性是什么？

A=Atomicity
原子性：要么全部成功，要么全部失败，不可能只执行一部分。
C=consistency
一致性：系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态，不会存在中间状态。
I=Isolation
隔离性：通常来说，一个事务在完全提交之前，对其他事务是不可见的。一般情况是这样，不过有特殊情况。
D=Durability
持久性：一旦事务提交，就永远是这个样子，哪怕之后系统崩溃也不会影响这次事务的结果。

12.同时有多个事务在进行会怎么样？

多事务的并发进行一般会造成以下几个问题:

• 脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.
• 不可重复读: 当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询,结果竟然不一样,因为在此期间B事务进行了提交操作.
• 幻读: A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成"幻觉".

13.如何解决事务并发的问题？MySql事务的隔离级别？

MySQL的四种隔离级别如下:

• 未提交读(READ UNCOMMITTED)

这就是上面所说的例外情况了,这个隔离级别下,其他事务可以看到本事务没有提交的部分修改.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).

这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用.

• 已提交读(READ COMMITTED)

其他事务只能读取到本事务已经提交的部分.这个隔离级别有 不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改.

• REPEATABLE READ(可重复读)

可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是 幻读,当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥,那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.

• SERIALIZABLE(可串行化)

这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.

14.Innodb使用的是哪种隔离级别？

InnoDB默认使用的是可重复读隔离级别.

15.Mysql有哪些锁？

从锁的类别上来讲,有共享锁和排他锁.

• 共享锁: 又叫做读锁. 当用户要进行数据的读取时,对数据加上共享锁.共享锁可以同时加上多个.
• 排他锁: 又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁.排他锁只可以加一个,他和其他的排他锁,共享锁都相斥.

锁的粒度取决于具体的存储引擎,InnoDB实现了行级锁,页级锁,表级锁.
他们的加锁开销从大大小,并发能力也是从大到小.

表结构设计

16.为什么要尽量设计一个主键？

主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的ID列作为主键.设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全.

17.主键使用自增ID还是UUID？

推荐使用自增ID,不要使用UUID.

因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降.

总之,在数据量大一些的情况下,用自增主键性能会好一些.

关于主键是聚簇索引,如果没有主键,InnoDB会选择一个唯一键来作为聚簇索引,如果没有唯一键,会生成一个隐式的主键.

18.字段为什么要求定义为not null？

null值会占用更多的字节,且会在程序中造成很多与预期不符的情况.

19.如果要存储用户的密码散列,应该使用什么字段进行存储?

密码散列,盐,用户身份证号等固定长度的字符串应该使用char而不是varchar来存储,这样可以节省空间且提高检索效率.

存储引擎

20.MySQL支持哪些存储引擎?

MySQL支持多种存储引擎,比如InnoDB,MyISAM,Memory,Archive等等.在大多数的情况下,直接选择使用InnoDB引擎都是最合适的,InnoDB也是MySQL的默认存储引擎.

21.InnoDB和MyISAM有什么区别？

• InnoDB支持事物，而MyISAM不支持事物
• InnoDB支持行级锁，而MyISAM支持表级锁
• InnoDB支持MVCC, 而MyISAM不支持
• InnoDB支持外键，而MyISAM不支持
• InnoDB不支持全文索引，而MyISAM支持。

其他

22.MySQL中的varchar和char有什么区别？

char是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用10个字符,而varchar是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间.

在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char.

23. varchar(10)和int(10)代表什么含义?

varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度,而int的10只是代表了展示的长度,不足10位以0填充.也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示.

24.MySQL的binlog有有几种录入格式?分别有什么区别?

有三种格式,statement,row和mixed.

statement模式下,记录单元为语句.即每一个sql造成的影响会记录.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制.

row级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大.

mixed. 一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row.

此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录.

25.超大分页怎么处理?

超大的分页一般从两个方向上来解决.

数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于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泄漏且连续被人恶意攻击.

26.什么是慢查询？慢查询如何优化？

• 首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写.
• 分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引.
• 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.

27.举一个横向分表和纵向分表的例子？

横向分表是按行分表.假设我们有一张用户表,主键是自增ID且同时是用户的ID.数据量较大,有1亿多条,那么此时放在一张表里的查询效果就不太理想.我们可以根据主键ID进行分表,无论是按尾号分,或者按ID的区间分都是可以的. 假设按照尾号0-99分为100个表,那么每张表中的数据就仅有100w.这时的查询效率无疑是可以满足要求的.

纵向分表是按列分表.假设我们现在有一张文章表.包含字段id-摘要-内容.而系统中的展示形式是刷新出一个列表,列表中仅包含标题和摘要,当用户点击某篇文章进入详情时才需要正文内容.此时,如果数据量大,将内容这个很大且不经常使用的列放在一起会拖慢原表的查询速度.我们可以将上面的表分为两张.id-摘要,id-内容.当用户点击详情,那主键再来取一次内容即可.而增加的存储量只是很小的主键字段.代价很小.

28.什么是存储过程？有哪些优缺点？

存储过程是一些预编译的SQL语句。1、更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。2、存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全

但是,在互联网项目中,其实是不太推荐存储过程的,比较出名的就是阿里的《Java开发手册》中禁止使用存储过程,我个人的理解是,在互联网项目中,迭代太快,项目的生命周期也比较短,人员流动相比于传统的项目也更加频繁,在这样的情况下,存储过程的管理确实是没有那么方便,同时,复用性也没有写在服务层那么好.

29.三个范式

第一范式: 每个列都不可以再拆分. 第二范式: 非主键列完全依赖于主键,而不能是依赖于主键的一部分. 第三范式: 非主键列只依赖于主键,不依赖于其他非主键.

在设计数据库结构的时候,要尽量遵守三范式,如果不遵守,必须有足够的理由.比如性能. 事实上我们经常会为了性能而妥协数据库的设计.