Mysql使用索引实现查询优化

索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的。

1.索引的优点

假设你拥有三个未索引的表t1、t2和t3,每个表都分别包含数据列i1、i2和i3,并且每个表都包含了1000条数据行,其序号从1到1000。查找某些值匹配的数据行组合的查询可能如下所示:

SELECT t1.i1, t2.i2, t3.i3
FROM t1, t2, t3
WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;

  这个查询的结果应该是1000行,每个数据行包含三个相等的值。如果在没有索引的情况下处理这个查询,那么如果我们不对这些表进行全部地扫描,我们是没有办法知道哪些数据行含有哪些值的。因此你必须尝试所有的组合来查找符合WHERE条件的记录。可能的组合的数量是1000 x 1000 x 1000(10亿!),它是匹配记录的数量的一百万倍。这就浪费了大量的工作。这个例子显示,如果没有使用索引,随着表的记录不断增长,处理这些表的联结所花费的时间增长得更快,导致性能很差。我们可以通过索引这些数据表来显著地提高速度,因为索引让查询采用如下所示的方式来处理:

  1.选择表t1中的第一行并查看该数据行的值。

  2.使用表t2上的索引,直接定位到与t1的值匹配的数据行。类似地,使用表t3上的索引,直接定位到与表t2的值匹配的数据行。

  3.处理表t1的下一行并重复前面的过程。执行这样的操作直到t1中的所有数据行都被检查过。

  在这种情况下,我们仍然对表t1执行了完整的扫描,但是我们可以在t2和t3上执行索引查找,从这些表中直接地获取数据行。理论上采用这种方式运行上面的查询会快一百万倍。当然这个例子是为了得出结论来人为建立的。然而,它解决的问题却是现实的,给没有索引的表添加索引通常会获得惊人的性能提高。
-

2.索引的代价

首先,索引加快了检索的速度,但是减慢了插入和删除的速度,同时还减慢了更新被索引的数据列中的值的速度。也就是说,索引减慢了大多数涉及写操作的速度。发生这种现象的原因在于写入一条记录的时候不但需要写入数据行,还需要改变所有的索引。数据表带有的索引越多,需要做出的修改就越多,平均性能的降低程度也就越大。在本文的”高效率载入数据”部分中,我们将更细致地了解这些现象并找出处理方法。

  其次,索引会花费磁盘空间,多个索引相应地花费更多的磁盘空间。这可能导致更快地到达数据表的大小限制:

  · 对于MyISAM表,频繁地索引可能引起索引文件比数据文件更快地达到最大限制。

  · 对于BDB表,它把数据和索引值一起存储在同一个文件中,添加索引引起这种表更快地达到最大文件限制。

  · 在InnoDB的共享表空间中分配的所有表都竞争使用相同的公共空间池,因此添加索引会更快地耗尽表空间中的存储。但是,与MyISAM和BDB表使用的文件不同,InnoDB共享表空间并不受操作系统的文件大小限制,因为我们可以把它配置成使用多个文件。只要有额外的磁盘空间,你就可以通过添加新组件来扩展表空间。

  使用单独表空间的InnoDB表与BDB表受到的约束是一样的,因为它的数据和索引值都存储在单个文件中。

  这些要素的实际含义是:如果你不需要使用特殊的索引帮助查询执行得更快,就不要建立索引。

3.选择索引

  假设你已经知道了建立索引的语法,但是语法不会告诉你数据表应该如何索引。这要求我们考虑数据表的使用方式。这一部分指导你如何识别出用于索引的备选数据列,以及如何最好地建立索引:

  用于搜索、排序和分组的索引数据列并不仅仅是用于输出显示的。换句话说,用于索引的最好的备选数据列是那些出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。仅仅出现在SELECT关键字后面的输出数据列列表中的数据列不是很好的备选列:

SELECT
col_a <- 不是备选列
FROM
tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c <- 备选列
WHERE
col_d = expr; <- 备选列

  当然,显示的数据列与WHERE子句中使用的数据列也可能相同。我们的观点是输出列表中的数据列本质上不是用于索引的很好的备选列。

  Join子句或WHERE子句中类似col1 = col2形式的表达式中的数据列都是特别好的索引备选列。前面显示的查询中的col_b和col_c就是这样的例子。如果MySQL能够利用联结列来优化查询,它一定会通过减少整表扫描来大幅度减少潜在的表-行组合。

  考虑数据列的基数(cardinality)。基数是数据列所包含的不同值的数量。例如,某个数据列包含值1、3、7、4、7、3,那么它的基数就是4。索引的基数相对于数据表行数较高(也就是说,列中包含很多不同的值,重复的值很少)的时候,它的工作效果最好。如果某数据列含有很多不同的年龄,索引会很快地分辨数据行。如果某个数据列用于记录性别(只有”M”和”F”两种值),那么索引的用处就不大。如果值出现的几率几乎相等,那么无论搜索哪个值都可能得到一半的数据行。在这些情况下,最好根本不要使用索引,因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。惯用的百分比界线是”30%”。现在查询优化器更加复杂,把其它一些因素也考虑进去了,因此这个百分比并不是MySQL决定选择使用扫描还是索引的唯一因素。

  索引较短的值。尽可能地使用较小的数据类型。例如,如果MEDIUMINT足够保存你需要存储的值,就不要使用BIGINT数据列。如果你的值不会长于25个字符,就不要使用CHAR(100)。较小的值通过几个方面改善了索引的处理速度:

  · 较短的值可以更快地进行比较,因此索引的查找速度更快了。

  · 较小的值导致较小的索引,需要更少的磁盘I/O。

  · 使用较短的键值的时候,键缓存中的索引块(block)可以保存更多的键值。MySQL可以在内存中一次保持更多的键,在不需要从磁盘读取额外的索引块的情况下,提高键值定位的可能性。

  对于InnoDB和BDB等使用聚簇索引(clustered index)的存储引擎来说,保持主键(primary key)短小的优势更突出。聚簇索引中数据行和主键值存储在一起(聚簇在一起)。其它的索引都是次级索引;它们存储主键值和次级索引值。次级索引屈从主键值,它们被用于定位数据行。这暗示主键值都被复制到每个次级索引中,因此如果主键值很长,每个次级索引就需要更多的额外空间。

  索引字符串值的前缀(prefixe)。如果你需要索引一个字符串数据列,那么最好在任何适当的情况下都应该指定前缀长度。例如,如果有CHAR(200)数据列,如果前面10个或20个字符都不同,就不要索引整个数据列。索引前面10个或20个字符会节省大量的空间,并且可能使你的查询速度更快。通过索引较短的值,你可以获得那些与比较速度和磁盘I/O节省相关的好处。当然你也需要利用常识。仅仅索引某个数据列的第一个字符串可能用处不大,因为如果这样操作,那么在索引中不会有太多的唯一值。

  你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀。

  使用最左(leftmost)前缀。建立多列复合索引的时候,你实际上建立了MySQL可以使用的多个索引。复合索引可以作为多个索引使用,因为索引中最左边的列集合都可以用于匹配数据行。这种列集合被称为”最左前缀”(它与索引某个列的前缀不同,那种索引把某个列的前面几个字符作为索引值)。

  假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列,因此它们也会自动地按照state/city和state次序排列。这意味着,即使你在查询中只指定了state值,或者指定state和city值,MySQL也可以使用这个索引。因此,这个索引可以被用于搜索如下所示的数据列组合:

state, city, zip
state, city
state

  MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如,如果你按照city或zip来搜索,就不会使用到这个索引。如果你搜索给定的state和具体的ZIP代码(索引的1和3列),该索引也是不能用于这种组合值的,尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围。

  不要过多地索引。不要认为”索引越多,性能越高”,不要对每个数据列都进行索引。我们在前面提到过,每个额外的索引都会花费更多的磁盘空间,并降低写操作的性能。当你修改表的内容的时候,索引就必须被更新,甚至可能重新整理。如果你的索引很少使用或永不使用,你就没有必要减小表的修改操作的速度。此外,为检索操作生成执行计划的时候,MySQL会考虑索引。建立额外的索引会给查询优化器增加更多的工作量。如果索引太多,有可能(未必)出现MySQL选择最优索引失败的情况。维护自己必须的索引可以帮助查询优化器来避免这类错误。

  如果你考虑给已经索引过的表添加索引,那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。如果是这样的,不用增加索引,因为已经有了(例如,如果你在state、city和zip上建立了索引,那么没有必要再增加state的索引)。

  让索引类型与你所执行的比较的类型相匹配。在你建立索引的时候,大多数存储引擎会选择它们将使用的索引实现。例如,InnoDB通常使用B树索引。MySQL也使用B树索引,它只在三维数据类型上使用R树索引。但是,MEMORY存储引擎支持散列索引和B树索引,并允许你选择使用哪种索引。为了选择索引类型,需要考虑在索引数据列上将执行的比较操作类型:

  · 对于散列(hash)索引,会在每个数据列值上应用散列函数。生成的结果散列值存储在索引中,并用于执行查询。散列函数实现的算法类似于为不同的输入值生成不同的散列值。使用散列值的好处是散列值比原始值的比较效率更高。散列索引用于执行=或<=>操作等精确匹配的时候速度非常快。但是对于查询一个值的范围效果就非常差了:

id < 30
weight BETWEEN 100 AND 150

  · B树索引可以用于高效率地执行精确的或者基于范围(使用操作<、<=、=、>=、>、<>、!=和BETWEEN)的比较。B树索引也可以用于LIKE模式匹配,前提是该模式以文字串而不是通配符开头。

  如果你使用的MEMORY数据表只进行精确值查询,散列索引是很好的选择。这是MEMORY表使用的默认的索引类型,因此你不需要特意指定。如果你希望在MEMORY表上执行基于范围的比较,应该使用B树索引。为了指定这种索引类型,需要给索引定义添加USING BTREE。例如:

CREATE TABLE lookup
(
id INT NOT NULL,
name CHAR(20),
PRIMARY KEY USING BTREE (id)
) ENGINE = MEMORY;

  如果你希望执行的语句的类型允许,单个MEMORY表可以同时拥有散列索引和B树索引,即使在同一个数据列上。

  有些类型的比较不能使用索引。如果你只是通过把值传递到函数(例如STRCMP())中来执行比较操作,那么对它进行索引就没有价值。服务器必须计算出每个数据行的函数值,它会排除数据列上索引的使用。

  使用慢查询(slow-query)日志来识别执行情况较差的查询。这个日志可以帮助你找出从索引中受益的查询。你可以直接查看日志(它是文本文件),或者使用mysqldumpslow工具来统计它的内容。如果某个给定的查询多次出现在”慢查询”日志中,这就是一个线索,某个查询可能没有优化编写。你可以重新编写它,使它运行得更快。你要记住,在评估”慢查询”日志的时候,”慢”是根据实际时间测定的,在负载较大的服务器上”慢查询”日志中出现的查询会多一些。

*4.建索引的几大原则*

4.1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

4.2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式

4.3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录

4.4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = '2014-05-29'就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp('2014-05-29');

4.5.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

以上所述是小编给大家介绍的Mysql使用索引实现查询优化,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我们网站的支持!

时间: 2016-07-25

mysql性能优化工具--tuner-primer使用介绍

下载并改变执行权限: wget http://www.day32.com/MySQL/tuning-primer.sh chmod +x tuning-primer.sh ./tuning-primer.sh 结果报告: 会用几种颜色标记: 蓝色:总指标 绿色:表示此参数还可以 红色:表示此参数有严重问题 深红色:表示有问题参数 黄色:一些信息提示 而且还有警告: Note! This script will still suggest raising the join_buffer_size

MySQL存储过程的优化实例

前言 在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用存储过程来封装数据库操作.如果项目的存储过程较多,书写又没有一定的规范,将会影响以后的系统维护困难和大存储过程逻辑的难以理解,另外如果数据库的数据量大或者项目对存储过程的性能要求很,就会遇到优化的问题,否则速度有可能很慢,经过亲身经验,一个经过优化过的存储过程要比一个性能差的存储过程的效率甚至高几百倍.下面介绍某一个MySQL存储过程优化的整个过程. 在本文中,需要被优化的存储过程如下: drop procedu

MySQL数据库21条最佳性能优化经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情. 当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库.希望下面的这些优化技巧对你有用. 1. 为查询缓存优化你的查询 大多数的MySQL服务器都开启了查询缓存.这是提高性最有效的方法之一,而且这是被M

MySQL优化总结-查询总条数

1.COUNT(*)和COUNT(COL) COUNT(*)通常是对主键进行索引扫描,而COUNT(COL)就不一定了,另外前者是统计表中的所有符合的纪录总数,而后者是计算表中所有符合的COL的纪录数.还有有区别的. 优化总结,对于MyISAM表来说: 1.任何情况下SELECT COUNT(*) FROM tablename是最优选择: 2.尽量减少SELECT COUNT(*) FROMtablename WHERE COL = 'value' 这种查询: 3.杜绝SELECT COUNT(

win2008 r2 服务器php+mysql+sqlserver2008运行环境配置(从安装、优化、安全等)

win2008 r2 安装 http://www.jb51.net/article/38048.htm iis的安装 http://www.jb51.net/article/86390.htm php的安装注意事项: 下载非安全线程 版本 nts php 5.2.17 http://www.jb51.net/softs/268745.html 其它版本可以到 http://museum.php.net/php5/里面很多经典的老版本. PHP 5.5 (5.5.36) VC11 x64 Non

Ubuntu服务器下搭建php运行环境的方法

本文实例讲述了Ubuntu服务器下搭建php运行环境的方法.分享给大家供大家参考,具体如下: 安装 Apache2: sudo apt-get install apache2 安装PHP模块: sudo apt-get install php5 安装Mysql sudo apt-get installmysql-server 其他模块安装: sudo apt-get install libapache2-mod-php5 sudo apt-get installlibapache2-mod-au

阿里云完美教程 Window2003 iis+mysql+php+zend环境配置

在我的使用过程中只遇到几个小问题: 一.HTTP 错误 403.1 – 禁止访问:执行访问被拒绝 原因是执行权限不够,解决的方法是: 解决方法一: 打开"管理工具"的"Internet 信息服务",右键选择"WEB站点属性"的"主目录"选项卡,把"执行许可"的选项从"无"改为"纯脚本"就好了. 解决方法二: 1.打开IIS设置 2. 在建虚拟目录或网站时注意以下设置

深入PHP运行环境配置的详解

运行环境配置:php54+Apache2.2+Mysql 1.默认端口设置: #Listen 12.34.56.78:80 Listen 8080 2.加载模型: LoadModule php5_module "D:\MyZILIAO\PHP\php54\php5apache2_2.dll" 3.支持的类型: AddType application/x-compress .Z    AddType application/x-gzip .gz .tgz    AddType appli

VSCode各语言运行环境配置方法示例详解

系统环境变量的配置 如:将F:\mingw64\bin添加到系统环境变量Path中 VSCode软件语言json配置C语言 创建个.vscode文件夹,文件夹内创建以下两个文件 launch.json 文件配置 { "version": "0.2.0", "configurations": [ { "name": "(gdb) Launch", "type": "cppdbg&

windows系统下node.js环境配置与安装教程图文详解(详细版)

国内目前关注最高,维护最好的一个关于nodejs的网站应该是http://www.cnodejs.org/ windows系统下简单nodejs环境配置. 第一步:下载安装文件 下载地址:官网https://nodejs.org/en/download/ 这里用的是 第二步:安装nodejs 下载完成之后,双击 node-v6.9.1-x64.msi,开始安装nodejs,默认是安装在C:\Program Files\nodejs下面 第三步:安装相关环境 打开C:\Program Files\

TensorFlow的环境配置与安装教程详解(win10+GeForce GTX1060+CUDA 9.0+cuDNN7.3+tensorflow-gpu 1.12.0+python3.5.5)

记录一下安装win10+GeForce GTX1060+CUDA 9.0+cuDNN7.3+tensorflow-gpu 1.12.0+python3.5.5 之前已经安装过pycharm.Anaconda以及VS2013,因此,安装记录从此后开始 总体步骤大致如下: 1.确认自己电脑显卡型号是否支持CUDA(此处有坑) 此处有坑!不要管NVIDIA控制面板组件中显示的是CUDA9.2.148. 你下载的CUDA不一定需要匹配,尤其是CUDA9.2,最好使用CUDA9.0,我就在此坑摔的比较惨.

win2008 r2 服务器环境配置(FTP/ASP/ASP.Net/PHP)

以下是FTP/ASP/PHP/ASP.Net环境配置 一.FTP与ASP安装流程通过远程登录VPS,点击计算机管理>角色1.1首先我们选择自己需要装的配置环境ASP与FTP,所以只要在前面打勾再安装就可以了 1.2:需要安装的配置,确认安装 1.3:安装完成 二.安装PHP环境2.1首先我们通过在线平台安装PHP所需要的软件配置 2.2进入平台>产品>框架>选择PHP5.3.8与Windows Cache Extension 1.1 for PHP 5.3安装 2.3安装确认 2.

win2008 R2服务器下修改MySQL 5.5数据库data目录的方法

说明: 操作系统:Windows Server 2008 R2 MySQL版本:5.5.25a MySQL程序安装目录:D:\Program Files\MySQL\MySQL Server 5.5 MySQL数据库默认目录:C:\ProgramData\MySQL\MySQL Server 5.5\data\ 需求:修改MySQL数据库目录为D:\Program Files\MySQL\MySQL Server 5.5\data 具体操作: 一.停止MySQL 开始-cmd net stop

win2008 r2 服务器安全配置步骤小结 原创

首先.安装好系统先激活,暂时不要安装补丁,一般机房就给弄好了 不懂的同学可以参考这篇文章: Windows Server 2008 R2 服务器系统安装图文教程 一.IP安全策略(导入导出,这个时候没有任何防护必须要弄下) http://www.jb51.net/article/86271.htm 单独下载规则:http://xiazai.jb51.net/server/win2008_ipsec(jb51.net).rar 二. 防火墙导出并导入(导入一定要关闭macfee否则导致无法远程)