Mysql高级
性能分析
-
MySQL Query Optimizer
- Mysql中有专门负责优化SELECT语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的Query提供优化器认为最优的执行计划(优化器认为最优的数据检索方式,但不见得DBA认为是最优的,这部分最耗费时间)
- 当客户端向MYSQL请求一条Query,命令解析器模块完成请求分类,区别出是SELECT并转发给MYSQL Query Optimizer(优化器)时,Mysql Query Optimizer首先会对整条Query进行优化,处理掉一些常量表达式的预算,直接换算成常量值。并对Query中的查询条件进行简化和转换,(如去掉一些无用或显而易见的条件、结构调整等)。然后分析Query中Hint信息(如果有),看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据Query进行写相应的计算分析,然后在得出最后的执行计划。
-
Mysql的常见瓶颈
- CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
- IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件性能瓶颈:top,free,iostat和vmstat来查看系统的性能状态
-
Explain命令(查看执行计划)
-
概述
- 使用Explain关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL是如何处理SQL语句的。分析查询语句或是表结构的性能瓶颈
-
Explain的作用
- 表的读取顺序 --id
- 数据读取操作的操作类型 --select_type
- 哪些索引可以使用 -possible_keys-
- 哪些索引被实际使用 -key key_len-
- 表之间的引用 – ref
- 每张表有多少行被优化器查询 – rows
-
用法
-
Explain + SQL语句
-
执行计划包含的信息
-
id select_type table patitions type possible_keys key key_len ref rows filtered Extra
-
-
字段名 含义 id 标识符,语句涉及表的执行顺序 select_type 表查询类型 table 表名称 partitions 涉及表哪个分区 type 表的查询(连接)类型 possible_keys 表可能使用到的索引 key 表实际使用到的索引 key_len 表实际使用索引的长度,单位:字节 ref 表哪些字段或者常量用于连接查找索引上的值 rows 查询预估返回表的行数 filtered 表经过条件过滤之后与总数的百分比 Extra 额外的说明信息
-
-
各个字段的解释
-
id:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
- 三种情况
-
id相同:执行顺序由上至下
-
id不同:如果是子查询,id的序号会递增,id越大优先级越高,越先被执行
-
id相同不同,同时存在,如果id相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行
-
DERIVED=衍生
-
总结:id号的每个号码,表示一趟独立的查询,一个sql的查询趟数越少越好。
-
- 三种情况
-
select_type
-
select_type值 含义 SIMPLE 简单查询不包含Union查询或子查询 PRIMARY 位于最外部的查询 UNION 当出现union查询时第二个或之后的查询 DEPENDENT UNION 当出现union查询时第二个或之后的查询,取决于外部查询,若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DEPENDENT UNION UNION RESULT union查询的结果集 SUBQUERY 子查询当中第一个select查询 DEPENDENT SUBQUERY 子查询当中第一个select查询,取决于外部查询 DERIVED 衍生表(FROM子句中的子查询) MATERIALIZED 物化子查询 UNCHACHABLE SUBQUERY 结果集无法缓存的子查询,必须重新评估外部查询的每一行 UNCHACHABLE UNION UNION第二个或之后的SELECT,属于无法缓存的子查询 -
查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询
- SIMPLE:简单的select查询,查询中不包含Union查询或子查询
- PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY
- SUBQUERY:在Select或where列表中包含了子查询
- DERIVED:在From列表中包含的子查询被标记为DERIVED(衍生)MYSQL会递归执行这些子查询,把结果放在临时表里。
- UNION:若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DEPENDENT UNION
- UNION RESULT:从UNION表中获取结果的SELECT
-
-
table:显示这一行的数据时关于哪张表的,如果为表指明了别名,则显示别名,如果没有涉及对表的数据读取,则显示NULL
- <union M,N>:引用id为M和N union后的结果
- :引用id为N的结果派生出的表,派生表可以是一个结果集,例如派生自FROM中子查询的结果。
- :引用id为N的子查询结果物化得到的表,即生成一个临时表保存子查询的结果。
-
type:访问类型
-
说明
-
system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL -
按照最好到最差的连接类型依次为system,const,eq_ref,ref,fulltext,ref_or_null,index_range,unique_subquery,index_subquery,range,index,ALL。
-
system:表中只有一行数据或者是空表,这是const类型的一个特例。且只能用于myisam和memory表。如果是innodb引擎表,type列在这个情况通常是all或者index。
-
const:最多只有一行记录匹配。当联合主键或唯一索引的所有字段跟常量值比较时,type类型为const。其他数据库也叫作唯一索引扫描。
-
eq_ref:多表join时,对于来自前面表的每一行,在当前表中只能找到一行。这是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join连接时会使用此类型。eq_ref可用于使用’='操作符比较的索引列。比较值可以是常量,也可以是使用在此表之前的表的列的索引列
-
ref:对于来自前面表的每一行,在此表的索引中可以匹配到多行。若联接只用到索引的最左前缀或者索引不是主键或唯一索引时,使用ref类型(也就是说,此联接可以匹配到多行记录)。ref可用于使用’=’,’<=>’ 操作符比较的索引列
- eq_ref相对于ref的区别就是它使用的是唯一索引,即主键或唯一索引,而ref使用是非唯一索引或者普通索引,eq_ref只能找到一行,而ref能找到多行。
-
fulltext:使用全文索引的时候是这个类型。要注意,全文索引优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先使用全文索引。
-
ref_or_null:跟ref类型相似,只是增加了null值的比较。实际用的不多。
-
index_merge:表示使用了两个以上的索引,最后取交集或并集,常见and,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但实际上由于读取多个索引,性能可能大部分时间都不如range。
-
unique_subquery:用于where中的in形式子查询,子查询返回不重复值唯一值,可以完全替换子查询,效率更高。
-
index_subquery:该联接类型类似与unique_subquery,适用于非唯一索引,可以返回重复值。
-
range:索引范围查询,常见于使用=,<>,>,>=,<,<=,IS NULL,<=>,BETWEEN,IN()或者like等运算符的查询中。
-
index:索引全表扫描,把索引从头到尾扫一遍,这里包含两种情况
- 查询使用了覆盖索引,那么它只要扫描索引就可以获得数据,这个效率要比全表扫描快,因为索引通常比数据表小,而且还能避免回表(二次查询)。在Extra中显示Using index。
- 反之,如果索引上进行全表扫描,则在Extra中没有Using index提示。
-
ALL:全表扫描,性能最差
-
-
总结
-
常用的访问类型排序,从最好到最差排序:system>const>eq_ref>ref>range>index>ALL
-
一般来说,得保证查询至少达到range级别,最好能达到ref。
-
sytem:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,这个也可以忽略不计
-
const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。如果将主键置于where列表中,Mysql就能将该查询转换成一个常量。
-
eq_ref:唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
-
ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。
-
range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在where语句中使用了between、<、>、in等的查询。这种范围扫描索引比全表扫描要效率高,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。
-
index:Full Index Scan(全索引扫描),index与ALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘中读取的)。出现index是sql使用了索引但是没用通过索引进行过滤,一般是使用了覆盖索引或者是利用索引进行了排序分组。
-
all:Full Table Scan,将遍历全表以找到匹配的行
-
-
-
possible_keys:显示了Mysql在查找当前表中数据的时候可能使用到的索引,实际意义不大。显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被实际查询使用。
-
key:显示了MYSQL在实际查找数据时决定使用的索引,如果该字段值为NULL,表明没有索引。查询中若使用了覆盖索引,则该索引仅出现在key列表中,possible_keys为NULL。
-
key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。单值索引,在不损失精确性的情况下,长度越短越好。(因为索引越大索引树的层级越多,会增加索引匹配数据的次数,而且索引会被加载到内存中,如果索引长度越大自然占用的内存越多)key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据定义计算而得,不是通过表内检索出的。复合索引中,key_len越长越好
- 如何计算key_len
- 先看看索引上的类型加长度,比如int=4; varchar(20)=20; char(20)=20;
- 如果是varchar或者char这种字符串字段,根据不同的字符集需要乘不同的值,比如 utf-8 要乘3,GBK要乘2
- varchar这种动态字符串要加2个字节
- 允许为空的字段要加1个字节
- 可以通过key_len的大小判断评估复合索引使用了哪些部分。几种常见字段类型索引长度大小如下
- 字符型
- char(n):4*n个字节
- varchar(n):4*n+2个字节
- 数值型
- tinyint:1个字节
- int:4个字节
- bigint:8个字节
- 时间类型
- date:3个字节
- datetime:5个字节+秒精度字节
- timestamp:4个字节+秒精度字节
- 秒精度字节(最大6位)
- 1~2位:1个字节
- 3~4位:2个字节
- 5~6位:3个字节
- 字符型
- 如何计算key_len
-
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。
- 如果使用的常数查询,ref显示const
- 如果是连接查询,被驱动表的执行计划ref会显示驱动表的关联字段
- 如果是条件使用了表达式或者函数,或者条件列发生了隐式转换,ref显示的可能是func
-
rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数。这是mysql估算的需要扫描的行数(不是精确值)。这个值非常直观的显示SQL的效率好坏,原则上rows越少越好。
-
filtered:这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不会具体记录数。
-
Extra:包含不适合在其他列中显示,但是又十分重要的额外信息
-
Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。Mysql中无法利用索引完成的排序操作称为“文件排序”。Mysql需要对获取的数据进行额外的一次排序操作,无法通过索引的排序完成。通常发生在order by 子句的语句当中。
-
Using temporary:使用了临时表保存中间结果,MySql在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by。Mysql需要创建临时表来存放结果集。通常发生在有GROUP BY和ORDER BY子句的语句中。
-
Using Index:仅查询索引树就可以获取到所需要的数据行,而不需要读取表中实际的数据行。通常适用于select字段就是查询使用索引的一部分,即使用了覆盖索引。Using Index表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!如果同时出现Using where,表明索引被用来执行索引键值的查找
-
Using Index Condition:显示采用了Index Condition Pushdown
-
Using where:表示使用了where过滤
-
impossible where:where 子句的值总是false,不能用来获取任何元组,sql写错了。
-
Using JoinBuffer:使用了连接缓存
-
select tables optimized away:在没有groupby 子句的情况下,基于索引优化min/max操作或者对于myisam存储引擎优化count(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
-
-
-
总结,在explain 结果表的字段中需要关注的字段是,id,type,key,key_len,ref,rows,Extra
-
思考往一张表插入一百万条数据
- 通过java,通过 insert into table values( 多条数据)
- 关闭自动提交,改成手动提交,一百万条数据插入后,手动提交
- 利用多线程插入
- 分析,对于一些数据量大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。例如报表系统,可能每天花费在数据导入的时间就会长达几个小时。因此优化数据库插入就需要考虑
- 先去掉索引,然后再插入,插入完成后再重现建索引
-
-
Mysql覆盖索引
- 覆盖索引是select的数据列只用从索引中就能够取到,不必读取数据行,换句话说查询要被所建的索引覆盖。
-
MySql查询优化器的提示(hint)
- 概述
- 如果对优化器选择的执行计划不满意,可以使用优化器提供的几个提示(hint)来控制最终的执行计划。
- 常见的hint
- 强制索引FORCE INDEX
- SELECT * FROM TABLE1 FORCE INDEX(FIELD1)
- 以上的SQL语句只使用建立在FIELD1上的索引,而不使用其他字段上的索引。
- 忽略索引IGNORE INDEX
- SELECT * FROM TABLE1 INGNORE INDEX(FIELD1,FIELD2)
- 以上的SQL语句中,TABLE1表中的FIELD1和FIELD2上的索引不被使用
- 关闭查询缓冲SQL_NO_CACHE
- SELECT SQL_NO_CACHE field1,field2 FROM TABLE1;
- 有一些SQL需要实时地查询数据,或者不经常使用(可能一天就执行一两次),这样就需要把缓冲关了,不管这条SQL语句是否被执行过,服务器不会在缓冲区查找,每次都会执行SQL语句
- 强制查询缓冲SQL_CACHE
- SELECT SQL_CACHE * FROM TABLE1;
- 如果在my.ini中的query_cache_type设成2,这样只有在使用了SQL_CACHE后,才使用查询缓冲。
- 优先操作HIGH_PRIORITY
- SELECT HIGH_PRIORITY * FROM TABLE1;
- HIGH_PRIORITY可以使用在select 和insert操作中,让mysql知道,这个操作优先进行
- 滞后操作LOW_PRIORITY
- SELECT LOW_PRIORITY * FROM TABLE1;
- LOW_PRIORITY可以使用在select和insert操作中,让mysql知道,这个操作滞后
- 延迟插入INSERT DELAYED
- INSERT DELAYED INTO table1 set field1 = …
- INSERT DELAYED INTO ,是客户端提交数据给mysql,mysql返回ok状态给客户端。而这时并不是已经将数据插入表中了,而是存储在内存里等待排队,当mysql有空余时,在执行插入。优点是并发时多个客户端的数据的插入集中在了一起,并编写入一个块中。这比执行独立插入要快很多。缺点是插入操作不能立马返回自增ID,还有当系统崩溃时,mysql如果还没有来得及插入数据的话,会导致数据的丢失。
- 强制连接顺序 STRAIGHT_JOIN
- SELECT TABLE1.FIELD1,TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE…
- 通过STRAIGHT_JOIN强迫mysql按table1,table2的顺序连接表。如果按自定义顺序比mysql推荐的顺序进行连接效率高的话,可以通过STRAIGHT_JOIN来确定连接顺序
- 强制使用临时表SQL_BUFFER_RESULT
- SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE…
- 当我们查询的结果集中的数据比较多时,可以通过SQL_BUFFER_RESULT选项强制将结果集放入临时表中,这样就可以很快的释放mysql的表锁了(其他sql就可以对这些记录进行查询),并且可以长时间的为客户端提供大记录集
- 分组使用临时表SQL_BIG_RESULT和SQL_SMALL_RESULT
- SELECT SQL_BIG_RESULT FIELD1,COUNT(1) FROM TABLE1 GROUP BY FIELD1;
- 一般用于分组或DISTINCT关键字,这个选项通知Mysql,如果有必要,就将结果放到临时表中,甚至在临时表中进行排序。SQL_BIG_RESULT和SQL_SMALL_RESULT差不多,很少使用。
- 强制索引FORCE INDEX
- 概述