关闭

MySql(21)------mysql索引使用

标签: 索引HASHBTREE索引设计原则性能优化
449人阅读 评论(0) 收藏 举报
分类:

一 概要

索引是数据库中用于提高性能最常用的工具,所有的mysql列类型都可以使用索引,每个表的最大索引数

和索引长度根据表的存储引擎而定,一般情况下每个表至少支持16个索引,索引总长度至少为256个字节,

有些存储引擎的索引限制会更高些。

常见存储引擎支持的索引:

(1)对于MyISAM和InnoDB存储引擎表的默认创建索引为BTREE索引。同时也支持全文索引,该索引

用于全文搜索,目前全文索引只能用于CHAR, VARCHAR以及TEXT类型列,索引针对整个列进行,不支持局部索引。

(2)InnoDB存储引擎默认也是BTREE索引,同时也支持HASH索引的方式。

(3)MEMORY存储引擎默认使用HASH索引,同时也支持BTREE索引方式。

******创建语法:

索引的创建可以随表建立时创建,也可以在表创建完后添加。

创建新索引:

CREATE [UNIQUE\FULLTEXT\SPATIAL\NORMAL] INDEX index_name [USING index_type] ON table_name(index_col_name,...)

其中index_col_name部分的内容:

col_name[(length)][ASC][DESC]

eg:

CREATE UNIQUE INDEX userName_index USING BTREE ON t_user_main (f_userName);

其中如语法所诉,USING BTREE表使用BTREE索引方式,如果不写,就使用表的存储引擎

默认的索引方式,如果不指定UNIQUE,就使用默认NORMAL索引类型。

注意的是[UNIQUE\FULLTEXT\SPATIAL\NORMAL]表示的是索引类型,

[USING index_type]表示的是索引方式,也就是检索方式。

增加索引时使用ALTER TABLE语法增加索引。

******删除索引:

DROP INDEX index_name ON table_name;

eg:

DROP INDEX userName_index ON t_user_main;

二 索引设计原则

索引的创建可以遵循一些千千万万革命同志实践总结出来的原则,对于创建索引提高效率会起到事半功倍的效果。

如果自己一味的去瞎创建索引,可能适得其反,不合理的使用索引对于提高效率起到的只是相反的作用。

设计原则,简单列举几个:

(1) 搜索的索引列,并不一定是选择的列。也就是说,最恰当的索引列应该是在WHERE子句中,

或者是连接子句中连接的列,而不是SELECT后面指定的查询列。简单来说,搜索的索引列是用来

作为查询条件或关联条件的,方便查询时能对条件或关联列进行特殊处理,从而提高查询效率。

(2)使用唯一索引。唯一的含义就是这个作为索引的列存的数据尽可能的不同,存的数据是唯一的,

比如存身份证号码的列上创建一个唯一索引,效果会很好,而对于存性别的列上创建一个唯一索引,

就没有啥效果,因为每次通过男或女去查询,都能筛选出一半数据。

(3)使用短索引。一些列中存放的数据很长,但是可以设计数据的前10个或20个是唯一的,这样创建索引

时可以正对该列的前10个或20个字符进行短索引创建,不用全局匹配,能够提高效率。

(4)尽可能不要过度的创建索引。物极必反,合理使用。

三 BTREE索引和HASH索引

MEMORY引擎表创建的默认索引是HASH索引,但是也可以使用BTREE索引,这两种索引有一定的使用范围。

HASH索引的使用范围:

(1)只用于=或<=>比较等式

(2)优化器不能使用HASH索引加速ORDER BY 操作,相生相克

(3)MySql不能确定在两个值之间有多少行,如果强行将MyISAM引擎表改为HASH索引的MEMORY引擎表,

效率会受到影响。

(4)只能使用与整个关键字搜索一行


BTREE索引的使用范围:

(1)>,<,>=,<=,BETWEEN,!=或<>可以使用BTREE索引

(2)like 'pattern',其中pattern不以通配符开头时,比如like 'kkkkk%'时可以使用BTREE索引,而like '%kkkkk'不能使用BTREE索引。

注意: SQL编写时按照使用原则,让自己创建的索引使用上,否则白创建了

0
0
查看评论

MySQL 如何使用索引 较为详细的分析和例子

在数据库表中,使用索引可以大大提高查询速度。  假如我们创建了一个 testIndex 表:   CREATE TABLE testIndex(i_testID INT NOT NULL,vc_Name VARCHAR(16) NOTNULL);   我们随机向里面插入了 1000 条记录,...
  • hjm4702192
  • hjm4702192
  • 2014-04-23 14:09
  • 3454

MySQL索引使用方法及实例学

索引用于快速找到特定一些值的记录。如果没有索引,MySQL就必须从第一行记录开始读取整个表来检索记录。表越大,资源消耗越大。如果在字段上有索引的话,MySQL就能很快决定该从数据文件的哪个位置开始搜索记录,而无须查找所有的数据。如果表中有1000条记录的话,那么这至少比顺序地读取数据快100倍。注意...
  • hi_jess
  • hi_jess
  • 2009-10-08 11:30
  • 2675

(二)mysql索引操作

索引   当我们对一个字段经常查询,而修改很少的时候 ,数据量又很大的情况下,我们可以对该字段建立索引。所谓的索引就像我们在word为一篇文章建立目录,我们可以根据word的页码和目录尽快的查询出我们想要的内容。在mysql中,索引的意义也是如此,mysql有如下几种索引累心。 一、普...
  • lovesummerforever
  • lovesummerforever
  • 2016-02-28 16:12
  • 866

mysql建索引的几大原则

1.选择唯一性索引唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话,可能存在同名现象,从而降低查询速度。2.为经常需要排序、分组和联合操作的字段建立索引经常需要ORDER BY、G...
  • u013412790
  • u013412790
  • 2016-06-08 12:38
  • 10926

Mysql常见四种索引的使用

提到mysql优化,索引优化是必不可少的。其中一种优化方式 ——索引优化,添加合适的索引能够让项目的并发能力和抗压能力得到明显的提升。 我们知道项目性能的瓶颈主要是在"查(select)"语句,要提升"查"这一性能,mysql索引是必不可少的。接下来总结一下m...
  • u013927110
  • u013927110
  • 2015-06-25 15:12
  • 6418

mysql之正确使用索引

创建索引的几种方式Mysql目前主要有以下几种索引方式:FULLTEXT,HASH,BTREE,RTREE。 那么,这几种索引有什么功能和性能上的不同呢?详情点击下面链接: http://blog.sina.com.cn/s/blog_4aca42510102v5l2.html常用索引的类型**...
  • jiang13479
  • jiang13479
  • 2017-01-16 14:34
  • 647

MySQL索引类型总结和使用技巧以及注意事项

在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) ...
  • shixiaoguo90
  • shixiaoguo90
  • 2014-08-10 16:44
  • 1102

Mysql索引的设计和使用

(一)索引的概述     1)什么是索引:索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。 如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的 所有记录,直至找到符合要求的 记录。表里面的记录数量越多,这个操作的代价就越高。如果作为...
  • whucaodi
  • whucaodi
  • 2016-08-18 14:40
  • 1623

JAVA面试题06-Mysql索引

1.什么场景下适合创建索引,什么场景下不适合创建索引。? 1.1 where,order by,group by中频繁出现,且数据分布比较离散的列适合创建索引。比如有个用户表,用户名,手机号经常会作为查询条件,且不同用户用户名,手机号都不同(数据较离散),因此适合创建索引。但是用户的性别,虽然也会...
  • fclxyz
  • fclxyz
  • 2017-03-19 22:36
  • 784

Mysql索引扫描排序

使用索引扫描来做排序生成有序结果Mysql有两种操作可以用来生成有序结果: + 排序操作: 将查找出来的结果使用排序算法进行排序 + 按索引顺序扫描: ORDER BY语句后跟着一个被索引的列,如此一来索引的顺序就是索引对应记录的顺序,这样直接顺着索引一直往下读取记录即可得到有序的结果。建立用于...
  • u013298300
  • u013298300
  • 2016-09-02 17:30
  • 502
    个人资料
    • 访问:464096次
    • 积分:8234
    • 等级:
    • 排名:第3003名
    • 原创:398篇
    • 转载:1篇
    • 译文:2篇
    • 评论:25条
    最新评论