我的MYSQL学习心得.docx

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

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

3、e;其中value是要查询的参数值,一些常用性能参数如下:connections:连接mysql服务器的次数uptime:mysql服务器的上线时间slow_queries:慢查询的次数com_select:查询操作次数com_insert:插入操作次数com_update:更新操作次数com_delete:删除操作次数如果查询mysql服务器的连接次数,可以执行如下语句showstatuslikeconnections;如果查询mysql服务器的慢查询次数,可以执行如下语句showstatuslikeslow_queries;2、优化查询查询是数据库最频繁的操作,提高查询速度可以有效地提高m

4、ysql数据库的性能（1）分析查询语句通过对查询语句的分析,可以了解查询语句的执行情况找出查询语句执行的瓶颈mysql中提供了explain语句和describe语句,用来分析查询语句explain语句的基本语法explainextendedselectselect_option使用extended关键字,explain语句将产生附加信息.select_option是select语句的查询选项,包括fromwhere子句等执行该语句,可以分析explain后面的select语句的执行情况,并且能够分析所查询的表的一些特征使用explain语句来分析1个查询语句usetest;explainex

5、tendedselect*fromperson;下面对结果进行解释idselect识别符.这是select的查询序列号.select_typeselect类型,可以为以下任何一种:simple:简单select(不使用union或子查询)primary:表示主查询,或者是最外层的查询语句（多表连接的时候）union:表示连接查询的第二个或后面的查询语句dependentunion:union连接查询中的第二个或后面的select语句,取决于外面的查询unionresult:union连接查询的结果subquery:子查询中的第一个select语句dependentsubquery:子查询中的第

6、一个select语句,取决于外面的查询derived:导出表的select(from子句的子查询)table表示查询的表type表示表的联接类型下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:（1）system表仅有一行(=系统表).这是const联接类型的一个特例.（2）const表最多只有一个匹配行,它将在查询开始时被读取.余下的查询优化中被作为常量对待.const表查询速度很快,因为它们只读取一次.const用于常数值比较primarykey或unique索引的所有部分的场合.在下面的查询中,tbl_name可以用于const表:（3）eq_ref对于每个来自于前面的表的行组合,

7、从该表中读取一行.这可能是最好的联接类型,除了const类型.它用在一个索引的所有部分被联接使用并且索引是unique或primarykey时.eq_ref可以用于使用“=”操作符比较的带索引的列.比较值可以为常量或一个使用在该表前面所读取的表的列的表达式.在下面的例子中,mysql可以使用eq_ref联接来处理ref_tables:select*fromref_table,other_tablewhereref_table.key_column=other_table.column;select*fromref_table,other_tablewhereref_table.key_colu

8、mn_part1=other_table.columnandref_table.key_column_part2=1;（4）ref对于每个来自于前面的表的任意行组合,将从该表中读取所有匹配的行.如果联接只使用索引键的最左边的前缀,或如果索引键不是unique或primarykey,则使用ref.如果使用的键仅仅匹配少量行,该联接类型是不错的.ref可以用于使用=或操作符的带索引的列.在下面的例子中,mysql可以使用ref联接来处理ref_tables:select*fromref_tablewherekey_column=expr;select*fromref_table,other_tab

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

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

11、是唯一索引,可以替换in子查询,但只适合下列形式的子查询中的非唯一索引:valuein(selectkey_columnfromsingle_tablewheresome_expr)（9）range只检索给定范围的行,使用一个索引来检索行数据.key列显示使用了哪个索引,key_len显示所使用索引的长度.在该类型中ref列为null.当使用=、=、=、isnull、between或者in操作符,用常量比较关键字列时,类型为range.下面介绍几种检索指定行数据的情况select*fromtbl_namewherekey_column=10;select*fromtbl_namewhereke

12、y_columnbetween10and20;select*fromtbl_namewherekey_columnin(10,20,30);select*fromtbl_namewherekey_part1=10andkey_part2in(10,20,30);（10）index该联接类型与all相同,除了扫描索引树.其他情况都比all快,因为索引文件通常比数据文件小.当查询只使用作为单索引一部分的列时,mysql可以使用该联接类型.（11）all对于每个来自于先前的表的行组合,进行完整的表扫描.如果第一个表没标记为const,这样执行计划就不会很好.通常可以增加更多的索引来摆脱all,使得行

13、能基于前面的表中的常数值或列值被检索出.possible_keyspossible_keys列指出mysql能供给使用的索引键有哪些.注意,该列完全独立于explain输出所示的表的次序.这意味着在possible_keys中的某些索引键实际上不能按生成的表次序使用.如果该列是null,则没有相关的索引.在这种情况下,可以通过检查where子句查看是否可以引用某些列或适合的索引列来提高查询性能.如果是这样,创造一个适当的索引并且再次用explain检查查询.如果要查询一张表有什么索引,可以使用showindexfromtbl_namekeykey列显示mysql实际决定使用的键（索引）.如果没

14、有选择索引,那么可能列的值是null.要想强制mysql使用或忽略possible_keys列中的索引,在查询中可以使用forceindex-强逼使用某个索引useindex-使用某个索引ignoreindex-忽略某个索引对于myisam引擎和bdb引擎的表,运行analyzetable可以帮助优化器选择更好的索引.对于myisam表,可以使用myisamchk-analyze.key_lenkey_len列显示mysql决定使用的索引键的长度（按字节计算）.如果键是null,则长度为null.注意通过key_len值我们可以确定mysql将实际使用一个多索引键索引的几个字段.refref列

15、显示使用哪个列或常数与索引一起查询记录.rowsrows列显示mysql预估执行查询时必须要检索的行数.extra该列包含mysql处理查询时的详细信息.下面解释了该列可以显示的不同的文本字符串:distinctmysql发现第1个匹配行后,停止为当前的行组合搜索更多的行.notexistsmysql能够对查询进行leftjoin优化,发现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中查找一次,无论t2内实际有多少匹配的行.rangecheckedforeachrecord(indexmap:#)mysql没有发现好的可以使用的索引,