我的MYSQL学习心得.docx
《我的MYSQL学习心得.docx》由会员分享,可在线阅读,更多相关《我的MYSQL学习心得.docx(19页珍藏版)》请在冰豆网上搜索。
我的MYSQL学习心得
我的MYSQL学习心得
我的mysql学习心得(十六)优化
一步一步走来已经写到了第十六篇了~
这一篇主要介绍mysql的优化,优化mysql数据库是dba和开发人员的必备技能
mysql优化一方面是找出系统瓶颈,提高mysql数据库整体性能;另一方面需要合理的结构设计和参数调整,以提高
用户操作响应的速度;同时还有尽可能节省系统资源,以便系统可以提供更大负荷的服务
如果大家看过我写的两篇文章,那么学习mysql的索引就不会太难,因为是相通的
其实mysql也有sqlserver堆表的概念
myisam允许没有任何索引和主键的表存在,个人觉得没有主键的myisam表都属于堆表,因为mysql不支持非主键的聚集索引.
innodb引擎如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见)
详细参考:
不过《myisamvsinnodb:
mysql存储引擎详解》文章也有一点错误,意向共享锁就是表锁,其实是不对的
1、优化简介
mysql优化是多方面的,原则是减少系统的瓶颈,减少资源的占用,增加系统的反应速度.
例如,通过优化文件系统,提高磁盘i/o的读写速度;通过优化操作系统调度策略,提高mysql在高负荷情况下
的负载能力;优化表结构、索引、查询语句等使查询响应更快
在mysql中,可以使用showstatus语句查询一些mysql的性能参数
showstatuslike"value";
其中value是要查询的参数值,一些常用性能参数如下:
connections:
连接mysql服务器的次数
uptime:
mysql服务器的上线时间
slow_queries:
慢查询的次数
com_select:
查询操作次数
com_insert:
插入操作次数
com_update:
更新操作次数
com_delete:
删除操作次数
如果查询mysql服务器的连接次数,可以执行如下语句
showstatuslike"connections";
如果查询mysql服务器的慢查询次数,可以执行如下语句
showstatuslike"slow_queries";
2、优化查询
查询是数据库最频繁的操作,提高查询速度可以有效地提高mysql数据库的性能
(1)分析查询语句
通过对查询语句的分析,可以了解查询语句的执行情况找出查询语句执行的瓶颈
mysql中提供了explain语句和describe语句,用来分析查询语句
explain语句的基本语法
explain[extended]selectselect_option
使用extended关键字,explain语句将产生附加信息.select_option是select语句的查询选项,包括fromwhere子句等
执行该语句,可以分析explain后面的select语句的执行情况,并且能够分析所查询的表的一些特征
使用explain语句来分析1个查询语句
usetest;explainextendedselect*fromperson;
下面对结果进行解释
·id
select识别符.这是select的查询序列号.
·select_type
select类型,可以为以下任何一种:
simple:
简单select(不使用union或子查询)
primary:
表示主查询,或者是最外层的查询语句(多表连接的时候)
union:
表示连接查询的第二个或后面的查询语句
dependentunion:
union连接查询中的第二个或后面的select语句,取决于外面的查询
unionresult:
union连接查询的结果
subquery:
子查询中的第一个select语句
dependentsubquery:
子查询中的第一个select语句,取决于外面的查询
derived:
导出表的select(from子句的子查询)
·table
表示查询的表
·type
表示表的联接类型
下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
(1)system
表仅有一行(=系统表).这是const联接类型的一个特例.
(2)const
表最多只有一个匹配行,它将在查询开始时被读取.余下的查询优化中被作为常量对待.const表查询速度很快,因为它们只读取一次.
const用于常数值比较primarykey或unique索引的所有部分的场合.
在下面的查询中,tbl_name可以用于const表:
(3)eq_ref
对于每个来自于前面的表的行组合,从该表中读取一行.这可能是最好的联接类型,除了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_column_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_tablewhereref_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_tablewherekey_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,不过索引类型不需要是唯一索引,可以替换in子查询,但只适合下列形式的子查询中的非唯一索引:
valuein(selectkey_columnfromsingle_tablewheresome_expr)
(9)range
只检索给定范围的行,使用一个索引来检索行数据.key列显示使用了哪个索引,key_len显示所使用索引的长度.
在该类型中ref列为null.
当使用=、<>、>、>=、<、<=、isnull、<=>、between或者in操作符,用常量比较关键字列时,类型为range.
下面介绍几种检索指定行数据的情况
select*fromtbl_namewherekey_column=10;select*fromtbl_namewherekey_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,使得行能基于前面的表中的常数值或列值被检索出.
possible_keys
possible_keys列指出mysql能供给使用的索引键有哪些.注意,该列完全独立于explain输出所示的表的次序.
这意味着在possible_keys中的某些索引键实际上不能按生成的表次序使用.
如果该列是null,则没有相关的索引.在这种情况下,可以通过检查where子句查看是否可以引用某些列或适合的索引列来提高查询性能.
如果是这样,创造一个适当的索引并且再次用explain检查查询.
如果要查询一张表有什么索引,可以使用
showindexfromtbl_name
key
key列显示mysql实际决定使用的键(索引).如果没有选择索引,那么可能列的值是null.
要想强制mysql使用或忽略possible_keys列中的索引,在查询中可以使用
forceindex--强逼使用某个索引useindex--使用某个索引ignoreindex--忽略某个索引
对于myisam引擎和bdb引擎的表,运行analyzetable可以帮助优化器选择更好的索引.
对于myisam表,可以使用myisamchk--analyze.
key_len
key_len列显示mysql决定使用的索引键的长度(按字节计算).如果键是null,则长度为null.
注意通过key_len值我们可以确定mysql将实际使用一个多索引键索引的几个字段.
ref
ref列显示使用哪个列或常数与索引一起查询记录.
rows
rows列显示mysql预估执行查询时必须要检索的行数.
extra
该列包含mysql处理查询时的详细信息.下面解释了该列可以显示的不同的文本字符串:
distinct
mysql发现第1个匹配行后,停止为当前的行组合搜索更多的行.
notexists
mysql能够对查询进行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没有发现好的可以使用的索引,