MYSQL索引优化

一，索引的优点

1，大大减少了服务器需要扫描的数据量

2，帮助服务器避免排序和临时表

3，将随机IO编程顺序IO

 

二，索引的用处

 

1，快速查找匹配WHERE子句的行；

2，从consideration中消除行，可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引；

3，如果表具有多列索引，则优化器可以使用索引的任何左前缀来查找行；

4，当表连接时，从其他表检索行数据；

5，查找特定索引列的min或max值；

6，如果排序或分组时可用索引的最左前缀上完成的，则对表进行排序和分组；

7，在某些情况下，可以优化查询以检索值而无需查询数据行。

三，索引分类

 

主键索引

唯一且非空

唯一索引

普通索引    *

全文索引 

varchar  char  一般不用

组合索引

 

 

四，面试技术名词

 

回表

普通了创建索引，普通索引叶子节点存储的并不是行的数据，放的是主键。

第一次查找普通列的B+Tree ，从中取到主键，然后再去查主键的B+Tree，最后去除整行数据。

回去主键B+Tree中找数据较回表。

 

覆盖索引

每次查询数据时每次需要查询主键，相当于查询了两次B+Tree，IO比较麻烦

SELECT  *  FROM ， SELECT ID FROM 。 ID已经确定的情况下，不需要再去主键B+Tree中查找数据。组合索引中用的比较多，要尽量使用覆盖索引。例如：  in  ( select  id  from  where ...) 

SELECT *  FROM  EMP  WHERE NAME =1;

先查询NAME的B+Tree找到对应的ID，然后在查询ID的B+Tree找到对应的数据。

SELECT ID FROM  EMP  WHERE NAME =1;

先查询NAME的B+Tree找到对应的ID，不用回表直接返回ID，这个叫覆盖索引

最左匹配

多列创建索引

SELECT *  FROM  EMP  WHERE NAME =? AND AGE=?

索引生效

SELECT * FROM EMP  WHERE  AGE =?

索引失效

 

但要两个都要实现

索引也需要持久化，会占用磁盘空间，选择占用空间更小的方案

1   name  age          age        *

2   age  name         name

 

索引下推

用于组合索引  ，5.6版本后。

WHERE   name  like 'A%'  and age =20

 

 

 

索引合并

 

索引页合并 页分裂

...待持续