玖叶教程网

常用的但容易忘的：

1. 如果有主键或者唯一键冲突则不插入：insert ignore into
2. 如果有主键或者唯一键冲突则更新,注意这个会影响自增的增量：INSERT INTO room_remarks(room_id,room_remarks) VALUE(1,"sdf") ON DUPLICATE KEY UPDATE room_remarks="234"
3. 如果有就用新的替代，values如果不包含自增列，自增列的值会变化： REPLACE INTO room_remarks(room_id,room_remarks) VALUE(1,"sdf")
4. 备份表：CREATE TABLE user_info SELECT * FROM user_info
5. 复制表结构：CREATE TABLE user_v2 LIKE user
6. 从查询语句中导入：INSERT INTO user_v2 SELECT * FROM user或者INSERT INTO user_v2(id,num) SELECT id,num FROM user
7. 连表更新：UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id

锁相关(作为了解，很少用)

1. 共享锁： select id from tb_test where id = 1 lock in share mode;
2. 排它锁： select id from tb_test where id = 1 for update

优化时用到：

1. 强制使用某个索引： select * from table force index(idx_user) limit 2;
2. 禁止使用某个索引： select * from table ignore index(idx_user) limit 2;
3. 禁用缓存(在测试时去除缓存的影响)： select SQL_NO_CACHE from table limit 2;

查看状态

1. 查看字符集 SHOW VARIABLES LIKE 'character_set%';
2. 查看排序规则 SHOW VARIABLES LIKE 'collation%';

SQL编写注意

1. where语句的解析顺序是从右到左，条件尽量放where不要放having
2. 采用延迟关联(deferred join)技术优化超多分页场景，比如limit 10000,10,延迟关联可以避免回表
3. distinct语句非常损耗性能，可以通过group by来优化
4. 连表尽量不要超过三个表

踩坑

1. 如果有自增列，truncate语句会把自增列的基数重置为0，有些场景用自增列作为业务上的id需要十分重视
2. 聚合函数会自动滤空，比如a列的类型是int且全部是NULL，则SUM(a)返回的是NULL而不是0
3. mysql判断null相等不能用“a=null”,这个结果永远为UnKnown，where和having中,UnKnown永远被视为false，check约束中，UnKnown就会视为true来处理。所以要用“a is null”处理