- 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
示例:有如上三表,要求查询所有用户及其订单和支付信息(即使没有订单或支付记录)
userid | name |
1 | Alice |
2 | Bob |
3 | Carl |
order_id | user_id | product |
101 | 1 | 手机 |
102 | 1 | 耳机 |
103 | 3 | 笔记本 |
payment_id | order_id | amount |
201 | 101 | 5000 |
202 | 103 | 8000 |
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(无耳机订单) |
Bob | NULL(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前后必须都是索引列,否则索引失效