文档章节

Mysql 索引

q叉烧p
 q叉烧p
发布于 2014/11/17 15:14
字数 2035
阅读 159
收藏 26

什么是索引

索引时一种特殊的文件,他们包涵着对数据表里所有记录的引用指针。

当对数据表记录进行更新后,都会对索引进行刷新。

索引会占用相当大的空间,应该只为经常查询和最经常排序的数据列建立索引。


索引类型


①普通索引:这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:

 I.创建索引

  例如:CREATE INDEX <索引的名字> ON tablename (列的列表);

 II.修改表

  例如:ALTER TABLE tablename ADDINDEX [索引的名字] (列的列表);

 III.创建表的时候指定索引

  例如:CREATE TABLE tablename ( [...],INDEX [索引的名字] (列的列表) );


②唯一性索引

  这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。

  创建方式:和普通索引创建方式一样,将“INDEX”替换成“UNIQUE”就可以了

③主键索引

  主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。每个表只能有一个主键

④全文索引

  文本字段上的普通索引只能加快对出现在字段内容最前面的字符串进行的检索操作。如果字段里存放的是由学多个单词构成的大段文字,普通索引就没什么作用“%word%”费时,效率低,响应时间长。

  生成全文索引时,mysql将把文中出现的所有单词创建一份清单,查询操作根据这份清单去检索相关数据记录。

  但目前全文索引对中文支持很差,需要相关分词插件。(如:http://code.google.com/p/mysqlcft/)


多重索引


 索引可以是单列索引,也可以是多列索引。

 创建多列索引:ALTERTABLE people ADD INDEX fname_lname_age (firstname,lastname,age);

 由于索引文件以B-树格式保存,MySQL能够立即转到合适的firstname,然后再转到合适的lastname,最后转到合适的age。MySQL只需一次检索就能够找出正确的结果!在没有扫描数据文件任何一个记录的情况下,MySQL就正确地找出了搜索的目标记录! 

 多重索引还有另外一个优点,它通过称为最左前缀(Leftmost Prefixing)的概念体现出来。

 假若现在我们有一个firstname、lastname、age列上的多重索引,我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时,MySQL将使用fname_lname_age索引:

  firstname,lastname,age

  firstname,lastname

  firstname


选择索引列


 在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列:

 

 在WHERE子句中出现的列

 在join子句中出现的列

例子:

  SELECT people.age, ##不使用索引

  town.name ##不使用索引

  FROM people LEFT JOIN town ON

  people.townid=town.townid ##考虑使用索引

  WHERE firstname='Mike'##考虑使用索引

  AND lastname='Sullivan'##考虑使用索引

  那么,我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢?

  差不多如此,但并不完全。我们还必须考虑到对列进行比较的操作符类型。MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形。


分析索引效率


EXPLAIN<SQL命令

 例子: EXPLAIN SELECT peopleid FROMpeople WHERE firstname='Mike'AND lastname='Sullivan' AND age='17';

 这个命令将返回下面这种分析结果:

 ------------------------

 Table  type  possible_keys    key              key_len  ref                rows  Extra

 people ref   fname_lname_age  fname_lname_age  102      const,const,const  1     Where used

 ------------------------


 table:这是表的名字。

 type:连接操作的类型。下面是MySQL文档关于ref连接类型的说明:

 对于每一种与另一个表中记录的组合,MySQL将从当前的表读取所有带有匹配索引值的记录。如果连接操作只使用键的最左前缀,或者如果键不是UNIQUE或PRIMARY KEY类型(换句话说,如果连接操作不能根据键值选择出唯一行),则MySQL使用ref连接类型。如果连接操作所用的键只匹配少量的记录,则ref是一种好的连接类型。”

 在本例中,由于索引不是UNIQUE类型,ref是我们能够得到的最好连接类型。

 如果EXPLAIN显示连接类型是“ALL”,而且你并不想从表里面选择出大多数记录,那么MySQL的操作效率将非常低,因为它要扫描整个表。你可以加入更多的索引来解决这个问题。预知更多信息,请参见MySQL的手册说明。 

 possible_keys:可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称;如果索引没有昵称,则默认显示的是索引中第一个列的名字(在本例中,它是“firstname”)。默认索引名字的含义往往不是很明显。

 Key: 它显示了MySQL实际使用的索引的名字。如果它为空(或NULL),则MySQL不使用索引。

 key_len: 索引中被使用部分的长度,以字节计。在本例中,key_len是102,其中firstname占50字节,lastname占50字节,age占2字节。如果MySQL只使用索引中的firstname部分,则key_len将是50。

 ref: 它显示的是列的名字(或单词“const”),MySQL将根据这些列来选择行。在本例中,MySQL根据三个常量选择行。

 rows: MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然,这里最理想的数字就是1。

 Extra: 这里可能出现许多不同的选项,其中大多数将对查询产生负面影响。在本例中,MySQL只是提醒我们它将用WHERE子句限制搜索结果集。


索引设计


 一般针对数据分散的关键字进行建立索引,比如ID、login_id,user_id,等建立索引没有意义


 尽量使用短索引,一般对int、char/varchar、date/time 等类型的字段建立索引


 需要的时候建立联合索引,但是要注意查询SQL语句的编写谨慎建立 unique 类型的索引(唯一索引)


 一般建议每条记录最好有一个能快速定位的独一无二定位的唯一标示(索引)


 不要过度索引,单表建立的索引不要超过5个,否则更新索引将很耗时


 能够快速缩小结果集的 WHERE 条件写在前面,如果有恒量条件,也尽量放在前面


 尽量避免使用 GROUP BY、DISTINCT、OR、IN等语句的使用,避免使用联表查询和子查询,因为将使执行效率大大下降能够使用索引的字段尽量进行有效的合理排列,如果使用了联合索引,请注意提取字段的前后顺序


 如果where子句的查询条件里有不等号(wherecolumns !=…),mysql将无法使用索引


 类似地,在SQL里使用了MySQL部分自带函数,索引将失效,同时将无法使用 MySQL的 QueryCache,比如 LEFT(),SUBSTR(), TO_DAYS()DATE_FORMAT(), 等,如果使用了 OR 或 IN,索引也将失效


 在join操作中,mysql只有在主键和外键的数据类型相同时才能使用索引


 针对索引字段使用 >, >=, =, <, <=, IF NULL和BETWEEN 将会使用索引,  如果对某个索引字段进行 LIKE 查询和REGEXP,mysql只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说,使用 LIKE  ‘%abc%’不能使用索引,使用 LIKE ‘abc%’ 将能够使用索引


 在orderby操作中,mysql只有在排序条件不是一个查询条件表达式的情况下才使用索引



本文转载自:http://blog.csdn.net/wud_jiyanhui/article/details/7403375

共有 人打赏支持
q叉烧p
粉丝 21
博文 76
码字总数 75416
作品 0
东莞
程序员
私信 提问
加载中

评论(1)

a
adamleung
多写一些屌屌的内容出来~~
SQL基础-DDL数据定义语言

SQL数据定义语言-DDL语句 DDL语句部分使我们有能力创建或删除表格。我们也可以定义索引(键),规定表之间的链接,以及施加表间的约束。 语句 释义 CREATE DATABASE 创建新数据库 ALTER DATA...

ZHAO_JH
08/10
0
0
MySQL 索引选择原则分析(二)

2:MySQL索引选择规则(唯一索引查询) studentsorigin表中只有主键,没有建立索引。 select id, name from studentsorigin; 根据MySQL 索引选择原则分析(一)2.1来分析,所以只能是全表扫描...

tara_qri
2015/10/14
24
0
MYSQL索引分析和优化设计方案

一、什么是索引? 索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时 MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求...

有些服务器
2015/11/02
0
0
MySQL专题9之MySQL索引、MySQL临时表、MySQL复制表

1、MySQL索引 - MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 - 打个比方,如果合理的设计并使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索...

极客微信条
2017/11/19
0
0
MySQL Index详解

①MySQL Index 一、SHOW INDEX会返回以下字段 1、Table 表的名称。 2、 Non_unique 如果索引不能包括重复词,则为0,如果可以则为1。 3、 Key_name 索引的名称 4、 Seqinindex 索引中的列序列号...

雾妄
2016/12/22
10
0

没有更多内容

加载失败,请刷新页面

加载更多

JSON数据从OSS迁移到MaxCompute最佳实践

摘要: 本文为您介绍如何利用DataWorks数据集成将JSON数据从OSS迁移到MaxCompute,并使用MaxCompute内置字符串函数GET_JSON_OBJECT提取JSON信息。 本文为您介绍如何利用DataWorks数据集成将J...

阿里云官方博客
4分钟前
1
0
LockSupport 源码

类 LockSupport java.util.concurrent.locks.LockSupport extends Object 用来创建锁和其他同步类的基本线程阻塞原语。 此类以及每个使用它的线程与一个许可关联(从 Semaphore 类的意义上说...

狼王黄师傅
4分钟前
0
0
《阿里巴巴 Java开发手册》读后感

前言 只有光头才能变强 前一阵子一直在学Redis,结果在黄金段位被虐了,暂时升不了段位了,每天都拿不到首胜(好烦)。 趁着学校校运会,合理地给自己放了一个小长假,然后就回家了。回到家才发...

Java3y
6分钟前
0
0
Mac sorceTree一直显示Passwprd Required

sourceTree 1.我是从码云上建了一个项目然后下载下来再推上去的是就报这个错 解决方法 打开sourceTree偏好设置===》打开网络===》修改url路径(这个就是你登录码云的用户名)...

潇潇程序缘
7分钟前
0
0
如何创建和部署一个属于自己的EOS代币

本文我们将弄清楚什么是EOS代币以及如何自己创建和部署EOS代币。 与以太坊相反,EOS带有即插即用的代币智能合约。以太坊拥有ERC20智能合约,EOS拥有eosio.token智能合约。Eosio.token智能合约...

笔阁
7分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部