MySQL学习笔记

基础

• redo log是InnoDB引擎特有的日志，binlog是Server层的日志

redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

• 主键索引(聚簇索引)存的值是整条记录, 普通索引(二级索引)存的值是主键索引的值，用普通索引查询，需要查询两次，称为回表
• 如果表里面只有一个索引，而且是统一索引，就应该将该字段设置为primary key
• 对于其他情况，应该使用自增主键，因为整型或长整型的主键占用的空间小，查询速度也快。
• 覆盖索引, 将idCard, name建一个联合索引，如果频繁根据idCard查询name, 将不需要回表，直接就能拿到name
• 最左前缀原则：如果在type字段建立了索引，type的类型有place, settle, 则查询type like 'p%'是能用到该索引的。如果有了一个(idCard, name)的联合索引，就不需要再建idCard的索引了，因为最左前缀原则可以保证能利用到该索引。
• 重建索引：单独删除和新增主建索引都会将整张表重建。应该使用下面的语句。

alter table T engine=InnoDB

• 全局锁，一般用于全局逻辑备份库，下面命令会让整个库处于只读状态，任何的写操作会阻塞

Flush tables with read lock (FTWRL)

• MDL(meta data lock), 所有DML操作都自动加MDL读锁，这时别的线程不能执行DDL。当执行DDL时，会加MDL写锁，此时，所有其他DML操作都将阻塞。
• 当对表做DDL操作时，特别是那些频繁读写的表，需要特别小心，否则会导致所有操作阻塞。解决办法是在alter table语句后面加上等待时间，如果time out, 就释放锁，不影响其他DML操作。
• 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。比如前面操作的表是每个用户一条记录的，后面操作的表是公共的记录。
• 如果业务已经确保记录的唯一性，则优先考虑选择普通索引，因为唯一索引在插入或更新时，需要读取数据判断是否满足约束，这个操作消耗时间较长。
• 设置慢查询超时时间为0，方便debug

set long_query_time=0;

实战

• 优化器对扫描行数的预估如果不准确，会导致索引选 错，影响性能。可以通过下面的命令，让优化器重新统计信息。

analyze table t

• 给字符串加索引，如果前缀部分区分度高，则只需要给前缀加索引，不需要整个字符串。如果后缀部分区分度高，则存储这个字段时反过来存，依旧把前缀部分建立索引。
• innodb_file_per_table参数如果设置为on, 则表数据会放在单独的文件中，drop table之后就会把整个文件删掉。如果设置为off, 则会放在共享表空间中，删掉数据之后，空间并不会回收。
• 对表做delete之后，要想回收空间，需要重建表。它会把数据拷贝到一个临时表，再做替换

alter table t engine = InnoDB

• alter table/analyze table/optimize table的区别
  • alter table就是重建表
  • analyze table是重新统计索引信息
  • optimize table等于上面两个操作加起来
• count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。
• 性能：count(字段) < count(id) < count(1) = count(*), 优先使用count(*)
• insert ignore, 实现幂等性

insert ignore into friend(friend_1_id, friend_2_id) values(A,B);

• 确定一个排序语句是否使用了临时文件

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算Innodb_rows_read差值 */
select @b-@a;

• 如果返回的记录太多字段，mysql会只把要排序的字段和主键放入sort buffer中，排好之后，再根据主键去取其他字段。可以根据下面的字段控制最大长度

SET max_length_for_sort_data = 16;

• 随机排序

select word from words order by rand() limit 3;

• 当where语句中有函数操作时，会不走索引

mysql> select count(*) from tradelog where month(t_modified)=7;

• 警惕隐式类型转换。比如tradeid是varchar，而跑下面的sql

mysql> select * from tradelog where tradeid=110717;

实际上相当于:

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

加了函数操作，就不走索引，导致了全表扫描。 当两张表的字符编码不一样时，join表也有可能导致全表扫描，因为字符编码需要转换，相当于加了函数操作。

• 查看正在跑的进程，一般可以用于排查锁等待

show processlist

再查看具体进程的详情:

select * from information_schema.processlist where id=1;

还有一种方法

select blocking_pid from sys.schema_table_lock_waits

• 查看什么进程占用了锁

select * from t sys.innodb_lock_waits

• 注意区分快照读和当前读。 当前读指的是select for update或者select in share mode，指的是在更新之前必须先查寻当前的值，因此叫当前读。 快照读指的是在语句执行之前或者在事务开始的时候会创建一个视图，后面的读都是基于这个视图的，不会再去查询最新的值。

• 连接数过多的处理 show processlist，然后kill掉那些sleep的连接。先断开事务外的连接，再考虑是否要断开事务内的。可通过下面的SQL查看什么连接是在事务内:

select * from information_schema.innodb_trx

断开连接之后，一定要通知相应的业务开发团队。

还有一种方法是，通过–skip-grant-tables跳过数据库的权限验证。

• query rewrite. 可以临时将输入的语句改写成另外一条语句。

insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");call query_rewrite.flush_rewrite_rules();

主备切换

• 可用性优先，直接切换主从，可能会导致数据不一致的情况。

可靠性优先

• 判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
• 把主库 A 改成只读状态，即把 readonly 设置为 true；
• 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
• 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
• 把业务请求切到备库 B。

误删数据的处理方法

• 如果是delete误删数据，则可以用Flashback工具来恢复，前提是binlog_format=row 和 binlog_row_image=FULL
• 预防用delete误删数据，sql_safe_updates 参数设置为 on, 这样如果delete和update语句没有加where条件，会报错
• 误删库或表，则只能使用全量备份加增加备份的方式恢复。还有一种方法是使用延迟备份。
• 预防误删库：1)truncate之前必须先把表改名，观察一段时间，对业务没有问题之后，再删除。2)改的名字要个_to_be_deleted的后缀，交给一个脚本来执行，该脚本只会删除带有该后缀的表。

面试

Mysql中ACID是由什么保证的？

• 原子性是由undo log保证的
• 一致性是由其他3个特性来保证的
• 隔离性由MVCC保证
• 持久性由redo log保证