Mysql 索引优化

Mysql 索引优化

mysql如何选择使用索引。

select *from user where username = 'a' and password = 'b'

如果useranme和password都是普通索引，那么他们会把2个索引都查出来，然后在把他们的交集拿出来

如果username是唯一索引，password是普通索引，那么我们的mysql就会优先使用唯一索引

select * from user where a > 2 and b =3

a和b都是索引,而且还是联合索引

这个时候索引b会失效，因为使用索引a然后在通过回表找里面的b是否等于3

因为联合索引的机制是先从做到有，这个操作在a索引中，他收到的任务是找到所有>2的数据，当大于2的数据都找出来了，那么此时的B是无序的，所以就导致了索引失效，索引不失效的前提的是索引必须保持有序。

虽然会索引失效，但是在Mysql新版本会使用索引下推的优化

1. 索引扫描： 数据库引擎使用联合索引（a, b）扫描满足 a > 1 条件的记录。
2. 条件判断： 在索引扫描的同时，对于每一条记录，会判断是否满足 b = 2 的条件。这个判断是在索引的层面上完成的，而不需要回表。
3. 结果集形成： 只有同时满足 a > 1 和 b = 2 的记录会被放入结果集。
4. 返回结果： 最终的结果集包含了同时满足 a 和 b 条件的记录，而这些记录是在索引扫描的过程中完成条件判断的。

select * from user a >= 2 and b = 3

这时候a和b索引就不会失效，因为a收到的命令是找到>=2的数据，当找到了2以后，然后就去看b，在2内的b索引是有序的，所以这时候b的索引是可以生效的。

ELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2

这个是和我们>= 是一样的，<=一样的不会导致我们的2个索引失效。不同的数据库会有差异，Mysql是这样的

SELECT * FROM t_user WHERE name like 'j%' and age = 22

name和age他们也是联合索引。首先会找到所有j开头的单词，然后，在挨个判断age是否等于22，也就是age索引会失效。

如果他们都是普通索引，那么mysql会根据索引的复杂度或者索引的大小，唯一值值数量 和 选择性（如j开头的数据比较少） 和索引长度大小（比如值int就是4个字节，varchar(20)就是20个字节)等去选择一个合适的索引去使用

Mysql在使用> <范围查询的时候会导致索引失效，但是在使用前缀匹配的范围不会失效如<= >= betwwen like等

什么时候使用索引

字段有一些唯一性，比如username，需要使用where快速定位的。经常用group by和order by的，因为索引都是排序好的就不需要在排序一次了。

什么字段不需要索引

where 和group by、 order by用不到的字段，因为索引的价值是快速定位。

当字段有大量重复内容是不需要加索引的。Mysql有一个优化，如果一个字段的内容重复率比较高，那么就会全表扫描。

经常更新的字段不需要建立索引

表数据太少的时候不需要建立索引。

合理的使用联合索引

select * from order where status = 1 order by create_time desc

这种情况要给status和create_time建立一个联合索引，这样只用到status索引还要对我们的time排序，这样就会using filesoft，这样根据status筛选出来的就是按照time排好序的，避免了在文件排序

索引的优化方案

前缀索引优化方案

​ 使用某个字段的前几个字符建立索引，优点就是减少索引大小，比较适合大字段作为索引的情况，缺点是group by无法利用这个前置索引，无法把前置索引作为覆盖索引。

覆盖索引优化

​ 就是我们要查出来的内容就是我们的索引的内容，比如我们联合查询(a,b,c)然后我们

select a,b,c from tables where a =12

这个时候我们就避免了一个回表找内存的操作，叶子节点直接找到了我们想要的数据。

主键最好是自增

如果我们主键是自增，每次插入数据都是追加操作，不需要重新移动数据。

如果非自增主键，我们每次插入的位置是不确定， 可能被插入了某个数据页的中间，这就不得不从启动其他数据到别的页来满足这个插入需求了，会导致页分裂，页分裂可能会导致大量的内存碎片，导致索引结构不紧密，影响查询效率。

比如当前有一个页[1,2,4,5],这时候我们要插一个3这时候就可能[1,2,null,null,null],[3,4,5,null,null] 导致了页分裂

索引设置NOT NULL

第一个原因，因为如果索引列为NULL，会导致优化器使用索引的时候更加复杂，更加难优化。因为NULL会列会使索引、索引统计和值比较都比较困难。在使用索引统计个数的时候，如果为null，会不作为个数相加。比较困难因为在null在sql中都是unkwon的。

tip：NULL一共有3种状态，真、假和未知

key_len

Key_len只是告诉你索引查询使用了哪些字段，而不是这个字段具体占了多少字节空间

索引执行计划参数 explain分析

• possible_keys 字段表示可能用到的索引；
• key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
• key_len 表示索引的长度；
• rows 表示扫描的数据行数。
• type 表示数据扫描类型，我们需要重点看这个。

其中的type

all是全表扫描

index是全索引扫描

range是索引范围扫描

ref是非唯一索引

eq_ref是唯一索引扫描

const 只有一条扫描索引，或者唯一扫描索引

为什么innodb选择B+Tree作为索引数据结构

B+Tree的叶子结点用双向链表连接起来，适合范围查找（B Tree无法做到这点）

B+Tree的高度很低，及时在千万级别的数据量下，依然维持到3-4层，也就是说一次查询只需要做3-4次io就可以找到目标数据

什么时候索引会失效

使用左模糊%xx 或者左右模糊%xx%

使用计算、函数、类型转换。

使用or