Mysql （索引）_mysql索引-优快云博客

索引

概念

索引是帮助数据库高效获取数据的数据结构

前言

索引的优点：

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

索引的缺点：

索引会占用储存空间。
索引大大的提高了查询效率，同事却也降低了insert、update、delete的效率

一、数据结构

在看过前言之后，你肯定会好奇它到底是如何降低查询数据的效率，这就需要讲到数据结构了。

1、什么是数据结构

数据结构简单的可以理解为是为了提高效率的一种结构，其中对应不同的代码可以使用不同的数据结构来去提高它的效率。
其中Mysql因为运用的innodb引擎，而innodb引擎所使用的索引是B+Tree索引所以Mysql使用的是B+Tree索引。

2、B+Tree数据结构

数据结构图形化网站
在看B+Tree之前可以先去看**数据结构**方便理解。

④B+Tree与B+Tree（Mysql）

优点：对比B-Tree来说B+Tree解决了节点上会超出其最大值（16KB）的问题，同时也优化了大量数据层级高的问题。（B+Tree（Mysql）是在次此基础上添加了双向链表）
缺点：节点分裂和合并的过程相对复杂。当数据经常插入和删除时，维护成本较高。
原理：在B-Tree的结构上的子节点上添加了单向列表，且改变B-Tree的存储方式，B+Tree的数据一般只会存储在其子节点上。如图：

B+Tree（Mysql）的示意图：

二、创建索引（index）

1.语法

创建索引： create [unique(唯一)] index 索引名 on 表名(字段名，…);
查看索引： show index from 表名;
删除索引： drop index 索引名 on 表名;

1、索引

① 复合索引、单列索引

复合索引：聚集索引、二级索引
单列索引：主键索引、唯一索引、全文索引、普通索引

聚集索引：将数据存储与索引放到一起，索引结构的叶子节点保存了“行数据”（在创建索引时聚集索引必须有且是唯一的，若没有创建则默认将主键设置为聚集索引，若是主键也没有则将唯一的字段设置为聚集索引，如还是没有的话，则其数据行存储在一个称为堆的无序结构中。）
二级索引：二级索引是指在非主键列的其他列上建立的索引，将数据与索引分开存储，索引结构的叶子节点关联的是‘对应的主键’（可以存在多个）

主键索引（有时当没有设置聚集索引时他就是聚集索引）：
唯一索引：

全文索引：当你存储的数据时小说、论文等这些数据的文字特别多的时候这时就可以使用全文索引，它的查询效率比模糊查询快几倍。

普通索引：

②、覆盖索引

概念：这里的覆盖是指将原有的索引的基础上再去创建一个索引，其中此索引还可以再添加其他的字段。

注意：在查询数据时尽量避免使用（select *），而是用那些数据去查那些数据（select name,id），因为这样可以避免“回表查询”浪费时间

③、前缀索引

概念：当存储的数据有大量的文本数据时，就可以使用前缀索引来提高查询效率。
基础语法： create index indexName tableName(列名(前几个字符));
前几个字符：此属性的意思是在创建index时，我存储的数据内容是字段数据的前几个字符，在去查询时可以明显的体会到效率有所提高。
列如：

如何判断到底取几个字符来去存储到索引中，这里有两个公式可以很好的判断出来：
判断此列数据不相同的比例：select count (distinct 对应的列名 ) / count(*) from tableName;
列如：

判断截取的字段在此列中有多少重复的（1：无重复，数字越小重复率越高）：select count (distinct substring(列名,begin（截取的begin）,end（截取到end个字符） )) / count(*) from tableName;
列如：

三、sql提示

概念

sql提示：当索引过多时，一列数据有设置了两个索引（联合索引，单例索引），这时就可以使用sql提示去指定sql去使用那个索引

基础语法

建议sql使用某一个索引：explain select * from tableName use table(indexName) …;
（sql会将你的建议进行对比如果你的建议确实更好或者两个相同，便会采取你的建议）
禁止sql使用某一个索引：explain select * from tableName ignore table(indexName) …;
强制sql使用某一个索引：explain select * from tableName force table(indexName) …;

执行计划

语法：explain/desc select …
执行计划各个字段的含义： id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序
(id相同，执行顺序从上到下:id不同，值越大，越先执行)。
select_type:表示 SELECT 的类型，常见的取值有 SIMPLE(简单表，即不使用表连接或者子查询)、
PRIMARY(主查询，即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、
SUBOUERY(SELECT/WHERE之后包含了子查询)等
type：表示连接类型，性能由好到差的连接类型为：null、system、comst、eq_ref、ref、range、index、all
possible_key:显示可能应用在这张表上的索引，一个或多个
key：实际使用的索引，如果为Null，则没有使用索引。
key_len：表示索引中的使用的字节数，该值为索引字段最大可能的长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
rows：mysql认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered：表示返回结果的行数占用需读取行数百分比，filtered值越大越好。

四、索引的使用

1.最左前缀法则：

概念：最左（这里的最左是在创建变量的最左）前缀法则，当去查询储存在聚集索引中的数据时，若没有出现或违背了规则则不会使用此聚集索引
列如：

图一：

图二

图三：

在图一创建index_cid_name索引的时候可以看到最左边的字段为cid，由最左前缀法则可知只要查询时没有出现cid字段，就不会使用次索引（实例的图二和图三也可以证明这个法则的准确性）

2.范围查询

概念：范围查询（常在联合（有多个字段）索引中出现）：当有范围（>,<）查询时,则它右边的列索引失效，所以在条件允许的情况下最好使用（>=.<=）
以此表为例：

创建索引：

使用explain判断范围查询：

这里可以看到，使用explain时返回的key_len值是不同的就可以证明在使用判断（<、>）时，最右边的字段name的判断是没有走索引的。