一、索引
1、索引定义
索引是数据库中专门用于帮助用户快速查询数据的一种数据结构。索引需要单独保存在索引文件中。
类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取即可。
优点:可以提高检索数据的速度
缺点:创建和维护索引需要耗费时间
2、索引分类
(1)普通索引:仅加速查询
(2)唯一索引:加速查询 + 列值唯一(可能有null)
(3)主键索引:加速查询 + 列值唯一 + 表中只有一个(不可以有null)
(4)组合索引:多列值组成一个索引,其效率大于索引合并
(5)全文索引:对文本的内容进行分词搜索
3、如何创建/删除索引
(1)创建表的时候创建索引
CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
,,,,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY[索引名称](字段名称[(长度)])
[ASC|DESC]
);
(2)在已存在的表上创建索引
创建索引时如果是BLOB和TEXT类型,必须指定length
(1) CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名{字段名称[(长度)] [ASC|DESC]};
(2) ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX索引名称(字段名称[(长度)][ASC|DESC]);
(3)删除索引
DROP INDEX 索引名称 ON tbl_name
create table in1(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
index ix_name (name), // 创建普通索引
unique ix_name (name), // 创建唯一索引
primary key(nid), // 创建主键
)
// 创建普通/唯一索引
create [unique] index 索引名 on 表名(列名);
// 创建主键
alter table 表名 add primary key(列名);
// 创建组合索引
create index 索引名 on 表名(列名1, 列名2);
列名1 and 列名2 --- 使用索引
列名1 --- 使用索引
列名2 --- 不使用索引
// 删除普通/唯一索引
drop [unique] index 索引名 on 表名
// 删除主键
alter table 表名 drop primary key
二、explain + 查询SQL
用于显示SQL执行信息参数,根据参数信息进行SQL优化
mysql> explain select * from t_users where Fuser_id=2000;
+----+-------------+-----------------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | t_users | ref | Fuser_id | Fuser_id | 8 | const | 7 | Using index condition |
+----+-------------+-----------------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
参数解释
1、id:查询顺序标识
2、select_type:查询类型
SIMPLE 简单查询
PRIMARY 最外层查询
SUBQUERY 映射为子查询
DERIVED 子查询
UNION 联合
UNION RESULT 使用联合的结果
...
3、table:正在访问的表名
4、type:查询时的访问方式
性能 all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
all:全表扫描,对于数据表从头到尾找一遍
select * from tb1;
index:全索引扫描,对索引从头到尾找一遍
select nid from tb1;
range:对索引列进行范围查找
select * from tb1 where name < 'alex';
index_merge:合并索引,使用多个单列索引搜索
select * from tb1 where name = 'alex' or nid in (11,22,33);
ref:根据索引查找一个或多个值
select * from tb1 where name = 'seven';
eq_ref:连接时使用primary key 或 unique类型
select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
const:常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
select nid from tb1 where nid = 2 ;
system:系统 表仅有一行(=系统表)。这是const联接类型的一个特例。
select * from (select nid from tb1 where nid = 1) as A;
5、possible_keys:可能使用的索引
6、key:真实使用的索引
7、key_len:mysql中使用索引字节长度
8、rows:mysql估计为了找到所需的行而要读取的行数(只是预估值)
9、extra:该列包含mysql解决查询的详细信息
"Using index"
此值表示mysql将使用覆盖索引,以避免访问表。不要把覆盖索引和index访问类型弄混了。
"Using where"
这意味着mysql服务器将在存储引擎检索行后再进行过滤,许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,
因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示:查询可受益于不同的索引。
"Using temporary"
这意味着mysql在对查询结果排序时会使用一个临时表。
"Using filesort"
这意味着mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。mysql有两种文件排序算法,这两种排序方式都可以在内存或者磁盘上完成,
explain不会告诉你mysql将使用哪一种文件排序,也不会告诉你排序会在内存里还是磁盘上完成。
"Range checked for each record(index map: N)"
三、聚集索引、辅助索引、覆盖索引、联合索引
1、聚集索引
按照每张表的主键构造一颗B+树,同时叶子节点中存放的即为整张表的行记录数据。
聚集索引的叶子节点称为数据页,每个数据页通过一个双向链表来进行链接,而且数据页按照主键的顺序进行排序。
如果定义了主键,InnoDB会自动使用主键来创建聚集索引;
如果没有定义主键,InnoDB会选择一个唯一的非空索引代替主键;
如果没有唯一的非空索引,InnoDB会隐式定义一个主键来作为聚集索引。
2、辅助索引
也叫非聚集索引,和聚集索引相比,叶子节点中并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点的索引行还包含了一个书签(bookmark),
该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。
3、覆盖索引
如果一个索引包含(或覆盖)所有需要查询的字段的值,称为覆盖索引,即只需要扫描索引而无须回表。
InnoDB存储引擎支持覆盖索引,即从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。
优点:
(1)可以减少大量的IO操作,select * from t_users where Fuser_id=2000;
如果要查询辅助索引中不含有的字段,得先遍历辅助索引,再遍历聚集索引,
而如果要查询的字段值在辅助索引上,就不用再查询聚集索引了,这显然会减少IO操作。
(2)有助于统计,select count(1) from t_users;
4、联合索引
联合索引是指对表上的多个列进行索引。
联合索引也是一棵B+树,其键值数量大于等于2。键值都是排序的,通过叶子节点可以逻辑上顺序的读出所有数据。
数据(1,1)(1,2)(2,1)(2,4)(3,1)(3,2)是按照(a,b)先比较a再比较b的顺序排列。
下列查询使用联合索引
select * from table where a=xxx and b=xxx;
select * from table where a=xxx;
下面查询不能使用联合索引
select * from table where b=xxx;