mysql索引引擎:InnoDB(5.5后默认)和MyISAM(5.5前默认)
InnoDB和MyISAM索引结构:B+Tree
区别:
MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。
MyISAM类型的表强调的是性能,其执行速度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能。
InnoDB使用行级锁并发性好 ,MyISAM使用表级锁,并发性差。
B-Tree
由于B-Tree的特性,在B-Tree中按key检索数据的算法非常直观:首先从根节点进行二分查找,如果找到则返回对应节点的data,否则对相应区间的指针指向的节点递归进行查找,直到找到节点或找到null指针,前者查找成功,后者查找失败。
B-Tree有一系列有趣的性质,例如一个度为d的B-Tree,设其索引N个key,则其树高h的上限为logd((N+1)/2)logd((N+1)/2),检索一个key,其查找节点个数的渐进复杂度为O(logdN)O(logdN)。从这点可以看出,B-Tree是一个非常有效率的索引数据结构。另外,由于插入删除新的数据记录会破坏B-Tree的性质,因此在插入删除时,需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质.
B+Tree
B-Tree有许多变种,其中最常见的是B+Tree. B+Tree内节点不存储data,只存储key;叶子节点不存储指针。
一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化,增加了顺序访问指针。
在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能,例如图4中如果要查询key为从18到49的所有数据记录,当找到18后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率。
MyISAM引擎
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图:
InnoDB引擎
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
最左前缀原理
在创建联合索引时,理论上索引对顺序是敏感的,但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引。
案例:
》desc token_write
+-------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| token | varchar(64) | NO | | NULL | |
| uname | varchar(64) | NO | | NULL | |
| desc | varchar(64) | YES | | NULL | |
| ct | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-------------+------+-----+-------------------+----------------+
》show index from token_write;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+------------+---------+---------------+
| token_write | 0 | PRIMARY | 1 | id | A | 16 | NULL | BTREE | | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+------------+---------+---------------
》alter table token_write add INDEX threeindex (token,uname,`desc`);
+-------------+------------+------------+--------------+-------------+-----------+-------------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type | Comment | Index_comment |
+-------------+------------+------------+--------------+-------------+-----------+-------------+------------+---------+---------------+
| token_write | 0 | PRIMARY | 1 | id | A | 16 | BTREE | | |
| token_write | 1 | threeindex | 1 | token | A | 16 | BTREE | | |
| token_write | 1 | threeindex | 2 | uname | A | 16 | BTREE | | |
| token_write | 1 | threeindex | 3 | desc | A | 16 | BTREE | | |
+-------------+------------+------------+--------------+-------------+-----------+-------------+------------+---------+---------------+
执行》EXPLAIN select * from token_write where token='aaa' and uname='bbb';
和 》EXPLAIN select * from token_write where uname='bbb' and token='aaa';
的结果一样。
【索引无效】
EXPLAIN select * from token_write where uname='bbb';
EXPLAIN select * from token_write where `desc`='ccc';
EXPLAIN select * from token_write where uname='aaa' and `desc`='ccc';
【索引有效】
EXPLAIN select * from token_write where token='bbb';
EXPLAIN select * from token_write where token='bbb' and `desc`='ccc';
EXPLAIN select * from token_write where token='bbb' and uname='aaa' and `desc`='ccc';