我的MYSQL学习心得

1、我的MYSQ岸习心得我的 mysql 学习心得（十六）优化一步一步走来已经写到了第十六篇了 这一篇主要介绍 mysql 的优化 , 优化 mysql 数据库是 dba 和开发人员的必备技能mysql 优化一方面是找出系统瓶颈, 提高 mysql 数据库整体性能；另一方面需要合理的结构设计和参数调整, 以提高用户操作响应的速度；同时还有尽可能节省系统资源, 以便系统可以提供更大负荷的服务如果大家看过我写的两篇文章, 那么学习 mysql 的索引就不会太难 , 因为是相通的其实 mysql 也有 sqlserver 堆表的概念myisam允许没有任何索引和主键的表存在，个人觉得没有主键的 myis

2、am表都属于堆表，因为mysql不支持非主键的聚集索引.innodb 引擎如果没有设定主键或者非空唯一索引 , 就会自动生成一个 6 字节的主键（用户不可见）详细参考 :不过 myisamvsinnodb:mysql 存储引擎详解文章也有一点错误 , 意向共享锁就是表锁 , 其实是不对的1、优化简介mysql 优化是多方面的 , 原则是减少系统的瓶颈 , 减少资源的占用增加系统的反应速度.例如 , 通过优化文件系统, 提高磁盘 i/o 的读写速度；通过优化操作系统调度策略, 提高 mysql 在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快在 mysql 中, 可以使用 s

3、howstatus 语句查询一些 mysql 的性能参数showstatuslike"value"其中 value 是要查询的参数值, 一些常用性能参数如下 :connections: 连接 mysql 服务器的次数uptime:mysql 服务器的上线时间slow_queries: 慢查询的次数com_select: 查询操作次数com_insert: 插入操作次数com_update:更新操作次数com_delete: 删除操作次数如果查询 mysql 服务器的连接次数, 可以执行如下语句showstatuslike"connections"如果查询

4、 mysql 服务器的慢查询次数, 可以执行如下语句showstatuslike"slow_queries"2、优化查询查询是数据库最频繁的操作, 提高查询速度可以有效地提高mysql 数据库的性能（ 1）分析查询语句通过对查询语句的分析 , 可以了解查询语句的执行情况找出查询语句执行的瓶颈mysql 中提供了 explain 语句和 describe 语句 , 用来分析查询语explain 语句的基本语法explainextendedselectselect_option使用 extended 关键字 ,explain 语句将产生附加信息 .select_option 是

5、 select 语句的查询选项 , 包括 fromwhere 子句 等执行该语句 , 可以分析 explain 后面的 select 语句的执行情况 , 并且能够分析所查询的表的一些特征使用 explain 语句来分析 1 个查询语句usetest;explainextendedselect*fromperson;下面对结果进行解释 idselect 识别符 . 这是 select 的查询序列号. select_typeselect 类型 , 可以为以下任何一种 :simple: 简单 select（ 不使用 union 或子查询 ）primary: 表示主查询 , 或者是最外层的查询语句（多

6、表连接的时候）union: 表示连接查询的第二个或后面的查询语句dependentunion:union 连接查询中的第二个或后面的 select 语 句 , 取决于外面的查询unionresult:union 连接查询的结果subquery: 子查询中的第一个select 语句dependentsubquery: 子查询中的第一个select 语句 , 取决于外面的查询derived: 导出表的 select（from 子句的子查询 ） table表示查询的表 type表示表的联接类型下面给出各种联接类型, 按照从最佳类型到最坏类型进行排序 :（ 1） system表仅有一行（= 系统表 ）

7、. 这是 const 联接类型的一个特例 .（ 2） const表最多只有一个匹配行, 它将在查询开始时被读取. 余下的查询优化中被作为常量对待.const 表查询速度很快, 因为它们只读取一次.const 用于常数值比较primarykey 或 unique 索引的所有部分的场合 .在下面的查询中 ,tbl_name 可以用于 const 表:（ 3） eq_ref对于每个来自于前面的表的行组合, 从该表中读取一行. 这可能是最好的联接类型 , 除了 const 类型 .它用在一个索引的所有部分被联接使用并且索引是unique 或primarykey 时.eq_ref 可以用于使用“=”操作

8、符比较的带索引的列. 比较值可以为常量或一个使用在该表前面所读取的表的列的表达式 .在下面的例子中 ,mysql 可以使用 eq_ref 联接来处理ref_tables:select*fromref_table,other_tablewhereref_table.key_column=other_table.column;select*fromref_table,other_tablewherer ef_table.key_column_part1=other_table.columnandref_table.ke y_column_part2=1;（ 4） ref对于每个来自于前面的表的任意

9、行组合, 将从该表中读取所有匹配的行 .如果联接只使用索引键的最左边的前缀, 或如果索引键不是unique 或 primarykey, 则使用 ref.如果使用的键仅仅匹配少量行, 该联接类型是不错的 .ref 可以用于使用=或 <=>操作符的带索引的列.在下面的例子中 ,mysql 可以使用 ref 联接来处理ref_tables:select*fromref_tablewherekey_column=expr;select*fromref_ table,other_tablewhereref_table.key_column=other_table.colu mn;select

10、*fromref_table,other_tablewhereref_table.key_colum n_part1=other_table.columnandref_table.key_column_part2=1;（ 5） ref_or_null该联接类型如同 ref, 但是添加了 mysql 可以专门搜索包含 null 值的行 , 在解决子查询中经常使用该联接类型的优化 .在下面的例子中 ,mysql 可以使用 ref_or_null 联接来处理ref_tables:select*fromref_tablewherekey_column=exprorkey_columnisnu ll;（

11、 6） index_merge该联接类型表示使用了索引合并优化方法. 在这种情况下 ,key 列包含了所用到的索引的清单 ,key_len 列包含了所用到的索引的最长 长度 .该类型替换了下面形式的 in 子查询的 ref:valuein(selectprimary_keyfromsingle_tablewheresome_expr)unique_subquery 是一个索引查找类型 , 可以完全替换子查询 , 效 率更高 .( 8) index_subquery该联接类型类似于 unique_subquery, 不过索引类型不需要是唯一索引 , 可以替换 in 子查询 , 但只适合下列形式的

12、子查询中的非唯一索引 :valuein(selectkey_columnfromsingle_tablewheresome_expr)( 9) range只检索给定范围的行, 使用一个索引来检索行数据.key 列显示使用了哪个索引 ,key_len 显示所使用索引的长度.在该类型中 ref 列为 null.当使用 =、 <>、 >、 >=、 <、 <=、 isnull 、 <=>、 between 或者 in操作符 , 用常量比较关键字列时 , 类型为 range.下面介绍几种检索指定行数据的情况select*fromtbl_namewherek

13、ey_column=10;select*fromtbl_nam ewherekey_columnbetween10and20;select*fromtbl_namewherekey_ columnin(10,20,30);select*fromtbl_namewherekey_part1=10andk ey_part2in(10,20,30);( 10) index该联接类型与all 相同 , 除了扫描索引树. 其他情况都比 all 快 ,因为索引文件通常比数据文件小 .当查询只使用作为单索引一部分的列时,mysql 可以使用该联接类型 .11) all对于每个来自于先前的表的行组合, 进行完

14、整的表扫描如果第一个表没标记为 const, 这样执行计划就不会很好通常可以增加更多的索引来摆脱all, 使得行能基于前面的表中的常数值或列值被检索出 .possible_keyspossible_keys 列指出 mysql 能供给使用的索引键有哪些 . 注意 ,该列完全独立于 explain 输出所示的表的次序 .这意味着在possible_keys 中的某些索引键实际上不能按生成的表次序使用 .如果该列是null, 则没有相关的索引 . 在这种情况下 , 可以通过检查 where 子句查看是否可以引用某些列或适合的索引列来提高查询性能 .如果是这样, 创造一个适当的索引并且再次用 exp

15、lain 检查查询 .如果要查询一张表有什么索引 , 可以使用showindexfromtbl_namekeykey 列显示 mysql 实际决定使用的键（索引） . 如果没有选择索 引, 那么可能列的值是null.要想强制 mysql 使用或忽略possible_keys 列中的索引 , 在查询中可以使用forceindex- 强逼使用某个索引 useindex- 使用某个索引ignoreindex- 忽略某个索引对于myisam 引擎和bdb 引擎的表 , 运行 analyzetable 可以帮助优化器选择更好的索引 .对于 myisam表，可以使用 myisamchk-analyze.k

16、ey_lenkey_len 列显示 mysql 决定使用的索引键的长度（按字节计算）如果键是 null, 则长度为 null.注意通过 key_len 值我们可以确定mysql 将实际使用一个多索引键索引的几个字段.refref 列显示使用哪个列或常数与索引一起查询记录 .rowsrows 列显示 mysql 预估执行查询时必须要检索的行数.extra该列包含 mysql 处理查询时的详细信息 . 下面解释了该列可以显示的不同的文本字符串 :distinctmysql 发现第 1 个匹配行后 , 停止为当前的行组合搜索更多的行.notexistsmysql 能够对查询进行leftjoin 优化

17、 , 发现 1 个匹配 leftjoin标准的行后 , 不再为前面的的行组合在该表内检查更多的行 .下面是一个可以这样优化的查询类型的例子:select*fromt1leftjoint2ont1.id=t2.idwheret2.idisnull;假定 t2.id 定义为 notnull. 在这种情况下 ,mysql 使用 t1.id 的 值扫描 t1 并查找 t2 中的行 .如果 mysql 在 t2 中发现一个匹配的行, 它知道 t2.id 绝不会为null, 并且不再扫描t2 内有相同的 id 值的行 . 换句话说 , 对于 t1 的每个行 ,mysql 只需要在 t2 中查找一次, 无论

18、 t2 内实际有多少匹配的行 .rangecheckedforeachrecord(indexmap:#)mysql 没有发现好的可以使用的索引 , 但发现如果来自前面的表的列值已知 , 可能部分索引可以使用 .对前面的表的每个行组合,mysql 检查是否可以使用 range 或index_merge 访问方法来获取行.这并不很快, 但比执行没有索引的联接要快得多 .可以参考一下这篇文章:里面就提到了 rangecheckedforeachrecordusingfilesortmysql 需要额外的一次传递, 以找出如何按排序顺序检索行.通过根据联接类型浏览所有行并为所有匹配where子句的行

19、保存排序关键字和行的指针来完成排序 .然后关键字被排序 , 并按排序顺序检索行如果是 orderby 操作就会用到这个usingfilesort, 当然filesort 不是指使用文件来排序, 大家不要误会了 .usingindex从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息 . 当查询只使用作为单一索引一部分的列时, 可以使用该策略 .usingtemporary为了解决查询 ,mysql 需要创建一个临时表来容纳结果.典型情况如查询包含可以按不同情况列出列的 groupby 和orderby 子句时 .一般用到临时表都会看到 usingtemporaryusing

20、wherewhere子句用于限制哪一个行匹配下一个表或发送到客户端除非你专门从表中索取或检查所有行, 如果 extra 值不为usingwhere 并且表联接类型为 all 或 index, 查询可能会有一些错误usingindexforgroup-by类似于访问表的 usingindex 方式 ,usingindexforgroup-by 表示 mysql 发现了一个索引 , 可以用来查询 groupby 或 distinct 查询的 所有列 ,而不要额外搜索硬盘访问实际的表. 并且 , 按最有效的方式使用索引, 以便对于每个组, 只读取少量索引条目 .descibe 语句的使用方法与 ex

21、plain 语句是一样的 , 并且分享结果也是一样的 descibe 语句的语法如下describeselectselect_optionsdescibe 可以缩写成desc（ 2）索引对查询速度的影响mysql 中提高性能的一个最有效的方式就是对数据表设计合理的索引 . 索引提供了高效访问数据的方法 , 并且加快查询速度因此索引对查询速度有着至关重要的影响 .如果查询没有索引 , 查询语句将扫描表中所有记录 . 在数据量大的情况下 , 这样查询的速度会很慢. 如果使用索引进行查询 ,查询语句可以根据索引快速定位到待查询记录 , 从而减少查询的记录数 , 达到提高查询速度的目的 .下面是查询语

22、句中不使用索引和使用索引的对比 , 首先分析未使用索引的查询情况,explain 语句执行如下explainselect'id','name'from'test'.'emp'where'name'="nihao"可以看到 ,rows 列的值是3说“select'id','name'from'test'.'emp'where'name'="nihao" ” 语句扫描了表中的 3 条记录然后在emp表

23、加上索引createindexix_emp_nameonemp（name）现在再分析上面的查询语句 , 执行的 explain 语句结果如下结果显示 ,rows 列的值为 1.这表示这个查询语句只扫描了表中的一条记录 , 其他查询速度自然比扫描3 条记录快 .而且 possible_keys 和key的值都是ix_emp_name这说明查询 时使用了 ix_emp_name索弓I如果表中记录有100条、 1000条、 10000条优势就显现出来了（ 3）使用索引查询索引可以提高查询速度, 但并不是使用带有索引的字段查询时, 索引都会起作用 .下面的几种情况跟跟sqlserver 一样 , 有可

24、能用不到索引（ 1）使用 like 关键字的查询语句使用 like 关键字进行查询的时候, 如果匹配字符串的第一个字符为“ %” , 索引不起作用 . 只有“%”不在第一个位置, 索引才会起作用使用 like 关键字 , 并且匹配字符串中含有“%”字符,explain 语句如下usetest;explainselect*from'test'.'emp'where'name'like"%xH.usetest;explainselect*from'test'.'emp'where'name'l

25、ike"x%H.name上有索弓 I ix_emp_name第一个查询type 为 all, 表示要全表扫描第二个查询type 为 index, 表示会扫描索引like 关键字是否能利用上索引跟sqlserver 是一样的我之前写过一篇文章:（ 2）使用多列索引的查询语句mysql 可以为多个字段创建索引 . 一个索引可以包括16个字段（跟 sqlserver 一样）对于多列索引 , 只有查询条件中使用了这些字段中的第一个字段时, 索引才会被使用 , 这个字段叫 :在表 person 中 name,age 字段创建多列索引, 验证多列索引的情况createindexix_person

26、_name_ageon'person'（name,age）explain selectid,name,age,jobfrom'person'where'name'="suse"explainselectid,name,age,jobfrom'person'where'age'=12从第一条查询看出，where'name'="suse"的记录有一条，扫描了一条记录并且使用了 ix_person_name_age 索引从第二条记录可以看出 ,rows 列的值为 4

27、,说明共扫描了4 条记录 ,并且 key 列值为 null, 说明explainselectid,name,age,jobfrom'person'where'age'=12语句并没有使用索引.因为age字段是多列索引的第二个字段，只 有查询条件中使用了 nam#段才会使用ix_person_name_age索引这个跟 sqlserver 是一样的 , 详细请看 :（ 3)使用 or 关键字的查询语句查询语句的查询条件中只有 or 关键字 , 而且 or 前后的两个条件中的列都是索引时, 查询中才使用索引 , 否则 , 查询不使用索引查询语句使用 or 关键字的情

28、况我们再创建一个索引createindexix_person_ageon'person'(age)explainselectname,agefrom'person'where'name'="suse"or'jo b'="sportman"explainselectname,agefrom'person'where'age'=2or'name'="s use大家要注意, 这里跟刚才不一样, 这次我们 select 的字段只有nam褥口

29、age,而不是select 出全部字段因为并没有在job 这个字段上建立索引 , 所以第一个查询使用的是全表扫描第二个查询因为nam弃段和age字段都有索引，那么mysql可以 利用这两个索引的其中之一, 这里是 ix_person_name_age 索引来查找记录利用索引来查找记录会快很多（ 4）优化子查询mysql 从 4.1 版本开始支持子查询 , 使用子查询可以进行select语句的嵌套查询 , 即一个 select 查询的结果作为另一个select 语句的条件子查询可以一次性完成很多逻辑需要多个步骤才能完成的 sql 操 作 . 子查询虽然使查询语句灵活 , 但是执行效率不高 .执行

30、子查询时,mysql 需要为内层查询语句结果建立一个临时表 .然后外层查询语句从临时表中查询记录查询完毕后, 再撤销临时表. 因此 , 子查询的速度会受到一定影响 ,如果查询的数据量特别大, 这种影响就会更大.在 mysql 中, 可以使用连接（ join ）查询来代替子查询 . 连接查询不需要建立临时表, 其速度比子查询快, 如果查询中使用索引的话, 性能会更好 .所以很多网上的文章都说尽量使用 join 来代替子查询 , 虽然网上也说 mysql5.7 对于子查询有很大的改进, 但是如果不是使用mysql5.7 还是需要注意的如果系统中 join 语句特别多还需要注意修改 my.ini 或

31、 f文件中的 join_buffer_size 大小 , 预防性能问题优化数据库结构一个好的数据库设计方案对于数据库的性能常常起到事半功倍的效果 .数据库结构的设计需要考虑数据冗余、查询和更新速度、字段的数据类型是否合理等多方面（ 1）将字段很多的表拆分成多个表有时候有些字段使用频率很低或者字段的数据类型比较大, 那么可以考虑垂直拆分的方法, 把不常用的字段和大字段拆分出去（ 2）增加中间表对于需要经常联合查询的表, 可以建立中间表以提高查询效率. 通过建立中间表, 把需要经常联合查询的数据插入到中间表中 ,然后将原来的联合查询改为对中间表的查询 , 以此来提高查询效 率.（ 3）增加冗余字段

32、设计数据库表时应尽量遵循范式理论, 尽可能减少冗余字段, 但是现今存储硬件越来越便宜 , 有时候查询数据的时候需要join 多个表这样在高峰期间会影响查询的效率 , 我们需要反范式而为之, 增加一些必要的冗余字段, 以空间换时间需要这样做会增加开发的工作量和维护量 , 但是如果能换来可观的性能提升, 这样做也是值得的（ 4）优化插入记录的速度插入记录时, 影响插入速度的主要是索引、唯一性校验、一次插入记录条数等 .根据实际情况, 可以分别进行优化对于myisam表，常见优化方法如下1、禁用索引对于非空表, 插入记录时,mysql 会根据表的索引对插入的记录建立索引 . 如果插入大量数据, 建立

33、索引会降低插入记录的速度.为了解决这个问题, 可以在插入记录之前禁用索引, 数据插入完毕后再开启索引禁用索引语句如下:altertabletable_namedisablekeys;其中 table_name 是禁用索引的表的表名重新开启索引语句如下 :altertabletable_nameenablekeys;对于空表批量导入数据，则不需要进行此操作，因为myisam表是 在导入数据之后才建立索引！2、禁用唯一性检查插入数据时,mysql 会对插入的记录进行唯一性校验. 这种唯一性校验也会降低插入记录的速度.为了降低这种情况对查询速度的影响 , 可以在插入记录之前禁用唯一性检查, 等到记录

34、插入完毕之后再开启禁用唯一性检查的语句如下:setunique_checks=0;开启唯一性检查的语句如下:setunique_checks=1;3、使用批量插入插入多条记录时, 可以使用一条insert 语句插入一条记录 , 也可以使用一条insert 语句插入多条记录 .第一种情况insertintoemp(id,name)values(1,"suse");insertintoemp(id, name)values(2,"lily");insertintoemp(id,name)values(3,"tom");第二种情况inser

35、tintoemp(id,name)values(1,"suse"),(2,"lily"),(3,"to m")第二种情况要比第一种情况要快4、使用loaddatainfile 批量导入当需要批量导入数据时, 如果能用 loaddatainfile 语句 , 就尽量使用 . 因为 loaddatainfile 语句导入数据的速度比 insert 语句快很 多对于 innodb 引擎的表 , 常见的优化方法如下 :1、禁用唯一性检查插入数据时,mysql 会对插入的记录进行唯一性校验. 这种唯一性校验也会降低插入记录的速度.为了降低这种情

36、况对查询速度的影响 , 可以在插入记录之前禁用唯一性检查, 等到记录插入完毕之后再开启禁用唯一性检查的语句如下:setunique_checks=0;开启唯一性检查的语句如下:setunique_checks=1;2、禁用外键约束插入数据之前执行禁止对外键的检查, 数据插入完成之后再恢复对外键的检查 . 禁用外键检查的语句如下 :setforeign_key_checks=0;恢复对外键的检查语句如下setforeign_key_checks=1;3、禁止自动提交插入数据之前禁止事务的自动提交 , 数据导入完成之后 , 执行恢复自动提交操作或显式指定事务(5)分析表、检查表、优化表、修复表和c

37、hecksum表mysql 提供了分析表、检查表和优化表的语句分析表主要是分析关键字的分布；检查表主要是检查表是否存在错误；优化表主要是消除删除或者更新造成的空间浪费修复表主要对myisam表文件进行修复checksum表主要对表数据传输前和传输后进行比较1、分析表mysql 中提供了 analyzetable 语句分析表 ,analyzetable 语句的基本语法如下analyzelocal|no_write_to_binlogtabletbl_name,tbl_name.local 关键字是 no_write_to_binlog 关键字的别名 , 二者都是执行过程不写入二进制日志 ,tbl

38、_name 为分析的表的表名可以有一个或多个使用 analyzetable 分析表的过程中 , 数据库系统会自动对表加一个只读锁 . 在分享期间 , 只能读取表的记录 , 不能更新和插入记录analyzetable 语句能分析innodb、bdb和myisam类型的表 使用analyzetable 来分析em速，执行语句如下： analyzetableemp;上面结果显示说明table: 表示分析的表名op: 表示执行的操作,analyze 表示进行分析操作msg_type: 表示信息类型其值通常是状态（ status ）、信息（info ）、注意（note）、警告（warning ）和错误（

39、error ）之一msg_text: 显示信息实际上分析表跟sqlserver 里的更新统计信息是差不多的主要就是为了索引的基数更加准确 , 从而使查询优化器能够更加准确的预估行数emp表的记录行数是18分析表之后 ,cardinality 基数更加准确了2、检查表mysql 中使用 checktable 语句来检查表.checktable 语句能够检查innodb和myisam类型的表是否存在错误.对于myisam类型的表,checktable语句还会更新关键字统计数据 . 而且 ,checktable 也可以检查视图是否有错误,比如在视图定义中被引用的表已不存在.该语句基本语法如下 :ch

40、ecktabletbl_name,tbl_name.option.option=quick|fast|medium|extended|changed其中 ,tbl_name 是表名； option 参数有 5个取值分别是quick 、fast 、 medium、 extended 、 changed各个选项的意思分别是quick: 不扫描行 , 不检查错误的连接fast: 只检查没有被正确关闭的表medium:扫描行，以验证被删除的连接是有效的，也可以计算各行的关键字校验和 , 并使用计算出的校验和验证这一点extended: 对每行的所有关键字进行一个全面的关键字查找. 这可以确保表是100

41、%一致的, 但是花的时间较长changed: 只检查上次检查后被更改的表和没有被正确关闭的表option 只对 myisam表有效,对innodb 表无效.checktable 语句在执行过程中也会给表加上只读锁 .3、优化表mysql 中使用 optimizetable 语句来优化表. 该语句对 innodb 和myisam表都有效.但是,optimizetable 语句只能优化表中的varchar 、 blob 、 text 类型的字段optimizetable 语句的基本语法如下 :optimizelocal|no_write_to_binlogtabletbl_name,tbl_nam

42、 e.local 和 no_write_to_binlog 关键字的意义和分析表相同 , 都是指定不写入二进制日志tbl_name 是表名通过 optimizetable 语句可以消除删除和更新造成的文件碎片 .optimizetable 语句在执行过程中也会给表加上只读锁 .提示 : 一个表使用了 text 或者 blob 这样的数据类型 , 如果已经删除了表的一大部分, 或者已经对含有可变长度行的表（含有 varchar 、blob 或 text 列的表）进行了很多更新, 则应使用 optimizetable 来重新利用未使用的空间 , 并整理数据文件的碎片 . 在多数设置中 , 根本不需

43、要运行optimizetable.即使对可变长度的行进行了大量更新, 也不需要经常运行, 每周一次或每月一次即可, 并且只需要对特定表进行optimizetableoptimizetable 语句类似于 sqlserver 的重建索引和收缩数据文件的功能4、修复表mysql中使用 repairtable 来修复 myisam表，只对 myisam和archive 类型的表有效.repairlocal|no_write_to_binlogtabletbl_name,tbl_name.option.option=quick|extended|use_frm选项的意思分别是:quick: 最快的选项

44、 , 只修复索引树.extended: 最慢的选项 , 需要逐行重建索引 .use_frm: 只有当 myi 文件丢失时才使用这个选项 , 全面重建整个索引 .与 analyzetable 一样 ,repairtable 也可以使用 local 来取消写入 binlog.5、checksum表数据在传输时, 可能会发生变化 , 也有可能因为其它原因损坏, 为了保证数据的一致，我们可以计算checksum （校验值）.使用myisam引擎的表会把checksum存储起来，称为livechecksum, 当数据发生变化时,checksum 会相应变化 .语法如下 :checksumtabletbl

45、_name,tbl_name.quick|extendedquick:表示返回存储的 checksum值extended: 表示重新计算checksum如果没有指定选项 , 则默认使用 extended.checksum表主要用来对比在传输表数据之前和表数据之后，表的数据是否发生了变化 , 例如插入了数据或者删除了数据, 或者有数据损坏checksum值都会改变.优化 mysql 服务器水电费优化 mysql 服务器主要从两个方面入手, 一方面是对硬件进行优化；另一方面是对mysql 服务器的参数进行优化1、优化服务器硬件服务器的硬件性能直接决定着 mysql 数据库的性能 . 硬件的性能瓶颈

46、直接决定mysql 数据库的运行速度和效率.优化服务器硬件的几种方法（ 1）配置较大的内存. 足够大的内存, 是提高 mysql 数据库性能之一 . 内存速度比磁盘i/o 快得多 , 可以通过增加系统缓冲区容量, 使数据库在内存停留时间更长, 以减少磁盘i/o（ 2）配置高速磁盘系统, 以减少读盘等待时间 , 提高响应速度（ 3）合理分布磁盘i/o, 把磁盘 i/o 分散在多个设备上 , 以减少资源竞争 , 提高并行操作能力（ 4）配置多处理器,mysql 是多线程的数据库, 多处理器可同时执行多个线程2、优化mysql 的参数通过优化 mysql 的参数可以提高资源利用率, 从而达到提高my

47、sql 服务器的性能的目的 .mysql 服务器的配置参数都在f 或者 my.ini 文件的mysqld 组中 .下面对几个对性能影响较大的参数进行介绍我们先看一下与网络连接的性能配置项及对性能的影响. max_conecctions: 整个 mysql 允许的最大连接数；这个参数主要影响的是整个mysql 应用的并发处理能力 , 当系统中实际需要的连接量大于 max_conecctions 的情况下 , 由于 mysql 的设置限制 , 那么应用中必然会产生连接请求的等待, 从而限制了相应的并发量. 所以一般来说 , 只要 mysql 主机性能允许, 都是将该参数设置的尽可能大一点 . 一般

48、来说 500 到 800 左右是一个比较合适的参考值 max_user_connections: 每个用户允许的最大连接数；上面的参数是限制了整个mysql 的连接数 , 而 max_user_connections 则是针对于单个用户的连接限制. 在一般情况下我们可能都较少使用这个限制只有在一些专门提供mysql 数据存储服务, 或者是提供虚拟主机服务的应用中可能需要用到. 除了限制的对象区别之外, 其他方面和max_connections 一样 . 这个参数的设置完全依赖于应用程序的连接用户数 , 对于普通的应用来说, 完全没有做太多的限制 , 可以尽量放开一些 . net_buffer_

49、length: 网络包传输中 , 传输消息之前的netbuffer 初始化大小；这个参数主要可能影响的是网络传输的效率 , 由于该参数所设置的只是消息缓冲区的初始化大小 , 所以造成的影响主要是当我们的每次消息都很大的时候mysql 总是需要多次申请扩展该缓冲区大小 . 系统默认大小为 16kb, 一般来说可以满足大多数场景 , 当然如果我们的查询都是非常小 , 每次网络传输量都很少 , 而且系统内存又比较紧缺的情况下 , 也可以适当将该值降低到8kb. max_allowed_packet: 在网络传输中 , 一次传消息输量的最大值；这个参数与net_buffer_length相对应 , 只

50、不过是 netbuffer 的最大值 . 当我们的消息传输量大于 net_buffer_length 的设置时 ,mysql 会自动增大netbuffer 的大小 , 直到缓冲区大小达到max_allowed_packet所设置的值.系统默认值为1mb,最大值是1gb, 必须设定为 1024的倍数 , 单位为字节. back_log: 在 mysql 的连接请求等待队列中允许存放的最大连接请求数. 连接请求等待队列 , 实际上是指当某一时刻客户端的连接请求数量过大的时候,mysql 主线程没办法及时给每一个新的连接请求分配（或者创建）连接线程的时候 , 还没有分配到连接线程的所有请求将存放在一

51、个等待队列中 ,这个队列就是mysql 的连接请求队列 . 当我们的系统存在瞬时的大量连接请求的时候, 则应该注意back_log 参数的设置. 系统默认值为50, 最大可以设置为 65535. 当我们增大back_log 的设置的时候, 同时还需要主义os 级别对网络监听队列的限制 , 因为如果 os 的网络监听设置小于 mysql 的 back_log 设置的时候, 我们加大“ back_log ”设置是没有意义的 . 上面介绍了网络连接交互相关的主要优化设置 ,下面我们再来看看与每一个客户端连接想对应的连接线程. 在 mysql中, 为了尽可提高客户端请求创建连接这个过程的性能 , 实现

52、了一个threadcache 池 , 将空闲的连接线程存放在其中 , 而不是完成请求后就销毁 . 这样 , 当有新的连接请求的时候,mysql 首先会检查threadcache 池中是否存在空闲连接线程, 如果存在则取出来直接使用, 如果没有空闲连接线程, 才创建新的连接线程. 在 mysql 中与连接线程相关的系统参数及状态变量说明如下 : thread_cache_size:threadcache 池中应该存放的连接线程数当系统最初启动的时候, 并不会马上就创建thread_cache_size 所设置数目的连接线程存放在threadcache 池中 , 而是随着连接线程的创建及使用 ,

53、慢慢的将用完的连接线程存入其中 . 当存放的连接线程达到 thread_cache_size 值之后 ,mysql 就不会再续保存用完的连接线程了 . 如果我们的应用程序使用的短连接,threadcache 池的功效是最明显的 . 因为在短连接的数据库应用中 , 数据库连接的创建和销毁是非常频繁的 , 如果每次都需要让mysql 新建和销毁相应的连接线程那么这个资源消耗实际上是非常大的 , 而当我们使用了 threadcache之后 , 由于连接线程大部分都是在创建好了等待取用的状态, 既不需要每次都重新创建, 又不需要在使用完之后销毁, 所以可以节省下大量的系统资源. 所以在短连接的应用系统

54、中 ,thread_cache_size 的值应该设置的相对大一些, 不应该小于应用系统对数据库的实际并发请求数 . 而如果我们使用的是长连接的时候,threadcache 的功效可能并没有使用短连接那样的大, 但也并不是完全没有价值. 因为应用程序即使是使用了长连接, 也很难保证他们所管理的所有连接都能处于很稳定的状态, 仍然会有不少连接关闭和新建的操作出现. 在有些并发量较高，应用服务器数量较大的系统中，每分钟十来次的连接创建与关闭的操作是很常见的 . 而且如果应用服务器的连接池管理不是太好 , 容易产生连接池抖动的话, 所产生的连接创建和销毁操作将会更多 . 所以即使是在使用长连接的应用

55、环境中 ,threadcache 机制的利用仍然是对性能大有帮助的 . 只不过在长连接的环境中我们不需要将 thread_cache_size 参数设置太大, 一般来说可能50到 100之间应该就可以了 . thread_stack: 每个连接线程被创建的时候,mysql给他分配的内存大小 . 当 mysql 创建一个新的连接线程的时候, 是需要给他分配一定大小的内存堆栈空间 , 以便存放客户端的请求query以及自身的各种状态和处理信息 . 不过一般来说如果不是对mysql 的连接线程处理机制十分熟悉的话, 不应该轻易调整该参数的大小 , 使用系统的默认值(192kb)基本上可以所有的普通应

56、用环境.如果该 值设置太小 , 会影响 mysql 连接线程能够处理客户端请求的 query 内 容的大小 , 以及用户创建的 procedures 和 functions 等计算出系统 新建连接连接的 threadcache 命中率 , 也就是通过threadcache 池中取得连接线程的次数与系统接收的总连接次数的比率, 如下 :threads_cache_hit=(connections- threads_created)/connections*100% 我们可以通过上面的这个运算 公式计算一下上面环境中的 threadcache 命中率:thread_cache_hit=(127-12)/127*100%=90.55%一般来说，当系统稳定运行一段时间之后 , 我们的 threadcache 命中率应该保持在 90% 左右甚至更高的比率才算正常. 可以看出上面环境中的 threadcache命中比率基本还算是正常的.tablecache 相关的优化我们先来看一下 mysql 打开表的相关机制. 由于多线程的实现