MySql 优化的琐碎

名词:

QPS (query per second),
TPS(transaction per second)
DDL----Data Definition Language 数据定义语言 (alter table)
DML----Data Manipulation Language 数据操作语言 (update table_name set)
DCL----Data Control Language 数据库控制语言 (grant all )

事物的四大特性 ACID 原子性, 一致性, 隔离性, 永久性

事物的隔离级别
读未提交(read uncommited)—>产生脏读问题
读已提交(read commited)—>不可重读问题
可重复读(read repeateable)—>可重复读 幻读
串行化(serializeable)—>以上问题都可解决, 但效率底下

innodb特性: 事务, 行级锁, 外键约束, 热备

start transaction 开启一个事务类似于设置 autocommit = 0;在事务中使用索引select数据是不会自动加锁的, 只有在事务中update, insert, delete 使用相同的索引关键字并且锁定同一行才会加锁;在隔离级别为repeatable-read时,开启事务之后如果想避免幻读, 读到真实数据需要手动加锁, 读锁 lock in share mode 写锁 for update; lock in share mode 意味着其他会话可以继续在此读锁上加读锁, 在其他会话加读锁之前当前会话可修改此数据, 自动升级为写锁. 若其他会话已加读锁当前会话不可修改数据, 会被阻塞. for update 以为着其他会话可读不可写, 不可加锁, 当前会话可读可写.

show engine innodb status 
-> LATEST DETECTED DEADLOCK查看引擎状态;

配置参数

innodb_file_per_table = on; //独立表空间
skip_name_resolve = on
read_only = on //从库设置
skip_slave_start = on //从库设置,禁止自动启动复制
sort_buffer_size; //排序缓冲区,每个进程 保持默认
join_buffer_size //连接缓冲, 每个进程 保持默认
innodb_buffer_pool_size //innodb缓冲池大小,至关重要

innodb_log_file_size 单个事务日志文件大小
innodb_log_file_in_group 事务日志数量
innodb_log_buffer_size 异步写入事务日志内存缓冲区大小, 32MB-128MB
innodb_flush_log_at_trx_commit 是否同步写入
innodb_flush_method = O_DIRECT //关闭操作系统缓存
innodb_doublewrite = 1;

sync_binlog //是否同步写入binlog日志;

优化

select * from table_name force key(index_name)

一些信息查询语句

show processlist; //显示连接数
show collation;

数据库简单备份恢复

//使用cmd备份需要先设置中文
//单库备份
set names gbk;
mysqldump -u root -p  --default-character-set=utf8 database_name > /paht/to/x.sql 
//多库备份
mysqldump -u root -p  -B dabase_name1 database_name2 > /path/to/x.sql
//备份多表
mysqldump -uroot -p dabase_name table_name1 table_name2 table_name3 > /path/to/x.sql
//恢复数据库时若使用cmd
登陆mysql之后
set names gbk;
source /path/to/x.sql