一、数据准备

1.1、创建数据库

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

1.2、创建数据

• 设置MySQL可以创建 函数

# 不加global只是当前窗口有效
SET GLOBAL log_bin_trust_function_creators = 1;

• 创建随机字符串 函数

DELIMITER $
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n
        DO
            SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 52), 1));
            SET i = i + 1;
        END WHILE;
    RETURN return_str;
END $
DELIMITER ;

• 创建随机数字函数

DELIMITER $
CREATE FUNCTION rand_num(from_num INT, to_num INT) RETURNS INT(11)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1));
    RETURN i;
END $
DELIMITER ;

• 制造数据的存储过程

DELIMITER $
CREATE PROCEDURE insert_data(start INT, max_num INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0; #  设置手动提交事务
    REPEAT
        #循环
        SET i = i + 1; #  赋值
        INSERT INTO single_table (key1, key2, key3, key_part1, key_part2, key_part3, common_field)
        VALUES (rand_string(100),
                i,
                rand_string(100),
                rand_string(100),
                rand_string(100),
                rand_string(100),
                rand_string(100));
    UNTIL i = max_num
        END REPEAT;
    COMMIT; #  提交事务
END $
DELIMITER ;

• 创建 10000 条数据

CALL insert_data(1, 10000);

二、访问方法（access method）的概念

2.1、const

2.1.1、使用主键

DESC
SELECT *
FROM single_table
WHERE id = 1438;

使用主键.png

2.1.2、使用唯一的二级索引

DESC
SELECT *
FROM single_table
WHERE key2 = 3841;

使用唯一索引.png

2.1.3、使用唯一的二级索引，但是查询条件为NULL时

唯一二级索引列并不限制 NULL 值的数量

DESC
SELECT *
FROM single_table
WHERE key2 IS NULL;

image.png

2.2、ref

2.2.1、二级索引列值为NULL的情况

不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法

2.2.2、如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref了

DESC
SELECT *
FROM single_table
WHERE key_part1 = 'god like'
  AND key_part2 > 'legendary';

2.3、ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来

DESC
SELECT *
FROM single_table
WHERE key1 = 'abc'
   OR key1 IS NULL;

image.png

2.4、range

DESC
SELECT *
FROM single_table
WHERE key2 IN (1438, 6328)
   OR (key2 >= 38 AND key2 <= 79);

range.png

2.5、index

DESC
SELECT key_part1, key_part2, key_part3
FROM single_table
WHERE key_part2 = 'abc';

index.png

小结

由于key_part2并不是联合索引idx_key_part最左索引列，所以我们无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下面这两个条件：

• 它的查询列表只有3个列：key_part1, key_part2, key_part3，而索引idx_key_part又包含这三个列
• 搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中

2.6、all

三、注意事项

四、索引合并

五、Union合并