MySql的查询性能分析-Explain

MySql的查询毋庸置疑是许多系统的性能瓶颈，如何在有限的资源中获得最大的性能释放？查询语句的优化技能你真的不提升一下吗？

• MySql常见瓶颈一般在cpu和io两个方面
  • CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
  • 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

Explain(执行计划)

• 使用Explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的，分析你的查询语句或是表结构的性能瓶颈
• 可以得到表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引实际被使用
• 表之间的引用
• 每张表有多少行被优化器查询
• 语法：explain+sql语句

名词解释

• id:
  • select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序（select执行的优先级），有三种情况：
  • id相同，执行顺序由上至下
  • id不同，查询，id的序如果是子号会递增，id值越大优先级越高，越 先被执行
  • id相同不同，同时存在
• select_type:
  • simple(简单查询),简单的select查询
  • primary(主查询),查询中若包含任何复杂的子部分，最外层查询则被标记为主查询
  • subquery(子查询),在select或where列表中包含了子查询
  • derived(衍生),在from列表中包含的子查询被标记为derived(衍生)。mysql会递归执行这些子查询，把结果放在临时表里
  • union,若第二个select出现在union之后，则被标记为union;若union包含在from子句的子查询中，外层select将被标记为 derivde
  • union result ,从union表获取结果的select
• type(访问类型):
  • system,表只有一行记录(等于系统表)，这是const类型的特例，平时不会出现，可以忽略不计
  • const,表示通过索引一次就找到了，const用于比较primary key或者 unique索引。因为只匹配一行数据所以很快。如将主键置于where列表中，mysql就能将该查询转换为一个常量。例如通过索引查询
  • eq_ref,唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配,常见于主键或唯一索引扫描 例如：链表时的1对1
  • ref,非唯一性索引扫描，返回匹配某个单独值的所有行本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体,例：链接表时的1对多
  • range,只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引一般就是在你where语句中出现了between，<，>，in等查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，结束于另一点，不用全部扫描。
  • index,Full Index Scan,index于all区别为index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但是index是从索引中读取的，而all是从磁盘中读取的）例：没有where条件的查询索引 explain select id from landmanage_massif type是index。explain select * from landmanage_massif type是all
  • all,全表扫描
  • null
  • 显示的是访问类型，其中从最好到最差：system>const>eq_ref>ref>range>index>all
  • 一般来说，得保证查询至少达到range级别，最好能达到ref
• possible_keys
  • 显示可能应用在这张表上的索引，一个或多个。
  • 查询涉及到的字段上若存在索引则该索引将被列出，
  • 但不一定被查询实际使用
• key:
  • 实际使用的索引 ，如果为null则没有使用索引
  • 查询中若使用到了覆盖索引，则该索引只出现在key列表中
• key_len:
  • 表示索引中使用的字节数， 可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
  • key_len显示的值为索引字段的最大可能长度，并非实际使用长度，
  • 即key_len是根据表定义计算而得，不是通过表内检索出的

列类型	KEY_LEN	备注
id int	key_len = 4+1	int为4bytes,允许为NULL加1byte
id bigint not null	Key_len=8	bigint 8bytes
user char(30) utf8	key_len=30*3+1	utf8每个字符为3bytes,允许为NULL,加1byte
user varchar(30) not null utf8	key_len=30*3+2	utf8每个字符为3bytes,变长数据类型,加2bytes
user warchar(30) utf8	key_len=30*3+2+1	utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes
detail text(10) utf8	key_len=30*3+2+1	TEXT截取部分,被视为动态列类型。

• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
• rows: 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
• extra: 包含不适合在其他列中显示但十分重要的额外信息
  • using filesort
    • 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
    • mysql中无法利用索引完成的排序操作称为“文件排序”
  • using temporary
    • 使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by
  • using index
    • 表示相应的select操作中使用了覆盖索引（covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where 表明索引用来读取数据 而非执行查找动作
  • using where: 表明使用了where过滤
  • using join buffer：使用了链接缓存
  • impossiable where：where子句的值总是false,不能用来获取任何元组
  • sleect table optimized away
    • 在没有group by 子句的情况下，基于索引优化min/max 操作或者对于myisam存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
  • distinct
    • 优化distinct操作，在找到第一匹配的元组后即停止找同样值的操作

MySql索引优化

索引失效

• 全值匹配我最爱：建立的复合索引，在where条件中全部用到，且条件都是等于
• 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
• 在索引列上做任何操作（计算，函数，（自动or手动）类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列,范围之后全失效，范围内个用来排序不是用来检索数据
• 尽量使用覆盖索引（只访问索引的查询（查询列和索引列一致））减少select *
• mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
• is null , is not null 也无法使用索引
• like以通配符开头('%abc...')索引失效，会变成全表扫描,通配符加后面，索引不失效。解决以通配符开头的sql索引失效的办法 就是使用覆盖索引 即，建立的复合索引，查询的时候要覆盖上。
• 字符串不加单引号索引失效
• 少用or，用它来连接时会索引失效, 用or时 如果是复合索引，只用其中一个 索引失效 变全表扫描，如果是单值索引，不失效，变range