MySQL 索引及执行计划 (一)
索引有什么作用？
提供了类似于书中目录的作用,目的是为了优化查询
索引的种类(算法)
B树索引
Hash索引
R树
Full text
GIS
聚集索引(C)
(1)表中设置了主键,主键列就会自动被作为聚集索引.
(2)如果没有主键,会选择唯一键作为聚集索引.
(3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)
# 聚集索引如何构建B树结构？
(1) 在建表时,设置了主键列(ID)
(2) 在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚集索引组织表)
(3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点
辅助索引(S)
(1). 索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度
聚集索引和辅助索引构成区别
聚集索引只能有一个,非空唯一,一般时主键
辅助索引,可以有多个,时配合聚集索引使用的
聚集索引叶子节点,就是磁盘的数据行存储的数据页
MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
辅助索引,只会提取索引键值,进行自动排序生成B树结构
辅助索引细分
1.普通的单列辅助索引
2.联合索引
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
3.唯一索引
索引列的值都是唯一的.
索引树的高度问题
1. 数据量级, 解决方法:分表,分库,分布式
2. 索引列值过长 , 解决方法:前缀索引
3. 数据类型:
变长长度字符串,使用了char,解决方案:变长字符串使用varchar
enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)
1 2 3
索引的基本管理
创建表
CREATE DATABASE world;
USE world;
CREATE TABLE city(
ID INT(11) PRIMARY KEY AUTO_INCREMENT,
Name CHAR(35) NOT NULL,
CountryCode CHAR(3) NOT NULL,
District CHAR(20) NOT NULL,
Population INT(11) NOT NULL DEFAULT 0
) ENGINE=INNODB CHARSET=utf8;
desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | NULL | |
| CountryCode | char(3) | NO | | NULL | |
| District | char(20) | NO | | NULL | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
key : 有没有索引,索引类型
PRI: 主键索引
UNI: 唯一索引
MUL: 辅助索引(单列,联和,前缀)
单列普通辅助索引
创建索引
# 方法一：alter table city add index idx_name(name);
# 方法二：create index idx_name1 on city(name);
查看索引
show index from city;
删除索引
alter table city drop index idx_name1;
覆盖索引(联合索引)
alter table city add index idx_co_po(countrycode,population);
前缀索引
alter table city add index idx_di(district(5));
# 注意：数字列不能用作前缀索引。
唯一索引
alter table city add unique index idx_uni1(name);
