MySQL优化（7）：其他注意事项

ddl执行

线上服务器执行ddl，更新表结构，需要谨慎，结构更改会导致全表被独占锁定（新版本有改善）

避免这种情况，使用copy策略，而不是直接执行alter table语句

思路：创建一个新表，满足新要求，将旧表数据逐条导入新表，同时表上可以执行其他任务，导入的过程其他任务都记录在日志，导入完成后根据日志更新新表，最后新表替换旧表（java等代码中实现）

 

数据导入语句

在恢复数据时，需要大量的数据导入

思路：导入时，先禁用索引和约束；导入完毕后，再开启索引和约束，一次性创建索引

有可能使用到的语句：禁用索引和开启索引

alter table [table-name] disable keys;
alter table [table-name] enable keys;

对于innodb存储引擎，可以将多条sql放入一个事务中完成 

可以使用prepare预编译的方案执行导入操作，减少相同结构的sql编译次数

 

大容量页码limit

尽量不要使用大的offset，比如limit 10000,10

思路：尽量使用条件过滤，完成数据筛选，而不是通过offset跳过已经查询到的数据

 

select *

不要使用select *，而是使用具体的字段；这一点对性能影响不是很大，不过这是规范

 

order by rand()不要用

这句话是随机排序，如果业务要随机选择尽量在服务器代码中实现，随机生成主键进行检索

 

单表和多表查询

尽量使用单表查询来代替多表查询，原因有以下几点：

（1）单表计算的压力在java代码，多表计算的压力在数据库

（2）多表查询会一个一个表依次执行，最后再合并结果

（3）多表查询会增加锁定时间，降低程序并发性能

 

count(*)

myisam会自动存储count(*)的值，innodb没有内部计数器，执行这句话相对来说较慢

解决思路：额外创建一张表，把count(*)的值记录下来

另外：

count(id)表示的是：统计id字段不为null的数量

count(1)表示的是：统计记录数量，和count(*)几乎一样

 

limit 1

如果你确定查到的结果只有一个，那么加入limit 1会提高性能

 

慢查询日志

用于记录执行时间超过某个临界值的sql的日志，用户快速定位慢查询

开启该日志：

查询是否开启

show variables like 'slow_query_log';

开启

set global slow_query_log=on;

开启后会在数据目录创建slow.log文件

查看临界时间，默认是十秒，很长

show variables like 'long_query_time';

设置临界时间

set long_query_time = 0.5;

一旦超过了0.5秒，就会被自动记录

 

profile信息统计

详细记录sql语句执行时间的工具

开启profile：

set profiling=on;

使用命令查看结果

show profiles;

具体某条的详情

show profile for query [query_id]

 

典型的服务器配置

（1）max_connections：当前mysql允许最大连接数151个

（2）table_open_cache：表句柄缓存2000

（3）key_buffer_size：索引缓冲大小8388608

（4）innodb_buffer_pool_size：innodb存储引擎缓存池大小，比较重要，尽可能地设置更大

（5）innodb_file_per_table：innodb表的独立表文件，on就表示一个表对应一个表文件，否则所有innodb表文件存到一起

 以上的配置全部取决于实际的运行环境

 

mysqlslap压力测试工具

这是一个mysql自带的工具，是一个exe，直接运行即可，具体的百度，这是dba的工作，不是程序员的工作

 

 

最后，推荐一篇文章，写的很不错