mysql增加索引不生效的几种情况

阳光穿透心脏的1/2处 2024-04-17 05:41 61阅读 0赞

#### 增加索引可以提高查询效率。 ####

增加索引就是增加一个索引文件，存放的是数据的地址，类似与我们文档的目录，在查找过程中可以不用从书的内容查找，直接根据目录对应的页码查找。索引是根据地址查找。  
创建索引，索引使用的数据结构也有很多种。常见的是B-tree,哈希等。mysql默认使用的数据库索引是innerDB,innerDB的索引结构是B-tree。  
但是在使用过程中哪些情况增加索引无法达到预期的效果呢？下面列举几种常见情况：  
假设name age address 都已经加了索引。索引名字分别为 index\_name,index\_age,index\_address。

##### 用explain查看SQL的执行计划 #####

###### 执行计划的 type ######

表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下:

ALL, index,  range, ref, eq_ref, const, system, NULL
    ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
    index：Full Index Scan，index与ALL区别为index类型只遍历索引树
    range:索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。
    ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
    eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件。

###### 1、使用查询多列使用or，type=all ######

![在这里插入图片描述][20190823181643620.png]

###### 2、使用模糊查询，type=all ######

![在这里插入图片描述][20190823182410497.png]

###### 3、在加索引的字段上有运算 ######

![在这里插入图片描述][20190823182625852.png]

###### 4、如果列类型是字符串，需要使用引号引用起来。name字段是字符串类型 ######

没引号 type=all![在这里插入图片描述][2019082318275411.png]  
有引号 tye=ref  
![在这里插入图片描述][20190823182919660.png]  
首先mysql有个类型转换规则就是将“字符转成数字”，上面的sql是包含运算的：  
explain SELECT name,age,address FROM user where cast(name as signed)= 10;

###### 5、反向查询区分情况 ######

（not , not in, not like, <>, != ,!>,!< ） 使用索引效果  
![在这里插入图片描述][20190823190837393.png]  
![在这里插入图片描述][20190823190340632.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDE5NzQ5NA_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDE5NzQ5NA_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][20190823184137897.png]

![在这里插入图片描述][20190823190223879.png]

##### 其他情况 #####

1、联合索引中关联字段没有使用相同的字符集；  
2、联合索引查询时没有用到第一个索引字段，即不满足最左原则；  
3、本身数据量很少，mysql会判断是否需要使用索引；

[20190823181643620.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/ffb81079af5e4ff3ac1fdaf748d202df.png
[20190823182410497.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/4f5b8d32b7334bddbcbc8b403056866b.png
[20190823182625852.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/69b34f84435e4d6aba3c72eb29c085fa.png
[2019082318275411.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/e2d76cdec73549f99e145deec0fd5fdd.png
[20190823182919660.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/7830d259b9064fd2bc7dc92b77890651.png
[20190823190837393.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/ba81f1c1cc45400ca4a5d3182fc4b55c.png
[20190823190340632.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/f5d08126a1ab4c5f8b53b0dedc6e2ff5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDE5NzQ5NA_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/739f1f8048b44920a6474e7ce4639637.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDE5NzQ5NA_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/4c27571e1437438da654059a23d9fc6a.png
[20190823184137897.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/5b84aa180c2a413e89f24adb5e4d5a10.png
[20190823190223879.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/f613e39ec0944e7a83276a15eaefda57.png