当前位置：首页 > article >正文

MySQL优化二索引使用

article 2024/9/20 19:18:00

1、索引分类

类型	解释
全局索引(FULLTEXT)	全局索引，目前只有 MyISAM 引擎支持全局索引，它的出现是为了解决针对文本的模糊查询效率较低的问题，并且只限于 CHAR、VARCHAR 和 TEXT 列
哈希索引(HASH)	哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构，很适合作为索引。HASH 索引具有一次定位的好处，不需要像树那样逐个节点查找，但是这种查找适合应用于查找单个键的情况，对于范围查找，HASH 索引的性能就会很低。默认情况下，MEMORY 存储引擎使用 HASH 索引，但也支持 BTREE 索引
B-Tree 索引	B 就是 Balance 的意思，BTree 是一种平衡树，它有很多变种，最常见的就是 B+ Tree，它被 MySQL 广泛使用。
R-Tree 索引	R-Tree 在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对于 B-Tree 来说，R-Tree 的优势在于范围查找。

类型	解释
普通索引	普通索引是最基础的索引类型，它没有任何限制。创建方式如下 CREATE INDEX index_name ON table_name (column_name)，比如： `create index normal_index on cxuan003(id);` 删除方式如下： DROP INDEX index_name on column_name，比如： `drop index normal_index on cxuan003;`
唯一索引	唯一索引列的值必须唯一，允许有空值，如果是组合索引，则列值的组合必须唯一，创建方式如下 `create unique index normal_index on cxuan003(id);`
主键索引	是一种特殊的索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。
组合索引	指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则，下面我们就会创建组合索引。
全文索引	主要用来查找文本中的关键字，而不是直接与索引中的值相比较，目前只有 char、varchar，text 列上可以创建全文索引，创建表的适合添加全文索引，创建如下： `CREATE FULLTEXT INDEX index_content ON article(content)`

根据 id 和 hash 创建一个复合索引，然后根据 id 进行执行计划的分析

explain select * from cxuan005 where id = '333';

可以发现，即使 where 条件中使用的不是复合索引（Id 、hash），索引仍然能够使用，这就是索引的前缀特性。

如果 where 条件使用了 like 查询，并且 % 不在第一个字符，索引才可能被使用。比如：

走索引：explain select * from cxuan005 where id like '%1';

不走索引：explain select * from cxuan005 where id like '1%';

如果列名是索引的话，那么对列名进行 NULL 查询，将会触发索引。

explain select * from cxuan005 where id is null;

类型	解释
最简单的	如果使用索引后比不使用索引的效率还差，那么 MySQL 就不会使用索引
OR 条件	如果 SQL 中使用了 OR 条件，OR 前的条件列有索引，而后面的列没有索引的话，那么涉及到的索引都不会使用，比如 cxuan005 表中，只有 id 和 hash 字段有索引，而 info 字段没有索引，那么我们使用 or 进行查询，没有使用索引 `explain select * from cxuan005 where id = 111 and info = 'cxuan';`
列参与了计算	如果 where 条件的列参与了计算，那么也不会使用索引 `explain select * from cxuan005 where id + '111' = '666';`
列使用函数	索引列使用函数，一样也不会使用索引 `explain select * from cxuan005 where concat(id,'111') = '666';`
order by 操作	在 order by 操作中，排序的列同时也在 where 语句中，将不会使用索引。
隐式转换	当数据类型出现隐式转换时，比如 varchar 不加单引号可能转换为 int 类型时，会使索引无效，触发全表扫描
操作符	在索引字段上使用 <>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描
IS NOT NULL	在索引列上使用 IS NOT NULL 操作