【学习笔记】查询优化的一点点技巧

 

背景：查询优化，大多是建立索引，怎么建立索引是最好的

=====================背景分割线======================

上干货：

NO.1:使用EXPLAIN评估查询效率，执行一遍查询返回的行数（比如执行count*统计返回行数，有where条件时，执行最左边的条件进行评估）， 与EXPLAIN返回的优化器预估行数比较， 两者一致或相差很小，可以判断索引统计信息是正确的。( 相差较大时执行 ANALYZE TABLE tablename进行表统计信息更新；建议非生产时间更新表统计信息，因为会加锁...)

统计信息没问题，这时若查询还是耗时较大，就不是优化器统计信息的问题。建议从索引是否正确配置，以及其他软硬件问题排查。

========================分割线===========================

NO.2:如何正确配置索引，这是DBA，开发甚至加上运维一起讨论的事情。

日常的查询，一般是有多个查询条件，SELECT ...from ... where t1=条件1 AND t2=条件2；  或者t1>1000 AND t2=10;   

这里就是看选择率/操作符。索引的过滤效率，是从左边开始，效率最好。因此把过滤最好的条件放在复合索引的左边。

看选择率就是，总行数=100=select  count(*) from tablename.  过滤条件1过滤行数=10=select  count(*) from tablename where t1=条件1； 

选择率1=过滤条件1过滤行数  ÷ 总行数 × 100% 

选择率好的放在复合索引最左边， create index indexname on tablename(t1,t2); t1过滤条件好。。

操作符，比如，where t1>1000 AND t2=10；这时操作符大于小于是过滤了一个数据段，相当于二分法。 没有t2=10这样的直接找到10的过滤条件好。

所以一般 操作符 优先考虑等于条件的放在左边。

避免建多个单列索引；比如两个where条件，单独创建两个索引，第一个索引返回行数100行符合条件，第二个索引返回100条符合条件，两者结果进行组合一次，再过滤一次。效率低。

尽量不要建立重复索引； 表维护索引要占用存储空间，另外DML操作更新索引也花费时间；

说到花费时间，一般单张表不建议建立超过5条索引，因维护成本太高。

冗余索引根据不同的查询，选择率不同，可以存在。 例如 create index index1 on tablename（条件1,条件2）; on tablename（条件2,条件1）;

========================分割线===========================

NO3.索引类型大致分为普通索引，唯一索引，主键索引，全文索引，空间索引。

再记录以下EXPLAIN的查看方法：

Id,标识检查的语句编号，如果有子查询，连接查询的评估，会有多个ID，对应多个查询；

select_type:查询语句使用的选择类型， 分为简单查询-simple，子查询-subqueries，连接查询-UNION;

table:查询的表名；

partitions:优化器执行查询需要检查的分区，分区表会用到这个；

type：优化器用于过滤行所使用的比较类型。 一般有全表扫描-ALL,索引扫描比较-index,将主键或唯一键与常量查询条件进行匹配-const，

匹配单个引用值（由ref列标识）是否相等 -eq_ref， 匹配一个或多个参考值以确保相等-ref， 在有索引支持的情况下匹配一个范围的数据-range；

possible_keys：可能使用到的索引；

key：查询所使用的索引；也叫优化器所最终选择使用的索引；

key_len：使用到的索引的字节长度（以字节为单位），It shows how many bytes will be used from each index row；

ref：与索引相比的列（或const）；

rows：优化器预测的估计行数；

filtered：按表条件过滤的行的百分比；

Extra：优化程序或存储引擎提供的每个查询的额外信息；

SHOW WARINGS \g展示了查询优化更多的信息，给了优化器在执行查询时所做的查询语句优化，以及涉及子查询，连接时的执行顺序更改；

 

======================End,先写到这里吧，后续有记录再追加，查询优化有人是出书的，专门写SQL的人需要=========

 

MARK一个链接： mysql 优化 explain 和show warnings 用法 https://blog.csdn.net/chuangli1101/article/details/100814828