MySQL索引未命中场景

2019/09/24

准备测试数据

  • 创建一张表
create table person(
    id     int,
    name   varchar(20),
    gender char(6),
    email  varchar(50)
);
  • 创建存储过程
delimiter $$ # 声明存储过程的结束符号为$$
create procedure insertPerson()
BEGIN
    declare i int default 1;
    while(i < 10000) do
        insert into person 
        values(i, concat('yyc',i),'male', concat('yyc',i,'@163.com'));
        set i = i + 1;
    end while;
END$$ # $$结束
delimiter; # 重新声明分号为结束符号

  • 执行存储过程
call insertPerson();


索引相关基本操作

  • 如何创建一个普通索引?
    CREATE INDEX index_name on table_name(column(length));
    
  • 如何创建一个主键?
    ALTER TABLE table_name ADD PRIMARY KEY (column);
    ALTER TABLE table_name ADD CONSTRAINT constraint_description PRIMARY KEY (column);
    
  • 如何删除一个主键?
    ALTER TABLE table_name DROP PRIMARY KEY
    
  • 如何创建一个唯一索引?
    CREATE UNIQUE INDEX index_name on table_name(column(length));
    
  • 如何创建一个联合索引?
    CREATE INDEX index_name on table_name(column1, column2);
    
  • 如何删除一个索引?
    DROP INDEX index_name on table_name;
    
  • 如何显示表中已经创建的索引?
    SHOW INDEX FROM table_name;
    


模糊查询

# Index has been created for the name column.
EXPLAIN SELECT * FROM person WHERE name LIKE 'yy%333';

模糊查询


并集查询

# Index has been created for the name column.
EXPLAIN SELECT * FROM person WHERE `name` = 'yyc8888' OR id = 333;

并集_idx_name

# Index has been created for the name and id column.
EXPLAIN SELECT * FROM person WHERE `name` = 'yyc8888' OR id = 333;

并集_idx_id_and_idx_name


范围查询

# Index has been created for the id column.
EXPLAIN SELECT * FROM person WHERE id > 3000

范围查询


使用函数

# Index has been created for the name column.
EXPLAIN SELECT * FROM person WHERE REVERSE(`name`) = '333cyy';

使用函数


存在类型转换

# Index has been created for the name column.
EXPLAIN SELECT * FROM person WHERE name = 8888

存在类型转换


联合索引不满足最左匹配原则

# Union index has been created for the name and id column.
EXPLAIN SELECT * FROM person WHERE id = 333 and `name` = 'yyc333';

union_index_id_name

# Union index has been created for the name and id column.
EXPLAIN SELECT * FROM person WHERE id = 333;

union_index_id

# Union index has been created for the name and id column.
EXPLAIN SELECT * FROM person WHERE `name` = 'yyc333';

union_index_name


参考

MySQL索引原理以及查询优化


一位喜欢提问、尝试的程序员

(转载本站文章请注明作者和出处 姚屹晨-yaoyichen

Post Directory