MySQL认识索引

什么是索引?

索引在MySQL中也叫是一种“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。

索引的原理

索引原理

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

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

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

磁盘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,这个理论对于索引的数据结构设计非常有帮助。

认识key

认识mysql中的key:
    index key    普通索引,能够加速查询,辅助索引
    unique key   唯一 + 索引,辅助索引
    primary key  唯一 + 非空 + 聚集索引
        主键作为条件的查询如果能够让索引生效那么效率总是更高
    foreign key  本身没有索引的,但是它关联的外表中的字段是unique索引
    primary key 和unique 标识的字段不需要再添加索引
        直接就可以利用索引加速查询
    能用unique的时候尽量不用index
        unique除了是索引之外还能做唯一约束,如果做了唯一约束
        b+树就更健康
索引 : 实际上就是一个搜索表时使用的目录
    聚集索引 : 叶子节点内直接存储行内容
        只有innodb存储引擎才有聚集索引
        主键
    辅助索引 : 叶子节点中的数字指向数据的具体地址
        innodb中和myisam中都可以存在
innodb 对应2个文件 表结构 数据 + 索引
myisam 对应3个文件 表结构 纯数据 辅助索

聚集索引和辅助索引

id name gender age
1 alex male 18
primary key   index 
聚集索引      辅助索引

创建普通索引
create index 索引名 on 表名(字段名)
desc 表名;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)           | YES  |     | NULL    |                |
| sex    | enum('male','female') | NO   |     | male    |                |
| age    | int(11)               | YES  | MUL | NULL    |                |
| dep_id | int(11)               | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+
show create table 表名;
| employee | CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `sex` enum('male','female') NOT NULL DEFAULT 'male',
  `age` int(11) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ind_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |

结论:

聚集索引:叶子节点直接存储行内容,健康的树查询速度快仅需3次IO;(select * from 表名 where id = 20;),用聚集索引查找内容;

辅助索引:叶子节点中的数字指向数据的内存地址,我们通过用辅助索引查找到数据的内存地址后需要回表到聚集索引,拿着数据的内存地址找到真实的数据,需要6次IO;

注意:如果我们在创建表结构的时候没有创建主键(聚集索引),那么mysql会自动的帮我们创建一个主键,这个主键我们是看不到的,因为只有主键才是聚集索引,那么我们以后查询表中的数据都是通过辅助索引查询的,因为辅助索引比聚集索引遇到的IO多,查询速度更慢些,所以一般我们在创建表结构的时候都是自定义主键,这样能更好的利用聚集索引的优势;

 

posted @ 2019-07-09 19:58  叫我大表哥  阅读(416)  评论(0编辑  收藏  举报