MySQL 索引的使用

末蓝、 2022-11-05 12:57 101阅读 0赞

## 索引的使用 ##

### 文章目录 ###

*   *  索引的使用
     *   *  1. 准备环境
         *  2. 避免索引失效的方式
         *   *  (1) 全值匹配
             *  (2) 最左前缀法则
             *  (3) 覆盖索引
             *  (4) 复合索引
         *  3. 索引失效的情况
     *  查看索引使用情况

### 1. 准备环境 ###

create table `tb_seller` (
    	`sellerid` varchar (100),
    	`name` varchar (100),
    	`nickname` varchar (50),
    	`password` varchar (60),
    	`status` varchar (1),
    	`address` varchar (100),
    	`createtime` datetime,
        primary key(`sellerid`)
    )engine=innodb default charset=utf8mb4; 
    
    --插入多条数据
    
    --创建联合索引
    create index idx_seller_name_sta_addr on tb_seller(name,status,address);

### 2. 避免索引失效的方式 ###

#### (1) 全值匹配 ####

对索引中所有列都指定具体值

![20210306175641248.png][]

#### (2) 最左前缀法则 ####

查询从索引的最左列开始，并且不跳过索引中的列

*  遵守最左法则的情况
    
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70][]
 *  违反最左法则的情况
    
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 1][]
 *  跳跃索引列的情况
    
    只有跳跃列之前的索引生效
    
    ![20210306175712492.png][]

#### (3) 覆盖索引 ####

尽量使用覆盖索引，减少select\*

覆盖索引指的是只访问索引的查询（索引列完全包含查询列）

![20210306175722769.png][]

如果查询列超出索引列，也会降低性能：

![20210306175733359.png][]

Extra列取值：
    
    	using index ：使用覆盖索引的时候就会出现
    
        using where ：在使用索引的情况下，需要回表查询所需的数据（回表查询：获取到了索引的数据，还要拿				   着这些数据去表中获取一整行的数据）
    
        using index condition ：查找使用了索引，但是需要回表查询数据
    
        using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表								 查询数据

#### (4) 复合索引 ####

尽量使用复合索引，减少使用单列索引

原因：使用复合索引，多列查询时数据库会选择一个最优的索引来使用，并不会使用多个索引；如果使用单列索引，查询多个列时会使用多个索引

### 3. 索引失效的情况 ###

*  范围查询之后的条件，索引将失效
    
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 2][]
    
    根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件 address 没有用到索引
 *  在索引列上进行运算操作， 索引将失效
    
    ![20210306175756475.png][]
 *  字符串不加单引号，索引将失效
    
     *  注意：varchar类型加不加单引号都可以查询出数据
    
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 3][]
    
    原因：没有对字符串加单引号，MySQL的查询优化器会自动的进行类型转换，进行了运算操作，造成索引失效
 *  or中条件有非索引列，全部索引将失效
    
    and无此规定
    
    下述示例中，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的
    
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 4][]
 *  以%开头的Like模糊查询全列，索引将失效
    
    如果仅仅是尾部模糊匹配，索引不会失效
    
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 5][]
    
    解决方案：
    
    通过覆盖索引来解决，不使用select\*
    
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 6][]
 *  如果MySQL评估使用索引比全表更慢，索引将失效
    
    比如某一列的值全是重复的，但也创建了索引，此时查询索引比查询表更慢
 *  is null、is not null 有时索引失效
 *  in 走索引，not in 索引失效

## 查看索引使用情况 ##

show status like 'Handler_read%';	--当前Session
    
    show global status like 'Handler_read%';	--全局

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 7][]

Handler_read_first ：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。
    
    Handler_read_key ：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
    
    Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
    
    Handler_read_prev ：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
    
    Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。 
    
    Handler_read_rnd_next ：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

[20210306175641248.png]: /images/20221023/5670b0882f554c4492d1efd47946c842.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70]: /images/20221023/beff77a24c1f49b6a8a8868d4d55c669.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 1]: /images/20221023/fdbf1fb1c0a7456c881db6a04680e52c.png
[20210306175712492.png]: /images/20221023/e835f2ce69dc43aeb3aeb198cec4ba50.png
[20210306175722769.png]: /images/20221023/d4d6e99fa120426a8cf625ad0eba37dc.png
[20210306175733359.png]: /images/20221023/6454afaae99f4fd88c66ef15350ea14a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 2]: /images/20221023/4af1e25dbb1d48918e4e7e0250da1fd4.png
[20210306175756475.png]: /images/20221023/7355aa3c13694068bf782f9e709b06e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 3]: /images/20221023/44f9a1c4f8c34e85ad92c529dcc1cc0a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 4]: /images/20221023/c8c57d842aa84bf2a5cf356863510dc3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 5]: /images/20221023/49692d4a645e44d58d3ccea3ecb2b0e5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 6]: /images/20221023/68ed533ebe7e4268bb01ed522a719b94.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 7]: /images/20221023/c6160bba778842bc86e316e94c62372f.png