一、MySQL索引类型
mysql里目前只支持4种索引分别是:full-text,b-tree,hash,r-tree
b-tree索引应该是mysql里最广泛的索引的了,除了archive基本所有的存储引擎都支持它.
1. full-text索引
full-text在mysql里仅有myisam支持它,而且支持full-text的字段只有char、varchar、text数据类型。
full-text主要是用来代替like "%***%"效率低下的问题
2. b-tree索引
b-tree在myisam里的形式和innodb稍有不同
在 innodb里,有两种形态:一是primary key形态,其leaf node里存放的是数据,而且不仅存放了索引键的数据,还存放了其他字段的数据。二是secondary index,其leaf node和普通的b-tree差不多,只是还存放了指向主键的信息.
而在myisam里,主键和其他的并没有太大区别。不过和innodb不太一样的地方是在myisam里,leaf node里存放的不是主键的信息,而是指向数据文件里的对应数据行的信息.
3. 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索引的效率会变低.
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.
三、几个技巧
1. 强制连接顺序: STRAIGHT_JOIN
SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
由上面的SQL语句可知,通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话,就可以通过STRAIGHT_JOIN来确定连接顺序。
2. 强制使用临时表: SQL_BUFFER_RESULT
SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
当我们查询的结果集中的数据比较多时,可以通过SQL_BUFFER_RESULT,选项强制将结果集放到临时表中,这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了),并且可以长时间地为客户端提供大记录集。
3. 分组使用临时表 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差不多,很少使用。
转载声明:本文转自http://apps.hi.baidu.com/share/detail/23985447
分享到:
相关推荐
2、存储结构正如其名,这类索引的物理文件大多就是以BTree结构来存储的,但会有不同的存储引擎在使用BTree索引时,对存储结构稍作修改 2、Hash索引的弊端
主要介绍了Mysql中的Btree与Hash索引比较,本文起讲解了B-Tree 索引特征、Hash 索引特征等内容,需要的朋友可以参考下
介绍BTree索引和Hash索引,详细介绍索引的优化策略等等 1.Btree索引和Hash索引 2.安装演示数据库 3.索引优化策略上 4.索引优化策略中 5.索引优化策略下
MySQL中有几种索引类型,可以简单说说吗? FULLTEXT :即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引...
btree 索引btree 索引btree 索引
总结了mysql优化的一些内容:例如btree索引,hash索引,聚簇索引和费举措索引,多列索引、重复索引和冗余索引等如何用,count、unicon等优化查询的方法。。。
MySQL Hash索引和B-Tree索引的区别究竟在哪里呢?相信很多人都有这样的疑问,下文对两者的区别进行了详细的分析,需要的朋友可以参考下
在mysql 中,索引可以分为两种类型 hash索引和 btree索引。 什么情况下可以用到B树索引? 1.全值匹配索引 比如: orderID=”123” 2.匹配最左前缀索引查询 比如:在userid 和 date字段上创建联合索引。 ...
3、尽量在采用MyIsam作为引擎的时候使用索引(因为MySQL以BTree存储索引),而不是InnoDB。但MyISAM不支持Transcation。 4、当你的程序和数据库结构/SQL语句已经优化到无法优化的程度,而程序瓶颈并不能顺利解决,那...
实现m阶B_树 (2)将线性表中的键值插入到B_树中(即建立索引); (3)当删除和插入数据到线性表时,同时维护其索引B_树。 (4)可根据所给键值范围,输出该范围的所有键值
而用的多,并且是mysql默认的是二叉树算法 BTREE,通过BTREE算法建立索引的字段,比如扫描20行能得到未使用BTREE前扫描了2^20行的结果,具体的实现方式后续本博客会出一个算法专题里面会有具体的分析讨论;...
Oracle-Btree索引,索引的ppt
特别需要说明的是, MySQL支持诸多存储引擎,而各种存储引擎对 索引的支持也各不相同,因此MySQL数据库支 持多种索引类型,如BTree索引,哈希索引, 全文索引等等。为了避免混乱,本文将只关注 于BTree索引,因为这...
一、 MySQL: 索引以B树格式保存 Memory存储引擎可以选择Hash或BTree索引,Hash索引只能用于=或<=>的等式比较。 1、普通索引:create index on Tablename(列的列表) alter table TableName add index (列的列表) ...
MySQL的MyISAM、InnoDB引擎默认均使用B+树索引(查询时都显示为“BTREE”),本文讨论两个问题:为什么MySQL等主流数据库选择B+树的索引结构?如何基于索引结构,理解常见的MySQL索引优化思路?索引结构的选择基于...
07.MySQL高级索引数据结构BTREE.avi 08.MySQL高级索引数据结构B+TREE.avi 09.MySQL高级索引索引分类.avi 10.MySQL高级索引索引语法.avi 11.MySQL高级索引索引设计原则.avi 12.MySQL高级视图概述.avi 13.MySQL高级...
主要介绍了浅谈MySQL的B树索引与索引优化小结,小编觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小编过来看看吧
索引作用Btree 数据结构索引利弊索引的利索引的弊索引分类主键索引单列索引唯一索引复合索引Navicat for MySQL 操作索引MySQL 创建索引、删除索引、修改索引、查看索引SQL 执行计划查询优化器影响expain 关键字id 列...
MySQL 最佳优化实践手册内容:例如btree索引,hash索引,聚簇索引和费举措索引,多列索引、重复索引和冗余索引等如何用,count、unicon等优化查询的方法