Mysql索引知识整理

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

建立索引的优点 :

① 保证行的唯一性,生成唯一的rowId

② 有效缩短数据的检索时间

③ 加快表与表之间的连接

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

建立索引的缺点 :

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

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

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

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

索引的分类:

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、索引的创建、查询、删除

添加单列索引:

直接创建索引:CREATE INDEX INDEX_NAME ON TABLE_NAME(COL_NAME);

修改表结构的方式添加索引 :

ALTER TABLE TABLE_NAME ADD INDEX INDEX_NAME(COL_NAME);

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

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))
)

删除单列索引:

DROP INDEX INDEX_NAME ON TABLE_NAME; 或者

alter table `表名` drop index 索引名;

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

直接创建组合索引:

CREATE INDEX INDEX_NAME ON TABLE_NAME(COL_NAME1,COL_NAME2,...);

修改表结构的方式添加组合索引:

ALTER TABLE TABLE_NAME ADD INDEX INDEX_NAME(COL_NAME,COL_NAME2,...);

唯一索引:唯一索引和单列索引类似。

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

已存在数据的表上添加唯一性索引 :

1、添加索引的列的值存在两个或者两个以上的空值,则不能创建唯一索引(一般在创建表的时候,要对自动设置唯一性索引,需要在字段上加上 not null)
2、添加索引的列的值存在两个或者两个以上的null值,还是可以创建唯一性索引,但是后面创建的数据不能再插入null值 (严格意义上此列并不是唯一的,因为存在多个null值)
3、多个字段组合创建的唯一索引,列值的组合必须唯一(在order表创建orderId字段和 productId字段 的唯一性索引,那么这两列的组合值必须唯一!)

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

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

Mysql索引知识整理

直接创建唯一单列索引:

CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(COL_NAME);

直接创建唯一组合索引:

CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(COL_NAME,...);

修改表时创建唯一单列索引:

ALTER TABLE TABLE_NAME ADD UNIQUE INDEX INDEX_NAME(COL_NAME);

修改表时创建唯一组合索引:

ALTER TABLE TABLE_NAME ADD UNIQUE INDEX INDEX_NAME(COL_NAME,...);

创建表时,创建唯一索引:

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)
)

主键索引:一种特殊的唯一索引,一个表只能有一个主键,不允许有空值

建表时创建主键索引:

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`)
)

建表后创建主键索引:

ALTER TABLE TBL_NAME ADD PRIMARY KEY(COL_NAME);

全文索引:主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext 索引更像是一个搜索引擎,而不是简单的 where 语句的参数匹配。配合 match against 操作使用,而不是一般的 where 语句加 like “%value%”

目前只有char、varchar,text 列上可以创建全文索引

一般情况下,模糊查询通过 like 的方式进行查询。like “%value%” 这样的方式,执行全表查询,这在数据量小的表,不存在性能问题,但是对于海量数据,全表扫描是非常耗时的,匹配性能非常差。

这种情况下,需要考虑使用添加全文索引的方式进行优化 :

小技巧 :


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

创建表时添加全文索引 :

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)
)

创建表后添加全文索引 :

ALTER TABLE TABLE_NAME ADD FULLTEXT INDEX_FULLTEXT_CONTENT(COL_NAME);

直接创建全文索引:

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:这个值越高,说明查询低效

常见索引失效的情况:

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 查看 索引是否生效!

1、创建两个单列索引

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

# 使用了索引
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、仍旧使用 index_name_email 索引

# 使用了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、复合索引遵守“最左前缀”原则,即在查询条件中使用了复合索引的第一个字段,索引才会被使用

create index index_email_phone on students(email,phone);

# 使用了 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、如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引

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() 格式化时间,格式化后的时间再去比较,可能会导致索引失效。

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 条件中时,才会利用索引!

索引使用注意规则

索引的优化

上一篇:用java实现一个随机点名器


下一篇:MySQL常用语法