sql

Mysql拓展知识

Mysql拓展知识

MySQL中show语法使用总结

https://www.cnblogs.com/saneri/p/6963583.html

我们一般接触到的Mysql语句都是增删改查数据库中存储的数据，但是Mysql还提供了很多对于其他信息的查询，这样的语句可以用于例如堆叠注入的地方。

show命令就可以显示很多的metadata的信息

Figure 1: help show: https://dev.mysql.com/doc/refman/5.7/en/show.html

Figure 1: help show: https://dev.mysql.com/doc/refman/5.7/en/show.html

帮助查看：

mysql> help show mysql> help SHOW TABLE; mysql> help SHOW WARNINGS

1.显示mysql中所有数据库的名称.

mysql>show databases;

2.显示当前数据库中所有表的名称

mysql>show tables; 或 mysql>show tables from database_name;

3.显示表中列名称

mysql>show columns from database_name.table_name;

4.查看某MySQL用户的使用权限

mysql>show grants for user_name;

5.显示create database 语句是否能够创建指定的数据库,并可以查看到创建库语句的SQL信息。

mysql>show create database database_name;

6.显示create table 语句是否能够创建指定的数据表,并可以查看到表创建语句的SQL信息。

mysql>show create table table_name;

7.显示安装以后可用的存储引擎和默认引擎。

mysql>show engines;

8.显示最后一个执行的语句所产生的错误、警告和通知

mysql> show warnings;

9.只显示最后一个执行语句所产生的错误

mysql>show errors;

10.显示系统中正在运行的所有进程，也就是当前正在执行的查询.

mysql> show processlist\G

11.查看所有存储过程。

mysql> show procedure status;

12.查看某个存储过程内容

show create procedure 存储过程名称;

13.查看函数的内容.

show create function func_name;

预编译语句 Prepare statement

Syntax: PREPARE stmt_name FROM preparable_stmt

PREPARE 语句会把一个SQL语句赋值给一个变量名stmt_name用于后面的引用。

预编译语句会被 EXECUTE 执行，然后被 DEALLOCATE PREPARE 释放

以下是一个使用预编译语句的例子，使用’?‘来占位，然后通过 SET 来赋值再结合在一起使用，最后用 DEALLOCATE PREPARE 来释放 

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;

Index 索引

参考： 一文搞懂MySQL索引（清晰明了）

MySQL索引实现原理分析

Basic 基础概念

Index，索引，是Mysql数据库中一个用来提高表内查询速度的数据结构。

如果没有Index，那么当我们想要从一个有一万条记录的表中找到我们想要的一列时，最坏的情况就是要遍历一万条数据才能发现匹配的;

但是如果是有Index的话，就可以像字典一样，提前找到其位置，然后根据位置快速得到其内容，Index的使用极大的提升了Mysql的查询速度和效率；

Index的使用将极大的帮助SELECT语句，但是会降低INSERT 以及 UPDATE的速度：因为对于table的变动还需要更新index的信息；

我在学习的时候有Index的概念的时候还是没有跳脱出之前增删改查的语法的使用，一直在找资料想看看Index是不是一个独立的结构，具体要创建以及在查询中使用，慢慢开始理解之后才突然意识到Index的创建其实就是一种字典索引的建立，只要创建了合适的索引，那么再下一次进行select语句的时候，Index就会自动加速本次查询，也就是我想找的如何使用Index的答案。

接着我就看到了W3schools给的解释，就非常清晰了：The users cannot see the indexes, they are just used to speed up searches/queries :)

接下来我们就来看看具体要如何创建Index

Index的语法

创建一个最基本的Index，允许存在重复的值 

CREATE INDEX index_name
ON table_name (column1, column2, ...);

例子 

CREATE INDEX idx_lastname
ON Persons (LastName); 

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

创建一个唯一的Index，不允许重复的值 

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

通过修改表结构来添加索引 

ALTER table tableName ADD INDEX indexName(columnName)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length。

创建表的时候直接指定INDEX 

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);

删除索引 

DROP INDEX [indexName] ON mytable;

Index使用的准则与场景

之前我们提到了Index本质是一个数据结构，那么作为一个数据结构来存储大量的数据，势必也需要占用物理磁盘，那么如何合理的利用索引来加速查询的同时又减少开销呢？

  • 应当创建索引的列

    • 在 经常需要搜索的列 上，可以加快搜索的速度
    • 在作为 主键的列 上，强制该列的唯一性和组织表中数据的排列结构
    • 在经常用在 连接（JOIN）的列 上，这些列主要是 外键 ，可以加快连接的速度
    • 在经常需要 根据范围 （<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为 索引已经排序 ，其指定的范围是连续的
    • 在经常需要 排序 （order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
    • 在经常使用在 WHERE子句中的列 上面创建索引，加快条件的判断速度。

  • 不该创建索引的列

    • 对于那些在查询中 很少使用或者参考reference的列 不应该创建索引。
      • 若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
    • 对于那些只有 很少数据值 或者 重复值多的列 也不应该增加索引。
      • 这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
    • 对于那些定义为 text, image和bit 数据类型的列不应该增加索引。
      • 这些列的数据量要么相当大，要么取值很少。
    • 当该列 修改(Insert, Update)性能要求远远高于检索(Select)性能 时，不应该创建索引。（修改性能和检索性能是互相矛盾的）

Index的实现原理

那么Index究竟使用了什么数据结构来进行存储进而加快了检索速度呢？

我们以常见的InnoDB来举例，其使用的是B+Tree来进行索引结构，将原本需要对所有的记录依次匹配记录的方式变为了通过树来进行二元判断逐渐缩小范围

B+Tree的特点是其本身在叶节点就会存储完整的数据记录，因此我们可以说，索引所存储的文件，就是数据表其本身，以及：

  • 所有关键字都出现在叶子结点的链表中，且链表中的关键字恰好是有序的；
  • 不可能在非叶子结点命中；
  • 非叶子结点相当于是叶子结点的索引，叶子结点相当于是存储（关键字）数据的数据层；
  • 每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
  • 更适合文件索引系统

具体的分类与实现方法我们放在下面讲，这里先有一个基本的认识

Index的分类

  • 逻辑分类

    • 按功能划分

      1. 主键索引：将一张表的主键列设为索引，特点是一张表只能有一个主键索引，不允许重复，也不允许为NULL 

        ALTER TABLE TableName ADD PRIMARY KEY(column_list);

        反过来说，一个有主键的数据表的存储形式就是一个主键索引的文件

      2. 唯一索引：被索引的列的值不允许重复，但允许为NULL，一张表可以创建多个唯一索引；同时，如果是组合索引，列值的组合也必须唯一 

        CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD UNIQUE (column_list);

      3. 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许NULL值插入 

        CREATE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length));

      4. 全文索引：查找文本中的关键词，主要用于全文内容的检索

    • 按被索引的列数划分

      1. 单例索引：一个索引只包含一个列，一个表可以创建多个单例索引
      2. 组合/联合索引：一个组合索引包含两个或两个以上的列组合成为索引检索的对象。

        • 其满足“最左前缀”原则，即使用where时条件要按照建立所以的字段的排列方式设置才能使索引生效

          Figure 3: 联合索引：将几个列的值组合起来进行索引

          Figure 3: 联合索引：将几个列的值组合起来进行索引

  • 物理分类

    物理分类主要通过实现Index实例的方法氛围聚簇索引和非聚簇索引（也叫辅助索引或者耳机索引），那么主要的区别就是

    • Clustered Index 聚簇索引

      其主要特点就是，数据的存储与索引是放在一起的，索引完成之后，数据也随之被找到。

      Figure 4: B+Tree

      Figure 4: B+Tree

    • 非聚簇索引

      数据和索引是分开的，其B+Tree叶子结点存放的是真正数据的地址而非数据的内容

      Figure 5: 辅助索引：数据与索引分开

      Figure 5: 辅助索引：数据与索引分开

Index: pros & cons

减少了服务器扫描的数据量，大大加快了数据的检索速度 主要优点：

  • 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
  • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

主要缺点：

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
  • 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
  • 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
  • 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  • 对于非常小的表，大部分情况下简单的全表扫描更高效；

Mysql中key(primary key, unique key) v.s. index的区别

我们在优化Mysql的时候，经常会看到key与index的连用或者替换，这同样也是我非常的困扰，我们今天就来好好理一理，到底如果来使用这两个名词

key
key包含两层含义
  1. 约束，表示对于列的一些特殊的要求
  2. 索引，当我们将一列设置为key时，其自动就会创建一个Index（可用show index from table_name;进行查询）
primary key
  1. 约束：列的值唯一,非空，唯一标识
  2. 为这个主键列（这个key）建立一个主键索引（InnoDB中就会在叶子节点存储记录的真实数据）
unique key
  1. 约束：保证列值的唯一性
  2. 为这个列建立一个唯一索引
foreign key

  1. 约束：外键的约束，规范数据的引用完整性

  2. 为这个外键建立一个普通索引

DESCRIBE/EXPLAIN

Mysql文档：13.8.2 EXPLAIN Statement

EXPLAIN的目的可以帮助分析一条select语句具体是如何进行查询的，包括对于引用，索引的使用； 

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

Figure 7: explain example

Figure 7: explain example

DESCRIBE语句通常被用来解析table的结构：

Figure 8: DESCRIBE语句描述table的结构

Figure 8: DESCRIBE语句描述table的结构

Handler 句柄

参考： 【MySQL】MySQL 之 handler 的详细使用及说明

13.2.4 HANDLER Statement

HANDLER 语句提供了直接访问table storage engine的接口，适用于InnoDB and MyISAM 表

HANDLER ... OPEN 语句可以打开一个table，并通过接下来的 HANDLER ... READ 语句来进行访问，一旦打开后就开启了一个独立的会话，并且不会被其他的会话共享直到 HANDLER ... CLOSE 之前都不会消失

Basic Syntax, 基础语法： 

# 打开一张表，并且可以选择alias
HANDLER tbl_name OPEN [ [AS] alias] 

# 1. 查看表所创建的索引来检索反过来查看table的内容，
HANDLER tbl_name READ index_name { = | <= | >= | < | > } (value1,value2,...)
    [ WHERE where_condition ] [LIMIT ... ]
# 2. 同时也可以用FIRST, NEXT, PREV, LAST来辅助索引进行遍历
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]

# 3. 不通过索引来查看表（速度变慢，检索的形式也变少了）
# READ FIRST: 获取句柄的第一行
# READ NEXT: 依次获取其他行（当然也可以在获取句柄后直接使用获取第一行）
# 最后一行执行之后再执行 READ NEXT 会返回一个空的结果
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE where_condition ] [LIMIT ... ]

HANDLER tbl_name CLOSE

SELECT … INTO

参考： Mysql文档：13.2.9.1 SELECT … INTO Statement

SQL学习 之 into outfile 和 into dumpfile

SELECT ... INTO 表达式允许由SELECT语句查询到的结果存储到用户自定义变量或者文件中，mysql提供了三种形式：

SELECT … INTO var_list

SELECT … INTO var_list可以将查询到的column中的值存储在用户自定义变量当中 

SELECT * INTO @myvar FROM t1;
SELECT * FROM t1 INTO @myvar FOR UPDATE;
SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;

需要注意的是，查询的列数必须与自定义变量的数量一致，同时只能取一行的值（因为自定义变量也只能存储一行中的某一个具体的值）

Figure 9: SELECT &hellip; INTO var_list

Figure 9: SELECT … INTO var_list

SELECT … INTO OUTFILE/DUMPFILE ‘file_name’

SELECT … INTO OUTFILE ‘file_name’ 可以将查询到的多行写入文件当中，并且字段和行终止符都可以作为格式输出，由于存在服务器上的文件读写操作，因此必须要拥有文件的权限才能使用。

同时 file_name 不能是已经存在文件。

SELECT … INTO OUTFILE一般用于在服务器本地打开文件并存储，不太适用于其他远程host

而SELECT … INTO DUMPFILE ‘file_name’只能写入一行，并且输出中不存在任何格式

例子：

Figure 10: SELECT &hellip; INTO OUTFILE/DUMPFILE &lsquo;file_name&rsquo;

Figure 10: SELECT … INTO OUTFILE/DUMPFILE ‘file_name’

结果：

Figure 11: OUTFILE识别格式，DUMPFILE只能得到一行（实际情况是两行）

Figure 11: OUTFILE识别格式，DUMPFILE只能得到一行（实际情况是两行）

User-Defined Variables 用户自定义变量

Mysql文档：《9.4 User-Defined Variables》 

SET @var_name = expr [, @var_name = expr] ...
SET @var_name := expr [, @var_name := expr] ...

用户可以以上使用 SET 语句来存储自定义变量以供后续的sql语句使用。

自定义变量的格式为 @var_name, 其中 var_name 的部分可以使用字母表以及., ., 以及$三种符号，也可以用引号包裹来使用任意字符：

Figure 12: @var_name格式

Figure 12: @var_name格式

所有的自定义变量只在当前用户所开启的session会话中有效，并且一旦用户退出结束该session，所有的变量都会被清空。

同时自定义变量是不区分大小写的！

Locking Reads 上锁读取

参考：

Mysql文档：《14.7.2.4 Locking Reads》

深入理解SELECT … LOCK IN SHARE MODE和SELECT … FOR UPDATE

mysql 共享锁(读写锁) 修改数据问题（update,insert）（LOCK IN SHARE MODE）

Locking Reads的目的是为了保护在多个client的多个session会话中，对于数据读取的操作的一致性。

InnoDB提供了两种形式的保护：共享锁以及排他锁

SELECT … LOCK IN SHARE MODE，共享锁 Share Locks

共享锁，又称读锁，S锁，上锁之后该资源只能读不能修改以及删除，同时其他用户也可以给资源上共享锁；

使用场景：简单来说，共享锁适用于，你不要修改该数据，只需要保证数据的完整性的情况下，使用共享锁；

原因就是要避免死锁：

  • 当client A对于目标数据上了共享锁之后，这段数据只能读，而不能改，只有释放之后才能进行修改和删除；
  • 此时client B也对目标数据上了共享锁，并且想要修改这段数据，那么就要等待client A释放共享锁；
  • 可是此时client A也想要对目标数据进行修改，那么就要等待client B释放共享锁；
  • 双方都等待对方释放共享锁，因此双方也不会主动释放自己的共享锁，造成死锁；

因此我们说共享锁适用于两张表存在关系时的写操作，例如

  • 我们假设有child， parent两张表，child的外键是parent的主键uuid；
  • 当client A想要修改child的一条数据时，其需要保证parent中的主键没有被修改，否则这条child数据就会失去意义；
  • 因此client A就需要给parent中对应主键的记录加上 共享锁, 然后再修改child中的记录（使用排他锁）；
  • 这样就可以保证parent中的主键没有被修改；
  • 同时当client B也给parent中主键的对应记录加上共享锁，并且想要进行修改的时候，只需要等待client A完成对于child的作业之后，释放共享锁，即可开始操作，不会发生死锁。

对查询到的记录上共享锁： 

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

SELECT … FOR UPDATE，排他锁 Exclusive Locks

排他锁，又称为写锁、独占锁，X锁，一旦有一个用户对一段数据上了排他锁，那么其他用户不能再对该数据上任何锁，也不能进行读取和修改：

对t1中查询到的数据上排他锁，但是对于t2无法上锁： 

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

对t1, t2中查询到的数据上锁： 

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;
mysql
Licensed under CC BY-NC-SA 4.0
Last updated on Oct 02, 2022 09:05 CST
comments powered by Disqus
© 2022 Under The Ginkgo
Cogito, ergo sum
Built with Hugo
Theme Stack designed by Jimmy