Mysql索引整理总结

一、索引概述

1. 简介

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。

举例说明索引:如果把数据库中的某一张看成一本书,那么索引就像是书的目录,可以通过目录快速查找书中指定内容的位置,对于数据库表来说,可以通过索引快速查找表中的数据。

2. 索引的原理

索引一般以文件形式存在磁盘中(也可以存于内存中),存储的索引的原理大致概括为以空间换时间,数据库在未添加索引的时候进行查询默认的是进行全量搜索,也就是进行全局扫描,有多少条数据就要进行多少次查询,然后找到相匹配的数据就把他放到结果集中,直到全表扫描完。而建立索引之后,会将建立索引的KEY值放在一个n叉树上(BTree)。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表,每次以索引进行条件查询时,会去树上根据key值直接进行搜索。

3. 索引的优点

建立索引的目的是加快对表中记录的查找或排序!

① 建立索引的列可以保证行的唯一性,生成唯一的rowId

② 建立索引可以有效缩短数据的检索时间

③ 建立索引可以加快表与表之间的连接

④ 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

4. 索引的缺点

① 创建索引和维护索引需要时间成本,这个成本随着数据量的增加而加大

② 创建索引和维护索引需要空间成本,每一条索引都要占据数据库的物理存储空间,数据量越大,占用空间也越大(数据表占据的是数据库的数据空间)

③ 会降低表的增删改的效率,因为每次增删改索引需要进行动态维护,导致时间变长

二、索引的使用场景

数据库中表的数据量较大的情况下,对于查询响应时间不能满足业务需求,可以合理的使用索引提升查询效率。

三、索引的分类和创建和修改删除等命令

1. 基本索引类型

① 普通索引(单列索引)

② 复合索引(组合索引)

③ 唯一索引

④ 主键索引

⑤ 全文索引

2. 创建的语句

CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

  • unique|fulltext为可选参数,分别表示唯一索引、全文索引

  • index和key为同义词,两者作用相同,用来指定创建索引

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

  • index_name指定索引的名称,为可选参数,如果不指定,默认col_name为索引值

  • length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度

  • asc或desc指定升序或降序的索引值存储

3. 索引的创建、查询和删除

索引的创建

① 普通索引(单列索引)

普通索引(单列索引):单列索引是最基本的索引,它没有任何限制。

(1)直接创建索引

CREATE INDEX index_name ON table_name(col_name);

(2)修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

(3)创建表的时候同时创建索引

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(255))
)

(4)删除索引

DROP INDEX index_name ON table_name;
或者
alter table `表名` drop index 索引名;

② 复合索引(组合索引)

复合索引:复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则即在查询条件中使用了复合索引的第一个字段,索引才会被使用。因此,在复合索引中索引列的顺序至关重要。

(1)创建一个复合索引

create index index_name on table_name(col_name1,col_name2,...);

(2)修改表结构的方式添加索引

alter table table_name add index index_name(col_name,col_name2,...);

③ 唯一索引

唯一索引:唯一索引和普通索引类似,主要的区别在于,唯一索引限制列的值必须唯一,但允许存在空值(只允许存在一条空值)

如果在已经有数据的表上添加唯一性索引的话:

  • 如果添加索引的列的值存在两个或者两个以上的空值,则不能创建唯一性索引会失败。(一般在创建表的时候,要对自动设置唯一性索引,需要在字段上加上 not null)
  • 如果添加索引的列的值存在两个或者两个以上的null值,还是可以创建唯一性索引,只是后面创建的数据不能再插入null值 ,并且严格意义上此列并不是唯一的,因为存在多个null值。

对于多个字段创建唯一索引规定列值的组合必须唯一。
比如:在order表创建orderId字段和 productId字段 的唯一性索引,那么这两列的组合值必须唯一!


“空值” 和”NULL”的概念: 
1:空值是不占用空间的 .
2: MySQL中的NULL其实是占用空间的.

长度验证:注意空值的之间是没有空格的。

> select length(''),length(null),length(' ');
+------------+--------------+-------------+
| length('') | length(null) | length(' ') |
+------------+--------------+-------------+
|          0 |         NULL |           1 |
+------------+--------------+-------------+

(1)创建唯一索引

# 创建单个索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);

# 创建多个索引
CREATE UNIQUE INDEX index_name on table_name(col_name,...);

(2)修改表结构

# 单个
ALTER TABLE table_name ADD UNIQUE index index_name(col_name);
# 多个
ALTER TABLE table_name ADD UNIQUE index index_name(col_name,...);

(3)创建表的时候直接指定索引

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    UNIQUE index_name_unique(title)
)

④ 主键索引

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

(1)主键索引(创建表时添加)


CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`)
)

(2)主键索引(创建表后添加)

alter table tbl_name add primary key(col_name);

CREATE TABLE `order` (
    `orderId` varchar(36) NOT NULL,
    `productId` varchar(36)  NOT NULL ,
    `time` varchar(20) NULL DEFAULT NULL
)

alter table `order` add primary key(`orderId`);

⑤ 全文索引

在一般情况下,模糊查询都是通过 like 的方式进行查询。但是,对于海量数据,这并不是一个好办法,在 like "value%" 可以使用索引,但是对于 like "%value%" 这样的方式,执行全表查询,这在数据量小的表,不存在性能问题,但是对于海量数据,全表扫描是非常可怕的事情,所以 like 进行模糊匹配性能很差。

这种情况下,需要考虑使用全文搜索的方式进行优化。全文搜索在 MySQL 中是一个 FULLTEXT 类型索引。FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB,而之前的版本只支持 MyISAM 表

全文索引主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。目前只有char、varchar,text 列上可以创建全文索引。

小技巧:
在数据量较大时候,先将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。

(1)创建表的适合添加全文索引

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` text  NOT NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
     PRIMARY KEY (`id`),
    FULLTEXT (content)
)

(2)修改表结构添加全文索引

ALTER TABLE table_name ADD FULLTEXT index_fulltext_content(col_name)

(3)直接创建索引

CREATE FULLTEXT INDEX index_fulltext_content ON table_name(col_name)

注意: 默认 MySQL 不支持中文全文检索!

MySQL 全文搜索只是一个临时方案,对于全文搜索场景,更专业的做法是使用全文搜索引擎,例如 ElasticSearch 或 Solr。

索引的查询和删除

#查看:
show indexes from `表名`;
#或
show keys from `表名`;
 
#删除
alter table `表名` drop index 索引名;


注:MySQl的客户端工具也可以进索引的创建、查询和删除,如 Navicat Premium!

四、简单实例演示

查看索引使用情况

show status like 'Handler_read%';

handler_read_key:这个值越高越好,越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高,说明查询低效

常见索引失效的情况:

创建一个students表:
其中stud_id为主键!

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `stud_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(1) NOT NULL,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`stud_id`)
 
)

INSERT INTO `learn_mybatis`.`students` (`stud_id`, `name`, `email`, `phone`, `create_date`) VALUES ('1', 'admin', 'student1@gmail.com', '18729902095', '1983-06-25');
INSERT INTO `learn_mybatis`.`students` (`stud_id`, `name`, `email`, `phone`, `create_date`) VALUES ('2', 'root', '74298110186@qq.com', '2', '1983-12-25');
INSERT INTO `learn_mybatis`.`students` (`stud_id`, `name`, `email`, `phone`, `create_date`) VALUES ('3', '110', '7429811086@qq.com', '3dsad', '2017-04-28');

使用 explain 查看 索引是否生效!Mysql中explain用法和结果字段的含义介绍

1. 在where后使用or,导致索引失效(尽量少用or)

简单实例演示:
创建两个普通索引,

CREATE INDEX index_name_email ON students(email);
CREATE INDEX index_name_phone ON students(phone);

使用下面查询sql,

# 使用了索引
EXPLAIN select * from students where stud_id='1'  or phone='18729902095'
# 使用了索引
EXPLAIN select * from students where stud_id='1'  or email='742981086@qq.com'

#--------------------------

# 没有使用索引
EXPLAIN select * from students where phone='18729902095' or email='742981086@qq.com'

# 没有使用索引
EXPLAIN select * from students where stud_id='1'  or phone='222' or email='742981086@qq.com'


2.使用like ,like查询是以%开头

在1的基础上,还是使用 index_name_email 索引。

使用下面查询sql

# 使用了index_name_email索引
EXPLAIN select * from students where email like '742981086@qq.com%'

# 没有使用index_name_email索引,索引失效
EXPLAIN select * from students where email like '%742981086@qq.com'

# 没有使用index_name_email索引,索引失效
EXPLAIN select * from students where email like '%742981086@qq.com%'
3.复合索引遵守“最左前缀”原则,即在查询条件中使用了复合索引的第一个字段,索引才会被使用

删除1的基础创建的 index_name_email 和 index_name_phone 索引。

重新创建一个复合索引:

create index index_email_phone on students(email,phone);

使用下面查询sql

# 使用了 index_email_phone 索引
EXPLAIN select * from students where email='742981086@qq.com' and  phone='18729902095'

# 使用了 index_email_phone 索引
EXPLAIN select * from students where phone='18729902095' and  email='742981086@qq.com'

# 使用了 index_email_phone 索引
EXPLAIN select * from students where email='742981086@qq.com' and name='admin'

# 没有使用index_email_phone索引,复合索引失效
EXPLAIN select * from students where phone='18729902095' and name='admin'

4. 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引

给name创建一个索引!

CREATE INDEX index_name ON students(name);

# 使用索引
EXPLAIN select * from students where name='110'

# 没有使用索引
EXPLAIN select * from students where name=110

5. 使用in导致索引失效
# 使用索引
EXPLAIN select * from students where name='admin'

# 没有使用索引
EXPLAIN SELECT * from students where name in ('admin')

6. DATE_FORMAT()格式化时间,格式化后的时间再去比较,可能会导致索引失效。

删除 students 上的创建的索引!重新在create_date创建一个索引!

CREATE INDEX index_create_date ON students(create_date);

# 使用索引
EXPLAIN SELECT * from students where create_date >= '2010-05-05'

# 没有使用索引
EXPLAIN SELECT * from students where DATE_FORMAT(create_date,'%Y-%m-%d') >= '2010-05-05'


7. 对于order by、group by 、 union、 distinc 中的字段出现在where条件中时,才会利用索引!
8. 更多索引的使用注意可以参看这一篇博文:

索引使用注意规则(索引失效--存在索引但不使用索引)

五、总结

MySQL改善查询性能改善的最好方式,就是通过数据库中合理地使用索引!

一般当数据量较大的时候,遇到sql查询性能问题,首先想到的应该是查询的sql时候使用了索引,如果使用了索引性能还是提高不大,就要检查索引是否使用正确,索引是否在sql查询中生效了!

如果索引生效了,并且索引的使用也是合理的,最后sql性能还是不高,那就考虑重新优化sql语句!

六、参考博文


如果您觉得这篇博文对你有帮助,请点赞或者喜欢,让更多的人看到,谢谢!

如果帅气(美丽)、睿智(聪颖),和我一样简单善良的你看到本篇博文中存在问题,请指出,我虚心接受你让我成长的批评,谢谢阅读!
祝你今天开心愉快!


欢迎访问我的csdn博客,我们一同成长!

不管做什么,只要坚持下去就会看到不一样!在路上,不卑不亢!

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 204,445评论 6 478
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 85,889评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 151,047评论 0 337
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,760评论 1 276
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,745评论 5 367
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,638评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,011评论 3 398
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,669评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,923评论 1 299
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,655评论 2 321
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,740评论 1 330
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,406评论 4 320
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,995评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,961评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,197评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 45,023评论 2 350
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,483评论 2 342

推荐阅读更多精彩内容