文档章节

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
多写一些屌屌的内容出来~~
MySQL 索引选择原则分析(二)

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

tara_qri
2015/10/14
24
0
SQL基础-DDL数据定义语言

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

ZHAO_JH
2018/08/10
0
0
MySQL专题9之MySQL索引、MySQL临时表、MySQL复制表

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

极客微信条
2017/11/19
0
0
MySQL单列索引和组合索引的区别介绍

MySQL单列索引是我们使用MySQL数据库中经常会见到的,MySQL单列索引和组合索引的区别可能有很多人还不是十分的了解,下面就为您分析两者的主要区别,供您参考学习。 为了形象地对比两者,再建...

BearCatYN
2015/07/10
166
0
Mysql索引介绍及常见索引(主键索引、唯一索引、普通索引、全文索引、组合索引)的区别

Mysql索引概念: 说说Mysql索引,看到一个很少比如:索引就好比一本书的目录,它会让你更快的找到内容,显然目录(索引)并不是越多越好,假如这本书1000页,有500也是目录,它当然效率低,目...

zhdan
2016/05/26
196
0

没有更多内容

加载失败,请刷新页面

加载更多

Pages Manager——可本地管理Pages服务内容,一键生成漂亮的文档界面。

Pages Manager Git地址 可本地管理Pages服务内容,一键生成漂亮的文档界面。在线预览 简单、轻便,无需安装数据库。 框架:spring-boot 数据库:sqlite 原理 本地维护一组markdown文档 将mar...

tanghc
19分钟前
1
0
基础目标检测算法介绍:CNN、RCNN、Fast RCNN和Faster RCNN

每次丢了东西,我们都希望有一种方法能快速定位出失物。现在,目标检测算法或许能做到。目标检测的用途遍布多个行业,从安防监控,到智慧城市中的实时交通监测。简单来说,这些技术背后都是强...

AI女神
19分钟前
1
0
哪有什么互联网寒冬?只是你穿的少而已!

声明:本文由终端研发部原创发布,未经允许,不得转载 前言 最近一段时间,大家都在说一些大公司纷纷裁员, 优化公司内部的组织架构。面对如此的寒冬变化,很多人在迷茫,在焦虑,在担忧自己...

终端研发部
24分钟前
2
0
nginx: [error] open() "/var/run/nginx.pid" failed (2: No such file or directory)

Nginx 启动时报错:nginx: [error] open() "/var/run/nginx.pid" failed (2: No such file or directory) 原因:系统重启 /var/run/ 目录下文件会清空。 方法一: # sudo nginx -c /etc/ngi......

驛路梨花醉美
27分钟前
2
0
TiDB 源码阅读系列文章(二十四)TiDB Binlog 源码解析

作者:姚维 TiDB Binlog Overview 这篇文章不是讲 TiDB Binlog 组件的源码,而是讲 TiDB 在执行 DML/DDL 语句过程中,如何将 Binlog 数据 发送给 TiDB Binlog 集群的 Pump 组件。目前 TiDB 在...

TiDB
40分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部