mysql索引

目录

一、索引介绍

二、索引的工作方式

三、索引的创建依据

四、索引的创建与分类

五、查看索引

六、删除索引

七、select查询语句执行速度慢如何优化？

八、总结

---

一、索引介绍

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

通俗来说, 索引就相当于一本书的目录, 可以根据页码快速查找到指定的内容, 目的就是加快数据库的查询速度

副作用是：会额外占用磁盘空间；更新包含索引的表会花费更多的时间
 

二、索引的工作方式

没有索引的情况下，要查询某行记录数据时，需要先扫描全表，再定位某行记录数据的位置

有了索引后，会先通过索引查询到行记录数据所在的物理地址，即可直接访问相应的行记录数据，就像通过书目录的页码快速查找书内容一样。
 

三、索引的创建依据

• 表的记录行数较多时（一般超过三五百行时），且读操作多的情况下应该要创建索引
• 建议在表的 主键字段、外键字段、多表连接使用的公共字段、唯一性较好的字段、不经常更新的字段、where条件字段、分组(group by)字段、排序(order by)字段、短小的字段 上创建索引
• 不建议在 唯一性较差的字段、更新太频繁的字段、大文本字段 上创建索引

四、索引的创建与分类

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

直接创建索引：

语法：create index 索引名 on 表名（索引名）；

列子：

修改表方式创建

语法：alter table 表名（字段1 数据类型1，字段2 数据类型2... ,index 索引名（列名））；

列子:

创建表的时候指定索引

create table 表名 ( 字段1 数据类型,字段2 数据类型,[...],index 索引名 (列名));
例子:

五、唯一索引

create unique index 索引名 on 表名(列名);
 

修改表方式创建

alter table 表名 add unique 索引名 (列名);
 

 创建表的时候指定

create table 表名 (字段1 数据类型,字段2 数据类型,[...],unique 索引名 (列名)); 

 主键索引

是一种特殊的唯一索引，必须指定为==‘‘PRIMARY KEY’’==。一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。

创建表时候指定：

create table 表名 ([...],primary key (列名));

 

 修改表方式创建

alter table 表名 add primary key (列名); 

 组合索引

（单列索引与多列索引）：可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为select语句的where条件是依次从左往右执行的，所以在使用select语句查询时，where条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效

直接创建索引

create index 索引名 on 表名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ;   
#查询语句使用 and 做逻辑运算符时，字段顺序要与创建多列索引的字段顺序一致（要满足最左原则）

 修改表方式创建

alter table 表名 add index 索引名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ;   
#查询语句使用 and 做逻辑运算符时，字段顺序要与创建多列索引的字段顺序一致（要满足最左原则）

全文索引

fulltext）：适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。在 MySQL5.6版本以前FULLTEXT索引仅可用于MyISAM引擎，在5.6版本之后innodb引擎也支持fulltext索引。全文索引可以在char、vaechar或者text类型的列上创建。

直接创建索引

create fulltext index 索引名 on 表名 (列名);

 修改表方式创建

alter table 表名 add fulltext 索引名 (列名);

创建表的时候创建索引

create table 表名 (字段1 数据类型,[...],fulltext 索引名 (列名));
#数据类型可以为char、varchar或者text

 

使用全文索引查询

select * from 表名 where match(列名) against('查询内容');

中文全文索引查询

vim /etcmy.cfn
ngram_token_size=2       #指定查询的单词的最小字数
syetemctl restart mysqld
 
create table xy106 (id int, name varchar(20), age int, sex vhar(2), remark text, fulltext (remark) with parser ngram);  #创建表
insert into xy106 values (1, 'zhang', 25, '男', '足球，篮球，台球');
insert into xy106 values (2, 'san', 26, '男', '吃饭，足球，乒乓球‘）;
insert into xy106 values (3, 'li', 26, '男', '篮球，羽毛球，乒乓球');
insert into xy106 values (4, 'si', 26, '男', '篮球，乒乓球，足球');
#添加四行数据

五、查看索引

show create table 表名;
show index from 表名;
show index from 表名\G;   #纵向显示索引信息
show keys from 表名;
show keys from 表名\G; 

六、删除索引

直接删除索引

drop index 索引名 on 表名;

 修改表方式删除索引

alter table 表名 drop index 索引名;

 删除主键索引

alter table 表名 drop primary key;

七、select查询语句执行速度慢如何优化？

1. 升级 CPU 内存 硬盘 硬件性能
2. 对 MySQL 配置进行优化
3. 对查询语句的结构进行优化，比如将嵌套子查询优化成表连接查询；或连接表时，可以先用where条件对表进行过滤，然后做表连接
4. 进行索引优化：先使用 explain 分析 select 语句，判断这个查询语句是否正确的使用了索引；再根据查询语句中的 where 条件字段建立相应的单列索引或者多列组合索引（多列组合索引要满足最左原则）

八、总结

普通索引创建

create index 索引名 on 表名(列名[ ( length) ]);
alter table 表名 add index 索引名 (列名);
create table 表名 ( 字段1 数据类型,字段2 数据类型,[...],index 索引名 (列名));

唯一索引创建

create unique index 索引名 on 表名(列名);
alter table 表名 add unique 索引名 (列名);
create table 表名 (字段1 数据类型,字段2 数据类型,[...],unique 索引名 (列名)); 

主键索引创建

alter table 表名 add primary key (列名); 
create table 表名 ([...],primary key (列名));

组合索引创建

create index 索引名 on 表名(字段1, 字段2, ....);
alter table 表名 add index 索引名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ;   
#查询语句使用 and 做逻辑运算符时，字段顺序要与创建多列索引的字段顺序一致（要满足最左原则）

全文索引创建

create fulltext index 索引名 on 表名 (列名);
alter table 表名 add fulltext 索引名 (列名);
create table 表名 (字段1 数据类型,[...],fulltext 索引名 (列名));
#数据类型可以为char、varchar或者text

查看索引

show create table 表名;
show index from 表名;
show index from 表名\G;   #纵向显示索引信息
show keys from 表名;
show keys from 表名\G;

删除索引

drop index 索引名 on 表名;
alter table 表名 drop index 索引名;
alter table 表名 drop primary key;