目录
一、索引
1.索引的概念
- 索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址(类似于c语言的链表通过指针指向数据记录的内存地址)。
- 使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据,因此能加快数据库的查询速度。
- 索引就好比是一本书的目录,可以根据目录中的页码快速找到所需的内容。
- 索引是表中一列或者若干列值排序的方法。
- 建立索引的目的是加快对表中记录的查找或排序。(加快查询速度、对字段值进行排序)
2.索引的作用
- 加快表的查询速度(主要作用)
- 可以对字段排序
- 可以降低数据库的IO成本,并且索引还可以降低数据库的排序成本
- 通过创建唯一性索引,可以保证数据表中每一行数据的唯一性
- 建立索引在搜索和恢复数据库中的数据时能显著提高性能
索引的副作用
- 索引需要占用额外的磁盘空间
- 对于 MyISAM 引擎而言,索引文件和数据文件是分离的,索引文件用于保存数据记录的地址。而 InnoDB 引擎的表数据文件本身就是索引文件。(索引文件和数据文件是同一个)在插入和修改数据时要花费更多的时间、消耗更多性能,因为索引也要随之变动
3.工作方式
- 没有索引的情况下,要查询某行记录数据时,需要先扫描全表,再定位某行记录数据的位置
- 有了索引后,会先通过索引查询到行记录数据所在的物理地址,即可直接访问相应的行记录数据,就像通过书目录的页码快速查找书内容一样
4.索引创建的原则依据
索引虽可以提升数据库查询的速度,但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源,在有索引的情况下,数据库会先进行索引查询,然后定位到具体的数据行,如果索引使用不当,反而会增加数据库的负担
- 表的主键、外键必须有索引。因为主键具有唯一性,外键关联的是主表的主键,查询时可以快速定位。
- 记录数超过300行(或500行)的表应该有索引。如果没有索引,每次查询都需要把表遍历一遍,会严重影响数据库的性能。(会影响IO和CPU的性能)
- 经常与其他表进行连接的表,在连接字段上应该建立索引。
- 唯一性太差的字段不适合建立索引。(因为字段值会有重复)
- 更新太频繁地字段不适合创建索引。
- 经常出现在 where 子句中的字段,特别是大表的字段,应该建立索引。
- 在经常进行GROUP BY、ORDER BY的字段上建立索引。
- 索引应该建在选择性高的字段上。(即重复性低的字段)
- 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引。(大字段建索引会增加磁盘空间,而且搜索起来会影响性能
二、索引的分类和创建
最基本的索引类型,没有唯一性之类的限制.
1.普通索引
- 最基本的索引类型,没有唯一性之类的限制
(1)直接创建索引
create index 索引名 on 表名(列名[ ( length) ]);
(2)修改表方式创建
alter table 表名 add index 索引名 (列名);
(3)创建表的时候指定索引
create table 表名 ( 字段1 数据类型,字段2 数据类型,[...],index 索引名 (列名));
2.唯一索引
- 与普通索引类似,但区别是唯一索引列的每个值都唯一
- 唯一索引允许有空值(注意和主键不同)
- 如果是用组合索引创建,则列值的组合必须唯一添加
- 唯一键将自动创建唯一索引
(1)直接创建唯一索引
create unique index 索引名 on 表名(列名);
(2)修改表方式创建
alter table 表名 add unique 索引名 (列名);
(3)创建表的时候指定
create table 表名 (字段1 数据类型,字段2 数据类型,[...],unique 索引名 (列名));
3.主键索引
- 是一种特殊的唯一索引,必须指定为“PRIMARY KEY”
- 一个表只能有一个主键,不允许有空值
- 添加主键将自动创建主键索引
(1)创建表的时候指定
create table 表名 ([...],primary key (列名));
(2)修改表方式创建
alter table 表名 add primary key (列名);
4.组合索引
- 可以是单列上创建的索引,也可以是在多列上创建的索引
- 需要满足最左原则,因为 select 语句的 where 条件是依次从左往右执行的,所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致,否则索引将不会生效
(1)直接创建索引
create index 索引名 on 表名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ;
#查询语句使用 and 做逻辑运算符时,字段顺序要与创建多列索引的字段顺序一致(要满足最左原则)
(2)修改表方式创建
alter table 表名 add index 索引名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ;
#查询语句使用 and 做逻辑运算符时,字段顺序要与创建多列索引的字段顺序一致(要满足最左原则)
5.全文索引
(1)直接创建索引
create fulltext index 索引名 on 表名 (列名);
(2)修改表方式创建
alter table 表名 add fulltext 索引名 (列名);
(3)创建表的时候指定索引
create table 表名 (字段1 数据类型,[...],fulltext 索引名 (列名));
#数据类型可以为char、varchar或者text
(4)使用全文索引查询
select * from 表名 where match(列名) against('查询内容');
(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 条件字段建立相应的单列索引或者多列组合索引(多列组合索引要满足最左原则)