mysql 查询分析
索引
索引的本质是排好序的快速查找数据结构。
创建唯一索引可以保证数据库中每一行的数据唯一性。
索引可以加速表与表之间的连接。
索引可以显著减少分组和排序时间。
HASH
存储无序，不支持范围查找，等值判断有优势。
InnoDB 不支持。
高纬空间搜索
geometry 数据类型把 B 树的思想扩展到了多维空间，支持对 (x,y) 类型数据的索引，不需要遍历整表。
SQL 查询执行顺序
FROM
ON->JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT
JOIN
小表驱动大表
被驱动表只查索引
连接之后的表存放在 buffer 中
INNER JOIN 时会自动优化驱动表
日常操作规范
禁止泄漏数据库连接地址及账号密码。
禁止通过数据库连接软件开启大量 session。
表设计规范
注意事项
禁止使用除 InnoDB 之外的引擎。
存在自增列的表，自增列至少有一个单独的索引，或者以自增列开头的复合索引。
每张表必须有主键，推荐用自增短列作为主键。
字段必须定义为 NOT NULL 并加上默认值。
建议事项
原则上同一类业务表放一个库，不同业务表避免共用一个库。
原则上表名应该包含：业务名、模块名、对象名。
避免在程序中执行跨库关联操作。
必须使用过 utf8mb4 字符集。
小数字段必须使用 decimal 类型。
避免使用 text/blob 类型，二进制数据、图片、文件等内容，应存入磁盘，数据库中仅保存索引信息。
禁止使用外建更新，应在应用层处理相关逻辑。
禁止使用存储过程、触发器、函数、视图等功能。
避免使用分区表。
索引设计规范
注意事项
禁止在更新频繁、低区分度的字段建立索引。
建立复合索引时，区分度高的列放在左边。
建议事项
单表索引不超过 5 个，单个索引不超过 5 个字段。
选择区分度高的，业务常用的列创建索引，例如对性别创建索引是低效的。
varchar 字段创建索引，应指定索引长度，不建议整列创建索引，可以使用 count(distinct left(列名, 索引长度))/count(*) 来查看索引区分度。
避免冗余索引，例如 (a,b) (a) 索引同时存在时，不用单独建立 (a) 索引。
合理利用覆盖索引来避免回表。
建立索引的情况
字段值具有唯一性（业务上唯一必须建立）。
频繁作为 WHERE 查询条件。
经常作为 GROUP BY 和 ORDER BY 的列。
经常作为 UPDATE、DELETE 的条件列（使用索引字段更新非索引字段）。
DISTINCT 字段。
使用字符串前缀创建索引，创建索引长度。
区分度高的列。
联合索引左侧同时考虑频繁程度及区分度。
联合索引优于单值索引。
索引数目不超过 5 个。
不建立索引的情况
WHERE 条件不使用。
数据量小于 1000 行。
大量重复数据。
频繁更新。
MD5、HASH、UUDI 等无序值。
不再使用或很少使用的索引应该删除。
不定义冗余或重复索引。
索引失效情况
不在联合索引左侧
计算、函数、类型转换
联合索引中，范围条件右侧的索引
负方向查询 (!=,not null)
字符串左侧模糊匹配
or 前后存在非索引列
JOIN 的索引
连接表数量禁止超过 3 张，会发生乘数级增长。
WHERE 条件创建索引。
对连接字段创建索引，且类型必须一致。
SQL 编写规范
注意事项
UPDATE、DELETE 操作禁止使用 LIMIT，必须使用 WHERE 精准匹配。
使用 INSERT 语句时必须显式指定插入的列属性。
避免隐式类型转换，例如索引 a 是 varchar 但 SQL 语句写成 where a = 1。
避免对索引列进行数学计算及函数操作，尤其是对日期时间列进行格式化处理。
禁止 join 列类型不一致，join 列必须都建有索引。
避免多列排序与索引顺序不一致。
在对 varchar 类型进行模糊查询时应使用右侧匹配 xxx% 形式。
避免使用负方向查询，例如 not,!=,not in 等
建议事项
禁止使用 select *。
尽量避免使用大事务，建议拆分为小事务。
禁止业务代码中不及时提交事务，造成锁等待。
避免使用多表 join，大表禁止使用 join。
使用 join 时必须使用小表驱动大表。
大表 LIMIT 操作必须确认首记录位置再分页。
避免使用多层嵌套自查询。
EXPLAIN
select_type：查询类型
type：访问方法（重点）
key：实际上使用的索引
key_len：索引长度（重点）
ref：与索引进行匹配的对象信息
filtered：条件过滤后剩余记录百分比（越高越好）
rows：扫过的行数（重点）
extra：额外信息（重中之重）
type
system：count 存在系统表中
const：主键匹配
eq_ref：使用主键或唯一索引（join 是简单查询）
ref：二级索引匹配
fulltext：全文索引
ref_or_null：二级索引为空
index_merge：索引合并
unique_subquery：子查询使用主键
index_subquery：自查询使用二级索引
range：范围索引
index：虽然使用索引，但是要扫全部索引
ALL：全表扫描
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
目标：至少达到 range 级别，要求是 ref 级别，最好是 const 级别。
key_len
越大越好
要考虑是否充分利用索引
针对联合索引考虑更多
Extra
Impossible WHERE
Using Where 存在使用数据过滤的情况
No matching
Using index 仅使用索引（好事）
Using index condition 有索引但是用不了
Using join buffer 没有索引可用，启动了内存加速来连表
Not exists 连接表有不存在的记录
Using intersect｜union｜sort_union 合并几个索引一起用
Using filesort 文件排序（特别可怕）
Using temporary 内存排序（同样可怕）