mysql 索引优化 btree hash rtree

标签: mysql 索引 优化 | 发表时间:2014-11-25 12:21 | 作者:saintsaya
出处:http://www.iteye.com
mysql里目前只支持4种索引分别是:b-tree,full-text,hash以及r-tree索引

b-tree索引应该是mysql里最广泛的索引的了,除了archive,基本所有的存储引擎都支持它.

1.b-tree在myisam里的形式和innodb稍有不同

在innodb里面有两种形态:其一是primary key形态其leafnode里存放的是数据.而且不仅存放了索引键的数据,还存放了其他字段的数据.其二是secondary index,其leafnode和普通的b-tree差不多,只是还存放了指向主键的信息.

而在myisam里,主键和其他的并没有太大区别.不过和innodb不太一样的地方是,在myisam里,leaf node里存放的不是主键的信息,存的是指向数据文件里的对应数据行的信息.

2.hash索引,目前我所知道的就只有memory和ndb cluster支持这种索引.

hash索引由于其结构,所以在每次查询的时候直接一次到位不行b-tree那样,一点点的前进,所以hash索引的效率高于b-tree.但hash也有缺点,主要如下:

(1)由于存放的是hash值,所以仅支持<=>以及in操作.

(2)hash索引无法通过操作索引来排序,这是因为存放的时候经过hash计算,但是计算的hash值和存放的不一定相等,所以无法排序.

(3)在组合所以里,无法对部分使用索引.

(4)不能避免全表扫描,只是由于在memory表里支持非唯一值hash索引,就是不同的索引键,可能存在相同的hash值.

(5)当存在大量相同hash值得时候,hash索引的效率会变低.

3.full-text索引

full-text在mysql里仅有myisam支持它,而且支持full-text的字段只有char,varchar,以及text数据类型,full-text主要是用来代替like "%***%"效率低下的问题的.

4.r-tree索引

r-tree在mysql很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有myisam,bdb,innodb,ndb,archive几种.相对于b-tree,r-tree的优势在于范围查找.



mysql里sql语句值得注意的地方

1.myisam里所以键的长度仅支持1000字节,innodb是767.

2.blob和text字段仅支持前缀索引.

3.使用!=以及<>不等于的时候mysql不使用索引.

4.当在字段使用函数的时候,mysql无法使用索引,在join的时候条件字段类型不一致的时候,mysql无法使用索引,在组合索引里使用非第一个索引时也不使用索引.

5.在使用like的时候,以%开头,即"%***"的时候无法使用索引,在使用or的时候,要求or前后字段都有索引.


有时候mysql query optimizer会认为使用索引并不是最优计划,所以不使用索引,可以在sql语句里可以用use,force index,当然有时候使用也不会比不用快,所以需要忽略掉index方法是ignore index.

关闭查询缓存sql_no_cache

select sql_no_cache * from table_name;

这样可以让一些很少使用的语句不放在缓存里,查找的时候不会去缓存里找.对应的是强制缓存sql_cache

select sql_cache * from table_name;

另外在my.cnf中如果设置query_cache_type=2的话,那么只有在使用sql_cache后才会使用缓存;

还有mysql里的优先操作hight_priority让mysql优先操作这个语句

select high_priority * fromtable_name;

与其对应的是low_priority;

mysql里还有延时插入insert delayed

insert delayed into table_name....;#当提交之后,mysql返回ok,但不立即插入,二十当mysql有空再插入.假如等待时服务器崩溃,那么所有数据丢失!并且插入不会返回自增id.

再转几个技巧:

强制连接顺序 STRAIGHT_JOIN

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

由上面的SQL语句可知,通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话,就可以通过STRAIGHT_JOIN来确定连接顺序。

强制使用临时表 SQL_BUFFER_RESULT

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

当我们查询的结果集中的数据比较多时,可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中,这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了),并且可以长时间地为客户端提供大记录集。

分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;

一般用于分组或DISTINCT关键字,这个选项通知MySQL,如果有必要,就将查询结果放到临时表中,甚至在临时表中进行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多,很少使用。

已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [mysql 索引 优化] 推荐:

MySQL B+树索引及索引优化

- - 数据库 - ITeye博客
    MySQL的索引实现由很多种实现,包括hash索引,B+索引,全文索引等,本文只讨论B+树索引. 1.评价一个索引好坏主要看IO的访问次数,B+树红黑树来说,树高很小(出度很大)即可以有效降低IO的访问次数. B+数的高度h=logd(n),d越大,h越小,查询效率越高. 相对B树,B+树d可以很大,因为非叶子节点不存储数据,只存储key,在一个存储页上可以存储更多的key值.

mysql 索引优化 btree hash rtree

- - 数据库 - ITeye博客
mysql里目前只支持4种索引分别是:b-tree,full-text,hash以及r-tree索引. b-tree索引应该是mysql里最广泛的索引的了,除了archive,基本所有的存储引擎都支持它. 1.b-tree在myisam里的形式和innodb稍有不同. 在innodb里面有两种形态:其一是primary key形态其leafnode里存放的是数据.而且不仅存放了索引键的数据,还存放了其他字段的数据.其二是secondary index,其leafnode和普通的b-tree差不多,只是还存放了指向主键的信息.

MySQL学习(索引、引擎、优化)

- - 数据库 - ITeye博客
索引对于查询的速度至关重要,理解索引也是数据库调优的起点. 建立索引前,先设计好建立索引列的数据类型. 1)越小的数据类型性能越好:因为越小的数据类型对于硬盘读取、内存、CPU缓存都需要更少的空间,处理起来更快. 2)简单的数据类型更好:整型比字符型更好. 3)尽量避免使用NULL: 建立索引的列最好是Not Null约束的,如果一定要用NULL,可以用0或者某特殊值替代.

【mysql的设计与优化专题】mysql的最佳索引攻略 - 菜问

- - 博客园_首页
所谓索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找,而用的最多,并且是mysql默认的就是二叉树算法 BTREE,通过BTREE算法建立索引的字段,比如扫描20行就能得到未使用BTREE前扫描了2^20行的结果,具体的实现方式后续本博客会出一个算法专题里面会有具体的分析讨论;.

MYSQL 架构优化和索引之列设计篇

- - 博客园_首页
情况:如果你的表结构设计不良或你的索引设计不佳,那么请你优化你的表结构设计和给予合适的索引,这样你的查询性能就能提高几个数量级. ——数据越大,索引的价值越能体现出来. 我们要提高性能,需要考虑的因素:. 今天要讲的是表列的设计,暂不谈索引设计. 以下是数据储备脚本:主要是做表的建立和数据的插入——你也可以视情况修改表结构.

Mysql数据库索引查询优化的分享

- - 膘叔
这是昨天SAE分享的一篇文章,开始的时候,我看了一遍,发现好象没有什么特别的内容,但再仔细看的时候,发现居然可以这样做.       我们要访问的表是一个非常大的表,四千万条记录,id是主键,program_id上建了索引.       执行一条SQL:.       这条SQL非常慢. 我们原以为处理记录太多的原因,所以加了id限制,一次只读五十万条记录.

【转载】MySQL索引原理及慢查询优化

- - 数据库 - ITeye博客
MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库. 虽然性能出色,但所谓“好马配好鞍”,如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求. 我们知道一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然是重中之重.

mysql负载,查询优化,索引和锁等

- - 开源软件 - ITeye博客
mysql引擎有多种,每种实现的索引方式也不尽相同:. 有hash索引,b树索引,b+树索引. 我这边接触最大的是innodb引擎:. 1:InnoDB的主键索引(Primary Key)是Cluster形式的(聚簇索引). :   按主键值生产b+树,叶子节点放的列的数据.  2:InnoDB的非主键索引(Secondary Index)是普通的B-Tree索引.

mysql性能优化-慢查询分析、优化索引和配置

- - 数据库 - ITeye博客
profiling分析查询. MySQL 数据库是常见的两个瓶颈是CPU和I/O的瓶颈,CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候. 磁盘I/O瓶颈发生在装入数据远大于 内存容量的时候,如果应用分布在网络上,那么查询量相当大的时候那么平瓶颈就会出现在网络上,我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态.

mysql优化

- - 数据库 - ITeye博客
公司网站访问量越来越大,MySQL自然成为瓶颈,因此最近我一直在研究 MySQL  的优化,第一步自然想到的是 MySQL 系统参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL  默认的系统参数能够让 MySQL运行得非常顺畅. 在Apache, PHP,  MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.