Pardofelis

索引结构#

二叉树 缺点：顺序插入时会形成一个链表，查询性能会大大降低。大数据量的情况下，层级较深，检索速度慢 Hash 通过哈希函数把键映射到槽位；发生哈希冲突时，存储引擎需要进一步比较或处理冲突。

1. Hash索引仅支持对等比较（=，IN），不支持范围查询（between，>，<，。。。）
2. 无法通过索引完成排序操作
3. 等值查询通常很快，但实际性能取决于数据分布、冲突和执行计划，不能一概认为比 B+ 树快。

MySQL中的InnoDB对Hash有自适应，Hash索引是存储引擎根据B+树索引在指定条件下自动构建的

索引分类#

根据存储形式划分

聚集索引选取规则：如果存在主键，主键索引就是聚集索引 如果没有主键，InnoDB 会选择所有列都为 NOT NULL 的第一个 UNIQUE 索引。 如果也没有这样的唯一索引，InnoDB 会生成隐藏的聚集索引。

由二级索引找到主键值，再到聚集索引读取行数据，称为回表查询。

索引语法#

创建索引

1
CREATE [UNIQUE|FULLTEXT] INDEX <INDEXNAME> ON <TABLENAME>(COLUMN,COLUMN,...);

查看索引

1
SHOW INDEX FROM <TABLENAME>;

删除索引

1
DROP INDEX <INDEXNAME> ON <TABLENAME>;

SQL性能分析#

SHOW STATUS 可查看服务器状态变量。下面的 Com_% 计数是服务器级别的语句计数，不限定当前数据库；也不等同于实际读取或修改的行数。

1
SHOW GLOBAL STATUS LIKE 'Com\_%';

慢查询日志 慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位秒，默认10）的所有SQL语句的日志。默认不开启。需要在配置文件中配置：slow_query_log=1 和long_query_time=10。配置完成之后重启服务器

profile详情 可以帮助了解SQL语句时间消耗 可通过 have_profiling 查看当前 MySQL 是否支持该功能：

1
SELECT @@have_profiling;

SHOW PROFILE 已弃用；旧版本中可在当前会话启用，新的性能分析应优先使用 Performance Schema。

1
SET profiling=1;

1
-- 查看当前会话中各语句的耗时
2
SHOW profiles;
3

4
-- 查看指定 query_id 的各阶段耗时
5
SHOW PROFILE FOR QUERY <QUERY_ID>;
6

7
-- 查看指定 query_id 的 CPU 耗时
8
SHOW PROFILE CPU FOR QUERY <QUERY_ID>;

explain执行计划 EXPLAIN或者DESC命令获取MySQL任何执行SELECT语句的信息，包括SELECT语句执行过程中表如何连接和连接顺序。

1
-- 在 SELECT 语句前加 EXPLAIN
2
EXPLAIN SELECT <COLUMN> FROM <TABLENAME>;

各个字段含义

• Id
  • SELECT 的标识符；不能只凭 id 大小推断实际执行顺序。
• select_type
  • 表示select的类型，常见的取值由simple（简单表，即不使用表连接或子查询）、Primary（主查询，即外层的查询）、UNION（UNION中的第二以及以后的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
• type
  • 表访问类型，常见值有 system、const、eq_ref、ref、range、index、ALL；需结合行数和过滤条件判断性能。
• possible_keys
  • 显示可能应用在这张表上的索引，一个或多个
• key
  • 实际使用的索引。如果为NULL，就是没有使用索引
• key_len
  • 优化器选用的索引键长度，可帮助判断联合索引用了哪些列；不是越短越好。
• rows
  • MySQL认为必须要执行查询的行数，在InnoDB引擎的表中是一个估计值，可能并不总是准确的
• filtered
  • 估计经过表条件筛选后保留的行占 rows 的百分比；需结合实际执行计划判断，不能单凭数值高低评价查询。

索引的使用#

最左前缀法则 联合索引通常按最左列开始使用；跳过前导列或遇到范围条件后，后续列能否用于索引查找取决于查询条件和优化器。用 EXPLAIN 确认实际使用情况。 索引列运算 对索引列直接做函数或运算，通常会妨碍普通索引用于查找；可考虑改写条件或使用函数索引，并用 EXPLAIN 验证。 字符串不加引号 字符串值应使用引号。若比较时发生隐式类型转换，可能妨碍索引使用，具体取决于列类型和执行计划。 模糊查询 LIKE 'aa%' 有机会利用 B+ 树索引做范围查找；LIKE '%aa' 通常不能使用普通 B+ 树索引定位前缀。 or连接的条件 OR 的各条件能否用索引，取决于可用索引、数据分布和优化器选择；可检查 EXPLAIN 中是否使用 Index Merge 或全表扫描。 数据分布影响 如果MySQL评估索引比全表慢，则不使用索引 SQL提示 在SQL语句中加入一些人为的提示优化操作

1
-- USE INDEX
2
SELECT * FROM <TABLENAME> USE INDEX(<INDEXNAME>) WHERE <OPTION>;
3

4
-- IGNORE INDEX
5
SELECT * FROM <TABLENAME> IGNORE INDEX(<INDEXNAME>) WHERE <OPTION>;
6

7
-- FORCE INDEX
8
SELECT * FROM <TABLENAME> FORCE INDEX(<INDEXNAME>) WHERE <OPTION>;

覆盖索引 当查询需要的列都可从索引取得时，可避免回表；按实际查询选择列，避免无必要的 SELECT *。

前缀索引 对较长的字符串列，可只索引前缀以减少索引占用；前缀过短会降低区分度，需要按数据分布选择长度。

1
CREATE INDEX <INDEX_NAME> ON <TABLE_NAME> (<COLUMN_NAME>(<PREFIX_LENGTH>));

可用 COUNT(DISTINCT LEFT(列名, N)) / COUNT(*) 估计前缀的区分度。区分度越高通常越利于筛选，但实际性能仍需结合索引大小和查询计划衡量。

单列索引与联合索引 单列：一个索引只包含一列 多列：一个索引包含多列 多个条件经常一起出现时，可考虑符合列顺序的联合索引；单列索引与联合索引的取舍应依据实际查询、写入成本和 EXPLAIN 结果。

索引设计原则#

1. 针对数据量大、且查询比较频繁的表建立索引。
2. 对常作为查询条件、分组、排序的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的索引，字符串长度较长，可以根据字段的特点建立前缀索引。
5. 联合索引可覆盖部分常用查询并减少回表，但不一定比单列索引节省空间。
6. 要控制索引数量，索引不是越多越好，索引越多，维护索引的代价越大，影响增删改的效率。
7. 如果索引列不能存储NULL，在建表时候用NOT NULL限制，当优化器指定每列是否包含NULL时，它可以更好得确定哪个索引最有效。

语法#

创建或修改视图

1
CREATE [OR REPLACE] VIEW <VIEWNAME>(NEWCOLUMNNAME1,NEWCOLUMNNAME2,...) AS <SELECT语句> [WITH [CASCADED | LOCAL] CHECK OPTION];

查询视图

1
-- 查看创建视图语句
2
SHOW CREATE VIEW <VIEWNAME>;
3

4
-- 查询视图数据
5
SELECT * FROM <VIEWNAME>;

删除视图

1
DROP VIEW <VIEWNAME>;

视图的检查选项： 使用 WITH CHECK OPTION 创建可更新视图后，经该视图执行的 INSERT 和 UPDATE 必须使新行符合视图条件。 默认是 CASCADED。若视图 A 基于视图 B：CASCADED 会递归检查底层视图条件；LOCAL 检查 A 的条件，并继续按 B 自身的检查选项判断，不会强制给 B 添加检查选项。

视图的更新#

要使得视图可以更新，视图与基表的行必须为一对一关系。如果视图包含以下任何一项，则视图无法更新：

1. distinct
2. group by
3. having
4. UNION or UNION ALL
5. 聚合函数或窗口函数

视图的作用#

1. 视图可以封装常用查询，减少重复书写查询条件。
2. 可以向用户授予视图权限，并通过视图定义限制其可见的数据；权限效果还取决于视图的安全上下文和底层表权限。
3. 视图可以帮助用户屏蔽真实表结构变化带来的影响

语法#

创建

1
CREATE PROCEDURE 存储过程名称([参数列表])
2
BEGIN
3
    SQL语句;
4
END;

调用

1
CALL 名称(参数);

查看数据库的存储过程

1
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
2
WHERE ROUTINE_SCHEMA = '数据库名称' AND ROUTINE_TYPE = 'PROCEDURE';

查看存储过程创建语句

1
SHOW CREATE PROCEDURE 存储过程名称;

删除存储过程

1
DROP PROCEDURE [IF EXISTS] 存储过程名称;

变量#

在 mysql 命令行客户端中定义多语句存储程序时，需要临时更改语句结束符；DELIMITER 是客户端命令，不是 SQL。

1
-- 以下示例在 mysql 命令行客户端执行
2
DELIMITER //
3
CREATE PROCEDURE hello()
4
BEGIN
5
    SELECT 'hello' AS message;
6
END//
7
DELIMITER ;

1
SHOW [GLOBAL | SESSION] VARIABLES;
2

3
SHOW [GLOBAL | SESSION] VARIABLES LIKE '变量名模式';
4

5
SELECT @@SESSION.系统变量名;
6
SELECT @@GLOBAL.系统变量名;

1
SET [SESSION | GLOBAL] 变量名=值;
2

3
SET @@SESSION.变量名=值;
4
SET @@GLOBAL.变量名=值;

1
SET @变量名=值,@变量名=值,...;
2

3
SELECT @变量名:=值,@变量名:=值,...;
4
-- SELECT设置变量，必须要要用:=，SET的话:=和=都可

1
SELECT @变量名;

1
DECLARE 变量名 类型 [DEFAULT ...];

1
SET 变量=值;
2
SET 变量:=值;
3
SELECT 字段 INTO 变量 FROM 表...;

IF判断#

1
IF 条件 THEN
2
  ...
3
ELSEIF 条件2 THEN
4
  ...
5
ELSE
6
  ...
7
END IF;

参数（形参）#

• IN
  • 需要调用时输入的，是默认的
• OUT
  • 作为返回值的参数
• INOUT
  • 既可以作为输入，也可以作为输出

用法

1
-- 使用SET对参数2进行赋值
2
CREATE PROCEDURE 存储过程(IN 参数1 类型, OUT 参数2 类型,...)
3
BEGIN
4
    SQL语句;
5
END;

CASE#

语法一

1
CASE 变量
2
    WHEN 值1 THEN SQL语句;
3
    WHEN 值2 THEN SQL语句;
4
    ...
5
    ELSE SQL语句;
6
END CASE;

语法二

1
CASE
2
    WHEN 条件1 THEN SQL语句;
3
    WHEN 条件2 THEN SQL语句;
4
    ...
5
    ELSE SQL语句;
6
END CASE;

WHILE#

1
WHILE 条件 DO
2
    SQL语句;
3
END WHILE;

REPEAT#

有条件的循环，满足条件则退出。

1
REPEAT
2
    SQL语句;
3
UNTIL 条件
4
END REPEAT;

LOOP#

可以实现简单的死循环，不在SQL中增加退出循环的条件。

LEAVE: 退出循环 ITERATE: 必须在循环中，表示跳过当前循环剩下的语句。

1
[开始标记]:LOOP
2
    SQL语句;
3
    -- 根据条件选择 LEAVE 或 ITERATE，通常不会连续执行二者
4
    LEAVE 开始标记;
5
END LOOP [结束标记];

游标#

用来存储查询结果集的数据类型，可以用游标进行循环处理。游标的使用包括声明、OPEN、FETCH、CLOSE。

声明

1
DECLARE 游标名 CURSOR FOR 查询语句;

打开游标

1
OPEN 游标名;

获取游标记录

1
FETCH 游标名 INTO 变量1,变量2,...;

关闭游标

1
CLOSE 游标名;

条件处理程序#

定义在流程控制执行过程中遇到条件时的处理步骤。

1
DECLARE handler_action HANDLER FOR condition_value,... SQL;

handler_action:

• EXIT 满足condition_value时退出
• CONTINUE 满足condition_value时继续

condition_value:

• SQLSTATE sqlstate_value:状态码，如SQLSTATE ‘02000’

常用的条件类别：

• SQLWARNING：SQLSTATE 类别 01。
• NOT FOUND：SQLSTATE 类别 02。
• SQLEXCEPTION：除成功、警告和未找到之外的异常类别。

语法#

1
CREATE TRIGGER 触发器名
2
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
3
ON 表名 FOR EACH ROW --行级触发器
4
BEGIN
5
    SQL;
6
END;

查看

1
SHOW TRIGGERS;

删除触发器

1
DROP TRIGGER [数据库名.]触发器名;
2
--如果没有指定数据库则默认为当前数据库