Mysql索引查询效率优化

柔光的暖阳◎ 2021-12-16 09:04 453阅读 0赞

### Mysql索引查询效率优化 ###

**1.为什么要使用索引？什么是索引**  
答：使用索引可以大幅度增强查询的速度。但是我们在一些频繁修改或者删除的表上面，不建议建立太多的索引，因为每次修改、增加、删除都要根据设置的索引类型，对内存的文件进行重新排序，这样很损耗性能。索引就是通过特定的算法（例：Btree或者Hash算法），然后根据建立的特征值去匹配。比如，原先我们查询一条数据，要执行一万次，建立合适的索引以后，可以根据某个特定条件，10次就能查出结果，这样性能和查询速度上面都有大大的提高。

**2.mysql中Explain查询计划参数说明==建议我们每写一条sql,查看一下执行效果**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70]

select_type: SIMPLE -- 查询类型（简单查询、联合查询、子查询） 
    table: user -- 显示这一行的数据是关于哪张表的 。
    type: range -- 区间索引（在小于1990/2/2区间的数据)，这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中，ALL代表扫描了全表才确定结果。一般来说，得保证查询至少达到range级别,最好能达到ref。 
    possible_keys: birthday  -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。  
    key: birthday -- 实际使用到的索引。如果为NULL，则没有使用索引。如果为primary的话，表示使用了主键。 
    key_len: 4 -- 最长的索引宽度。如果键是NULL，长度就是NULL。在不损失精确性的情况下，长度越短越好。
    ref: const -- 显示哪个字段或常数与key一起被使用。  
    rows: 1 -- 这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。 
    Extra: Using where; Using index -- 执行状态说明，这里可以看到的坏的例子是Using temporary和Using

***这里面参数所代表的的意思，在网上都可以找得到，这里不做描述了，这是主要是Explain的用法***

**3.mysql中索引使用场景详解：**  
01：普通索引（INDEX），如果字段有重复值，并且没有什么唯一性，就设置为普通索引效果最佳。

02：唯一索引（UNIQUE），这里面要求字段的值是惟一的，并且不是主键，并且字段值可以为NULL，这样就可以使用唯一索引。

03：主键索引（PRIMARY KEY），也就是每张表都会有主键，设置为主键的时候，主键不能为空，且值是惟一的。此时，这个字段自然就是主键索引了，无需我们另外添加。

04：全文索引（Full Text）。其实也是一种搜索引擎，他可以搜索某一个字段里面内容关键词出现的频率和重要性，并且根据特定的算法，筛选出我们想要的结果。但是：MySql自带的全文索引只能用于数据库引擎为MYISAM的数据表，如果是其他数据引擎，则全文索引不会生效。此外，MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。所以说，全文索引，基本上在日常开发中很少遇到。

05：复合索引也叫作联合索引。主要是针对于需要建多个索引的情况，将多个字段的索引合并成一个。但是，要想使用复合索引，必须是从左到右执行，也就是说，复合索引生效的条件是： 如果有索引 a,b,c。 生效条件为：a或者 a,b或者a,b,c 其他如果是b,c则索引不生效，这点要切记，也就是复合索引必须要复合最左原则。

**4.mysql中索引失效的一些情况总结**  
01：使用like并且 前后有百分号。列如： where name like ‘%张三%’。 *注： 只使用后%索引不会失效*  
02：索引列参与了数学计算。 列如，age字段建了索引。但是 现在需求是查询出满足年龄加10等于30的所有用户，这样索引也会失效。

select age from user where age+10=30

03：mysql查询where条件中只有or关键字，并且or的前后都是索引字段时，索引才会起作用，否则不起作用。

04：mysql需要查询的列类型是字符串时（varchar），查询的时候，必须使用单引号（包含起来），否则不使用索引。

05：查询where条件中，没有用到联合索引的第一个字段，也就是我们上文说到的最左原则，则不会走索引。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70]: /images/20211214/f0d78722866e405a949cd7cede22c7f7.png