表结构设计规范

• 不在数据库做运算，复杂运算移动到程序端，节省数据库服务CPU等资源，同时经过普通运算或函数运算后的索引字段不能使用索引,但是经过函数运算字段的字段要使用可以使用函数索引。
• 控制单表数据量，纯 INT 不超过1000W行，含 CHAR 不超过500W行
• 合理分表，如按用户USERID，日期，地区等
• 建议单库不超过300-400个表
• 单表分表个数必须控制在1024个以内
• 表字段数少而精，有利于IO高效、全表遍历、表修复快、提高并发、alter table快
• 单表字段上限控制在20~50个
• 单表不超过50个纯 INT 字段、不超过20个 CHAR(10) 字段
• 单行不超过200Byte
• 适当牺牲范式、加入适当的冗余

字段设计规范

• 选用合适的数值字段类型（根据业务区分使用 tinyint/int/bigint，分别会占用1/4/8字节）
• 表达是与否概念的字段，使用 is_xxx 方式命名，数据类型为 UNSIGNED TINYINT （1表示是，0表示否）（注：此条暂时不遵守，kettle会把TINYINT改成Y和N，同步数据过程中会报错，改成char(1)）
• 小数类型为 DECIMAL ，禁止使用 float 和 double，存在精度缺损
• 时间类型使用 BIGINT ，统一存储时间戳，方便前端格式化（注：日期类型用int，create_at、update_at字段用bigint)
• 字段长度固定，或者长度近似的业务场景，适合使用 CHAR，能够减少碎片，查询性能高
• 字段长度相差较大，或者更新较少的业务场景，适合使用 VARCHAR ，能够减少空间
• 将字符串转化为数字，因为数字型比字符串型索引更高效、查询更快、占用空间更小
• 避免使用 ENUM，因为增加新类型需要修改表结构
• 避免使用 NULL 字段，因为很难进行查询优化、含 NULL 复合索引无效、NULL 列加索引需要额外空间，NULL 只能采用 IS NULL 或者 IS NOT NULL，而在 =/!=/in/not in 时有大坑
• 少用并拆分 TEXT/BLOB，处理性能远低于 VARCHAR，若必须使用则拆分单独的表（建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据）
• 不在数据库里存图片，借助文件系统存储图片，数据库中仅存图片存储地址（文件类同）
• 使用INT UNSIGNED存储IPv4，不要用 CHAR(15)
• 使用 VARCHAR(20) 存储手机号，不要使用整数（牵扯到国家代号，可能出现+/-/()等字符，例如+86；VARCHAR 可以模糊查询，例如like ‘138%’）
• 下划线两边的字母数大于等于2个，否则 Java 在生成 get 和 set 方法时不符合 Java 规范
• 所有的日期字段命名以‘_date’结尾。几个重要的日期字段，公告日期pub_date，变更日期chan_date，交易日期trade_date，上市日期list_date，退市日期delist_date
• 非日期字段不能以‘_date’结尾
• 所有的唯一编码字段命名以‘_key’结尾。如证券唯一编码sec_key，机构唯一编码org_key，日期唯一编码date_key、
• 非编码字段不能以‘_key’结尾

索引规范

• 自增列或全局ID做InnoDB主键，推荐用独立于业务的 AUTO_INCREMENT 列或全局ID生成器做代理主键
• 非唯一索引命名默认为 idx_字段名 来命名
• 唯一索引使用 uk_字段名 来命名
• 主键索引 pk_ 来命名
• 单张表索引数量建议控制在5个以内
• 合理添加索引可以改善查询，但会减慢更新，并不是索引越多越好，索引字段数最好不超过表字段的20%
• 字符字段必须建前缀索引， like '%name%' 不会使用索引
• 不在索引列做运算，否则无法使用索引，导致全表扫描
• 尽量不用外键，高并发时容易死锁（外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈）
• 非必要不要进行 JOIN 查询，如果要进行 JOIN 查询，被 JOIN 的字段必须类型相同，并建立索引
• 理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)
• 防止因字段类型不同造成的隐式转换，导致索引失效

SQL规范

• SQL语句尽可能简单，一条SQL只能在一个CPU运算，5000+QPS的高并发中，可能一条大SQL就把整个数据库堵死
• 拆解成多条简单SQL，缓存命中率更高，用上更多的CPU；减少锁表时间，特别是MyISAM
• 事务/连接使用原则：即开即用，用完即关
• 与事务无关操作放到事务外面，减少资源的占用
• 不破坏一致性前提下，使用多个短事务代替长事务
• 尽可能避免使用存储过程、触发器、Event（调试，排错，迁移都比较困难，扩展性较差），减少使用MySQL函数对结果进行处理，由程序端负责
• 尽量不用 SELECT *  只取需要的数据列，否则会占用更多的CPU、内存、IO、网络带宽资源
• 同一字段，将 OR 改写为 IN ，注意控制 IN 的个数，建议 N 小于200

select id from t where phone=’159′ or phone=’136′;

=>

select id from t where phone in (’159′, ’136′);

• 不同字段，将 OR 改为 UNION

select id from t where phone = ’159′ or name = ‘john’;

=>

select id from t where phone=’159′

union

select id from t where name=’jonh’

• 避免负向查询和%前缀模糊查询，使用不了索引，导致全表扫描
• 尽量不用或少用 COUNT(*)
• LIMIT 高效分页，传统分布 SELECT id FROM user LIMIT 10000, 10，偏移量越大则越慢；推荐分页 SELECT id FROM user WHERE id >= 10000 LIMIT 10
• 若无需对结果进行去重，使用 UNION ALL 而非 UNION， UNION 有去重开销
• 高并发DB禁止进行两个表以上的 JOIN

互联网公司为什么不推荐使用join?

1.不利于写操作。执行读操作时，会锁住被读的数据，阻塞其他业务对该部分数据的更新操作。如果涉及多个聚合函数，相当于同时锁住多张表，不能进行读写操作，影响系统整体性能。

2.不利于维护。业务发生变动时，如join中的一张表修改了，可能导致系统中原有的SQL不可用。

3.数据库计算资源相对于service层的水平扩展要难很多。

• 同数据类型的列值进行比较（防止因字段类型不同造成的隐式转换，导致索引失效）
• 避免大SQL、大事务、大批量长时间占用系统资源而堵塞系统，一个SQL只能在一个CPU运算
• 尽量不用 INSERT ... SELECT
• Load data 批量导入数据，尽量避开高峰期操作
• 使用 EXPLANIN 查看执行计划
• 观察慢查询日志
• show processlist 查看进程状态
• MySQL子查询大部分情况下优化较差，特别是 WHERE 中使用 IN id 的子查询，一般可用 JOIN 改写

通用约定

• 永远不在程序端显示加锁，外部锁对数据库不可控，高并发时是灾难
• 表存储引擎必须使用InnoDB
• 统一字符集为UTF8，乱码：SET NAMES UTF8，必要时候使用utf8mb4（utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它）
• 库表等名称统一用小写，MySQL库表大小写敏感，字段名的大小写不敏感（Linux VS win）
• 表名不使用复数名词
• 禁止使用关键字
• 必备三个字段 id, create_at, update_at
• 表命名最好加上“业务名称_表的作用”
• 库名与服务名最好一致

MySQL建表规范

1. 库名、表名、字段名必须使用小写字母，“_”分割。

2. 库名、表名、字段名不超过12个字符。

3. 库名、表名、字段名见名知意,建议使用名词而不是动词。

4. 建议使用InnoDB存储引擎

5. 存储精确浮点数使用DECIMAL替代FLOAT和DOUBLE。

6. 建议使用UNSIGNED存储非负数值。

7. 整形定义中不添加长度

8. 使用短数据类型，比如取值范围为0-80时，使用TINYINT UNSIGNED。

9. 不使用TEXT、BLOB类型。

10. VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N。N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存。

11. 表字符集选择UTF8。

12. 禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。

13. 存储时间---存疑

mysql 数据类型及占用字节数

TINYINT                                    1 字节

SMALLINT                                  2 个字节

MEDIUMINT                                 3 个字节

INT                                         4 个字节

INTEGER                                    4 个字节

BIGINT                                      8 个字节

FLOAT(X)                        4 如果 X < = 24 或 8 如果 25 < = X < = 53

FLOAT                                       4 个字节

DOUBLE                                    8 个字节

DOUBLE PRECISION                        8 个字节

REAL                                         8 个字节

DECIMAL(M,D)                          M字节(D+2 , 如果M < D)

NUMERIC(M,D)                          M字节(D+2 , 如果M < D)

日期和时间类型

列类型                                     需要的存储量

DATE                                        3 个字节

DATETIME                                 8 个字节

TIMESTAMP                               4 个字节

TIME                                         3 个字节

YEAR                                         1 字节

字符串类型

列类型                                                         需要的存储量 

CHAR(M)                                        M字节，1 <= M <= 255

VARCHAR(M)                                 L+1 字节, 在此L <= M和1 <= M <= 255

TINYBLOB, TINYTEXT                     L+1 字节, 在此L< 2 ^ 8

BLOB, TEXT                                   L+2 字节, 在此L< 2 ^ 16

MEDIUMBLOB, MEDIUMTEXT         L+3 字节, 在此L< 2 ^ 24

LONGBLOB, LONGTEXT                 L+4 字节, 在此L< 2 ^ 32

ENUM('value1','value2',...)                1 或 2 个字节, 取决于枚举值的数目(最大值65535）

SET('value1','value2',...)                    1，2，3，4或8个字节, 取决于集合成员的数量(最多64个成员）

机器执行SQL语句

From a_table on a_table.id = b_table.a_id left join b_table where a_table.id = 1 group by a_table.age select a_table.id order by a_table.age desc limit 100

索引是什么

索引(Index)是帮助MySQL高效获取数据的数据结构

索引种类

主键索引

单值索引：一个索引只包含单个列

唯一索引：索引列的值必须唯一，允许有空值

复合索引：即一个索引包含多个列

基本语法

1、创建索引：

1.PRIMARY  KEY（主键索引）
mysql>ALTER  TABLE  table_name  ADD  PRIMARY  KEY (  column  ) 
2.UNIQUE(唯一索引)
mysql>ALTER  TABLE  table_name  ADD  UNIQUE (column ) 
3.INDEX(普通索引)
mysql>ALTER  TABLE  table_name  ADD  INDEX index_name (  column  )
4.FULLTEXT(全文索引)
mysql>ALTER  TABLE  table_name  ADD  FULLTEXT ( column )
5.多列索引
mysql>ALTER  TABLE  table_name  ADD  INDEX index_name (  column1,  column2,  column3  )

2、删除索引：DROP INDEX [indexName] ON mytable;

3、查看索引：SHOW INDEX FROM table_name\G

索引结构

BTree索引、Hash索引、full-text全文索引、R-Tree索引

重点学习BTree索引

需要创建索引的情况

1.主键自动建立唯一索引

2.频繁作为查询的条件的字段应该创建索引

3.查询中与其他表关联的字段，外键关系建立索引

4.频繁更新的字段不适合创建索引

5.Where条件里用不到的字段不创建索引

6.在高并发下倾向创建组合索引

7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度

8.查询中统计或者分组字段

哪些情况不要创建索引

1.表记录太少

2.经常增删改的表

3.数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。

注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

Explain

使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈

1、id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

       id相同，执行顺序由上至下

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

       id相同不同，同时存在

2、select_type：查询的类型，主要用于区别普通查询、联合查询、子查询等的复杂查询

        1.SIMPLE：简单的select查询，查询中不包含子查询或者UNION

        2.PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为

3.SUBQUERY：在SELECT或者WHERE列表中包含了子查询

        4.DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。

        5.UNION：若第二个SELECT出现在UNION之后，则被标记为UNION;若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

        6.UNION RESULT：从UNION表获取结果的SELECT

3、table：显示这一行的数据是关于哪张表的

4、type：显示查询使用了何种类型从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

       1. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计

       2. const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键至于where列表中，MySQL就能将该查询转换为一个常量

       3. eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

       4. ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

       5. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束语另一点，不用扫描全部索引

       6. index：Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

       7. all：FullTable Scan,将遍历全表以找到匹配的行

至少达到range，能达到ref更好

5、possible_keys：显示可能应用在这张表中的索引,一个或多个。查询涉及的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

6、key：实际使用的索引

7、key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

8、ref：显示索引那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值

9、rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

10、Extra：其他信息

1. 1.Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作成为“文件排序”

2.Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by

3. USING index：表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表面索引用来读取数据而非执行查找动作。

4.Using where：表面使用了where过滤

5.using join buffer：使用了连接缓存

6.impossible where：where子句的值总是false，不能用来获取任何元组

7.select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者

对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，

查询执行计划生成的阶段即完成优化。

8.distinct：优化distinct，在找到第一匹配的元组后即停止找同样值的工作

9. using index condition：mysql5.6后新加的新特性,查找使用了索引，但是需要回表查询数据

避免索引失效方式

1、 如果建立复合索引，遵守索引顺序，不要随意跳过索引

2、 不在索引列上做任何操作如：计算、函数、（自动/手动）类型转换，会导致索引失效而转向全表扫描

3、 如果索引放在范围后面会失效，如：> <等

4、 尽量使用覆盖索引（索引列和查询列一致），减少select*

5、 6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

6、 7.is null,is not null 也无法使用索引

7、 like以通配符开头mysql索引失效会变成全表扫描操作，可以用like “aa%”这种方式

8、 字符串不加单引号索引失效

9、 少用or,用它连接时会索引失效

10、        order by使用索引去排序，否则会产生“文件内排序”，使用字符串索引去排序也会产生Using filesort

11、        少用group by，会产生Using temporary; Using filesort，可以使用Java的函数式编程去实现分组

慢查询日志

查看慢查询日志里面的SQL，进行优化

Show profiles

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量

步骤：

         执行要优化的SQL语句

         执行show profiles;

         show profile (type) for id;

type: 

  | ALL                --显示所有的开销信息 

  | BLOCK IO           --显示块IO相关开销 

  | CONTEXT SWITCHES   --上下文切换相关开销 

  | CPU                --显示CPU相关开销信息 

  | IPC                --显示发送和接收相关开销信息 

  | MEMORY             --显示内存相关开销信息 

  | PAGE FAULTS        --显示页面错误相关开销信息 

  | SOURCE             --显示和Source_function，Source_file，Source_line相关的开销信息 

  | SWAPS              --显示交换次数相关开销的信息  

以下情况必须优化：

3.1  converting HEAP to MyISAM ： 查询结果太大，内存都不够用了，往磁盘上搬了；

3.2   creating tmp table ：创建临时表，拷贝数据到临时表，然后再删除；

3.3   copying to tmp table on disk ：把内存中临时表复制到磁盘，危险！！！

3.5   locked

3.6 如果不分页，一次性查询数量太大，sending data 会非常耗时

索引规范

• 自增列或全局ID做InnoDB主键，推荐用独立于业务的 AUTO_INCREMENT 列或全局ID生成器做代理主键
• 非唯一索引命名默认为 idx_字段名 来命名
• 唯一索引使用 uk_字段名 来命名
• 主键索引 pk_ 来命名
• 单张表索引数量建议控制在5个以内
• 合理添加索引可以改善查询，但会减慢更新，并不是索引越多越好，索引字段数最好不超过表字段的20%
• 字符字段必须建前缀索引， like '%name%' 不会使用索引
• 不在索引列做运算，否则无法使用索引，导致全表扫描
• 尽量不用外键，高并发时容易死锁（外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈）
• 非必要不要进行 JOIN 查询，如果要进行 JOIN 查询，被 JOIN 的字段必须类型相同，并建立索引
• 理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)
• 防止因字段类型不同造成的隐式转换，导致索引失效
• 避免负向查询和%前缀模糊查询，使用不了索引，导致全表扫描

哪些字段上建立索引效果好？

• 索引列应该建立在数据区分度值大的列上，区分度越大，数据的不重复率就越大，新建索引效果就越好。

为什么要创建组合索引呢？

• 在MySQL中执行查询时，只能使用一个索引，如果我们在多列（last_name、first_name、age）上分别建索引，执行查询时，只能使用一个索引，MySQL会选择一个最严格(获得结果集记录数最少)的索引。
• 理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)
• 如果有一个组合索引(col_a,col_b,col_c)，下面的情况都会用到这个索引：

col_a = “value”;

col_a = “value” and col_b = “value”;

col_a = “value” and col_b = “value” and col_c = “value”;

col_b = “value” and col_a = “value” and col_c = “value”;

• 对于最后一条语句，MySQL会自动优化成第三条一样（ = 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会优化成索引可以识别的形式）。下面的情况就不会用到索引：

col_b = “value”;

col_b = “value” and col_c = “value”;

理解单列索引、多列索引以及最左前缀原则

示例：现在我们想查出满足以下条件的用户id：

mysql>SELECT ｀id｀ FROM user WHERE last_name｀=’L’ AND ｀first_name｀=’ZW’ AND ｀age｀= 30 ，因为我们不想扫描整表，故考虑用索引。

单列索引：

ALTER TABLE user ADD INDEX last_name (last_name);

将lname列建索引，这样就把范围限制在 last_name='L' 的结果集1上，之后扫描结果集1，产生满足 first_name='ZW' 的结果集2，再扫描结果集2，找到 age=30 的结果集3，即最终结果。

由于建立了 last_name 列的索引，与执行表的完全扫描相比，效率提高了很多，但我们要求扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除 last_name 列上的索引，再创建 first_name 或者age 列的索引，但是，不论在哪个列上创建索引搜索效率仍旧相似。

多列索引：

ALTER TABLE user ADD INDEX lname_fname_age (last_ame,first_name,age);

为了提高搜索效率，我们需要考虑运用多列索引，由于索引文件以B－Tree格式保存，所以我们不用扫描任何记录，即可得到最终结果。多列索引中其实还有一个字段的先后顺序问题，一般是将区分度较高的放在前面，这样联合索引才更有效。

最左前缀：

顾名思义，就是最左优先，上例中我们创建了 lname_fname_age 多列索引，相当于创建了(last_name)单列索引，(last_name,first_name)组合索引以及(last_name,first_name,age)组合索引。

注：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配

在什么情况下建立索引呢？

一般来说，在 WHERE 和 JOIN 中出现的列需要建立索引。

索引的不足之处

• 上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

使用索引的注意事项

• 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

• 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

• 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

• like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

• 不要在列上进行运算

select id from user where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select id from user where adddate<‘2018-01-01’;

• 不使用NOT IN、<>、BETWEEN操作