Mysql（基础+索引）

三大范式

  • 确保字段不可以再分开，比如地址按照省份，城市拆开不能混在一起，否则如果我查询在武汉的条件就不好筛选
  • 非主键字段要依赖于主键，而不是部分依赖，比如订单信息由订单id,商品id，商品名称，然后订单id和商品id是复合主键，那么商品名称值依赖于这个商品id，正确的应该拆成两张表
  • 非主键不能传递依赖于主键：order_id → user_id → user_city，比如在一张表里面有这样三个字段，那么用户城市这个字段就是传递性的依赖于主键

IN和EXISTS区别

  • IN是先进行最查询，然后再进行外层查询，意味着子操作要全部加载到内存中。EXISTS是先进行外层查询，再进行子查询。巨坑就是NOT IN查询如果子集返回为空，那么就相当于判定结果为false，整个结果就是为空，没有返回的数据。所以优先使用这个NOT EXISTS不受影响，因为判断的是是否有行数返回，不在乎返回的是不是NULL

drop,delete,truncate

  • drop是物理删除，可以用来删除整张表，包括表结构，不能回滚
  • delete可以行级删除，可以where携带条件，可以回滚，在没有提交事务之前，回滚都是通过这个undo log日志进行的，当我们回滚再把这个删除的数据都插入回去
  • truncate用于清空表中所有的数据但是会保留表结构，不能回滚

UNION和UNION ALL有什么区别

  • UNION返回两个查询的合并并且进行去重，UNION ALL 只是对结果进行合并

count(1)/count(*)/count(列名)区别

  • 在InnoDB引擎中这个count(1)和count(*)没啥区别，都是统计准确的行数，包括NULL行只要存在就统计，count(列名)会统计不含NULL的数据的行数、并且后面如果带有where条件筛选，都是先进行筛选再统计行数

SQL的执行顺序

  • 先FROM确定是哪个主表,再执行join连接，然后where过滤，group by 分组， 聚合函数（COUNT/SUM/AVG)等,having过滤聚合结果，select选择列，order by排序，limit限制行数
  • where是对单条原始数据进行筛选，having是对聚合函数之后进行筛选，也就是group by的分组对象

如何避免数据重复的插入

  • 使用unique唯一键约束
  • 使用insert... on duplicate key update允许插入记录时出现重复键的情况。如果插入的记录与现有的记录冲突，可以选择更新现有记录
  • 使用insert ignore: 在插入记录时会忽略因为重复造成的错误

varchar后面代表的是什么？

  • varchar括号后面代表的是字符，具体多少个字节要看使用的字符集。如果是ASCLL字符集，每个字符占一个字节，如果是UTF-8字符集每个字符占用1到4个字节

int(1)和int(10)的区别

  • int都是固定存储4个字节，只是显示宽度不一样，int(10)前面多余的空间会使用0进行填充

IP地址存储方式

  • 192.168.124.1
  • 使用字符串存储：varchar()存储直观方便进行数据的插入，但是占用空间大，不方进行范围查询
  • 使用整数存储：转换成32为无符号整数进行存储，占用空间小，但是不够直观

mysql的第3-10条数据怎么查？

  • 可以使用limit语句，结合偏移量进行查询，偏移量是从这个0开始的，所以limit 2,8;

mysql中的一些基本的函数

  • 字符串函数：concat（连接多个字符串，返回拼成一个字符串），length,substring,replace,left（对字符串进行截取）
  • 数值函数：abs(绝对值)，power(指定数值的幂次方)
  • 日期函数：now(返回当前日期和时间),curdate(返回当前日期)
  • 聚合函数：count,sum,avg（平均值）,max,min

sql题给了学生表和课程表，求不存在01课程但是存在02课程的学生成绩

  • 使用左连接left on,分别和这个01课程和02课程进行左连接，如果没有不存在01课程就返回这个null值，再在后面进行一个where筛选，需要这个01返回为null的，02课程返回不为Null的
  • 使用NOT EXISTS实现，先在外层筛选出这个选择了c2的学生，然后内层where排除掉这个选了c1课程的

引擎介绍

  • InnoDB：默认引擎，支持ACID事务支持，行级锁，外键约束，适用于高并发读写操作
  • MyISAM:较低存储空间和内存消耗，使用大量读场景
  • Memory:将数据存储在内存中，适用于性能高的读操作，但是服务器重启或者数据崩溃的时候会导致数据丢失

索引的分类

  • 按物理存储：聚簇索引，二级索引

  • 按数据结构：B+tree索引，Hash索引，Full-text索引

  • 按字段特性：主键索引，唯一索引，普通索引，前缀索引（针对字符串截取前N个字符减少索引占用的空间，但是可能导致区分度过低）

  • 按字段个数：单例索引，联合索引（存在最左匹配原则，比如id,name先进行id比较，如果相同再进行name比较，所以要注意把区别度大的字段放在前面）

  • 物理存储：主键索引叶子结点存储的是实际的数据，所有用户的记录都放在叶子节点里，二级索引里面存储的是主键值而不是实际数据，所以要查询的数据如果能在二级索引里面查到就不需要进行回表，这个就是覆盖索引，否则的话就需要获得这个主键值然后再去检索主键索引拿到数据，这个过程就是回表。

  • Hash索引优势：查询很快，直接通过Hash函数定位到数据位置，但是短板是不支持这个范围查询，排序。比如Memory就默认使用这个Hash索引，因为是基于内存存储的所以追求极致的查询速度

到B+树的叶子节点之后怎么查数据

  • 叶子结点里面的数据行之间是通过这个单链表连接的，但是单链表的查询速度很慢，所以有一个页目录，由多个槽组成，通过二分法确定选择哪个行然后找到对应位置上

索引失效

  • 使用左模糊或者左右模糊查询的时候像（like %xx）和（like %xx%）因为左边是模糊的没办法根据索引查询，只能全表搜索
  • 对索引使用函数，比如name索引，在where left(name,2)='张三'；索引记录的都是列的原始值，进行函数操作之后就没办法利用索引了
  • 对索引表达式进行运算，比如说年龄age+1运算
  • 字符串和数值的隐式类型转换，SELECT * FROM user WHERE phone = 13800138000;这个会自动使用函数把字符串转换成数字
  • 联合索引不遵循“最左原则”，比如联合索引abc，查询的时候cba顺序查询
  • OR中存在非索引列，因为是满足任意一个，可能没办法通过索引覆盖所有的查询条件，所以直接放弃索引使用进行全表搜索

索引建立的问题

  • 索引不能频繁建立，建立和维护都是很占空间的，数据量很小的时候也不要建，像字段唯一（商品编码），经常使用where查询，group by或者order by要使用的字段可以设置索引提高效率
  • 索引的优化：使用前缀索引，当有一大段字符串作为索引的时候，这个前缀索引能够减小索引的大小；覆盖索引；避免索引失效；主键最好是id自增的，有序自增能直接在数据页进行追加，如果不有序有可能导致不断地页分裂，产生内存碎片，索引结构不够紧凑，从而影响查询效率

B+树特性

  • B+树所有的叶子节点在一层，并且各个叶子节点之间是双向链表连接的，所以就非常适合范围查询
  • B+树非叶子节点只记录键值和指向子节点的指针不存储数据，这样在数据量很大的情况下，B+树的层高相比于B树就更少，查询速率就更快
  • B+树自平衡，在插入，删除，更新之后会自动平衡
posted @ 2025-12-16 14:30  Huangyien  阅读(3)  评论(0)    收藏  举报