MySQL基本知识点梳理和查询优化

3、mysql的utf8最大是3个字节不支持emoji表情符号，必须只用utf8mb4。需要在mysql配置文件中配置客户端字符集为utf8mb4。jdbc的连接串不支持配置characterEncoding=utf8mb4，最好的办法是在连接池中指定初始化sql，例如：hikari连接池，其他连接池类似spring.datasource.hikari.connection-init-sql=set names utf8mb4。否则需要每次执行sql前都先执行set names utf8mb4。

4、msyql排序规则(一般使用_bin和_genera_ci)：

utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感，

utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感，但是目前MySQL版本中已经不支持类似于***_genera_cs的排序规则，直接使用utf8_bin替代。

utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。

那么，同样是区分大小写，utf8_general_cs和utf8_bin有什么区别?

cs为case sensitive的缩写，即大小写敏感;bin的意思是二进制，也就是二进制编码比较。

utf8_general_cs排序规则下，即便是区分了大小写，但是某些西欧的字符和拉丁字符是不区分的，比如ä=a，但是有时并不需要ä=a，所以才有utf8_bin

utf8_bin的特点在于使用字符的二进制的编码进行运算，任何不同的二进制编码都是不同的，因此在utf8_bin排序规则下：ä<>a

5、sql yog中初始连接指定编码类型使用连接配置的初始化命令

四、SQL语句总结

常用的但容易忘的：

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

8、连表删除：DELETE user FROM user,black WHERE user.id=black.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”处理

六、千万大表在线修改

mysql在表数据量很大的时候，如果修改表结构会导致锁表，业务请求被阻塞。mysql在5.6之后引入了在线更新，但是在某些情况下还是会锁表，所以一般都采用pt工具( Percona Toolkit)

如对表添加索引：

如下：

pt-online-schema-change --user='root' --host='localhost' --ask-pass --alter "add index idx_user_id(room_id,create_time)"  
D=fission_show_room_v2,t=room_favorite_info --execute 

七、慢查询日志

有时候如果线上请求超时，应该去关注下慢查询日志，慢查询的分析很简单，先找到慢查询日志文件的位置，然后利用mysqldumpslow去分析。查询慢查询日志信息可以直接通过执行sql命令查看相关变量，，常用的sql如下：

-- 查看慢查询配置 
-- slow_query_log  慢查询日志是否开启 
-- slow_query_log_file 的值是记录的慢查询日志到文件中 
-- long_query_time 指定了慢查询的阈值 
-- log_queries_not_using_indexes 是否记录所有没有利用索引的查询 
SHOW VARIABLES LIKE '%quer%'; 
 
-- 查看慢查询是日志还是表的形式 
SHOW VARIABLES LIKE 'log_output' 
 
-- 查看慢查询的数量 

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!