explain的小用法
这里说的是mysql的explain的小用法。
今天看讨论组里面讨论一个sql，在百万级的数据情况下，运行需要4秒左右。太慢了，于是大家都在分析这个sql的问题。
sql如下：
select uid,date,state from tablename where uid=1 and date>'2019-07-01 00:00:00' and date <'2019-07-03 00:00:00' order by date;
首先我们用explain对这个语句进行一下分析
explain select uid,date,state from tablename where uid=1 and date>'2019-07-01 00:00:00' and date <'2019-07-03 00:00:00' order by date;
#解释一下显示的字段含义
#id:SELECT识别符。这是SELECT的查询序列号
#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子句的子查询)
#table：表名
#type：显示连接使用的访问类型
#结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > #unique_subquery > index_subquery > range > index > ALL
#一般来说，得保证查询至少达到range级别，最好能达到ref。
#possible_keys：表中可能出现的索引，根据where后的字段得出，如果没写条件会显示NULL
#key_len：使用的索引的长度
#ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
#rows：MYSQL认为必须检查的用来返回请求数据的行数
#Extra：额外信息
#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，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）
一眼看过去，就知道需要优化的就是使用了filesort。
接下来看一下是不是索引没建好：
show index from tablename;
#以下是返回参数的详细解释
#table : 表的名称
#Non_unique : 表示索引值是否可以重复，为0是不能重复，1是可以重复
#Key_name : 索引名称,如果是主键的话 则为PRIMARY
#Seq_in_index : 索引中序列的序列号,从1开始,如果是组合索引 那么按照字段在建立索引时的顺序排列 如 ('c1', #'c2', 'c3') 那么 分别为 1, 2, 3
#Column_name : 字段名
#Collation： 列以什么方式存储在索引中。A=有值、升序显示 或 NULL（无分序）
#Cardinality : 索引中唯一值的数目的估计值,通过运行 ANALYZE TABLE or myisamchk -a 来更新,基数根据被存储为##整数的统计数据来计数,所以对于小表该值没必要太过于精确,而对于大数据量的表来说,改值越大当进行联合时，MySQL使用该#索引的机 会就越大。
#Sub_part : 索引的长度
#部分被编入索引，该值表示索引的长度
#整列被编入索引，该值显示为null
#Packed : 指示关键字如何被压缩。如果没有被压缩，则为NULL
#Null : 该列值有NULL，则为YES，否则为NO
#Index_type：所用索引方法（BTREE, FULLTEXT, HASH, RTREE）
#这里我会稍微扩展一下，想先详见下面的索引类型。
#Comment：关于在其列中没有描述的索引的信息
#Index_comment : 为索引创建时提供了一个注释属性的索引的任何评论
检查索引后发现，缺少了uid和date的复合索引，添加上索引，在进行查询，速度就是0.004左右了。
添加索引的方法：
#ALTER TABLE
#1.普通索引 添加INDEX
ALTER TABLE table_name ADD INDEX index_name (column);
#2.主键索引 添加PRIMARY KEY
ALTER TABLE table_name ADD PRIMARY KEY (column);
#3.唯一索引 添加UNIQUE
ALTER TABLE table_name ADD UNIQUE (column);
#4.全文索引 添加FULLTEXT
ALTER TABLE table_name ADD FULLTEXT (column);
#5.如何添加多列索引
ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3);
#CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list);
CREATE UNIQUE INDEX index_name ON table_name (column_list);
#table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE #INDEX语句创建PRIMARY KEY索引。
#删除索引
DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;
ps 1.索引方法
mysql里目前只支持4种索引分别是：full-text，b-tree，hash，r-tree
b-tree索引应该是mysql里最广泛的索引的了，除了archive基本所有的存储引擎都支持它.
1. full-text索引
full-text在mysql里仅有myisam支持它，而且支持full-text的字段只有char、varchar、text数据类型。
full-text主要是用来代替like "%***%"效率低下的问题.
2. b-tree索引
b-tree在myisam里的形式和innodb稍有不同
在 innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的b-tree差不多，只是还存放了指向主键的信息.
而在myisam里，主键和其他的并没有太大区别。不过和innodb不太一样的地方是在myisam里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息.
3. hash索引
目前我所知道的就只有memory和ndb cluster支持这种索引.
hash索引由于其结构，所以在每次查询的时候直接一次到位，不像b-tree那样一点点的前进。所以hash索引的效率高于b-tree，但hash也有缺点，主要如下：
(1)由于存放的是hash值，所以仅支持<=>以及in操作.
(2)hash索引无法通过操作索引来排序，这是因为存放的时候经过hash计算，但是计算的hash值和存放的不一定相等，所以无法排序.
(3)在组合所以里，无法对部分使用索引.
(4)不能避免全表扫描，只是由于在memory表里支持非唯一值hash索引，www.linuxidc.com就是不同的索引键，可能存在相同的hash值.
(5)当存在大量相同hash值得时候，hash索引的效率会变低
4. r-tree索引
r-tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。相对于b-tree，r-tree的优势在于范围查找.
ps 2.一些小技巧
-
myisam里所有键的长度仅支持1000字节，innodb是767.
-
blob和text字段仅支持前缀索引.
-
使用!=以及<>不等于的时候，mysql不使用索引.
-
当在字段时候函数的时候，mysql无法使用索引；在join时条件字段类型不一致的时候，mysql无法使用索引；在组合索引里使用非第一个索引时也不使用索引.
-
在使用like的时候，以%开头，即"%***"的时候无法使用索引；在使用or的时候，要求or前后字段都有索引.
-
分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT
SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;
一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。
-
强制使用临时表： SQL_BUFFER_RESULT
SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT，选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集.
-
强制连接顺序： STRAIGHT_JOIN
SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。
ps 2.oracle的explain plan for
EXPLAIN PLAN FOR SELECT * FROM T_USER;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
扫一扫
484
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
