MySQL总结

1.字段类型隐式转换

没加单引号(‘ ’)后果：索引失效/逻辑错误

2.sql顺序

FORM:
对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1

ON:
对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。

JOIN：
如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。

WHERE：
对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。

GROUP BY:
根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.

HAVING：
对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。

SELECT：
执行select操作，选择指定的列，插入到虚拟表VT8中。

DISTINCT：
对VT8中的记录进行去重。产生虚拟表VT9.

ORDER BY:
将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

3.更新多列不能用and：用了之后就成了逻辑语句。

4.where之后的条件要写全，否则就会执行所有带where之后字段的数据

5.哪种insert语句插入效率更高：

可以一次insert多条，大数据量的时候可以使用load

6.删除数据：drop table tb1 表如果太大，会暂挂

7.truncate与drop的异同

drop会把数据表也一并删除，truncate只会清空表，而不会把表删除。

8.Qunar命令规范

库表：名称全小写，使用下划线做分隔符，禁止使用驼峰式命名；名称得体，可以望文生义。

临时表、库，以tmp为前缀，日期为后缀；备份表、库要以bak为前缀，日期为后缀，这样便于维护。

字段：字段名称全小写，使用下划线作为单词之间的分隔符，禁止使用驼峰式命名。

索引：索引名称全小写，使用下划线作为单词之间的分隔符，禁止使用驼峰式命名。

非唯一索引使用idx为前缀，后面加上索引使用的字段名称，例如idx_book_name。

唯一索引使用uniq作为前缀，后面加上索引使用的字段名称，例如uniq_book_id。

联合索引的索引名称包含索引中的所有字段名，例如idx_author_time。

9.数据库使用规范

建表：
数据库引擎一般使用innodb引擎，使用与业务无关的自增无符号数作为表的主键。字符集为utf8或utf8mb4字符集。要在所有的表和字段中添加注释。表中字段不能存储大数据，例如图片、文件等。将表中数据量控制在5000w以内为最佳。

索引规范：
单长表的索引数不超过5个，单个索引的字段数不超过5个，因为MySQL会对每一个索引建表，太多、太繁琐的索引会降低数据库的效率。每一张表都要有主键索引，并使用自增的UNSIGNED的数值作为索引。禁止使用外检，需要使用JOIN查询的列的数据类型必须相同，且应当建立索引。在过长的VARCHAR上建立索引是，应使用前缀索引或添加CRC32、MD5伪列建立索引。

字符集：
utf8字符集中，一个字符占3个字节；utf8mb4字符集在utf8字符集的基础上，还支持emoj等字符。JOIN字段的字符集必须匹配，否则可能导致索引失效。对于字符集更改，可以使用一下两种办法：

alter table t1 charset utf8mb4; #只更改之后插入的数据的字符集为utf8mb4

alter table t1 covert to charset utf8mb4; #不仅更改之后插入的数据的字符集为utf8mb4，还会更新之前插入的数据

10.数据库表字段

使用UNSIGNED类型储存非负值；

使用INT UNSIGNED类型来储存IPv4；

所有字段是非空的；

货币相关字段应使用DECIMAL，因为浮点数FLOAT、DOUBLE的值会有浮动，产生争议；尽可能使用小的VARCHAR以节省空间。

表中字段禁止存储明文密码，不建议使用TEXT、BLOB类型。

11.索引原理

索引的作用：
快速定位数据位置，可以保证数据的完整性（主键索引、非空索引）

索引的数据结构：
B+Tree，这种数据结构具有如下特点：叶子节点包含所有的数据；非叶子节点只包含索引信息，即部分数据；搜索需要的时间稳定；叶子节点排列有序，有指针将所有的叶子节点链接起来。

类型：
聚集索引（主键）：直接在B+Tree上查询数据。

非聚集索引（非主键）：
在B+Tree上查询到主键，再定位到聚集索引上的数据。

单列索引

联合索引：
联合索引是有顺序的，它可以作为第一个字段的单列索引，也可以优化第二字段的排序

覆盖索引：
索引包括所有要查询的字段的值。

12.SQL优化：避免bad sql

select/delete/update中无where条件，或条件范围太大：

delete无where条件时，应将delete改为truncate table

select/update范围太大，可以改为分段执行。

避免子查询：子查询会产生临时表，临时表中无索引。

不要再索引字段上使用函数，使用函数会使索引失效

避免like ‘%XXX%’，可以使用like ‘XXX%’