索引优化

索引优化

索引是排好序的快速查找的数据结构（order by和where）

 

创建索引的好处

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

 

创建索引的坏处

1、实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

2、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

3、索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。

 

 

索引分类

1、单值索引

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

2、唯一索引

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

3、复合索引

即一个索引包含多个列

 

索引结构

B+树

在B树上再进行了优化，

1、data信息由节点移动到了叶子节点上，而非叶子节点只存储键值信息。

2、叶子之间，增加了链表，获取所有节点，不再需要中序遍历。

聚簇索引和非聚簇索引的区别

聚簇索引（innodb）：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。聚簇索引之上创建的索引称之为辅助索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找，先通过找到主键值，再用主键值找到具体存储数据。

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

非聚簇索引（MyISAM）：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

 

 

创建索引的情况

1、主键自动创建唯一索引

2、频繁作为查询条件的字段

3、查询中与它关联的字段

4、频繁更新的字段不适合创建

5、where条件用不到的不添加

6、高并发情况下创建复合索引好

7、查询中排序字段

8、查询中统计或者分组字段

 

不创建索引的情况

1、表记录太少

2、经常增删改的表

3、数据类别太少的字段（相当于性别，数据的差异不高，对这种字段建索引，一般不会提高查询速度）

 

 

Explain查看sql执行计划

id、type、possible_keys、key、rows、Extra

id：数字大的越先执行，id如果相同，可以认为是一组，从上往下顺序执行

type：从最好到最差system>const>eq_ref>ref>range>index>ALL，index是从索引中读取，ALL:Full Table Scan，将遍历全表以找到匹配的行

possible_keys、key：possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中。

rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

 

Extra：Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MysQL中无法利用索引完成的排序操作称为"文件排序”。（九死一生）

Using temporary：使了用临时表保存中间结果，MysQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。（十死无生，要么不建索引，要么按索引顺序使用）

USING index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找。如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

覆盖索引(Covering Index) ,一说为索引覆盖。理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。

注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

 

for update

如果查询条件用了索引/主键，那么select ..... for update就会进行行锁。

如果是普通字段(没有索引/主键)，那么select ..... for update就会进行锁表。

索引的优化

单表

范围查询会导致复合索引出现Using filesort去掉复合索引的范围字段。

 

两表

左关联时，我们要为右边的表字段创建索引（因为左边的数据是一定有的）

 

 

索引失效

最佳左前缀法则（相当于火车，没有火车头就跑不起来）：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引（中间不能断）中的列。

左%号会导致索引失效

解决like "%字符串%'时索引不被使用的方法??使用覆盖索引，建的索引和查询的字段，查询字段个数必须小于等于索引

隐式类型转换，字符串不加''

使用函数，!=，or，is null，is not null

 

 

一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几

InnoDB，不重启MySQL，如果新增一条记录，id是8；重启，ID是6；因为InnoDB表只把自增主键的最大ID记录在内存中，如果重启，已删除的最大ID会丢失。

MyISAM，重启之后，最大ID也不会丢失，ID是8；