哈尔滨网站建设科技公司,大型平面设计网站,服装公司网站模版,wordpress 提请审批创建高性能的索引
空间数据索引(R-Tree)
MyISAM表支持空间索引#xff0c;可以用作地理数据存储。和B-Tree索引不同#xff0c;这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时#xff0c;可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如…创建高性能的索引
空间数据索引(R-Tree)
MyISAM表支持空间索引可以用作地理数据存储。和B-Tree索引不同这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善所以大部分人都不会使用这个特性。开源关系数据库系统中对GIS的解决方案做得比较好的是PostgreSQL的PostGIS.
全文索引
全文索引是一种特殊类型的索引它查找的是文本中的关键词而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎作得事情而不是简单的WHERE条件匹配。 在相同的列上同时创建全文索引和基于值得B-Tree索引不会有冲突全文索引适用于MATCH AGAINST操作而不是普通得WHERE条件操作
索引的优点
索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用到目前为止可以看到根据创建索引的数据结构不同索引也有一些其他的附加作用。最常见的B-Tree索引按照顺序存储数据所有MySQL可以用来做ORDER BY 和GROUP BY操作。因为数据是有序的所以B-Tree也就会将相关的列值存储在一起。最后因为索引中存储了实际的列值所以某些查询只使用索引就能够完成全部查询。据此特性总结下来索引有如下三个优点:
1.索引大大减少了服务器需要扫描的数据量2.索引可以帮助服务器避免排序和临时表3.索引可以将随机IO变为顺序IO
索引这个主题完全值得单独写一本书如果想深入理解这部分内容强烈建议阅读由Tapio Lahdenmaki和Mike Leach编写的Relational Database Index Design and the Optimizers(Wiley出版社)一书该书详细介绍了如何计算索引的成本和作用、如何评估查询速度、如何分析索引维护的代价和其带来的好处等.Lahdenmaki和Leach在书中介绍了如何评价一个索引是否适合某个查询的三星系统(three-star system):索引将相关的记录放到一起则获得一星如果索引中的数据顺序和查找的排列顺序一致则获得二星如果索引中的列包含了查询中需要的全部列则获得三星
索引是最好的解决方案吗
索引并不总是最好的工具。总的来说只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时索引才是有效地。对于非常小的表大部分情况下简单的全表扫描更高效。对于中到大型的表索引就非常有效。但对于大型的表建立和使用索引的代价将随之增长。这种情况下则需要一种技术可以直接区分出查询需要的一组数据而不是一条记录一条记录地匹配。例如可以使用分区技术。如果表的数量特别多可以建立一个元数据信息表用来查询需要用到的某些特性。例如执行那些需要聚合多个应用分布在多个表的数据的查询则需要记录哪个用户的信息存储在哪个表中的元数据这样在查询时就可以直接忽略那些不包含指定用户信息的表。对于大型系统这是一个常用的技巧事实上Infobright就是使用类似的实现。对于TB级别的数据定位单条记录的意义不大所以经常会使用块级别元数据技术来替代索引
高性能的索引策略
正确地创建和使用索引是实现高性能查询的基础。前面已经介绍了各种类型的索引及其对应的优缺点。现在一起来看看如何真正地发挥这些索引的优势。 高效地选择和使用索引有很多种方式其中有些是针对特殊案例的优化方法有些则是针对特定行为的优化。使用哪个索引以及如何评估选择不同索引的性能影响的技巧则需要持续不断地学习。
独立的列
我们通常会看到一些查询不当地使用索引或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的则MySQL就不会使用索引。独立的列是指索引不能是表达式的一部分也不能是函数的参数。例如下面这个查询无法使用actor_id列的索引:
mysql SELECT actor_id FROM actor WHERE actor_id 1 5;凭肉眼很容易看出WHERE中的表达式其实等价于actor_id4,但是MySQL无法自动解析这个方程式。这完全是用户行为。我们应该养成简化WHERE条件的习惯始终将索引列单独放在比较符号的一侧。下面是另一个常见的错误:
mysql SELECT .... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) 10;前缀索引和索引选择性
有时候需要索引很长的字符列这会让索引变得大且慢。一个策略是前面提到过的模拟哈希索引。但有时候这样做还不够还可以做些什么呢 通常可以索引开始的部分字符这样可以大大节约索引空间从而提高索引效率。但这样也会降低索引的选择姓。索引的选择性是指不重复的所引致(也成为技术cardinality)和数据表的记录总数(#T)的比值。范围从1/#T到1之间。索引的选择性越高则查询效率越高因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1这是最好的索引选择性性能也是最好的。
一般情况下某个列前缀的选择性也是足够高的足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列必须使用前缀索引因为MySQL不允许索引这些列的完整长度。诀窍在于要选择足够长的前缀以保证较高的选择性同时又不能太长(以便节约空间)。前缀应该足够长以使得前缀索引的选择性接近于索引整个列。换句话说前缀的技术应该接近于完整列的基数。为了决定前缀的合适长度需要找到最常见的值得列表然后和最常见得前缀列表进行比较。(MySQL Sakila官方链接http://downloads.mysql.com/docs/sakila-db.zip)大家可以下载下来导入进去。这里从表city中生成一个示例表这样就有足够得数据进行演示
use sakila;
CREATE TABLE city_demo(city VARCHAR(50) NOT NULL);INSERT INTO city_demo(city) SELECT city FROM city;INSERT INTO city_demo(city) SELECT city FROM city_demo;# 执行五次UPDATE city_demo SET city (SELECT city FROM city ORDER BY RAND() LIMIT 1);现在我们有了示例数据集。数据分布当然不是真实得分布因为我们使用了RAND().所以你的结果会与此不同但对这个例子说这并不重要。首先我们找到最常见的城市列表:
mysql SELECT COUNT(*) AS cnt,city FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;
------------------
| cnt | city |
------------------
| 64 | London |
| 50 | Bagé |
| 49 | Brockton |
| 48 | Soshanguve |
| 47 | Bucuresti |
| 47 | El Alto |
| 47 | Balašiha |
| 46 | Osmaniye |
| 46 | Arecibo |
| 45 | Santo André |
------------------
10 rows in set (0.09 sec)注意到上面每隔值都出现了45~64次。现在查找结果最频繁出现的城市前缀先从前3个前缀字母开始: mysql SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;
-----------
| cnt | pref |
-----------
| 456 | San |
| 175 | Cha |
| 174 | Tan |
| 172 | Sou |
| 165 | Sal |
| 144 | al- |
| 128 | Man |
| 127 | Hal |
| 124 | Bal |
| 122 | Shi |
-----------
10 rows in set (0.08 sec)每个前缀都比原来的城市出现的次数更多因此唯一前缀比唯一城市要少得多。然后我们增加前缀长度直到这个前缀的选择性接近完整列的选择性。经过实验后发现前缀为7时比较合适:
mysql SELECT COUNT(*) AS cnt, LEFT(city, 7) AS pref FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;
--------------
| cnt | pref |
--------------
| 64 | London |
| 62 | San Fel |
| 60 | Santiag |
| 58 | Valle d |
| 50 | Bagé |
| 49 | Brockto |
| 48 | Soshang |
| 47 | El Alto |
| 47 | Bucures |
| 47 | Balaših |
--------------
10 rows in set (0.08 sec)计算合适的前缀长度的另外一个办法就是计算完整列的选择性并使前缀的选择性接近于完整列的选择性。下面显式如何计算完整列的选择性:
mysql SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo;
-------------------------------
| COUNT(DISTINCT city)/COUNT(*) |
-------------------------------
| 0.0312 |
-------------------------------
1 row in set (0.08 sec)通常来说(尽管也有例外情况)。这个例子中如何前缀的选择性能够接近0.031基本上就可以可用了。可以在一个查询中针对不同前缀长度进行计算这对于大表非常有用下面给出了如何在同一个查询中计算不同前缀长度的选择性:
mysql SELECT
COUNT(DISTINCT LEFT(city,3))/COUNT(*) AS sel3,COUNT(DISTINCT LEFT(city,4))/COUNT(*) AS sel4,
COUNT(DISTINCT LEFT(city,5))/COUNT(*) AS sel5,COUNT(DISTINCT LEFT(city,6))/COUNT(*) AS sel6,COUNT(DISTINCT LEFT(city,7))/COUNT(*) AS sel7
FROM city_demo;
----------------------------------------
| sel3 | sel4 | sel5 | sel6 | sel7 |
----------------------------------------
| 0.0237 | 0.0293 | 0.0305 | 0.0309 | 0.0310 |
----------------------------------------查询显式当前缀长度达到7的时候再增加前缀长度选择性能提升的复度已经很小了只看平均选择性使不够的也有例外的情况需要考虑最坏情况下的选择性。平均选择性会让你认为前缀长度4或者5的索引已经足够了但如果数据分布很不均匀可能就会有陷阱。如果观察前缀为4的最常出现城市的次数可以看到明显不均匀:
mysql SELECT COUNT(*) AS cnt, LEFT(city,4) AS pref FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 5;
-----------
| cnt | pref |
-----------
| 203 | San |
| 190 | Sant |
| 141 | Sout |
| 98 | Chan |
| 85 | Toul |
-----------如果前缀事4个字节则最常出现的前缀的出现次数比最常出现的城市的出现次数要大很多。即使这些值的选择性比平均选择性要地。如果有比这个随机生成的示例更真实地数据就更有可能看到这种现象。例如在真实的城市名上建一个长度为4地前缀索引对于以San和New开头地城市地选择性就会非常糟糕因为很多城市都以这两个词开头。
在上面地示例中已经找到了合适的前缀长度下面演示如何创建前缀素银:
mysql ALTER TABLE city_demo ADD KEY(city(7));前缀索引是一种使索引更小、更快的有效方法但另一方面也有其缺点:MySQL无法使用前缀索引做ORDER BY 和GROUP BY也无法使用前缀索引做覆盖扫描。一个常见的场景使针对很长的十六进制唯一ID使用前缀索引。
有时候后缀索引(suffix index)也有用途(例如找到某个域名的所有电子邮件地址)。MySQL原生不支持反向索引但是可以把字符串反转后存储并基于词建立前缀索引
