MySQL学习笔记
数据库编码和排序类型选择
数据库编码
MySQL中的数据库编码 utf8 并不是真正的标准字符集 UTF-8 ， utf8mb4 才是，使用 utf8mb4 可以存储个别复杂汉字和表情符号，可以存储个别复杂汉字和表情符号，所以含有备注、富文本等类似字段的表格推荐使用 utf8mb4 编码避免入库字符串包含不支持编码的字符时报错的问题
数据库链接中指定字符集为标准字符集，可以放心食用
数据库排序类型
utf8mb4_general_ci 查询速度快，但准确度稍差
utf8mb4_unicode_ci 准确度高，但查询速度稍慢
一般来说， utf8mb4_general_ci 的准确度能够满足需求，推荐使用
常见数据类型的选择
基本原则
- 更小的通常更好
- 简单就好
- 尽量避免
NULL
整数类型
整数类型有 TINYINT 、 SMALLINT 、 MEDIUMINT 、 INT 、 BIGINT ，分别使用 8 、 16 、 24 、 32 、 64 位存储空间，可以存储的值的范围从 -2^(n-1) 到 2^(n-1)-1 ，其中 n 是存储空间的位数
整数类型可选择为 UNSIGNED 类型，表示不允许负值，可以使正整数的上限提高近一倍，例如 TINYINT UNSIGNED 存储的值的范围是 0 ~ 255 ，而 TINYINT 存储的范围是 -128 ~ 127
无符号和有符号的计算性能相同，所以根据需要选择合适的类型即可，但是要遵循 “更小的通常更好” 的原则，避免浪费空间
除此之外，我们还可以指定整型类型的 显示宽度 ，例如 INT(1) ，其实这样做是没有什么意义的，因为它不会去限制值的存储空间位数（你是不是这样理解的），比如 INT(1) 的意思是使用1位存储空间，这是错误的理解，它只是规定了MySQL的一些管理客户端工具（如命令行客户端）显示查询结果时整数的显示宽度，**对于存储和计算来说， INT(1) 和 INT(20)
是一样的，都占用4个字节的存储空间（大坑）**
实数类型
实数是带有小数部分的数字，但是它们并不是只为了存储小数，比如也可以使用 DECIMAL 存储比 BIGINT 还大的整数，MySQL既支持精确类型也支持不精确类型
FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算， DECIMAL 用于存储精确的小数，支持精确计算
它们都可以指定精度， FLOAT 和 DOUBLE 的精度比较复杂（因为存在精度缺失问题），这里对 DECIMAL 的存储方式以及精度进行说明
经过测试（具体浮点数的存储细节没做考虑，只是在数据库中经过测试得到的结果），当指定
FLOAT类型精度为M + D > 6时会出现精度缺失问题，当指定DOUBLE类型M + D > 16时会出现精度缺失问题，所以使用FLOAT存储数据时，数据的整数位数和小数位数之和不要超过(<=)6；使用DOUBLE存储数据时，和不要超过16就可以避免存储时精度缺失问题（但是还是不能进行精确运算）
对于 DECIMAL ，也可以指定小数点前后所允许的最大位数（十进制），但是存储方式有所不同，MySQL使用二进制字符串表示小数点前后的十进制值，所以指定的位数（十进制）与需要的存储空间大小（字节Byte）关系如下表：
| 指定的位数 | 需要存储空间（字节Byte） | 备注 |
|---|---|---|
0 |
0 |
DECIMAL 默认精度为 (10, 0) |
1(9) |
1(2^8 - 1 = 255 > 9) |
存储1位十进制值 （0-9） 最少需要4位二进制 （2^4 - 1 = 15 > 9） ，而最小的存储空间单位为字节（1字节=8位），所以1位十进制需要存储空间为 1Byte |
2(99) |
1(2^8 - 1 = 255 > 99) |
存储2位十进制值 （0-99） 最少需要7位二进制 （2^7 - 1 = 127 > 99） ，而最小的存储空间单位为字节（1字节=8位），所以2位十进制需要存储空间也为 1Byte |
3(999) |
2(2^16-1 = 65535 > 999) |
存储3位十进制值 （0-999） 最少需要10位二进制 （2^10 - 1 = 1023 > 999） ，而最小的存储空间单位为字节（1字节=8位），所以3位十进制需要存储空间为 2Byte |
4 |
2 |
类推 |
5 |
3 |
类推 |
6 |
3 |
类推 |
| ... | ... | ... |
小数点左右需要存储空间之和加上1（小数点本身需要一个字节存储空间）就是指定精度下 DECIMAL 所占的存储空间大小
因为需要额外的存储空间和计算开销，所以只有当需要对小数部分进行精确计算时才使用 DECIMAL ，比如财务数据或科学计算数据，但在数据量较大的时候可以考虑使用 BIGINT 代替 DECIMAL ，将需要存储的货币单位根据小数的位数乘以相应的倍数转换为整数即可，这样可以同时避免浮点数计算不精确和 DECIMAL 精确计算代价高的问题
字符串类型
CHAR 和 VARCHAR
VARCHAR 用来存储可变长字符串，它比定长更节省空间，因为它仅仅使用必要的空间（例如越短的字符串需要越少的空间），但是 VARCHAR 需要额外的空间存储字符串的长度，如果列的最大长度小于等于 255 个字节，则只需1个字节存储长度，否则使用2个字节；另外，在执行 UPDATE 操作时可能会使行变得比原来长，这就导致需要做额外的工作，从而影响性能
CHAR 类型是定长的，MySQL总是会根据定义的字符串长度分配足够的空间，所以 CHAR 很适合存储定长的字符串（越短越好，或者所有值都接近同一个长度），例如存储MD5加密的密码；对于经常变更的数据， CHAR 的性能也比 VARCHAR 更好，因为定长的 CHAR 不容易产生碎片。另外对于非常短的列， CHAR 比 VARCHAR 在存储空间上也更有效率。例如用 CHAR(1) 来存储只有 Y 和 N 的值，只需一个字节，但是 VARCHAR(1) 实际需要两个字节，因为还需要一个字节存储长度
VARCHAR不会删除字符串的末尾空格，CHAR会
早期版本的MySQL（5.0.3之前）中
VARCHAR列最大长度限制为255字节，VARCHAR(20)中的20表示字节数，如果存放UTF-8编码的汉字的话只能放最多6个，每个汉字占用3个字节
新版本的MySQL（5.0.3及之后）列最大长度限制为65535字节，VARCHAR(20)中的20表示字符数，不管什么编码，也就是说汉字也能放20个，但最多占65532字节的字符数（实际行存储从第二个字节开始（-1），头部的2个字节表示长度（-2））
BOLB 和 TEXT 类型
BOlB 和 TEXT 都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储
BOLB 和 TEXT 之间仅有的不同是 BOLB 类型存储的是二进制数据，没有排序规则或字符集，而 TEXT 类型有字符集合排序规则
因为Memory引擎不支持 BOLB 和 TEXT 类型，所以如果查询中使用了 BOLB 或 TEXT 列并且需要使用隐式临时表会导致严重的性能开销，最好的办法就是尽量避免使用 BOLB 和 TEXT 类型，尽量避免查询条件中出现 BOLB 和 TEXT 列
日期时间类型
DATETIME 能够用来保存大范围的值，从1001年到9999年，精度为秒，它把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中，与时区无关，使用8个字节的存储空间
TIMESTAMP 类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数（MySQL中只精确到秒），只是用4个字节的存储空间，因此它的范围比 DATETIME 小得多，只能表示从 1970 年到 2038 年的时间
除此之外， TIMESTAMP 提供的值和时区有关系，而 DATETIME 则保留文本表示的日期和时间
所以，根据两者的特性， TIMESTAMP 通常用于跟踪记录的更改，并且通常在每次记录更改时进行更新。因为它比 DATETIME 空间效率更高（就2038年之前而言，不过到那时候肯定会给出新的方式，比如扩大存储空间）
CREATE TABLE `test` (
`name` varchar(255) DEFAULT '' COMMENT '内容',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DEFAULT CURRENT_TIMESTAMP表示当插入数据的时候，该字段默认值为当前时间；ON UPDATE CURRENT_TIMESTAMP表示每次更新这条数据的时候，该字段都会更新成当前时间
如果要存储特定值，则应使用 DATETIME 字段
如果在这两者之间犹豫不决，那就优先使用时间戳
位数据类型
对于位数据类型，不管存储格式和处理方式如何，从技术上来说都是字符串类型
位数据类型比较不常用，所以主要讲一下 BIT ，可以使用 BIT 存储一个或多个 true/false 值， BIT(1) 定义一个包含单个位的字段， BIT(2) 存储2个位，以此类推。 BIT 列的最大长度为 64 位
之前也有说过，MySQL中最小的数据大小是1个字节，清楚了这个再想想使用 BIT 真的有用吗？
答案是不一定，如果说某个数据库引擎确定 BIT 类型的基本单位就是位（比如MyISAM），那么在存储的时候必定需要通过将多个 BIT 类型的列进行打包存储，使用或修改时再根据位置映射关系进行修改，这样做虽然提高了空间利用率，但又增加了操作复杂度，真正的效率也不一定比使用 TINYINT 高，况且对于主流的Memory和InnoDB引擎来说，它们是为每个 BIT 列使用一个足够存储的最小整数类型来存放，并没有节省存储空间
所以我们应该谨慎使用 BIT 类型，对于大部分应用应该避免使用这种类型
标识符类型的选择
标识符是指类似表的主键、外键、索引以及查询条件列等这种对数据元（表中的一行数据）具有标记作用的字段名
给标识列选择合适的数据类型非常重要，遵循的原则一般如下：
- 整数通常是标识列最好的选择
- 尽量避免字符串类型
特殊类型数据
实际应用场景中，某些类型的数据并不是直接与内置类型一致，比如存储货币时使用整数就是一个例子
另一个例子是存储 IPv4 地址，正常情况下我们会使用 CHAR(15) 来存储，然而， IP 地址实际上是 32 位无符号整数，用点分十进制的表示方法只是为了让人们容易阅读，所以应该使用无符号整数存储 IP 地址，并且MySQL提供 INET_ATON() 和 INET_NTOA() 函数在这两种表示方法之间转换
多表查询
首先，我们得分清楚连接 JOIN 和联合 UNION ，我阅读的大部分文章中将这两者的中文名互相混用，以至于我在写笔记的时候都搞不清楚该用哪个中文翻译，所以在进行下面的阅读前我觉得有必要说明一下，英文 JOIN 的意思是加入、连接，所以 JOIN 叫做【连接查询】或【关联查询】，英文 UNION 的意思是联盟、联合，所以使用关键字 UNION 的叫做【联合查询】，除此之外，Sql语句有嵌套的情况叫做【嵌套查询】
连接查询
内连接
内连接 INNER JOIN 简单理解就是对两个表求某种条件下的交集
需要解释的地方是ON 关键字后的 JOIN 条件和 WHERE 关键字之后的筛选条件的执行计划是一样的，并且 WHERE 后的条件在实际执行中也会被转化为 ON
的写法，也就是最终都是在关联的时候根据条件对数据进行过滤，而不是在关联后所有结果集都出来了再通过 WHERE 来筛选
个人推荐在编写 INNER JOIN 类型的查询Sql时，必须的字段可以写到 ON 条件之后，非必须的字段可以放到 WHERE 关键字之后使用动态Sql生成，这样逻辑上更加清晰一点
MySql中只使用关键字
JOIN的默认行为是内连接INNER JOIN
左连接
左连接 LEFT JOIN 和 INNER JOIN 不同， LEFT JOIN 准确来说应该是左外连接（ LEFT OUTER JOIN ），连接时即使右表中没有与左表对应的记录时也会保留左表的记录
需要注意的是 LEFT JOIN 中的 ON 和 WHERE 条件的执行计划就不同了， ON 条件会在关联的时候执行，而 WHERE 条件会在关联后所有结果集都出来的情况下执行
联合查询
联合查询 UNION 是用来合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行，注意与连接查询进行区分
嵌套查询
顾名思义
分步查询
分步查询严格来说并不是MySQL中的一种查询方式，而是一种查询策略，任何多表查询（包括连接和联合以及嵌套查询）都可以分解成多个单表查询分步执行
分步查询有两种实现方式，一种是直接使用代码控制，另一种是使用 ORM 框架实现（如Mybatis）
使用代码控制
使用代码控制的实现方式优点是比较方便，可以对查询出来的数据直接进行处理后封装到 VO 中返回，减少了POJO对象，但是业务代码就会增多，后期维护的工作量也随之增加
使用ORM框架实现（推荐）
使用ORM框架实现的优点自然就是业务代码简洁明了，并且分解后的单表查询也可以复用，但是POJO对象会增多，resultMap的配置也会增多，不过这样又会使数据库字段与POJO类解耦，方便后期维护，比较推荐这种方式
查询性能优化
本部分的所有结论只适用于一般情况、多数情况，也就是说在某些具体的特殊场景下可能需要做出变通，但是也只有在遇到特殊场景时才需要考虑，如果在项目初始阶段就对所有情况进行考虑处理太依赖于“经验”
独立的查询条件列
查询条件中的列应该是独立的，这是指需要使用到索引的列不能是表达式的一部分，也不能是函数的参数，如下面两个查询都不能使用索引：
SELECT * FROM t1 WHERE id + 1 = 2
SELECT * FROM t1 WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 2
分页性能优化
即使有索引，如果数据量非常大，那么页码靠后的分页查询性能会非常差，例如下面的sql：
SELECT * FROM user WHERE sex='男' ORDER BY birthday LIMIT 100000, 10
对于这种数量级的分页查询，无论如何创建索引都是个严重的问题，为什么这么说，我们来看看LIMIT的执行原理：
explain SELECT * FROM user WHERE sex='男' ORDER BY birthday LIMIT 100000, 10
得到
***************** 1. row **************
id: 1
select_type: SIMPLE
table: message
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 100010
Extra:
1 row in set (0.00 sec)
注意 rows = 100010 ，也就是说，对于 LIMIT m, n 条件，MySQL会扫描满足条件的 m + n 条数据然后丢弃前 m 条，问题就在这里，如果是 LIMIT 100000, 10 ，则需要扫描 100010 行数据然后丢弃 100000 行，性能不必多说
优化这类索引的一个比较好的策略是使用延迟关联，通过使用索引覆盖先查询得到需要的数据元的主键，再根据这些主键关联原表获得需要的其他数据：
SELECT * FROM user INNER JOIN (
SELECT id FROM user
WHERE sex='男' ORDER BY birthday LIMIT 100000, 10
) AS x USING(id)
那么延迟关联是如何提高性能的？
首先，优化后的关联查询中的子查询只查询 id ，如果这个表的列非常多，那么只查询 id 相比查询 * 时的数据量已经减少了许多
其次，子查询使用了覆盖索引，所谓覆盖索引就是需要查询的列与索引列完全匹配，索引本身就存储了需要查询的列数据，避免了回表操作，相当于避免了m次（偏移量）的简单主键查询操作，当偏移量非常大时的性能提升可见一斑
回表是指当查询的列和索引列不完全匹配时，需要根据索引中的主键（数据指针）回到数据表中查询不匹配列数据的操作
最后，根据已经筛选好的主键关联原表即可获得需要的行（此时数据量已经减少为页大小级别）
关于JOIN
阿里巴巴开发手册中规定三张表以上不能使用 JOIN（即使两张表也要慎重考虑），做出这样的规定是由阿里巴巴海量的数据规模所决定的，但对于一般公司来讲可能完全达不到那样的数据规模，这样的限制对于较小规模的应用来说显得不是那么必要，但是我个人认为还是有必要的
关于查询分解
设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询，在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情，但是这样的想法对于MySql并不适用，MySql从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效，现代的网络速度比以前要快很多，无论是带宽还是延迟，在某些版本的MySql上，即使在一个通用服务器上，也能够运行每秒超过10万的查询，即使是一个千兆网卡也能轻松满足每秒超过2000次的查询，所以运行多个小查询现在已经不是大问题了（摘自《高性能MySQL（第3版）》）
总结
-
需要使用到索引的列不能是表达式的一部分，也不能是函数的参数
-
对于偏移量较大的分页查询如果有必要使用延迟关联进行优化
-
尽量避免三张表及以上的
JOIN，可以考虑对关联查询进行分解后分步查询或使用子查询 -
尽量避免多重（>=3）嵌套查询（并不是所有的嵌套查询性能都很差，需要测试）
此外，查询的性能和索引的关系非常密切，设计一个好的索引能够让查询效率提升几个数量级
本节内容是针对一般情况而言的，给出的优化方式也都是建议，真实业务场景下的查询往往需要进行测试才能验证哪种方式更好，并且某些关联查询是不能进行分解的
如果想要更加深入了解MySQL可以阅读《高性能MySQL（第3版）》
高性能索引策略
来自《高性能MySQL（第3版）》，主要针对一般情况下的索引优化策略进行了讲解，一些特殊情况并未进行详细说明，如果有需要建议去阅读原文
索引的类型
B+Tree索引
在一个多列的B+Tree索引中，B+Tree对索引列是顺序组织存储的，索引列的顺序意味着索引首先按照最左列进行排序（这是由索引的数据结构所确定的），其次是第二列，以此类推。所以，索引可以按照升序或者降序进行扫描以满足精确符合列顺序的 ORDER BY 、 GROUP BY 、和 DSITINCT 等子句的查询需求
假如有如下数据表：
CREATE TABLE people (
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
dob DATE NOT NULL,
gender ENUM('m', 'f') NOT NULL,
key(last_name, first_name, dob)
)
注：last_name 代表姓、first_name 代表名字
对于表中的每一行数据，索引中包含了 last_name 、 first_name 和 dob 的值，下图显示了索引是如果组织数据的存储的：
索引对多个列进行排序的依据是定义索引时列的顺序
索引就像一本书的目录，我们在使用目录的过程中需要遵循一定的查找顺序（你得先查章目录，再去查节目录），一样的道理，在使用索引时自然也得遵循一定的规则，否则索引就会失效
B+Tree索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找，举例如下：
| 匹配类型 | 解释 | 举例（以上图为例） |
|---|---|---|
| 全值匹配 | 查询条件和索引中的所有列匹配 | 查找姓名为Cuba Allen、出生于1960-01-01的人 |
| 匹配最左前缀 | 当不是全值匹配时，匹配索引前n列的查询条件会使用索引，其他条件不会 | 正例：查找姓为Allen的人或姓名为Allen Kim的人；反例：查找名字为Kim或名字为Kim，出生日期为1930-07-12或出生日期为1930-07-12的人；特例：查找姓为Allen，出生日期为1930-07-12的人时只会使用索引的第一列 |
| 匹配列前缀 | 遵循最左前缀或全值匹配原则的情况下，可以只匹配某一列值的开头部分 | 查询所有以J开头的姓的人，这里只使用了索引的第一列 |
| 匹配范围值 | 遵循最左前缀或全值匹配原则的情况下，可以范围匹配某一列值 | 查找姓在Allen和Barrymore之间的人，这里也只使用了索引的第一列 |
| 精确匹配前n-1列并范围匹配第n列 | 遵循匹配最左前缀的情况下，允许出现前n-1列是精确查询（=、in等）而第n列是范围查询（<、>等） | 查找姓为Allen，名字以K开头的所有人，第一列last_name全匹配，第二列first_name范围匹配 |
| 只访问索引的查询（覆盖索引） | 查询结果列数据只需要访问索引即可获得而不需要根据主键去查找数据行（称为回表） | 查找姓名为Cuba Allen、出生于1960-01-01的人的姓、名字和出生日期 |
特别地，针对第5种匹配条件，如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询
WHERE last_name = 'Smith' AND frst_name LIKE 'J％' AND dob = '1976-12-23'，虽然这个查询满足全值匹配，但是这个查询只能使用索引的前两列，因为 LIKE 是一个范围条件。如果范围查询列值的数量有限，那么可以通过使用IN让范围查询变为“多个等值条件查询”
哈希索引
我们都知道哈希表在查询速度上的表现是无可匹敌的，所以哈希索引（Hash Index）也是另一种常见的索引实现方式，只有精确匹配所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（Hashcode），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向数据行的指针
然而，哈希索引也有它的限制：
- 哈希索引只支持精确匹配所有列的查询，因为哈希索引始终都是通过使用索引列的全部内容来计算哈希的
- 哈希索引只包含哈希值和行指针，而不存储字段，所以不能通过使用索引中的数据来避免访问行数据
- 哈希索引数据并不是按照索引值顺序存储的，故无法用于排序
- 哈希索引只支持等值比较查询，包括=、IN等
在MySQL中，只有Memery引擎显式支持哈希索引，另外，InnoDB有一个特殊的功能叫做“自适应哈希索引（Adaptive Hash index）”，当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B+Tree索引值上再建立一个哈希索引，这样就让查询速度进一步提高，但这是一个完全自动的、内部的行为（自适应），用户无法控制或者配置
多列索引
对于具有多个条件的查询来说，单列索引最好的情况下也只是相当于一级目录，并且实际中使用最多的往往是多条件查询，所以设计多列索引是非常有必要的，并且，一个好的多列索引能够大大提高查询性能，所以下面将针对多列索引讲述一些索引优化策略
三星索引
在讲述优化策略之前，我们先讲讲一个索引的评价标准，换句话说，就是适合某个查询的好索引应该具备哪些性质，主要有以下三点：
- 索引是否将相关的记录放到一起
- 索引中的数据顺序和查找中的排列顺序是否是一致的
- 索引中的列是否包含了查询中需要的全部列
参见Tapio Lahdenmaki和Mike Leach编写的Relational Database Index Design and the Optimizers 一书中的“三星系统”（three-star system）
理解索引的最好办法就是结合实例，所以这里先准备一个具体的表，假设要设计一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色，等等。网站必须支持上面这些特征的各种组合来搜索用户，还必须允许根据用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。在后面的优化策略中会根据此表进行举例说明
合适的索引顺序
很多人对多列索引的理解都不够，一个常见的错误就是按照错误的顺序创建索引，而索引的顺序又是索引设计中的关键环节
对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放在前面通常是很好的
什么是选择性最高？索引的目的就是为了快速地根据条件筛选数据，所以一个能够使 筛选过后的数据行数越少 的条件 的选择性 就越高，选择性越高的条件放到索引列顺序的前面就能够越快地缩小查询范围，从而提升查询速度
但是，这是通常的情况，当不需要考虑排序和分组时，将选择性最高的列放到前面通常是很好的（又是通常，所以说没有一个放之四海皆准的准则，场景不同则选择不同），这时候索引的作用只是用于优化WHERE条件的查找，但是同时也一定要记住别忘了WHERE子句中的排序、分组和范围条件等其他因素，这些因素可能对查询性能造成非常大的影响
现在，针对前面的例子，哪些列的选择性更高？很明显是地区、城市等，但是考虑到使用的频率，索引应该将性别列作为前缀，那么问题来了，根据刚刚的经验法则不是说不应该在选择性低的列上创建索引吗？那为什么要将选择性很低的性别作为索引的前缀？
这么做有两个理由：第一点，如前面所述，考虑到几乎所有的查询都会用到性别列，甚至网页会被设计成为每次都只能按一种性别搜索用户，更重要的是，索引中加上这一列也没有坏处，即使查询没有使用性别列也可以通过下面的“诀窍”绕过
这个诀窍就是：如果某个查询不限制性别，那么可以通过在查询条件中新增 AND SEX IN('m', 'f') 来让MySQL选择该索引，虽然这样写不会过滤任何行，但是伙计，它能用索引了呀！这个诀窍在类似的场景中非常有效，但如果列有太多不同的值，就会让IN()列表太长，这样做就不行了
支持多种过滤条件
在设计索引时还需要遵循的一个原则就是考虑表上所有的选项。当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。应该同时优化查询和索引已找到最佳的平衡，而不是闭门造车去设计最完美的索引
避免多个范围条件
假设有一个last_online列并希望通过下面的查询显示过去几周上线过的用户：
WHERE
eye_color IN ( 'brown', 'blue', 'hazel' )
AND hair_color IN ( 'black', 'red', 'blonde', 'brown' )
AND sex IN ( 'M', 'F' )
AND last_online > DATE_SUB( NOW(), INTERVAL 7 DAY )
AND age BETWEEN 18 AND 25
这个查询有一个问题：它有两个范围条件，last_online列和age列，MySQL可以使用last_online列索引或者age列索引，但无法同时使用它们。
如果条件中只last_online而没有age，那么我们可能考虑在索引的后面加last_online列。这里考虑如果我们无法把age字段转换为一个IN()的列表，并且仍要求对于同时有last_online和age这两个维度的范围查询的速度很快，那该怎么办？
答案是，很遗憾没有一个直接的办法能够解决这个问题。但是我们能够将其中的一个范围查询转换为一个简单的等值比较。为了实现这一点，我们需要事先计算好一个active列，这个字段由定时任务来维护。当用户每次登录时，将对应值设置为1，并且将过去连续七天未曾登录的用户的值设置为0
这样查询就可以使用age范围条件了
优化排序
对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。例如，可以创建（sex，rating）索引用于下面的查询：
SELECT<cols>
FROM PROFILES
WHERE sex='M'
ORDER BY rating
LIMIT 10
这个查询同时使用了ORDER BY和LIMIT，如果没有索引的话会很慢。
EXPLAIN
参考：Mysql官方文档
简单记录一下MySql内置的sql分析命令 EXPLAIN 执行结果的参数意义， EXPLAIN 的执行结果如下：
| 参数名 | 参数意义 | 取值说明 |
|---|---|---|
id |
子查询的执行顺序 | id 相同时由上至下执行； id 不同时 id 值越大优先级越高越先被执行 |
select_type |
查询的类型，用于区分简单查询、联合查询和嵌套查询 | SIMPLE ：简单查询PRIMARY ：嵌套查询中最外层的查询SUBQUERY ：子查询（ SELECT 后或 WHERE 后）DERIVED ： 子查询（ FROM 后）UNION ：子查询（ UNION 后）UNION RESULT ： UNION 执行结果 |
table |
该行的查询是关于哪张表的 | 表名（有别名时会显示别名） |
type |
查询类型，从最好到最差依次是： system > const > eq_ref > ref > range > index > ALL ，一般情况下至少得保证达到 range 级别，最好 ref |
system ：表只有一行记录，基本不会出现，可以忽略const ：查询使用了【主键】或【唯一索引】且条件为固定值（=）ref ：查询使用了普通索引且条件为固定值（=）eq_ref ：使用主键或唯一索引作为连接条件的连接查询（=）range ：查询使用了普通索引且条件为范围index ：遍历索引树，通常比 ALL 快，因为索引文件通常比数据文件小ALL ：全表扫描 |
possible_keys |
可能使用到的索引 | 一个或多个，执行时不一定会被用到 |
key |
实际使用的索引 | 出现 possible_keys 列有值而 key 列为 null 的情况表示实际没用索引 |
key_len |
索引字段的最大可能长度 | 一般情况下越短越好 |
ref |
显示索引的哪些列被使用 | 多个表的情况下会显示表前缀，一个表的简单查询情况下只会显示一个或多个const |
rows |
... | ... |
filtered |
... | ... |
Extra |
... | ... |
分库分表
常用函数
字符串操作
CONCAT(str1[, str2, ...])
直接拼接参数列表给出的多个字符串，如果参数列表中存在 NULL ，则结果为 NULL
GROUP_CONCAT(expr)
也是拼接多个字符串，与 CONCAT 的区别是，这个是聚合函数，对当前组中某列的所有值进行拼接并采用 , 作为分割符，会自动跳过 NULL 值，如果参数全部为 NULL ，则结果为 NULL
LENGTH 和 CHAR_LENGTH
这两个都是计算字符串长度的函数，区别是 LENGTH 计算的是字符串占用的存储空间，而 CHAR_LENGTH 计算的是字符串中的字符数
，一般来说一个英文字符只需一个字节的存储空间去存储，而一个汉字字符一般会占用两个字节，如果采用utf8mb4字符格式，一些比较复杂的汉字还会占用到四个字节
在进行数据库设计时，字符串类型的字段长度确定也要考虑到中英文字符占用存储空间是不同的，此外还要考虑该字段保存的内容是否包含复杂汉字或Emoji表情从而确定字符编码使用utf8还是utf8mb4
REPLACE(str, from_str, to_str)
SUBSTR 和 SUBSTRING
POSITION
数值操作
SUM
求和，会自动跳过 NULL 值，如果参数全部为 NULL ，则结果为 NULL
ROUND(X, [D])
对数值 X 进行四舍五入操作， X 可以为负数，向负无穷大方向舍入
D 指定精度，省略时精度默认为 0 ，也就是只保留整数部分，可以为负数
X 为 NULL 时结果为 NULL ，为其它非数值类型时会隐式转换为数值类型，适用MySQL的隐式转换规则
TRUNCATE(X, D)
对数值 X 进行向下取舍操作， X 可以为负数，向 0 方向舍入
D 指定精度，不可省略 ，可以为负数
X 为 NULL 时结果为 NULL ，为其它非数值类型时会隐式转换为数值类型，适用MySQL的隐式转换规则
日期时间操作
CURRENT_DATE 和 CURDATE
CURRENT_TIME 和 CURTIME
CURRENT_TIMESTAMP 和 NOW 和 SYSDATE
DATE(expr) 和 STR_TO_DATE(str, format)
DATEDIFF(expr1, expr2)
DATE_ADD(date, INTERVAL expr unit)
DATE_SUB(date, INTERVAL expr unit)
DATE_FORMAT(date, format)
常用语句
记录一下一些修改表结构的语句模板
ALTER TABLE `db`.`table_name`
ADD `new_column_name` UNSIGNED BIGINT(20) NOT NULL DEFAULT 0 COMMENT '添加新列' AFTER `exist_column_name`,
CHANGE `old_column_name` `new_column_name` VARCHAR(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '整体修改列，包括列名',
MODIFY `column_name` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改除列名外的其它列属性';
ALTER TABLE `db`.`table_name`
ADD UNIQUE INDEX `u_idx`(`column_name1`, `column_name2`),
ADD INDEX `idx`(`column_name1`, `column_name2`);
常见问题
记录一下自己遇到过的问题
- 如果 表名 或 字段名 为 mysql 关键词（尽量避免），需要加反引号，否则会报错，例如：
order，user
SELECT *
FROM `order`
SELECT *
FROM `user`
- 数据库连接url的
zeroDateTimeBehavior=convertToNull参数代表接受的时间格式错误时把错误的日期转换为null
，避免了java.sql. SQLException: Cannot convert value '0000-00-00 00:00:00' from column XX to TIMESTAMP异常，例：
spring.datasource.url=jdbc:mysql://localhost:3306/bingo?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
不推荐使用，错误的日期时间应该在参数校验或业务处理过程中杜绝，不应该让其入库
-
开发和测试环境下的数据库连接后可加上参数
useSSL=false
避免程序发出Establishing SSL connection without server's identity verification is not recommended.警告，但是生产环境下必须使用SSL安全连接 -
强制结束查询
# 进入命令行
mysql -u root -p
# 查看当前正在执行的查询
show processlist;
# 根据查询ID强制停止查询
kill [ID];
- 使用
ORDER BY排序后，用LIMIT只取前几条，多次执行发现返回的结果集不唯一
MySQL官方参考手册中对 LIMIT 有如下描述（原文地址）：
如果
ORDER BY列有多行具有相同的值，服务器可以自由地以任何顺序返回这些行，并且根据总体执行计划可能以不同的方式返回
换句话说，如果 ORDER BY 列有相同的值，那么MySQL可以自由地以任何顺序返回这些行。如果你需要确保无论带不带 LIMIT 都要以相同的顺序返回，那么你可以在 ORDER BY 中包含附加列，以使顺序具有确定性。比如可以在排序列后加上主键字段或其它唯一字段。
- 隐式类型转换问题
在某次处理生产问题时，发现一个SQL语句的查询条件明明是 condition = 1 ，结果却返回了 condition = 2 的记录，经排查后发现该 condition 字段的数据库类型是用 varchar 存储的 bigint （其实设计上也不合理），SQL中等式后给的是数字，对应JAVA的 long 类型，这样一来MySQL在执行比较的时候会自动将数据库的字段类型转换为数字，由于默认的数字类型是 int ，所以转换过程中发生了精度丢失的问题，导致最终返回结果与预期不符
MySQL官方参考手册中对隐式类型转换的说明如下（原文地址）：
当运算符与不同类型的操作数一起使用时，将进行类型转换以使操作数兼容。有些转换是隐式发生的。例如，MySQL 根据需要自动将字符串转换为数字，反之亦然。
所以，在不熟悉MySQL隐式类型转换规则的情况下，写SQL的时候需要注意给出的条件值类型需要和对应条件列的数据库类型匹配，这样才能保证不会出现意想不到的问题
浙公网安备 33010602011771号