微网站建设制作设计,用帝国cms做的网站首页,费县做网站,软装设计ppt目录 一. 索引的价值
二. 数据库与磁盘的IO
2.1 磁盘的结构
2.2 磁盘访问
2.3 MySQL与磁盘的交互
三. 对索引的理解
3.1 Page的结构
3.2 B树和B树索引结构
3.2.1 B树的结构
3.2.2 B树
3.3 聚簇索引和非聚簇索引
四. 索引的操作
4.1 索引的创建
4.2 索引的查看
4.…目录 一. 索引的价值
二. 数据库与磁盘的IO
2.1 磁盘的结构
2.2 磁盘访问
2.3 MySQL与磁盘的交互
三. 对索引的理解
3.1 Page的结构
3.2 B树和B树索引结构
3.2.1 B树的结构
3.2.2 B树
3.3 聚簇索引和非聚簇索引
四. 索引的操作
4.1 索引的创建
4.2 索引的查看
4.3 使用索引字段检索数据
4.4 索引的删除
五. 复合索引
5.1 复合索引的价值
5.3 复合索引操作
六. 总结 一. 索引的价值
索引能够极大提高数据库检索性能对数据库添加合适的索引能够在不额外使用内存不编写新的程序不调用sql的情况下大大提高数据的检索效率。
一般的企业级大型数据库管理的数据量可以达到数千万甚至上亿级别更有甚者超过百亿千亿的数据量如果不添加适当的索引那么查找数据就会变为逐个遍历数据查询这样的查找效率显然是不可接受的。而数据库一般都是远端网络服务数据库中的数据存储在磁盘中磁盘和网络的IO效率都是十分低下的。那么建立适当的索引就能够大大减少IO的次数提高数据检索效率。
索引底层是通过特定的数据结构来实现的常见的可用于检索的数据结构有二叉搜索树、平衡树AVL树、红黑树、哈希表、B/B树等。在数据库中通常使用B树作为检索的数据结构。
然而索引并不是没有代价的对数据库添加索引会降低插入和删除数据的效率因为插入和删除数据需要调整索引的数据结构因此索引适用于对存有海量数据、被高频检索的数据库。
数据库的索引可以分为四类1主键索引2唯一键索引3普通索引4全文索引。在创建表的时候如果指定了主键/唯一键那么MySQL就会自动为主键和唯一键添加索引而普通索引和全文索引需要手动添加。
二. 数据库与磁盘的IO
2.1 磁盘的结构
数据库中的数据是存储在磁盘上的而磁盘在计算机中是一个机械设备相比于内存和寄存器效率十分低下那么要提高数据库的效率就有尽量减少与磁盘之间的IO操作。
首先来了解一下磁盘的基本结构我们主要关注磁盘的盘片即可单个磁盘盘片是双面有效的其上下面都可以存储数据磁盘盘面上被划分为若干个同心圆每个同心圆又按照相等的角度划分为一个个扇区每个删除存储固定大小的数据。一般而言一个扇区存储512bytes数据近年来单个扇区的数据正有被扩展为4KB大小的趋势。
在Linux操作系统下绝大部分文件都是存储在存盘上的除了proc、sys等少部分内存级文件。 图2.1 磁盘的物理结构和磁盘盘片 2.2 磁盘访问
从磁盘中读取数据首先要定位到数据所在的扇区如图2.2为磁盘的寻址结构只要知道磁头、柱面和扇区所对应的编号就能获取目标扇区。
柱面对于多盘磁盘每个磁盘都是两面所有盘面上相同半径的磁道就构成了柱面。磁头用于读取磁盘盘片上存储的数据每个盘面都对应一个磁头。CHS寻址通过定位磁头Head、柱面Cylinder和扇区Sector就能够找到目标扇区这种寻址方法称为CHS寻找法。在内存及上层应用软件中并不是直接采用CHS来进行寻址的而是采用LBA法一种线性寻址的方法来进行寻址操作系统会将线性地址进行转换得到对应的CHS地址。 图2.2 磁盘寻址结构 一个扇区的大小一般为512bytes操作系统从磁盘中读取数据的基本单位是4KB不让OS读取磁盘数据的基本单位和扇区大小保持一致的原因在于
在软件和硬件层面实现解耦如果操作系统IO基本单位和磁盘扇区大小一致那么当磁盘扇区大小改变时操作系统源代码也需要重新编写不符合软件工程高内聚、低耦合的设计理念。一次IO读取512bytes的数据还是偏少磁盘IO效率低的主要问题并不在于读取本身而在于读取前的准备工作比如定位扇区真正开始读取磁盘数据后效率并不低。
磁盘的访问可分为连续访问和随机访问
磁盘的连续访问多次访问磁盘的地址相邻不需要过多的定位就可以找到对应的扇区。磁盘的随机访问多次访问磁盘的地址不连续每次访问需要磁盘磁头摆动较大的范围才能找到对应的扇区位置。磁盘时通过磁头的机械运动来进行寻址的对于随机访问如果摆动范围过大会严重降低磁盘的IO效率连续访问的效率要高于随机访问。
2.3 MySQL与磁盘的交互
MySQL数据库是一款上层应用软件其作用是对海量数据进行管理相比于操作系统需要更多的IO操作因此MySQL一次IO的基本单位是16KB16KB的基本IO单元在MySQL中被称为页Page这里要注意区分MySQL于操作系统Page的区别操作系统的Page基本单位是4KB。
MySQL是应用层软件无法与磁盘直接交互需要现将数据从磁盘预加载到内存中然后MySQL才能将数据读取。如果需要更新数据库中的数据需要以4KB为基本单位将更新后的数据写到内存中然后再将数据刷新到磁盘中去。图2.3为MySQL与磁盘之间交互的示意图。 图2.3 MySQL与磁盘IO的基本单位 三. 对索引的理解
3.1 Page的结构
设想一本几百页的厚书我们希望检索其中的特定内容如果我们逐页遍历检索那么效率就会十分低下而现实中一般采用目录的方式来提高检索效率。书本中的目录就类似于MySQL数据库中的检索。对于一张表的主键key primary和 唯一键uniqueMySQL会自动为其创建索引结构。
创建测试表
mysql create table stu (- id int unsigned primary key,- age tinyint unsigned,- name varchar(20) not null- );
Query OK, 0 rows affected (0.15 sec)
插入数据
mysql insert into stu values- (3,20,张三),(1,25,李四),- (4,18,王五),(5,23,赵六),- (2,28,田七);
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
查看表中数据
mysql select * from stu;
------------------
| id | age | name |
------------------
| 1 | 25 | 李四 |
| 2 | 28 | 田七 |
| 3 | 20 | 张三 |
| 4 | 18 | 王五 |
| 5 | 23 | 赵六 |
------------------
5 rows in set (0.00 sec)通过观察发现我们看到的表中数据与实际插入数据的顺序并不相同显然数据被重新排序了数据之所以被重新排序就是因为MySQL为数据库建立了检索。 对单个Page的理解 在MySQL中数据IO的基本单位是页一个页的大小为16KBMySQL中可能会同时存在多个页这些页需要被管理起来需要通过特定的数据结构进行组织。不同的Page在MySQL中页会被组织为双向链表每个Page中包含一个prev指针一个Next指针指向前后的Page同时每一条数据记录之间也会被组织为单链表。 图3.1 单个Page的一般结构 MySQL的单个Page大小16KB在单个Page中通过逐个遍历进行数据检索效率还是太低为了提高检索效率每个Page中对应索引字段还会建立相应的目录这样就能通过索引键的值锁定范围以降低IO的次数提高效率。 图3.2 带有目录的单个Page结构 对多个Page的理解 如果MySQL中管理的数据过多一个Page无法容纳所有数据数据那么就需要更多的Page来对数据进行管理在每个Page之间通过双向链表结构进行连接。这样在进行数据检索的时候就可以遍历多个Page结构通过单个Page里面的目录判断所要检索的数据是否位于某个Page之中这就体现了单个Page内部目录的作用。 图3.3 多个Page的组织结构 然而如果MySQL中管理的数据量进一步加大那么仅仅通过单链表组织结构依次遍历每一个Page仍然会存在IO次数多检索效率低下的问题因此对于MySQL中的每个Page依然会对其建立目录索引结构。
解决Page过多造成检索效率低下问题的方法给Page也带上目录。
目录页的本质也是Page在目录页中记录所指向页的最小键值数据。一般的数据库都采用B树作为索引数据结构非叶子节点目录页只记录目录数据而不存储有效数据。目录页只存储目录不存储有效数据的目的是提高单页能够存放的目录数据量以减少IO的次数提高检索的效率。
在MySQL中检索使用的数据库也是存储在磁盘中的。通过图3.4我们可以发现这就是B树大部分情况下检索都是通过B树来实现的而每个页都是B树的一个节点。在实际的工程项目中常用的检索结构还有二叉搜索树、平衡二叉树、哈希表等而采用B树而不是其它几种数据结构的原因在于
二叉搜索树在极端情况下会退化为线性检索。平衡二叉树虽然能避免二叉搜索树退化为线性检索的问题但其检索效率仍然为O(logN)而从磁盘中读取数据是一种效率低下的操作O(logN)比O(1)的效率会很明显降低。哈希表虽然可实现平均时间复杂度为O(1)的检索但是如果发生过多的哈希冲突效率依然会明显降低甚至退化为O(N)的检索效率。 图3.4 MySQL检索结构 3.2 B树和B树索引结构
3.2.1 B树的结构
B树是一种高效的搜索树结构对于一个M阶的B树具有以下特点
B树的根节点至少有两个孩子节点。每个分支节点有k个孩子节点和k-1个键值其中 ceil(m/2) k m。每个叶子节点的键值都是小到大排序的键值key为子树的阈值划分。所有叶子节点都在同一层。 图3.5 B树结构 3.2.2 B树
B树相对于B树有以下的区别
每个节点的键值和孩子节点的数目是相同的。只有叶子节点存有有效数据分支节点存储的都是键值。孩子节点指针p[i]所执行的子树的节点值范围在[k[i], k[i1])之间每个子树的第一个节点的值是其父亲节点对应的键值k[i]。所有孩子节点都被指针连接起来了。 图3.6 B树的结构 3.3 聚簇索引和非聚簇索引
MySQL最常用的两种搜索引擎为MyISAM和InnoDB这两种引擎创建的索引底层都是通过B树来实现的MyISAM和InnoDB的区别在于
MyISAM采用的是非聚簇索引存储的有效数据和B树检索结构分离B树叶子节点中的有效数据data是实际存储数据的映射地址。InnoDB采用的是聚簇索引有效数据就被存放在B树的叶子节点data区域。
下面来对MyISAM和InnoDB这两种搜索引擎进行测试打开两个终端其中一个终端启动mysql服务终端A另一个终端进行监测终端B按照如下步骤进行测试
在终端A下创建名为test_index的数据库终端B进入路径/var/lib/mysql/test_index下。在test_index数据库下创建两张表testMyISAM和testInnoDB分别采用MyISAM和InnoDB为搜索引擎。在终端B下通过ll指令查看目录/var/lib/mysql/test_index下的文件及其属性信息。
通过观察发现在/var/lib/mysql/test_index路径下表testMyISAM对应2个文件testMyISAM.frm为表的属性相关信息、testMyISAM.idb为索引结构和有效数据表testInnoDB对应三个文件testInnoDB.frm为表的属性相关信息、testInnoDB.MYI为索引、testInnoDB.MYD为有效数据。
终端A
mysql create database test_index;
Query OK, 1 row affected (0.00 sec)mysql use test_index;
Database changed
mysql create table testMyISAM (- id int unsigned primary key,- name varchar(10) not null- );
Query OK, 0 rows affected (0.22 sec)mysql create table testInnoDB (- id int unsigned primary key,- name varchar(10) not null- )engine MyISAM;
Query OK, 0 rows affected (0.04 sec)
终端B
[rootVM-8-5-centos ~]# cd /var/lib/mysql/test_index
[rootVM-8-5-centos test_index]# pwd
/var/lib/mysql/test_index
[rootVM-8-5-centos test_index]# ll
total 128
-rw-r----- 1 mysql mysql 61 Jan 2 18:53 db.opt
-rw-r----- 1 mysql mysql 8586 Jan 2 18:56 testInnoDB.frm
-rw-r----- 1 mysql mysql 0 Jan 2 18:56 testInnoDB.MYD
-rw-r----- 1 mysql mysql 1024 Jan 2 18:56 testInnoDB.MYI
-rw-r----- 1 mysql mysql 8586 Jan 2 18:55 testMyISAM.frm
-rw-r----- 1 mysql mysql 98304 Jan 2 18:55 testMyISAM.ibd
四. 索引的操作
4.1 索引的创建 主键索引 通过以下三种方法可以创建主键索引
方法1直接在表成员声明后面添加key primary关键字声明主键。
mysql create table t1 (- id int primary key,- name varchar(10) not null- );
Query OK, 0 rows affected (0.16 sec)
方法2在创建表的指令最后单独声明主键。
mysql create table t2 (- id int unsigned,- name varchar(10) not null,- primary key(id)- );
Query OK, 0 rows affected (0.09 sec)
方法3在完成表的创建后添加主键。
mysql create table t3 (- id int unsigned,- name varchar(10) not null- );
Query OK, 0 rows affected (0.27 sec)mysql alter table t3 add primary key(id);
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0 唯一键索引 如果对一个唯一键添加not null声明那么这个唯一键索引就等同于主键索引有以下三种方法可以创建唯一键索引
方法1直接在表成员声明后面添加unique关键字声明唯一键。
mysql create table t4 (- id int unsigned unique,- name varchar(10)- );
Query OK, 0 rows affected (0.20 sec)
方法2在创建表的指令最后单独声明唯一键。
mysql create table t5 (- id int unsigned,- name varchar(10),- unique(id)- );
Query OK, 0 rows affected (0.39 sec)
方法3在完成表的创建后添加唯一键。
mysql create table t6 (- id int unsigned,- name varchar(10)- );
Query OK, 0 rows affected (0.24 sec)mysql alter table t6 add unique(id);
Query OK, 0 rows affected, 1 warning (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 1 普通索引 普通索引相比于主键索引和唯一键索引其值可以重复有以下三种方法可以创建普通索引
方法1在创建表的指令后面声明普通索引。
mysql create table t7 (- id int unsigned not null,- name varchar(10) not null,- index(id)- );
Query OK, 0 rows affected (0.27 sec)
方法2在创建完表后通过alter指令添加普通索引。
mysql create table t8 (- id int unsigned not null,- name varchar(10) not null- );
Query OK, 0 rows affected (0.13 sec)mysql alter table t8 add index(id);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
方法3在创建完表后添加普通索引自定义索引名称。
mysql create table t9 (- id int unsigned not null,- name varchar(10) not null- );
Query OK, 0 rows affected (0.10 sec)mysql create index myIndex on t9(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0 全文索引 当对大段文字进行索引的时候需要用到全文索引。MySQL支持全问索引但是MySQL的全文索引受到以下的限制
必须使用MyISAM搜索引擎。只能支持英文索引不支持中文索引。
mysql create table t10 (- id int unsigned primary key,- title varchar(100) not null,- body text,- fulltext (title,body)- );
Query OK, 0 rows affected (1.82 sec)
4.2 索引的查看
方法1show keys from 表名 \G
mysql show keys from t1 \G
*************************** 1. row ***************************Table: t1Non_unique: 0Key_name: PRIMARY -- 索引名称Seq_in_index: 1 Column_name: id -- 建立索引的字段名称Collation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREE -- 索引使用的数据结构Comment:
Index_comment:
1 row in set (0.00 sec)
方法2show index from 表名 \G
mysql show index from t1 \G
*************************** 1. row ***************************Table: t1Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment:
Index_comment:
1 row in set (0.00 sec)
方法3desc 表名;
desc为打印表的详细属性信息其中字段key就是索引信息PRI表示主键索引、UNI表示唯一键索引、MUL表示普通索引。
mysql desc t1;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)
4.3 使用索引字段检索数据
通过where条件筛选可以实现通过索引来检索特定的行数据。我们采用 explain 指令 的方法来查看MySQL通过索引字段检索数据的底层实现。
explain不实际执行指令输出指令的底层实现流程。
如果explain后面跟的检索指令所使用的条件筛选字段没有对应的索引那么其explain的key字段为NULL反之key字段为其使用的索引名称。
mysql insert into t1 values (1,zhangsan),(2,lisi);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql explain select * from t1 where namezhangsan \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: NULLtype: ALL
possible_keys: NULLkey: NULL -- 不使用索引遍历查找key_len: NULLref: NULLrows: 2filtered: 50.00Extra: Using where
1 row in set, 1 warning (0.00 sec)mysql explain select * from t1 where id1 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: NULLtype: const
possible_keys: PRIMARYkey: PRIMARY -- 使用主键索引查找key_len: 4ref: constrows: 1filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.00 sec)对于全文索引通过like模糊匹配无法有效利用全文索引如下所示代码向建立了全文索引的表t10中插入数据然后通过模糊匹配查找带有 DataBase 的文本explain查看其底层实现发现并没有使用全文索引而是遍历匹配。 使用全文检索的语法match ... against ... 通过模糊匹配检索文本
mysql insert into t10 values - (1,MySQL Tutorial,DBMS stands for DataBase ...),- (2,How To Use MySQL Well,After you went through a ...),- (3,Optimizing MySQL,In this tutorial we will show ...),- (4,1001 MySQL Tricks,1. Never run mysqld as root. 2. ...),- (5,MySQL vs. YourSQL,In the following database comparison ...),- (6,MySQL Security,When configured properly, MySQL ...);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql select * from t10 where body like %DataBase%;
-----------------------------------------------------------------
| id | title | body |
-----------------------------------------------------------------
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
-----------------------------------------------------------------
2 rows in set (0.00 sec)mysql explain select * from t10 where body like %DataBase% \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t10partitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 6filtered: 16.67Extra: Using where
1 row in set, 1 warning (0.00 sec)
通过全文检索匹配文本
mysql select * from t10 where match (title,body) against (DataBase);
-----------------------------------------------------------------
| id | title | body |
-----------------------------------------------------------------
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
-----------------------------------------------------------------
2 rows in set (0.00 sec)mysql explain select * from t10 where match (title,body) against (DataBase) \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t10partitions: NULLtype: fulltext
possible_keys: titlekey: titlekey_len: 0ref: constrows: 1filtered: 100.00Extra: Using where; Ft_hints: sorted
1 row in set, 1 warning (0.00 sec)
4.4 索引的删除
根据索引的不同类型有以下三种方法可以删除索引
方法1用于删除主键索引alter table 表名 drop primary key;方法2用于删除除主键索引外的其它索引alter table 表名 drop index 索引名;方法3drop index 索引名 on 表名;
其中方法2和方法3所使用的表名是show index from ... 查询到的Key_name值。
mysql alter table t1 drop primary key;
Query OK, 2 rows affected (0.62 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql alter table t5 drop index id;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql drop index myIndex on t9;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
五. 复合索引
5.1 复合索引的价值
对于使用InnoDB建立索引的表由于检索使用的B树叶子节点直接存储有效数据这种索引叫做聚簇索引表中有效数据要按照主键来进行聚簇因此InnoDB必须要有主键。
对应使用InnoDB索引建立的表创建辅助索引辅助索引所使用的B树叶子节点的data记录的是对应的主键通过辅助索引检索数据时要先通过辅助索引的B树拿到对应的主键值然后再根据拿到的主键值去主键索引建立的B树中检索数据。这样就需要二次索引。 图5.1 B树主键StuId索引 图5.2 name辅助索引 复合索引的价值在于能够避免二次检索造成效率降低的问题复合索引就是在使用非主键天剑的索引B树结构的叶子节点data中添加复合的字段值例如如果要建立(name,age)的复合索引那么会以name为键值创建B树并且在叶子节点上添加对应age的值。 图5.3 (name,age)复合索引 5.3 复合索引操作 创建复合索引 语法create index 索引名称 on 表名称(字段1, 字段2, ... ...);
mysql create table stu (- StuId int unsigned primary key,- age tinyint unsigned,- name varchar(20) not null- );
Query OK, 0 rows affected (0.23 sec)mysql insert into stu values - (12,32,Mike),(15,24,Lisa),(19,52,Cathy),- (22,15,Alice),(34,45,Tom),(45,32,Bob);
Query OK, 6 rows affected (0.06 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql create index myIndex on stu(name,age); -- 创建name/age复合索引
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
以(name, age)复合索引为例这个索引对应的B树是以name为键值来创建的这就引出了对于复合索引使用时的一条原则最左匹配原则。 最左匹配原则 对于复合索引(fild1, fild2, ... )如果使用的检索条件是符合索引靠近左边的字段那么就走复合索引进行检索如果不是就遍历检索。如果where条件中同时指出fild1和fild2的值或者只给出fild1的值那么就走复合索引如果只给出fild2的值那么就遍历查找。
mysql explain select * from stu where nameMike \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: stupartitions: NULLtype: ref
possible_keys: myIndex -- 使用myIndex索引进行查找key: myIndexkey_len: 62ref: constrows: 1filtered: 100.00Extra: Using index
1 row in set, 1 warning (0.00 sec)mysql explain select * from stu where nameMike and age32 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: stupartitions: NULLtype: ref
possible_keys: myIndex -- 使用myIndex索引进行查找key: myIndexkey_len: 64ref: const,constrows: 1filtered: 100.00Extra: Using index
1 row in set, 1 warning (0.00 sec)mysql explain select * from stu where age32 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: stupartitions: NULLtype: index
possible_keys: NULL -- 遍历查找不使用索引key: myIndexkey_len: 64ref: NULLrows: 6filtered: 16.67Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)索引覆盖 索引覆盖就是在执行一个查询语句的时候在辅助索引中就能够查找到对应的数据不需要再拿着主键回表查询变二次检索为一次检索这样就能够很大程度上提高检索的效率。使用复合索引进行检索就能够实现索引覆盖。
通过explain语句查看检索的底层实现逻辑Extra字段值 using index 表示使用索引Using where表示全文遍历检索。
mysql explain select name,age from stu where nameMike \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: stupartitions: NULLtype: ref
possible_keys: myIndexkey: myIndexkey_len: 62ref: constrows: 1filtered: 100.00Extra: Using index
1 row in set, 1 warning (0.00 sec)
六. 总结
数据库索引的价值在于避免遍历所有数据进行检索大大提高了数据检索的效率。磁盘通过CHS法来进行寻址定位到要检索的数据所在的扇区磁盘是计算机体系中的一种机械设备IO效率相比于内存和寄存器十分低下对于数据库这种频繁进行IO操作的应用软件减少与磁盘的IO次数是提高效率的关键。操作系统与磁盘的IO基本单位是4KBMySQL与磁盘IO的基本单位是16KBMySQL要读取或者向磁盘中写数据需要以内存为中间媒介。在MySQL中一般以B树作为索引使用的数据结构每个16KB的Page是一个B树的节点除了叶子节点的Page以外其他的分支节点仅存储目录信息而存有有效数据的叶子节点中也有对应的目录信息。索引分为主键索引、唯一键索引、普通索引和全文索引如果在对表的某个字段声明了主键或唯一键那么mysql会自动为其创建索引主键索引和唯一键索引对应的字段不允许有重复值而普通索引对应的字段有重复值。MyISAM和InnoDB都是使用B树作为索引数据结构的区别在于MyISAM是聚簇索引即有效数据和索引结构放在一起InnoDB是非聚簇索引即有效数据和索引结构分离。InnoDB创建辅助索引其索引结构中data处的数据为对应主键值需要二次检索才能够拿到有效数据。使用复合索引能够实现索引覆盖从而避免二次索引提高数据检索效率复合索引遵循最左匹配原则。
