SQL基础知识(二)

以你之姓@ 2021-09-26 00:14 402阅读 0赞

一、执行计划

select\_type  
        查询类型  
            SIMPLE          简单查询  
            PRIMARY         最外层查询  
            SUBQUERY        映射为子查询  
            DERIVED         子查询  
            UNION           联合  
            UNION RESULT    使用联合的结果  
            ...  
    table  
        正在访问的表名  
  
  
    type  
        查询时的访问方式，性能：all < index < range < index\_merge < ref\_or\_null < ref < eq\_ref < system/const  
            ALL             全表扫描，对于数据表从头到尾找一遍  
                            select \* from tb1;  
                            特别的：如果有limit限制，则找到之后就不在继续向下扫描  
                                   select \* from tb1 where email = 'seven@live.com'  
                                   select \* from tb1 where email = 'seven@live.com' limit 1;  
                                   虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。  
  
            INDEX           全索引扫描，对索引从头到尾找一遍  
                            select nid from tb1;  
  
            RANGE          对索引列进行范围查找  
                            select \*  from tb1 where name < 'alex';  
                            PS:  
                                between and  
                                in  
                                >   >=  <   <=  操作  
                                注意：!= 和 > 符号  
  
  
            INDEX\_MERGE     合并索引，使用多个单列索引搜索  
                            select \*  from tb1 where name = 'alex' or nid in (11,22,33);  
  
            REF             根据索引查找一个或多个值  
                            select \*  from tb1 where name = 'seven';  
  
            EQ\_REF          连接时使用primary key 或 unique类型  
                            select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;  
  
  
  
            CONST           常量  
                            表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。  
                            select nid from tb1 where nid = 2 ;  
  
            SYSTEM          系统  
                            表仅有一行(=系统表)。这是const联接类型的一个特例。  
                            select \* from (select nid from tb1 where nid = 1) as A;  
    possible\_keys  
        可能使用的索引  
  
    key  
        真实使用的  
  
    key\_len  
        MySQL中使用索引字节长度  
  
    rows  
        mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值  
  
    extra  
        该列包含MySQL解决查询的详细信息  
        “Using index”  
            此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。  
        “Using where”  
            这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。  
        “Using temporary”  
            这意味着mysql在对查询结果排序时会使用一个临时表。  
        “Using filesort”  
            这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。  
        “Range checked for each record(index map: N)”  
            这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible\_keys列中索引的位图，并且是冗余的。

二、配置MySQL自动记录慢日志  
  
1.修改配置文件 my.ini，没有则添加              
slow\_query\_log = OFF                   \# 是否开启慢日志记录  
long\_query\_time = 2                    \# 时间限制，超过此时间，则记录  
slow\_query\_log\_file = /usr/slow.log    \# 日志文件  
log\_queries\_not\_using\_indexes = OFF    \# 为使用索引的搜索是否记录  
  
\# 查看当前配置信息：  
　  show variables like '%query%'  
\# 修改当前配置：  
  　set global 变量名 = 值  
   
 2.查看日志  
 mysqldumpslow -s at -a  xx/xx/slow.log

--verbose    版本  
\--debug      调试  
\--help       帮助  
   
\-v           版本  
\-d           调试模式  
\-s ORDER     排序方式  
             what to sort by (al, at, ar, c, l, r, t), 'at' is default  
              al: average lock time  
              ar: average rows sent  
              at: average query time  
               c: count  
               l: lock time  
               r: rows sent  
               t: query time  
\-r           反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)  
\-t NUM       显示前N条just show the top n queries  
\-a           不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'  
\-n NUM       abstract numbers with at least n digits within names  
\-g PATTERN   正则匹配；grep: only consider stmts that include this string  
\-h HOSTNAME  mysql机器名或者IP；hostname of db server for \*-slow.log filename (can be wildcard),  
             default is '\*', i.e. match all  
\-i NAME      name of server instance (if using mysql.server startup script)  
\-l           总时间中不减去锁定时间；don't subtract lock time from total time