文档章节

Mysql 索引

蔡少东
 蔡少东
发布于 2014/11/17 15:14
字数 2035
阅读 159
收藏 26
点赞 1
评论 1

什么是索引

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

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

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


索引类型


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

 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

共有 人打赏支持
蔡少东
粉丝 20
博文 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
MySQL专题9之MySQL索引、MySQL临时表、MySQL复制表

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

极客微信条
2017/11/19
0
0
MYSQL索引分析和优化设计方案

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

有些服务器
2015/11/02
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
MySQL索引类型一览 让MySQL高效运行起来

本文介绍了七种MySQL索引类型。在数据库表中,对字段建立索引可以大大提高查询速度。通过善用这些索引,可以令MySQL的查询和运行更加高效。 索引是快速搜索的关键。MySQL索引的建立对于MySQL...

5W1H-
2013/06/13
0
0
MySQL单列索引和组合索引的区别介绍

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

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

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

zhdan
2016/05/26
196
0
Mysql有效索引和无效索引的介绍

Mysql有效索引和无效索引的介绍 标签: Mysql 索引 内容转自前往 1.索引可能失效的场景 1.WHERE字句的查询条件里有不等于号(WHERE column!=...),MYSQL将无法使用索引 2.类似地,如果WHERE...

hebad
2015/01/22
0
1
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
当hibernate遇到不好的mysql索引选择时,该如何处理?

项目维护的时候遇到了这个问题: 项目框架:ssh 数据库:mysql mysql中有表[tb],里面有2个索引A、B,都是经常使用的 前几天项目的某个用户在某个网页需要十多秒才能响应,最终确定慢的原因是...

littleDuck
2013/11/19
777
1

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Iterm2安装Zsh + Oh My Zsh+Solarized

安装Oh My Zsh curl -L https://raw.github.com/robbyrussell/oh-my-zsh/master/tools/install.sh | sh 安装Zsh: 1.安装zsh brew install zsh 2.配置iterm2 sudo vim /etc/shells输入:......

孟飞阳
10分钟前
0
0
缓解ddos攻击的有效解决办法

因为防火墙无法处理所有类型的ddos攻击,下一代防火墙生成内置的DDoS防御,但是无法处理所有类型的攻击。对抗DDoS攻击的最佳做法是有专属的设备或者服务在攻击透过防火墙或者其他的IT基础设施...

上树的熊
10分钟前
0
0
Spark Streaming如何使用checkpoint容错

在互联网场景下,经常会有各种实时的数据处理,这种处理方式也就是流式计算,延迟通常也在毫秒级或者秒级,比较有代表性的几个开源框架,分别是Storm,Spark Streaming和Filnk。 伦理片 http...

刺猬一号
12分钟前
1
0
Xamarin Essentials教程地理定位Geolocation

Xamarin Essentials教程地理定位Geolocation 通过地理定位功能,应用程序可以获取用户的当前地理位置,如经纬度值。利用地理位置,可以在地图上定位,也可以转化物理位置,划分用户的归属地。...

大学霸
25分钟前
0
0
vue 编译警告 Compiled with 4 warnings

There are multiple modules with names that only differ in casing. This can lead to unexpected behavior when compiling on a filesystem with other case-semantic. Use equal casing.......

落雪飞声
29分钟前
0
0
开篇文章,长期记录安全情形

密码位置 密码位于注释中 密码位于服务器端文件中 通过访问根目录下.htaccess、robots.txt查看禁查路径 密码文件可能存在的路径:/、/extra/、/extras/ 密码加密 binary to base16 sha256 彩虹...

hirainn
42分钟前
0
0
mysql数据库设置root可以远程登录的方法

mysql数据库设置root可以远程登录的方法 Posted on 2018-02-21 21:08 sishuisufeng 阅读(161) 评论(0) 编辑 收藏 允许root用户在任何地方进行远程登录,并具有所有库任何操作权限,具体操作如...

rootliu
47分钟前
1
0
TensorFlow 图的基本操作

图的创建,一般只需要使用默认图就能满足大部分的需求了 # 1 创建图的方法# 在默认图中创建常量c = tf.constant(0.0)# 新建一个图g = tf.Graph()# 设置上下文管理器,标明操作...

阿豪boy
今天
0
0
git 忽略文件失效

git update-index --assume-unchanged */.project

林子大鸟
今天
1
0
实现验证码功能

1、实现验证码,并存储 import com.dtb.pc_enterprise.entity.EnterUserEntity;import com.dtb.pc_enterprise.service.AdminService;import com.dtb.pc_enterprise.util.RedisService;......

木九天
今天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部