SQL随笔
SQL随笔

SQL随笔

  • Join、Inner Join、Left Join、Right Join的区别
1.Join和inner Join一样,返回两张表关联字段共同存在的记录
2.Left Join,以左表为中心,返回左表(test1/a(别名))符合条件的记录和右表(test2/b(别名))与左表关联字段(on ?=?)相等的记录,没有则返回空
3.Right Joinn,以右表为中心,返回右表符合条件的记录,和左表与右表关联字段相等的记录,没有则返回
总结和注意事项:
1.从左到右依次执行:每个Left/Right Join都是基于前一步的结果集进行的
2.保留左/右表(取决于left或right join)所有数据,即使连接表中没有匹配的行,主表的数据仍会被保留,未匹配的字段显示为 NULL

示例:有如上三表,要求查询所有用户及其订单和支付信息(即使没有订单或支付记录)

useridname
1Alice
2Bob
3Carl
user用户表
order_iduser_idproduct
1011手机
1021耳机
1033笔记本
order订单表
payment_idorder_idamount
2011015000
2021038000
payment支付表
SQL如下:
SELECT 
    u.name AS 用户名,
    o.product AS 订单商品,
    p.amount AS 支付金额
FROM user u
LEFT JOIN order o ON u.user_id = o.user_id
LEFT JOIN payment p ON o.order_id = p.order_id;
用户名订单商品支付金额
Alice手机5000
Alice耳机NULL(无耳机订单)
BobNULL(Bob无订单)NULL
Carl笔记本8000
执行结果
  • 索引

优点:对数据查询速度提升非常大

缺点:1.维护索引需要耗费数据库资源。2.索引需要占用磁盘空间。3.当表数据被增删改时,速度会受到影响。(数据可能会重排序,导致索引需要维护)

索引的类别(区别:叶节点是否存放了行数据)

  • 聚簇索引(一级索引)
    • 将数据与索引存放在一起,索引结构的叶节点保存了行数据
    • 在InnoDB中,如果表没有主键,则第一个非空且唯一的列作为聚簇索引。若无主键也无非空且唯一的列,则会自动创建一个隐藏的row-id为聚簇索引
  • 非聚簇索引(二级索引、辅助索引)
    • 将数据与索引分开存放,索引结构的叶节点保存了指向数据对应的位置(主键值/key)
    • 非聚簇索引查询需要回表,即从二级索引中拿到数据的位置(主键值/key),再在聚簇索引(主键索引)中查找数据

存储引擎

  • InnoDB(必包含聚簇索引)
    • 主键索引:列被设定为主键后,数据库会自动创建索引,索引列的值不能为空
    • 单值(普通、单列)索引:一个索引只包含单个列,一个表可以有多个单值索引
    • 唯一索引:索引列的值必须唯一,但允许为空值
    • 复合(组合)索引:一个索引包含多个列
    • 全文索引(Full Text):MySQL5.7以后可用,应用场景少
  • MyISAM (主键索引、二级索引都是非聚簇索引)

常用创建索引语句

  • — 查看索引
  • SHOW INDEX FROM 表名;
  • — 删除索引
  • DROP INDEX 索引名 on 表名;
  • — 建表后创建普通索引
  • CREATE INDEX 索引名 ON 表名(列名);
  • — 建表后创建唯一索引
  • CREATE UNION INDEX 索引名 ON 表名(列名);
  • — 建表后创建复合(组合)索引
  • CREATE INDEX 组合索引名 ON 表名(列名1,列名2);
  • — 建表时创建单列(普通)索引,其中key(列名)是给列创建创建普通索引,key()可以有多个,但须保证列存在
  • CREATE TABLE 表名(列名1 VARCHAR(20) PRIMARY KEY,列名2 VARCHAR(20),key(列名2));
  • — 建表时unique()是创建唯一索引
  • CREATE TABLE 表名(列名1 VARCHAR(20) PRIMARY KEY,列名2 VARCHAR(20),unique(列名2));
  • — 建表时创建复合索引
  • CREATE TABLE 表名(列名1 VARCHAR(20) PRIMARY KEY,列名2 VARCHAR(20),列名3 VARCHAR(8),key(列名2,列名3));

索引应用

  • 在复合索引中遵循最左前缀原则。如上示例中,复合索引为key(列名1,列名2),则查询中必须包含列名1才能使用此复合索引。MySQL引擎会动态调整查询字段顺序,所以列名1和列名2的顺序不重要,但必须包含列名1(左前缀)

无法应用索引的场景

  • 查询中使用LIKE关键字(分类讨论):在查询语句中使用LIKE字段时,匹配字符串的第一个字符为”%”时,索引失效,如果”%”不在第一个字符,索引能够正常使用
  • 查询中使用多列索引(分类讨论):在此情况下,遵循最左前缀原则
  • 查询中使用OR关键字(分类讨论):在使用到OR关键字时,OR前后必须都是索引列,否则索引失效

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注