- group by 分组
- 与聚合函数一同使用
- 按多个字段分组
- 与having一起使用,having相当于where,where不能与聚合函数联合使用
- where group by having使用是有顺序的
- 不支持group by all
- order by
- 接多个字段,靠前字段优先
- 与group 联合使用时,order by 字段应出现在group by中或者聚合函数中
- 连接查询
- 内连接
- 等值连接 与自然连接类似,全部选择 不删除重复列
- 不等连接 等号外的其他连接条件
- 自然连接 使用“=”,使用选择列表删除重复列 inner join on
- 外连接
- 左外连接 选出左边列所有数据,当左边行数>右边行数时,右边不足行为null left join
- 右外连接 right join
- 全连接 full join mysql不支持全连接,可以使用union 合并左右连接实现
- 交叉连接 不带where子句 cross join 显示所有组合,多余的均不显示
- 内连接
- union 合并双方列数量,类型(类型可以不一样),顺序相同,消去重复行
- union all 不消去重复行
- distinct
- 必须放在字段前面
- 多个字段时,每个字段的不重复值会被列出
- limit分页查询优化方法
- 直接使用数据库提供的SQL语句 适用数据量小
- 建立主键或唯一索引, 利用索引
- 使用预查询prepare
- 子查询(利用id查出起始id,在此id上再进行查询),连接查询等
- 如果id连续,可以使用id限定优化,即用between和limit结合使用
- explain关键字
- MySQL索引失效的几种情况
-
- where查询语句中含有不等于号
- where字句中的查询条件使用了函数
- join操作中,MySQL只有在主键和外键数据类型相同时才使用索引,否则即使建立了索引也不会使用
- where中查询条件使用了like 和regexp,nysql只会在搜索模板第一个字符不是通配符情况下才使用模板
- 在order by操作中,mysql只有在排序条件不是一个查询条件表达式时才使用索引
- 某数据列包含许多重复值,没必要建立索引
- 使用or时,必须将or条件中的每列都带有索引才能有效
- 如果列类型是字符串,一定要用引号将数据引用起来,即隐式转换
- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
- select *可能导致不走索引
- not in ,条件满足下改成左连接方式可解决
- in和exists的性能区别
-
- in先进行子查询,即in后面的语句 exists先查主查询,即exists前面的语句
- 如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。
- 其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了
- in不对null数据进行处理
- Innodb原理 https://www.cnblogs.com/shijingxiang/articles/4743324.html
- 基于B+树
- 表有多个字段,一颗B+树只能保存一个字段(索引),没有索引就变回了顺序查找
- 为了解决多个B+树访问同一套表数据,使用聚簇索引(InnoDB)和非聚簇索引(MyISAM)
- 聚簇索引是一种存储方式
- 聚簇索引
- 聚簇索引存储来说,行数据和主键B+树存储在一起,辅助键B+树只存储辅助键和主键,主键和非主键B+树几乎是两种类型的树。
- 聚簇索引中行数据和叶子节点存储在一起,这样主键和行数据是一起被载入内存的,按照主键Id来组织数据,获得数据更快。
- 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时辅助索引的维护工作,使用主键值当作指针会让辅助索引占用更多的空间,换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。
- 非聚簇索引存储来说,主键B+树在叶子节点存储指向真正数据行的指针,而非主键
- Page结构
- 大小16K,有头部,内容,尾部构成
- Page连接起来是一个双向链表
- 有4种不同的Record(保存数据库一行数据),是一种单链表形式
- 主键索引树非叶节点
- 主键索引树叶子节点
- 辅助键索引树非叶节点
- 辅助键索引树叶子节点
- 定位一个Record的过程
- 遍历B+树,通过各层非叶子节点最终到达一个Page,这个Page里存放的都是叶子节点(通过四种Record不同)
- 遍历Page内部Record,直到找到目标
- 未找到,遍历下一个Page
- 复合索引
- 分窄索引(1-2列)和宽索引(2列以上)原则:用窄不用宽
- 最左原则
- 数据库死锁原因
- 两种锁类型
- 排他锁 其他的事务不能对它读取和修改。
- 共享锁 可以被其他事务读取,但不能修改。
- 原因https://www.cnblogs.com/sivkun/p/7518540.html
- 两种资源c1,c2,A用户访问从c1(锁住),B访问c2(锁住),此时A要访问c2,,B要访问c1,产生死锁
- 两种锁类型
- 三大范式
-
①1NF :数据表中的每一列(每个字段)必须是不可拆分的最小单元,也就 是确保每一列的原子性;
②2NF:满足1NF后,要求表中的所有列,都必须依赖于主键,而不能有任何一列与主键没有关系,也就是说一个表只描述一件事情;
③3NF:必须先满足第二范式(2NF),要求:表中的每一列只与主键直接相关而不是间接相关,(表中的每一列只能依赖于主键)。
- 五大约束
-
1.primary KEY:设置主键约束;
2.UNIQUE:设置唯一性约束,不能有重复值;
3.DEFAULT 默认值约束;
4.NOT NULL:设置非空约束,该字段不能为空;
5.FOREIGN key :设置外键约束。
- 数据库索引优缺点
-
1.优点
(1)可以大大加快查询速度
(2)通过创建唯一性索引可以保证每一行数据的唯一性。
(3)可以加速表与表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
(4)在使用分组和排序子句检索时,可以减少分组和排序的时间
2.缺点
(1)创建和维护索引需要耗费一定时间,且随数据量增大而增大。
(2)索引需要占据一定空间
(3)当对表中数据进行增删时,索性也需要动态维护,降低了数据维护速度
- .innodb与MyISAM的区别
-
①Innodb支持事务MyISAM不支持
②Innodb支持外键MyISAM不支持
③Innodb支持行级锁,MyISAM只支持表级锁
④InnoDB支持MVCC, 而MyISAM不支持
- .四种数据库并发问题
-
1。脏读 一个事务读取到另一个事务未提交的结果
2。不可重复读 一个事务两次读取操作中间另一个事务可以进行update操作
3。幻读 一个事务两次读取操作中间另一个事务可以进行insert操作
- 隔离性
-
1。读未提交
2。读提交
3。重复读 时间点从事务开始时,即读取结果都是数据开始前的 MySql默认
4。序列化
数据库(面试)知识点总结
最新推荐文章于 2022-07-05 16:44:51 发布