软件世界网 购物 网址 三丰软件 | 小说 美女秀 图库大全 游戏 笑话 | 下载 开发知识库 新闻 开发 图片素材
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
移动开发 架构设计 编程语言 Web前端 互联网
开发杂谈 系统运维 研发管理 数据库 云计算 Android开发资料
  软件世界网 -> 数据库 -> Mysql索引及优化使用总结 -> 正文阅读
数据库 最新文章
Python&MySQL&PyQt
最新用python来操作mysql完全解析
mongodb的安装详解
1.PDO简介
《MySQL必知必会学习笔记》:高级联结
【翻译自mos文章】怎么对Microsoft(Office)
MyCAT全局表描述及示例
ocp
关于SQL数据表存储过程表名前缀换成dbo代码
数据库调优教程(二)慢查询数据准备

[数据库]Mysql索引及优化使用总结

  2016-03-28 21:48:50

Mysql索引及优化使用总结:
在关系数据库中,索引的使用十分重要,而且所有的关系数据库支持索引机制,因为有了索引之后,在大数据量检索数据时速度很快,性能消耗很低;当然,凡事有利必有弊,增加索引也会增加数据库系统的开销,我们很多时候需要在性能和检索间折中设计,而且正确使用索引及对他维护和优化是很重要的!
 
·     索引的类型?
·     索引的原理?
·     何时建索引?
·     索引的使用?
·     索引的优化?
 
一、索引的类型
在Mysql中,索引可分为普通索引、唯一索引、主键索引、外键索引及组合索引,它们在创建索引位置上都是一样的,即在表的一个或多个字段上创建使用,具体介绍下:
 
1、普通索引
普通索引的唯一任务就是加快数据的检索速度,应该在经常出现在WHERE或ORDER BY后的单一列上使用,而且该索引列可以重复,由关键字KEY或INDEX定义的索引。
 
2、唯一索引
唯一索引的主要任务除了加快数据的检索速度之外,还有就是约束创建或使用唯一索引的单一列必须是不重复的,也就是保证了数据的唯一性,所以,它的主要作用体现在检索速度和数据唯一方面,由关键字UNIQUE定义的索引。
 
3、主键索引
主键索引是MYSQL为数据表主键字段默认生成的索引,它与唯一索引的唯一不同就是它们的定义语句,这里使用的是PRIMARY而不是UNIQUE。
 
4、外键索引
如果为某个外键字段定义一个外键,MYSQL就会为其生成一个索引,来帮助加快对外键约束的使用,由关键字KEY定义,外键数据可重复。
 
5、组合索引
组合索引与普通索引的区别是在多个列上定义,与唯一索引相同的是组合索引必须保证唯一,而且MYSQL可以选择不同的索引字段的合适组合来组合查询索引(适用于排列在前的数据列组合)。
 
6、全文索引
文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字,普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现,这对MySQL来说很复杂,如果需要处理的数据量很大,响应时间就会很长。
这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时,MySQL将把在文本中出现的所有单词创建为一份清单,查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建,也可以等日后有必要时再使用下面这条命令添加:
ALTER TABLE tablename ADD FULLTEXT(column1, column2)
有了全文索引,就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法:
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)
上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。
 
注:
有博文说全文索引在数据库引擎InnoDB中不支持,其实不然,在MYSQL版本5.6已经引入了全文索引
 
二、索引的原理
索引的工作原理比较清楚明了,在创建或使用索引时,默认按照某种规则为索引字段排好顺序,在用户检索数据时,系统首先检索排序的索引内容,如果找到匹配的内容即刻返回,不进行全文检索,这样也就是大大提高了检索的速度了,在下面索引的分析中会演示索引的工作原理,请继续阅读。
 
三、何时建索引
1、准备工作
鉴于创建索引需要额外的磁盘空间,需要后期维护清理索引碎片,以及增加表的CUD操作的性能,所以创建索引需要谨慎考虑哦。
 
2、何时创建
既然索引的诞生就是为了解决数据的检索效率,那么很明显需要在经常查询的表的某些字段上创建并维护索引,鉴于准备工作部分考虑的因素,对于那些经常查询也同时经常CUD的表字段上如果必须添加索引,那么推荐使用合适的算法使用索引,比如:二分检索(这里不做介绍)。
 
四、索引的使用
语法结构:
CREATE [NORMAL|UNIQUE|FULLTEXT] INDEX index_name
ON table_name (col_name[(length)[ASC | DESC]],[…])
 
NOTE:
 index_name:索引名字;
 table_name:建立索引的数据表;
 col_name:建立索引的表字段,length为内容字符的前多少位加入索引算法;
 
1、普通索引
首先,我准备一张数据表t_user_info,该表中我添加了500万条数据,下面我们来测分析下,在使用索引和未使用索引的差别,同样条件的检索:
SELECT account,nickname,email,address FROM t_user_infoWHERE account="cwteam4000000";
未使用索引:使用EXPLAIN分析SQL,分析的结果:

从上图,我们看的出来,本次检索复杂界别为select_type,即简单查询;检索类型为type全表检索;并未使用索引key(NULL);查询条件Extra为where;检索的条数为4889736;另外,检索的时长在4秒左右浮动(去掉EXPLAIN执行查看)。
 
已使用索引:使用EXPLAIN分析SQL,分析的结果:
A、创建索引
DROP INDEX IF EXISTS idx_user_info ON t_user_info;
CREATE INDEX idx_user_info ON t_user_info(account);
 
B、分析检索

 
在这里,我们只对比不同的地方,使用了索引之后,检索的类型是ref即索引算法检索;使用了key索引,索引名字为idx_user_info;检索的条数为1;查询条件Extra为Using index condition;另外,检索的时长在0.01秒左右浮动,基本提高了4倍速度。
 
得出结论:
在数据量大的检索中,对比未使用索引,基本提高了4个级别程度,所以在经常检索的表字段建立索引使用。
 
2、唯一索引
A、创建索引
ALTER TABLE t_user_info DROP INDEX idx_user_info;
ALTER TABLE t_user_info ADD UNIQUE (account);
 
B、分析索引

从上图分析,为了演示准确,我们将上面建有的普通索引删除,再新建一唯一索引。使用与普通索引同样的检索SQL,分析后主要的结果是一样的;另外,得到的时长也是相同的。上面只是分析了唯一索引在速度方面,下面再来看看唯一索引在数据唯一性方面的特性,这里我们选择插入一条已有的数据,正常系统应该返回并提示错误信息:不能重复插入相同的唯一索引字段内容。
 
插入的语句:
INSERT INTO t_user_info(account,password,nickname,email,address) VALUES (
    "cwteam11",
    "e10adc3949ba59abbe56e057f20f883e",
    "cwteam11",
    "abc@yeah.net",
    "china shss"
);
 
测试结果:

 
得出结论:
唯一索引的创建和使用,不影响它的查询优势,同时也保证了数据的唯一性。
 
3、组合索引
A、创建索引
ALTER TABLE t_user_info DROP INDEX account;
ALTER TABLE t_user_info ADD INDEX idx_user_info(account,password);
 
B、分析索引

从上图看到,组合索引的使用与普通的索引相同的,只不过索引的字段数量不同罢了;另外,在查询时间上也是相同的。
 
得出结论:
组合索引与普通索引原理相同,不同的是索引的字段数量而已,同时也不影响检索速度。
 
NOTE:
一般,组合索引用在保证多个列唯一的需求,也就是要结合UNIQUE索引才有实际意义,上面只是演示索引的不同。
 
4、全文索引
全文索引默认在引擎MyISAM支持,但在Mysql5.6版本开始,数据库引擎InnoDB也开始支持了。由于我的MYSQL版本低于5.6,并未升级(后续升级),所以这里暂时修改数据表的引擎为MyISAM,目的是为了介绍全文检索。
在500万的数据量中,我们对比LIKE和MATCH(…)AGAINST(…)在达到同样功能时:
 
 
两者的差别:
A、使用LIKE检索
EXPLAIN SELECT account,nickname,email,address FROM t_user_info WHEREaccount LIKE " cwteam400000";
分析结果:

从上图看出,在使用LIKE检索时,采用的是全表扫描,并未使用索引扫描,时长在1.16秒左右浮动,这对于500万条数据其实还是可以的,但随着数据的增加这个时长也会随之增加的,下面来看下使全文索引生效的MATCH…AGAINST的特点。
 
B、使用全文索引
EXPLAIN SELECT account,nickname,email,address FROM t_user_info WHEREMATCH(account,password) AGAINST(' cwteam400000');
分析结果:

从上图明显看出,检索使用了全文检索FULLTEXT,时长在0.01秒左右浮动,所以全文索引的效率还是挺高的,当然它的检索时长也会随着数据量增多,不断增多哦(好像是废话)。
 
得出结论:
如果建立的索引列数较多,建议使用全文检索,而在大数据量检索中,可使用MATCH-AGAINST使FULLTEXT生效,代替使用LIKE全表检索。
 
五、索引的优化
根据上面对索引的介绍,我们可以总结一下常用的优化索引建议:
 
1、在经常查询的表字段建立索引
具体创建什么索引,可根据需求来定,如果没有特殊要求,如:是否允许重复,那么就可以创建一普通的索引;否则,可以创建一个唯一缩影;如果需要多个索引列唯一,那么就创建唯一组合索引即可。
 
2、在大数据量检索中,尽量使用FULL-TEXT索引代替LIKE
使用InnoDB引擎的同学,需要升级MYSQL版本到5.6,或者使用MyISAM引擎。
 
3、维护优化索引碎片
在建有索引的数据表中,每当删除记录数据时,对应记录上的索引标记并未删除,这会产生数据垃圾,也叫碎片,长期以往不作处理的话,会影响数据的检索效率。
比较好的办法:重建索引。
 
4、避免使用聚合函数
在建有索引的数据检索中,尽量在检索条件后不使用聚合函数,这可能会使索引失效,影响数据检索速度。
 
 
NOTE:
对于索引的优化,很多时候就是保证索能发挥其正常的功能,所以很多围绕索引的优化,其实就是避免索引被迫害,剩下的就是对检索的SQL的优化了。
 
 
 
好了,索引及优化就总结到这里,如有问题,欢迎积极指出并讨论。
 (技术讨论群:276592700(新))
 
 
上一篇文章      下一篇文章      查看所有文章
2016-03-28 21:48:11  
360图书馆 论文大全 母婴/育儿 软件开发资料 网页快照 文字转语音 购物精选 软件 美食菜谱 新闻中心 电影下载 小游戏 Chinese Culture
生肖星座解梦 人民的名义 人民的名义在线看 三沣玩客 拍拍 视频 开发 Android开发 站长 古典小说 网文精选 搜图网 天下美图
中国文化英文 多播视频 装修知识库
2017-4-24 23:16:40
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  软件世界网 --