MySQL数据库优化详解(2)

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]
如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多的改动，则需要做定期优化。这个命令可以将表中的空间碎片进行合并，但是此命令只对MyISAM、BDB和InnoDB表起作用。

常用SQL的优化

大批量插入数据
当用load命令导入数据的时候，适当设置可以提高导入的速度。

对于MyISAM存储引擎的表，可以通过以下方式快速的导入大量的数据。

ALTER TABLE tbl_name DISABLE KEYS
loading the data
ALTER TABLE tbl_name ENABLE KEYS
DISABLE KEYS 和ENABLE KEYS 用来打开或关闭MyISAM表非唯一索引的更新，可以提高速度，注意：对InnoDB表无效。

--没有使用打开或关闭MyISAM表非唯一索引：

mysql> load data infile ‘/home/mysql/film_test.txt'into table film_test2; Query OK,529056 rows affected (1 min 55.12 sec) Records:529056 Deleted:0 Skipped:0 Warnings:0

--使用打开或关闭MyISAM表非唯一索引：

mysql> alter table film_test2 disable keys; Query OK,0 rows affected (0.0 sec) mysql> load data infile ‘/home/mysql/film_test.txt'into table film_test2; Query OK,529056 rows affected (6.34 sec) Records:529056 Deleted:0 Skipped:0 Warnings:0 mysql> alter table film_test2 enable keys; Query OK,0 rows affected (12.25 sec)

--以上对MyISAM表的数据导入，但对于InnoDB表并不能提高导入数据的效率

（1）针对于InnoDB类型表数据导入的优化

因为InnoDB表的按照主键顺序保存的，所以将导入的数据主键的顺序排列，可以有效地提高导入数据的效率。

--使用test3.txt文本是按表film_test4主键存储顺序保存的

mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; Query OK, 1587168 rows affected (22.92 sec)

Records:1587168 Deleted:0 Skipped:0 Warnings:0
--使用test3.txt没有任何顺序的文本（效率慢了1.12倍）

mysql> load data infile ‘/home/mysql/film_test4.txt'into table film_test4; Query OK, 1587168 rows affected (31.16 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0

（2）关闭唯一性效验可以提高导入效率

在导入数据前先执行set unique_checks=0，关闭唯一性效验，在导入结束后执行set unique_checks=1，恢复唯一性效验，可以提高导入效率。

--当unique_checks=1时

mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; Query OK,1587168 rows affected (22.92 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0

--当unique_checks=0时

mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; Query OK,1587168 rows affected (19.92 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0

（3）关闭自动提交可以提高导入效率

在导入数据前先执行set autocommit=0，关闭自动提交事务，在导入结束后执行set autocommit=1，恢复自动提交，可以提高导入效率。

--当autocommit=1时

mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; Query OK,1587168 rows affected (22.92 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0

--当autocommit=0时

mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; Query OK,1587168 rows affected (20.87 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0

优化insert语句
尽量使用多个值表的insert语句，这样可以大大缩短客户与数据库的连接、关闭等损耗。

可以使用insert delayed（马上执行）语句得到更高的效率。

将索引文件和数据文件分别存放不同的磁盘上。

可以增加bulk_insert_buffer_size 变量值的方法来提高速度，但是只对MyISAM表使用当从一个文件中装载一个表时，使用LOAD DATA INFILE。这个通常比使用很多insert语句要快20倍。

优化group by语句
如果查询包含group by但用户想要避免排序结果的损耗，则可以使用使用order by null来禁止排序：

如下没有使用order by null来禁止排序

mysql> explain select id,sum(moneys) from sales2 group by id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using temporary;Using filesort 1 row in set (0.00 sec)

如下使用order by null的效果：

mysql> explain select id,sum(moneys) from sales2 group by id order by null\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using temporary 1 row in set (0.00 sec)

优化嵌套查询
下面是采用嵌套查询的效果（可以使用更有效的链接查询（Join）替代）。

mysql> explain select * from sales2 where company_id not in(select id from company2)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using where 1 row in set (0.00 sec) *************************** 2. row *************************** id: 2 select_type: SIMPLE table: company2 type: index_subquery possible_keys: ind_company2_id key: ind_company2_id key_len: 5 ref: func rows: 2 Extra: Using index 1 row in set (0.00 sec)

下面是使用更有效的链接查询（Join）

mysql> explain select * from sales2 left join company2 on sales2.company_id = company2.id where sales2.company_id is null\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ALL possible_keys: ind_sales2_companyid_moneys key: ind_sales2_companyid_moneys key_len: 5 ref: count rows: 1 Extra: Using where 1 row in set (0.00 sec) *************************** 2. row *************************** id: 2 select_type: SIMPLE table: company2 type: index_subquery possible_keys: ind_company2_id key: ind_company2_id key_len: 5 ref: func rows: 1 Extra: 1 row in set (0.00 sec)

从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。连接（JOIN）子所以更有效率一些，是因为MySQL不需要再内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

数据库优化

优化表的类型
在MySQL中，可以使用函数PROCEDUREANALYSE（）对当前应用的表进行分析，改函数可以对数据表中列的数据类型提出优化建议，用户可以根据应用的实际情况酌情考虑是否实施优化。

mysql> select * from duck_cust procedure analyse()\G *************************** 1. row *************************** Field_name: sakila.duch_cust.cust_num Min_value: 1 Max_value: 6 Min_length: 1 Max_length: 1 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 3.5000 Std: 1.7078 Optimal_fieldtype: ENUM(‘1',‘2',‘3',‘4') NOT NULL *************************** 2. row *************************** ……

大存储量解决
1.分库分表

2.分区

主要目的:

1.减少表的记录数

2.减小对操作系统的负担压力

中间表
中间表的产生:
1.view 视图
2.重新生成一个新表

Mysql服务器优化

myisam读锁定
1.

lock table t1 read
2.开启另一个mysql连接终端,接着去尝试:

select * from t1
3.再insert、update和delete t1这张表，你会发现所有的数据都停留在终端上没有真正的去操作

4.读锁定对我们在做备份大量数据时非常有用.

mysqldump -uroot -p123 test >test.sql
myisam写锁定
1.

lock table t1 write
2.打开另一个mysql终端,尝试去select、insert、update和delete这张表t1,你会发现都不能操作，都会停留在终端上，只有等第一个终端操作完毕,第二个终端才能真正执行.

3.可见表的写锁定比读锁定更严格

4.一般情况下我们很少去显式的去对表进行read 和write锁定的,myisam会自动进行锁定的.

Mysql服务器优化

二进制日志
1.log-bin=mysql-bin

查看bin-log日志:

mysql> show binary logs;
查看最后一个bin-log日志:

mysql> show master status;
慢查询日志
开户和设置慢查询时间:

vi /etc/my.cnf
log_slow_queries=slow.log
long_query_time=5
慢查询次数:

mysql> show global status like "%quer%"
socket问题
mysql socket无法登录

1. 有时登录mysql时提示不能用socket登录，此时可以换成tcp方式去登录，但是可以测试时可以这样用，但是必须要在php去用之前把这个事情解决了.

[root@localhost mysql]# mysql -uroot -pwei --protocol tcp -hlocalhost Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.0.77-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

这样就可以登录,这样就不用mysql.sock来登录，而mysql.sock是启动mysqld服务时产生的

root密码丢失

root密码丢失破解

1.service mysqld stop 2. mysqld_safe --skip-grant-tables --user=mysql & //跳过授权表mysql.user和mysql.db这些表 3. mysql -uroot 4. set password=password("wei"); //用这一条语句结果报错,就是因为加了--skip-grant-tables 4. mysql>update user set password=password("wei") where user='root' and host='localhost'; 5. mysql> set password for root@localhost=password("wei"); 6. mysql> set password=password("wei"); //和第五步一样,都可能成功修改密码

上一篇：mysql获得60天前unix时间思路及代码
下一篇：mysql中TINYINT的取值范围