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

示例：有如上三表，要求查询所有用户及其订单和支付信息（即使没有订单或支付记录）

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;

• 索引

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

缺点：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前后必须都是索引列，否则索引失效