结合网上大神还有自己面试经历,收集的总结Mysql面试题,方便自己准备面试;
mysql一个永远都复习不完,尽量总结,差不多就可以去面试(个人观点)
1、数据库事务的四个特性及含义
- 数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。
- 原子性:整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
- 一致性:在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。
- 隔离性:隔离状态执行事务,使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务,运行在相同的时间内,执行 相同的功能,事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化,为了防止事务操作间的混淆,必须串行化或序列化请 求,使得在同一时间仅有一个请求用于同一数据。
- 持久性:在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。
- 一般也会问事务的四个级别,理解事务并发问题的前提: 在多个事务同时使用相同的数据的时候,可能会出现并发问题。
2、事务的隔离级别
为了平衡事务并发问题的解决与数据库的效率,设计了多种隔离级别:
数据库事务的隔离级别有4个,由低到高依次为Read uncommitted(读未提交)、Read committed(读提交)、Repeatable read(重复读)、Serializable(序列化),这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。
隔离级别 脏读 不可重复读 虚读(幻读) Read uncommitted √ √ √ Read committed × √ √ Repeatable read × × √ Serializable × × × 备注: √: 可能出现 ×: 不会出现
补充:故事
Read uncommitted 读未提交
公司发工资了,领导把5000元打到长贵的账号上,但是该事务并未提交,而长贵的正好去查看账户,发现工资已经到账,是5000元整,非常高兴。可是不幸的是,领导发现发给长贵的工资金额不对,是2000元,于是迅速回滚了事务,修改金额后,将事务提交,最后长贵实际的工资只有2000元,长贵空欢喜一场。
出现上述情况,即我们所说的脏读,两个并发的事务,“事务A:领导给长贵发工资”、“事务B:长贵查询工资账户”,事务B读取了事务A尚未提交的数据。
当隔离级别设置为Read uncommitted时,就可能出现脏读,如何避免脏读,请看下一个隔离级别。
- Read committed 读提交
长贵拿着工资卡去消费,系统读取到卡里确实有2000元,而此时她的老婆谢大脚也正好在网上转账,把谢大脚把工资卡的2000元转到自己的账户,并在长贵之前提交了事务,当长贵扣款时,系统检查到长贵的工资卡已经没有钱,扣款失败,长贵十分纳闷,明明卡里有钱,为何……
出现上述情况,即我们所说的不可重复读,两个并发的事务,“事务A:长贵消费”、“事务B:长贵的老婆谢大脚网上转账”,事务A事先读取了数据,事务B紧接了更新了数据,并提交了事务,而事务A再次读取该数据时,数据已经发生了改变。
当隔离级别设置为Read committed时,避免了脏读,但是可能会造成不可重复读。大多数数据库的默认级别就是Read committed,比如Sql Server , Oracle。如何解决不可重复读这一问题,请看下一个隔离级别。
- Repeatable read 重复读
当隔离级别设置为Repeatable read时,可以避免不可重复读。当长贵拿着工资卡去消费时,一旦系统开始读取工资卡信息(即事务开始),长贵的老婆就不可能对该记录进行修改,也就是长贵的老婆不能在此时转账。
虽然Repeatable read避免了不可重复读,但还有可能出现幻读。
谢大脚查看长贵的工资卡消费记录。有一天,她正在查询到长贵当月信的总消费金额(select sum(amount) from transaction where month = 本月)为80元,而长贵此时正好在外面胡吃海塞后在收银台买单,消费1000元,即新增了一条1000元的消费记录(insert transaction … ),并提交了事务,随后谢大脚将长贵当月消费的明细打印到A4纸上,却发现消费总额为1080元,谢大脚很诧异,以为出现了幻觉,幻读就这样产生了。
简单的说,幻读指当用户读取某一范围的数据行时(不是同一行数据),另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。
Serializable,序列化。最严格的事务隔离。要求事务序列化执行,即一个接一个执行,不能并发执行。不会有任何问题,但是效率太低,实际中,很少会设置这个级别。
3、drop,delete与truncate的区别
- 这个很长,一般只要记住delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。
- TRUNCATE 和DELETE只删除数据, DROP则删除整个表(结构和数据)。
- 速度性能而言,drop > truncate > delete
- 具体链接:http://www.cnblogs.com/8765h/archive/2011/11/25/2374167.html
mysql引擎
- 在MySQL数据库中存在着多种引擎(不同版本的MySQL数据库支持的引擎不同),熟悉各种引擎才能在软件开发中应用引擎,从而开发出高性能的软件
- 链接:http://blog.csdn.net/gaohuanjie/article/details/50944782
- 默认 InnoDB:该存储引擎为MySQL表提供了ACID事务支持、系统崩溃修复能力和多版本并发控制(即MVCC Multi-Version Concurrency Control)的行级锁;该引擎支持自增长列(auto_increment),自增长列的值不能为空,如果在使用的时候为空则自动从现有值开始增值,如果有但是比现在的还大,则直接保存这个值; 该引擎存储引擎支持外键(foreign key) ,外键所在的表称为子表而所依赖的表称为父表。该引擎在5.5后的MySQL数据库中为默认存储引擎。
- 记住两个即可,MyISAM(搜索方面会比较好)
索引方面
- 创建索引的好处:创建唯一性索引,可以保证数据库表中每一行数据的唯一性。加大搜索速度,查询性能提高,提高系统性能
- 缺点:创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
- 索引失效的情况:
- 1.索引不存储null值,更准确的说,单列索引不存储null值,复合索引不存储全为null的值。索引不能存储Null,所以对这列采用is null条件时,因为索引上根本没Null值,不能利用到索引,只能全表扫描。
- 2.不适合键值较少的列(重复数据较多的列),
- 3.前导模糊查询不能利用索引(like ‘%XX’或者like ‘%XX%’)
- 4.索引失效的几种情况
- 1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
- 2.对于多列索引,不是使用的第一部分,则不会使用索引
- 3.like查询以%开头
- 4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
- 5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
- 链接:http://www.cnblogs.com/binyue/p/4058931.html
Mysql性能提升
- sql语句效率提升:太多了,建议把索引失效的几种情况说下,
- 链接:http://www.cnblogs.com/lykbk/p/aefweere45454545454.html
- mysql性能提升:还是太多了,链接:http://www.jianshu.com/p/5dd73a35d70f
- 个人总结:
- 1、搜索引擎的选取,MySQL默认innodb(支持事务),可以选择MYISAM(有b-tree算法查询)还有其他不同引擎
- 2、服务器的硬件提升
- 3、索引方面
- 4、建表的时候尽量使用notnull
- 5、字段尽量固定长度
- 6、垂直分隔(将很多字段多分成几张表),水平分隔(将大数据的表分成几个小的数量级,分成几张表,还可以分开放在几个数据库中,利用集群的思想)
- 7、优化sql语句(查询执行速度比较慢的sql语句))
- 8、添加适当存储过程,触发器,事务等
- 9、表的设计要符合三范式。
- 10、读写分离(主从数据库)