aaron note mysql-Aaron的笔记

偷瓜的贼

　　mysql 绑定变量, query cache
　　其实MySQL中有内存表了, 完全可以不用这个功能. 对于Query Cache, MySQL提供了query_cache_type变量来控制发送到数据库的SQL是否进行Cache, 有三个值:
　　0, 对SQL语句不进行Cache.
　　1, 对于有SQL_NO_CACHE提示的SQL不进行Cache.
　　2, 仅对于有SQL_CACHE进示的SQL进行Cache.
　　新版的PHP5提供的接口mysqli支持Bound Parameters，mysql版本需要4.1以上，例子：
　　代码:
prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
　　$stmt->bind_param('sssd', $code, $language, $official, $percent);
　　$code = 'DEU';
　　$language = 'Bavarian';
　　$official = "F";
　　$percent = 11.2;
　　/* execute prepared statement */
　　$stmt->execute();
　　%>
　　mysql的存储过程，临时变量用@
　　存储过程，mysql5及以后declare的变量支持作用范围，范围可以小到一个begin end(全局和局部可以声明同样的变量名)
　　系统数据字典: INFORMATION_SCHEMA库和mysql库中
　　支持Event Scheduler
　　innoDB支持事务，支持行级锁(必须有索引才能行级锁)，行级锁有开销(与oracle不同)，开销大(与MyiSAM相比)
　　innoDB用于OLTP，并发好
　　innoDB采用表空间存储，支持分区Partitioning
　　innodb_flush_log_at_trx_commit 如果事务要求不是非常严格的话, 设为0或2可以上百倍提高insert update的并发数量
　　myisam单表order by的字段有索引的情况下, 还是会using filesort
　　innodb不会
　　mysql 5.1 分区表不支持唯一索引
　　主从之间用复制, 相同的主之间用DRBD, 不同的主之间用sharding或分区表
　　sharding比分区表好, 没有单点风险, 价格低廉
　　sharding: http://www.dbanotes.net/database/database_sharding.html
　　InnoDB不支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号
　　查询where中有a和b两个字段，建复合索引index(a,b)，比在a、b上分别建索引要快
　　复合索引有时也适合用于第一个字段的查询
　　复合索引左前缀性：必须用到索引的第一个字段
　　索引前缀性：只能like '1%'，不能like '%1'
　　在字段前加了函数，则索引会被抑制
　　对innoDB执行 select count(*) from table_name的时候，后面跟一个where 索引字段> ，会快很多
　　use 'multiple-row INSERT'
　　范围型select会引起Next-Key Locking
　　InnoDB支持多版本读(很好, 很强!!!)
　　InnoDB, for upate和lock in share mode用于行级锁和表级共享锁
　　innoDB数据恢复工具: http://code.google.com/p/innodb-tools/wiki/InnodbRecoveryHowto
(　　innodb_buffer_pool_size越大越好，官方推荐使用物理内存的50%－80%
　　innodb_flush_method=O_DIRECT是必须的，而innodb_log_file_size也不宜太大
　　见 MySQL InnoDB性能调整的一点实践
) mysql备份 导出(mysqldump很快):方法一 (不能支持sql语句):mysqldump -h127.0.0.1 -P3306 -uroot -proot -t --default-character-set=UTF8 db_name table_name > bak.sql(mysqldump支持MyISAM和InnoDB, 可以备份单个表、单个库或所有库)方法二 (不能支持分隔符、换行符等):mysql -h127.0.0.1 -uroot -proot --default-character-set=UTF8 db_name -r -N -e &quot;select * from table_name&quot; > bak.sql方法三 (不能支持在第一行包含字段名称):select * into outfile 'bak.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '&quot;' LINES TERMINATED BY '\n' FROM table_name;方法四 (方法三的改进):(select 'col1','col2') union all (select col1,col2 FROM table_name into outfile 'data.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '&quot;' LINES TERMINATED BY '\n');   mysql还原 导入(Load Data Local InFile很快):mysql -h127.0.0.1 -uroot -proot --default-character-set=UTF8 db_name -e &quot;LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE table_name Fields TERMINATED BY '\t' ENCLOSED BY '\&quot;' LINES TERMINATED BY '\n' (id,name)&quot;或mysql -u root -p db_name < 文件InnoDB引擎可以通过binlog还原到时间点　　由于spring连接池每次执行sql不一定是同一个连接, 所以写了一个通用存储过程:
drop procedure if exists add_and_return_insert_id;　　delimiter ?
　　create procedure add_and_return_insert_id(s text)
　　begin
　　DECLARE stmt text;
　　set @sqlstr=s;
　　prepare stmt from @sqlstr;
　　execute stmt;
　　select LAST_INSERT_ID();
　　end?
　　delimiter ;
存储过程中的事务:只有oracle高级, 存储过程 触发器默认级别就是 事务级的, SQL Server 2005开始也有了try...catch的功能mysql可能只能通过每个语句后面用select @error来判断, 不是null就执行rollback; 否则最后执行commit;