139 MySQL索引

一、初识索引

1.1 为什么要有索引

一般的应用系统，读往往是比写的速度要块的，而且插入操作和一般的更新操作很少会出现性能问题，在生产环境中，我们遇到的最多的，也就是最容易出现问题的，还是一些比较复杂的查询操作，因此对查询语句的优化显然是重中之重的，而优化加速查询，就肯定会提到数据表的索引问题

1.2 什么是索引？

索引在MySQL中也叫做是一种键，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能是非常关键的，尤其是当表中的数据量越来越大时，索引对于性能的影响是很重要的

索引优化应该是对查询新能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

索引相当于字典的音序表，如果想要查某一个字，直接去音序表中查这个子的读音，第几声，查到第几页，这样就缩小了查寻的范围了，否则就需要从几百页中一个字一个字的去找，查询的速度会大大的降低

若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要

二、索引的原理

2.1 索引的原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。>

数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是logN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

2.2 磁盘IO与预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内>，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

三、索引的数据结构

四、MySQL索引管理

4.1 功能

对于MySQL来说，索引就是能加速查找数据的速度，优化数据的查询速度
mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

4.2 MySQL常用的索引

1.普通索引index：加速查找

2.唯一索引：(primary key / unique)

主键索引 primary key ：加速查找+约束（不为空、不能重复）
唯一索引 unique:加速查找+约束（不能重复）

3.联合索引

primary key(id,name):联合主键索引
unique(id,name):联合唯一索引
index(id,name):联合普通索引

4.3 各个索引的应用场景

举个列子来说，比如是一个超市的会员卡系统

这个系统有一个会员表
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

4.4 索引的两大类型hash与btree

我们可以在创建索引的时候，为其指定索引类型，分两类

1.hash类型的索引：查询单条数据时块，范围查询慢

2.btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

不同的存储引擎支持的索引类型也不一样：

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

4.5 创建/删除索引的语法

# 方法一：创建表时
	create table 表名(
    	   字段名1  数据类型 [完整性约束条件…],
           字段名2  数据类型 [完整性约束条件…],
           [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
           [索引名]  (字段名[(长度)]  [ASC |DESC]) 
           );
                
# 方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;


# 方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
                             
# 删除索引：DROP INDEX 索引名 ON 表名字;

4.6 示例

# 方式一
create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key uni_id(id),
    index ix_name(name) # index没有key
);



# 方式二:CREATE在已存在的表上创建索引
create index ix_age on t1(age);


# 方式三:CREATE在已存在的表上创建索引
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);

# 查看t1表的索引
mysql> show create table t1;
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  UNIQUE KEY `uni_id` (`id`),
  KEY `ix_name` (`name`),
  KEY `ix_age` (`age`),
  KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

五、测试索引

5.1 数据准备

# 1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

# 2. 创建存储过程，实现批量插入记录
delimiter $$ # 声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'eva','female',concat('eva',i,'@user'));
        set i=i+1;
    end while;
END$$ # $$结束
delimiter ; # 重新声明分号为结束符号

# 3. 查看存储过程
show create procedure auto_insert1\G 

# 4. 调用存储过程
call auto_insert1();

1.在没有索引的前提下测试以下查询速度：

没有索引的时候，mysql根本就不知道是否右2222222222这条记录，所以mysql会把这张表从头到尾的依次扫描一遍，然后再给结果，在这里有多少个磁盘就会io对少次，所以查询速度慢

mysql> select * from s1 where id=22222222222;
Empty set (0.13 sec)

2. 我们在表创建时就没有创建索引的情况下给表中的字段新建一个索引：

我们会发现，在表中已经存储了大量的数据前提下，在去给表中的某一个字段建立索引的时候，建索引的速度会很慢

mysql>create index x on s1(id);
Query OK, 0 rows affected (1.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

3. 在表索引建立完毕以后，去测试以下查询的速度：

这个时候，我们会发现建立完索引之后，查询的速度明显的提高了

mysql> select * from s1 where id=222222222211;
Empty set (0.00 sec)

注意：

我们在给表建立索引之后，查询数据时mysql先去索引表里根据b+树的索索原理快速搜索到id等于2222222222的记录不存在，大大降低，因而速度明显提升
我们可以去mysql的data目录下找到该表，可以看到占用的硬盘空间多了
需要注意，如下图

由于表的email字段并没有创建索引，所以通过email字段来查询数据速度依旧很慢
```
mysql> select * from s1 where email='aaaaa';
Empty set (0.14 sec)
```

5.2 小结

一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引
在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快，比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。建完以后，再查询就会很快了。
需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.

六、正确使用索引

6.1 索引未命中

并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题：

范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like、大于号、小于号

between and

尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  | MUL | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(5)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

mysql> drop index x on s1;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index y on s1;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(5)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

分析原因：

我们编写存储过程为表s1批量添加记录，name字段的值均为eva，也就是说name这个字段的区分度很低（gender字段也是一样的，我们稍后再搭理它）

回忆b+树的结构，查询的速度与树的高度成反比，要想将树的高低控制的很低，需要保证：在某一层内数据项均是按照从左到右，从小到大的顺序依次排开，即左1<左2<左3<...

而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用b+树存放这些等值的数据，只能增加树的高度，字段的区分度越低，则树的高度越高。极端的情况，索引字段的值都一样，那么b+树几乎成了一根棍。本例中就是这种极端的情况，name字段所有的值均为'nick'

现在我们得出一个结论：为区分度低的字段建立索引，索引树的高度会很高，然而这具体会带来什么影响呢？？？

如果条件是name='xxxx',那么肯定是可以第一时间判断出'xxxx'是不在索引树中的（因为树中所有的值均为'eva’），所以查询速度很快
如果条件正好是name='eva',查询时，我们永远无法从树的某个位置得到一个明确的范围，只能往下找，往下找，往下找。。。这与全表扫描的IO次数没有多大区别，所以速度很慢
索引列不能在条件中参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)

and/or
1. and与or的逻辑
  - 条件1 and 条件2：所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
  - 条件1 or 条件2：只要有一个条件成立则最终结果就成立
2. and的工作原理
  - 条件：a = 10 and b = 'xxx' and c > 3 and d =4
  - 索引：制作联合索引(d,a,b,c)
  - 工作原理：对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序
3. or的工作原理
  - 条件：a = 10 or b = 'xxx' or c > 3 or d =4
  - 索引：制作联合索引(d,a,b,c)
  - 工作原理：对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d

在左边条件成立但是索引字段的区分度低的情况下（name与gender均属于这种情况），会依次往右找到一个区分度高的索引字段，加速查询。

posted @ 2019-10-17 23:50 Cecilia陈阅读(145) 评论(0) 编辑收藏举报

刷新页面返回顶部

Cecilia Chen

孤独，是人一生最好的修行