索引的几种类型:
1.普通索引
2.唯一索引
3.主键索引
4.组合索引
5.全文索引
普通索引:
最简单的索引,它没有任何的限制
create index 索引名 on table(列名)
唯一索引:
与普通索引类型,它要求索引列不能有重复值,允许有空值,当唯一索引是组合索引,组合值需唯一。
create unique 索引名 on table(列名)
主键索引:
是一种特殊的唯一索引,在主键上建立,其他的索引都是基于主键索引,mysql默认在创建表时建立主键索引。
组合索引:
由多列组合起来建立的索引,遵循最左前缀规则,将选择性最高的列放到索引最前列
create index 索引名 on table(列名,列名,列名)
查看索引使用的命令:
explain select * from student where code= 1000;
EXPLAIN的解释
table:来源哪一张表
type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
是较为重要的一个指标,结果值从好到坏依次是:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref。
possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key: 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE
INDEX(indexname)来强制MYSQL忽略索引
key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
rows:MYSQL认为必须检查的用来返回请求数据的行数
Extra:关于MYSQL如何解析查询的额外信息,但这里可以看到的坏的例子是Using
temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢
索引的创建原则几点
(1) 基于优化器对SQL语句的优化处理,我们在创建索引时可以遵循下面的一般性原则:
(2) 为经常用作查询选择的字段,建立索引。
(3) 在经常用作表连接的属性上,建立索引。
(4) 限制表上的索引数目。
对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。
(5) 不要在有大量相同取值的字段上,建立索引。
在这样的字段(例如:性别)上建立索引,字段作为选择条件时将返回大量满足条件的记录,优化器不会使用该索引作为访问路径。
(6) 短值上建立索引
不要在varchar(255)这种字段建立,索引
(7) 建立组合索引时,字段值差异很大的列,放在最前面
(8) 删除不再使用,或者很少被使用的索引。
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。
MYSql的InodB引擎,索引结构:
他的索引使用的B+树,B+树是一种矮胖树,只有叶子节点存在data,其他节点只包括key,另外叶子之间使用链表,所以方便区间查询。
InnoDB中存在两种索引结构,一种是聚合索引,一种是非聚合索引。差异在于,聚合索引key为主键。非聚合索引key为索引值,data为聚合索引的key。