mysql手册09_索引的使用

红太狼 2022-11-28 10:44 162阅读 0赞

**mysql手册09\_索引的使用**

--------------------

**索引是数据库优化最重要最常用的手段之一**

建立测试表testdata
    create table testdata(
    	id int primary key,
    	name varchar(20),
    	address varchar(10)
    )
    
    利用存储过程插入300000条测试数据（耗时极长！）：
    
    delimiter $
    
    create procedure add_data()
    begin
    	declare num int default 1;
    	while num <= 300000 do
    		insert into testdata values(num,concat('name',num),'南京市');
    		set num=num+1;
    	end while;
    end$
    
    delimiter ;
    
    call add_data();

**验证索引对查询效率的提升：**

通过id查询一条数据，耗时0.01秒：
    select * from testdata where id = 7856;
    +------+----------+-----------+
    | id   | name     | city      |
    +------+----------+-----------+
    | 7856 | name7856 | 南京市    |
    +------+----------+-----------+
    1 row in set (0.01 sec)
    
    
    通过name查询数据，耗时0.23秒：
    select * from testdata where name = 'name9824';
    +------+----------+-----------+
    | id   | name     | city      |
    +------+----------+-----------+
    | 9824 | name9824 | 南京市    |
    +------+----------+-----------+
    1 row in set (0.23 sec)
    
    原因是主键字段默认拥有索引！
    
    
    给name字段创建索引：
    create index idx_name on testdata(name);
    
    
    再次通过name查询数据，耗时0.01秒：
    select * from testdata where name = 'name10464';
    +-------+-----------+-----------+
    | id    | name      | city      |
    +-------+-----------+-----------+
    | 10464 | name10464 | 南京市    |
    +-------+-----------+-----------+
    1 row in set (0.01 sec)

**索引的使用：**

准备环境：

create table seller (
    	id int(10) primary key auto_increment,
    	name varchar(20),
    	nickname varchar(10),
    	password varchar(20),
    	status varchar(1),
    	address varchar(100),
    	createtime datetime
    )
    
    insert into seller values(1,'华为官方旗舰店','HUAWEI','123456','1','中国','2012-12-21 12:00:00');
    insert into seller values(2,'小米商城','MI','121212','1','中国','2012-12-21 12:00:00');
    insert into seller values(3,'大疆无人机','DJI','111111','1','中国','2015-10-11 10:30:00');
    insert into seller values(4,'Apple Store','Apple','123123','0','美国','2015-12-21 12:00:00');
    insert into seller values(5,'索尼','Sony','111111','0','日本','2002-12-21 12:00:00');
    insert into seller values(6,'三星','SAMSUNG','123456','0','韩国','2018-03-15 09:00:50');
    insert into seller values(7,'京东方','BOE','123456','1','中国','2012-12-21 12:00:00');
    insert into seller values(8,'中芯国际','SMIC','123456','1','中国','2020-12-21 12:00:00');
    insert into seller values(9,'台积电','TSMC','123456','1','中国','2012-12-21 12:00:00');
    insert into seller values(10,'长鑫存储','','123444','1','中国','2021-01-01 12:00:00');
    
    创建复合索引：
    create index idx_name_status_address on seller(name,status,address);

**避免索引失效：**

**1. 最左前缀法则**（查询条件从最左列name开始，不能跳过中间的列status）

**2. 范围查询右边的列不能使用索引：**

**3. 不要在索引列上进行运算操作，否则索引失效：**

**4. 字符串需要加单引号，否则索引失效：**

**5. 尽量使用覆盖索引，避免使用select \***

**6. 用 or 分隔的条件，or之前的字段有索引，or之后的字段没有索引，则or之前的字段索引失效**

**7. 模糊查询**

**8. 若MySQL评估使用索引比全表扫描还慢，则不使用索引：**

**9. （对于数据较为均匀的场景）in 索引生效， not in 索引失效：**

**10. 尽量选择复合索引，少使用单列索引：**

创建以下复合索引：
    create index idx_name_status_address on seller(name,status,address);
    相当于：
    name
    name + status
    name + status + address
    
    如果创建单列索引：
    create index idx_name on seller(name);
    create index idx_status on seller(status);
    create index idx_address on seller(address);
    
    当使用多个字段进行查询时，数据库只会选择使用一个最优的索引（辨识度最高），并不会使用全部的索引。

**查看索引的使用情况：**

当前会话：
    show status like 'Handler_read%';
    
    +-----------------------+--------+
    | Variable_name         | Value  |
    +-----------------------+--------+
    | Handler_read_first    | 11     |	
    | Handler_read_key      | 211    |
    | Handler_read_last     | 0      |
    | Handler_read_next     | 214    |
    | Handler_read_prev     | 0      |
    | Handler_read_rnd      | 11     |
    | Handler_read_rnd_next | 300080 |
    +-----------------------+--------+
    
    全局：
    show global status like 'Handler_read%';
    
    +-----------------------+----------+
    | Variable_name         | Value    |
    +-----------------------+----------+
    | Handler_read_first    | 912      |
    | Handler_read_key      | 10870    |
    | Handler_read_last     | 0        |
    | Handler_read_next     | 12720    |
    | Handler_read_prev     | 0        |
    | Handler_read_rnd      | 2256     |
    | Handler_read_rnd_next | 13865689 |
    +-----------------------+----------+
    
    具体参数的含义：
    Handler_read_first		读取索引第一个条目的次数
    Handler_read_key		通过index获取数据的次数
    Handler_read_last		读取索引最后一个条目的次数
    Handler_read_next		通过索引读取下一条数据的次数
    Handler_read_prev		通过索引读取上一条数据的次数
    Handler_read_rnd		从固定位置读取数据的次数
    Handler_read_rnd_next	从数据节点读取读取下一条数据的次数