软件世界网 购物 网址 三丰软件 | 小说 美女秀 图库大全 游戏 笑话 | 下载 开发知识库 新闻 开发 图片素材
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
移动开发 架构设计 编程语言 Web前端 互联网
开发杂谈 系统运维 研发管理 数据库 云计算 Android开发资料
  软件世界网 -> 数据库 -> Mysql索引及优化使用总结 -> 正文阅读

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


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,分析的结果:
[img]http://img.blog.csdn.net/20160326221613957?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
从上图,我们看的出来,本次检索复杂界别为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、分析检索
[img]http://img.blog.csdn.net/20160326221624426?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
 
在这里,我们只对比不同的地方,使用了索引之后,检索的类型是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、分析索引
[img]http://img.blog.csdn.net/20160326221629114?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
从上图分析,为了演示准确,我们将上面建有的普通索引删除,再新建一唯一索引。使用与普通索引同样的检索SQL,分析后主要的结果是一样的;另外,得到的时长也是相同的。上面只是分析了唯一索引在速度方面,下面再来看看唯一索引在数据唯一性方面的特性,这里我们选择插入一条已有的数据,正常系统应该返回并提示错误信息:不能重复插入相同的唯一索引字段内容。
 
插入的语句:
INSERT INTO t_user_info(account,password,nickname,email,address) VALUES (
    "cwteam11",
    "e10adc3949ba59abbe56e057f20f883e",
    "cwteam11",
    "abc@yeah.net",
    "china shss"
);
 
测试结果:
[img]http://img.blog.csdn.net/20160326221634520?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
 
得出结论:
唯一索引的创建和使用,不影响它的查询优势,同时也保证了数据的唯一性。
 
3、组合索引
A、创建索引
ALTER TABLE t_user_info DROP INDEX account;
ALTER TABLE t_user_info ADD INDEX idx_user_info(account,password);
 
B、分析索引
[img]http://img.blog.csdn.net/20160326221640786?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
从上图看到,组合索引的使用与普通的索引相同的,只不过索引的字段数量不同罢了;另外,在查询时间上也是相同的。
 
得出结论:
组合索引与普通索引原理相同,不同的是索引的字段数量而已,同时也不影响检索速度。
 
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";
分析结果:
[img]http://img.blog.csdn.net/20160326221645989?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
从上图看出,在使用LIKE检索时,采用的是全表扫描,并未使用索引扫描,时长在1.16秒左右浮动,这对于500万条数据其实还是可以的,但随着数据的增加这个时长也会随之增加的,下面来看下使全文索引生效的MATCH…AGAINST的特点。
 
B、使用全文索引
EXPLAIN SELECT account,nickname,email,address FROM t_user_info WHEREMATCH(account,password) AGAINST(' cwteam400000');
分析结果:
[img]http://img.blog.csdn.net/20160326221703068?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
从上图明显看出,检索使用了全文检索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  
数据库 最新文章
Python&MySQL&PyQt
最新用python来操作mysql完全解析
mongodb的安装详解
1.PDO简介
《MySQL必知必会学习笔记》:高级联结
【翻译自mos文章】怎么对Microsoft(Office)
MyCAT全局表描述及示例
ocp
关于SQL数据表存储过程表名前缀换成dbo代码
数据库调优教程(二)慢查询数据准备
360图书馆 软件开发资料 文字转语音 购物精选 软件下载 美食菜谱 新闻资讯 电影视频 小游戏 Chinese Culture
生肖星座 三丰软件 视频 开发 Android开发 站长 古典小说 网文精选 搜图网 美图 中国文化英文版 多播 租车 短信
2017-7-24 10:35:29
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  软件世界网 --