关于Mysql数据库的优化

     mysql数据库有多种存储引擎

一般可以按照以下顺序进行：

1. 通过show status和应用特点了解各种SQL的执行频率
SHOW STATUS可以根据需要显示session级别的统计结果和global级别的统计结果。
以下几个参数对Myisam和Innodb存储引擎都计数：
1. Com_select  执行select操作的次数，一次查询只累加1；
2. Com_insert 执行insert操作的次数，对于批量插入的insert操作，只累加一次；
3. Com_update 执行update操作的次数；
4. Com_delete　执行delete操作的次数。


2. 定位执行效率较低的SQL语句
可以通过以下两种方式定位执行效率较低的SQL语句：
• 可以通过慢查询日志定位那些执行效率较低的sql语句，用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。可以链接到管理维护中的相关章节。
• 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看SQL执行情况，同时对一些锁表操作进行优化。










3.通过EXPLAIN分析低效SQL的执行计划
通过以上步骤查询到效率低的SQL后，我们可以通过explain或者desc 获取MySQL如何执行SELECT语句的信息，包括select语句执行过程表如何连接和连接的次序。


explain可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。
 
mysql> explain select sum(moneys) from sales a,companys b where a.company_id = b.id and a.year = 2006;
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| select_type   | table | type  | possible_keys| key            | key_len   | rows  | Extra     |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| SIMPLE    | b     | index | PRIMARY   | PRIMARY   | 4     |    1  | Using index   |
| SIMPLE    | a     | ALL   | NULL      | NULL      | NULL  |   12  | Using where   |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
type：表示表的连接类型
• ①当表中仅有一行是type的值为system是最佳的连接类型；
• ②当select操作中使用索引进行表连接时type的值为ref；
• ③当select的表连接没有使用索引时，经常会看到type的值为ALL，表示对该表进行了全表扫描，这时需要考虑通过创建索引来提高表连接的效率。


myisam表的数据文件和索引文件是自动分开的；innodb的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。 创建索引语法如下：
    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...)
    index_col_name:
    col_name [(length)] [ASC | DESC]
 
索引的存储类型目前只有两种（btree和hash），具体和表的模式相关：
    myisam     btree
    innodb      btree
    memory/heap hash，btree





大量插入数据的Sql语句优化


1) 对于Myisam类型的表，可以通过以下方式快速的导入大量的数据。 
    ALTER TABLE tblname DISABLE KEYS;
    loading the data
    ALTER TABLE tblname ENABLE KEYS;
这两个命令用来打开或者关闭Myisam表非唯一索引的更新。在导入大量的数据到一个非空的Myisam表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的Myisam表，默认就是先导入数据然后才创建索引的，所以不用进行设置。


而对于Innodb类型的表，这种方式并不能提高导入数据的效率。


2) 对于Innodb类型的表，我们有以下几种方式可以提高导入的效率：
• 因为Innodb类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果Innodb表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。
• 在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
• 如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
•                         Insert语句优化
• 1) 如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。
    Insert into test values(1,2),(1,3),(1,4)…


2) 如果你从不同客户插入很多行，能通过使用INSERT DELAYED语句得到更高的速度。Delayed的含义是让insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完后才进行插入。


3) 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。


4) 如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对myisam表使用。


5) 当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。


6) 根据应用情况使用replace语句代替insert。


7) 根据应用情况使用ignore关键字忽略重复记录。


MySQL的默认的调度策略可用总结如下：
• 写入操作优先于读取操作。
• 对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。
• 对某张数据表的多个读取操作可以同时地进行。


MySQL提供了几个语句调节符，允许你修改它的调度策略：
• LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。
• HIGH_PRIORITY关键字应用于SELECT和INSERT语句。
• DELAYED关键字应用于INSERT和REPLACE语句。
 
如果写入操作是一个LOW_PRIORITY（低优先级）请求，那么系统就不会认为它的优先级高于读取操作。在这种情况下，如果写入者在等待的时候，第二个读取者到达了，那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候，才允许写入者开始操作。这种调度修改可能存在LOW_PRIORITY写入操作永远被阻塞的情况。


SELECT查询的HIGH_PRIORITY（高优先级）关键字也类似。它允许SELECT插入正在等待的写入操作之前，即使在正常情况下写入操作的优先级更高。另外一种影响是，高优先级的SELECT在正常的SELECT语句之前执行，因为这些语句会被写入操作阻塞。


如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理，那么请使用--low-priority-updates选项来启动服务器。通过使用INSERT HIGH_PRIORITY来把INSERT语句提高到正常的写入优先级，可以消除该选项对单个INSERT语句的影响。




优化表的数据类型
表需要使用何种数据类型，是需要根据应用来判断的。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样即浪费存储也浪费内存。


我们可以使用PROCEDURE ANALYSE()对当前已有应用的表类型的判断，该函数可以对数据表中的列的数据类型提出优化建议，可以根据应用的实际情况酌情考虑是否实施优化。语法：
   SELECT * FROM tbl_name PROCEDURE ANALYSE();
   SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);


输出的每一列信息都会对数据表中的列的数据类型提出优化建议。第二个例子告诉PROCEDURE ANALYSE()不要为那些包含的值多于16个或者256字节的ENUM类型提出建议。如果没有这样的限制，输出信息可能很长；ENUM定义通常很难阅读。


在对字段类型进行优化时，可以根据统计信息并结合应用的实际情况对其进行优化。


通过拆分，提高表的访问效率
这里我们所说的拆分，主要是针对Myisam类型的表，拆分的方法可以分成两种情况：


1) 纵向拆分：
纵向拆分是只按照应用访问的频度，将表中经常访问的字段和不经常访问的字段拆分成两个表，经常访问的字段尽量是定长的，这样可以有效的提高表的查询和更新的效率。


2) 横向拆分：
横向拆分是指按照应用的情况，有目的的将数据横向拆分成几个表或者通过分区分到多个分区中，这样可以有效的避免Myisam表的读取和更新导致的锁问题。


逆规范化
数据库德规范化设计强调数据的独立性，数据应该尽可能少地冗余，因为存在过多的冗余数据，这就意味着要占用了更多的物理空间，同时也对数据的维护和一致性检查带来了问题。


但是对于查询操作很多的应用，一次查询可能需要访问多表进行，如果通过冗余纪录在相同表中，更新的代价增加不多，但是查询操作效率可以有明显提高，这种情况就可以考虑通过冗余数据来提高效率。


使用冗余统计表
使用create temporary table语法，它是基于session的表，表的数据保存在内存里面，当session断掉后，表自然消除。


对于大表的统计分析，如果统计的数据量不大，利用insert, select将数据移到临时表中比直接在大表上做统计要效率更高。


选择更合适的表类型
1、如果应用出现比较严重的锁冲突，请考虑是否更改存储引擎到innodb，行锁机制可以有效的减少锁冲突的出现。
2、如果应用查询操作很多，且对事务完整性要求不严格，则可以考虑使用Myisam存储引擎。


错误日志
记录内容：包含了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息


文件位置和格式：可以用--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值，mysqld使用错误日志名host_name.err 并在数据目录中写入日志文件


BINLOG
记录内容：二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改情况。


文件位置和格式：当用--log-bin[=file_name]选项启动时，mysqld写入包含所有更新数据的SQL命令的日志文件。如果未给出file_name值， 默认名为-bin后面所跟的主机名。如果给出了文件名，但没有包含路径，则文件被写入数据目录。


查看binlog内容：shell> mysqlbinlog log-file


删除日志：
    RESET MASTER;//删除所有binlog日志，新日志编号从头开始
    PURGE MASTER LOGS TO 'mysql-bin.010';//删除mysql-bin.010之前所有日志
    PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26'
    ;// 删除2003-04-02 22:46:26之前产生的所有日志


相关选项：
1) --binlog-do-db=db_name
告诉主服务器，如果当前的数据库(即USE选定的数据库)是db_name，应将更新记录到二进制日志中。其它所有没有明显指定的数据库  被忽略


2) --binlog-ignore-db=db_name
告诉主服务器，如果当前的数据库(即USE选定的数据库)是db_name，不应将更新保存到二进制日志中要想记录或忽视多个数据库，使用多个选项，为每个数据库指定相应的选项。


3) -innodb-safe-binlog
使用此选项和sync_binlog＝N（每写N次日志同步磁盘）全局变量将使得事务能够记录的更加安全


4) 具有SUPER权限的客户端可以通过SET SQL_LOG_BIN=0语句禁止将自己的语句记入二进制记录


查询日志
记录内容：记录了客户端的所有语句，而二进制日志不包含只查询数据的语句
 
文件位置和格式：用--log[=file_name]或-l [file_name]选项启动它。如果没有给定file_name的值， 默认名是host_name.log。


慢查询日志：
记录内容：记录包含所有执行时间超过long_query_time秒的SQL语句的日志文件。获得初使表锁定的时间不算作执行时间。


文件位置和格式：用--log-slow-queries[=file_name]选项启动它。如果没有给出file_name值， 默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。


快速查看：使用mysqldumpslow命令获得日志中显示的查询摘要来处理慢查询日志，例如：
    [zzx@bj37 data]$ mysqldumpslow bj37-slow.log


其他选项：在MySQL 5.1中,通过--log-slow-admin-statements服务器选项，你可以请求将慢管理语句，例如OPTIMIZE TABLE、ANALYZE TABLE和 ALTER TABLE写入慢查询日志