关于mysql索引:MySQL索引详解

1、前言

MySQL 数据库管理系统自身就是一个文件管理系统，尽管它的实现形式的确比较复杂，但实质上是要通过拜访磁盘能力实现数据的存储与检索。所以如果咱们想要进一步理解 MySQL 索引的的话，磁盘相干的操作也是须要大抵理解一下的。

2、硬盘的读写原理

-------------------- 磁盘的硬件组成 --------------------

一般说来，硬盘都是由盘片、磁头、盘片主轴、管制电机、磁头控制器、数据转换器、接口、缓存等几个部份组成。-------------------- 硬盘的基本概念 --------------------

-- 2.1 盘片
硬盘由多个盘片组成，盘片个别用铝合金资料做基片，高速硬盘也可能用玻璃做基片，通常有 2 - 3 个盘片。-- 2.2 盘面
每一个盘片都有两个盘面(Side)，即上、下盘面，个别每个盘面都会利用，都能够存储数据，成为无效盘片，也有极个别的硬盘盘面数为复数。每一个这样的无效盘面都有一个盘面号，按程序从上至下从 0 开始顺次编号。在硬盘零碎中，盘面号又叫磁头号，因为每一个无效盘面都有一个对应的读写磁头，因为磁盘通常有 2 - 3 个盘片，故盘面号为 0 - 3 或 0 -5。-- 2.3 磁头
每一个无效盘面都有一个对应的读写磁头，作用就是将对盘面进行读写操作。-- 2.4 磁道
磁盘在格式化时被划分成许多同心圆，这些同心圆轨迹叫做磁道(Track)。磁道从外向内从 0 开始程序编号。硬盘的每一个盘面有 300-1024 个磁道。-- 2.5 柱面
所有盘面上的同一磁道形成一个圆柱，通常称做柱面(Cylinder)，每个圆面上的磁头由上而下从 0 开始编号。数据的读 / 写按柱面进行，即磁头读 / 写数据时首先在同一柱面内从 0 盘面开始进行操作，顺次向下在同一柱面的不同盘面上进行操作，只有当同一柱面上所有的盘面全副读 / 写结束后，磁头才转移到下一柱面(同心圆再往里的柱面)。为什么磁盘的读写操作是依照柱面进行？起因是选取盘面 (磁头) 只需通过电子切换即可，而选取柱面则必须通过机械切换。又因为电子切换比在机械上磁头向邻近磁道挪动快得多，所以磁盘的读写操作是依照柱面进行。也就是说，在进行写操作时，在某个柱面的某个盘面的磁道写满数据后，切换到同一柱面的下一个盘面的磁道上接着写，一个柱面写满后，才移到下一个柱面的 0 盘面。读数据也依照这种形式进行，这样就进步了硬盘的读 / 写效率。-- 2.6 扇区
每个磁道被等分为若干个弧段，这些弧段便是硬盘的扇区(Sector)，扇区是硬盘的最小读写单元。一个扇区有两个次要局部：存储数据地点的标识符和存储数据的数据段。标识符：包含组成扇区三维地址的三组数字
        柱面号：扇区所在的磁道;
        盘面号：扇区所在的盘面;
        扇区号：扇区在磁道上的地位。也叫块号;
    数据段：512 个字节的数据

-- 2.7 盘块
操作系统与磁盘打交道的最小单位是磁盘块。因为扇区的内容比拟小，且数目泛滥，导致操作系统在寻址时比拟艰难，所以操作系统就将相邻的扇区 (2 的 N 次方个扇区) 组合在一起，造成一个块，再对块进行整体的操作。-------------------- 磁盘的拜访流程 --------------------

1、获取数据的物理地址：当操作系统须要从磁盘读取数据时，操作系统会将数据的逻辑地址传给磁盘，磁盘的控制电路依照寻址逻辑将逻辑地址翻译成物理地址(柱面号、盘面号、扇区号)。2、从指定扇区读取数据：在获取了数据的物理地址之后，接下来从扇区上读取的数据。首先磁盘须要找到柱面与盘面，即磁头须要挪动对准相应柱面下的磁道，这个过程叫做寻道，所消耗工夫叫做寻道工夫。而后指标扇区旋转到磁头下，即磁盘旋转将指标扇区旋转到磁头下，这个过程消耗的工夫叫做旋转工夫。最初将扇区中的数据传输到操作系统的内存中，所消耗工夫叫做传输工夫。3、即一次访盘申请 (读 / 写) 实现过程由三个动作组成：寻道提早（工夫）：指定磁头挪动定位到指定柱面的指定磁道 
    旋转提早（工夫）：期待指定扇区从磁头下旋转通过 
    数据传输（工夫）：数据在磁盘与内存之间的理论传输
    
-------------------- 磁盘的拜访原理 --------------------

零碎将文件存储到磁盘上时，按柱面、磁头、扇区的形式进行，即最先读 / 写第 0 磁道的第 0 磁头下 (也就是第 0 盘面的第 0 磁道) 的所有扇区。而后是同一柱面的下一磁头，……，一个柱面存储满后就推动到下一个柱面，直到把文件内容全副写入磁盘。文件的记录在同一盘组上寄存时，应先集中放在一个柱面上，而后再程序寄存在相邻的柱面上，对应同一柱面，则应该按盘面的秩序程序寄存。即：从上到下，而后从外到内。数据的读 / 写按柱面进行，而不按盘面进行。零碎也以雷同的程序读出数据。读出数据时通过通知磁盘控制器要读出扇区所在的柱面号、磁头号和扇区号（物理地址的三个组成部分）进行。磁盘控制器则 间接使磁头部件步进到相应的柱面，选通相应的磁头，期待要求的扇区挪动到磁头下。在扇区到来时，磁盘控制器读出每个扇区的头标，把这些头标中的地址信息与期待检出的磁头和柱面号做比拟（即寻道），而后，寻找要求的扇区号。待磁盘控制器找到该扇区头标时，依据其工作是写扇区还是读扇区，来决定是转换写电路，还是读出数据和尾部记录。找到扇区后，磁盘控制器必须在持续寻找下一个扇区之前对该扇区的信息进行后处理。如果是读数据，控制器计算此数据的 ECC 码，然 后，把 ECC 码与已记录的 ECC 码相比拟。如果是写数据，控制器计算出此数据的 ECC 码，与数据一起存储。在控制器对此扇区中的数据进行必要解决期间，磁 盘持续旋转。-------------------- 局部性原理与磁盘预读 --------------------

因为存储介质的个性，磁盘自身存取就比主存慢很多，再加上机械运动消耗，磁盘的存取速度往往是主存的几百分分之一，因而为了提高效率，要尽量减少磁盘 I /O。为了达到这个目标，磁盘往往不是严格按需读取，而是每次都会预读，即便只须要一个字节，磁盘也会从这个地位开始，程序向后读取肯定长度的数据放入内存。这样做的理论依据是计算机科学中驰名的局部性原理：1、当一个数据被用到时，其左近的数据也通常会马上被应用。2、程序运行期间所须要的数据通常比拟集中。因为磁盘程序读取的效率很高（不须要寻道工夫，只需很少的旋转工夫），因而对于具备局部性的程序来说，预读能够进步 I / O 效率。预读的长度个别为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区宰割为间断的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为 4k），主存和磁盘以页为单位替换数据。当程序要读取的数据不在主存中时，会触发一个缺页异样，此时零碎会向磁盘收回读盘信号，磁盘会找到数据的起始地位并向后间断读取一页或几页载入内存中，而后异样返回，程序持续运行。

3、索引的根本应用

-------------------- 索引简介 --------------------
数据库在保留数据时，除了保留自身之外，还保护着一个满足特定查找算法的数据结构，这些数据结构以某种形式指向数据，这样就能够在这些数据结构的根底上实现高级查找算法，这种数据结构就是索引。简而言之，索引就是帮忙 MySQl 高效获取数据的数据结构。-------------------- 索引语法 --------------------

---------- 一般索引的创立语法 ----------

-- 形式一：间接创立索引
CREATE INDEX index_name ON table_name (column_name);

-- 形式二：批改表构造时增加索引
ALTER TABLE table_name ADD INDEX index_name ON column_name;

-- 形式三：创立表时间接增加索引
CREATE TABLE dept (
  dept_id INTEGER PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(32) DEFAULT NULL,
  INDEX id_dept_deptname (dept_name)
)

---------- 主键索引的创立语法 ----------

-- 数据库会主动为主键字段设置主键索引
CREATE TABLE dept (
  dept_id INTEGER PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(32) DEFAULT NULL
)

-------------------- 惟一索引的创立语法 --------------------

-- 形式一：间接创立索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

-- 形式二：批改表构造时增加索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name ON column_name;

-- 形式三：创立表时间接增加索引

-- 独自设置惟一索引
CREATE TABLE dept (
  dept_id INTEGER PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(32) DEFAULT NULL, 
  UNIQUE INDEX id_dept_deptname (dept_name)
)

-- 字段增加惟一束缚之后，数据库会主动为存在惟一束缚的列设置惟一索引
CREATE TABLE dept (
  dept_id INTEGER PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(32) UNIQUE DEFAULT NULL
)

---------- 组合索引的创立语法 ----------

-- 创立表时间接增加索引
CREATE TABLE dept (
  dept_id INTEGER PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(32) DEFAULT NULL,
    manager_id INTEGER,
    INDEX idx_deptnameManagerid (dept_name,manager_id)
)

-- 批改表构造时增加索引
ALTER TABLE table_name ADD INDEX index_name (column_name,column_name);

---------- 索引的查问删除语法 ----------

-- 查问索引
SHOW INDEX FROM table_name;

-- 删除索引
DROP INDEX index_name ON table_name;