为什么要使用数据库?
数据如果保存在内存:存取速度快,但数据不能永久保存
数据保存在文件:数据永久保存,速度比内存操作慢,频繁IO,查询数据也不方便
数据保存在数据库:数据永久保存,使用sql语句查询很方便,管理数据方便
数据库的三大范式?
列不可分,非主键列完全依赖于主键,非主键列不依赖于其他非主键,这三大范式是递进关系,比如第二范式需要在第一范式的基础上
MyISAM对于Innodb引擎的区别?
| MyISAM | Innodb | |
| 存储空间 | 可以被压缩,存储空间小 | 需要更多的内存 |
| 可移植性 | 以文件形式存储,在跨平台中转移数据方便 | 需要拷贝数据文件,备份binlog等 |
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 哈希索引 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持(5.6以后支持) |
| 索引实现方式 | B+,非聚簇索引 | B+,聚簇索引 |
| 锁 | 表锁 | 表锁和行锁 |
| select语句/count操作 | 更高效(count维护了一个计数器) | |
| insert,update,delete | 更高效 |
MyISAM:适用于以读写插入为主的应用程序,比如博客系统
Innodb:更新(删除)操作频率高,或者要保证数据完整性,并发量高,支持事务和外键,比如电商后台
MyISAM和InnoDB索引的区别?
- InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
- InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
- MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
- InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引(即遵循最左查询原则)会非常高效。
索引是什么?优缺点?使用场景?
索引是特殊的文件,包含着对数据表里所有记录的引用指针,相当于目录
优点:加快数据检索速度,在查询过程中,提高性能
缺点:创建和维护索引需要耗费时间,具体的,当对表中数据进行增加,删除和修改的时候,索引也需要动态的维护,降低了效率;索引还需要占用额外的空间
使用场景:
- where语句中使用的列
- order by使用的列
- join连接 on所使用的列
- 覆盖索引
索引的类型?
- 主键索引:列不重复且不为null,只能有一个
- 唯一索引:列不重复,但允许为null,可以有多个
- 普通索引:列可重复,允许为null,可以多个
- 全文索引:是搜索引擎使用的关键技术
索引的数据结构?
B树索引(实际是使用b+树实现):主键索引时,一次检索直接拿到数据,非普通索引查询时,拿到主键id,再拿到具体数据(二次检索)
哈希索引:使用hash算法,将数据库字段数据转换成定长的hash值,使用拉链法解决hash冲突,只能进行等值比较,不能使用like,=,>=等符号,但是等值比较的检索效率远远大于b树索引
创建索引的原则?
- 最左前缀匹配原则:带头大哥不能死,中间兄弟不能断,范围查询后面的全部寄
- 比较频繁查询的字段才创建索引
- 更新频繁的字段不适合创建索引
- 不能有效区分数据的列,比如性别不创建索引
- 尽量扩展索引,而不是新建索引,比如表中有a的索引,现在要加(a,b)索引,那么只需要修改之前的索引
- 定义有外键的数据列一定要建立索引
- 对于定义为text,image,bit的类型数据列不要建立索引
最左前缀:并不是说对于(a,b,c)复合索引,一定要使用where a=1 and b=2 and c=3,where中a,b,c三个位置交换也没有问题,因为sql查询优化器会将我们的语句优化
为什么数据库使用b+树而不是b树?
b树只适合随机检索,而b+树支持随机检索和顺序检索
b+树空间利用率高,可以减少io次数,磁盘读写代价更低,因为它的内部节点作为索引使用,使得其能容纳的节点中的关键字数量更多
b+树的查询效率更加稳定,因为所有的数据都要在叶子节点才能找到
b+树的叶子节点包含所有关键字,并以有序链表结构存储,可以提高增删效率
b+树对于范围查询,只需找到范围的一边,然后进行有序遍历链表即可
聚簇索引和非聚簇索引?非聚簇索引是否一定要回表查询?
聚簇索引:数据和索引放一起,找到了索引也就找到了数据
非聚簇索引:数据和索引分开,索引结构的叶子节点指向了数据对应行
在innodb中,在聚簇索引之上创建的索引叫辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引,唯一索引这些,存储的不是行的物理位置,而是主键的值
非聚簇索引不一定会回表查询,如果查询语句要求的字段都是索引的内容,就不必要再进行回表查询例如select age from employee where age < 20;
mysql中in和exists的区别?
in语句把外表和内标当做hash连接,exists语句对外表循环,每次循环再对内表进行查询
如果两个表大小相当,那么使用两者差别不大
如果两个表中一个小,一个大,此时子查询表大的使用exists,小的使用in
not in和not exists:如果查询语句使用了not in,那么内外表都进行全表扫描,没有用到索引,not exists的子查询可以用到表上的索引,所以无论哪个表大not exists都比not in要快
varchar 和char 的区别?
char表示定长字符串,长度固定,varchar长度可变
char中插入的数据长度小于其固定长度时,使用空格填充
char存取速度很快,varchar则慢一些
char能存放的字符数为255,varchar最多存放字符数为65532
varchar(50)中50的含义?int(20)中20的含义
最多存放50个字符,显示字符的长度为20
drop,delete,truncate的区别
drop:把表连同数据全部删除,不可回滚,速度最快
truncate:表结构依然存在,删除表中所有数据
delete:删除数据行,可以回滚,速度最慢
where字句的优化?
对查询进行优化,应该避免全表扫描,首先考虑在where和order by涉及的列上建立索引
避免where字句中的null值判断,引擎会放弃使用索引进行全表扫描
避免在where字句中使用或<>操作符,会进行全表扫描
避免在where字句中使用or来连接条件,会进行全表扫描
在连续少量数据的时候,能不用in与not in就不用比如in (1,2,3)这样的条件
类似于like "%吴%"这样的条件也会导致全表扫描
where语句中使用参数会导致全表扫描
where语句的=号左边使用函数,算数符号操作会导致无法正确使用索引
大表优化?垂直分区和水平分区?
- 将字段很多的表分解成多个表
- 增加中间表用于联合查询
- 增加冗余字段,提高查询字段
垂直分区:根据数据库里数据表的相关性进行拆分,它的作用是使得行数据变小,在查询的时候减少IO次数,简化表的结构,易于维护
?水平分区:保持表结构不变,通过某种策略存储数据分片,仅解决了单一表数据过大的问题,对于mysql的并发能力没有什么意义,当进行水平拆分的时候最好的做法是分库
