目录
一.索引概述
1.概念
- 索引是一个排序的列表,包含索引字段的值和其对应的行记录数据所在的物理地址
- 索引就好比是一本书的目录,可以根据目录中的页码快速找到所需的内容
- 索引是表中一列或者若干列值排序的方法
- 建立索引的目的是加快对表中记录的查找或排序(加快查询速度、对字段值进行排序)
2.作用
- 加快表的查询速度(主要作用)
- 可以对字段排序
- 可以降低数据库的IO成本,并且索引还可以降低数据库的排序成本
- 通过创建唯一性索引,可以保证数据表中每一行数据的唯一性
- 建立索引在搜索和恢复数据库中的数据时能显著提高性能
3.副作用
- 会额外占用磁盘空间
- 更新包含索引的表会花费更多的时间
4.工作方式
- 没有索引的情况下,要查询某行记录数据时,需要先扫描全表,再定位某行记录数据的位置
- 有了索引后,会先通过索引查询到行记录数据所在的物理地址,即可直接访问相应的行记录数据,就像通过书目录的页码快速查找书内容一样
5.创建索引的原则依据
- 表的记录行数较多时(一般超过三五百行时),应该要创建索引
- 建议在表的 主键字段、外键字段、多表连接使用的公共字段、唯一性较好的字段、不经常更新的字段、where条件字段、分组(group by)字段、排序(order by)字段、短小的字段 上创建索引
- 不建议在 唯一性较差的字段、更新太频繁的字段、大文本字段 上创建索引
二.索引的分类与创建
create table member (id int(10),name varchar(10),cardid int(18),phone int(11),address
varchar(50),remark text);
1.普通索引
- 最基本的索引类型,没有唯一性之类的限制
1.1.直接创建索引
CREATE INDEX 索引名 ON 表名 (列名[(length)]);
1.2.修改表方式创建
alter table 表名 add index 索引名 (列名);
1.3.创建表的时候指定索引
create table 表名 ( 字段1 数据类型,字段2 数据类型,[...],index 索引名 (列名));
2.唯一索引
- 与普通索引类似,但区别是唯一索引列的每个值都唯一
- 唯一索引允许有空值(注意和主键不同)
- 如果是用组合索引创建,则列值的组合必须唯一添加
- 唯一键将自动创建唯一索引
2.1.直接创建唯一索引
create unique index 索引名 on 表名(列名);
2.2.修改表方式创建
alter table 表名 add unique 索引名 (列名);
2.3.创建表的时候指定
create table 表名 (字段1 数据类型,字段2 数据类型,[...],unique 索引名 (列名));
3.主键索引
- 是一种特殊的唯一索引,必须指定为“PRIMARY KEY”
- 一个表只能有一个主键,不允许有空值
- 添加主键将自动创建主键索引
3.1.创建表的时候指定
create table 表名 ([...],primary key (列名));
3.2.修改表方式创建
alter table 表名 add primary key (列名);
4.组合索引
- 可以是单列上创建的索引,也可以是在多列上创建的索引
- 需要满足最左原则,因为 select 语句的 where 条件是依次从左往右执行的,所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致,否则索引将不会生效
4.1.直接创建索引
create index 索引名 on 表名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ;
#查询语句使用 and 做逻辑运算符时,字段顺序要与创建多列索引的字段顺序一致(要满足最左原则)
4.2.修改表方式创建
alter table 表名 add index 索引名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ;
#查询语句使用 and 做逻辑运算符时,字段顺序要与创建多列索引的字段顺序一致(要满足最左原则)
5.全文索引
5.1.直接创建索引
create fulltext index 索引名 on 表名 (列名);
5.2.修改表方式创建
alter table 表名 add fulltext 索引名 (列名);
5.3.创建表的时候指定索引
create table 表名 (字段1 数据类型,[...],fulltext 索引名 (列名));
#数据类型可以为char、varchar或者text
5.4.使用全文索引查询
select * from 表名 where match(列名) against('查询内容');
5.5.使用中文蜷缩索引查询
vim /etcmy.cfn
ngram_token_size=2 #指定查询的单词的最小字数
syetemctl restart mysqld
create table xy108 (id int, name varchar(20), age int, sex vhar(2), remark text, fulltext (remark) with parser ngram); #创建表
insert into xy108 values (1, 'A', 25, '男', '足球,篮球,台球');
insert into xy108 values (2, 'B', 26, '男', '吃饭,足球,乒乓球‘);
insert into xy108 values (3, 'C', 26, '男', '篮球,羽毛球,乒乓球');
insert into xy108 values (4, 'D', 26, '男', '篮球,乒乓球,足球');
#添加四行数据
三.索引的查看与删除
1.查看索引
show create table 表名;
show index from 表名;
show keys from 表名;
2.删除索引
drop index 索引名 on 表名; #这两种方法都可以用来删除普通索引、唯一索引、全文索引、
组合索引
alter table 表名 drop index 索引名;
alter table 表名 drop primary key; #删除主键索引
补充
遇到 select 查询语句执行速度慢该怎么办
- 升级 CPU 内存 硬盘 硬件性能
- 对 MySQL 配置进行优化
- 对查询语句的结构进行优化,比如将嵌套子查询优化成表连接查询;或连接表时,可以先用where条件对表进行过滤,然后做表连接
- 进行索引优化:先使用 explain 分析 select 语句,判断这个查询语句是否正确的使用了索引
- 再根据查询语句中的 where 条件字段建立相应的单列索引或者多列组合索引(多列组合索引要满足最左原则)