原创

MySQL索引


什么是索引

“索引(在MySQL中也叫“键key”)是存储引擎快速找到记录的一种数据结构。”

索引是表的目录,是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,以此快速定位查询数据。对于索引,会保存在额外的文件中

我们需要知道索引其实是一种数据结构,其功能是帮助我们快速匹配查找到需要的数据行,是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。

索引可以提高查询速度,会减慢写入速度,索引的缺点是创建和维护索引需要耗费时间。

索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insertupdate的效率,因为 insertupdate时有可能会重建索引,

所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过5个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

什么样的字段适合创建索引

1、表的主键、外键必须有索引,外键是唯一的,而且经常会用来查询;

2、经常与其他表进行连接的表,在连接字段上应该建立索引;

3、数据量超过300的表应该有索引;

4、重要的SQL或调用频率高的SQL,比如经常出现在where子句中的字段,order bygroup bydistinct的字段都要添加索引;

5、经常用到排序的列上,因为索引已经排序;

6、经常用在范围内搜索的列上创建索引,因为索引已经排序了,其指定的范围是连续的。

什么场景不适合创建索引

1、 对于那些在查询中很少使用或者参考的列不应该创建索引,这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

2、对于那些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了,所以没有必要。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

3、对于那些定义为textimagebit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。

4、当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。

5、不会出现在where条件中的字段不该建立索引。

6、如果列均匀分布在 1 和 100 之间,却只是查询中where key_part1 > 1 and key_part1 < 90不应该增加索引。

索引使用以及设计规范

1、越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂

2、尽量避免null:应该指定列为not null, 含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂,也可能导致复合索引无效

3、主键选择策略

​ 1) 每个表必须显示指定主键;

​ 2) 主键尽量为一个字段,且为数字类型,避免使用字符串;

​ 3) 主键尽量保持增长趋势,建议使用id的生成器;

​ 4) 主键尽量杜绝联合索引。

4、每个表的索引个数尽量少于5个,避免创建重复冗余索引;每个组合索引尽量避免超过3个字段,索引不是越多越好,谨慎添加索引,综合考虑数据分布和数据更新。

5、重要的SQL或调用频率高的SQL

​ 1) update/select/delete的where条件列字段都要添加索引;

​ 2) order bygroup bydistinct的字段都要添加索引。

6、避免出现index merge(单索引or的查询),合理利用covering index。

7、组合索引创建时,把区分度(选择性)高的字段放在前面;根据SQL的特性,调整组合索引的顺序。

8、对于varchar字段加索引,建议使用前缀索引,从而减小索引大小。

索引的类型

普通索引 INDEX:

仅加速查询,最基本的索引,没有任何限制,是我们大多数情况下使用到的索引。

ALTER TABLE table_name ADD INDEX index_name (column);

唯一索引 UNIQUE:

唯一索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

-- 创建唯一索引
ALTER TABLE table_name ADD UNIQUE (column);

-- 创建唯一组合索引
ALTER TABLE table_name ADD UNIQUE (column1,column2);

组合索引 INDEX:

即一个索引包含多个列,多用于避免回表查询,使用最左匹配原则。

ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);

主键索引 PRIMARY KEY:

它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。注意:一个表只能有一个主键。

全文索引 FULLTEXT:

也称全文检索,是目前搜索引擎使用的一种关键技术,作用于CHAR、VARCHAR、TEXT数据类型的列。

ALTER TABLE table_name ADD FULLTEXT (column);

索引的创建和删除

注意:对于创建索引时如果是blob 和 text 类型,必须指定length。

索引一经创建不能修改,如果要修改索引,只能删除重建。

创建表的时候同时创建索引:

create table table_name (
  id   bigint(20)  NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT  '主键',
  name  VARCHAR(32) NOT NULL COMMENT '姓名',
  email VARCHAR(64) NOT NULL COMMENT  '邮箱',
  message text DEFAULT  NULL COMMENT '个人信息',
  INDEX index_name (name) COMMENT '索引name'
) comment  = '索引测试表';

在存在的表上创建索引:

create index index_name on table_name(name)
create index index_name on table_name(message(200));

alter table table_name add index emp_name (name);

删除索引:

drop index_name on table_name;
alter table table_name  drop index  index_name ;

查看索引:

show index from table_name;

设计索引的原则

1)适合索引的列是出现在where子句中的列,或者连接子句中指定的列;

2)基数较小的类,索引效果较差,没有必要在此列建立索引;

3)使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间;

4)不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。

聚簇和非聚簇索引的区别

都是B+树的数据结构

  • 聚簇索引:将数据存储与索引放到了一块、并且是按照一定的顺序组织的,找到索引也就找到了数据,数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的
  • 非聚簇索引:叶子节点不存储数据、存储的是数据行地址,也就是说根据索引查找到数据行的位置再取磁盘查找数据,这个就有点类似一本树的目录,比如我们要找第三章第一节,那我们先在这个目录里面找,找到对应的页码后再去对应的页码看文章。

索引使用总结

1、联合索引:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上

2、单列索引:多个单列索引在多条件查询时只会生效第一个索引所以多条件联合查询时最好建联合索引

当创建(a,b,c)联合索引时,相当于创建了(a)单列索引,(a,b)联合索引以及(a,b,c)联合索引,想要索引生效的话,只能使用 a和a,b和a,b,c三种组合;当然,我们上面测试过,a,c组合也可以,但实际上只用到了a的索引,c并没有用到 ,具体 使用 a b c 的顺序无关,mysql会自动优化,但是我们建议按照索引的顺序进行查询,而且尽量将筛选力度大的放到前面。

3、使用 like 的查询

对于使用 like 的查询,只有%号不在第一个字符,索引才可能会被使用。

-- 最左侧是百分号,不使用索引
select * from table_name where column like '%我%'
--最左侧不是%,则使用索引
select * from table_name where column like '我%'

4、column_name is null

如果列是索引,使用 column_name is null 将使用索引。

select * from table_name where column is null

5、列是字符型,传入的是数字,则不加上'' 不会使用索引:

-- 列是字符型,传入数字,不会使用索引
select * from table_name where column = 1
-- 加上'',使用索引
select * from table_name where column = '1'
正文到此结束