mysql性能优化培训.doc

一 概述数据库属于 IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一步需要优化的就是 IO，尽可能将磁盘IO转化为内存IO。所以我们先从 MySQL 数据库IO相关参数（缓存参数）的角度来看看可以通过哪些参数进行IO优化二 表结构优化：由于MySQL数据库是基于行（Row）存储的数据库，而数据库操作 IO 的时候是以 page（block）的方式，也就是说，如果我们每条记录所占用的空间量减小，就会使每个page中可存放的数据行数增大，那么每次 IO 可访问的行数也就增多了。反过来说，处理相同行数的数据，需要访问的 page 就会减少，也就是 IO 操作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数据命中的几率，也就是缓存命中率。优化原则：使数据量最小，性能的瓶颈在于磁盘性能，数据量越小，磁盘读取数据的速度就越快，同时，在较小的列上建立索引，索引文件占用的资源也会更少。1. 尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型。尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间要少25%2. 尽量使用 NOT NULLNULL 类型比较特殊，SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外的存放空间。很多人觉得 NULL 会节省一些空间，所以尽量让NULL来达到节省IO的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL，也是一个很好的表结构设计优化习惯。3. 只创建你确实需要的索引。索引对检索有好处，但是当你需要快速存储东西时就变得糟糕。如果主要通过搜索列的组合来存取一个表，对它们做一个索引。第一个索引部分应该是最常用的列。如果从表中选择时总是使用许多列，应该首先以更多的副本使用列以获得更好的索引压缩。4. 垂直拆分保证经常查询、显示的主要字段在一张表中，其他的信息放在明细表中。此类优化更利于常用字段频繁查询时，提高系统性能。例如教师信息表，经常用到的字段非常有限，每次查询浪费大量的内存资源加载不使用的信息，对性能造成一定影响。5. 水平拆分当表中数据量非常大时，可以根据具体的业务情况，按照某个字段进行分类，存储在不同的表甚至是数据库中，使得某个表或库的数据量在一个比较小的范围内，一次来提高查询性能。三 索引的使用1. 索引对单个表查询的影响索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。例如对下面这样的一个student表：mysqlSELECT * FROM studentIdNameEnglishChineseHistory12Tom66936756Paul78527510Marry5489744Tina99834839William43965274Stone42406186Smith49857837Black49634789White943152这样，我们试图对它进行一个特定查询时，就不得不做一个全表的扫描，速度很慢。例如，我们查找出所有english成绩不及格的学生：mysqlSELECT name,english FROM student WHERE englishSELECT name,english FROM user WHERE english SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。你可以这样创建一个多列索引：mysqlALTER TABLE tbl_name ADD INDEX(col1,col2);而你应该这样创建分开的单行列索引：mysqlALTER TABLE tble_name ADD INDEX(col1);mysqlALTER TABLE tble_name ADD INDEX(col1);如果表有一个多列索引，任何最左面的索引前缀能被优化器使用以找出行。例如，如果你有一个3行列索引(col1,col2,col3)，你已经索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜索能力。如果列不构成索引的最左面前缀，MySQL不能使用一个部分的索引。假定你下面显示的SELECT语句：mysql SELECT * FROM tbl_name WHERE col1=val1;mysql SELECT * FROM tbl_name WHERE col2=val2;mysql SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;如果一个索引存在于(col1、col2、col3)上，只有上面显示的第一个查询使用索引。第二个和第三个查询确实包含索引的列，但是(col2)和(col2、col3)不是(col1、col2、col3)的最左面前缀。如果LIKE参数是一个不以一个通配符字符起始的一个常数字符串，MySQL也为LIKE比较使用索引。例如，下列SELECT语句使用索引： mysql select * from tbl_name where key_col LIKE Patrick%;mysql select * from tbl_name where key_col LIKE Pat%_ck%;在第一条语句中，只考虑有Patrick = key_col Patricl的行。在第二条语句中，只考虑有Pat = key_col select * from tbl_name where key_col LIKE %Patrick%;mysql select * from tbl_name where key_col LIKE other_col;在第一条语句中，LIKE值以一个通配符字符开始。在第二条语句中，LIKE值不是一个常数。如果 column_name 是一个索引，使用column_name IS NULL的搜索将使用索引。MySQL通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列：=、=、=M AND first_name N;然而，name索引不用于下面的查询：SELECT * FROM test WHERE first_name=Michael;SELECT * FROM test WHERE last_name=Widenius OR first_name=Michael;4. 选择索引的准则1) 搜索的索引列，不一定是所要选择的列最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列，例如：SELECTcol_a 不适合作索引列FROMTbl1 LEFT JOIN tbl2ON tbl1.col_b = tbl2.col_c 适合作索引列WHEREcol_d = expr 适合作索引列当然，所选择的列和用于 WHERE 子句的列也可能是相同的。关键是，列出现在选择列表中不是该列应该索引的标志。2) 使用惟一索引考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“M”和“F”，则对此列进行索引没有多大用处(不管搜索哪个值，都会得出大约一半的行)。3) 使用短索引如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 I/O 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。(当然，应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的，因为这个索引中不会有许多不同的值。)4) 利用最左前缀在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同，索引一个列的前缀是利用该的前 n 个字符作为索引值。)假如一个表在分别名为 state、city 和 zip 的三个列上有一个索引。索引中的行是按 state/city/zip 的次序存放的，因此，索引中的行也会自动按 state/city 的顺序和 state 的顺序存放。这表示，即使在查询中只指定 state 值或只指定 state 和 city 的值，MySQL 也可以利用索引。因此，此索引可用来搜索下列的列组合：MySQL 不能使用不涉及左前缀的搜索。例如，如果按 city 或 zip 进行搜索，则不能使用该索引。如果要搜索某个州以及某个 zip 代码(索引中的列1和列3)，则此索引不能用于相应值的组合。但是，可利用索引来寻找与该州相符的行，以减少搜索范围。5) 不要过度索引不要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。5. 比较类型索引可用于“”、“=”、“”和 BETWEEN 运算。在模式具有一个直接量前缀时，索引也用于 LIKE 运算。如果只将某个列用于其他类型的运算时(如 STRCMP( )，对其进行索引没有价值。例如t1表中的c1字段,是varchar(32)类型。Select * from t1 where c1=1;没有用到索引Select * from t1 where c1=1;用到索引了四 explain执行计划执行计划explain select包含如下信息：1. Id包含一组数字，表示查询中执行select子句或操作表的顺序id相同，执行顺序由上至下如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行2. select_type表示查询中每个select子句的类型（简单 OR复杂）1) SIMPLE：查询中不包含子查询或者UNION2) 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY3) 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY4) 在FROM列表中包含的子查询被标记为：DERIVED（衍生） 5) 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVED6) 从UNION表获取结果的SELECT被标记为：UNION RESULT 第一行：id列为1，表示第一个select，select_type列的primary表 示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中3代表该查询衍生自第三个select查询，即id为3的select。 第二行：id为3，表示该查询的执行次序为2（43），是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。 第三行：select列表中的子查询，select_type为subquery，为整个查询中的第二个select。 第四行：select_type为union，说明第四个select是union里的第二个select，最先执行。 第五行：代表从union的临时表中读取行的阶段，table列的表示用第一个和第四个select的结果进行union操作。3. Type表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下：由左至右，由最差到最好 1) ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行 2) index：Full Index Scan，index与ALL区别为index类型只遍历索引树3) range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、in等的查询 4) ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找5) eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描 6) const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量 system是const类型的特例，当查询的表只有一行的情况下， 使用system7) null: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引4. possible_keys 指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用5. key显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULLTIPS：查询中若使用了覆盖索引，则该索引仅出现在key列表中6. key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度TIPS：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的7. ref表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值本例中，由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ac 8. rows表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数9. Extra该列包含MySQL解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串：1) DistinctMySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。2) Not existsMySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。下面是一个可以这样优化的查询类型的例子：SELECT * 从t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL；假定t2.id定义为NOT NULL。在这种情况下，MySQL使用t1.id的值扫描t1并查找t2中的行。如果MySQL在t2中发现一个匹配的行，它知道t2.id绝不会为NULL，并且不再扫描t2内有相同的id值的行。换句话说，对于t1的每个行，MySQL只需要在t2中查找一次，无论t2内实际有多少匹配的行。3) range checked for each record (index map:#)MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。这并不很快，但比执行没有索引的联接要快得多。4) Using filesortMySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。5) Using index从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。6) Using temporary为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。7) Using whereWHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。如果想要使查询尽可能快，应找出Using filesort和Using temporary的Extra值。8) Using sort_union(.),Using union(.),Using intersect(.)这些函数说明如何为index_merge联接类型合并索引扫描。9) Using index for group-by类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。五 Sql优化基本原则1. 优化示例关联优化 left join:SELECT * FROM t1LEFT JOIN t2 ON t1.c1=t2.c1;Mysql会对t1表进行全表扫描,在处理完t1前，是不会考虑t2表的内容SELECT * FROM t1,t2 WHERE t1.c1=t2.c1;2. 尽量少 joinMySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。3. 尽量少排序排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL 的响应时间。对于MySQL来说，减少排序有多种办法，比如：l 上面误区中提到的通过利用索引来排序的方式进行优化l 减少参与排序的记录条数l 非必要不对数据进行排序4. 尽量避免 select *很多人看到这一点后觉得比较难理解，上面不是在误区中刚刚说 select 子句中字段的多少并不会影响到读取的数据吗？是的，大多数时候并不会影响到 IO 量，但是当我们还存在 order by 操作的时候，select 子句中的字段多少会在很大程度上影响到排序效率.5. 尽量用 join 代替子查询虽然 Join 性能并不佳，但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。6. 尽量少 or当 where 子句中存在多个条件以“或”并存的时候，MySQL 的优化器并没有很好的解决其执行计划优化问题，再加上 MySQL 特有的 SQL 与 Storage 分层架构方式，造成了其性能比较低下，很多时候使用 union all 或者是union（必要的时候）的方式来代替“or”会得到更好的效果。7. 尽量用 union all 代替 unionunion 和 union all 的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。8. 尽量早过滤这一优化策略其实最常见于索引的优化设计中（将过滤性更好的字段放得更靠前）。在 SQL 编写中同样可以使用这一原则来优化一些 Join 的 SQL。比如我们在多个表进行分页数据查询的时候，我们最好是能够在一个表上先过滤好数据分好页，然后再用分好页的结果集与另外的表 Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省 IO 操作所消耗的时间。9. 避免类型转换这里所说的“类型转换”是指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换： 人为在column_name 上通过转换函数进行转换直接导致 MySQL（实际上其他数据库也会有同样的问题）无法使用索引，如果非要转换，应该在传入的参数上进行转换 ,由数据库自己进行转换,如果我们传入的数据类型和字段类型不一致，同时我们又没有做任何类型转换处理，MySQL 可能会自己对我们的数据进行类型转换操作，也可能不进行处理而交由存储引擎去处理，这样一来，就会出现索引无法使用的情况而造成执行计划问题。10. 优先优化高并发的 SQL，而不是执行频率低某些“大”SQL对于破坏性来说，高并发的 SQL 总是会比低频率的来得大，因为高并发的 SQL 一旦出现问题，甚至不会给我们任何喘息的机会就会将系统压跨。而对于一些虽然需要消耗大量 IO 而且响应很慢的 SQL，由于频率低，即使遇到，最多就是让整个系统响应慢一点，但至少可能撑一会儿，让我们有缓冲的机会。 11. 从全局出发优化，而不是片面调整SQL 优化不能是单独针对某一个进行，而应充分考虑系统中所有的 SQL，尤其是在通过调整索引优化 SQL 的执行计划的时候，千万不能顾此失彼，因小失大。12. 尽可能对每一条运行在数据