mysql索引与md5_MySQL系列(六)--索引优化

在进行数据库查询的时候，索引是非常重要的，当然前提是达到一定的数据量。索引就像字典一样，通过偏旁部首来快速定位，而不是一页页

的慢慢找。

索引依赖存储引擎层实现，所以支持的索引类型和存储引擎相关，同一种索引底层实现在不同存储引擎也是不一样的

本文基于MySQL8.0版本，关于explain用法，可以参考：MySQL高级 之 explain执行计划详解

创建索引语法：

CREATE TABLE table_name[col_name data_type]

[UNIQUE|FULLTEXT|SPATIAL]

[INDEX|KEY]

[index_name](col_name[length])

[ASC|DESC]

参数：

1、UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引

2、INDEX和KEY为同义词，二者作用相同，用来指定创建索引

3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择

4、index_name为指定索引的名称，为可选参数，如果不指定则MySQL默认col_name为索引

5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果数据列很长的话，MySQL不允许把整列作为索引

6、ASC或DESC指定升序或者降序的索引值存储

主键：主键索引是特殊的唯一索引，不能为null，建表时指定主键，只能有一个。

唯一索引：索引列的值必须唯一，包含联合索引，允许为null。

一般索引：最普通的索引，没有限制，相对于前两种效率最差。

全文索引：用于全文搜索，V5.7版本之后，MyIsam和InnoDB支持，只能用于char、varchar、Text列

除了建表时创建索引，还有两种方式：

1、Alter TABLE table_name ADD PRIMARY KEY (column_name)/UNIQUE  [indexName] (column_name)/INDEX index_name (column_name)/FULLTEXT (column_name)

2、CREATE UNIQUE INDEX index_name/INDEX index_name ON table_name (column_name)　　不适用于primary key和FULLTEXT

索引分类：

1、B-tree索引：除了archive以外的存储引擎，都支持

使用B+树的数据结构实现来存储数据，能够加快数据的查询速度，从索引的根节点开始往下搜索

B-tree索引的数据是顺序存储的，所以适合范围查找

使用场景：

1、全值匹配的查询，例如：id='1001'

2、匹配最左前缀的查询，例如现在把id和name建立一个联合索引，这时候查询id='1001'可以使用到联合索引，因为id为这个索引最左字段，

但是如果通过name进行筛选，就无法用到联合索引

3、匹配列前缀查询，例如：id like '100%'也可以用到联合索引

4、匹配范围值的查询，例如id < '1001' and id > '1010'

5、精确匹配左前列并范围匹配另一列

6、值访问索引的查询(覆盖索引)

总结：对于联合索引，MySQL从左向右匹配知道遇到范围查询(>,

个人使用MySQL8.0版本，测试发现范围匹配照样可以使用索引

使用限制：

1、不使用索引最左列的查询，无法使用到联合索引

2、使用索引时不能跳过索引中的列

3、not in和<>无法使用索引

4、索引中有某个列使用了范围查找，则右边的所有列都无法使用索引

PS：不仅可以在where查询中使用，也可以使用在order by和group by中

2、Hash索引：

Memory存储引擎默认的索引，InnoDB也有Hash索引，这是InnoDB自动建立

Hash索引是基于Hash表实现的，对于Hash索引中所有列，存储引擎为每一列计算一个hash值，hash索引存储的就是hash码

理论上，效率还是好于B树索引，但是其使用限制太多了

使用限制：

通过hash索引找到对应的行，然后对行的数据进行读取，进行两次查找

无法用于避免数据的排序操作

无法使用部分索引键的查询

<