mysql索引的理解_理解mysql索引

简介

mysql主要有以下几种索引普通索引

唯一索引

主键索引

组合索引

全文索引

语句

create table tablename[colname data type][unique|fulltext][index|key][index_name](col_name[length])[asc|desc]unique/fulltext为可选参数,分别表示唯一索引,全文索引

index和key为同义词,两者作用相同,用来指定创建索引

col_name为需要创建索引的字段列,该列必须 从数据表中该定义的多个列中选择

indexname指定索引的名称,为可选参数,如果不指定,默认colname为索引值

length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度

asc或desc指定升序或降序的索引值存储

索引类型

普通索引是最基本的索引,他没有任何限制,他有以下几种创建方式直接创建索引

create index index_name on table(column(length))修改表结构的方式添加索引

alter table tablename add index indexname(column(length))创建表的时候同时创建索引

create table tablename(id int(11) not null auto_increment,title char(255) not null,primary key (id),index index_name (title(length)))删除索引

drop index index_name on tablename

唯一索引与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值.如果是组合索引,则列值的组合必须唯一.他有以下几种创建方式创建唯一索引

create unique index indexname on table(column(length))修改表结构

alter table table_name add unique indexname(column(length))创建表的时候直接指定

create teble tablename (id int(11) not null,unique indexname(id(length)))

主键索引是一种特殊的唯一索引,一个表只能有一个逐渐,不允许有空,一般是在建表的时候创建主键索引

create table tablename (id int(11) not null,auto_increment,primary key (id))

组合索引只多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用,使用组合索引时遵循最左前缀原则

alter table add index indexname(name, city,age)

全文索引

只要用来查找文本中的关键字,而不是直接与索引中的值比较,fulltext索引很其他索引大不相同,他像一个搜索引擎,而不是简单的where语句的参数匹配,fulltext索引配合match,against操作使用,而不是一般的where加like.

他可以在create table,alter table,create index使用,不过目前只有char,varchar,text列上创建全文索引.在数据量较大的时候,先将数据放入一个没有全局索引的表中,然后再用create index创建fulltext索引,要比先为一张建立fulltext索引再将数据写入的速度快很多创建表时添加全文索引

create table tablename (id int(11) not null auto_increment,title char(255) not null,primary key (id),fulltext (title))修改表结构添加全文索引

alter table tablename add fulltext indexname(column)直接创建索引

create fulltext index index_name on tablename(column)

缺点索引虽然大大提高了查询速度,同时却会降低更新表的速度

建立索引会占用磁盘的空间.

注意索引不会包含有null值得列,只要列中包含有null值都将不会被包含在索引中,符合索引中只要有一列含有null值,那么这一列对于复合索引无效.所以设置默认值,不为null

使用短索引,比如一个char(255)得列,如果前10-20个字符,基本都是不重复的,就不要对整列进行索引.可以提高查询速度,节省磁盘空间和io操作

索引列排序查询只是用一个索引,如果where子句中已经使用了索引,order by得列不会使用索引.数据库默认排序可以符合要求的情况下,不要使用排序操作,尽量不要包含多个列的排序,如果需要最好创建复合索引

like语句一般不推荐,如果非用,like “a” 不会使用索引,而like “aaa%”可以使用索引

不要在列上进行运算会导致索引失效而全盘扫描.

索引本质

查询速度希望尽可能的快.

最基本的算法是顺序查找,但是复杂度为O(n),可以用二分查找和二叉树查找等. 但是每种算法都需要特定的数据结构,比如二分查找必须有序,二叉树查找必须是二叉树,索引就在这些数据结构基础上实现高级的查找算法.

索引也是一种数据结构

b-tree

定义一条数据记录为一个二元组[key,data],key为记录的键值,对于不同的数据记录,key值不同,data为key以外的数据.

b-tree的数据结构:度:d为大于1的正整数

高度:h为正整数.

每个非叶子节点由n-1个key和n个指针组成

每个叶子节点最少包含一个key和两个指针,最多包含2d-1个key和2d个指针,叶节点的指针均为null.

所有叶子节点深度相同.为h

key和指针互相间隔,节点两端是指针

一个节点的key从左到右非递减排列

b-tree检索数据的算法:首先从根节点进行二分查找,如果找到则返回对应节点的data,否则对相应区间的指针指向节点递归进行查找,直到找到节点或者到null指针.

伪代码:

BTree_search(node,key){

if(node==null) return null;

foreach(node,key){

if(node.key[i]==key) return node.data[i];

if(node.key[i]>key) return BTree_search(point[i]->node);

}

return BTree_search(point[i+1]->node);

}

data=BTree_search(root,mykey);

b+tree

是b-tree的变种,mysql就普遍使用b+tree实现索引结构

不同点:每个节点的指针上限为2d,不是2d+1.

内节点不存储data,只存储key,叶子节点不存储指针.

在叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的b+tree,这个优化的目的是为了提高区间访问的性能,当访问一个叶子节点时,只要顺着节点和指针顺序遍历,就可以一次性访问所有节点,提高了区间的查询效率

为什么用b+tree而不用红黑树

红黑树也是可以实现的,这就跟计算机组成原理有关.

myisam索引

同样也是b+tree,data保存数据记录的地址,因此,myisam中索引的算法为首先按照b+tree搜索算法搜索引擎,如果指定的key存在,则取出data域的值.

innodb索引

区别就是myisam索引文件和数据文件是分离的,索引文件保存数据记录的地址

在innodb中,表数据文件本身就是按b+tree组织的一个索引结构,这个树的叶子节点data域保存了完整的数据记录,这个索引的key就是数据表的主键,因此innodb表数据文件本身就是主索引.

叶节点包含完整的数据记录,这种索引叫聚集索引,因为innodb的数据文件本身按主键聚集,所以innodb必须有主键,myisam可以没有,如果没有显式指定,mysql系统会自动选择一个可以唯一标识的列作为主键,如果没有这样列,就会生成一个隐含字段作为主键,长度6字节的长整形.

innodb的辅助索引data域存储相应记录主键的值而不是地址.所有辅助索引都引用主键作为data域.

以英文的ASCII作为比较准则,是对主键的搜索十分高效,但辅助索引搜索需要检索两遍索引,首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录.

因为所有的辅助索引都引用主索引,过长的主索引会令辅助索引变得过大.用非单调的字段作为主键会造成插入新纪录时数据文件为了维持b+tree的特性而频繁分裂调整,十分低效,而使用自增字段作为主键是一个很好地选择.

索引使用策略和优化

mysql的优化主要分为结构优化和查询优化.

最左前缀原理和相关优化

mysql中的索引可以以一定的顺序引用多个列,这种索引叫做联合索引,一般的,一个联合索引是一个有序元组,其中各个元素均为数据表的一列,实际上要严格定义索引需要用到关系代数

一个联合索引(NO,title,data)全列匹配

当按照索引中的所有列进行精确匹配时,索引可以被列引用.mysql查询优化器会自动调整where子句的条件顺序已使用合适的索引.最左前缀匹配

当查询条件精确匹配索引的左边连续一个或几个列时,比如where NO=’‘,或where no=’’ and title=’‘都可以用到中间列的某个条件未提供

where no=’’ and data=’’

因为中间列title未提供,所以查询只用到了索引的第一列,后面的data虽然在索引中,但是由于title不存在而无法和左前缀连接,因此需要对结果进行扫描过滤data.如果想让data也使用索引,可以增加一个辅助索引no,data联合索引.诗词上面的查询会使用这个索引.开头的索引未使用

索引未被使用模糊查询某列的前缀字符串

where no=’’ and title like ‘s%’

可以使用索引,如果通配符在开头就不能使用索引

范围查询

where no

查询条件中含有函数或者表达式

where no-1=’’ 应该改成where no=’‘+1

索引选择性和前缀索引

表记录比较少,比如一两千条几百条的表,没必要建立索引.

索引的选择性selectivity,是指不重复的索引值(基数cardinality)与表记录数的比

selectivity = cardinality / #T

选择性的取值范围为(0,1],选择性越高索引价值越大.

前缀索引:用列的前缀代替整个列作为索引key.

innodb主键选择和插入优化

如果没有特别需要,使用一个与业务无关的自增字段作为主键.

每当一条新记录的插入,mysql会根据主键将其插入合适的节点位置,如果达到装载因子15/16,则开辟一个新的页.

如果使用自增主键,就会顺序的添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页.就会形成一个紧凑的索引结构,每次插入也不需要移动已有数据,效率高.