mysql优化(第一部分)

一、设计局限和初衷

1、MyISAM存储引擎，MySQL使用极快速的表锁定，以便允许多次读或一次写。使用该存储引擎的最大问题出现在同一个表中进行混合稳定数据流更新与慢速选择。

2、为了更容易地让非事务表顺利工作，为所有NOT NULL列设置默认值。

3、不是运行在严格模式或TRADITIONAL SQL模式时，插入值会变为“最好可能值”。

4、所有表达式的计算结果返回一个表示错误状况的信号。例如，1/0返回NULL。(使用ERROR_FOR_DIVISION_BY_ZERO SQL模式可以更改该行为)。

二、为可移植性设计应用程序

1、因为不同SQL服务器实现了标准SQL的不同部分，需要花功夫来编写可移植的SQL应用程序。对很简单的选择/插入，很容易实现移植，但是需要的功能越多则越困难。如果想要应用程序对很多数据库系统都快，它变得更难。

2、MySQL的crash-me程序来找出能用于数据库服务器选择的函数、类型和限制。crash-me并不能找出所有的特性，但是其广度仍然很合理，可以进行大约450个测试。

3、前端使用缓存时，表创建信息应该包含缓存初始大小和表刷新频率等信息。

三、优化SELECT语句和其它查询

1、影响所有语句的一个因素是：你的许可设置得越复杂，所需要的开销越多。

2、执行GRANT语句时使用简单的许可，当客户执行语句时，可以使MySQL降低许可检查开销。例如，如果未授予任何表级或列级权限，服务器不需要检查tables_priv和columns_priv表的内容。同样地，如果不对任何 账户进行限制，服务器不需要对资源进行统计。如果查询量很高，可以花一些时间使用简化的授权结构来降低许可检查开销。

3、select benchmark(执行次数,表达式)，例如：select benchmark(1000000,date_add(now(),interval 1 day));该语句执行的时间是date_add执行一百万次的时间。

4、explain语句：

（1）explain table_name：相当于desc tablename或show columns from table_name；

（2）用join链接多张表时，返回多行数据，且这些数据是以表被读入的顺序列出；多表的执行方式是，先从第一张表获取一条记录，在查找第二张表，接着第三张，可见多表联合查询速度很慢。

（3）当使用EXTENDED关键字时，EXPLAIN产生附加信息，可以用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子。

（4）explain结果各列：

id：select标识符；

select_type：select类型，包含simple(不包含union和子查询)、primary、unoin(union语句第二个或后面的，取决于外查询)、union result(union结果)、subquery(子查询的第一个select)、dependent subquery(子查询的第一个select)、derived(导出表的sect，from子查询)；

table：引用的表；

type：链接类型，包含system(只有一行，是const的特例)、const(只有一行常用于primary key 和unique)、eq_ref(简单的说，这是多表查询的const，也是用在primary key和unique)、ref(不只一行，比如：name='hehe'，记录数可以有多条)、ref_or_null(包含or name is null 的ref)、index_merge(索引合并法)、unique_subquery(用于in子查询的primary key)、index_subquery(用于in子查询，非主键)、range(用于范围表达式，包括in、between等)、index(使用了普通索引)、all(全表扫描)共十一种；

possible_keys：能使用的索引；

key：实际使用的索引；

key_len：键长，通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

ref：ref列显示使用哪个列或常数与key一起从表中选择行；

rows：必须检查的行数；

Extra：解决查询的重要信息，包括Distinct(只匹配一行)、

四、优化语句：

1、怎么优化where字句：

（1）除去重叠的常量，如：a>b and a=c abd b=5应改为 a>5 and a=c and b=5;