一号学院MySQL性能优化课程-途牛网王强

1、 SQL优化SQL优化相关：1、CBO2、索引3、执行计划4、日常注意事项5、调优实战CBO语句的代价(Cost),这里的代价主要指Cpu和内存。CPU Costing的计算方式现在默认为CPU+I/O两者之和。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。CBOMysql如何查看统计信息： show table status like tablename;CBORows:表中记录的数量Av

2、g_row_length:每条记录的长度Index_length：索引占用磁盘的空间大小 。data_length：表占用磁盘的空间大小 。如何收集统计信息Analyze table收集表和索引统计信息，适用于MyISAM和InnoDB；对于innodb表，还可以使用以下选项来收集1、表第一次打开的时候2、表修改的行超过1/16或者20亿条3、执行show index/table或者查询information_schema.tables/statistics表时统计信息相关参数innodb_stats_on_metadata参数用来控制此行为，设置为false时不自动更新统计信息。Innodb

3、_stats_sample_pages每次收集统计信息时采样的页数，默认为8，建议适当加大这个值，使统计信息更准确。索引索引是sql优化中一个很重要的手段，很多性能问题，都是没有合适的索引造成的。假设我们有个表中数据量是100w，我们要查找某个记录，如果没有索引，那么我们就只能遍历整个表，如果记录是表中最后一条记录，那么我们就是访问了100w条记录，才找到需要的，而如果有索引，我们一般经过3-4次IO，就可以找到需要的记录，效果对比起来十分明显。索引的作用索引主要的作用：1、快速定位记录2、避免排序和使用临时表3、可以将随机IO变为顺序IO索引的字段索引可以包含一个或者多个字段，如果包含多个字

4、段，那么列的顺序也很重要，因为mysql只能高效的使用索引的最左前缀列，建议是将选择性高的放在最前面。选择性：是指列的num_distinct/rowcount，这个比值越高，说明字段的选择性越好，查询的效率就越高。举个例子，比如员工表包含了身份证和性别等字段，下面2个查询条件：select * from table where 身份证号码=320112XXXXXXXXXXXXX select * from table where 性别=320112XXXXXXXXXXXXX字段选择性在执行第一条sql的时候，就返回一条记录，效率最高，而第二条记录则返回了全部的某个性别的记录，数据量很大，自然

5、效果就不好，甚至都不会使用索引了。MYSQL索引种类B-TREE索引：mysql的表就是基于索引的表哈希索引：基于哈希表实现，只能精确匹配，不能用于排序空间索引：myisam支持的索引全文检索：myisam支持MYSQL执行计划要优化sql，我们首先要理解sql是如何运作的，只有这样，我们才能去做优化。优化的最终目标，就是通过最少的IO，获取我们需要的数据，在Mysql里，我们可以通过explain查看执行计划，通过执行计划可以基本看清楚一个sql执行起来是如何获取数据的。EXPLAINEXPLAIN各个字段的含义ID：序号，表示了执行顺序，执行的时候，是从大到小的顺序执行，如果ID相同，则从

6、上到下依次执行。EXPLAIN各个字段的含义SELECT TYPE：表示查询中每个select子句的类型（简单 OR复杂），有以下一些：SIMPLE ：查询中不包含子查询或者UNIONRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为primarySUBQUERY：在SELECT或WHERE列表中包含了子查询。EXPLAIN各个字段的含义DERIVED：在FROM列表中包含的子查询被标记为：（衍生）若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVEDUNION RESULT：从UNI

7、ON表获取结果的SELECT被标记为这个TYPE字段Type：表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下，由左至右，由最差到最好：ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行index，Full Index Scan，index与ALL区别为index类型只遍历索引树range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、等的查询RANGE访问类型的不同形式性能差异ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找,比如 field1 = abc

8、 eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描，比如 field1 = abc const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量system是const类型的特例，当查询的表只有一行的情况下， 使用systemNULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引possible_keys：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用Key：显示MyS

9、QL在查询中实际使用的索引，若没有使用索引，显示为NULLTIPS：查询中若使用了覆盖索引，则该索引仅出现在key列表中key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度Ref:表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值Rows: 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数Extra: 包含不适合在其他列中显示但十分重要的额外信息，有以下一些常见信息：1、Using where： 表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）, 如果查询未能使用索引，Using where的作

10、用只是提醒我们MySQL将用where子句来过滤结果集2、using temporary： 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询3、Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”日常的一些注意事项1、索引建议用选择性好的字段，这样效率高一些2、不要过度索引，过多索引会影响表的插入、更新效率3、不要对索引上的字段使用表达式，这样索引不会被使用： where to_char(tm,yyyy-mm-dd) = 2015-03-01应该改为where tm = 2015-03-01and tm 200 - where valu 200 -

11、 1 4、所以只能使用前缀索引：比如有索引包含 a、b两个字段，那么条件可以是a = X and b = X a = x 对于单独的 b = x ，这个索引是无效的，所以上面的2个情况，建立一个负荷索引就可以了。 Like 操作，对于 like xxx%是可以使用索引的，但是 like %xxx%, like %XXX 是不可以使用索引的。SQL调优实战SELECT m.userid ,m.point ,panyname_s,c.logo ,c.great ,i.answercount,m.areaid AS company_areaidFROM phpcms_member mLEFT JOI

12、N phpcms_member_company c ON c.userid = m.useridLEFT JOIN phpcms_member_info i ON i.userid = m.useridWHERE m.userid 1 AND c.logo != ORDER BY m.point DESC LIMIT 0 ,9;执行计划：优化思路：这个sql其实最后就返回了9条记录，典型的分页问题。很多的分页操作，是我们过滤了全部数据以后，只取排序以后的前N条记录，大部分其他记录是被扔掉了，所以优化的思路，应该是直接取驱动表中的9条记录，然后再关联其他表，这样的执行效率才是最高的，下面我们就按

13、照这个思路来处理这条sql。这条sql我们看的话，应该是以phpcms_member为驱动表，为了避免最后的排序操作，我们需要以point为索引的第一个字段，这样就避免的最后的排序操作，然后我们再加上userid字段，这样where条件我们就可以用这个索引来过滤了。1、给 phpcms_member 表添加索引：Create index index_point_userid on phpcms_member(point desc , userid ) ;添加了这个索引以后，我们看到执行计划还是没有大的改观改写SQLSELECT m.userid ,m.point ,panyname_s ,c.

14、logo , c.great ,i.answercount ,m.areaid AS company_areaidFROM ( SELECT userid FROM phpcms_member a WHERE userid 1 AND EXISTS ( SELECT *FROM phpcms_member_companyWHERE userid = a.userid AND c.logo != ）ORDER BY a.point DESC LIMIT 0 , 9 ) mLEFT JOIN phpcms_member_company c ON c.userid = m.useridLEFT JOIN phpcms_member_info i ON i.userid = m.useridORDER BY m.point DESC LIMIT 0 , 9 ;优化后的执行计划SQL优化实例2Sql语句如下：KQ_USER_INFO符合条件的记录大概有1.7w，KQ_ATTENDANCE_DETAIL表在指定日