一 介绍
一般刚开始写代码的时候,是不太会注意索引的,甚至是意识不到sql优化,查询性能的。
一方面索引能极大的提高查询性能,另一方面索引建的过多或不合理,会对应用程序造成影响,所以程序员很有必要了解它!有些开发人员在DBA反馈慢sql的时候,才意识到建索引,甚至觉得是DBA优化的工作,这其实是一个误区,我们应该再一开始设计的时候,根据可能的数据流添加索引。
我们接下来探讨一下Mysql的索引。由于InnoDB支持事物,是很多OLTP应用的首选,而InnoDB支持3种常见索引:
1.B+索引
2.全文索引
3.哈希索引
其中B+索引时最常见也是最有效的关系型数据库索引,而全文索引只支持英文,对中文的分词效果并不好;哈希索引属于数据库InnoDB引擎层面的,用户干预不了
二 数据结构
三 B+索引
数据库中的B+索引时基于B+树的,其实就是B+树在数据库中的实现;其中又分为
聚集索引:
内部是B+树,叶子节点存放所有数据,并且是完整的一整行信息
InnoDB是索引组织表,即表中数据按照主键顺序存放。而聚集索引是按照每张表的主键构造B+树,同时叶子节点存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。实际上一张表只能有一个聚集索引,而查询优化器页倾向与采用聚集索引,因为能够再B+树叶子节点上直接找到数据。
而且由于B+树是主键排序的,能够很快的进行范围查找。
那可能就有疑惑,如果表沒有主键呢?这就涉及到InnoDB对聚集索引的选择
1.如果有主键,自动选择主键
2.如果无主键,选择第一个唯一非空索引
3.如果1,2都不满足,默认生成一个6字节的隐藏自增序列
辅助索引:
内部是B+树,叶子节点存放所有数据,但不是完整的一整行信息。那么到底存放什么呢,一般辅助索引存放的是主键+索引列的值。比如表t
CREATE TABLE test1(
id int not null,
age int not null,
PRIMARY key (id),
INDEX idx_age(age)
) ENGINE=INNODB CHARSET=utf8
列age有一个辅助索引(普通索引),那么索引上存放的是test1表的主键id值+列age的值。当通过idx_age来查找数据时,InnoDB会遍历索引idx_age并通过叶级别的指针找到主键索引的主键id,然后再通过主键索引找到一个完整的行记录,所以辅助索引查找效率一般比聚集索引低。(也有例外的情况,就是覆盖索引,后面再介绍)
四 索引的管理
方式1:alter table
ALTER TABLE tb_name
ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option]
ALTER TABLE tb_name
DROP PRIMARY key
| DROP {INDEX|KEY} index_name
看一个例子
首先建一个表test2,不添加任何索引
CREATE TABLE test2(
id INT NOT NULL,
name VARCHAR(255) not null,
age int not null
) ENGINE=INNODB CHARSET=utf8;
添加一个主键索引到id上
ALTER TABLE test2
ADD PRIMARY KEY idx_name (id);
查看一下索引
说明创建成功
删除索引
ALTER TABLE test2
DROP PRIMARY key;
方式2:create /drop index
建表时创建索引
CREATE TABLE test2(
id INT NOT NULL,
name VARCHAR(255) not null,
age int not null,
PRIMARY KEY (id) -- 默认主键索引
) ENGINE=INNODB CHARSET=utf8;
CREATE INDEX idx_name ON test2(name);
添加联合索引
CREATE INDEX idx_name_age ON test2(name, age);
删除索引
DROP INDEX idx_name ON test2;
索引的查看
SHOW INDEX FROM tb;
查看索引里面会有以下几个属性
Table:索引所在的表名
Non_unique:是否唯一索引,0-是,1-否
Key_name:索引名称,PRIMARY-主键索引,用户可以通过这个名称来drop index
Seq_in_index:列在索引中的位置;对于联合索引idx_name_age,name在前,所以是1,age在后,为2
Column_name:索引的列名称
Collation:列在索引中的存储方式。B+索引是A,Hash索引是NULL
Cardinality:索引中唯一值的数目估计值,这个值应尽可能接近1。这个值很关键!
Sub_part:是否列的部分被索引;如果整个列被索引,为NULL,否则为索引的字符长度。
比如现在建一个索引,只对列name前100索引
CREATE INDEX idx_name ON test2(name(100));
查看一下索引
发现name列的Sub_part为100了
Packed:关键字如何压缩;无压缩,则为NULL
Null:是否索引的列含有NULL值。
Index_type:索引的类型。InnoDB只支持B+索引,所以都显示BTREE
Comment:注释
如果添加索引是,有注释
CREATE INDEX idx_name ON test2(name(100)) COMMENT 'name普通索引';
看下索引
五 索引的使用
前面讲了聚集索引,辅助索引,它们是B+索引在数据库的实现与本质,那么怎么去用呢?一般有2种索引方式
联合索引
对表中多个列进行索引,即创建一个索引时,有多个列,比如上面的
CREATE INDEX idx_name_age ON test2(name, age);
现在向表里面插入几条数据
INSERT INTO test2(id,name,age) VALUES(1,'tw',20);
INSERT INTO test2(id,name,age) VALUES(2,'tw',21);
INSERT INTO test2(id,name,age) VALUES(3,'tw',20);
INSERT INTO test2(id,name,age) VALUES(4,'tw',24);
现在创建一个联合索引idx_id_age
CREATE INDEX idx_id_age ON test2(id,age) COMMENT '联合索引';
比如现在有如下sql
SELECT * FROM test2 WHERE
id = xx AND age=xx
SELECT * FROM test2 WHERE
id = xx
查看一下执行计划
会发现在都走了联合索引idx_id_age
但是这个sql
SELECT * FROM test2 WHERE
age = xx
却不能走索引,查看一下执行计划验证下,的确如此?
EXPLAIN SELECT * FROM test2 WHERE
age = 20
这是为什么呢?因为联合索引是也排序的,内部类似(1,20),(2,21),(3,20),(4,24),数据按(id,age)排序,单个id列也排序,但是单个age列并不排序,因此无法使用到联合索引, 这被称为索引的最左原则,也是面试中很容易问到的
覆盖索引
InnoDB支持覆盖索引,即从辅助索引中就可以取出查询的记录,而不用查询聚集索引。这样的好处是,辅助索引没有存放整行的记录,占用空间更小,可以大大减少IO操作。
所以如果我们要查询主键的一些信息,就可以通过辅助索引,这也是有些Mysql分页优化的常用手段,在大数据下规制limit的一个选择