数据库面试常考题
文章目录
- 数据库面试常考题
-
- **一、 left join, right join, inner join区别?**
- **二、 存储引擎MyIsam和Innodb区别?**
- **三、 mysql的优化手段有哪些?**
- **四、 如何查看Mysql执行计划?**
- **五、 索引是什么? 有什么用? 如何建立? 索引的底层实现是什么? 什么情况下适合建立索引, 什么情况下不适合建立索引?**
- **六、 什么是存储过程?有什么好处?**
- **七、 乐观锁和悲观锁是什么?**
- **八、 表锁、页锁、行锁的区别?**
- **九、 having和where的区别?**
- **十一、 事务的特点有哪些?**
- **十二、 触发器是什么?**
- **十三、 为什么大部分数据库索引的实现使用B+树而不是哈希表或红黑树之类的?**
一、 left join, right join, inner join区别?
- left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
- right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
- inner join(等值连接) 只返回两个表中联结字段相等的行
注意:在某些数据库中left join等同于left outer join
举个例子:
表A记录如下
aID | aNum |
---|---|
1 | a20050111 |
2 | a20050112 |
3 | a20050113 |
4 | a20050114 |
5 | a20050115 |
表B记录如下 |bID| bName | |:--|:--| | 1 | 2006032401 | | 2 | 2006032402 | | 3 | 2006032403 | | 4 | 2006032404 | | 8 | 2006032408 |
1、sql语句如下:
select * from A
left join B
on A.aID = B.bID
结果如下: |aID| aNum |bID| bName | |:--|:--|:--|:--| | 1 | a20050111 | 1 | 2006032401 | | 2 | a20050112 | 2 | 2006032402 | | 3 | a20050113 | 3 | 2006032403 | | 4 | a20050114 | 4 | 2006032404 | | 5 | a20050115 | NULL| NULL |
(所影响的行数为5行) **结果说明:** left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的。换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为:A.aID = B.bID)。B表记录不足的地方均为NULL。
2、sql语句如下:
select * from A
right join B
on A.aID =B.bID
结果如下: |aID| aNum |bID| bName | |:--|:--|:--|:--| | 1 | a20050111 | 1 | 2006032401 | | 2 | a20050112 | 2 | 2006032402 | | 3 | a20050113 | 3 | 2006032403 | | 4 | a20050114 | 4 | 2006032404 | | NULL | NULL | 8| 2006032408 |
(所影响的行数为5行) **结果说明**: 仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充。
3、sql语句如下: ``` select * from A inner join B on A.aID = B.bID ```
结果如下: | aID | aNum | bID | bName | |:--|:--|:--|:--| | 1 | a20050111 | 1 | 2006032401 | | 2 | a20050112 | 2 | 2006032402 | | 3 | a20050113 | 3 | 2006032403 | | 4 | a20050114 | 4 | 2006032404 |
结果说明: 很明显,这里只显示出了A.aID = B.bID的记录,这说明inner join并不以谁为基础,它只显示符合条件的记录。
二、 存储引擎MyIsam和Innodb区别?
(1)MyIsam类型不支持事务处理等高级处理,而Innodb类型支持
事务处理是指原子性操作。例如,支持事务处理的Innodb表中,你发了一个帖子执行insert语句,来插入帖子内容,插入后要执行一个update语句来增加你的积分。假设一种特殊情况突然发生,insert成功了,update操作却没有被执行。也就是说你发了帖子却没有增加相应的积分。这就会造成用户不满。如果使用了事务处理,insert和update都放入到事务中去执行,这个时候,只有当insert和update两条语句都执行生成的时候才会将数据更新、写入到表中。如果其中任何一条语句失败,那么就会回滚为初始状态,不执行写入。这样就保证了insert和update肯定是一同执行的。
(2)MyIsam表不支持外键。innodb支持外键
(3)在执行数据写入的操作(insert,update,delete)的时候,MyIsam表会锁表,而innodb表会锁行。
通俗的讲,就是你执行一个update语句,那么MyIsam表会将整个表都锁住,其他的insert和delete、update都会被拒之门外,等到这个update语句执行完成后才会被依次执行。而锁行,就是说,你执行update语句时,只会将这一条记录进行锁定,只有针对这条记录的其他写入、更新操作会被阻塞并等待这条update语句执行完毕后再执行,针对其他记录的写入操作不会有影响。但是innodb表的行锁也不是绝对的,如果在执行一个sql语句时,mysql不能确定要扫描的范围,innodb表同样会锁全表,例如update table set num =1 where name like “%aaa%”
(4)表的具体行数
select count(*) from table , MyIsam只要简单的读出保存好的行数,注意的是,当count(*)语句包含where条件时,两种表的操作是一样的。
innodb中不保存表的具体行数,也就是说,执行select count(*) from table时,innodb要扫描一遍整个表来计算有多少行。
总结:
因此,当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择innodb表。当你的数据库主要以查询为主,相比较而言更新和写入比较少,并且业务方面数据完整性要求不那么严格,就选择myisam表。因为MyIsam表的查询操作效率和速度都比innodb要快。
InnoDB的设计目标是处理大容量数据库系统,它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。
我觉得使用InnoDB可以应对更为复杂的情况,特别是对并发的处理要比MyISAM高效。同时结合memcache也可以缓存SELECT来减少SELECT查询,从而提高整体性能。
使用以下mysql sql语句,可以给表设定数据库引擎:
ALTER TABLE `wp_posts` ENGINE = MyISAM;
查看当前数据库的引擎,可看出默认是InnoDB类型。
![](https://imgconvert.csdnimg.cn/aHR0cDovL2ltZy5ibG9nLmNzZG4ubmV0LzIwMTcwNDMwMjA1MDM5NTU2?x-oss-process=image/format,png)
三、 mysql的优化手段有哪些?
MYSQL数据库优化的八种方式:
(1)选取最适用的字段属性
MYSQL可以很好的支持大数据量的存取,但是一般来说,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。
例如,在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数据库增加了不必要的空间,甚至使用VARCHAR这种类型也是多余的,因为CHAR(6)就可以很好的完成任务了。同样的,如果可以的话,我们应该使用MEDIUMINT而不是BIGINT来定义整型字段。
另外一个提高效率的方法是在可能的情况下,应该尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值。
对于某些文本字段,例如“省份”或者“性别”,我们可以将它们定义为ENUM类型。因为在MYSQL中,ENUM类型被当做数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快得多。这样,我们又可以提高数据库的性能。
(2)使用连接(JOIN)来代替子查询(Sub-Queries)
例如:我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询先从销售信息表中