1. 如何减少数据库连接数?
当数据库连接数过多时,通过show processlist 查看连接状态,id等信息,使用kill connection +id中断连接。
2. MySQL是怎么保证数据不丢失的?
只要保证redolog和binlog持久化磁盘,就能保证mysql数据不丢失,redolog用于宕机后数据恢复,binlog用于记录每次事务操作;
binlog持久化过程是:开启事务后,先把日志记录到binlog cache,提交事务的时候binlog cache再write写到binlog当中,最后binlog再fsync到磁盘中。参数 sync_binlog 控制write 和fsync的时机;
• sync_binlog = 0 表示每次提交只wirte,等到binlog内存用完的时候再fsync到磁盘中,默认为0;
• sync_binlog = 1 表示每次提交后都进行fsync到磁盘中,建议设为1;
• sync_binlog = N 表示每次提交后都wirte,累积N个事务后再flush,不建议因为宕机后可能无法恢复最近的N个事务。
redolog是一个固定大小的循环写入,持久化过程和binlog类似,先写入cache中,再写入磁盘中。innodb_flush_log_at_trx_commit控制redolog的写入策越。innodb_flush_log_at_trx_commit写入策越和 sync_binlog 相似,也推荐设为1;(著名的双1设置)。时序上,开启事务后redolog先prepare,再写binlog,最后提交redolog。
3. Mysql是怎么做到主从同步的?
主库开启事务后将日志写入binlog当中,然后从库读取binlog上的日志,写入中转日志(relaylog)中,线程读取中转日志后并执行sql。
binlog存在三种格式 statement,row,mixed。
• statement:binlog记录的是sql原文,例如delete from t where id = 1 ,系统默认值。
• row:binlog记录的是受影响的具体所有数据,例如delete from t where id = 1 ,binlog会记录id =1这一行的所有数据,缺点是binlog会占用空间变大,优点是可以用于数据恢复,推荐设为1 ,误操作后可以用mysqlbinlog ,binlog2sql做数据恢复。
• mixed 是row和statement的混合体,系统会根据影响数据的大小做判断,使用statement 还是 row。
4. 大查询会不会把内存用光?
不会, mysql查询采用的是边查边发的逻辑,但是大查询比较耗费IO资源。
5. 使用join时需要注意的地方?
假设t为大表10W行数据,t1为小表100行数据。
EXPLAIN select * from t1 left join t on t1.a = t.a;
• 当两个表关联条件中a字段都存在索引时,执行流程是遍历t1所有行,取出每一行的t1.a字段去t表中进行查询,满足条件则组成结果集。扫描行数为t1为全表扫描 , t表的查询是走B+树搜索,扫描行数大概为200行,判断行数为100行。结论是使用小表作为驱动表,大表作为被驱动表,
• 当关联条件中t表a字段不存在索引时,查看语句的执行计划,发现t表和t1表都是走的全表扫描,执行流程是遍历t1表所有行数据放入内存join_buffer中,然后扫描t表数据和join_buffer数据做判断,满足条件则组成结果集。扫描行数10W+100行,判断行数为1000W行数据。结论是大表的关联条件中尽量走索引,使判断行数和扫描行数尽量少一些。
• 其次当join_buffer满了的时候,驱动表会分段存放,但是被驱动表又需要重新整表扫描比较剩余行数,结论是驱动表的存放到join_buffer的数据尽可能小,或者修改调大join_buffer_size参数。
• 小表和大表? 小表是指加了where条件之后,数据较少的表才叫小表。当两个表的行数都相同时,再比较两个表存放在join_buffer中较小的表作为驱动表。