mysql优化--索引
在谈论索引优化之前,要先了解一下他的定义以及作用。
索引是一种方便数据库查询的排好顺序的一种数据结构,其相当于图书目录对于整本书,起到的方便查询的作用。
1.从索引的结构说起
因为查询过程是在内存中完成的,而数据存储都是在磁盘上,每一次读取节点就相当于做一次I/O即访问一次硬盘,由于硬盘读取速率的限制,每次的I/O操作会消耗大量的时间,因此查询效率的一个重要指标就是尽量少的I/O操作。
几种可供选择的数据结构:
二叉树
红黑树(平衡二叉树)
B树
B+树
hash
二叉树作为索引结构会导致层数过多,查询次数太多,查询效率极低。
红黑树是二叉树的改进,可以自动平衡数据,使层数有所下降,然而效率还是很低。
作为平衡二叉树引申,B树可以在每个节点存储更多的key值,将数据分为了key+1个区间,每个区间都对应一个子节点。因而具有良好的查询性能,具体结构如下:
B+树和B树的主要区别有三点:
1.在B+树中,key 的副本存储在内部节点,真正的 key 和 data 存储在叶子节点上 。
n 个 key 值的节点指针域为 n 而不是 n+1。
添加了叶子节点间的指针,大大增加区间访问性,可使用在范围查询,而B树每个节点 key 和 data 在一起,则无法区间查找。
其主要结构如下:
B+树一般为每个节点分配4k/8k/16k的大小,key值一般为bigint类型,占用8B,每个节点指针占用6B,这样每个节点最多可存储1170+的key值,最后一层的叶子结点的数据大小假设为1k,那么最后一层的数据量最少是16个。对于3层的B树,可查询的数据量就在16x1170x1170=21,902,400个。这就意味着对于两千万甚至更大的数量级数据的查找仅仅需要3次I/O就可以完成。
实际上mysql的大部分引擎如Myisam和Innodb就采用的B+树的数据结构。
哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。至于为什么不采用这种结构,就在于它无法实现逻辑性条件的查找,而且哈希索引也没办法利用索引完成排序,以及like ‘xxx%’ 这样的部分模糊查询。在MySQL中,只有HEAP/MEMORY引擎表才能显式支持哈希索引(NDB也支持,但这个不常用),InnoDB引擎的自适应哈希索引(adaptive hash index)不在此列,因为这不是创建索引时可指定的。还需要注意到:HEAP/MEMORY引擎表在mysql实例重启后,数据会丢失。
2.mysql的索引类型
mysql常用的索引类型有如下几种:
1)普通索引index :加速查找
2)唯一索引
主键索引:primary key :加速查找+约束(不为空且唯一)
唯一索引:unique:加速查找+约束 (唯一)
3)联合索引
-primary key(id,name):联合主键索引
-unique(id,name):联合唯一索引
-index(id,name):联合普通索引
4)全文索引fulltext :用于搜索很长一篇文章的时候,效果最好。
普通索引:这是最基本的索引类型,它没有唯一性之类的限制,可依据情况适当添加。
主键索引:主键是一种唯一性索引,每个表只能有一个主键,在单表查询中,PRIMARY主键索引与UNIQUE唯一索引的检索效率并没有多大的区别,但在关联查询中,PRIMARY主键索引的检索速度要高于UNIQUE唯一索引。
唯一索引:这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。
联合索引:联合索引的使用一般依据于具体的业务逻辑,可以将多个字段创建成其他类型的索引,比如确定一个老师的信息就可以把班级和教授科目联系在一起,组合成联合唯一索引,保证逻辑正确。
全文索引:MySql从3.23版开始支持全文索引和全文检索。全文索引只可以在VARCHAR或者TEXT类型的列上创建,且全文索引的关键字不支持
中文字符,如果需要添加中文索引就必须借助于插件。
3.mysql索引优化手段
在介绍索引优化方法之前先介绍一个explain方法,此方法返回mysql查询语句的预处理内容。语法为
explain 你的查询语句
。
具体优化内容很多,在这推荐一篇博客:
MySQL 性能优化,索引和查询优化