按在地上摩擦面试官之mysql优化

mysql中explain关键字解释

1、system：表中仅有一行（=系统表）这是const联结类型的一个特例。

2、const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量

3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描

4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体

5、range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描

6、index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。

7、all：遍历全表以找到匹配的行

注意:一般保证查询至少达到range级别，最好能达到ref。

02

2.哈希索引

哈希索引基于哈希表实现，只有精确匹配索引的所有列的查询才有效。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，如果多个列的哈希码相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引使用限制：

· 哈希索引值存储哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行数据；

· 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序；

· 哈希索引不支持部分索引列匹配查询，因为哈希索引始终是使用索引列的全部内容来计算哈希值的；

· 哈希索引只支持等值比较查询，包括=、IN()、<=> 。不支持任何范围查询；

· 访问哈希索引的数据非常快，除非出现哈希冲突，此时存储引擎需要遍历链表中所有行指针，逐行进行比较；

· 如果哈希冲突非常多的话，一些索引维护操作的成本也会非常高。

注意：InnoDB不支持hash索引，但是通过伪哈希的方式，你可以自己创建一个hash字段，多冗余一个字段，通过编码的方式存储到冗余的hash列。哈希索引效率在一般情况效率会非常高。

3.空间数据索引(R-tree)

R树

对于B/B+-Trees 由于它的线性特点，通常用来索引一维数据。（比它大的往一边走，比它小的往一边走，但只是在一个维度下进行比较）。MyISAM表支持空间索引,可以用作地理数据存储.和B-Tree索引不同,这类索引无需前缀查询.

4.全文索引

全文索引是一种比较特殊的索引，查找的是文本中关键词，而不是直接比较索引中的值。常见的全文搜索引擎lucence，solr,es.本篇就不展开讲，以后单独讲。

5.其他索引类别(这个用的不多，略过)

03

04

1.独立的列索引

列不能是表达式的一部分，也不能是函数的参数。例如：SELECT room FROM tb_sale_house_info WHERE room + 1 = 3;或者SELECT room FROM tb_sale_house_info WHERE AVG(room) = 3;

2.前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得很大且很慢。此时可以有两个策略，一个是自定义哈希索引，另一个就是前缀索引；

前缀索引能大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性（索引选择性——不重复的索引值和数据表记录总数的比值）；

索引前缀长度的选择——计算法。例如：LELECT COUNT(DISTINCT address)/COUNT(*) AS sel1, COUNT(DISTINCT LEFT(address, 3))/COUNT(*) AS sel2, ...; 如果前缀的选择性接近sel1就可以使用了。有时候只看平均选择型也不靠谱，还需要做进一步判断。

缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描；

有时候也可以使用前缀索引——可将对应列的字符串反序存储，并创建前缀索引。

3. 多列索引

为多列创建合适的索引。例如：key(c1), key(c2), key(c3)

MySQL5.0之后的版本引入了“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位表中的行；

索引合并策略有时候是一种优化后的结果，但实际上更说明表上的索引建得很垃圾。

当出现服务器对多个索引做相交操作时（多个AND），通常意味着需要一个包含相关列的多列索引，而不是多个独立的单列索引;

当服务器需要对多个索引做联合操作时（多个OR），通常需要耗费大量的CPU和内存在算法的缓存、排序和合并上。

4. 选择合适的索引顺序

正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要；

索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY 、GROUP BY和DISTINCT等子句的查询需求；

索引列顺序的选择——在不考虑分组和排序的情况下，将选择性最高的列放到索引最前面（经验法则）最容易分辨，区分度最高的放前面

避免随机I/O和排序；

对于某些特殊用户和分组，避免其使用普通的索引查询。

5. 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式

6. 覆盖索引

覆盖索引大大提高查询性能，不用回表查询。

7. 使用索引扫描排序

MySQL有两种方式可以生成有序结果：通过排序操作；按照索引顺序扫描。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（升序/降序）都一样时，MySQL才能使用索引来对结果做排序；

当查询需要关联多张表时，只有当ORDER BY子句引用的字段全部来自第一张表时，才能使用索引排序；

ORDER BY子句中的字段需要满足索引的最左前缀的要求，才能使用索引排序；

当索引的前导列为常量时，ORDER BY子句可以不满足索引的最左前缀要求也能使用索引排序。例如：key(rental_date, inventory_id, customer_id)；... where rental_data='2018-01-08' ORDER BY inventory_id DESC;

8. 压缩（前缀压缩）索引

由于内存空间有限，为了将更多的索引放到内存中。采取压缩算法。或进行前缀压缩，只存一些索引列的前缀。从而来提高查询性能。但是缺点也是比较明显的，减少了一定的磁盘上空间，增加了cpu的计算。

9.未使用的索引

生产中经常遇到表见了好多索引，但是又用不到，你占了磁盘的空间，却不干活，要你有何用。

10.索引和锁

索引为了缩小数据范围，而行锁可以锁定更少的行。InnoDB只有在访问行的时候才会加锁，而索引减少行的范围，从而减少锁的数量。索引和锁搭配起来才能发挥更大的作用，举个栗子：庞光准备去找苍老师，说见个面。这时候史珍香告诉庞光

说苍老师在北京的某个酒店,庞光就来到酒店门口，发现门口被封上了，来到前台问了下在8025房间，来到这发现门还是锁着的。

第一次索引是史珍香告诉庞光在北京的

第一次遇到锁，锁的范围较大，无奈庞光等了会。

第二次索引，前台说的8025房间

第二次的锁比较小，就是房间锁。

我们发现索引提高找的效率，锁的范围大就会阻塞，锁的范围小效率就会高。