mysql优化之索引

红太狼 2021-05-12 11:55 830阅读 0赞

## 概念: ##

在数据库中除了数据之外，还维护着满足特定查找算法的数据结构。这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。通常使用B树以及变种B+树来实现。简单来说索引就是一种B树或者B+树的数据结构。

> **一棵m阶的B树满足下列条件：**  
> ⑴ 树中每个结点至多有m个孩子；  
> ⑵ 除根结点和叶子结点外，其它每个结点至少有m/2个孩子；  
> ⑶ 若根结点不是叶子结点，则至少有2个孩子；  
> ⑷ 所有叶子结点都出现在同一层，叶子结点不包含任何关键字信息；  
> ⑸ 有k个孩子的非终端结点恰好包含有k-1个关键字。

**用java的思想来说：**

类似于student s=new student（） 这里的s相当于这些数据结构，这个student（）对象相当于实实在在的数据。

## 存放位置: ##

一般来说索引本身很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘中。

**MyISAM：**如果mysql使用的MyISAM这个引擎的话，索引文件应该存在.myi文件中。

**InnoDB：**如果mysql的存储引擎是InnoDB的话，索引文件应该存在.ibdata%这些文件中。

## 优缺点: ##

**优点：**提高数据检索的效率，降低数据的io成本，通过索引对数据进行排序，降低数据排序的成本，降低了cpu的消耗。索引主要功能就是：查找和排序。

> 索引就是通过事先排好序，从而在查找时候可以应用二分查找等高效的算法，一般的顺序查找复杂度为o(n),而二分查找的复杂度为o(log2n)，当n很大时，二者的效率差距很悬殊。  
> e：一百万条数据，如果顺序查找的话，平均需要查询50万条数据，而用二分法之多不超过20（2的20次方是104万）次就能找到。二者的效率查了2.5万倍。

**缺点：**实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，索引列也是要占用空间的；虽然索引大大提高了查询速度，同时却降低了更新表（update，insert，delete）的速度，因为更新表时，mysql不仅要保存数据，还要保存一下索引文件，每次更新添加索引列的字段，都会调整因为更新所带来的减值变化后的索引信息。

## 分类: ##

我们平常所说的索引，如果没有特别指明，都是指B树（多树搜索树，并不一定是二叉的）结构组织的索引，其中聚集索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B树索引，统称索引。除了B树这种类型的索引之外，还有哈希索引（hash index）、全文索引（fulltext）、R数索引。

**单值索引：**即一个索引只包含单个列，一个表可以有多个单值索引。

create  index  索引名字  on 表名 (字段名（长度）)

**唯一索引：**索引列的值必须唯一，但允许有空值。

create unique index 索引名字 on 表名 (字段名（长度）)

**复合索引：**一个索引包含多个列（最多貌似是16个）。一般都是建复合索引优于建单值索引，因为所需的开销更小,可以代替多个单一索引，还有就是mysql查询每次只能使用一个索引，复合索引就变成了变相的使用多个索引了。一般最常用作限制条件的列放在最左边，依次递减。

create  index  索引名字  on  表名 (字段1（长度），字段2（长度），字段3（长度）...)

## 索引失效: ##

**1、drop表：** drop将会删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；从而导致索引失效。

**2、like关键字：**如果查询的条件中使用了like关键字，并且%放在了第一个位置，则索引将会失效。

select id from cs_human where username like '%明';---失效的索引

select id from cs_human where username like '明%';---生效的索引

**注意：**  
1、如果非要将%放在第一个或者非要使用两个百分号，而且又想使用索引，就需要建立覆盖索引，就是查询的字段不能超过指定的字段（复合索引中的字段加上主键字段）。  
2、网上都说instr是能使用索引的，我真的是服了，你们真的测过吗？？？都睁眼看看这是什么吧（type是索引字段）。不过在都不使用索引的情况下，instr函数确实要比like快！记住：instr不会使用索引(不使用覆盖索引的情况下)！  
![这里写图片描述][70]

**3、复合索引：**在查询条件中使用了第一个字段（组成复合索引的那些字段）时候，复合索引才会生效，不然就会失效。  
例子：如果cs\_human 这张表有一个复合索引（userid\_age）

select * from cs_human where age=20 and orgid='111'---失效的索引

select * from cs_human where age=20 and userid='111'---生效的索引

**注意：**  
1、查询条件必须含有复合索引的第一个字段，否则整个复合索引失效。  
2、复合索引就像一列火车一样，假如复合索引是a\_b\_c，而查询条件是a=XXX and c=XX，并没有b，则复合索引只是部分生效而已，即只用到了a字段的索引，并没有用c字段索引。差不多可以理解为：火车头在，但是火车中间厢没了，后面的自然就不能跑了。  
3、范围之后全失效：比如复合索引是a\_b\_c，查询条件是a=XXX and b>20 and c=XX，这种情况下，a和b的索引将会被使用，但是c将不会被使用，而且索引级别将变成range。

**4、or关键字：**在查询条件中使用了or关键字，只有or前后两个字段都是索引列，索引才能生效，否则索引将不会生效。  
例子：如果cs\_human 这张表中，userid和age是索引列，而username不是索引列

select * from cs_human where age=20 or username='小明'---失效的索引

select * from cs_human where age=20 or userid='111'---生效的索引

**5、在索引列上进行计算：**索引失效的原因是索引是针对原值建的B树的，将列值计算后，原来的B树就用不上了；为了解决索引列上计算引起的索引失效问题，将计算放到索引列外的表达式上。

**6、null值：**只要列中包含有NULL值的行都将不会被包含在索引中（即索引中只有非null的行），复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。（个人猜想：因为null值对于mysql来说就是未知不确定，没法排序，但是C字段为null的这些行A字段又不为null，所以对于AC复合索引来说这些行必须要包含进来，只是C字段对复合索引来说是失效的）

**7、不使用NOT IN和<>操作：** NOT IN和<>操作都不会使用索引，将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

**8、短索引：** 如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

> 这里需要对索引的长度做一个声明：  
> 索引的长度的计算是根据表字段设定的长度来,对于复合索引就是将涉及到的字段长度加起来。  
> 在MyISAM的引擎中：索引的长度不得超过333字节。超过了也只取最前面的333字节  
> 在InnoDB的引擎中：索引的长度不得超过255字节。超过了也只取最前面的255字节

**9、类型不一致：** 比如username这个列定义的是varchar类型，但是查询的时候条件写了其他类型，这样也会使索引失效（这个问题在mysql5.5之后就不存在了）

> select \* from cs\_user where username=1;

**10、小总结：** 此图来自于尚硅谷  
![这里写图片描述][70 1]

## 是否需要建立索引: ##

**需要创建索引的情况：**

1.主键自动建立唯一索引  
2.频繁作为查询条件的字段应该创建索引  
3.查询中与其他表关联的字段，外键关系建立索引  
4.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度  
解释：建索引的时候需要考虑是否排序也是这个顺序  
比如，建了一个复合索引：名字，性别，地址 那么order的时候也是第一个排名字然后性别然后地址排序，这样是能大大提高排序速度的  
5.查询中统计或分组的字段建立索引—group by是分组，但是分组的前提是排序，也就是说group by和索引也是息息相关的，也要满足复合要求

**不能创建索引的情况：**

1.表单的记录太少的时候 ，2万条数据不需要建索引，据说mysql在500万以内还是比较牛逼的  
2.经常增删改查的表 因为：虽然提高了查询速度，同时会降低更新表的速度，如果对表进行增删改的话。更新表时mysql不仅要保存数据，还要保存一下索引文件  
3.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引  
注意：如果某个数据列包含许多重复的内容，为他建立索引就没有太大的实际效果，也就是说数据的差异性不高的字段建索引没有意义

> 比如是性别字段，国籍这种字段，再怎么都没有多大的差异性  
> 索引效率问题：  
> 假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度，索引的选择性是指索引列中不同值的数目与表中记录数的比，如果一个表中有2000条数据，表索引类由1980个不同的值，那么这个索引的选择性就是1980/2000=0.99  
> 一个索引的选择性越接近1，这个索引的效率就越高

4.where条件中用不到的字段不要创建索引

[70]: /images/20210511/7d9a892b98174e37b4f4abd9e65568b4.png
[70 1]: /images/20210511/952eb834275a4da69ac82011eda24a43.png