学习总结：数据库-索引，引擎，SQL语句

• 数据库中，mysql用到哪些引擎？区别？

InnoDB MyISAM

事务：Innodb支持事务，MyISAM不支持。

索引：Innodb使用聚集索引，MYISAM使用非聚集索引。Innodb不支持全文索引，Myisam支持全文索引，所以查询效率比较高，适合那种增删改不怎么频繁，但是查询频繁的场景。

行数：Innodb不保存表的具体行数，MyISAM保存了表的行数

外键：Innodb支持外键，而MyISAM不支持

 

 

•  数据库索引及原理

索引是满足某种特定查找算法的数据结构，可以快速的查找数据。

 

•  常见的索引结构。

二分查找树，B Tree，B+ Tree，Hash

 

• mysql 索引类别

B-Tree Hash Full-text

 

• 索引的优点和缺点

优点：可以快速的查找数据

缺点：

1 索引会占用磁盘空间

2 对于需要增删改的操作，索引会降低它们的速度。

 

• 建立过多的索引有什么负面影响

1 会占用大量的磁盘空间

2 数据库变更也需要维护索引，所以会需要更多的维护成本

 

 

• 优化数据库的方法，从sql到缓存到cpu到操作系统。

SQL

1 建立索引

2 优化查询语句顺序

3 分表分库

4 选择正确的存储引擎

缓存

1.使用缓存，连接池。

操作系统

1.服务器调优及各个参数设置（调整my.cnf）

 

 

• sql如何优化

1 建立索引

2 优化查询语句顺序

3 垂直分割分表

4 选择正确的存储引擎

 

 

•  如何解决慢查询 一个服务有多条SQL怎么快速定位？

1.根据慢日志定位慢查询SQL

2.使用Explain等工具分析SQL，比如说是否使用的是临时表（一般是order by和group by的时候会出现），是否使用的是外部索引排序（一般文件排序的时候会出现）。

3. 修改SQL或者让SQL走索引。

 

 

• explain会看哪几个参数

key 实际使用的索引

type 查看连接使用了什么类型 一般如果是index 和all 就是比较差的

extra 查看一些额外信息 是否出现filesort或者temporary

 

 

• .什么情景下做分表，什么情景下做分库

数据库分表可以解决单表海量数据的查询问题，分库可以解决单台数据库的并发访问压力。

 

• 怎样分表分库？举例说明如何分表？

取模进行路由计算 比如100张表，用户ID为101，取模后就放进第一张表。

 

• .当数据库访问量过大时，怎么办？分库分表，什么时候垂直拆分，什么时候水平拆分

业务拆分，主从复制，分库分表。

https://blog.csdn.net/xlgen157387/article/details/53976153 数据库分库分表策略的具体实现方案

 

• 聚簇索引和非聚簇索引的区别？

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点还是索引节点，只不过有指向对应数据块的指针。

 

•  innodb中聚集索引，非聚集索引？myisam中的索引？

Innodb是聚簇索引，myisam是非聚簇索引

 

• mysql怎么分页，写下命令

limit

• limit的使用

可以获取前几条或者几条记录

也可以进行分页

 

 

 

• 什么是最左前缀匹配原则

一条SQL语句，mysql会一直向右匹配直到遇到范围查询（>,<,between,like)就停止匹配。

比如说 a=3 and b=4 and c>5 and d=6 如果建立（a,b,c,d)顺序的索引，d是用不到索引的，但如果建立的是（a,b,d,c）的索引就可以用到，并且abd可以调整顺序。

 

 

• MySQL的问题，面试官写了一条语句，关键部位".……where a=...and b=... and c=..."，给你两个联合索引是a，c和b，c。

问1：说下两个联合索引的使用情况。

问2：select a和select d和select b，c在上述两个联合索引的使用下，查询性能怎样

 

 

• 建立联合索引a,b,c，请问查询条件为a,c是否使用索引？a,b是否使用索引？b,a,c是否使用索引？

ac不会用

ab会用

bac 如果是= in就可以使用

 

 

• 数据库索引，分别给abc  a创建索引，select a  索引能用上吗

select abc from table group by a索引能用上吗

能用

能

 

• 用abc三个列作为索引 select * where b=1 and c=1 如何运行的

没有使用索引

• 用abc三个列作为索引，where a=1 or group by c会不会走索引

不会

• 用abc三个列作为索引 select b where c = 1 会不会用到辅助索引，用到怎么样 不用的又怎么样

要建一个辅助索引a

 

• mysql怎么做到联合索引的

b+树

 

• 创建索引和使用索引注意事项

1 数据量少的字段不需要加索引，因为建索引有开销

2 需要加索引的字段需要在where条件中

3 建联合索引比建立单个索引更有优势，会节省空间。

 

 

• 为什么使用索引查数据更快？什么时候使用索引查数据比全表扫描还更慢？使用什么关键字会触发全表扫描？

因为索引就是事先排好序，查找的时候会按照特定的高效率算法。

 

• 为什么选用自增量作为主键索引

节省空间

如果使用非单调的索引，会造成B+tree频繁分裂调整，降低效率

 

 

• 哪些情况会引起索引的失效

没有建立索引

遇到or

字符串没有使用引号

遇到匹配范围 如（> < between like）

没有遵循最左前缀匹配原则

mysql预估全表扫描会更快

 

 

•  b树和b+树的区别

b树下面的节点存储的是数据的指针，而b+树下面节点存储的是数据，这样可以使树矮一点，查询速度更快。

 

•  hash索引和b树索引的原理，区别，适用场景，优缺点，实现。

b数是下面的节点存储数据的指针，而hash索引是存储每行每列的hash值

访问hash索引数据是比b+tree树快的，但是他只适用一些特殊的场景，有一些缺点。

比如说指那个满足“=”，“in”，不能使用范围查询。

比如不能进行排序

比如不能使用部分索引查找。

还有如果出现hash冲突很多的话，效率也不是很高。

 

• 怎么用原生的JDBC访问数据库；

加载驱动，然后get connection

 

• Statement和PreparedStatement的区别

prestatement可以使用占位符

 

• 数据库的触发器说一下

类型

DML 数据操纵语言 insert delete update 比如说在学生表删除一个学生，成绩表也会删除这个学生的相关信息

DDL 数据定义语言

 

• left 与inner 区别

left 返回左表中所有记录以及右表中联结字段相等的记录

inner 只返回两个表中联结字段相等的记录

 

 

• 数据库的三大范式

第一范式 关系中的每个属性值都是不可再分的最小数据单位

第二范式 建立在第一范式的基础上，确保数据表中的每一列都和主键相关

第三范式 建立在第二范式的基础上，确保数据表中的每一列都和主键直接相关

 

 

• 两种写法实现查询ID在A表但不在B表

select ID from A where ID not in（select ID from B）

 

 

• 写sql，一张表三个字段，学生sid，课程cid，成绩，查询每一个学生得分最高的那门课

select cid from a

group by sid

order by grade

limit 1

 

• sql命令  查询平均分在60以上的人 且按序排列

select sid from a

where avg(score)>60

order by avg(score) desc

• 手写sql输出姓名叫张三，出生日期在1996-1998之间人的平均年龄

select avg(age)

from a

where age in (select age from a where a.name="张三")

 

• 写SQL语句，只显示重复的数据

select distinct sid form a

 

• 给一个数据库表，ID、score两个字段分别代表学生ID和成绩，写SQL语句求ID=？的学生排第几名？

select count(1)

from a

group by score having(score > ID.score)

 

 

• 显示热门评论，按赞数倒序排序，写SQL

select comment from a order by like desc