mysql优化

数据库优化一般来说，要保证数据库的效率，要做好以下四个方面的工作：①数据库设计②sql语句优化③数据库参数配置④恰当的硬件资源和操作系统数据库表设计通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；(只要是关系型数据库都满足1NF)第二范式：2NF是对记录的惟一性约束(设置主键)，要求记录有惟一标识，即实体的惟一性；第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。正确认识数据冗余主键与外键在多表中的重复出现，不属于数据冗余，这个概念必须清楚，事实上有许多人还不清楚。非键字段的重复出现，才是数据冗余！而且是一种低级冗余，即重复性的冗余。高级冗余不是字段的重复出现，而是字段的派生出现。例1：派生性冗余(推荐)商品中的“单价、数量、金额”三个字段，“金额”就是由“单价”乘以“数量”派生出来的，它就是冗余，而且是一种高级冗余。冗余的目的是为了提高处理速度。只有低级冗余才会增加数据的不一致性，因为同一数据，可能从不同时间、地点、角色上多次录入。因此，我们提倡高级冗余(派生性冗余)，反对低级冗余(重复性冗余)。例2：重复性冗余(反对)为了提高学生活动记录的检索效率，把单位名称冗余到学生活动记录表里。单位信息有500条记录，而学生活动记录在一年内大概有200万数据量。如果学生活动记录表不冗余这个单位名称字段，只包含三个int字段和一个timestamp字段，只占用了16字节，是一个很小的表。而冗余了一个varchar(32)的字段后则是原来的3倍，检索起来相应也多了这么多的I/O。而且记录数相差悬殊，500VS2000000，导致更新一个单位名称还要更新4000条冗余记录。由此可见，这个冗余根本就是适得其反。SQL语句优化的一般步骤1.通过showstatus命令了解各种SQL的执行频率。2.定位执行效率较低的SQL语句-（重点select）3.通过explain分析低效率的SQL语句的执行情况4.确定问题并采取相应的优化措施Select语句分类DML数据操作语言(insertupdatedeleteselect)DTL数据事物语言(commitrollbacksavepoint)DDL数据定义语言(createalterdrop..)DCL(数据控制语言)grantrevokeShowstatus常用命令该命令可以显示你的mysql数据库的当前状态，我们主要关心的是“com”开头的指令。--查询本次回话Showsessionstatuslike'com_%';//显示当前控制台的情况--查询全局Showglobalstatuslike'com_%';//显示数据库从启动到查询的次数--显示连接数据库次数showstatuslike'Connections';SQL语句优化-show参数MySQL客户端连接成功后，通过使用show[session|global]status命令可以提供服务器状态信息。其中的session来表示当前的连接的统计结果，global来表示自数据库上次启动至今的统计结果。默认是session级别的。下面的例子：showstatuslike'Com_%';其中Com_XXX表示XXX语句所执行的次数。重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。还有几个常用的参数便于用户了解数据库的基本情况。Connections：试图连接MySQL服务器的次数Uptime：服务器工作的时间（单位秒）Slow_queries：慢查询的次数(默认是10)例如：showstatuslike‘Connections’showstatuslike‘Uptime’showstatuslike‘Slow_queries’如何查询mysql的慢查询时间Showvariableslike'long_query_time';修改mysql慢查询时间setlong_query_time=2//查询时间超过2秒，就表示是慢查询SQL语句优化-定位慢查询1.慢查询有什么用?它能记录下所有执行超过long_query_time(单位：秒)时间的SQL语句,帮你找到执行慢的SQL,方便我们对这些SQL进行优化.2.查看慢查询状态是否开启?首先我们先查看MYSQL服务器的慢查询状态是否开启.执行如下命令:mysqlshowvariableslike'%query%';我们可以看到当前log_slow_queries状态为OFF,说明当前并没有开启慢查询.3.Windows下开启MySQL慢查询在默认情况下，mysql不会记录慢查询的语句，需要在my.ini配置重启mysql。MySQL在Windows系统中的配置文件一般是在D:\dev\MySQL\my.ini找到[mysqld]下面加上log-slow-queries=D:\dev\MySQL\MySQLServer5.1\data\slow_query_log.loglong_query_time=2注意：log-slow-queries代表MYSQL慢查询的日志存储目录,此目录文件一定要有写权限；4.最后重新启动mysql，即可生效。查看慢查询的情况Showvariableslike'long_query_time';重新设置时间setlong_query_time=2需求：如何在一个项目中，找到慢查询的select，mysql数据库支持把慢查询语句记录到日志中(但是注意，默认情况下不启动.)操作步骤如下:1.进入到mysql安装目录2.启动xxD:\dev\MySQL\bin\mysqld.exe–slow-query-logSQL语句优化-explain分析问题explainselect*fromempwhereename=“zrlcHd”，会产生如下信息：select_type:表示查询的类型table:对哪张表查询type:表示表的连接类型，从最好到最差的连接类型为null、const/system、eq_reg、ref、range、index和allpossible_keys:表示查询时，可能使用的索引，如果为空，没有可能的索引。key:表示实际使用的索引。如果为null，则没有使用索引。很少的情况下，mysql会选择优化不足的索引。这种情况下，可以在select语句中使用useindex（indexname）来强制使用一个索引或者用ignoreindex（indexname）来强制mysql忽略索引key_len:索引字段的长度，在不损失精确性的情况下，长度越短越好rows:扫描的行数，从多少条记录取出Extra:执行情况的描述和说明select_type:表示查询的类型1、simple简单的select查询，不使用union及子查询例如：explainselect*fromt3whereid=3952602;2、primary最外层的select查询例如：explainselect*from(select*fromt3whereid=3952602)a;3、unionunion中的第二个或随后的select查询，不依赖于外部查询的结果集例如：explainselect*fromt3whereid=3952602unionallselect*fromt34、dependentunionunion中的第二个或随后的select查询，依赖于外部查询的结果集例如：explainselect*fromt3whereidin(selectidfromt3whereid=3952602unionallselectidfromt3);5、subquery子查询中的第一个select查询，不依赖于外部查询的结果集例如：explainselect*fromt3whereid=(selectidfromt3whereid=3952602);6、dependentsubquery子查询中的第一个SELECT，取决于外面的查询例如：explainselectidfromt3whereidin(selectidfromt3whereid=3952602);7、derived派生表的SELECT(FROM子句的子查询)例如：explainselect*from(select*fromt3whereid=3952602)a;8、uncacheablesubquery结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估。9、uncacheableunionunion中的第二个或随后的select查询，属于不可缓存的子查询type:表示表的连接类型const/system；eq_reg；使用有唯一性索引查找（主键或唯一性索引）ref；非唯一性索引访问range；以范围的形式扫描索引index；按索引次序扫描表，就是先读索引，再读实际的行，其实还是全表扫描。主要优点是避免了排序，因为索引是排好序的。all；全表扫描，MySQL从头到尾扫描整张表查找行，一般比较糟糕，应该尽量避免。Extra:执行情况的描述和说明1、distinct:一旦mysql找到了与行相联合匹配的行，就不再搜索了2、notexists:mysql优化了leftjoin，一旦它找到了匹配leftjoin标准的行，就不再搜索了3、rangecheckedforeachrecord（indexmap:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一4、Usingfilesort看到这个的时候，查询就需要优化了。表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL中无法利用索引完成的排序操作称为“文件排序”5、Usingtemporary看到这个的时候，查询就需要优化了。表示MySQL在对查询结果排序时使用临时表。常见于排序orderby和分组查询groupby。建立适当的索引说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行个正确的’createindex’，查询速度就可能提高百倍千倍，这可真有诱惑力。可是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。为什么使用索引?在无索引的情况下，MySQL会扫描整张表来查找符合sql条件的记录，其时间开销与表中数据量呈正相关。对关系型数据表中的某些字段建索引可以极大提高查询速度（当然，不同字段是否selective会导致这些字段建立的索引对查询速度的提升幅度不同，而且索引也并非越多越好，因为写入或删除时需要更新索引信息）。没有索引为什么会慢?在无索引的情况下，MySQL会扫描整张表来查找符合sql条件的记录，其时间开销与表中数据量呈正相关。使用索引为什么会快?适当的使用索引可以提高数据检索速度，可以给经常需要进行查询的字段创建索引索引的代价1、磁盘占用2、对dml(updatedeleteinsert)语句的效率影响，因为写入、更新或删除时需要更新索引信息。哪些列上适合添加索引经常查询的条件字段应该创建索引select*fromempwhereempno=1唯一性太差的字段不适合单独创建索引，即使频繁