Mysql索引

目录

一、什么是索引

二、什么情况下需要使用索引

三、索引在哪些情况下会失效

四、SQL索引执行情况

五、下面是查看索引使用情况：

六、索引类型type

---

一、什么是索引

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据；拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使

用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

1.1：索引具体采用的哪种数据结构

常见的MySQL主要有两种结构：Hash索引和B+ Tree索引，InnoDB/Myisam引擎，默认的都是B+树；MyISAM不支持哈希索引，而InnoDB中的hash索引是存储引擎根据B-Tree索引自建。

1.2：hash索引和b+ tree索引区别

哈希索引适合等值查询，但是不无法进行范围查询 哈希索引没办法利用索引完成排序 哈希索引不支持多列联合索引的最左匹配规则 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

二、什么情况下需要使用索引

（1）列经常被用于where条件中

（2）列中有大量的空值

（3）表几乎没有被修改

（4）数据量很大，只有2-4%的数据被选出来

三、索引在哪些情况下会失效

（1）对列进行计算或者是使用函数，则该列的索引会失效

（2）不匹配数据类型，会造成索引失效

（3）where语句中使用了IS NULL或者IS NOT NULL，会造成索引失效

（4）使用了反向操作，该索引将不起作用

（5）使用了link操作，索引就将不起作用

（6）在WHERE中使用OR时，有一个列没有索引，那么其它列的索引将不起作用

四、SQL索引执行情况

EXPLAIN SQL;

五、下面是查看索引使用情况：

show global status like '%Handler_read%';不加global默认是show session status like '%Handler_read%';

1. handler_read_key:这个值越高越好，越高表示使用索引查询到的次数  
2. handler_read_rnd_next:这个值越高，说明查询低效 

六、索引类型type

首先类型有许多，这里我只给大家介绍企业里面用的最多的类型：

system>const>eq_ref>ref>range>index>ALL

查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < const < system

越往右边，性能越高，比如system就比ALL类型性能要高出许多，其中system、const只是理想类型，基本达不到；

我们自己实际能优化到ref>range这两个类型，就是你自己写SQL，如果你没优化基本上就是ALL，如果你优化了，那就尽量达到ref>range这两个级别；

ALL：全表扫描，对于数据表从头到尾找一遍select * from 表名，特别的：如果有limit限制，则找到之后就不再继续向下扫描 select * from 表名 where name = '张三' limit 1;虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。

INDEX：全索引扫描,查询全部索引中的数据，对索引从头到尾找一遍,select id from 表名-->你把id这一列给查了一遍前提是ID有索引。
       RANGE：对索引列进行范围查找select *  from 表名 where id < 10;
                        PS:between and in >   >=  <   <=  操作
                        注意：in 有时会失效，导致为ALL；

INDEX_MERGE：合并索引，使用多个单列索引(index)搜索select *  from 表名 where name = 'xx' or id in (11,22,33);
       REF ：根据索引查找一个或多个值select *  from 表名 where name = 'xx';
       EQ_REF: 连接时使用primary key(主键) 或 unique(外键)类型select 表1.id,表2.name from 表1 left join 表2 on 表1.id = 表2.nid;
      CONST：表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次，即只有一条数据会匹配。select * from tb1 where id = 2 ;
     SYSTEM： 表仅有一行(=系统表)。这是const联接类型的一个特例。

七、结语

参考文章：https://blog.csdn.net/ssspk_/article/details/89422085

https://www.runoob.com/mysql/mysql-index.html