一、索引的优缺点
优点:
- 减少了服务器扫描的数据量
- 帮助服务器避免排序和临时表
- 将随机io变成顺序io
缺点:
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行
insert
、update
和delete
。因为更新表时,不仅要保存数据,还要保存一下索引文件。 - 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会增长很快。
索引只是提高效率的一个因素,如果有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句
二、索引的用处
- 快速查找匹配WHERE子句的行
- 从
consideration
中消除行,如果可以在多个索引之间进行选择,mysql通常会使用找到最少行的索引 - 如果表具有多列索引,则优化器可以使用索引的任何最左前缀来查找行
- 当有表连接的时候,从其他表检索行数据
- 查找特定索引列的min或max值
- 如果排序或分组时,在可用索引的最左前缀上完成的
- 在某些情况下,可以优化查询以检索值而无需查询数据行
三、索引的分类
3.1 主键索引
一个表只能有一个主键,不允许有空值。
3.2 唯一索引
索引列的值必须唯一,但允许有空值。
3.3 普通索引
最基本的索引,它没有任何限制。
3.4 组合索引
指多个字段上创建的索引。列值的组合必须唯一。使用组合索引时遵循最左前缀集合。
3.5 全文索引
主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。
四、索引的数据结构
MyISAM和InnoDB 都是是B+树。枝干上不存数据,叶子节点存数据。
区别在于:MyISAM的叶子结点存的数据是指向数据的指针;Innodb直接存的是数据。
五、索引的技术名词
5.1 聚簇索引与非聚簇索引
这是并不是指的索引,而是两种数据存储方式。
- 聚簇索引:数据文件跟索引文件存放在一起(InnoDB)
- 非簇集索引:数据文件跟索引文件分开存放(MyISAM)
小技巧: 进行数据迁移时,可以先不创建索引,迁移完数据后再建立索引,因为索引可能会随着数据的插入而频繁的更新,降低插入的效率。
聚簇索引
- 优点:
- 可以把相关数据保存在一起,索引和数据保存在同一个树中,数据访问更快。
- 使用覆盖索引(见下文)扫描的查询可以直接使用页节点中的主键值。
- 缺点:
- 如果数据全部在内存,那么聚簇索引就没有什么优势。
- 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式。(基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题。页分裂和页合并影响性能)
- 更新聚簇索引列的代价很高,因为会强制将每个被更新的行移动到新的位置
- 页分裂: 假如经过判断,需要在某个索引块中插入数据,如果这个索引块中没有空余位置了,就会页分裂,新增一个索引块,把原先的块中的数据分到两个索引块中(一人一半);页分裂会导致空间的浪费
- 页合并: 假如删除数据后,判断两个数据页可以合并成一个,就会把他俩合并
5.2 回表
前提:索引不是主键索引(主键索引是聚簇索引,即索引的叶子节点存的是整个单条记录的所有字段值)。
当非主键索引时,叶子结点键会存储主键字段,需要其他列时,根据主键再去查主键的B+树。
回表需要查询两次B+树,IO次数比较多。
5.3 覆盖索引(索引覆盖)
如果建立的普通索引/组合索引,我们查询的时候只需要查询该索引(或者主键),不需要查询其他列,就不需要回表了,这就是索引覆盖。常见于组合索引。
5.4 最左匹配
建立组合索引后,比如对三个列name,age和sex 建立了组合索引 index_name_age_sex
。
那么where name=? and age=? and sex=?
时肯定可以用到索引;where name=?
或者 where where name=? and age=?
时也可以用到索引 (使用多个where条件时,顺序无所谓,MySQL会帮我们优化调整成最左匹配的顺序)。如果达不到最左匹配,比如where age=?
,则不会使用该组合索引。
解决方法:
- 把组合索引调整一下顺序,改成
index_age_name_sex
- age单独建立一个索引
如果需要在为name和age单独创建索引中 二选一,那么我会选择age;因为age占用磁盘空间更少,每页存储的数据个数就会多,减少磁盘IO。
5.5 索引(条件)下推 Index Condition Pushdown (ICP)
MySQL 5.6添加的,用于优化数据查询:减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。
“下推”:条件判断由server下推到存储引擎。
- 不使用索引条件下推优化时:存储引擎通过索引检索到数据,然后返回给MySQL服务器,然后服务器判断数据是否符合条件。
- 使用索引条件下推优化时:如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
比如:查询sql的过滤条件where name=? and age=?
老版本的MySQL,server第一步会先根据name条件从存储引擎中取出数据,第二步在server层根据age筛选;在组合索引中,高版本MySQL会优化一下,会把第二步合并到第一步,在查询数据时就根据name和age筛选,减少了IO量。
- 不使用ICP技术(过程使用数字符号标示,如①②③等)
- 使用ICP技术(过程使用数字符号标示,如①②③等)
配置
索引下推优化是默认开启的。可以通过下面的脚本控制开关
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';
六、索引的匹配方式全值匹配
create table staffs(
id int primary key auto_increment,
name varchar(24) not null default '' comment '姓名',
age int not null default 0 comment '年龄',
pos varchar(20) not null default '' comment '职位',
add_time timestamp not null default current_timestamp comment '入职时间'
) charset utf8 comment '员工记录表';
-----------
alter table staffs add index idx_nap(name, age, pos);
6.1 全值匹配
指的是和索引中的所有列进行匹配
explain select * from staffs where name = ‘July’ and age = ‘23’ and pos = ‘dev’;
6.2 匹配最左前缀
只匹配前面的几列
explain select * from staffs where name = ‘July’ and age = ‘23’;
explain select * from staffs where name = ‘July’;
6.3 匹配列前缀
可以匹配某一列的值的开头部分
explain select * from staffs where name like ‘J%’;
explain select * from staffs where name like ‘%y’;
6.4 匹配范围值
可以查找某一个范围的数据
explain select * from staffs where name > ‘Mary’;
精确匹配某一列并范围匹配另外一列
可以查询第一列的全部和第二列的部分
explain select * from staffs where name = ‘July’ and age > 25;
6.5 只访问索引的查询
查询的时候只需要访问索引,不需要访问数据行,本质上就是覆盖索引
explain select name,age,pos from staffs where name = ‘July’ and age = 25 and pos = ‘dev’;
七、索引的使用语法
语法
CREATE TABLE table_name
[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]
1.unique|fulltext为可选参数,分别表示唯一索引、全文索引
2.index和key为同义词,两者作用相同,用来指定创建索引
3.col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择
4.index_name指定索引的名称,为可选参数,如果不指定,默认col_name为索引值
5.length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度
6.asc或desc指定升序或降序的索引值存储
普通索引:
(1)直接创建索引
CREATE INDEX index_name ON table(column(length))
(2)修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name(column(length))
(3)创建表的时候同时创建索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)
(4)删除索引
DROP INDEX index_name ON table
唯一索引
(1)创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))
(2)修改表结构
ALTER TABLE table_name ADD UNIQUE indexName(column(length))
(3)创建表的时候直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
UNIQUE indexName (title(length))
);
主键索引
创建主键索引:
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`)
);
组合索引
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
全文索引
(1)创建表的适合添加全文索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);
(2)修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
(3)直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)