MySQL索引整理总结

 

一、索引是什么？

二、索引类型

三、索引的优化

 

一、索引是什么？

    是一种特殊文件，包含数据表里所有的引用指针。好比一本书的目录。

    索引，分为 聚簇索引，非聚簇索引 两大类，都是引用的 B+ 数索引。

    聚簇索引：也叫聚集索引，能提高 多行检索 的速度。类似新华字典，正文内容本身就是按照一定规律排列的目录。

    非聚簇索引：也叫非聚集索引，对于 单行检索 很快。这种目录纯粹是目录，正文纯粹是正文的排列方式。

 

聚集索引的形成：

    InnoDB 要求表必须有主键（MyISAM 可以没有），InnoDB 会按照以下规则进行处理：

      1.如果有一个主键被定义，那么这个主键就作为，聚集索引。

      2.如果没有主键定义，那么该表第一个，唯一非空索引就被作为，聚集索引。

      3.如果，没有主键，也没有合适唯一索引，那么 InnoDB 内部就会生成一个隐藏的主键作为聚集索引，这个索引是一个6字节的列，该列的值会随着数据插入自增。

      但是，业务角度上来说，自增ID列相对没什么意义，而聚集索引一个表只能有一个。所以这里引入了非聚集索引的概念。

 

非聚集索引：

    定义的原则往往是基于业务逻辑，非聚集索引在物理地址上不相邻，更像是一个数据字典索引。比聚集索引慢，但是表中可以建立多个。

 

    BTREE：默认，二叉树形式存储数据。

    HASH：一次定位，类似键值对形式存储，唯一。（只有“=”，“in”条件下有效，效率不高）

 

二、索引类型

1.普通索引：最基本索引，没有限制。

CREATE INDEX indexName ON table(column(length))

 

2.唯一索引：索引列必须唯一，但允许有空值（与主键不同），组合索引，则列值组合必须唯一

CREATE UNIQUE INDEX indexName ON table(column(length))

 

3.全文索引（FULL TEXT）：仅可用于 MyISAM 引擎，针对 CHAR，VARCHAR，TEXT 列，吃资源。

CREATE FULLTEXT INDEX index_content ON article(content)

比使用 like 去逐行检索要快，因为建立倒排索引，极大的提升检索效率，用于全文搜索，是文档检索系统中最常用的数据结构。

 

4.单列索引、多列索引：多个单索引，与单个多索引不一样，因为查询时，mysql 只能是用一个索引，会从多个索引中选择一个限制最严格的索引。

 

5.组合索引（最左前缀）：平常 SQL 查询一般都有多个限制条件，为了进一步提升效率，就要考虑组合索引。

    ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))

    此时，相当于分别建立了下面两组，组合索引：

      -- title，time

      -- title

    为什么没有 time 的组合索引呢？因为“最左前缀”：就是从最左面开始组合，例如

      -- 使用上述索引

      select * from article where title="程序猿" and time=20200313;

      select * from article where title="程序猿"；

      -- 未使用上述索引

      select * from article where time=20203013;

 

三、索引的优化

索引能大大提升查询效率，也会降低更新表的速度，如 INSERT/UPDATE/DELETE。因为保存数据同时，也要保存索引文件。

1.索引不包含 NULL 值列

只要列中有 NULL，就不会包含在索引中，所以最好索引列默认不为 NULL。复合索引也会因 为 NULL 列而失效。

 

2.使用短索引

对串列进行索引，最好指定前缀长度。如，有一个CHAR(255)的列，如果在前10个或者20个字符内，多数值是唯一的，那么就不需要对整个列进行索引。

短索引，不近可以提升查询效率，还能节省磁盘空间，和 I/O 操作。

 

3.索引列排序

MySQL 查询只用一个索引，即 where 中使用，则 order by 就不使用索引了。如果能使用默认排序，就不要添加排序。尽量不要使用多个列排序，如果需要则添加复合索引。

 

4.like 语句操作

不鼓励使用 like，若非使用不可，like "%aaa%" 不使用索引，like "aaa%" 使用索引。

 

5.不要在列上进行运算

例如：select * from users where YEAR(add_date)<2007; --将每行计算，索引失效

改成：select * from users where add_date<'2007-01-01';

MySQL 只对以下操作符使用索引：<,<=,=,>,>=,between,in 以及某些时候的 like（不加通配符%）。

理论上，每张表最多可创建 16 个索引，除非数据量真的多，要不不要轻易添加索引。

 

6.不要使用 NOT IN 和 <> 操作