SQL M题

索引和性能优化

-- 问题一
-- 假如存在以下表 t1 和索引：
CREATE TABLE t1 (
  id INT NOT NULL PRIMARY KEY,
  i  INT,
  dt DATE);
CREATE INDEX idx1 ON t1(dt);

--下面的查询语句有没有性能问题？
SELECT * FROM t1 WHERE YEAR(dt) = '2019';

-- 问题二
-- 假如存在以下表 t2 和索引：
CREATE TABLE t2 (
  id INT NOT NULL PRIMARY KEY,
  i  INT,
  dt DATE,
  v  VARCHAR(50));
CREATE INDEX idx2 ON t2(i, dt);

-- 下面的查询语句有没有性能问题？
SELECT *
FROM t2
WHERE i = 99
ORDER BY dt DESC
LIMIT 5;

-- 问题三
-- 为了优化下面两个查询，表 t3 中的索引有没有问题？
SELECT *
FROM t3
WHERE col1 = 99
AND col2 = 10;

SELECT *
FROM t3
WHERE col2 = 10;

-- 表 t3 的结构和索引如下：
CREATE TABLE t3 (
  id   INT NOT NULL PRIMARY KEY,
  col1 INT,
  col2 INT,
  col3 VARCHAR(50));
CREATE INDEX idx3 ON t3(col1, col2);

-- 问题四
-- 假如存在以下表 t4 和索引：
CREATE TABLE t4 (
  id   INT NOT NULL PRIMARY KEY,
  col1 INT,
  col2 VARCHAR(50));
CREATE INDEX idx4 ON t4(col2);

-- 下面的查询语句有没有性能问题？
SELECT *
FROM t4
WHERE col2 LIKE '%sql%';

-- 问题五
-- 假如存在以下表 t5 和索引：
CREATE TABLE t5 (
  id   INT NOT NULL PRIMARY KEY,
  col1 INT,
  col2 INT,
  col3 VARCHAR(50));
CREATE INDEX idx5 ON t5(col1, col3);

-- 下面两个查询语句，哪个性能更快？
SELECT col3, count(*)
FROM t5
WHERE col1 = 99
GROUP BY col3;

SELECT col3, count(*)
FROM t5
WHERE col1 = 99
AND col2 = 10
GROUP BY col3;

SQL案例分析-微信好友关系

MySQL 8

-- 创建示例表
create table t_user(user_id int primary key, user_name varchar(50) not null);

insert into t_user values(1, '刘一');
insert into t_user values(2, '陈二');
insert into t_user values(3, '张三');
insert into t_user values(4, '李四');
insert into t_user values(5, '王五');
insert into t_user values(6, '赵六');
insert into t_user values(7, '孙七');
insert into t_user values(8, '周八');
insert into t_user values(9, '吴九');

create table t_friend(
  user_id int not null, 
  friend_id int not null, 
  created_time timestamp not null, 
  primary key (user_id, friend_id)
);

insert into t_friend values(1, 2, current_timestamp);
insert into t_friend values(2, 1, current_timestamp);
insert into t_friend values(1, 3, current_timestamp);
insert into t_friend values(3, 1, current_timestamp);
insert into t_friend values(1, 4, current_timestamp);
insert into t_friend values(4, 1, current_timestamp);
insert into t_friend values(1, 7, current_timestamp);
insert into t_friend values(7, 1, current_timestamp);
insert into t_friend values(1, 8, current_timestamp);
insert into t_friend values(8, 1, current_timestamp);
insert into t_friend values(2, 3, current_timestamp);
insert into t_friend values(3, 2, current_timestamp);
insert into t_friend values(2, 5, current_timestamp);
insert into t_friend values(5, 2, current_timestamp);
insert into t_friend values(3, 4, current_timestamp);
insert into t_friend values(4, 3, current_timestamp);
insert into t_friend values(4, 6, current_timestamp);
insert into t_friend values(6, 4, current_timestamp);
insert into t_friend values(5, 8, current_timestamp);
insert into t_friend values(8, 5, current_timestamp);
insert into t_friend values(7, 8, current_timestamp);
insert into t_friend values(8, 7, current_timestamp);

-- 查看好友列表
SELECT u.user_id AS "好友编号", u.user_name AS "好友姓名"
FROM t_user u 
JOIN t_friend f 
ON (u.user_id = f.friend_id)
WHERE f.user_id = 1;

-- 查看共同好友
WITH f1(user_id, user_name) AS (
SELECT u.user_id, u.user_name
FROM t_friend f
JOIN t_user u ON (f.friend_id = u.user_id)
WHERE f.user_id = 3),
f2(user_id, user_name) AS (
SELECT u.user_id, u.user_name
FROM t_friend f
JOIN t_user u ON (f.friend_id = u.user_id)
WHERE f.user_id = 4)
SELECT *
FROM f1
JOIN f2 ON (f1.user_id = f2.user_id);

-- 推荐好友
WITH friend(user_id, user_name) AS (
SELECT u.user_id, u.user_name
FROM t_friend f
JOIN t_user u ON (f.friend_id = u.user_id)
WHERE f.user_id = 2),
fof(user_id, user_name) AS (
  SELECT u.user_id, u.user_name
  FROM t_friend f 
  JOIN t_user u ON (u.user_id = f.friend_id)
  JOIN friend ON (f.user_id = friend.user_id)
  WHERE f.friend_id != 2
  AND f.friend_id NOT IN (SELECT user_id FROM friend)
)
SELECT user_id,user_name,count(*) FROM fof
GROUP BY u.user_id, u.user_name;

-- 关系链
WITH RECURSIVE relation(uid, fid, hops, path) AS (
SELECT user_id, friend_id, 0 AS hops, concat(',', user_id, ',', friend_id) AS path
FROM t_friend tf 
WHERE user_id= 6
UNION ALL 
SELECT r.uid, f.friend_id, hops+1, concat(r.PATH, ',', f.friend_id)
FROM relation r
JOIN t_friend f ON (r.fid = f.user_id)
WHERE instr(r.PATH,concat(',',f.friend_id,','))=0
AND hops