索引与SQL优化学习.pptx

《索引与SQL优化学习.pptx》由会员分享，可在线阅读，更多相关《索引与SQL优化学习.pptx（24页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、1.1.1.1.索引介绍索引介绍索引介绍索引介绍2.2.如何使用索引如何使用索引3.3.索引优化索引优化4.4.SQLSQL优化优化5.5.不良不良SQLSQL集锦集锦6.6.Q&AQ&A目目 录录第1页/共24页索引介绍索引介绍索引介绍索引介绍v 什么是什么是索引索引索引用来快速地寻找那些具有特定值的记录，MySQL索引大部分以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置 第2页/共

2、24页索引介绍索引介绍索引介绍索引介绍v 索引索引如何工作如何工作我们为什么不对数据行进行排序从而省掉索引？我们为什么不对数据行进行排序从而省掉索引？第3页/共24页索引介绍索引介绍索引介绍索引介绍v 索引索引如何工作如何工作SELECTt1.i1,t2.i2,t3.i3FROMt1,t2,t3WHEREt1.i1=t2.i2ANDt2.i1=t3.i3;这个查询的结果有索引扫描应该是1000行,没有索引 可能的组合的数量是1000 x 1000 x 1000(10亿!)1选择表t1中的第一行并查看该数据行的值。2使用表t2上的索引，直接定位到与t1的值匹配的数据行。类似地，使用表t3上的索引

3、，直接定位到与表t2的值匹配的数据行。3处理表t1的下一行并重复前面的过程。执行这样的操作直到t1中的所有数据行都被检查过。在这种情况下，我们仍然对表t1执行了完整的扫描，但是我们可以在t2和t3上执行索引查找，从这些表中直接地获取数据行 第4页/共24页索引介绍索引介绍索引介绍索引介绍v索引类型索引类型普通索引 这是最基本的索引类型，而且它没有唯一性之类的限制。唯一性索引 索引列的所有值都只能出现一次，即必须唯一 主键主键是一种唯一性索引,AUTO_INCREMENT类型的列 必须为主键全文索引 全文索引的索引类型为FULLTEXT 第5页/共24页v单列索引单列索引 v所有MySQL列类型

4、可以被索引,所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节 vMyISAM和InnoDB存储引擎还支持对BLOB和TEXT列的索引。当索引一个BLOB或TEXT列时，你必须为索引指定前缀长度,前缀可以达到1000字节长v只有MyISAM存储引擎支持空间类型。空间索引使用R-树 v 默认情况MEMORY(HEAP)存储引擎使用hash索引，但也支持B-树索引 vMySQL可以为多个列创建索引,一个索引可以包括15个列,规则为最左匹配原则索引介绍索引介绍索引介绍索引介绍第6页/共24页v多列索引多列索引 一个索引可以包括15个列,复合索引可以作为多个索引使用，因为索引中最左边的列

5、集合都可以用于匹配数据行。这种列集合被称为最左前缀（它与索引某个列的前缀不同，那种索引把某个列的前面几个字符作为索引值）。索引介绍索引介绍索引介绍索引介绍第7页/共24页索引介绍索引介绍索引介绍索引介绍v索引索引存储类型存储类型B+Tree点查询，范围查询Hash点查询RTree空间查询，比如对平面坐标建立R树索引Bitmap多谓词查询，Sybase的强项第8页/共24页1.1.索引介绍索引介绍2.2.2.2.如何使用索引如何使用索引如何使用索引如何使用索引3.3.索引优化索引优化4.4.SQLSQL优化优化5.5.不良不良SQLSQL集锦集锦6.6.Q&AQ&A目目 录录第9页/共24页索引

6、介绍索引介绍索引介绍索引介绍v使用索引的方式使用索引的方式 v 索引被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。v 对于使用了MIN()或MAX()函数的查询，索引数据列中最小或最大值可以很快地找到，不用检查每个数据行。v MySQL利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。v 有时候MySQL会利用索引来读取查询得到的所有信息。v 删除行。如果可以在多个索引中进行选择，MySQL通常使用找到最少行的索引 v 当执行联接时，从其它表检索行 第10页/共24页索引介绍索引介绍索引介绍索引介绍v索引的代价索引的代价首先，索引

7、加快了检索的速度，但是减慢了插入和删除的速度，同时还减慢了更新被索引的数据列中的值的速度。也就是说，索引减慢了大多数涉及写操作的速度。其次，索引会花费磁盘空间，多个索引相应地花费更多的磁盘空间。这可能导致更快地到达数据表的大小限制：v 对于MyISAM表，频繁地索引可能引起索引文件比数据文件更快地达到最大限制。v 在InnoDB的共享表空间中分配的所有表都竞争使用相同的公共空间池，因此添加索引会更快地耗尽表空间中的存储。但是，与MyISAM表使用的文件不同，InnoDB共享表空间并不受操作系统的文件大小限制，因为我们可以把它配置成使用多个文件。只要有额外的磁盘空间，你就可以通过添加新组件来扩展

8、表空间。第11页/共24页索引介绍索引介绍索引介绍索引介绍v MySQL MySQL两种主要引擎索引两种主要引擎索引不同的MySQL存储引擎的索引实现的具体细节信息是不同的。例如，对于MyISAM数据表，该表的数据行保存在一个数据文件中，索引值保存在索引文件中。一个数据表上可能有多个索引，但是它们都被存储在同一个索引文件中。索引文件中的每个索引都包含一个排序的键记录（它用于快速地访问数据文件）数组。与此形成对照的是，InnoDB存储引擎没有使用这种方法来分离数据行和索引值，尽管它们也把索引作为排序后的值集合进行操作。在默认情况下，InnoDB使用单个数据表空间（tablespace），在表空间

9、中管理所有InnoDB表的数据和索引存储。我们可以把InnoDB配置为每个表都在自己的表空间中创建，但是即使是这样，数据表的数据和索引也存储在同一个表空间文件中。第12页/共24页vMySQLMySQL两种主要引擎索引两种主要引擎索引MyISAM将行数据和索引数据保存在不同的文件中.当使用MyISAM存储引擎时，MySQL使用极快速的表锁定，以便允许多次读或一次写。使用该存储引擎的最大问题出现在同一个表中进行混合稳定数据流更新与慢速选择 InnoDB更常见的情形是索引和数据保存在一起。在这种情况下，你可以在索引的叶级页找到行的信息。该布局比较好的事情是在许多情况下，根据索引缓存得怎样，可以保存

10、一个硬盘读取。该布局的不利之处表现在：v 表扫描要慢得多，因为你必须通读索引以获得数据。v 你不能只使用表来检索查询的数据。v 你需要使用更多的空间，因为你必须从节点复制索引(你不能保存节点上的行)。v 删除要经过一段时间后才退化表(因为删除时通常不会更新节点上的索引)。v 只缓存索引数据会更加困难。索引介绍索引介绍索引介绍索引介绍第13页/共24页v如何选择索引如何选择索引v 用于索引的最好的备选数据列是那些出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列 v 索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最

11、好。惯用的百分比界线是30%。v 索引较短的值。尽可能地使用较小的数据类型。v 对于InnoDB和BDB等使用聚簇索引（clustered index）的存储引擎来说，保持主键（primary key）短小的优势更突出。v 索引字符串值的前缀（prefixe）。如果你需要索引一个字符串数据列，那么最好在任何适当的情况下都应该指定前缀长度。索引介绍索引介绍索引介绍索引介绍第14页/共24页v如何选择索引如何选择索引Hash索引还有一些其它特征：v 它们只用于使用=或操作符的等式比较(但很快)。它们用于比较 操作符，例如发现范围值的。v 优化器不能使用hash索引来加速ORDER BY操作。(该类

12、索引不能用来按顺序搜索下一个条目）。v MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索引)。如果你将一个MyISAM表改为hash-索引的MEMORY表，会影响一些查询。v 只能使用整个关键字来搜索一行。(用B-树索引，任何关键字的最左面的前缀可用来找到行）。索引介绍索引介绍索引介绍索引介绍第15页/共24页1.1.索引介绍索引介绍2.2.如何使用索引如何使用索引3.3.3.3.索引优化索引优化索引优化索引优化4.4.SQLSQL优化优化5.5.不良不良SQLSQL集锦集锦6.6.Q&AQ&A目目 录录第16页/共24页索引优化索引优化vMyISAMMyISAM索

13、引的优化索引的优化 为了帮助MySQL更好地优化查询，在一个装载数据后的表上运行ANALYZE TABLE或myisamchk-analyze。这样为每一个索引更新指出有相同值的行的平均行数的值（当然，如果只有一个索引，这总是1。）MySQL使用该方法来决定当你联接两个基于非常量表达式的表时选择哪个索引。你可以使用SHOW INDEX FROM tbl_name并检查Cardinality值来检查表分析结果。myisamchk-description-verbose可以显示索引分布信息。要想根据一个索引排序一个索引和数据，使用myisamchk-sort-index-sort-records=

14、1（如果你想要在索引1上排序）。如果只有一个索引，想要根据该索引的次序读取所有的记录，这是使查询更快的一个好方法。但是请注意，第一次对一个大表按照这种方法排序时将花很长时间！第17页/共24页索引优化索引优化vInnoDBInnoDB索引的优化索引的优化 使用使用ALTER TABLE tbl_name ENGINE=INNODBALTER TABLE tbl_name ENGINE=INNODB来重建脆片来重建脆片第18页/共24页1.1.索引介绍索引介绍2.2.索引优化索引优化3.3.如何使用索引如何使用索引4.4.4.4.SQLSQLSQLSQL优化优化优化优化5.5.不良不良SQLSQ

15、L集锦集锦6.6.Q&AQ&A目目 录录第19页/共24页v查询优化器查询优化器(EXPLAIN语法语法)vSELECT BENCHMARK(1000000,1+1)SELECT BENCHMARK(1000000,1+1)；评估机器的处理能力v借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。v如果由于使用不正确的索引出现了问题，应运行ANALYZE TABLE更新表的统计（例如关键字集的势），这样会影响优化器进行的选择。v还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个SELECT语句按照表命名顺序的联接次序，语句应以ST

16、RAIGHT_JOIN而不只是SELECT开头。索引介绍索引介绍索引介绍索引介绍第20页/共24页v优化器的行为变量优化器的行为变量优化器关于方案数量评估的行为可以通过两个系统变量来控制：v optimizer_prune_level变量告诉优化器根据对每个表访问的行数的估计跳过某些方案。我们的试验显示该类“有根据的猜测”很少错过最佳方案，并且可以大大降低查询编辑次数。这就是为什么默认情况该选项为on(optimizer_prune_level=1)。然而，如果你认为优化器错过了一个更好的查询方案，则该选项可以关闭(optimizer_prune_level=0)，风险是查询编辑花费的时间更长

17、。请注意即使使用该启发，优化器仍然可以探测呈指数数目的方案。v optimizer_search_depth变量告诉优化器对于每个未完成的“未来的”方案，应查看多深，以评估是否应对它进一步扩大。optimizer_search_depth值较小会使查询编辑次数大大减小。例如，如果optimizer_search_depth接近于查询中表的数量，对12、13或更多表的查询很可能需要几小时甚至几天的时间来编译。同时，如果用optimizer_search_depth等于3或4编辑，对于同一个查询，编译器编译时间可以少于1分钟。如果不能确定合理的optimizer_search_depth值，该变量

18、可以设置为0，告诉优化器自动确定该值。索引介绍索引介绍索引介绍索引介绍第21页/共24页索引优化索引优化v估计查询性能估计查询性能 在大多数情况下，可以通过计算磁盘搜索来估计性能。对小的表，通常能在1次磁盘搜索中找到行(因为索引可能被缓存)。对更大的表，可以使用B树索引进行估计，将需要log(row_count)/log(index_block_length/3*2/(index_length+data_pointer_length)+1次搜索才能找到行。在MySQL中，索引块通常是1024个字节，数据指针通常是4个字节，我们假设uin的索引4字节,20000000数据量log(2000000

19、0)/log(1024/3*2/(4+4)+1=4.7 大约5次搜索完成一次请求上面的索引需要大约20000000*8*3/2/1024/1024=229M(假设典型情况下索引缓存区填充率为2/3),可以将大部分索引保存在内存中，仅需要1-2调用从OS读数据来找出行。然而对于写，将需要4次搜索请求(如上)来找到在哪儿存放新索引，并且通常需要2次搜索来更新这个索引并且写入行第22页/共24页vWhere子句的优化子句的优化v去除不必要的括号：(aANDb)ANDcOR(aANDb)AND(cANDd)常量重叠(ab5ANDb=cANDa=5去除常量条件(由于常量重叠需要)(B=5ANDB=5)OR(B=6AND5=5)OR(B=7AND5=6)索引介绍索引介绍索引介绍索引介绍第23页/共24页感谢您的观看！第24页/共24页