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

原创 2016年08月31日 09:29:15

一 概要

索引是数据库中用于提高性能最常用的工具,所有的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编写时按照使用原则,让自己创建的索引使用上,否则白创建了

版权声明:本文为博主原创文章,未经博主允许不得转载。

【Day21】关于mysql数据库索引所涉及的一些知识(新)

我们首先了解,数据库的索引是什么?数据库的索引:数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。(什么是B树和B+树,见文章...

mysql索引的设计和使用

  • 2012年03月26日 17:24
  • 541KB
  • 下载

MySQL查询索引的正确使用

索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用,但是一般来说引起最大性能差异的都是索引的正确使用。在MySQL邮件列表中,人们经常询问那些让查询运行得更快的方法。在大多数情况下,我们...
  • pcyph
  • pcyph
  • 2015年05月17日 14:32
  • 23122

MySQL索引缺点&使用详细注意事项

本文转自:点击打开链接 以下主要介绍的是MySQL索引的缺点以及MySQL索引在实际操作中有哪些事项是值得我们大家注意的,我们大家可能不知道过多的对索引进行使用将会造成滥用,有兴趣的...

mysql数据库索引的创建及使用

1.mysql索引类型及创建 常用的索引类型有 (1)主键索引 它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引: 复制代码 代码如下: CREATE TABLE user...

mysql使用索引为什么查询速度变快很多?

首先来看看表是否有索引的命令 show index from 表名; 看到主键索引,索引类型是BTREE(二叉树) 正是因为这个二叉树算法,让查询速度快很多,二叉树的原理,就是取最中间的...
  • jaryle
  • jaryle
  • 2016年07月25日 14:39
  • 3076

MySQL查询不使用索引汇总

众所周知,增加索引是提高查询速度的有效途径,但是很多时候,即使增加了索引,查询仍然不使用索引,这种情况严重影响性能,这里就简单总结几条MySQL不使用索引的情况 如果MySQL估计使用索引比全表...

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

在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,  ...

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

在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表: 复制代码代码如下: CREATE TABLE mytable(   ID INT NOT ...

mysql select中子查询中使用强制索引的优化案例

1,朋友找我帮忙看下比较慢的sql语句SELECT pg.product_goods_id, pg.product_id, pg.pdt_code, pg.pdt_na...
  • mchdba
  • mchdba
  • 2015年11月05日 20:18
  • 6563
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySql(21)------mysql索引使用
举报原因:
原因补充:

(最多只允许输入30个字)