mysql索引结构原理、性能分析与优化(一)

第一部分：基础知识

索引

官方介绍索引是帮助MySQL高效获取数据的数据结构。笔者理解索引相当于一本书的目录，通过目录就知道要的资料在哪里， 不用一页一页查阅找出需要的资料。

唯一索引(unique index)

强调唯一，就是索引值必须唯一。

创建索引：

create unique index 索引名 on 表名(列名);
alter table 表名 add unique index 索引名 (列名);

删除索引：

drop index 索引名 on 表名;
alter table 表名 drop index 索引名;

主键

主键就是唯一索引的一种，主键要求建表时指定，一般用auto_increment列，关键字是primary key

主键创建：

creat table test2 (id int not null primary key auto_increment);

全文索引

InnoDB不支持，MyISAM支持性能比较好，一般在 CHAR、VARCHAR 或 TEXT 列上创建。

Create table 表名( 
    id int not null primary key anto_increment,
    title varchar(100),FULLTEXT(title)
)type=MyISAM;

单列索引与多列索引

索引可以是单列索引也可以是多列索引（也叫复合索引）。按照上面形式创建出来的索引是单列索引，现在先看看创建多列索引：

create table test3 (
    id int not null primary key auto_increment,
    uname char(8) not null default '',
    password char(12) not null,
    INDEX(uname,password)
)type=MyISAM;

注意：INDEX(a, b, c)可以当做a或(a, b)的索引来使用，但不能当作b、c或(b,c)的索引来使用。这是一个最左前缀的 优化方法，在后面会有详细的介绍，你只要知道有这样两个概念。

聚簇索引

一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。 聚簇索引确定表中数据的物理顺序。Mysql中MyISAM 表是没有聚簇索引的，innodb有（主键就是聚簇索引），聚簇索引在下面介绍innodb结构的时有详细介绍。

查看表的索引

通过命令：Show index from 表名 如：

mysql> show index from test3;  
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | 
Packed | Null | Index_type | Comment |  
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+
| test3 |          0 | PRIMARY  |        1  |    id          |     A     |   0          |     NULL | 
NULL   |     | BTREE      |         |  
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
Table：表名
Key_name：什么类型索引（这里是主键）
Column_name：索引列的字段名
Cardinality：索引基数，很关键的一个参数，平均数值组=索引基数/表总数据行，平均数值组越接近1就越有可能利用索引
Index_type：如果索引是全文索引，则是fulltext,这里是b+tree索引，b+tree也是这篇文章研究的重点之一

第二部分：MyISAM和INNODB索引结构

简单介绍B-tree B+ tree树

B-tree结构视图

一棵m阶的B-tree树，则有以下性质