Mysql基础

结绳记事，记录、思考，方有成长~

1. VARCHAR 和 CHAR的区别及应用场景

• 类型相似，但保存和检索数据的方式不同。CHAR(M)和VARCHAR(M)类型生命的长度标识要保存的最大字符数为M，不是字节数，例如：CHAR(30)可以占用30个字符，默认UTF8字符集存储，即90个字节。
• 如果分配的长度超过了M个字符，则对值进行裁剪以使其长度适合。
• CHAR是固定长度的字符串，它的长度固定位创建表时声明的长度。范围从0~255个字符。
  当保存CHAR值时，长度不够M，则会自动填充空格以使其达到指定长度。
  当检索到时，尾部的空格会被删除掉，这是MySQL服务器级别控制的，和存储引擎无关。
  CHAR类型适合存储大部分值的长度都差不多的数据，例如MD5值
• VARCHAR列中的值为可变长度的字符串，相对于固定长度的字符串，它需要更少的存储空间。
• 在保存VARCHAR的值时，只保存需要的字符数，然后再用1~2个字节来存储值的长度。
  VARCHAR类型是最长记录的长度值比平均值大得多的场景

2. LENGTH() 和 CHAR_LENGTH()
   LENGTH为字节长度，CHAR_LENGTH为字符长度，比如字段值为“数据库”或“DBA”，默认字符集为utf8，则LENGTH为9，CHAR_LENGTH为3

3. 保存值前缀相同场景
   如果数据库中某字段的值前缀相同的较多，比如：com.mysql.url、com.mysql.username、com.mysql.password，这种可按照字符颠倒的方式存储，这样做可以提高索引效率。

4. 保存URL场景
   存储URL值，一般推荐的做法是对URL值做一个散列，散列值最好是整形，然后存储这个散列值，并在其上做索引。例如：

SELECT CONV(RIGHT(MD5('http://www.mysql.com'), 16), 16, 10) AS HASH64

新建一个字段url_hash，用于保存类型为整形的散列值，查询可以这样

SELECT * FROM tab WHERE url_hash=CONV(RIGHT(MD5('http://www.mysql.com'), 16), 16, 10)  AND url = 'http://www.mysql.com'

推荐：散列函数用程序来实现，以减少在SQL侧的运算

5. 关于binlog
   执行一条写的SQL，默认为一个事务。会有TRANSACTION BEGIN、ROWDATA、TRANSACTION END 3部分binlog内容。
   如果事务发生回滚，则不会产生任何binlog，因为binlog是在事务commit提交时才会写入的。

6. 建表规约

• 数据库名、表名、字段名必须使用小写字母。MySQL在Windows环境下是不区分大小写的，但在Linux环境下默认区分大小写，所以数据库名、表名、字段名都不允许出现大写字母，避免节外生枝。
• 表名不使用复数名词。表名应该仅仅表示表里实体的内容，而不是实体的数量。对应的DO也同样需遵循该规则。
• 如果字段为非负整数，必须指定为unsigned，避免了误存负数，同时扩大了范围。
• 主键索引名以pk_开头，唯一索引以uk_开头，普通索引以idx_开头
• 小数类型定义为decimal，禁止使用float和double，否则精度会丢失。
• 如果存储的字符串长度相等，使用char长字符串类型；varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段为text，独立一张表出来，通过主键关联，避免影响其他字段索引效率。
• 单表记录数超过500万条、或者大小超过2G，再考虑分库分表。

7. 索引规约

• InnoDB作为MySQL最常用的引擎，默认使用主键作为“聚簇索引”，所谓聚簇索引，就是索引键值的逻辑顺序与索引所服务表中相应行的物理顺序相同，也就是说数据和索引（B+树）在一起，记录被保存在索引的叶子节点中。
• 业务上具有唯一性的字段，即使是多个字段的组合，也必须建立唯一索引。 因为即使在应用层做了非常完善的校验，根据墨菲定律，肯定会发生脏数据。
• 页面搜索禁止左模糊 或 全模糊，因为B-Tree的索引具有最左前缀匹配特性，如果左边的值未确定，会导致不走索引。
• 建立组合索引时，区分度最高的放在最左边。比如a的区分度最高，可以这样查询where a = ? and b = ?
• 利用“覆盖索引”进行查询操作，避免回表。索引的类型分为：主键索引、唯一索引、普通索引，而覆盖索引是一种查询效果，也就是查询的字段就是构建索引的字段，所以这样的查询结果，直接在索引中就能获取，而不需要再查具体的表数据了。
• 利用子查询优化超多分页场景，比如select * from 表a, (select id from 表a where limit 10000, 10) 表b where a.id = b.id。如果遇到分页查询时，count为0，直接返回，避免执行分页语句。
• SQL性能优化的目标：至少要达到range级别，要求是ref级别，如果可以是consts最好。range是对索引进行范围检索。ref指的是使用普通的索引（normal index），consts单表中只有一个匹配行（主键或唯一索引），在优化阶段即可读取到数据。index是索引物理文件全扫描，速度非常慢，这个index级别比range还低，与全表扫描是小巫见大巫。
• 在varchar字段上建立索引时，必须指定索引长度，没有必要对全字段建立索引，根据实际文本区分度决定索引长度即可。索引的长度与区分度是一对儿矛盾体，一般对于字符串类型数据，建立长度为20的索引即可。索引的区分度 = count(distinct left(列名, 索引长度)) / count(*)

9. SQL语句

• count(*)会统计值为NULL的行，count(列名)则不会统计值为null的行，同样，discount(列名)同样也不会统计为null的行。
• ORM映射中避免使用*号作为查询的字段列表，需要哪些字段必须明确写明。否则会增加查询分析器解析成本，同时加减字段，容易与resultMap配置不匹配。
• in操作能避免则避免，若实在避免不了，需仔细评估in后面的集合元素数量，要控制到1000以内。
• 不要写一个大而全的数据更新接口，传一个POJO对象，不管是不是目标更新字段，都执行update table set col1 = val1, col2 = val2，这是不对的。执行SQL时，不要更新无需改动的字段，一是效率低，二是易出错，三是增加了binlog存储。