开发者学堂课程【云数据库RDS MySQL从入门到高阶：【初级】MySQL 开发者规范】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/996/detail/15071
【初级】MySQL 开发者规范
内容介绍
一、MySQL 基础介绍
二、字段设计
三、表结构设计之数值类型
四、表结构设计之字符串类型
五、表结构设计之时间类型
六、索引设计之主键
七、索引设计之辅助索引
八、设计高效的索引策略
九、设计高效的索引策略
十、应用优化案例
一、MySQL 基础介绍
上图是较为经典的 MySQL 结构图，主要有8部分。
1、连接池：处理一些连接、绘画、管理等信息。
2、管理工具：面向 DB 或者运维的备份工具以及监控管理工具等。
3、SQL 接口
4、解析器
5、优化器
6、缓冲池
7、插件式存储引擎：这里面列举的比较多，我们推荐使用 innoDB 存储引擎，因为innoDB 存储引擎支持事务，对于索的力度支持行级别的并发。相比于 MYISAM 有很大的优势，MYISAM 不支持事务，而且索对性能的影响是比较大的。
8、物理文件：数据库的认证文件。
以上是 MySQL 的基本介绍
二、字段设计
1、对于应用通向为表的时候，需要对业务逻辑进行转化，我们设计表的原则为：
1、长度越小越好
2、类型越简单越好
3、尽量避免 NULL
2、决定列的类型
判断类型是数值、字符串、时间等。
（后面会有例子来阐述上述原则）
三、表结构设计之数值类型
1、数值、数据范围
下面列了四个种类：Tinyint、smallint、int、bigint
2、unsigned 属性表示非负数，把数值上限提高一倍。适用于主键
数据范围有一下四个种类：Tinyint、smallint、int、bigint，他们的范围大家注意一下，表内给出的是有符号的，无符号的需要乘2.
常用的数据是 int 类型。
表中给出了符号，没有给出的话乘2.大家可以根据自己的业务场景来设计，选择 int 作为主键。但是有一些特殊的业务，比如量很大的业务，消息类型对于21亿可能过一段时间就超过去了，数值不能够全部包含进去，这时可能会发生溢出现象。这时就推荐使用 bigint，无符号的是182亿。bigint 的数值范围很大，可以满足绝大多数的场景。
3、浮点数：对于有小数点的 float 和 double、decimal 这三种类型来支持。
decimal 只有对小数进行精确计算的时候才使用它，例如：金融类业务是不能有四舍五入的情况（钱或利率相关的）。
float 和 double 支持浮点数的近似运算，有一定的取舍。
4、三者之间的区别
Float，double 为非标准类型，在 DB 中保存数值的近似值
decimal为标准类型，在DB中以字符串的形式保存数值
上图中创建了一个表：
表中有C1、C3字段，C1字段是float类型；C3字段是decimal类型。之后插入两个数据，小数点分别是：.12、.21。当我们取出之后看C1字段小数点后变成了00.进行了四舍五入。C3字段还是保留了.12这样的精度。
通过这个例子我们可以直观地看到它们之间的区别。
四、表结构设计之字符串类型
上图中列举了三种：
1、固定长度的 char：定长、存储特点：去掉末尾空格；
适用范围：长度接近或固定的长度，如：MD5值
2、可变长度的 Varchar：
特点：可变字符长度、需要额外的字节存储长度（如果大于255就需要两个字节，如果是小于255需要一个字节。Varchar 理论上存储最大是5535，可以存储实际看行总体的大小，看一行最多能存多少。最少存两行数据，一行有8K.但具体数值换石油一定差异的）、保留末尾空格
适用范围：N 尽可能的小；比如 NAME 字段设置成 Varchar 20、30其实是合理的对于中国人或者外国人的名称可能20-30个字符就已经足够了，如果设置成200-300甚至1000显而不符合业务常理了。其他一些开源的软件，他们对于名字 JOB、NAME 等等设计都是 Varchar1024、2048等等，显然是超出了我们的正常的业务范围。显然是不合理的。
3、大字段 Text/blob：
特点：存储大数据、利用外部存储区域（不是一直放在同一行或者同一个页块里面，他会指向另外一个页块来存储，如果要去访问数据、大字段数据的话，数据量比较大，超过了 max_sort_length（会重新排序）因此不推荐使用 Text/blob
上图是 char 和 Varchar 对于末尾空格的处理方式，对于 V 和 C 两个字段插入两个“ab ”转换的值。通过 concat 看到 Varchar 是保留了某一个空格，char 去掉了某一个空格。通过上面的例子可以直观的看到两个对于末尾空格的差异
4、text 类型（最明显的是对性能影响）
1、查询、DML 性能低于 Varchar
解决方法：
1、父子表：拆分 text 段拆分到子表当中（正常来说直接访问主表即可，不会把text字段拿出来。MySQL 访问是按照页块来处理的，是访问某些字段将整页拿到内存里）这样的话如果我们有拆分的话，会把 Text/blob 也拿到内存里面，实际上这个是不需要访问的，会带来额外的L和内存 CPU 的压力。
2、将text字段存放到 HBase/ES（例如商品详情这样的放在 HBase/ES 内显然更合适）
测试数据：
上图中创建了5张表，有一个字段 C2是有不同的类型的。
在每一个表内插入1万行数据，之后我们可以检测它的准备耗时，很明显 text 的耗时是 Varchar200的5倍。显而就是比较大的。实际的影响也会明显的感受的到的。
每一条记录更新20万次，可以看一下他的耗时，很明显 text 字段类型的耗时是362秒，是远远高于 Varchar200、1000、2000更新的耗时，是他们的大概4倍多。
Binlog 大小：肉眼可见更新 text 字段类型产生21个 G 的日志量，会带来更多的网络带宽消耗，会对性能产生很大的影响。例如：秒杀场景或者双十一大促，如果 text 设置的不合理，可能会对于业务或者数据库带来比较大的性能压力，可能会导致数据库堵塞。
Select c2不同字段类型的时间消耗：显然 text 字段的消耗是比较大的81秒。网卡带宽147兆。上图是20万次，若压力很大的情况下，1秒1万次这样会给系统带来很大的压力，会产生稳定性风险。
五、表结构设计之时间类型
1、时区（datetime 没有时区概念，反之 timestamp 有）
2、范围：timestamp 有时间范围（2038.1.1）对业务的发展埋下隐患
datetime 的时间很宽泛（9999.12.31）能够满足绝大多数人对业务的需求（推荐使用）现在涉及到的业务到2038可能还在，会给业务的发展埋下隐患。因为 MySQL 对于目前而言 timestamp 如果超过了他的阈值，数据就 crash 掉了。当然以后 MySQL 会对做一个处理，看一下 timestamp 的数值是否可以延长。从目前的使用效果来看，还是推荐使用 datetime 来表示时间。
上图是两个字段类型对于时区上的差异
插入了 NULL 这个值，查询第一次2013-06-23 23：59：12，然后改变时区，将东8区改成了东9区，C1字段就变成了24号。
3、表结构设计的命名规范
（1）避免大小写驼峰，MySQL 小写比较合适一点。
（2）避免 MySQL 保留字冲突
上图是表结构存储引擎的选择和组件索引（推荐使用 innoDB 存储引擎、表一定要带有主键，如果表没有主键，会对组重复制带来很大影响。若使用联合主键或者字符串主键，相比于数值类型的占用的块更多，会带来更多的L压力，层高也会变化。不推荐使用text存放大数据，会带来性能损耗。禁止使用分区表，因为我们目前对于水平的数据库拆分的架构已经很成熟了，使用分区表会满足分区的需求，另外对于分区键以及全局索引会有一定的要求，如果使用分区表不当会带来性能的风险）。
总结：
1、表一定要有主键，推荐使用 bigint，unsigned 来做主键，防止溢出。
2、要考虑存储类型的大小，还要考虑 MySQL 如何对其进行计算和比较。从极致性能来讲，MySQL 处理数值类型肯定是要比处理字符类型的消耗的 CPU 帧数比较少。
3、选择最小的数据类型要考虑将来留出的增长时间（比如设计某个字段，但是随着业务的发展，表的变化比较大，可能会改变当前的类型）
4、注意隐式转换，例如：
创建一个表，注意用的是 ID:Varchar30，有四行记录，插入一行001这个记录去查，出来 name1这个记录。但是查 user-ID 001这个数值他会出现两行，因为数值001和数值1是相等的，所以出现了两行。
字符的001不等于1，只出现一行，这是业务逻辑的问题，是有一定歧义的，因此我们在设计使用的时候要尽量避免这样的歧义出现。
Use ID等于字符1，这是因为走了主键索引，这是正常的。如果使用数值的话，走了隐式转换，导致强加扫描，会产生性能问题。
选择字段类型时充分了解特性，优缺点，使用场景
避免等号左右数据类型不一致，如果字符集不一样也会导致隐式转换全面扫描
隐式转换会导致全表扫描
六、索引设计之主键
上图显示了主键的特点
叶子节点存在主键和数据记录，而且叶子节点是有序存放的。需要注意有序。
七、索引设计之辅助索引
辅助索引也是有序存放的，但辅助索引的叶子节点内存的是除了辅助介质以外，还存放了对应的主键的质。因为辅助索引查找记录的方式是先通过辅助索引的必加数去访问叶子节点再拿到对应主键的记录，再去访问主键。然后再走一遍。
八、设计高效的索引策略
1、索引的选择性
选择轻度高的字段作为前导列
轻度高值得是：selectivity=distinct values/total rows 越接近1，轻度越高。这样访问的记录就会越小，过滤性越强。比如1万行里面访问 ID=3，这样只要访问一个就满足要求了。性能就是最好的，如果是访问0=1的有9000条，那么访问1000条和9000条肯定是不一样的。
2、尽量避免 NULL
尽可能把字段定义为 NOT NULL，可以设置一个默认值，如数值类型的是0，字符类型的是为空。还有时间类型的，大家在注册某个会员或者某个APP，突然看到是1970年1月1日等等，这是一个比较典型的例子。当然有可能是000.用0来表示时间日期。这就是给她设置了一个默认值，而不是 NULL。因为 NULL 可能会让大家觉得出现问题。
第二个是 MySQL 对 NULL 做出优化，NULL 的性能可能会比 NOT NULL 加默认值低70%。大家有兴趣的可以测试一下。
3、隔离列
索引字段不能是表达式的一部分，不能出现运算类的东西，比如现在这个例子to_days(now0)-to_days(gmt_created) <=10，这个实际是对 created 字段做了一些运算，实际上MYSQL5.7之前是没有办法对于函数索引做支持的。若使用上面的第一个案例之后，后面的内容就不能走索引，正确的写法是第二种。
Select id, value from tab where to_days(now0)-to_days(gmt_created) <=10;
正确写法：
select id, value from tab where gmt _created >= DATA _SUB(now),interval 10 day );
4、覆盖索引（常见的优化手段）
通过访问辅助索引就能够拿到想要的数据，不需要回表。
正常而言有很多字段之后 from table where 某个索引条件，这时候会通过某个辅助索引找到主键再回到表找到对应的行记录把数据拿出来；如果使用覆盖索引的方式，如例子：select a,b from table where a=2如果将 ab 作为一个索引的话，在辅助索引的叶子节点里面就可以访问到数据，就不需要回表了。这样性能就会提高。
5、组合索引
对查询语句的表其实不止一条，应该有很多种条件组合。对于组合索引呢，原则上说尽量让一个索引被多个 Query 语句利用。
尽量避免同一个表上的索引数量。索引是有成本的，插入、删除会带来额外的成本，会影响性能也会影响空间大小。
九、设计高效的索引策略
1、SQL where 后面字段的顺序其实和组合索引中的字段顺序没有必然关系。这里其实是等值的。
比如 where b=x and c=y and a=2和索引的顺序 idx_abc(a,b,c)是毫无相关的。很多人有一定的误解，where 条件中的 bca 他的索引就是 bca，这样理解其实是不对的。如果是不等于应该放在后面。underby 这种的应该放在后面。
2、最左前缀原则
索引中的第一个字段必须在where条件中，如果不在就没有办法用到索引。
比如：idx_ab(a,b,c),a字段必须在where条件中且等值判断。一定要有a要是只有b或者c是用不到的。
3、重复索引
（a）,(ab)--不用
（a,b,c）
实际上没有ab两个索引的话，对于 abc 单独一个索引where a=？也可以复用到这个索引。所以避免重复索引，多余的索引就去掉。
4、尽量使用覆盖索引无需访问表，避免随机 IO，提升性能。
上图是索引的设计规范。
mysql会一直向右匹配直到遇到范国查询(＞、＜、between、like)就停止匹配，比如a=1 and b=2and c>3 and d=4 如果建立(a,b,c,d)（d 这个索引在大于3之后，我们实际上是用不到的）顺序的索引,d是用不到索引的，如果想要用到所有的字段，就要建立(a,b,d,c)的索引，a,b 的顺序可以任意调整。
其他的大家自己了解一下。
十、应用优化案例
不用 select*
1、更多消耗 CPU、内存、IO、网络带宽
2、只需要访问需要的列或者指定的列就好了。select把不必要的大字段都拿出来了，这样就会带来内存IO和网络带宽性能上的损耗。
尽量不用 SELECT*，另取需要数据列（会使性能损耗，直接取用具体的列访问）
3、更安全的设计：减少表变化带来的影响（对于表的变更，前端可能会没有注意到多了一个字段，或者好几个字段，变化的前端就是出错，影响前端业务的连续性和稳定性。）
4、使用覆盖索引提供可能性（使用具体字段可能会使用覆盖索引）
5、select/JOIN 减少硬盘临时表生成，特别是有 TEXT/BLOB 时（使用 TEXT/BLOB 查询内容较多时会导致离失表）
例子：
SELECT * FROM tag WHERE id = 999184
SELECT keyword FROM tag WHERE id = 999184
避免负向查询和％ 前缀模糊查询
避免负向查询 NOT、！=、<＞、！＞、NOT EXISTS、 NOT IN. NOT LIKE
等（对于 MYSQL 来说是范围查询，不能用类似于等值那样使用索引。这个基于优化器的成本，成本来源可能会导致全面扫描，性能上会有很多的损耗）
例子：
MySQL> select * from post WHERE title like ‘北京%’；
298 rows in set (0.01 sec)
MySQL> select * from post WHERE title like ‘%北京%’
572 rows in set (3.27 sec)
两者之间差了30多倍性能。
不推荐使用子查询，会带来比较大的性能损耗
sub_test 表中根据 group_id 取得什 k_test_id(2,3,4,5,6),然后在到 test 中，带入 test id-2,3,4,5,6取得查询数据，但是实际 MySQL 的处理方式：
select t.* from test t where exists ( select * from sub_test where group_id= 10 and sub_test.fk_test _id-test.id)
实际上是将每一个 text 的值和 sub_text 做比较。如果没有 where 条件就是 m*n 了。比如 T 有 M 行， sub_test 有N行，要去比较 M*N 次，这个里面相当于有结果级次，结果符合 group_id= 10的条件。这样会带来额外的性能损耗的。
优化：将结果集数据取出来上推，取出来和 text 做比较，这样性能会好很多，只要访问一次 sub_test 表就好了。
mysql 会扫描 test 中的所有数据，每条数据将会传到子查询中与 sub_test 关联，子查询不能首先被执行，如果 test 表很大的话，那么性能上将会出现问题：如何解决呢？
select t1.* from test t1, (select (R test_id from sub_test where group_id-10 ) t2 where t1.test_id-t2. k_test_id;
注意控制N的中值的个数，建议 n 小于200，（参数 eq_range_index_dive_limit)（大家感兴趣可以搜一下值的大小，会影响 MYSQL 优化器获取表的信息的准确性和时效性）
例子：
select id, name, col from tab WHERE phone in ('12347856' , '42242233*)
大 SQL 和多个简单的 SQL 意思就是大事务和小事务，尽量避免大事务。
1、一条 SQL 只能在一个 CPU 运算
2、7000+ QPS 的高并发场景，1秒大 SQL 意味着可能一条大 SQL 就把整个数据库堵死拒绝大 SQL（通道数容不下那麽多数据，通道被占用后续一直在等待，会导致数据库处理出现故障，导致应用超时，导致堵死）
拆解成多条简单 SQL
1、简单 SQL 缓存命中率更高
2、减少锁表时间
3、用上多 CPU，提升总体的性能
分页查询使用延迟关联解决
推荐使用延迟关联
低效分页
通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。
select id, cu_id, name from tab where type =0 and endt>='2014-05-29' order by id asc limit 149420 ,20;但是 limit 越到后面越慢，本身要通过索引去扫描表，每次都要定位到这里然后去扫描，性能会很慢。
延迟关联
推荐使用延迟关联，通过覆盖索引把主键ID拿出来直接定位到我们所需要的这些数据就够了，性能会得到有效的提升。不用每次回表，会便利很多。
select a.id a.name,a.gmt_create from tab a, (select id from tab where type =0 andendt >= '2014-05-29'order by id asc limit 149420 ,20 ) b where a.id=b.id;
总结：
1、不用 select*
2、不用存储过程/触发器/外键（实际是将业务逻辑下沉到数据库里面，对于业务的发展是不利的。当前只是业务代码少开发了，但是总体而言随着业务的发展，数据库要拆分，在存储中会影响拆分的。所以推荐数据库只做存储，业务逻辑上移到业务层）
3、避免负向查询和％ 前缀模糊查询
4、注意 IN（） 值的个数
5、禁止未经 DBA 确认的子查询（在执行子查询的时候要看一下执行计划到底是不是想要的执行计划）
6、使用简单 SQL（避免大的 SQL 把数据堵死）
7、分页查询使用延迟关联解决
