mysql优化

1、查看mysql服务器配置信息
show variables;
还可以使用类似SHOW VARIABLES LIKE 'Key%';

2、查看mysql服务器运行的各种状态值
show status;是当前会话，你退出就失效了。
show global status;全局会话，重启数据库或关闭数据库就失效了。

1）、用show status语句查询数据库性能，如：
show status like 'value';
其中，value参数有connections：连接mysql服务器的次数；uptime：mysql服务器的上线时间；slow_queries：慢查询的次数；com_select:查询操作的次数；com_insert:插入操作的次数；com_update:更新操作的次数；com_delete:删除操作的次数；
注：msyql中存在查询InnoDB类型的表的一些参数。如：InnoDB_rows_read参数表示select语句查询的记录数；InnoDB_rows_inserted表示insert语句插入的记录数；InnoDB_rows_updated参数表示update语句更新的记录数；InnoDB_rows_deleted参数表示delete语句删除的记录数。

3、优化查询语句
可以用explain或desc来分析查询语句
格式：explain select语句;
mysql> explain select * from pet71 \G
*************************** 1. row **
           id: 1
  select_type: SIMPLE
        table: pet71
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)
其中
id：表示select语句的编号；
select_type：表示select语句的类型。其中simple表示简单查询，其中不包括连接查询和子查询；primary表示主查询，或者是最外层的查询语句；union表示连接查询的第二个或后面的查询语句；
table：表示查询的表；
type：表示表的连接类型。其中，system表示表中只有一条记录；const表示表中有多条记录，但是只从表中查询一条记录；all表对表进行了完整的扫描；eq_ref表示多表连接时，后面的表使用了unique或者primary key；ref表示多表查询时，后面的表使用了普通索引；unique_subquery表示子查询中使用了unique或primary key；index_subquery表示子查询中使用了普通索引；range表示查询语句中给出了查询范围；index表示对表中的索引进行了完整的扫描；
possible_keys:表示查询中可能使用的索引；
key：表示查询使用到的索引；
key_len:表示索引字段的长度；
ref：表示使用哪个列或常数与索引一起来查询记录；
rows：表示查询的行数；
extra：表示查询过程的附件信息
1）、优化索引查询
a、在查询语句中使用like关键字进行查询时，如果匹配字符串的第一个字符为"%"时，索引不会被使用。如果"%"不在第一个位置，索引就会被使用。
b、多列索引只有查询条件中使用这些字段中第一个字段，索引才会被使用。
c、查询语句中只有or关键字时，如果or前后的两个条件的列都是索引时，查询中将使用索引。如果or前后有一个条件的列不是索引，那么查询中将不使用索引。
2）、优化子查询
子查询内层查询语句的查询结果建立了一个临时表。所以速度会受到影响。可以用连接查询替代子查询。

4、优化数据库结构
1）、有些表字段很多，可以分成多个表，数据量大的字段如备注栏字段可以单独存放在一个表中。需要显示该字段时可以使用联表查询。
2）、增加中间表：新建一个表存放联表查询后的数据，然后可以直接查询该中间表
3）、增加冗余字段

5、优化插入记录的速度
插入记录时，索引、唯一性校验都会影响到插入记录的速度。一次插入多条记录和多次插入记录耗费的时间是不同的。
1）、禁用索引
禁用索引alter table 表名 disable keys;
插入后重新开启索引alter table 表名 enable keys;
2）、禁用唯一性检查
禁用唯一性检查set unique_ckecks=0;
重新开启唯一性检查set unique_ckecks=1;
3)、优化insert语句
insert语句如下：insert into food values(null,a,b),(null,b,c);
或
insert into food values(null,a,b)
insert into food values(null,b,c);
第一种减少了与数据库的连接比第二种插入快
注：load data infile语句导入数据的速度比insert语句速度快。

6、分析表、检查表、优化表
分析表作用是分析关键字的分部。检查表作用是检查表是否存在错误，还可以检查视图是否存在错误。优化表主要作用是消除删除或者更新造成的空间浪费。
1）、分析表
analyze table 表名1[,表名2...];
分析表的过程中，数据库系统会对表加一个只读锁。在分析期间只能读取表中的记录，不能更新和插入记录。分析语句能分析InnoDB和MyISAM类型的表。如：
mysql> analyze table pet60;
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| test1.pet60 | analyze | status   | OK       |
+-------------+---------+----------+----------+
1 row in set (0.53 sec)
OP：表示执行的操作。analyze表示进行分析操作。check表示进行检查查找。optimize表示进行优化操作；
Msg_type:表示信息类型，其显示的值通常是状态、警告、错误和信息这四者之一；
Msg_text:显示信息。
2）、检查表
该语句能分析InnoDB和MyISAM类型的表是否存在错误。该语句执行过程中也会给表加上只读锁。
语法：check table 表名1[,表名2...][option];
其中optioin参数有5个参数，分别是quick、fast、changed、medium和extended。这5个参数的执行效率依次降低。option选项只对MyISAM类型的表有效，对InnoDB类型的表无效。
3）、优化表
该语句对InnoDB和MyISAM类型的表都有效。但是optimize table语句只能优化表中的varchar、blob或text类型的字段。如：
optilmize table 表名1[,表名2...];
通过该语句可以消除删除和更新造成的磁盘碎片，从而减少空间浪费。该语句执行过程中也会给表加上只读锁。
注：如果一个表用了text或blob这样的数据类型，那么更新、删除等操作后之前分配的磁盘空间不会自动收回会造成磁盘空间的浪费。使用优化语句就可以将这些磁盘碎片整理出来，以便以后再利用。

7、优化mysql服务器
分为服务器硬件优化和mysql服务参数优化
1）、优化服务器硬件
a、可在内存中为mysql设置更多缓冲区。电脑4G内存可以选择my-innodb-heavy-4G.ini作为mysql数据库的配置文件。但是这个配置文件主要支持InnoDB存储引擎的表。电脑2G内存可以选择my-huge.ini作为配置文件。
b、可以用多块磁盘存储数据，可以从多个磁盘并行读取数据，可以提高读取速度。通过镜像机制可以将不同计算机上的mysql服务器进行同步，这些服务器中数据是一样的，降低单个服务器压力。
2）、优化mysql的参数
内存中为mysql保留部分缓存区。缓存区大小都是在mysql的配置文件中进行设置的。mysql中比较重要的配置参数都在my.cnf或my.ini文件的[mysqld]组中。参数如：
  key_buffer_size:表示索引缓存的大小。值越大，使用索引进行查询的速度越快。
  table_cache:表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多。
  query_cache_size:表示查询缓存区的大小。默认为0，只适用于修改操作少且经常执行相同的查询的操作。
  query_cache_type:表示查询缓冲区的开启状态。取值为0时表示关闭，取值为1时表示开启，取值2时表示按要求使用查询缓存区。当取1时，在查询语句中加上sql_no_cache关键字，该查询语句将不使用查询查询缓存区。可以用flush query cache语句清理查询缓存区中的碎片。 当取值为2时， select语句中使用了sql_cache 关键字，查询缓存区才会使用。例如， select sql_cache * from score;
  max_connectioins:表示数据库最大连接数。
  sort_buffer_size:表示排序缓存区大小。值越大进行排序的速度越快。
  read_buffer_size:表示为每个线程保留的缓冲区大小。当线程需要从表连续读取记录时需要用到这个缓冲区。set session read_buffer_size=n;可以临时设置该参数的值。
  read_rnd_buffer_size:表示为每个线程保留的缓冲区大小。与 read_buffer_size相似。但主要用于存储特定顺序读取出来的记录。也可以用set session read_rnd_buffer_size=n来临时设置该参数的值。
  innodb_buffer_pool_size:表示InnoDB类型的表和索引的最大缓存。值越大查询速度越快。
  innodb_flush_log_at_trx_commit:表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数有3个值，分别为0、1、2。该参数默认值为1；为0时表示每隔1秒将数据写入日志文件并将日志文件写入磁盘；为1时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘；为2时表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘。