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' 
 
-- 查察慢查询的数目 

（编辑：湖南网）

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