MySQL数据库索引

目录：
1、MyISAM与InnoDB的区别：
2、索引的优缺点：
3、如何选用索引：
4、查看索引：
5、哈希索引：
6、B+树：
7、索引分类：

1、MyISAM与InnoDB的区别：
（1）show engines;

图1.1 截图1
A）MySQL数据表主要支持如图所示的存储引擎，分为“事务安全型”和“非事务安全型”，其中MyISAM属于非事务安全型，InnoDB属于事务安全型。
B）MyISAM的B+树的叶子结点上的data，并不是数据本身，而是数据存放的地址。主索引和二级索引区别不大，只是主索引中的key一定是唯一的。这里的索引都是非聚集索引。
MyISAM采用压缩机制存储索引。
C）InnoDB的数据文件本身就是索引文件，B+树上的叶子结点上的data就是数据本身，key为主键，这是聚集索引。
（2）show table status;
MySQL默认创建的数据表就是InnoDB。

图1.2 截图2

2、索引的优缺点：
（1）优点：加快检索速度
（2）缺点：
A）索引往往以索引文件的形式存储在磁盘上，要占据磁盘空间，因此索引在查找过程中就要产生磁盘I/O消耗，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。索引的结构组织要尽量减少在查找过程中磁盘I/O的存取次数。
B）降低更新表的速度。如对表进行insert、update和delete时，不仅要保存数据，还要保存索引文件。

3、如何选用索引：
（1）尽量为用来搜索、分类或分组的数据列编制索引，不要为作为输出显示的数据列编制索引。
where col1 = col2等where字句中使用的主键列
order by中使用的列
group by中使用的列
join on在连接操作中使用的列
p_order > 50 and p_order < 100按范围存取的列
（2）综合考虑各数据列的维度势。
数据列的维度等于它所容纳的非重复值的个数。数据列的维度越高，索引的使用效果就越好。
当查询优化程序确定出某一个数值在数据表的数据行中出现频率超过30%时， 查询优化程序通常会跳过索引，而进行全数据表扫描。
（3）对短小的值进行索引。
A）如果能使用char(20)，就不要使用char(100)，因为短小的值
①可以让比较操作更快地完成，加快索引的处理性能；
②可以让索引的“体积”更小，减少磁盘I/O活动；
③短小的键值意味着键缓存里的索引块可以容纳更多的键值，让MySQL可以在内存里同时容纳更多的键，而这将加大在不需要从磁盘读取更多索引块的前提下在内存里找到键值的概率。
B）对InnoDB存储引擎而言，因为它使用的是聚集索引，所以把主键尽量短小将更有好处。聚集索引（clustered index）：把数据行和主键值集中保存在一起的情况。其他的索引都是二级索引——它们保存着主键值和二级索引值。先在二级索引里找到一个主键值，再通过它找到相应的数据行。这意味着主键值再每一个二级索引里都会重复出现，如果主键值比较长的话，就会导致每一个二级索引都将需要占用更多的存储空间。
该索引中键值的逻辑顺序决定了表中相应行的物理顺序。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。
聚集索引优点：①数据访问更快，聚集索引将数据和引用保存在同一个B+树中，因此从聚集索引中获取数据往往比在非聚集索引中查找更快。
（4）充分利用最左边的前缀。
当创建一个n个数据列的复合索引时，实际上就创建了MySQL能够使用的n个索引。一个复合索引在工作时就相当于n个索引，因为索引中最左边的数据列集合能够用于匹配数据行。
如数据列 username，usersex，usertel作为复合索引，
则username，usersex
username
索引都能够搜索上面三种数据列组合，但是MySQL不能使用没有包含最左边前缀的搜索的索引。即按照usersex来搜索，则索引不能使用。

4、查看索引：
show index from user;查看user表中的索引

图4.1 截图3
Non unique：索引不能包括重复值，则为0
key name：索引的名称
Seq_in_index：索引中的列序列号，从1开始
Collation：列存储在索引中的方式，A表示升序，NULL表示无分类。
Cardinality：索引中唯一值的数目估计值。
Sub_part：列只是被部分地编入索引，则为被编入索引的字符的数目，如果整列被编入索引，则为NULL。
Packed：指示关键字如何被压缩，如果没有被压缩，则为NULL。
NULL：如果列包含NULL值，则为yes
IndexType：索引方法，包括BTree，FullText，Hash，RTree等等，注意BTree在MySQL文档里实际上是把B+树索引写成了BTree

5、哈希索引：
（1）哈希索引是采用一系列的哈希算法，把键值换算成新的哈希值，检索时不需要像B树一样从根节点到子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置。处理冲突的方法为链地址法。
（2）哈希索引不能用来范围检索。只能用于等值过滤。
（3）哈希索引不支持多列联合索引的最左匹配规则。
（4）哈希索引不能利用索引进行排序。因为经过Hash算法处理之后的Hash值的大小关系，并不能保证保证和Hash运算前完全一致。

6、B+树：
B+树是应文件系统所需提出的一种B树的变型。
一棵m阶B+树：
（1）有n棵子树的节点中含有n个关键字。
（2）所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针。且叶子结点本身依照关键字的大小自小而大顺序链接。
（3）所有的非终端结点可以看成索引部分，结点中仅含其子树（根结点）中的最大（或最小）关键字。
（4）在B+树，不管查找成功与否，每次查找都是走了一条从根到叶子结点的路径。
（5）树中每个结点最多含有m棵子树。

7、索引分类：
（1）普通索引：没有任何限制
（2）主键索引：特殊的唯一索引，不允许有空值
（3）唯一索引：索引列的值必须唯一，但允许有空值
（4）组合索引：列值的组合必须唯一

版权声明：感谢来访！欢迎指导、勘误、讨论及转载，转载请注明出处：blog.csdn.net/remoa_dengqinyi