索引简介

介绍

• 索引(index)是帮助MySQL高效获取数据的数据结构。
• 可以理解为：索引是数据结构；或者排好序的快速查找数据结构。
• 索引本身很大，不可能全部存在内存中，是以索引文件的形式存储在磁盘上。

优缺点

• 优点

1. 提高数据检索效率，降低数据库的io成本。
2. 通过索引对数据排序，降低排序成本，降低cpu消耗。

• 缺点

1. 降低了更新表的速度，如insert、update和delete。
2. 索引也是一张表，该表保存了主键和索引字段，并指向实体表的纪录，所以占用了更多空间。

索引结构

• BTree

BTree的特点：
（1）所有键值分布在整个树中。
（2）任何关键字出现且只出现在一个节点中。
（3）搜索有可能在非叶子节点结束。
（4）在关键字全集内做一次查找，性能逼近二分查找算法。

B-Tree存在的问题：
（1）每个节点中有key，也有data，而每一个页的存储空间是有限的，如果data数据较大时就会导致每个节点（即一个页）能存储的key的数量很小
（2）当存储的数据量很大时，同样1会导致B-Tree的深度较大，增加查询时的磁盘I/O次数，进而影响查询效率

image.png image.png

• BTree

B+Tree与BTree的不同在于：
（1）所有关键字存储在叶子节点，非叶子节点不存储真正的data。
（2）为所有叶子节点增加了一个链指针。

image.png image.png

• 总结：

因为计算机内存问题和查询效率问题，mysql选择B+Tree

索引分类

• 基本语法

# 创建索引
CREATE  [UNIQUE ]  INDEX [indexName] ON table_name(column)) 
# 删除索引
DROP INDEX [indexName] ON mytable; 
# 查看索引
SHOW INDEX FROM table_name\G

• 单一索引

# 随表一起建索引：
create table `t_emp`(
    `id` int(11) not null auto_increment,
    `name` varchar(20) default null,
    `age` int(3) default null,
    `deptId` int(11) default null,
    `empno` int not null,
    primary key (`id`),
    key 'idx_dept_id' ('deptId')    --创建索引
)engine=innodb auto_increment=1 default charset = utf8;
  
# 单独建单值索引：
create index idx_name on t_emp(name);
 
# 删除索引：
drop index idx_name on t_emp;

• 唯一索引

create unique index idx_empno on t_emp(empno);

• 主键索引

注意：新建的表可以创建主键索引，已有数据的表不能新建索引。

# 随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
   
CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
 
# 单独建主键索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);  
 
# 删除建主键索引：
ALTER TABLE customer drop PRIMARY KEY ;  
 
# 修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

• 复合索引

create index idx_age_deptid_name on t_emp(age,deptId,name);

image.png

索引创建条件

• 需要创建索引的条件

1. 主键自动建立唯一索引 。
2. 频繁作为查询条件的字段应该创建索引(where 后面的语句)。
3. 查询中与其它表关联的字段，外键关系建立索引。
4. 单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)。
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6. 查询中统计或者分组字段

• 不需要创建索引的条件

1. 表记录太少
2. 经常增删改的表或者字段
3. Where条件里用不到的字段不创建索引
4. 过滤性不好的不适合建索引

Explain（索引创建的标尺）

介绍

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是
如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

使用方式

#Explain + SQL语句
explain select c.name, ab.name ceoname from t_emp c left join (
    select a.name,b.id from t_emp a inner join t_dept b on a.id = b.CEO
) ab on c.deptId = ab.id;

可以得到以下字段

image.png

字段解释

id字段

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序。
每个id号，表示一趟独立的查询。一个sql 的查询趟数越少越好，即id号越少越好。

image.png

select_type字段

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

image.png

table字段

显示这一行的数据是关于哪张表的。

type字段

字段类型

image.png

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
（1）system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
（2）system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。

image.png

possible_keys字段

显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key字段

实际使用的索引。如果为NULL，则没有使用索引。
查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

key_len字段

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
key_len字段能够帮你检查是否充分的利用上了索引。

ref字段

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

rows字段

rows列显示MySQL认为它执行查询时必须检查的行数。越少越好。

Extra字段

image.png

参考内容

MySQL索引原理及慢查询优化