数据库面试题

1、mmsql分页的方法、
Row_Number Over()
top not in
top except top (except mssql 2005以上才支持) 取差集
最后如果可以在sql的基础上机上主键进行查询，就不用查询那么多页


2、触发器类型：
行级触发器：包含有for each row的就是行级触发器
加for each row是为了使用:old或者:new对象或者一行记录
语句级触发器：不包含有for each row的就是语句级触发器

3、事务四大特性
原子性，要么执行，要么不执行
隔离性，所有操作全部执行完以前其它会话不能看到过程
一致性，事务前后，数据总额一致
持久性，一旦事务提交，对数据的改变就是永久的

4、数据库隔离级别
read uncommitted:读未提交
产生问题：脏读、虚读、幻读
read committed：读已提交 （Oracle默认）
产生问题：虚读、幻读
repeatable read:可重复读（mysql默认）
产生问题：幻读
serializable：串行化
可以解决所有的问题

5、幻读产生的原因
行锁只能锁住行，即使把所有的行记录都上锁，也阻止不了新插入的记录。

6、mmsql如何当读取更快
在查询的表后面加上with (nolock)

7、mysql的存储引擎类型
MYISAM 不支持外键，不支持事务，表锁，插入数据时，锁定整个表，查表总行数时，不需要全表扫描，支持拷贝文件到新系统上就能运行
INNODB 支持外键，行锁，事务，查表总行数时，全表扫描

8、InnoDB索引和MyISAM索引的区别
一是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主

9、索引的底层实现（B+树，为何不采用红黑树，B树）重点
红黑树 增加，删除，红黑树会进行频繁的调整，来保证红黑树的性质，浪费时间
B树也就是B-树 B树，查询性能不稳定，查询结果高度不致，每个结点保存指向真实数据的指针，相比B+树每一层每屋存储的元素更多，显得更高一点。
B+树 B+树相比较于另外两种树,显得更矮更宽，查询层次更浅

10、为什么使用B+Tree
索引查找过程中就要产生磁盘I/O消耗,主要看IO次数，和磁盘存取原理有关。
根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，
将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入
局部性原理与磁盘预读

11、如何查找慢sql
开启慢日志


12、如何通过慢查日志发现有问题的SQL
1.查询次数多且每次查询占用时间长的sql，通常为pt-query-digest分析的前几个查询；该工具可以很清楚的看出每个SQL执行的次数及百分比等信息，执行的次数多，占比比较大的SQL
2.IO大的sql，注意pt-query-digest分析中的Rows examine项。扫描的行数越多，IO越大。
3.未命中的索引的SQL

13、mysql如何查询执行计划
explain
14、索引分类
普通索引 最基本的索引，没有任何限制
唯一索引 与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引 它是一种特殊的唯一索引，不允许有空值。
全文索引 针对较大的数据，生成全文索引很耗时好空间。
组合索引 为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则

15、数据库中join的left join , inner join, cross join,full join
1.以A，B两张表为例
A left join B
选出A的所有记录，B表中没有的以null 代替
right join 同理
2.inner join
A,B有交集的记录
3.cross join (笛卡尔积)
A中的每一条记录和B中的每一条记录生成一条记录
例如A中有4条，B中有4条，cross join 就有16条记录
4、全外连接full [outer] join
显示符合条件的数据行，同时显示左右不符合条件的数据行，相应的左右两边显示NULL，即显示左连接、右连接和内连接的并集

16、sql有哪些锁,select时怎么加排它锁
锁 概念
乐观锁 自己实现，通过版本号
悲观锁 共享锁，多个事务，只能读不能写，加 lock in share mode
排它锁 一个事务，只能写，for update
行锁 作用于数据行
表锁 作于用表

17、左匹配原则
举例来说：两个字段（name,age）建立联合索引，如果where age=12这样的话，是没有利用到索引的，
这里我们可以简单的理解为先是对name字段的值排序，然后对age的数据排序，如果直接查age的话，这时就没有利用到索引了，
查询条件where name=‘xxx’ and age=xx 这时的话，就利用到索引了，再来思考下where age=xx and name=’xxx‘ 这个sql会利用索引吗，
按照正常的原则来讲是不会利用到的，但是优化器会进行优化，把位置交换下。这个sql也能利用到索引了

18、sql常见命令

DEFAULT 0 默认值
ADD PRIMARY KEY (column_list) 主键
ADD UNIQUE (column_list) 唯一索引
ADD INDEX index_name (column_list) 普通索引
foreign key 外键(alter table tablename1 add foreign key tablename1(字段名) references tablename2(字段名) )
procedure 存储过程
trigger 触发器
FUNCTION 函数
grant 授权

19、查询语句or的优化
用多个查询结果，再用union 进行连接，如果是count()带有or的查询语句的话，则分成多列进行查询，如
select count(release_year='2006' or null) as '06films',count(release_year='2007' or null) as '07films' from film;
20、子查询优化
换成exist，或者是用join进行连接判断试试

21、mysql如何查找重复索引
工具：使用pt-duplicate-key-checker工具检查重复及冗余索引

22、如何索引维护
尽量逻辑删除数据，如果经常要物理删除数据的话，要定期索引重建

23、触发器
插入的触发器
create trigger tgr_classes_insert
on table_name
for insert --插入触发
as
Transact-SQL
go
更新的触发器
create trigger tgr_name
on table_name
with encrypion –加密触发器
for update...
as
Transact-SQL
go
create trigger tgr_classes_insert
on table_name
for delete --删除触发
as
Transact-SQL
go

24、常见6种约束 (Constraints)
NOT NULL 不为null
UNIQUE 唯一
PRIMARY KEY 主键
FOREIGN KEY 外键
CHECK 校验
DEFAULT 默认


25、MySQL执行引擎介绍（了解）
1、MyISAM存储引擎
不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表
支持3种不同的存储格式，分别是：静态表；动态表；压缩表
静态表：
表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）ps：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。
动态表：
记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能
压缩表：
因为每个记录是被单独压缩的，所以只有非常小的访问开支
2、InnoDB存储引擎
该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。
InnoDB存储引擎的特点：支持自动增长列，支持外键约束
3、MEMORY存储引擎
Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。
MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围
Hash索引优点：
Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
Hash索引缺点：
那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；
Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果，。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。
4、MERGE存储引擎
Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。


26、聚集索引与非聚集索引
聚集索引（clustered）：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。 主键索引
非聚集索引(unclustered)：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。 普通索引，唯一索引，全文索引