mysql

###优化 ####java_jdbc batch慢 innodb_flush_log_at_trx_commit = 0

1. 1InnoDB InnoDB will flush (fsync) the transaction logs to the disk at each commit
2. 0the log is only written to the log file and the log file flushed to disk approximately once per second
3. the log is written to the log file at each commit, but the log file is only flushed to disk approximately once per second.
4. 默认为1或者不开启

####连接数不够 max_connections = 1000

SELECT @@MAX_CONNECTIONS;

####避免表扫描

1. 利用query_cache,调整sql语句写法
2. Force index(createtime),告知mysql优化器，避免表扫描SELECT missionid,nodeid FROM task_history FORCE INDEX(createtime) WHERE createtime>='' and createtime<=''
3. 建立联合索引createtime_nodetype_type,并且强制走索引

实际测试结果表明并不是每次都优于单独索引，和数据规模有比较大的关系

####查询数据库大小

1. use information_schema
2. select * from information_schema.TABLES where information_schema.TABLES.TABLE_SCHEMA='encodeonline' and information_schema.TABLES.TABLE_NAME='encode_task_history'\G
3. select concat(round(sum(data_LENGTH)/(1024*1024),2),'MB') as 'Data Size' from tables where table_schema='encodeonline' 数据库表大小
4. select concat(round(sum(index_LENGTH)/(1024*1024),2),'MB') as 'Data Size' from tables where table_schema=''encodeonline'' 数据库索引大小

####二进制日志查看

1. scp -P 22 xx.log cmh@xx.xx.xx.xx:/home/cmh/tmp/
2. mysql/bin/mysqlbinlog --start-datetime='2014-04-20 13:09:50' --stop-datetime='2014-04-20 13:15:00' /home/cmh/tmp/mysql-bin.000508 >/home/cmh/chenminghao/17533/data/mysql1.txt
3. cat mysql1.txt| grep -v storagenodeid| grep storagenode> locatotal.txt

###常用命令 ####修改权限 grant all privileges on *.* to 'root'@'xx.xx.xx.xx' identified by '88888';

内网地址可以 xx.xx.xx.%

flush privileges; ####查看所有的用户和权限 select * from mysql.user

grant select on forex.* to 88@'xx.xx.xx.xx' identified by 'xxx' ;