一:mysql里目前只支持4种索引分别是:b-tree,full-text,hash以及r-tree索引
b-tree索引应该是mysql里最广泛的索引的了,除了archive,基本所有的存储引擎都支持它.
1.b-tree在myisam里的形式和innodb稍有不同
在 innodb里面有两种形态:其一是primary key形态其leaf node里存放的是数据.而且不仅存放了索引键的数据,还存放了其他字段的数据.其二是secondary index,其leaf node和普通的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差不多,很少使用。
原文:http://blog.csdn.net/xiao7ng/archive/2009/09/24/4591396.aspx
相关推荐
MySQL索引类型大汇总 mysql里需要用到了
MySQL索引类型大汇总.pdf
MySQL索引类型大汇总[文].pdf
对 myslq 基本索引类型的概述,以及在不同中情况下的运用。
本文实例讲述了Mysql索引类型与基本用法。分享给大家供大家参考,具体如下: 索引 MySQL目前主要有以下几种索引类型: 普通索引 唯一索引 主键索引 组合索引 全文索引 – 普通索引 是最基本的索引,它没有任何...
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。这篇文章主要介绍了详解mysql索引总结----mysql索引类型以及创建,有兴趣的可以了解一下。
今天小编就为大家分享一篇关于MySQL索引类型Normal、Unique和Full Text的讲解,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧
索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型
MySQL Innodb 索引原理详解
关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能...
讲座的一个ppt分享 1. MySQL的索引类型选择 2. MySQL数据结构分析 3. MySQL的引擎类型分析
│ │ 3_MySQL索引类型介绍.mp4 │ │ 4_MySQL索引底层结构和执行计划.mp4 │ │ 5_MySQL索引优化原则.mp4 │ │ 6_MySQL运维常见错误part1.mp4 │ │ 7_MySQL运维常见错误part2.mp4 │ │ ERROR1040_1917970.1.pdf ...
mysql数据库索引类型
在MySQL中,存储引擎也是用了类似的方法,先在索引中找到对应的值,然后再根据匹配的索引值找到对应表中记录的位置。 面试中为什么问索引? 之所以在索引在面试中经常被问到,就是因为:索引是数据库的良好性能表现...
MYSQL数据库四种索引类型 主键索引: 主键是一种唯一性索引,但它必须指定为PRIMARY KEY,每个表只能有一个主键。 唯一索引: 索引列的所有值都只能出现一次,即必须唯一,值可以为空。 普通索引 : 基本的...
下面介绍几种常见的MySQL索引类型。 在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表: CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); ...
MySQL目前主要有以下几种索引类型:普通索引、唯一索引、主键索引、组合索引、全文索引。