MySQL篇：查询优化

最新推荐文章于 2023-11-16 11:45:52 发布

只抓住一缕风

最新推荐文章于 2023-11-16 11:45:52 发布

阅读量415

点赞数

本文链接：https://blog.csdn.net/weixin_40214674/article/details/104267797

版权

一.什么导致查询速度变慢

在尝试编写快速的查询之前,需要清除一点,真正重要是响应时间。如果把查询看作是一个任务，那么他由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行的次数，要么让子任务运行得更快。

　　MySQL在执行查询的时候有哪些子任务。哪些子任务运行的速度很慢,这里很难给出完整的列表，通常来说查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后再服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。　　

　　在完成这些人物的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用擦欧总，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间，根据引擎不同，可能还会产生大量的上下文切换以及系统调用。

　　在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

二.优化数据库访问

最简单衡量查询开销的三个指标

响应时间：分为服务时间和排队时间

　 ①服务时间：是指数据库处理这个查询真正花了多长时间

　　②排队时间：是指服务器因为等待某些资源而没有真正执行查询的时间——坑内是等I/O操作完成，也可能使行锁等等

扫描的行数
返回的行数

　　在EXPLAIN语句中的type列反应了访问的类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描的行数也是小到大。你不需要记住这些访问类型，但是要明白扫描表，扫描索引，范围访问和单值访问的概念。如果查询没有办法找到合适的访问类型，那么最好的办法通常就是增加一个合适的索引。

一般MySQL能够使用如下三种应用WHERE条件，从好到坏一次为：

在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
使用索引覆盖扫描来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须在回表查询记录。
从数据表中返回数据，然后过滤不满足条件的记录。这是在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。

如果说发现查询需要扫描大量的数据但只返回少数的行，通常可以尝试下面的技巧去优化它：

使用索引覆盖扫描，把所有需要用到的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果
改变库表结构。例如使用单独的汇总表
重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询

3.重构查询方式

　　3.1 一个复杂查询还是多个简单查询

　　3.2 切分查询：将大查询切分成小查询，每个查询完全一样，只完成一小部分，每次只返回一小部分查询结果。

　　3.3 分解关联查询

　　　　　　对每一个表进行一次单表查询，然后再应用程序中进行关联，例如

SELECT * FROM tag

JOIN tag_post ON tag_post.tag_id=tag.id

JOIN post ON tag_post.post_id=post.id

WHERE tag.tag='mysql';

　　　　　　可以分解成下面的语句来代替　　

1 2	`SELECT` `` `FROM` `tag` `WHERE` `tag='mysql';` `SELECT` `` `FROM` `tag_post` `WHERE` `tag_id=1234;` `SELECT` `*` `FROM` `post` `WHERE` `post.id` `in` `(123,456,567,9098,8904);`

使用分解关联查询的方式重构查询有如下的优化：

让缓存的效率更高。
这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。　　　　　
可以减少冗余记录的查询。
查询本身效率也可能会有所提升。
在应用层做关联，可以更容易的对数据库进行拆分，更容易做到高性能和可扩展。
将查询分解后，执行单个查询可以减少锁的竞争。

四.查询执行的基础

　　　　查询执行路径

　　　　　步骤：

　　　　　　　　（1）客服端发送一条查询给服务器

　　　　　　　　（2）服务器先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一个阶段。

　　　　　　　　（3）服务器端进行SQL解析、预处理，在由优化器生成对应的执行计划。

　　　　　　　　（4）MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询

　　　　　　　　（5）将结果返回给客户端

　　4.1 MySQl客户端/服务器通信协议　　

　　　　（1）MySQL客户端和服务器之间的通讯是”双半工“的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。

　　　　（2）查询状态：对于一个MySQL连接，或者说一个线程，任何时刻都有一个状态，表示MySQL当前在做什么。我们使用最简单的SHOW FULL PROCESSLIST命令（该命令返回结果中的Command列就表示当前的状态）来查询。下面将这些状态列出来，并做一个简单的解释：　

　　　　　　a. Sleep：线程正在等待客户端发送新的请求。

　　b. Query：线程正在执行查询或者正在将结果发送给客户端。

　　c. Locked：在MySQL服务器层，该线程正在等待表锁。

　　 d. Analyzing and statistics : 线程正在收集存储引擎的统计信息，并生成查询的执行计划。

　　e. Coping to tmp table [on disk]：线程正在执行查询，并且将其结果都复制到一个临时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面

　　还有"on disk"标记，那表示MySQL正在讲一个内存临时表放到磁盘上。

　　f. Sorting result：线程正在对结果集进行排序。

　　g. Sending data：这表示多种情况：线程可能在对多个状态之间传输数据，或者而在生成结果集，或者在向客户端返回数据。

　 4.2 查询缓存

　　　（1）在解析一个查询语句之前，如果查询缓存是打开的，那么MYSQL会优先检查这个查询是否命中查询缓存中的数据。

　　　（2）这个检查是通过一个对大小写敏感的哈希查找的。查询和缓存中的查询即使只有一个不同，也不会匹配缓存结果。

　　　（3）如果命中缓存，那么在但会结果前MySQL会检查一次用户权限，有权限则跳过其他步骤直接返回数据

　　4.3 查询优化处理

　　　　　　查询的生命周期的下一步是将一个SQL转换成执行计划，MySQL再依照这个执行计划和存储引擎进行交互。

　　　　4.3.1 语法解析器和预处理

　　　　　　　MySQL解析器将使用MySQL语法规则验证和解析查询。例如验证是否使用错误的关键字、关键字顺序、引号前后是否匹配等

　　　　　　　预处理器则根据一些MySQL 规则进一步解析树是否合法，例如检查数据表和数据列是否存在，解析名字和别名是否有歧义等

　　　　4.3.2 查询优化器

　　　　　　　一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到其中最好的执行计划

　　　　　　　有很多中原因导致MySQL优化器选择错误的计划，如下所示：

- - 统计信息不准确：MySQL依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息偏差有点大，例如InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息
  - 执行计划中的成本估算不等于实际的操作成本
  - MySQL的最优可能和你想的最优不一样
  - MySQL从不考虑其他并发执行的查询
  - MySQL也并不是任何时候都是基于成本的优化
  - MySQL不会考虑不受其控制的操作成本。例如执行存储过程或者用户自定义函数的成本
  - 优化器有时间无法估算所有可能的执行计划

　　　　　　MySQL的查询优化器使用很多策略来生成一个最优的执行计划。

　　　　　　优化策略可以简单的分为两种

　　　　　　静态优化：静态优化可以直接对解析树进行分析，并完成优化。例如优化器可以通过简单的代数变化将WHERE条件转换成另外一种等价形式，静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会变化。可以认为是一种”编译时优化“

　　　　　　动态优化：和查询的上下文有关，也可能和其他因素有关，例如WHERE中取值、索引中条目对应的数据行数等。这需要在每次查询的时候重新评估，可以让那位u是”运行时优化“。

　　　　　　MySQL能够处理的优化类型：（部分）

- - 重新定义关联表顺序
  - 将外连接转化成内连接
  - 使用等价变换规则
  - 优化COUNT() 、MIN() 、 MAX()
  - 预估并转换为常数表达式
  - 覆盖索引扫描
  - 子查询优化
  - 提前终止查询
  - 等值传播
  - 列表IN()的比较

　　　　4.3.3 数据和索引的统计信息

　　　　　　在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息，有的引擎根本不存储任何统计信息，例如Archive引擎。

　　　　　　因为服务器层没有任何统计信息，所有MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息，优化器根据这些信息来选择一个最优的执行计划。

　　　　4.3.4 MySQL如何执行关联查询

　　　　　　　　MySQL中“关联”认为任何一个查询都是一次“关联”，并不仅仅是一个查询需要到两个表匹配才叫关联。素以在MySQL中，每一个查询，每一个片段（包括子查询，甚至于单表的SELECT）都可能是关联。

　　　　　　　　MySQL关联查询的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在要给表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表的行，返回查询中需要的各个列。

　　　　4.3.5 执行计划

　　　　　　和很多其他关系数据库不同，MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一颗指令树，然后通过存储引擎执行完成这颗树并返回结果

　　　　4.3.6 关联查询优化器

　　　　　　如果优化器给出的并不是最优的关联顺序，这时可以使用STRAIGHT_JOIN关键字重写查询，让优化器按照你认为最优的关联顺序执行——不过老实说，人的判断很难那么精准。绝大多数时候，优化器做出的选择都比普通人的判断更精准。

　　　　　　如果超过N个表的关联，那么需要检查N的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间‘，当搜索空间非常大的时候，优化器选择使用”贪婪“搜索方式查找”最优’的关联顺序。当关联的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式了。

　　　　4.3.7 排序优化　　　

　　　　　　排序优化：无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。尽量通过索引进行排序。当不能使用索引生成排序结果的时候，MySQL需要自己

　　进行排序，如果数据量小则在内存中进行，如果数量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序，即使完全是内存排序不需要任何磁盘文件时也是如此。

　　 MySQL有如下两种排序算法：

　　a. 两次传输排序(旧版本使用)：读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。需要进行两次传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读

　　取排序列进行排序后的所有记录。这回产生大量的随机IO。

　　b. 单次传输排序(新版本使用)：先读取查询所需要的所有列，然后在根据给定列进行排序，最后直接返回排序结果。效率更高，但占用内存更大。

　　如果查询中有LIMIT的话，LIMIT也会在排序之后应用的，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然后非常大。貌似5.6版本有所改进，会先抛弃不满足条件的记录，然后再进行排序。

　　4.4 查询执行引擎　

　　　　在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样会生成对应的字节码。

　　4.5 返回结果给客户端

　　　　即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如查询影响到的行数。如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到缓存中。

　　　　MySQL将结果集返回客户端是一个增量、逐步返回的过程。开始生成第一条结果时，MySQL就开始向客户端逐步返回结果集了。

五. MySQL查询优化器的局限性：

5.1 关联子查询：MySQL的子查询实现非常糟糕(5.6版本以后有改进)。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。

1). 因为使用IN()加子查询，性能经常会非常糟，所以通常建议使用EXISTS()等效的改写查询来获取更好的效率。

2). 一般建议使用左外连接(LEFT OUTER JOIN)代替子查询(?)。

5.2 UNION的限制：MySQL无法将限制条件从外层"下推"到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

例如如果希望UNION的各个子句能够根据LIMIT只去部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION的各个子句中分别使用这些语句。

(SELECT first_name,last_name FROM sakila.actor ORDER BY last_name) UNION ALL (SELECT first_name ,last_name FROM sakila.customer ORDER BY last_name) LIMIT 20;

优化后：

(SELECT first_name,last_name FROM sakila.actor ORDER BY last_name LIMIT 20) UNION ALL (SELECT first_name ,last_name FROM sakila.customer ORDER BY last_name LIMIT 20) LIMIT 20;

5.3 当WHERE子句包含多个复杂条件的时候，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

5.4 等值查询：某些时候，等值查询会带来一些意想不到额外消耗。例如：有一个非常大的IN()列表，而MySQ优化器发现存在WHERE、ON或者USING的子句。

5.5 并行执行：MySQL无法利用多核特性来并行执行查询(貌似5.6以后有改进)。

5.6 哈希关联：MySQL不支持哈希关联。

5.7 松散索引扫描：MySQL并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。通常，MySQL的索引扫描需要先定义一个起点和终点，即使需要的数据只是这段索引中的很少几个，MySQL仍需扫描这段索引中

的每一个条目。

5.8 最大值和最小值优化：对于MIN()和MAX()查询，MySQL的优化做的并不好。例如：

SELECT MIN(actor_id) FROM sakila.actor WHERE first_name='PENELOPE'

因为first_name上没有索引，所以会进行全表扫描。如果MySQL能够进行主键扫描，那么理论上，当MySQL读到第一个满足条件的记录的时候，就是我们需要的最小值，因为主键是严格按照actor_id大小字段排序的。

一个曲线优化的办法是移除MIN(),然后使用LIMIT来将查询重写。

5.9 在同一个表上查询和更新：MySQL不允许对同一张表同时进行查询和更新。

六. 查询优化器的提示(hint)：

　　如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示(hint)来控制最终的执行计划。

七. 优化特定类型的查询

7.1 优化COUNT()查询

1). COUNT()是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值的数量，也可以统计行数。在统计列值的时候要求列值是非空的(不统计NULL)。如果COUNT()的括号中指定了列或者列的表达式，则

统计的就是这个表达式有值的结果数。最简单的就是我们使用count(*)的时候，这种情况下通配符*并不会向我们猜想的那样扩展所有的行，实际上，它会忽略所有的值而直接统计所有的行数。

2). 使用近似值：有时候某些业务场景并不要求完全精确的COUNT值，此时可以用近似值来代替。

3). 更复杂的优化：覆盖索引，增加汇总表等。

7.2 优化关联查询：

1). 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用到列C关联的时候，如果优化器关联顺序是B、A，那就不需要在B表的对应列上建立索引。没有用到的索引只会

带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。

2). 确保任何的GROUP BY 和ORDER BY中的表达式只涉及到一个表中的列。这样MySQL才有可能使用索引来优化这个过程。

7.3 优化子查询：关于优化子查询我们给出的最重要的优化建议就是尽可能使用关联查询代替，至少当前MySQL版本需要这样。

7.4 优化GROUP BY和DISTINCT：

1). 它们都可以使用索引来优化，这也是最有效的方法。

2). 在MySQL中，当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或文件排序来做分组。对于任何查询语句，这两种策略的性能都有可以提升的地方。可以通过使用提示SQL_BIG_RESULT和

SQL_SMALL_RESULT来让优化器按你希望的方式运行。

3). 如果需要对关联查询分组(GROUP BY)，并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率比其他列更高。

4). 如果没有通过ORDER BY子句显式地指定排序列，当查询使用GROUP BY 子句的时候，结果集会自动按照分组的列进行排序。如果不关心结果集的顺序，而这中默认排序又导致了需要文件排序，则可以使用

ORDER BY NULL，让MySQL文件不再进行排序。也可以在GROUP BY子句中直接使用DESC或者ASC关键字，使分组的结果集按照需要的方向排序。

5). 优化GROUP BY WITH ROLLUP：分组查询的一个变种思想就是要求MySQL对返回的分组结果再做一次超级聚合。最好的办法尽可能的将WITH ROLLUP 功能转移到应用程序中处理。

7.5 优化LIMIT分页：

1). 使用索引

2). 要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

3). 尽肯能的使用索引覆盖

4). 延迟关联

5). 有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描找到对应的结果。

6). 其他优化办法还包括使用预先计算的汇总表，或者关联一个冗余表，冗余表只包含主键列和需要做排序的数据列。

7.6 优化SQL_CALC_FOUND_ROWS：分页的时候，另一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示(hint)，这样就可以获得去掉LIMIT以满足条件的行数，因此可以作为分页的总数。

用业务的手段解决：下一页，获取更多数据等。

7.7 优化UNION查询：

1). MySQL总是通过创建填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE,LIMIT,ORDER BY等子句"下推"到UNION的各个子查询中，以

便优化器可以充分利用这些条件进行优化。

2). 除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这回导致对临时表做唯一性检查。这样做的代价非常高，

即使有ALL关键字，MySQL仍然会使用临时表存储结果。事实上，MySQL总是经结果放入临时表，然后再读出，再返回给客户端。

7.8 静态查询分析：Percona Toolkit中的pt-query-advisor 能够解析查询日志、分析查询模式，然后再给出所有可能存在的潜在问题的查询，并给出足够详细的建议。这像是给MySQL所有的查询做一次全面的健康

检查，它能检测出很多问题

八.什么是索引

所谓索引就是为特定的mysql字段进行一些特定的算法排序，比如二叉树的算法和哈希算法，哈希算法是通过建立特征值，然后根据特征值来快速查找。而用的最多，并且是mysql默认的就是二叉树算法 BTREE，通过BTREE算法建立索引的字段，比如扫描20行就能得到未使用BTREE前扫描了2^20行的结果。

Explain优化查询检测

EXPLAIN可以帮助开发人员分析SQL问题，explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上Explain就可以了：

Explain select * from blog where false;

mysql在执行一条查询之前，会对发出的每条SQL进行分析，决定是否使用索引或全表扫描如果发送一条select * from blog where false，Mysql是不会执行查询操作的，因为经过SQL分析器的分析后MySQL已经清楚不会有任何语句符合操作。

实例

mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2"; 
-- 结果： 
id: 1 

select_type: SIMPLE -- 查询类型（简单查询、联合查询、子查询） 

table: user -- 显示这一行的数据是关于哪张表的 。

type: range -- 区间索引（在小于1990/2/2区间的数据)，这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中，ALL代表扫描了全表才确定结果。一般来说，得保证查询至少达到range级别,最好能达到ref。 

possible_keys: birthday  -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。  

key: birthday -- 实际使用到的索引。如果为NULL，则没有使用索引。如果为primary的话，表示使用了主键。 

key_len: 4 -- 最长的索引宽度。如果键是NULL，长度就是NULL。在不损失精确性的情况下，长度越短越好。

ref: const -- 显示哪个字段或常数与key一起被使用。  

rows: 1 -- 这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。 

Extra: Using where; Using index -- 执行状态说明，这里可以看到的坏的例子是Using temporary和Using

select_type

simple:简单select（不使用union或子查询）。
primary:最外面的select。
union:union中的第二个或后面的select语句。
dependent union:union中的第二个或后面的select语句，取决于外面的查询。
union result:union的结果。
subquery:子查询中的第一个select。
dependent subquery:子查询中的第一个select，取决于外面的查询。
derived:导出表的select（from子句的子查询）。

其它说明

Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了。
Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
Range checked for each Record(index map:#):没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。
Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）。
system 表只有一行：system表。这是const连接类型的特殊情况。
const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待。
eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。
ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于索引匹配的记录多少，越少越好。
range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。
index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。
ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。

其中type:

如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。
如果是where used，就是使用上了where限制。
如果是impossible where 表示用不着where，一般就是没查出来啥。
如果此信息显示Using filesort或者Using temporary的话会很吃力，WHERE和ORDER BY的索引经常无法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。

索引的类型

UNIQUE唯一索引

不可以出现相同的值，可以有NULL值。

INDEX普通索引

允许出现相同的索引内容。

PRIMARY KEY主键索引

不允许出现相同的值，且不能为NULL值，一个表只能有一个primary_key索引。

fulltext index 全文索引

上述三种索引都是针对列的值发挥作用，但全文索引，可以针对值中的某个单词，比如一篇文章中的某个词，然而并没有什么卵用，因为只有myisam以及英文支持，并且效率让人不敢恭维，但是可以用coreseek和xunsearch等第三方应用来完成这个需求。

索引的CURD

索引的创建

ALTER TABLE

适用于表创建完毕之后再添加。

ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名，可要可不要；如果不要，当前的索引名就是该字段名。 
ALTER TABLE `table_name` ADD UNIQUE (`column_list`) 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`) 
ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)

CREATE INDEX

CREATE INDEX可对表增加普通索引或UNIQUE索引。

--例：只能添加这两种索引 
CREATE INDEX index_name ON table_name (column_list) 
CREATE UNIQUE INDEX index_name ON table_name (column_list)

另外，还可以在建表时添加：

CREATE TABLE `test1` ( 
  `id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 注意，下面创建了主键索引，这里就不用创建了 
  `username` varchar(64) NOT NULL COMMENT '用户名', 
  `nickname` varchar(50) NOT NULL COMMENT '昵称/姓名', 
  `intro` text, 
  PRIMARY KEY (`id`),  
  UNIQUE KEY `unique1` (`username`), -- 索引名称，可要可不要，不要就是和列名一样 
  KEY `index1` (`nickname`), 
  FULLTEXT KEY `intro` (`intro`) 
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='后台用户表';

索引的删除

DROP INDEX `index_name` ON `talbe_name`  
ALTER TABLE `table_name` DROP INDEX `index_name` 
-- 这两句都是等价的,都是删除掉table_name中的索引index_name; 

ALTER TABLE `table_name` DROP PRIMARY KEY -- 删除主键索引，注意主键索引只能用这种方式删除

索引的查看

show index from tablename;

索引的更改

更改个毛线,删掉重建一个既可

创建索引的技巧

维度高的列创建索引。
- 数据列中不重复值出现的个数，这个数量越高，维度就越高。
- 如数据表中存在8行数据a,b ,c,d,a,b,c,d这个表的维度为4。
- 要为维度高的列创建索引，如性别和年龄，那年龄的维度就高于性别。
- 性别这样的列不适合创建索引，因为维度过低。
对 where,on,group by,order by 中出现的列使用索引。
对较小的数据列使用索引，这样会使索引文件更小，同时内存中也可以装载更多的索引键。
为较长的字符串使用前缀索引。
不要过多创建索引，除了增加额外的磁盘空间外，对于DML操作的速度影响很大，因为其每增删改一次就得从新建立索引。
使用组合索引，可以减少文件索引大小，在使用时速度要优于多个单列索引。

组合索引与前缀索引

注意，这两种称呼是对建立索引技巧的一种称呼，并非索引的类型。

组合索引

MySQL单列索引和组合索引究竟有何区别呢？

为了形象地对比两者，先建一个表：

CREATE TABLE `myIndex` ( 
  `i_testID` INT NOT NULL AUTO_INCREMENT,  
  `vc_Name` VARCHAR(50) NOT NULL,  
  `vc_City` VARCHAR(50) NOT NULL,  
  `i_Age` INT NOT NULL,  
  `i_SchoolID` INT NOT NULL,  
  PRIMARY KEY (`i_testID`)  
);

假设表内已有1000条数据，在这 10000 条记录里面 7 上 8 下地分布了 5 条 vc_Name=”erquan” 的记录，只不过 city,age,school 的组合各不相同。来看这条 T-SQL：

SELECT `i_testID` FROM `myIndex` WHERE `vc_Name`='erquan' AND `vc_City`='郑州' AND `i_Age`=25; -- 关联搜索;

首先考虑建MySQL单列索引：

在 vc_Name 列上建立了索引。执行 T-SQL 时，MYSQL 很快将目标锁定在了 vc_Name=erquan 的 5 条记录上，取出来放到一中间结果集。在这个结果集里，先排除掉 vc_City 不等于”郑州”的记录，再排除 i_Age 不等于 25 的记录，最后筛选出唯一的符合条件的记录。虽然在 vc_Name 上建立了索引，查询时MYSQL不用扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样的,在 vc_City 和 i_Age 分别建立的MySQL单列索引的效率相似。

为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。就是将 vc_Name,vc_City,i_Age 建到一个索引里：

ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age);

建表时，vc_Name 长度为 50，这里为什么用 10 呢？这就是下文要说到的前缀索引，因为一般情况下名字的长度不会超过 10，这样会加速索引查询速度，还会减少索引文件的大小，提高 INSERT 的更新速度。

执行 T-SQL 时，MySQL 无须扫描任何记录就到找到唯一的记录！

如果分别在 vc_Name,vc_City,i_Age 上建立单列索引，让该表有 3 个单列索引，查询时和上述的组合索引效率一样吗？答案是大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引，另外两个是用不到的，也就是说还是一个全表扫描的过程。

建立这样的组合索引，其实是相当于分别建立了：

vc_Name,vc_City,i_Age
vc_Name,vc_City
vc_Name

这样的三个组合索引！为什么没有 vc_City,i_Age 等这样的组合索引呢？这是因为 mysql 组合索引 “最左前缀” 的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个 T-SQL 会用到：

SELECT * FROM myIndex WHREE vc_Name=”erquan” AND vc_City=”郑州” SELECT * FROM myIndex WHREE vc_Name=”erquan”

而下面几个则不会用到：

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City=”郑州” SELECT * FROM myIndex WHREE vc_City=”郑州”

也就是，name_city_age(vc_Name(10),vc_City,i_Age) 从左到右进行索引，如果没有左前索引Mysql不执行索引查询。

前缀索引

如果索引列长度过长，这种列索引时将会产生很大的索引文件，不便于操作，可以使用前缀索引方式进行索引前缀索引应该控制在一个合适的点，控制在0.31黄金值即可（大于这个值就可以创建）。

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; — 这个值大于0.31就可以创建前缀索引，Distinct去重复 ALTER TABLE `user` ADD INDEX `uname`(title(10)); — 增加前缀索引SQL，将人名的索引建立在10，这样可以减少索引文件大小，加快索引查询速度。

什么样的sql不走索引

要尽量避免这些不走索引的sql

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引，因为所有索引列参与了计算 

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引，因为使用了函数运算，原理与上面相同 

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引 

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引 

-- 正则表达式不使用索引，这应该很好理解，所以为什么在SQL中很难看到regexp关键字的原因 

-- 字符串与数字比较不使用索引; 
CREATE TABLE `a` (`a` char(10)); 
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引 
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引 

select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引，我们建议大家尽量避免使用or 关键字 

-- 如果mysql估计使用全表扫描要比使用索引快，则不使用索引

多表关联时的索引效率

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; — 不会使用索引，因为使用了函数运算，原理与上面相同
SELECT * FROM `houdunwang` WHERE `uname` LIKE’后盾%’ — 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE “%后盾%” — 不走索引

从上图可以看出，所有表的type为all，表示全表索引。也就是6 6 6，共遍历查询了216次。

除第一张表示全表索引（必须的，要以此关联其他表），其余的为range（索引区间获得），也就是6+1+1+1，共遍历查询9次即可。

所以我们建议在多表join的时候尽量少join几张表，因为一不小心就是一个笛卡尔乘积的恐怖扫描，另外，我们还建议尽量使用left join，以少关联多。因为使用join 的话，第一张表是必须的全扫描的，以少关联多就可以减少这个扫描次数。

索引的弊端

不要盲目的创建索引，只为查询操作频繁的列创建索引，创建索引会使查询操作变得更加快速，但是会降低增加、删除、更新操作的速度，因为执行这些操作的同时会对索引文件进行重新排序或更新。

但是，在互联网应用中，查询的语句远远大于DML的语句，甚至可以占到80%~90%，所以也不要太在意，只是在大数据导入时，可以先删除索引，再批量插入数据，最后再添加索引。

九. 如何使用索引进行优化

作为免费又高效的数据库，mysql基本是首选。良好的安全连接，自带查询解析、sql语句优化，使用读写锁（细化到行）、事物隔离和多版本并发控制提高并发，完备的事务日志记录，强大的存储引擎提供高效查询（表记录可达百万级），如果是InnoDB，还可在崩溃后进行完整的恢复，优点非常多。即使有这么多优点，仍依赖人去做点优化，看书后写个总结巩固下，有错请指正。

　　完整的mysql优化需要很深的功底，大公司甚至有专门写mysql内核的，sql优化攻城狮，mysql服务器的优化，各种参数常量设定，查询语句优化，主从复制，软硬件升级，容灾备份，sql编程，需要的不是一星半点的知识与时间来掌握，作为一名像俺这样的菜鸟开发，强吃这么多消化不了也没意义：没地儿用啊，况且还有运维和dba，还不如把手头的业务写好，也就是写好点的sql，而且很多sql语句优化跟索引还是有很大关系的。

　　首先，mysql的查询流程大致是：mysql客户端通过协议与mysql服务器建立连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析，有一系列预处理，比如检查语句是否写正确了，然后是查询优化（比如是否使用索引扫描，如果是一个不可能的条件，则提前终止），生成查询计划，然后查询引擎启动，开始执行查询，从底层存储引擎调用API获取数据，最后返回给客户端。怎么存数据、怎么取数据，都与存储引擎有关。然后，mysql默认使用的BTREE索引，并且一个大方向是，无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。

　　mysql通过存储引擎取数据，自然跟存储引擎有很大关系，不同的存储引擎索引也不一样，如MyISAM的全文索引，即便索引叫一个名字内部组织方式也不尽相同，最常用的当然就是InnoDB了（还有完全兼容mysql的MariaDB，它的默引擎是XtraDB，跟InnoDB很像），这里写的是InnoDB引擎。而索引的实现也跟存储引擎，按照实现方式分，InnoDB的索引目前只有两种：BTREE索引和HASH索引。通常我们说的索引不出意外指的就是B树索引，InnoDB的BTREE索引，实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引。至于B树与B+树的区别，原谅的俺数据结构没好好学，也是需要补的地方。

　　使用了BTREE索引，意味着所有的索引是按顺序排列存储的（升序），mysql就是这么干的，mysl中的BTREE索引抽象结构如下图（参考高性能mysql）。

　　结构中，每一层节点均从左往右从小到大排列，key1 < key2 < ... < keyN，对于小于key1或在[key1，key2)或其他的值的节点，在进入叶子节点查找，是一个范围分布，同时，同一层节点之间可直接访问，因为他们之间有指针指向联系（MyISAM的BTREE索引没有）。每次搜索是一个区间搜索，有的话就找到了，没有的话就是空。索引能加快访问速度，因为有了它无需全表扫描数据（不总是这样），根据查找的值，跟节点中的值比较，通常使用二分查找，对于排好序的数值来说，平均速度几乎是最快的。

　　val指向了哪里，对于InnoDB，它指向的就是表数据，因为InnoDB的表数据本身就是索引文件，这是与MyISAM索引的显著区别，MyISAM的索引指向的是表数据的地址（val指向的是类似于0x7DFF..之类）。比如对于InnoDB一个主键索引来说，可能是这样

　　InnoDB的索引节点val值直接指向表数据，即它的叶子节点就是表数据，它们连在一起，表记录行没有再单独放在其他地方，叶子节点（数据）之间可访问。

　　前面在BTREE的抽象结构中，索引值的节点是放在页中的，这里有两个需注意的问题：

　　1. 叶子页、页中的值（上上图），即所谓的页是啥，俺加了个节点注释，即这里的页最小可近似当做是单个节点。我们知道计算机的存储空间是一块一块的，通常一块用完了再用另一块，如果上一块只剩余5kb，但这里刚好要申请8kb的空间，就得在一个新的块上申请这个空间，然后以后的申请又接在这个8kb后面，只要这个块的空间足够，那么上一块的5kb通常就成了所谓的“碎片”，电脑用多了会有很多这样零散的碎片空间，因此有碎片整理。在mysql中，这里的页可理解为块存储空间，即索引的树节点是存放在页中的，每一页（称为逻辑页）有固定大小，InnoDB目前是16kb，一页用完了，当继续插入表生成新的索引节点时，就去新的页中存储这个节点，再有新的节点就继续放在这个新的页的节点后面。

　　2. 页分裂问题，一页总要被存满，然后新开一页继续，这种行为被称作页分裂。何时开辟新的页，mysql规定了一个分裂因子，达到页存储空间的15/16则存到下一页。页分裂的存在可能极大影响性能维护索引的性能。通常提倡的是，设定一个无意义的整数自增索引，有利于索引存储

　　如果非自增或不是整数索引，如非自增整数、类似MD5的字符串，以他们作为索引值时，因为待插入的下一条数据的值不一定比上一条大，甚至比当前页所有值都小，需要跑到前几页去比较而找到合适位置，InnoDB无法简单的把新行插入到上一行后面，而找到并插入索引后，可能导致该页达到分裂因子阀值，需要页分裂，进一步导致后面所有的索引页的分裂和排序，数据量小也许没什么问题，数据量大的话可能会浪费大量时间，产生许多碎片。

　　主键总是唯一且非空，InnoDB自动对它建立了索引（primary key），对于非主键字段上建立的索引，又称辅助索引，索引排列也是顺序排列，只是它还附带一个本条记录的主键值的数据域，不是指向本数据行的指针，在使用辅助索引查找时，先找到对应这一列的索引值，再根据索引节点上的另一个数据域---主键值，来查找该行记录，即每次查找实际经过查找了两次。额外的数据域存储主键值的好处是，当页分裂发生时，无需修改数据域的值，因为即使页分裂，该行的主键值是不变的，而地址就变了。比如name字段的索引简示如下　

　　包含一列的索引称为单列索引，多列的称为复合索引，因为BTREE索引是顺序排列的，所以比较适合范围查询，但是在复合索引中，还应注意列数目、列的顺序以及前面范围查询的列对后边列的影响。

　　比如有这样一张表

create table staffs(

id int primary key auto_increment,

name varchar(24) not null default '' comment '姓名',

age int not null default 0 comment '年龄',

pos varchar(20) not null default '' comment '职位',

add_time timestamp not null default current_timestamp comment '入职时间'

) charset utf8 comment '员工记录表';

　　添加三列的复合索引

1	`alter` `table` `staffs` `add` `index` `idx_nap(name, age, pos);`

　　在BTREE索引的使用上，以下几种情况可以用到该索引或索引的一部分（使用explain简单查看使用情况）：

　　1. 全值匹配

　　如select * from staffs where name = 'July' and age = '23' and pos = 'dev' ，key字段显示使用了idx_nap索引

　　2. 匹配最左列，对于复合索引来说，不总是匹配所有字段列，但是可以匹配索引中靠左的列

　　如select * from staffs where name = 'July' and age = '23'，key字段显示用到了索引，注意，key_len字段（表示本次语句使用的索引长度）数值比上一条小了，意思是它并未使用全部索引列（通常这个长度可估摸着用了哪些索引列，埋个坑），事实上只用到了name和age列

　　再试试select * from staffs where name = 'July'，它也用了索引，key_len值更小，实际只用到了索引中的name列

　　3. 匹配列前缀，即一个索引中列的前一部分，主要用在模糊匹配，如select * fromstaffs where name like 'J%'，explain信息的key字段表示使用了索引，但是mysql的B树索引不能非列前缀的模糊匹配，如select * from staffs where name like '%y' 或者 like '%u%'，据说是由于底层存储引擎的API限制

　　4. 匹配范围，如select * from staffs where name > 'Mary'，但俺在测试时发现>可以，>=却不行，至少在字符串列上不行（测试mysql版本5.5.12），然而在时间类型（timestamp）上却可以，不测试下还真不能确定说就用到了索引==

　　出于好奇测了下整型字段的索引（idx_cn(count, name)，count为整型），发现整型受限制少很多，下面的都能用到索引，连前模糊匹配的都行

select * from indexTest1 where count > '10'

select * from indexTest1 where count >= '10'

select * from indexTest1 where count > '10%'

select * from indexTest1 where count >= '10%'

select * from indexTest1 where count > '%10%'

select * from indexTest1 where count >= '%10%'

　　5. 精确匹配一列并范围匹配右侧相邻列，即前一列是固定值，后一列是范围值，它用了name与age两个列的索引（key_len推测）

　　如select * from staffs where name = 'July' and age > 25

　　6. 只访问索引的查询，比如staffs表的情况，索引建立在(name,age,pos)上面，前面一直是读取的全部列，如果我们用到了哪些列的索引，查询时也只查这些列的数据，就是只访问索引的查询，如

1 2	`select` `name,age,pos` `from` `staffs` `where` `name` `=` `'July'` `and` `age = 25` `and` `pos =` `'dev'` `select` `name,age` `from` `staffs` `where` `name` `= July` `and` `age > 25`

　　第一句用到了全部索引列，第二句只用了索引前两列，select的字段就最多只能是这两列，这种查询情况的索引，mysql称为覆盖索引，就是索引包含（覆盖）了查询的全部字段。是不是用到了索引查询，在explain中需要看最后一个Extra列的信息，Using index表明使用了覆盖索引，同时Using where表明也使用了where过滤

　　7. 前缀索引

　　区别于列前缀（类似like 'J%'形式的模糊匹配）和最左列索引（顺序取索引中靠左的列的查询），它只取某列的一部分作为索引。通常在说InnoDB跟MyISAM的区别时，一个明显的区别是：MyISAM支持全文索引，而InnoDB不行，甚至对于text、blob这种超长的字符串或二进制数据时，MyISAM会取前多少个字符作为索引，InnoDb的前缀索引跟这个类似，某些列，一般是字符串类型，很长，全部作为索引大大增加存储空间，索引也需要维护，对于长字符串，又想作为索引列，一个可取的办法就是取前一部分（前缀），代表一整列作为索引串，问题是：如何确保这个前缀能代表或大致代表这一列？所以mysql中有个概念是索引的选择性，是指索引中不重复的值的数目（也称基数）与整个表该列记录总数（#T）的比值，比如一个列表（1,2,2,3），总数是4，不重复值数目为3，选择性为3/4，因此选择性范围是[1/#T, 1]，这个值越大，表示列中不重复值越多，越适合作为前缀索引，唯一索引（UNIQUE KEY）的选择性是1。

　　比如有一列a varchar(255)，以它作前缀索引，比如以7个测试，逐个增加看看选择性值增长到那个数基本不变，就表示可以代表整列了，再结合这个长度的索引列是否存储数据太多，做个权衡，基本就行了。但如果这个选择性本来就小的可怜还是算了

1	`select` `count(distinct` `left(a, 7))/count(*)` `as` `non_repeat` `from` `tab;`

　　定好一个前缀数目，如9，添加索引时可以这样

1 2	`alter` `table` `tab` `add` `index` `idx_pn(name(9))` `--单独前缀索引` `alter` `table` `tab` `add` `index` `idx_cpn(count,` `name(9))` `--复合前缀索引`

　　以上为常见的使用索引的方式，有这么些情况不能用或不能全用，有的就是上面情况的反例，以key(a, b, c)为例

　　1. 跳过列，where a = 1 and c = 3，最多用到索引列a；where b = 2 and c = 3，一个也用不到，必须从最左列开始

　　2. 前面是范围查询，where a = 1 and b > 2 and c = 3，最多用到 a, b两个索引列；

　　3. 顺序颠倒，where c = 3 and b = 2 and a = 1，一个也用不到；

　　4. 索引列上使用了表达式，如where substr(a, 1, 3) = 'hhh'，where a = a + 1，表达式是一大忌讳，再简单mysql也不认。有时数据量不是大到严重影响速度时，一般可以先查出来，比如先查所有有订单记录的数据，再在程序中去筛选以'cp1001'开头的订单，而不是写sql过滤它；

　　5. 模糊匹配时，尽量写 where a like 'J%'，字符串放在左边，这样才可能用得到a列索引，甚至可能还用不到，当然这得看数据类型，最好测试一下。

　　排序对索引的影响

　　order by是经常用的语句，排序也遵循最左前缀列的原则，比如key(a, b)，下面语句可以用到（测试为妙）

select * from tab where a > 1 order by b

select * from tab where a > 1 and b > '2015-12-01 00:00：00' order by b

select * from tab order by a, b

　　以下情况用不到

　　1. 非最左列，select * from tab order by b;

　　2. 不按索引列顺序来的，select * from tab where b > '2015-12-01 00:00:00' order by a;

　　3. 多列排序，但列的顺序方向不一致，select * from tab a asc, b desc。

　　聚簇索引与覆盖索引

　　前面说到，mysql索引从结构上只有两类，BTREE与HASH，覆盖索引只是在查询时，要查询的列刚好与使用的索引列完全一致，mysql直接扫描索引，然后就可返回数据，大大提高效率，因为不需再去原表查询、过滤，这种形式下的索引称作覆盖索引，比如key(a,b)，查询时select a,b from tab where a = 1 and b > 2，本质原因：BTREE索引存储了原表数据。

　　聚簇索引也不是单独的索引，前面简要写到，BTREE索引会把数据放在索引中，即索引的叶子页中，包括主键，主键是跟表数据紧挨着放在一起的，因为表数据只有一份，一列键值要跟每一行数据都紧挨在一起，所以一张表只有一个聚簇索引，对于mysql来说，就是主键列，它是默认的。

　　聚簇索引将表数据组织到了一起（参考前面主键索引简略图），插入时严重依赖主键顺序，最好是连续自增，否则面临频繁页分裂问题，移动许多数据。

　　哈希索引

　　简要说下，类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，也是对索引列计算一个散列值（类似md5、sha1、crc32），然后对这个散列值以顺序（默认升序）排列，同时记录该散列值对应数据表中某行的指针，当然这只是简略模拟图

　　比如对姓名列建立hash索引，生成hash值按顺序排列，但是顺序排列的hash值并不对应表中记录，从地址指针可反应出来，而且，hash索引可能建立在两列或者更多列上，取得是多列数据后的hash值，它不存储表中数据。它先计算列数据的hash值，与索引中的hash值比较，找到了然后比对列数据是否相等，可能涉及其他列条件，然后返回数据。hash当然会有冲突，即碰撞，除非有很多冲突，一般hash索引效率很高，否则hash维护成本较高，因此哈希索引通常用在选择性较高的列上面。哈希索引的结构决定了它的特点：

　　1. hash索引只是hash值顺序排列，跟表数据没有关系，无法应用于order by；

　　2. hash索引是对它的所有列计算哈希值，因此在查询时，必须带上所有列，比如有(a, b)哈希索引，查询时必须 where a = 1 and b = 2，少任何一个不行；

　　3. hash索引只能用于比较查询 = 或 IN，其他范围查询无效，本质还是因不存储表数据；

　　4. 一旦出现碰撞，hash索引必须遍历所有的hash值，将地址所指向数据一一比较，直到找到所有符合条件的行。

　　填坑

　　前面提到通过explain的key_len字段，可大致估计出用了哪些列，索引列的长度跟索引列的数据类型直接相关，一般，我们说int是4字节，bigint8字节，char是1字节，考虑到建表时要指定字符集，比如utf8，还跟选的字符集有关（==!），在utf8下边，一个char是3字节，但是知道这些仍不能说key_len就是将用到的索引列的数据类型代表字节数一加不就完啦？事实总有点区别，测试方法比较机械（以下基于mysql 5.5.2）

　　建表，加索引，int型

--测试表

create table keyLenTest1(

id int primary key auto_increment,

typeKey int default 0 ,

add_time timestamp not null default current_timestamp

) charset utf8

--添加索引

alter table keyLenTest1 add index idx_k(typeKey);

　　可知int型索引默认长度为5，在4字节基础上+1

　　char型

1 2	`--改为char型，1个字符` `alter table keyLenTest1 modify typeKey` `char(1);`

1 2	`--改为char型，2个字符` `alter` `table` `keyLenTest1` `modify` `typeKey` `char(2);`

　　可知，char型初始是4字节（3+1 bytes），后续按照3字节递增

　　varchar型

1 2	`--改为varchar型，1个字符` `alter` `table` `keyLenTest1` `modify` `typeKey` `varchar(1);`

1 2	`--改为varchar型，2个字符` `alter` `table` `keyLenTest1` `modify` `typeKey` `varchar(2);`

　　可知，varchar型，1个字符时，key_len为6，以后以3字节递增

　　所以，如果一个语句用到了int、char、varchar，key_len如何计算以及用了哪些索引列应该很清楚了。

　　如果想了解的更详细点，explain各字段意义，索引的更多细节，除了explain，还有show profiles、慢查询日志等（没细看），推荐看高性能mysql

只抓住一缕风

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL篇：查询优化

一.什么导致查询速度变慢在尝试编写快速的查询之前,需要清除一点,真正重要是响应时间。如果把查询看作是一个任务，那么他由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行的次数，要么让子任务运行得更快。　　MySQL在执行查询的时候有哪些子任务。哪些子任务运行的速度很慢,这里很难给出完整的列表，通...
复制链接

扫一扫