mysql索引原理以及如何正确使用索引

mysql索引

什么是索引

索引的本质其实就是排好序的快速查找数据结构，就像查字典，你可以根据拼音去快速的检索你所需要查询的汉字。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上。

优势

提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为 更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为 更新所带来的键值变化后的索引信息。 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，索引列也是要占用空间的。所以索引并不是越多越好。

所以索引不是越多越好。

如图就是我们的B+树，mysql最常用的索引。

根据B+树的特性，例如我们需要查找9这个数据，先去找根节点磁盘块1，9小于17，所以在P1，再查找P1所在的磁盘块2，9大于8，所以再找到磁盘块2中的P2，找到P2所在的磁盘块6，然后就找到了9了。

mysql的索引分类：

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引: 索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列。优先使用

基本语法

创建索引

CREATE [UNIQUE] INDEX  indexName ON mytable(columnname(length));
或
ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname(length));

如果是CHAR,VARCHAR类型，length可以小于字段实际长度；
如果是BLOB和TEXT类型，必须指定length。

删除索引

DROP INDEX [indexName] ON mytable;

查看索引

SHOW INDEX FROM table_name\G

什么情况需要建立索引

1.主键自动建立唯一索引
2.频繁作为查询的条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
5.查询中统计或者分组字段

什么情况不适合建立索引

1.表记录太少
2.经常增删改的表
3.数据重复且分布平均的表字段，其实就是索引的辨识度不高，这时候加了索引效果不明显。

执行计划的查看EXPLAIN

explain能干什么：
1.查看表的读取顺序
2.数据读取操作的操作类型
3.哪些索引可以使用
4.哪些索引被实际使用
5.表之间的引用
6.每张表有多少行被优化器查询

怎么用：
explain+sql语句

执行explain后的结果：

怎么去看。

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同同时存在，按照前两条的规则执行

select_type

查询的类型，主要用于区别
普通查询、联合查询、子查询等的复杂查询

table

查询的是哪一张表。

type：重要，一般来说，得保证查询只是达到range级别，最好达到ref。

显示查询使用了何种类型
从最好到最差依次是：
system>const>eq_ref>ref>range>index>ALL

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键至于where列表中，MySQL就能将该查询转换为一个常量

eq_ref:唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描，在联表查询时出现

ref：非唯一索引扫描，返回匹配某个单独值的所有行。
本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，
它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等的查询
这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束语另一点，不用扫描全部索引

index：全索引扫描,index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

all：全表扫描,将遍历全表以找到匹配的行

possible_keys：

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

key:

实际使用的索引。如果为null则没有使用索引

key_len

表示索引中使用的字节数。数值越大，证明索引用的越充分，多见于字符串的匹配

ref

显示索引那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值

row

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

Extra

其他信息。几种常见的情况
Using filesort ：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作成为“文件排序”，用来排序的字段没有索引时出现。

Using temporary
使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by

Using index
表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！覆盖索引，即要查的字段在索引中就能获取到，避免了在磁盘上查询数据。
如果同时出现using where，表明索引被用来执行索引键值的查找；
如果没有同时出现using where，表面索引用来读取数据而非执行查找动作。

Using where
使用了where过滤。

正确的使用索引

建立索引 CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

1 最左前缀原则

如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
首先是能用到索引的情况。当你的where条件能按照索引建立的顺序被找到的时候你都是能用到索引的，不管你的条件有几个，顺序对不对，顺序不对的话mysql会帮你自动优化，其实也是使用最左前缀原则


下面的情况就不能用到索引，当你跳过了前面的某个索引时，你的索引就会失效，是用不了索引

当你在前面的索引使用了范围查询的时候，会导致后面的索引失效

2.不要在索引列上进行计算和转换

在等号的左边进行运算，会使索引失效

name是字符串类型，但你给了数字，虽然mysql不会报错，但索引会失效

3.使用!=,><无法使用索引

4. like 的前后模糊匹配

前面加%无法使用索引，后面加%就能用到索引

5 使用or无法使用索引

当使用or时，索引也用不了，使用 union all 或者 union 来替代