MySQL 开发规范

曾经终败给现在 2022-12-14 12:25 292阅读 0赞

## 1. 基础规范 ##

1.使用InnoDB 存储引擎

没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb）Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好

2.表字符集使用utf8mb4

默认使用 utf8mb4 字符集，数据库排序规则使用 utf8mb4\_general\_ci，采用 utf8 编码的 MySQL 无法保存占位是 4 个字节的 Emoji 表情。为了使后端的项目全面支持客户端输入的 Emoji 表情，升级编码为 utf8mb4 是最佳解决方案；

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效

3.所有表都需要添加注释

使用comment从句添加表和列的备注 从一开始就进行数据字典的维护

4.单表数据量建议控制在500万以内

500万并不是MySQL数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题，可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小

5.不在数据库中存储图、文件等大的二进制数据

通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时 通常存储于文件服务器，数据库只存储文件地址信息

6.尽量做到冷热数据分离，减小表的宽度

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO） 更有效的利用缓存，避免读入无用的冷数据 经常一起使用的列放到一个表中（避免更多的关联操作）

## 2.命名规范 ##

1.库名、表名、字段名必须使用小写字母，并采用下划线分割。

MySQL有配置参数lower\_case\_table\_names=1，即库表名以小写存储，大小写不敏感。如果是0，则库表名以实际情况存储，大小写敏感；如果是2，以实际情况存储，但以小写比较。

如果大小写混合使用，可能存在abc，Abc，ABC等多个表共存，容易导致混乱  
字段名显示区分大小写，但实际使用时不区分，即不可以建立两个名字一样但大小写不一样的字段。

为了统一规范，库名、表名、字段名使用小写字母，不允许"-"号

2.库名、表名、字段名禁止超过32个字符，需见名知意，建议使用名词不是动词

库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符

3.库名、表名、字段名禁止使用MySQL保留字

当库名、表名、字段名等属性含有保留字时，SQL语句必须用反引号引用属性名称，这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。

4.临时库、表名必须以`tmp_`为前缀，并以日期`_YYYYddmm`为后缀

形如：tmp\_user\_account\_20201013

5.备份库、表必须以`bak_`为前缀，并以日期`_YYYYddmm`为后缀

形如：bak\_user\_account\_20201013

6.主键索引名为`pk_`字段名；唯一索引名为`uk_`字段名；普通索引名则为 `idx_`字段名

*  `pk_` 即 `primary key`；
 *  `uk_` 即 `unique key`；
 *  `idx_` 即 `index` 的简称；

7.在不同的库或表中，要保证所有存储相同数据的列名和列类型必须一致

一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低.

8.表的命名最好是加上“业务名称\_表的作用”

正例：`user_task` / `force_project` / `trade_config`

## 3. 字段设计规范 ##

1.优先选择符合存储需要的最小的数据类型

列的字段类型越大，建立索引占据的空间就越大，导致一个页中的索引越少，造成IO次数增加，影响性能.

*  业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间
 *  能用int的就不用char或者varchar
 *  能用tinyint的就不用int
 *  使用 UNSIGNED 存储非负数值
 *  使用tinyint来代替 enum和boolean
 *  表中的自增列（auto\_increment属性），推荐使用bigint类型
 *  存储 ip 最好用 int存储而非 char(15)

> 通过MySQL函数inet\_ntoa和inet\_aton来进行转化。IPv6地址目前没有转化函数，需要使用DECIMAL或两个BIGINT来存储  
> SELECT INET\_ATON(‘209.207.224.40’); 3520061480  
> SELECT INET\_NTOA(3520061480); 209.207.224.40

2.不推荐使用blob，text等类型

blob，text是为了存储极大的字符串而设计的数据类型，采用二进制与字符串方式存储，该数据类型不能设置默认值、不便于排序、不便于建立索引， varchar 的性能会比 text 高很多，如果非要使用，建议将这种数据分离到单独的拓展表中

3.禁止使用字符串来存储日期型数据

*  无法使用日期函数计算比较
 *  字符串存储要占更多的内存空间，datetime(8字节)和timestamp(本身是以int存储，占4字节,范围:1970-01-01 00:00:01到2038-01-19 03:14:07)
 *  TIMESTAMP 记录经常变化的更新/创建/发布/日志时间等，并且是近来的时间，够用，可免时区处理
 *  DATETIME 记录生日、纪念事件、超出 TIMESTAMP 的时间，记得时区处理

4.用 `DECIMAL` 代替 `FLOAT` 和 `DOUBLE` 存储精确浮点数

`Decimal`类型为精准浮点数，`float` 和 `double` 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 `decimal` 的范围，建议将数据拆成整数和小数分开存储。

5.必须把字段定义为NOT NULL并设默认值

*  NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化
 *  NULL需要更多的存储空间
 *  NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑

6.使用`varchar(20)`存储手机号，不要使用整数

*  牵扯到国家代号，可能出现`+/-/()`等字符，例如+86
 *  手机号不会用来做数学运算
 *  `varchar`可以模糊查询，例如`like ‘138%’`

7.根据业务区分使用`char/varchar`

*  字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高
 *  字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间

8.禁止在数据库中存储明文密码，把密码加密后存储

9.禁止使用外键

建议在应用层实现外键的逻辑，外键与级联更新不适合高并发场景，降低插入性能，大并发下容易产生死锁。

10.整形定义中不添加长度，比如使用`INT`，而不是`INT[4]`

值类型括号后面的数字只是表示宽度而跟存储范围没有关系

11.核心表必须有行数据的创建时间和最后更新时间

核心表（如用户表，金钱相关的表）必须有行数据的创建时间字段create\_time和最后更新时间字段update\_time，便于查问题

## 4.索引设计规范 ##

索引其实就是一种数据结构，（哈希表、树等等）不同类型的索引有着不同的数据结构和功能。

MySQL的查询速度依赖良好的索引设计，因此索引对于高性能至关重要。合理的索引会加快查询速度，不合理的索引会降低速度

1.索引的作用

*  加速查询速度
 *  维护数据的约束性（完整性、一致性）  
    对于加速查询，使用索引不一定是最好的选择。小表就直接全表扫描，中到大表就建索引，超大表就分区分表。其实主要就要索引带来的好处和维护索引的成本之间的权衡。

2.单表的索引数建议不超过 5 个，单个索引中的字段数建议不超过 5 个  
太多就起不到过滤作用了，索引也占空间，管理起来也耗资源

3.对字符串使用前缀索引，前缀索引长度不超过8个字符  
不要索引blob/text等字段，不要索引大型字段，这样做会让索引占用太多的存储空间

前缀索引就是对文本的前几个字符建立索引，前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)

4.主键准则

*  表必须有主键
 *  不使用更新频繁的列
 *  尽量不选择字符串列
 *  不使用UUID MD5 HASH
 *  默认使用非空的唯一键
 *  建议选择自增或发号器

5.重要的SQL必须被索引，核心SQL优先考虑覆盖索索引  
UPDATE、DELETE 语句的 WHERE 条件列  
ORDER BY、GROUP BY、DISTINCT 的字段  
多表 JOIN 的字段  
覆盖索引可以避免Innodb表进行索引的二次查询，把随机IO变成顺序IO，加快查询效率

6.区分度最大的字段放在前面  
选择筛选性更优的字段放在最前面，比如单号、userid等，type，status等筛选性一般不建议放在最前面  
索引根据左前缀原则，当建立一个联合索引(a,b,c)，则查询条件里面只有包含(a)或(a,b)或(a,b,c)的时候才能走索引，(a,c)作为条件的时候只能使用到a列索引，所以这个时候要确定a的返回列一定不能太多，不然语句设计就不合理，(b,c)则不能走索引

7.业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引  
不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明  
显的； 另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生

8.InnoDB 和 MyISAM 存储引擎表，索引类型必须为BTREE  
MEMORY表可以根据需要选择 HASH 或者 BTREE 类型索引

9.MySQL 中索引的限制

*  MYISAM 存储引擎索引长度的总和不能超过 1000 字节
 *  BLOB 和 TEXT 类型的列只能创建前缀索引
 *  使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引
 *  过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引
 *  join语句中join条件字段类型不一致的时候MYSQL无法使用索引
 *  使用 LIKE 操作的时候如果条件以通配符开始 (如 ‘%abc…’)时, MYSQL无法使用索引。
 *  使用非等值查询的时候, MYSQL 无法使用 Hash 索引

## 5.SQL查询规范 ##

1.按需索取，禁止使用 `select *`

*  无法索引覆盖，回表操作，增加 io
 *  额外的内存负担，大量冷数据灌入innodb\_buffer\_pool\_size，降低查询命中率
 *  额外的网络传输开销

2.能确定返回结果只有一条时，使用 `limit 1`

在保证数据不会有误的前提下，能确定结果集数量时，多使用limit，尽快的返回结果。

3.涉及到复杂sql时，务必先参考已有索引设计，先`explain`查看执行计划

*  简单SQL拆分，不以代码处理复杂为由
 *  比如 OR 条件： `f_phone=’10000’ or f_mobile=’10000’`，两个字段各自有索引，但只能用到其中一个。可以拆分成2个sql，或者`union all`  
    先`explain`的好处是可以为了利用索引，增加更多查询限制条件

4.对应同一列进行 `or` 判断时，使用 `in` 代替 `or`

in 的值不要超过 500 个， in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引

5.禁止使用 `order by rand()` 进行随机排序

*  会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源
 *  推荐在程序中获取一个随机值，然后从数据库中获取数据的方式

6.`WHERE`从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引

不推荐：
    where date(create_time)='20190101'
    推荐：
    where create_time >= '20190101' and create_time < '20190102'

7.不要使用 `count(列名)`或 `count(常量)`来替代 `count(*)`

*  `count(*)`是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 `NULL` 和`非NULL` 无关
 *  `count(*)`会统计值为`NULL` 的行，而 `count(列名)`不会统计此列为 `NULL` 值的行

8.不得使用外键与级联，一切外键概念必须在应用层解决

以学生和成绩的关系为例，学生表中的 `student_id`是主键，那么成绩表中的 `student_id`则为外键。如果更新学生表中的 `student_id`，同时触发成绩表中的 `student_id` 更新， 即为级联更新。

外键与级联更新适用于单机低并发，不适合分布式、高并发集群；

级联更新是强阻塞，存在数据库更新风暴的风险； 外键影响数据库的插入速度

9.`in` 操作能避免则避免

若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内

10.超过三个表禁止 `join`

需要 `join` 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引；即使双表 `join` 也要注意表索引、 `SQL` 性能

11.`SELECT`语句不要使用`UNION`，推荐使用`UNION ALL`

`UNION`子句个数限制在5个以内。因为`union all`不需要去重，节省数据库资源，提高性能

12.建议使用合理的分页方式以提高分页效率

不推荐 SELECT * FROM table ORDER BY TIME DESC LIMIT 10000，10;
    原因：会导致大量的io，因为MySQL使用的是提前读取策略
    推荐：SELECT * FROM table WHERE TIME < last_TIME ORDER BY TIME DESC LIMIT 10.
    SELECT * FROM table inner JOIN (SELECT id FROM table ORDER BY TIME LIMIT 10000，10) as t USING(id)

MySQL分页查询的性能优化 — 详细说明

13、减少与数据库交互次数，尽量采用批量SQL语句

## 附录 ##

MySQL数据类型

MySQL支持多种类型，大致可以分为三类：数值、**日期/时间** 和 \*\*字符串(字符)\*\*类型。

## 数值类型 ##

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(`INTEGER`、`SMALLINT`、`DECIMAL`和`NUMERIC`)，以及近似数值数据类型(`FLOAT`、`REAL`和`DOUBLE PRECISION`)。

关键字`INT`是`INTEGER`的同义词，关键字`DEC`是`DECIMAL`的同义词。

`BIT`数据类型保存位字段值，并且支持`MyISAM`、`MEMORY`、`InnoDB`和`BDB`表。

作为`SQL`标准的扩展，`MySQL`也支持整数类型`TINYINT`、`MEDIUMINT`和`BIGINT`。下面的表显示了需要的每个整数类型的存储和范围。

<table> 
 <thead> 
  <tr> 
   <th align="left">类型</th> 
   <th align="left">大小</th> 
   <th align="left">范围（有符号）</th> 
   <th align="left">范围（无符号）</th> 
   <th align="left">用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">TINYINT</td> 
   <td align="left">1 字节</td> 
   <td align="left">(-128，127)</td> 
   <td align="left">(0，255)</td> 
   <td align="left">小整数值</td> 
  </tr> 
  <tr> 
   <td align="left">SMALLINT</td> 
   <td align="left">2 字节</td> 
   <td align="left">(-32 768，32 767)</td> 
   <td align="left">(0，65 535)</td> 
   <td align="left">大整数值</td> 
  </tr> 
  <tr> 
   <td align="left">MEDIUMINT</td> 
   <td align="left">3 字节</td> 
   <td align="left">(-8 388 608，8 388 607)</td> 
   <td align="left">(0，16 777 215)</td> 
   <td align="left">大整数值</td> 
  </tr> 
  <tr> 
   <td align="left">INT或INTEGER</td> 
   <td align="left">4 字节</td> 
   <td align="left">(-2 147 483 648，2 147 483 647)</td> 
   <td align="left">(0，4 294 967 295)</td> 
   <td align="left">大整数值</td> 
  </tr> 
  <tr> 
   <td align="left">BIGINT</td> 
   <td align="left">8 字节</td> 
   <td align="left">(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)</td> 
   <td align="left">(0，18 446 744 073 709 551 615)</td> 
   <td align="left">极大整数值</td> 
  </tr> 
  <tr> 
   <td align="left">FLOAT</td> 
   <td align="left">4 字节</td> 
   <td align="left">(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)</td> 
   <td align="left">0，(1.175 494 351 E-38，3.402 823 466 E+38)</td> 
   <td align="left">单精度 浮点数值</td> 
  </tr> 
  <tr> 
   <td align="left">DOUBLE</td> 
   <td align="left">8 字节</td> 
   <td align="left">(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td> 
   <td align="left">0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td> 
   <td align="left">双精度 浮点数值</td> 
  </tr> 
  <tr> 
   <td align="left">DECIMAL</td> 
   <td align="left">DECIMAL(M,D) ，如果M&gt;D，为M+2否则为D+2</td> 
   <td align="left">依赖于M和D的值</td> 
   <td align="left">依赖于M和D的值</td> 
   <td align="left">小数值</td> 
  </tr> 
 </tbody> 
</table>

## 日期和时间类型 ##

表示时间值的日期和时间类型为`DATETIME`、`DATE`、`TIMESTAMP`、`TIME`和`YEAR`。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

`TIMESTAMP`类型有专有的自动更新特性，将在后面描述。

<table> 
 <thead> 
  <tr> 
   <th align="left">类型</th> 
   <th align="left">大小 (字节)</th> 
   <th align="left">范围</th> 
   <th align="left">格式</th> 
   <th align="left">用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">DATE</td> 
   <td align="left">3</td> 
   <td align="left">1000-01-01/9999-12-31</td> 
   <td align="left">YYYY-MM-DD</td> 
   <td align="left">日期值</td> 
  </tr> 
  <tr> 
   <td align="left">TIME</td> 
   <td align="left">3</td> 
   <td align="left">‘-838:59:59’/‘838:59:59’</td> 
   <td align="left">HH:MM:SS</td> 
   <td align="left">时间值或持续时间</td> 
  </tr> 
  <tr> 
   <td align="left">YEAR</td> 
   <td align="left">1</td> 
   <td align="left">1901/2155</td> 
   <td align="left">YYYY</td> 
   <td align="left">年份值</td> 
  </tr> 
  <tr> 
   <td align="left">DATETIME</td> 
   <td align="left">8</td> 
   <td align="left">1000-01-01 00:00:00/9999-12-31 23:59:59</td> 
   <td align="left">YYYY-MM-DD HH:MM:SS</td> 
   <td align="left">混合日期和时间值</td> 
  </tr> 
  <tr> 
   <td align="left">TIMESTAMP</td> 
   <td align="left">4</td> 
   <td align="left">1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07</td> 
   <td align="left">YYYYMMDD HHMMSS</td> 
   <td align="left">混合日期和时间值，时间戳</td> 
  </tr> 
 </tbody> 
</table>

## 字符串类型 ##

字符串类型指`CHAR`、`VARCHAR`、`BINARY`、`VARBINARY`、`BLOB`、`TEXT`、`ENUM`和`SET`

<table> 
 <thead> 
  <tr> 
   <th align="left">类型</th> 
   <th align="left">大小</th> 
   <th align="left">用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">CHAR</td> 
   <td align="left">0-255字节</td> 
   <td align="left">定长字符串</td> 
  </tr> 
  <tr> 
   <td align="left">VARCHAR</td> 
   <td align="left">0-65535 字节</td> 
   <td align="left">变长字符串</td> 
  </tr> 
  <tr> 
   <td align="left">TINYBLOB</td> 
   <td align="left">0-255字节</td> 
   <td align="left">不超过 255 个字符的二进制字符串</td> 
  </tr> 
  <tr> 
   <td align="left">TINYTEXT</td> 
   <td align="left">0-255字节</td> 
   <td align="left">短文本字符串</td> 
  </tr> 
  <tr> 
   <td align="left">BLOB</td> 
   <td align="left">0-65 535字节</td> 
   <td align="left">二进制形式的长文本数据</td> 
  </tr> 
  <tr> 
   <td align="left">TEXT</td> 
   <td align="left">0-65 535字节</td> 
   <td align="left">长文本数据</td> 
  </tr> 
  <tr> 
   <td align="left">MEDIUMBLOB</td> 
   <td align="left">0-16 777 215字节</td> 
   <td align="left">二进制形式的中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td align="left">MEDIUMTEXT</td> 
   <td align="left">0-16 777 215字节</td> 
   <td align="left">中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td align="left">LONGBLOB</td> 
   <td align="left">0-4 294 967 295字节</td> 
   <td align="left">二进制形式的极大文本数据</td> 
  </tr> 
  <tr> 
   <td align="left">LONGTEXT</td> 
   <td align="left">0-4 294 967 295字节</td> 
   <td align="left">极大文本数据</td> 
  </tr> 
 </tbody> 
</table>

## MySQL之`varchar`的设计 ##

### 存储规则 ###

*  4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）
 *  5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个，最大大小是65532字节

### 限制规则 ###

1）存储限制

varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度，小于255为1个字节，大于255则要2个字节

2）编码长度限制

*  字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766
 *  字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845
 *  字符类型若为utf8mb4，每个字符最多占4个字节，最大长度不能超过16383

3）表长度限制

*  mysql的一个表总共字段长度不超过65535
 *  varchar(10)和varchar(100)的区别
 *  保存"hello"时占用的空间是一样的，但是更小的varchar(10)有着更好的性能

比如：MySQL建立索引时如果没有限制索引的大小，索引长度会默认采用的该字段的长度，也就是varchar(100)建立的索引存储大小要比varchar(10)建立索引存储大小大的多，加载索引使用的内存也更多

### 位、字节、字符的区别 ###

*  位（bit）：计算机存储信息的最小单位，二进制的一个“0”或一个“1”叫一位
 *  字节（byte）：是计算机中数据处理的基本单位，习惯上用大写 B 来表示，1B（byte,字节）= 8bit（位）
 *  字符：是指计算机中使用的字母、数字、字和符号，不同的编码里，一个字符对应几个字节是不同的

### ASCIIS码： ###

*  1个英文字母（不分大小写）= 1个字节的空间
 *  1个中文汉字 = 2个字节的空间
 *  1个ASCII码 = 一个字节

### UTF-8编码： ###

*  1个英文字符 = 1个字节
 *  英文标点 = 1个字节
 *  1个中文（含繁体） = 3个字节
 *  中文标点 = 3个字节

### utf8mb4 ###

*  中文 = 3个字节
 *  emoji表情符号 = 4个字节

## 索引种类 ##

根据数据库的功能，可以分以下索引类型：

### 1.唯一索引 ###

唯一索引是不允许其中任何两行具有相同索引值的索引，加速查询 + 列值唯一（可以有null）

ALTER TABLE `table_name` ADD UNIQUE uk_xxx (`column` )

### 2.主键索引 ###

加速查询 + 列值唯一（不可以有null）+ 表中只有一个  
在数据库中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型，该索引要求主键中的每个值都唯一

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)

### 3.聚集索引 ###

查询列要被所建的索引覆盖；在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同，一个表只能包含一个聚集索引，主键就是一个聚集索引

### 4.组合索引 ###

多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

ALTER TABLE `table_name` ADD INDEX idx_xxx (`column1`, `column2`, `column3`)

### 5.普通索引 ###

ALTER TABLE `table_name` ADD INDEX idx_xxx (`column`)

### 6.全文索引 ###

ALTER TABLE `table_name` ADD FULLTEXT (`column`)

### 7.覆盖索引 ###

InnoDB存储引擎中，`secondary index`（非主键索引）中没有直接存储行地址，存储主键值。如果用户需要查询`secondary index`中所不包含的数据列时，需要先通过`secondary index`查找到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。覆盖索引的概念就是查询可以通过在一个索引中完成，覆盖索引效率会比较高，主键查询是天然的覆盖索引。合理的创建索引以及合理的使用查询语句，当使用到覆盖索引时可以获得性能提升。比如`SELECT email,uid FROM user_email WHERE uid=xx`，如果uid不是主键，适当时候可以将索引添加为index(uid,email)，以获得性能提升.

## MySQL执行顺序 ##

SELECT
       DISTINCT <select_list>
       FROM <left_table>
       <join_type> JOIN <right_table>
       ON <join_condition>
       WHERE <where_condition>
       GROUP BY <group_by_list>
       HAVING <having_condition>
       ORDER BY <order_by_condition>
       LIMIT <limit_number>

示例：较为规范的建表语句

CREATE TABLE `user` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(11) NOT NULL COMMENT '用户id',
      `username` varchar(45) NOT NULL COMMENT '真实姓名',
      `email` varchar(30) NOT NULL COMMENT '用户邮箱',
      `nickname` varchar(45) NOT NULL COMMENT '昵称',
      `avatar` int(11) NOT NULL COMMENT '头像',
      `birthday` date NOT NULL COMMENT '生日',
      `sex` tinyint(4) DEFAULT '0' COMMENT '性别',
      `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
      `user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址',
      `user_register_ip` int(11) NOT NULL COMMENT '用户注册时的源ip',
      `create_time` timestamp NOT NULL COMMENT '用户记录创建的时间',
      `update_time` timestamp NOT NULL COMMENT '用户资料修改的时间',
      `user_review_status` tinyint(4) NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未通过，4为还未提交审核',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uk_user_id` (`user_id`),
      KEY `idx_username` (`username`),
      KEY `idx_create_time` (`create_time`,`user_review_status`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='网站用户基本信息'