Mysql小知识

• 关于多列联合索引列顺序的注意事项

创建多列索引时，建议将选择性较高的列放在左边。计算列的选择性方式通常为：SELECTCOUNT(DISTINCTcol)/COUNT(col)FROMtb，该值越接近1表示选择性越好

• MySQL中，int(10)和int(3)的区别

在MySQL中，int(10) 和 int(3) 的区别主要体现在显示宽度上，而不是存储空间或数据范围。
1. 存储空间和数据范围
无论是 int(10) 还是 int(3)，它们都占用 4 字节的存储空间，数据范围都是从 -2147483648 到 2147483647（有符号）或 0 到 4294967295（无符号）。2. 显示宽度
int(10) 和 int(3) 中的数字（10 和 3）表示的是显示宽度。显示宽度仅在使用 ZEROFILL 时有效，用于指定在显示数值时填充零的宽度。int(10)：表示显示宽度为 10 个字符。如果数值不足 10 位，MySQL 会在左侧填充零（前提是使用了 ZEROFILL）。

• 为什么使用utf8mb4而不是utf8

在MySQL中utf8mb4是utf8的超集，前者可以兼容更多的字符，比如Emoji表情，而utf8的编码只支持最长三个字节的字符，utf8mb4则最长支持4个字节。
将utf8升级为utf8mb4不必担心丢失数据，修改表的字符集的方式如下：ALTERTABLEtbl_nameCONVERTTOCHARACTERSETutf8mb4;

• 合理使用通配符

有前项通配符的话（例如：like'%foo'）是无法使用已有索引的，而不使用通配符的like查询(like‘foo’)可能存在逻辑错误，在逻辑上他和等值查询相同

• MySQL在参数比较时会进行隐式转换，规则有哪些

1、两个至少有一个为NULL，返回结果为NULL，例外是使用⇔对两个NULL进行比较时，会返回1
2、两个参数类型一样（均为整数或者字符串），不进行类型转换
3、十六进制的值和非数字进行比较时，会被当作二进制串
4、有一个参数为timestamp或者datetime，另一个为常量，常量会被转化为timestamp
5、一个参数为decimal类型，如果另外一个为decimal或者整数，会将整数转化为decimal进行比较，如果另一个参数是浮点数，会将decimal转化为浮点数。
6、所有其他情况下，两个参数都会被转换为浮点数进行比较。

• MySQL库名、表名、字段名长度限制及命名规范

MySQL库名、表名、字段名长度限制均为64个字符。
命名以字母或下划线开头，名字只允许使用字母、数字和下划线。请统一大小写，不要使用驼峰命名法。

• CHAR和VARCHAR

特性	CHAR	VARCHAR
存储方式	固定长度	可变长度
存储空间	固定占用	根据实际长度占用
性能	较高	较低
最大长度	255 个字符	65535 个字符
空格处理	自动填充和去除空格	保留原始内容

• 前缀索引的一些小特点

当列长度较长时，会使得索引变得大且慢，而前缀索引可以节约索引空间，但是会降低索引的选择性。前缀索引的诀窍是选择足够长的前缀保证选择性，同时又不能太长，以节约空间

• NOW()和SYSDATE()函数有什么区别

两者都是获取系统时间的函数。NOW()在开始时就会获取时间，而SYSDATE()在执行时才会获取时间，所以，使用SYSDATE()会导致主从不一致，推荐使用NOW()来获取时间

• 怎样存储IPv4地址更节省空间

当使用字符串存储IPv4地址时，使用varchar要定义为varchar(15)，而使用无符号整数只需要4个字节即可，更节省空间

• MySQL每个表最多允许创建多少个列

MySQL每个表最多允许创建4096个列，不同的存储引擎的限制又有不同，InnoDB允许的最大列数为1017。虽然MySQL表允许创建的列很多，但一般建议不要超过40个列，避免因为列过多导致单行记录过长，影响查询效率

• 为什么不推荐使用BOOLEAN

MySQL没有内置的布尔类型，BOOLAEAN和BOOL是TINYINT(1)的同义词，MySQL通过0和1来代表TRUE和FALSE，该字段除了0和1，只要是小于128的数字都可以插入成功，而当使用“WHEREcol=TRUE”时，只会匹配到col=1的列，只能使用ISTRUE

• 前缀索引最大长度是多少

CREATEINDEXpart_of_nameONcustomer(name(10))中的name(10)对于非二进制字符串类型为字符数，对于二进制字符串类型则为字节数。InnoDB表前缀索引的最大长度是767Bytes，以utf8mb4为例最大允许191个字符。如果开启innodb_large_prefix选项，最长可以达到3072Bytes

• GROUPBY的隐式排序

MySQL对于GROUPBY请求会隐式的按照GROUPBY的列的顺序进行排序，如果不需要排序，可以增加ORDERBYNULL来避免无谓的排序

• ALTER操作的建议

ALTER操作会对线上造成影响，即使能够使用在线工具也建议合并同一张表的多条ALTER操作，以减少操作次数

• INSERT含有多列联合唯一索引的表为什么会导致死锁

对于多列联合唯一索引，在插入之前进行检查会加锁，在插入时会加锁。由于不同记录的加锁顺序不同，在并发时可能会造成死锁
解决方法：
- 固定加锁顺序：确保事务按相同顺序加锁。
- 减少事务长度：缩短事务执行时间，降低锁竞争。
- 重试机制：捕获死锁异常后重试事务。
- 调整隔离级别：降低隔离级别（如读已提交）以减少锁冲突。
- 优化索引设计：避免不必要的唯一索引。

• ORDERBY注意事项

混用 ASC 和 DESC 可能导致数据库无法有效使用索引，从而影响查询性能。
通过创建匹配的索引、调整查询逻辑或利用数据库的降序索引支持，可以显著优化性能。
在设计索引和查询时，需要结合实际查询模式和数据分布，选择最优方案ORDERBY的条件为表达式时，将无法使用索引

• OR在什么时候最好使用IN替代

OR在查询索引列时最好使用IN，IN-list可以用于索引检查，并且优化器可以对IN进行排序，以匹配索引顺序

• IN的注意事项

- IN元素过多可能会导致全表扫描，对于连续的数值，建议使用BETWEEN
- 尽量不要使用NOT IN，这将会导致无法使用索引，出现全表查询，影响较大，NOT LIKE也是同理

• ALTERTABLE中因为逗号产生的误操作

ALTER允许通过逗号连接多个操作（ADD，DROP，ALTER，CHANGE）
例如：ALTER TABLE tb1 add col2 int DEFAULT 0 AFTER col2, COMMENT "新增列";
这种情况会错误的导致将整张表的备注修改为"新增列"，MySQL会解析为两个ALTER语句：
ALTER TABLE tb1 add col2 int DEFAULT 0 AFTER col2和ALTER TABLE tb1 COMMENT "新增列"

• UPDATETABLE中因为逗号和AND产生的误操作

在UPDATETABLE中一次更新多列，应使用逗号连接，而不是AND。
例如UPDATE tb SET col1=1 AND col2=2 WHERE col3=3;
这会导致MySQL解析为UPDATE tb SET col1=(1 AND col2=2) WHERE col3=3;因为(1ANDcol2=2)是一个判断式，因此判定为false，MySQL会将tb表中所有满足col3=3的列的col1修改为0。正确写法为：UPDATE tb SET col1=1,col2=2 WHERE col3=3

• 因为日期未加引号产生的WHERE条件错误

类似"WHERE col<2019-09-20"日期应该用引号包裹，未加引号的话会被解释为 WHERE col<1990 从而产生错误

• COUNT(DISTINCT)多列时结果可能和预想的不同

COUNT(DISTINCTcol)计算该列除NULL之外，不重复行数，注意COUNT(DISTINCT col1,col2)如果其中一列全为NULL，那么即使另一列为不同的值，也会返回0

• JOIN中USING和ON的区别

ON后是连接条件，是一个表达式，而USING后加的是列名，例USING(col)等价于ON left_tbl.col=right_tbl.col，即USING后的列必须在两张表中同时存在

• 建议使用 TRUNCATE 代替DELETE * FROM tb

删除全表数据时，建议使用 TRUNCATE 代替 DELETE * FROM tb，相比之下，TRUNCATE 速度更快，所占用的系统和日志资源少
TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在二进制日志中记录TRUNCATE语句

• SUM(col)的注意事项

当某一列的值全为NULL时，COUNT(col)值为0，而SUM(col)值为NULL，因此在使用SUM()时，应注意NPE问题，建议使用 SELECT COALESCE(SUM(COL), 0) FROM tbl;来避免

• 上线前建议进行数据备份

建议在每次上线前，对数据进行备份，防止因为bug导致数据出现错误，一旦数据需要回滚，请及时联系DBA进行数据恢复

• 大量更新注意事项

MySQL5.7支持对更新请求进行EXPLAIN，可以在执行更新语句前，进行EXPLAIN，查看更新行数，如果涉及行数过多，必然会导致从库延迟以及主库读写阻塞，建议将其转换为主键更新并限制更新速度

• 更新数据时，一些防止误操作的小技巧

在开始更新前可以显式的开启事务，更新完成后可以校验数据的正确性后再提交事务，这样可以防止误操作。
如果是手工复制粘贴的话，可以先写注释符，确认无误后再去掉注释执行

• 通过OFFSET和LIMIT实现分页的缺点

使用LIMIT和OFFSET对结果集分页的复杂度是O(n^2)，并且会随着数据增大而导致性能问题。当OFFSET值过大时，MySQL会查询大量数据，然后丢弃大部分数据，所以在OFFSET值较大时，性能影响会比较大

• ORDERBY时列中多个行具有相同值，MySQL的处理办法

如果ORDERBY列中的多个行具有相同的值，则MySQL可以自由地以任何顺序返回这些行，并且返回的顺序取决于整体执行计划。换一种说法，这些行的排序顺序相对无序。所以在进行排序分页时，建议提供一个顺序唯一的排序方式，以确保结果集确定

• 数据超过字段定义长度后，MySQL将如何处理

对于字符串类型的字段，写入数据的长度大于字段定义的长度时，在不同的SQL_MODE下会有不同的表现。SQL_MODE为严格模式时，写入请求失败；在宽松模式下会自动截断

• 待续