48、SQL优化篇、课后题答疑

create table t_n(a int not null, primary key(a));


insert into t_n values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
  (11),(12),(13),(14),(15),(16),(17),(18),(19),(20);


create table t_abc(
    id int not null auto_increment,
    a int not null,
    b int not null,
    c int not null,
    d int not null,
    padding varchar(200),
    primary key(id),
    key idx_abc(a,b,c)
) engine=innodb;


insert into t_abc(a,b,c,d, padding)
select t1.a, t2.a, t3.a, t3.a, rpad('', 200, 'ABC DEF G XYZ')
from t_n t1, t_n t2, t_n t3;

-- SQL 1
select * from t_abc where a = 10 and b = 10;
-- SQL 2
select * from t_abc where a = 10 and c = 10;
-- SQL 3
select * from t_abc where a = 10 and d = 10;

-- SQL 4
select * from t_abc where a = 10 order by a,c;
-- SQL 5
select * from t_abc where a = 10 order by b,c;

-- SQL 6
select id, a, b, c from t_abc where a = 10;
-- SQL 7
select id, a, b, c from t_abc where b = 10;
-- SQL 8
select id, a, b, c, d from t_abc where b = 10;

mysql> explain select id, a, b, c from t_abc where b = 10;
+----+-------------+-------+-------+---------------+---------+---------+------+----------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | rows | Extra                                  |
+----+-------------+-------+-------+---------------+---------+---------+------+----------------------------------------+
|  1 | SIMPLE      | t_abc | range | idx_abc       | idx_abc | 8       |  793 | Using where; Using index for skip scan |
+----+-------------+-------+-------+---------------+---------+---------+------+----------------------------------------+

select id, a, (select avg(b) from tab where a=t1.a) from tab t1;

mariadb> select id, a, (select avg(b) from tab where a=t1.a) from tab t1 order by id;
+------+---+---------------------------------------+
| id   | a | (select avg(b) from tab where a=t1.a) |
+------+---+---------------------------------------+
|    0 | 0 |                             4999.5000 |
|    1 | 1 |                             4999.0000 |
|    2 | 2 |                             5000.0000 |
......
| 9999 | 0 |                             4999.5000 |
+------+---+---------------------------------------+
10000 rows in set (0.01 sec)

mysql> select id, a, (select avg(b) from tab where a=t1.a) from tab t1 order by id;
+------+------+---------------------------------------+
| id   | a    | (select avg(b) from tab where a=t1.a) |
+------+------+---------------------------------------+
|    0 |    0 |                             4999.5000 |
|    1 |    1 |                             4999.0000 |
|    2 |    2 |                             5000.0000 |
......
| 9999 |    0 |                             4999.5000 |
+------+------+---------------------------------------+
10000 rows in set (4 min 17.90 sec)

select
  t1.id,
  t1.a,
  t2.avg_b
from
  tab t1
  inner join (
    select
      a,
      avg(b) avg_b
    from
      tab
    group by
      a
  ) t2 on t1.a = t2.a
order by
  id;

create table t_business(
  id1 varchar(30) not null,
  id2 varchar(30) not null,
  id3 varchar(30) not null,
  col1 ...,
  col2 ...,
  primry key(id1, id2, id3)
) engine=innodb;

select * 
from t_business 
where (id1, id2, id3) > ('id1', 'id2', 'id3')
order by id1, id2, id3
limit 1000

select * 
from t_business
where (id1 > 'id1') 
or (id1 = 'id1' and id2 > 'id2') 
or (id1 = 'id1' and id2 = 'id2' and id3 > 'id3')
order by id1, id2, id3
limit 1000

select count(*)
from t_order_detail
where seller_id = ? 
and order_status in (?) 
and refund_status in(?) 
and is_deleted=0;

select t2.* from (
  select id
  from t_order_detail
  where seller_id = ? 
  and order_status in (?) 
  and refund_status in(?) 
  and is_deleted=0
  order by create_time
limit M, N) t1 straight_join t_order_detail t2
where t1.id = t2.id;

create table t_jointab(
  id int not null auto_increment,
  a int not null,
  b int not null,
  c varchar(4000),
    primary key(id),
    key idx_a(a)
) engine=innodb charset utf8mb4;


insert into t_jointab(a,b,c)
select case when n < 9000 then n else 9000 end as a,
    n % 1000, rpad('x', 2000, 'ABCD')
from numbers where n < 10000;

mysql> analyze table t_jointab;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| rep.t_jointab | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.62 sec)


mysql> show indexes from t_jointab;
+------------+----------+--------------+-------------+-------------+
| Non_unique | Key_name | Seq_in_index | Column_name | Cardinality |
+------------+----------+--------------+-------------+-------------+
|          0 | PRIMARY  |            1 | id          |        8580 |
|          1 | idx_a    |            1 | a           |        8580 |
+------------+----------+--------------+-------------+-------------+

mysql> explain format=tree 
    select count(*) 
    from t_jointab t1, t_jointab t2 
    where t1.a = t2.a and t1.b = t2.b\G
*************************** 1. row ***************************
EXPLAIN: 
-> Aggregate: count(0)  (cost=5079.75 rows=1)
    -> Nested loop inner join  (cost=4221.75 rows=8580)
        -> Table scan on t1  (cost=1218.75 rows=8580)
        -> Filter: (t2.b = t1.b)  (cost=0.25 rows=1)
            -> Index lookup on t2 using idx_a (a=t1.a)  (cost=0.25 rows=1)

mysql> select count(*)
   from t_jointab t1, t_jointab t2 
   where t1.a = t2.a and t1.b = t2.b;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (1 min 19.29 sec)

# Query_time: 79.285872  Lock_time: 0.000036 Rows_sent: 1  Rows_examined: 1019000
SET timestamp=1724921875;
select count(*)。
   from t_jointab t1, t_jointab t2
   where t1.a = t2.a and t1.b = t2.b;

select count(*) from t_jointab t1 ignore index(idx_a), t_jointab t2 ignore index(idx_a) where t1.a = t2.a and t1.b = t2.b;

alter table t_jointab drop index idx_a, add index idx_a(a,b);

mysql> explain format=tree  select count(*)
    from t_jointab t1 , t_jointab t2
    where t1.a = t2.a and t1.b = t2.b\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)  (cost=4307.55 rows=1)
    -> Nested loop inner join  (cost=4221.75 rows=858)
        -> Table scan on t1  (cost=1218.75 rows=8580)
        -> Filter: (t2.b = t1.b)  (cost=0.25 rows=0.1)
            -> Index lookup on t2 using idx_a (a=t1.a)  (cost=0.25 rows=1)

mysql>explain format=tree  select count(*)     
  from t_jointab t1 ignore index(idx_a)  , t_jointab t2 ignore index(idx_a)  
  where t1.a = t2.a and t1.b = t2.b\G
*************************** 1. row ***************************
EXPLAIN: 
-> Aggregate: count(0)  (cost=7364171.96 rows=1)
    -> Inner hash join (t2.b = t1.b), (t2.a = t1.a)  (cost=7363313.96 rows=8580)
        -> Table scan on t2  (cost=0.05 rows=8580)
        -> Hash
            -> Table scan on t1  (cost=1218.75 rows=8580)

mysql> select t1.item_id, sum(t1.sold) as sold
from stat_item_detail t1, (
  select distinct item_id 
  from stat_item_detail t2
  where t2.gmt_create >= '2026-04-26 10:30:00') t22
where t1.item_id = t22.item_id
group by t1.item_id;

select item_id, sum(sold) from (
    select distinct t1.id,  t1.item_id, t1.sold as sold
    from stat_item_detail t1, stat_item_detail t2
    where t1.item_id = t2.item_id
    and t2.gmt_create >= '2026-04-26 10:30:00'
) t group by item_id;

select * from tab force index(idx_x) where id = 10

select * from tab force index(idx_abc) where a >=0 

select * 
from t1, t2, t3
where t1.c1 = t2.c1
and t2.c2 = t3.c3
order by t1.s1, t2.s2
limit 100;

mysql> show indexes from tab;
+-------+------------+----------+--------------+-------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Cardinality |
+-------+------------+----------+--------------+-------------+-------------+
| tab   |          0 | PRIMARY  |            1 | id          |       10010 |
| tab   |          1 | idx_abc  |            1 | a           |           3 |
| tab   |          1 | idx_abc  |            2 | b           |       10000 |
| tab   |          1 | idx_abc  |            3 | c           |       10000 |
+-------+------------+----------+--------------+-------------+-------------+

mysql> explain format=tree 
  select * 
  from tab t1, tab t2 
  where t1.id = t2.id 
  order by t1.b, t1.c 
  limit 10\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s)  (cost=12089.17 rows=10)
    -> Nested loop inner join  (cost=12089.17 rows=10010)
        -> Sort: t1.b, t1.c  (cost=1160.67 rows=10010)
            -> Table scan on t1  (cost=1160.67 rows=10010)
        -> Single-row index lookup on t2 using PRIMARY (id=t1.id)  (cost=0.99 rows=1)

mysql> explain format=tree 
  select * 
  from tab t1, tab t2 
  where t1.id = t2.id 
  order by t1.b, t2.c
  limit 10\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s)
    -> Sort: t1.b, t2.c, limit input to 10 row(s) per chunk
        -> Stream results  (cost=12089.17 rows=10010)
            -> Nested loop inner join  (cost=12089.17 rows=10010)
                -> Table scan on t1  (cost=1160.67 rows=10010)
                -> Single-row index lookup on t2 using PRIMARY (id=t1.id)  (cost=0.99 rows=1)

{
  "optimizing_distinct_group_by_order_by": {
    "simplifying_order_by": {
      "original_clause": "`t1`.`b`,`t2`.`c`",
      "items": [
        {
          "item": "`t1`.`b`"
        },
        {
          "item": "`t2`.`c`"
        }
      ],
      "resulting_clause_is_simple": false,
      "resulting_clause": "`t1`.`b`,`t2`.`c`"
    }
  }
}