MySQL索引

火烈鸟

已于 2023-09-28 19:56:47 修改

阅读量550

点赞数

分类专栏： MySQL 文章标签： mysql

于 2022-04-04 00:56:34 首次发布

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、索引种类：

普通索引：这是最基本的索引类型，没唯一性之类的限制。

创建方式有三种：
创建索引，例如：CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表方式，例如：ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表时指定索引，例如：CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

唯一性索引：和普通索引基本相同，但所有的索引列只能出现一次，保持唯一性。

CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)

主键：跟唯一索引一样，不能有重复的列，但本质上，主键不能算是索引，而是一种约束，必须指定为"PRIMARY KEY"。它跟唯一索引的区别在于：

主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

唯一性索引列允许空值，而主键列不允许为空值。
主键列在创建时，已经默认为空值 + 唯一索引了。
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

全文索引：全文索引的索引类型为FULLTEXT，可以在VARCHAR或者TEXT类型的列上创建。在MySQL5.6以前的版本，只有 MyISAM 存储引擎支持全文索引，5.6及之后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引。

CREATE FULLTEXT INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)

联合索引：联合索引其实不是一种索引分类，就是包含多个字段的普通索引，比如有个联合索引为index(a，b)，查找的时候可以用 a and b 作为条件。

最左匹配原则：联合索引中，最左优先，以最左边的为起点任何连续的索引都能匹配上。就像上面说的index(a，b)或者是a单独作为查询条件都会走索引，但是如果是单独用 b 做查询条件就不会走索引了。或者是如果建立(a,b,c,d)顺序的索引的话，用a = 1 and b = 2 and c > 3 and d = 4这样的语句搜索，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

二、索引什么时候会失效

1、索引列用函数或表达式，比如这种

select * from test where  num  +  1 = 5

MySQL无法解析这种方程，这完全是用户的行为，应该把索引列当成独立的列，这样索引才会生效。

2、存在NULL值条件

select * from user where user_id is not null;

我们在设计数据库表时，应该尽力避免NULL值出现，如果数据有为空的情况可以给一个默认值，比如数值型的可以给0、-1，字符类型的可以给空字符串。

3、用or表达式作为条件，有一个列没有索引，那么其它列的索引将不起作用

select * from user where user_id = 700 or user_name = "老薛";

像这种，如果user_id有加索引，而user_name没有的话，那么执行的时候user_id的索引也是失效的，这也是为什么开发中尽量少用or的原因，除非是两个字段都加了索引。

4、列与列对比，某个表中，有两列(id和c_id)都建了单独索引，下面这种查询条件不会走索引

select * from test where id = c_id;

5、数据类型的转换。如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引

create index `idx_user_name` ON user(user_name)select * from user where user_name = 123;

像上面这种，虽然给user_name建立了索引，但查询的时候条件没有当成字符串，这样的话就不会走索引。

6、NOT条件

当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描，这类的查询条件有：<>、NOT、in、not exists

select * from user where user_id<>500;select * from user where user_id in (1,2,3,4,5);select * from user where user_id not in (6,7,8,9,0);select * from user where user_id exists (select 1 from user_record where user_record.user_id = user.user_id);

7、like查询是以%开头

当使用模糊搜索时，尽量采用后置的通配符，例如要查姓张的人，可以用user_name like ‘张%’，这样走索引时，可以从前面开始匹配索引列，但如果是这样user_name like ‘%张’，那么就会走全表扫描的方式

8、多列索引，遵循最左匹配原则，这个上面说了

三、什么时候该用索引

索引虽然能加快查询速度，但本身也会占用空间，所以，索引的创建并不是越多越好，为了使索引能有效应用，我们要把索引留给最有用的查询字段，一般来说，应该在这些字段上创建索引：

主键字段，这不用多说了吧；
经常需要搜索的列，比如where条件经常用到的字段；
其他表的外键字段，作为连接表的条件字段，可以有效加快连表查询速度；
查询中作为排序、统计或者是分组的字段；

同样，对于有些字段不应该创建索引，这些列包括

频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件
where条件里用不到的字段，不创建索引；
表记录太少，不需要创建索引；
对于那些定义为text，image类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少，不利于使用索引；
数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，这种字段建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。