^-^
单表查询
  1. Q：单表查询都有哪些方式？
  2. Q：当一个查询可以使用多个二级索引时如何选择？
  3. 知识点：索引合并
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `single_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb3

Q：单表查询都有哪些方式？

  1. const：使用主键或者唯一索引与一个常数值进行等值比较
  2. ref：使用二级索引与一个常数值进行等值比较
  3. ref_or_null：使用二级索引查出二级索引列的值等于某个常数记录的同时查出该二级索引列为null的记录
  4. range：使用索引进行范围匹配，包括主键索引和二级索引
  5. index：遍历二级索引记录获取目标值
  6. all：扫描聚簇索引

Q：当一个查询可以使用多个二级索引时如何选择？

sql：

1
2
3
4
SELECT * FROM single_table WHERE 
        (key1 > 'xyz' AND key2 = 748 ) OR
        (key1 < 'abc' AND key1 > 'lmn') OR
        (key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ;

A：思路：

  • 思路1：把索引不包含的列替换为true，对sql进行“化简”，这样做的原因是我们建立的索引中不包含条件列，我们只能在回表时去筛选
  • 思路2：替换掉永远为TRUE或者FALSE的条件

当使用idx_key1完成查询时：
当我们使用idx_key1进行查询时，使用思路1进行化简：common_field无索引，key1的%suf无法使用索引

1
2
3
4
SELECT * FROM single_table WHERE 
        (key1 > 'xyz') OR
        (key1 < 'abc' AND key1 > 'lmn') OR
        (key1 > 'zzz') ;

使用思路2再次进行化简：key1小于’abc’的同时大于’lmn’是不可能的

1
2
3
SELECT * FROM single_table WHERE 
        (key1 > 'xyz') OR
        (key1 > 'zzz') ;

对OR取并集：

1
2
SELECT * FROM single_table WHERE 
        (key1 > 'xyz')

当使用idx_key2完成查询时：
当我们使用idx_key2进行查询时，进行化简：

1
SELECT * FROM single_table WHERE TRUE

使用idx_key2的话需要遍历所有的二级索引并回表，效率很低，所以我们最终选择的索引是idx_key1

知识点：索引合并

Intersection合并：
并不是所有的查询都只能使用一个二级索引，在以下特殊情况时会使用Intersection合并，即多个二级索引

  • 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。
  • 情况二：主键列可以是范围匹配

Union合并：

  • 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
  • 情况二：主键列可以是范围匹配
2023-05-27 该篇文章被 remember 打上标签: innodb
无负今日 remember