Mysql

已于 2022-09-14 14:10:28 修改
阅读量808 收藏 2
点赞数
文章标签： 数据库 mysql
于 2022-09-13 21:13:47 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/A54324/article/details/126841956
版权

Mysql

文章目录

Navicat常用操作

1:数据库字段解密查询(加密方式:Aes对称加密)

需求:在运维阶段,如何查询出客户想要的数据?便于分析问题?在查询db的时候,发现生产数据库字段都被加密了?我们改如何解密获取数据呢?

image-20220815152110228

select 
id ,
AES_DECRYPT(from_base64(fullname), 'eEowtDMUluls8gMq') as fullname ,
AES_DECRYPT(from_base64(phonenumber), 'eEowtDMUluls8gMq') as phonenumber ,
AES_DECRYPT(from_base64(fullname), 'eEowtDMUluls8gMq') as fullname, 
AES_DECRYPT(from_base64(companycode), 'eEowtDMUluls8gMq') as companycode
from iboarding_empbasicinfo 
where AES_DECRYPT(from_base64(fullname), 'eEowtDMUluls8gMq')='王希'

由于采用的是AES对称加密,加密和解密的key均为同一个,我们在navicat中执行此sql即可查询出解密后的数据

2:Aes字段解密后乱码问题

我们在使用Aes解密后,有可能会出现中文乱码问题,使用以下sqlCONVERT设置字段编码格式即可解决乱码

select DISTINCT CONVERT (AES_DECRYPT(from_base64(fullname), 'eEowtDMUluls8gMq')USING utf8)

3:Aes解密后字段过长无法读取问题?

上两步骤都是基于很短的字段的varchar类型,但是我们对于longtext这种格式解密后,发现还是会读取不出数据

SELECT
	id,
	AES_DECRYPT(from_base64(CAST(datas as char(9000))), 'eEowtDMUluls8gMq') as datas
FROM
	iboarding_syncdata 
WHERE
	id = 'ece74e2342523dc992cfcb700956cdbd'

因为longtext作为一个大文本格式,通常用来记录存储一些json格式的信息,所以我们这里要将类型转换一下指定一下长度即可

4:批量执行更新多条sql

在每条sql结尾加上 ;(分号) 即可

image-20220815151857175

查询

DISTINCT

相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。

SELECT DISTINCT col1, col2 FROM mytable;

eg:image-20220720154706543

对单个字段进行去重sql：
select distinct age from user;

查询结果
age
10
20
30

对多个字段进行去重sql：
select distinct name,age from user;

查询结果
name	age

One	    10
Zero	20
Two     20
Four	30
One	    30

对多个字段进行去重并求count的sql：
select count(distinct name,age) as total from user;

查询结果
total
5

多条件查询

     select
        *
        from rbcn_comm_boschemployee
        where 1 = 1
        and (
        id = '13291171066'
        or mobile = '13291171066'
        or employeeid = '13291171066'	
		)
      只要符合其中一条数据就返回

SQL判断

#sql判断
select username,if(sex=1,'男','女') as sex from user;

LIMIT

限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

返回前 5 行：

SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;

返回第 3 ~ 5 行：2为偏移量,3为返回的行数

SELECT * FROM mytable LIMIT 2, 3;

八、排序

  • ASC ：升序（默认）
  • DESC ：降序

可以按多个列进行排序，并且为每个列指定不同的排序方式：

SELECT * FROM mytable ORDER BY col1 DESC, col2 ASC;

九、过滤

不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。

SELECT * FROM mytable WHERE col IS NULL;

下表显示了 WHERE 子句可用的操作符

操作符说明
=等于
<小于
>大于
<> !=不等于
<= !>小于等于
>= !<大于等于
BETWEEN在两个值之间
IS NULL为 NULL 值
select * from  acl_permission where type BETWEEN 1 and 2

1、不为空
Select   *   From   table_name Where id<>''
Select   *   From   table_name Where id!=''

2、为空
Select   *   From   table_name Where id=''
Select   *   From   table_name Where ISNULL(id)

select * from  acl_permission where type in (1,2)

具体情况具体分析，如果字段是char和varchar型用 id=''可以；如果是int型用 ISNULL好些.

应该注意到，NULL 与 0、空字符串都不同。

AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。

IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。

NOT 操作符用于否定一个条件。

使用OR关键字时：

  • 只要符合这几个查询条件的其中一个条件，这样的记录就会被查询出来。
  • 如果不符合这些查询条件中的任何一条，这样的记录将被排除掉。

使用and关键字时：

  • 需要符合所有条件，这样的记录就会被查询出来。

  • 如果有任何一个条件不符合，这样的记录将被排除掉。

  • 数据库–关于truncate和delete的区别

    1.truncate

    删除表中的内容，不删除表结构，释放空间；

    2.delete

    删除内容，不删除表结构，但不释放空间

十:通配符

通配符也是用在过滤语句中，但它只能用于文本字段。

  • % 匹配 >=0 个任意字符；
  • _ 匹配 ==1 个任意字符；
  • [ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。

使用 Like 来进行通配符匹配。

SELECT * FROM mytable WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

不要滥用通配符，通配符位于开头处匹配会非常慢。

十一:计算字段

在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。

计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。

当然这里的as关键字也可以省略

SELECT col1 * col2 AS alias FROM mytable;

十二:CONCAT

CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格。

SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col FROM mytable;

使用方法：CONCAT(str1,str2,…) 
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

image-20220825143126986

十三:函数

各个 DBMS 的函数都是不相同的，因此不可移植，以下主要是 MySQL 的函数。

[#](http://www.cyc2018.xyz/数据库/SQL 语法.html#汇总)汇总

函 数说 明
AVG()返回某列的平均值
COUNT()返回某列的行数
MAX()返回某列的最大值
MIN()返回某列的最小值
SUM()返回某列值之和

AVG() 会忽略 NULL 行。

使用 DISTINCT 可以汇总不同的值。

SELECT AVG(DISTINCT col1) AS avg_col FROM mytable;

十四:日期和时间处理

  • 日期格式：YYYY-MM-DD
  • 时间格式：HH:MM:SS
函 数说 明
ADDDATE()增加一个日期（天、周等）
ADDTIME()增加一个时间（时、分等）
CURDATE()返回当前日期
CURTIME()返回当前时间
DATE()返回日期时间的日期部分
DATEDIFF()计算两个日期之差
DATE_ADD()高度灵活的日期运算函数
DATE_FORMAT()返回一个格式化的日期或时间串
DAY()返回一个日期的天数部分
DAYOFWEEK()对于一个日期，返回对应的星期几
HOUR()返回一个时间的小时部分
MINUTE()返回一个时间的分钟部分
MONTH()返回一个日期的月份部分
NOW()返回当前日期和时间
SECOND()返回一个时间的秒部分
TIME()返回一个日期时间的时间部分
YEAR()返回一个日期的年份部分
mysql> SELECT NOW();
2018-4-14 20:25:11

十五unix_timestamp时间戳

unix_timestamp时间戳是自 1970 年 1 月 1 日（00:00:00 GMT）以来的秒数。它也被称为 Unix 时间戳（Unix Timestamp）

MySQL中的UNIX_TIMESTAMP函数有两种类型供调用

一: 无参数调用：

UNIX_TIMESTAMP() 返回值：自'1970-01-01 00:00:00'的到当前时间的秒数差 
例子：SELECT UNIX_TIMESTAMP()  => 1339123415

结果:image-20220825144019040

二:有参数调用

例子:SELECT UNIX_TIMESTAMP('2012-06-08')  =>1339084800
自'1970-01-01 00:00:00'与指定时间(2012-06-08)的秒数差

结果:image-20220825144435782

下面三种格式返回结果都相同
SELECT UNIX_TIMESTAMP('20120608'); 
SELECT UNIX_TIMESTAMP('2012-6-8'); 
SELECT UNIX_TIMESTAMP('2012-06-08');
结果都是:1339084800
DATETIME字符串格式(日期和时间的组合类型)
SELECT UNIX_TIMESTAMP('2022-08-15 14:50:00')
结果为:1660546200

十六:分组

把具有相同的数据值的行放在同一组中。

可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。

指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

SELECT col, COUNT(*) AS num FROM mytable GROUP BY col;

GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

SELECT col, COUNT(*) AS num FROM mytable GROUP BY col ORDER BY num;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

SELECT col, COUNT(*) AS num FROM mytable WHERE col > 2 GROUP BY col HAVING num >= 2;

分组规定：

  • GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
  • 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
  • NULL 的行会单独分为一组；
  • 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

–>过滤–>分组–>排序

十七:子查询

子查询中只能返回一个字段的数据。

可以将子查询的结果作为 WHRER 语句的过滤条件：

SELECT * FROM mytable1 WHERE col1 IN (SELECT col2 FROM mytable2);

select lesson_num from edu_course WHERE buy_count not in (select lesson_num from edu_course WHERE lesson_num BETWEEN 2 and 25)

下面的语句可以检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次：

SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

十八:连接

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

连接可以替换子查询，并且比子查询的效率一般会更快。

可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

内连接

内连接又称等值连接，使用 INNER JOIN 关键字。

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。

SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

检索所有顾客的订单信息，包括还没有订单信息的顾客。

SELECT Customers.cust_id, Customer.cust_name, Orders.order_id
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

customers 表：

cust_idcust_name
1a
2b
3c

orders 表：

order_idcust_id
11
21
33
43

结果：

cust_idcust_nameorder_id
1a1
1a2
3c3
3c4
2bNull

自连接

自连接可以看成内连接的一种，只是连接的表是自身而已。

一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。

子查询版本

SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

自连接版本

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

[#](http://www.cyc2018.xyz/数据库/SQL 语法.html#自然连接)自然连接

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。

内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

插入

INSERT INTO mytable(col1, col2) VALUES(val1, val2);

更新

UPDATE mytable SET col = val WHERE id = 1;

删除

DELETE FROM mytable WHERE id = 1;

基础–事务

事务的四大特性

image-20220901155428962

并发事务问题

image-20220901155541745

事务隔离级别

image-20220901155952518

image-20220901160259049

--查看事务隔离级别
select @@TRANSACTION_ISOLATION
--设置事务隔离级别
set session transaction isolation level read uncommitted

进阶–索引

目录

image-20220901161009801

一:存储引擎

image-20220901161534506

--创建表的时候指定存储引擎
CREATE TABLE sso (
  id char(19) NOT NULL,
	name CHAR(20) NOT NULL  
)ENGINE=INNODB 

--查看表的创建信息
show create table sso

--查看当前数据库支持的存储引擎
show engines

InnoDB

介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎。

特点

DML操作遵循ACID模型，支持事务；
行级锁，提高并发访问性能；
支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

MyISAM

介绍

MYISAM是M小ySQL早期的默认存储引擎。

特点

不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快

Memory

介绍

Mmoy引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点

内存存放
hash索引（默认）
常见面试题:MyISAM和InnoDB的区别?
存储引擎特点
INNODB与MyISAM:事务、外键、行级锁
MyISAM只支持表锁

image-20220901165731967

存储引擎选择

image-20220901170026475

二:索引

索引概述

image-20220901173150602

索引结构

image-20220901173305771image-20220901173616636

索引结构

Btree

image-20220901200922311

如果我们按照顺序插入:就形成了单向链表

B+Tree

image-20220901202302123

image-20220901202457208

image-20220901202733052

索引分类

InnoDB索引分为两大类，

聚簇索引：将数据存储与索引放到了一块,找到索引也就找到了数据,索引即数据,索引结构的叶子节点保存了行数据,一个表只能包含一个聚集索引。

非聚簇索引：将数据与索引分开存储，非聚集索引的btree叶子节点中存储的是当行数据的PK（主键）

image-20220901203245513

image-20220901203656777

image-20220901204014839

回表查询:先找二级索引对应的主键值，再根据拿到聚集索引的主键值去找到对应的数据

索引语法

  • 创建索引
    CREATE [UNIQUE FULLTEXT] INDEX index_name ON table_name (index_col_name,...)
eg:
CREATE INDEX idx_title ON edu_subject (title)
ALTER TABLE edu_subject  ADD INDEX idx_title (title)
  • 查看索引
    SHOW INDEX FROM table_name
  • 删除索引
    DROP INDEX index_name ON table_name

SQL性能分析

查看db中sql执行频率
-- 因为7个下划线正好和select,update,-inserti和_delete的拼写长度一样 7个横线
show global status like 'Com_______'
慢查询日志

1:连接Liunx Mysql

mysql -u 你的数据库用户名 -p

show databases;

use 数据库名

show tables;

2:先查看当前数据库是否开启满查询日志

show variables like 'slow_query_log';

3:如果未开启,在配置文件中配置

慢查询日志记录了所有执行时间超过指定参数(long_query_.time,单位：秒，默认10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：

image-20220902163717386

Mysql对应的配置文件名Linux和Windows配置文件命名有差异

Linux: Mysql安装路径\my.ini

Windows: Mysql安装路径\etc\my.cnf

我的Mysql满查询日志路径:/www/server/data/mysql-slow.log

4:重启Mysql服务

systemctl restart mysqld

https://www.bilibili.com/video/BV1Kr4y1i7ru?p=76&spm_id_from=pageDriver&vd_source=737b91d72f14a59fdc115cd6b902c093 76集

性能分析-show profiles

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have profiling参数,能够看到

  • 当前MySQL是否支持profile操作(Yes|No)
select @@have_profiling;
  • 查看当前profile开关是否开启
select @@profiling;
  • 默认profiling是关闭的,可以通过set语句在session/global级别开启profiling(0|1)
set profiling=1;
  • 当我们开启了profiles之后,可以通过下语句来查看sql执行时间
show profiles;

image-20220904101419275

  • 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query 7;

tips: 7为show profiles; 中的 query_ID

  • 查看指定query id的SQL语句CPU的使用情况
show profile cpu for query query_id;

Explain执行计划(重要)

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

直接在我们sql前加上DESC/EXPLAIN

image-20220904102725976

Explain执行计划各字段含义:

image-20220904105313727

image-20220904105838216

我们在优化时,主要关注的几个字段 type/possible_keys/key/key_len/extra

image-20220904110040143

覆盖索引&回表查询

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少 select *。

explain 中 extra 字段含义：
using index condition：查找使用了索引，但是需要回表查询数据
using where; using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询

image-20220906171446100

面试题:一张表，有四个字段（id, username, password, status）如果有以下sql我们该如何建立索引？该如何进行才是最优方案:

select id, username, password from tb_user where username='itcast';

解：给username和password字段建立联合索引，则不需要回表查询，直接覆盖索引

索引使用

复合索引(联合索引)

例如联合索引（A,B,C），相当于三个索引 （A）|（A、B）|（A、B、C）

但复合索引在数据库操作期间所需的开销更小，可以代替多个单一索引。

当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度。

最左前缀法则
  • 如果索引了多列（联合索引），需要遵守最左匹配原则
  • 如果跳过了某一列，索引将部分失效（后面的字段索引失效）
-- 为profession, age, status创建联合索引
create index idx_user_pro_age_stat on tb_user(profession, age, status);
-- 遵从最左前缀法则
explain select * from tb_user where profession='软件工程' and  age=31 and status='0'
explain select * from tb_user where age=31 and status='0' and  profession='软件工程'
-- 遵从最左前缀法则
explain select * from tb_user where profession='软件工程' and  age=31
-- 遵从最左前缀法则
explain select * from tb_user where profession='软件工程'
-- 遵从最左前缀法则，但索引将部分失效，跳过了age
explain select * from tb_user where profession='软件工程' and status='0'
-- 不遵从最左前缀法则，查询没有从索引的最左列开始，跳过了profession， age
explain select * from tb_user where status='0'

索引失效情况

  • 联合索引中，出现范围查询（大于> ,小于<）时，范围查询右侧的列索引失效
-- 范围查询右侧的列索引失效
explain select * from tb_user where profession='软件工程' and age>22 and status='0';
-- 使用>=来规避索引失效问题
explain select * from tb_user where profession='软件工程' and age>=22 and status='0';
  • 在索引列上进行运算操作，索引将失效。
explain select * from tb_user where substring(phone, 10, 2) = '15';
  • 字符串类型字段使用时，不加引号，索引将失效。如
-- 此处phone的查询值没有加引号
explain select * from tb_user where phone = 17799990015;
  • 模糊查询中，如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效
-- 前后都有 % 也会失效
explain select * from tb_user where profession like '%工程';
  • 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
explain select from tb_user user where id =10 or age 23;
explain select from tb_user where phone ='17799990017'or age 23;
  • 如果 MySQL 评估使用索引比全表更慢，则不使用索引

SQL提示

是优化数据库的一个常用手段,我们可以通过use,ignore,force来指定是否使用索引

-- 例如，使用索引：
explain select * from tb_user use index(idx_user_pro) where profession="软件工程";
-- 不使用哪个索引：
explain select * from tb_user ignore index(idx_user_pro) where profession="软件工程";
-- 必须使用哪个索引：
explain select * from tb_user force index(idx_user_pro) where profession="软件工程";

use 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改

ignore是不使用索引

force就是无论如何都强制使用该索引。

前缀索引

当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只降字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：create index idx_xxxx on table_name(columnn(n));
前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
求选择性公式：

select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email, 1, 5)) / count(*) from tb_user;

单列索引与联合索引

单列索引：即一个索引只包含单个列
联合索引：即一个索引包含了多个列
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

单列索引情况：

-- 对phone创建了非聚集索引，所以这句只会用到phone索引字段
explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';

注意事项

  • 多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询

    image-20220906211628992

索引设计原则

索引设计原则

  1. 针对于数据量较大，且查询比较频繁的表建立索引
  2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
  4. 如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率
  7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

三:SQL优化

插入数据

insert优化

批量插入（一次插入的数据不建议超过1000条）

insert into t_user values(1, 'A'),(2, 'B')(3,'C');

手动提交事务

start transaction;
insert into tb_test values(1,Tom'),(2,'Cat'),(3,Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,Jerry');
commit;

主键顺序插入

主键顺序插入		1, 2, 3, 4, 5, 6, 7
主键乱序插入		7, 3, 2, 5, 1, 4, 6

大批量插入

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入。

image-20220907160034705

#客户端连接服务端时，加上参数-loca-infile
mysql --local-infile -u root -p
#查看数据库
show databases
#使用数据库
use xxx
#查看数据库是否开启了本地加载文件(1为开启,0为未开启)
select @@local_infile;
#设置全局参数local infile;为1，开启从本地加载文件导入数据的开关
set global local_infile 1;
#执行load指令将准备好的数据，加载到表结构中
#每个字段间使用,分隔,每行数据使用\n分隔
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

主键优化

数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（Index organized table, IOT）

image-20220907161834548

order by优化

  1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，

    所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
    在这里插入图片描述

  2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高

如果order by字段全部使用升序排序或者降序排序，则都会走索引(默认索引都是升序的)

但是如果一个字段升序排序，另一个字段降序排序，则不会走索引，explain的extra信息显示的是Using index, Using filesort。
如果要优化掉Using filesort，则需要另外再创建一个索引，如：

create index idx_user_age_phone_ad on tb_user(age asc, phone desc);
此时使用select id, age, phone from tb_user order by age asc, phone desc;会全部走索引

总结：
● 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
● 尽量使用覆盖索引，避免使用select *, 如select id, age, phone from tb_user order by age asc, phone desc;其中要查询的id, age, phone不就是索引字段的值嘛，因此不需要额外查询，但是如果是select *，其他没有在索引中涉及到的，就需要回表查询

image-20220907165620909

  • 如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）
  • show variables like 'sort_buffer_size',如果大数据量排序超过了这个size，就会在涉及到磁盘文件，在磁盘文件中排序，性能就非常低

image-20220907170212330

group by优化

● 在分组操作时，可以通过索引来提高效率
● 分组操作时，索引的使用也是满足最左前缀法则的
如索引为idx_user_pro_age_stat，则句式可以是select ... where profession order by age，这样也符合最左前缀法则

image-20220907171436446

limit优化

image-20220907173015505

-- 此语句耗时较长 1.381
select * from tb_sku limit 1000000, 10;
-- 通过覆盖索引加快速度，直接通过主键索引进行排序及查询
select id from tb_sku order by id limit 1000000, 10;
-- 下面的语句会报错，因为 MySQL 不支持 in 里面使用 limit，但是可以换种方式
-- select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
-- 通过将子查询数据变成一张表来进行连表查询即可实现与覆盖索引实现的同等效果 0.78
select s.* from tb_sku as s, (select id from tb_sku order by id limit 1000000, 10) as a where s.id = a.id;

count优化

EXPLAIN select count(*) from emp 返回emp总记录数(不会忽略空值)

EXPLAIN select count(idcard) from emp(会忽略null值)

EXPLAIN select count(1) from emp

各种count用法的性能：

image-20220909093459660

按效率排序：count(字段) < count(主键) < count(1) <= count(*)，所以尽量使用 count(*)

update优化

如以下两条语句：

这句由于id有主键索引，所以只会锁这一行；

update student set no = '123' where id = 1;，
update student set no = '123' where name = 'Jack';

这句由于name没有索引，所以会把整张表都锁住进行数据更新，优化方式是给name字段添加索引

Sql优化小结

image-20220909122916348

视图

视图(Viw)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条5QL查询语句上。

创建视图

create or replaece view testvw as select * from emp

查询视图

-- 查看创建视图语句：SHOW CREATE VIEW视图名称，
show create view testvw
-- 查看视图数据：SELECT*FROM视图名称(也可以给视图添加限定条件)
select * from testvw where

select * from testvw where workno <10

修改视图

alter view testvw  as select id,workno,name,gender from emp
create or replaece view testvw as select * from emp

删除视图

drop view if EXISTS testvw

检查选项(cascaded)

检查选项(local)

存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

创建存储过程

创建
create procedure p1()
begin
select count(*) from emp;

end;

调用

call p1();

查看

select * from information_schema.ROUTINES where ROUTINE_SCHEMA='bzovo-db';

删除

drop PROCEDURE if EXISTS p1;

image-20220912150629227

命令行中默认的结束字符是 ;号 直接执行会报错

可以通过delimiter 来手动指定SQL结束符

create procedure p1()
begin
select count(*) from emp;

end$$

变量

查看变量

image-20220912151310429

会话级别只会影响到当前会话

查看系统变量(不指定默认为会话变量)

show variables;
--模糊查询相关变量
show session variables like 'auto%';
--查看指定变量
select @@autocommit
select @@SESSION.autocommit

设置变量

set session autocommit=1;
set global  autocommit=1;

image-20220912153134602

局部变量

--变量：局部变量(stu_count)
--声明--declare
--赋值--into  

create procedure p2()
begin
    declare stu_count int default 0;
    select count(*)into stu_count from student;
    select stu_count;
end;

call p2();

IF条件判断

image-20220913200126103

例子:image-20220913200537327

create procedure p3()
begin
declare score int default 58;
declare result varchar(10);

if score >85 then
set result:='优秀';
elseif score >60 then
set result:='及格';
else
set result:='不及格';
end if;
select result;
end;

call p3();

in/out/inout参数

例子一:

image-20220913201422339

image-20220913202117826

create procedure p4(in score int,out result varchar(10))
begin

if score >85 then
	set result:='优秀';
elseif score >60 then
	set result:='及格';
else
	set result:='不及格';
end if;

end;
-- 将输出的结果赋值给@result
call p4(8,@result);

select @result
例子二

image-20220913202335262

create procedure p5(inout score double)
begin
  set score :=score*0.5;
end;
--先给@score赋值
set @score=198;
call p5(@score);
select @score;

case

image-20220913203133095

例子:

image-20220913203148078

create procedure p6(in month int)
begin
declare result varchar(20);
	case 
	when month>=1 and month<=3 then set result:='第一季度';
	when month>=4 and month<=6 then set result:='第二季度';
	when month>=7 and month<=9 then set result:='第三季度';
	when month>=10 and month<=12 then set result:='第四季度';
	
	else
	set result:='非法参数';
	end case;
	select CONCAT('您输入的月份为:',month,',所属的季度为:',result);
end;

call p6(8);

while循环

image-20220913204620836

例子:

image-20220913204839099

create procedure p7(in num int)
begin
  declare total int DEFAULT 0;
	
	while(num>0) do
		set total :=total+num;
		set num :=num-1;
	end while;
		select total;
end;

call p7(100);

repeat循环

image-20220913205521661

create procedure p8(in num int)
begin
declare total int DEFAULT 0;
REPEAT
	set total :=total+num;
	set num :=num-1;
UNTIL num<=0
END REPEAT;
end;

call P7(100);

loop循环

cursor游标

handler条件处理程序

触发器

锁

运维篇

日志

主从复制

分库分表

读写分离

SQL练习

1:https://leetcode.com/problems/big-countries/description/

SELECT name,
    population,
    area
FROM
    World 
WHERE
    area > 3000000
    OR population > 25000000;    
#mysql中或用or

2：https://leetcode.com/problems/not-boring-movies/description/

SELECT
    *
FROM
    cinema
WHERE
    id % 2 = 1
    AND description != 'boring'
ORDER BY
    rating DESC;
#奇数  %2 = 1   DESC降序   字符串用''表示

*group by 把select的结果集分成几个组合。*

*having 根据特定条件检索组合后的结果集。*

having和where的使用知识
where与having同时使用时，执行顺序时where->having。

where不能使用聚合函数
因为聚合函数是从确定的结果集中整列数据进行计算的 ，而where子句则是对数据行进行过滤的。所以相矛盾不能使用。

火星包18163112
关注
MySQL安装配置教程（超级详细、保姆级）
Soloversion的博客
03-26 42万+
一、 下载MySQL Mysql官网下载地址https://downloads.mysql.com/archives/installer/ 1. 选择想要安装的版本，本篇文章选择的是5.7.31版本，下面的那个文件，点击Download下载 二、 安装MySQL 1. 选择设置类型 双击运行mysql-installer-community-5.7.31.0.msi 这里选择是自定义安装，所以直接选择“Custom”，点击“Next” “Developer Default”是开发者默认
Java连接MySQL数据库的超级详细步骤（Windows）
热门推荐
firstlt0217的博客
05-19 11万+
1. 数据准备 1. 下载JDK 下载Java开发工具包JDK，下载地址：https://www.oracle.com/java/technologies/javase-jdk14-downloads.html，下载待用。 2. 下载Mysql Java 连接 MySQL 需要驱动包，下载地址为：http://dev.mysql.com/downloads/connector/j/。 解压后得到 jar 库文件，待用，最终会将红框文件导入相应项目文件中。 3. 下载MySQL 下载地址：https:/
MySQL自带的加密AES_ENCRYPT()解密AES_DECRYPT()
weixin_46037781的博客
07-07 5139
mysql的AES_ENCRYPT()和AES_DECRYPT() 查询解密、模糊查询 参考： MySql在使用AES_ENCRYPT或者ENCODE加密时，中文乱码问题（数据库正常，在java代码和页面中变成问号乱码）
MySQL 使用 AES 加解密，设置 mode、IV
锄禾日当午，啥都不靠谱；闲来没事做，不如写博客。
12-06 1483
参考来源：https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_aes-encrypt。由于使用 CBC 模式加密的需要有一个初始偏移量参数（IV 值），所以需要做例如一下处理才能做加解密。
mysql使用Aes加解密
Wind___Drift的博客
05-13 982
【代码】mysql使用Aes加解密。
mysql中AES_ENCRYPT、AES_DNCRYPT及CONVERT的用法
weixin_30387799的博客
11-26 110
这两天在弄公司的服务端项目的时候，发现mysql比较实用的3个函数，在这里给大家分享一下。 1、AES_ENCRYPT函数，这个函数的使用场景是对一些安全性要求比较高的数据使用AES加密，语法为： AES_ENCRYPT(str,key)，其中第一个参数表示要加密的数据，第二参数是对应的key。 2、AES_DECRYPT函数，这个函数是与AES_DECRYPT对应的一个函数，用于解密经过A...
mysql jdbc连接驱动jar包（适用于mysql5.7版本，亲测可用）
08-17
MySQL JDBC连接驱动是Java应用程序与MySQL数据库之间通信的桥梁，使得开发者可以使用Java语言来执行SQL语句，处理数据库中的数据。本驱动适用于MySQL 5.7版本，并且经过实际测试，确认可用。这个jar包是完全免费的，...
mysql-connector-java-8.0.27
12-24
MySQL Connector/J 8.0.27 是MySQL数据库与Java应用程序之间的重要桥梁，它是一个实现了JDBC（Java Database Connectivity）接口的驱动程序，允许Java开发者在应用中无缝地访问MySQL数据库。MySQL Connector/J使得...
mysql驱动jar 文件适用MySQL5.7
01-18
MySQL驱动jar文件是Java应用程序与MySQL数据库之间通信的关键组件，主要功能是提供Java Database Connectivity (JDBC) API，使得Java开发者能够通过编写Java代码来执行SQL语句，从而操作MySQL数据库。标题提到的...
【MySQL技术专题】「实战开发系列」一同探索一下数据库的加解密函数开发实战指南之AES系列
世界上并没有完美的程序，但是我们并不因此而沮丧，因为写程序就是一个不断追求完美的过程。
08-09 2780
AES_DECRYPT(crypt_str,key_str[,init_vector])，其中crypt_str为已加密的二进制串，key_str为秘钥，其中init_vector根据选择不同的块加密模式为可选项。AES_ENCRYPT(str,key_str[,init_vector])，其中str为待加密字符串，key_str为秘钥，其中init_vector根据选择不同的块加密模式为可选项。AES_ENCRYPT(str,key_str)，其中str为待加密字符串，key_str为秘钥。
mysql基于AES_ENCRYPT&&AES_DECRYPT实现密码的加密与解密
weixin_54401017的博客
09-04 1318
执行语句结果 加密过后的字符串是一串很奇怪的字符。尝试使用上面加密过后的字符解密。结果 并未成功的解密。
MySQL自带的AES加密解密比较常用的使用方法
Counter-Strike大牛
12-13 1万+
接手的老项目中，有些SQL使用了MySQL自带的AES加密解密，为了方便操作数据，研究了一下使用方法，在这里记录一下。 加密 update user_password set password = HEX(AES_ENCRYPT("{加密数据}", "{密钥}")) where user_id = 1; 解密 select AES_DECRYPT(UNHEX(password), "{密钥}") from user_password where u_id = 1; 创作很累，点赞免费。 自愿打赏，不为
mysql AES加密的使用
shining的专栏
06-21 820
建表如下： CREATE TABLE `aes_demo` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_bin DEFAULT NULL, `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT ...
MySQL 在线解密
牛肉胡辣汤
04-25 2565
通过使用MySQL提供的加密函数和自定义解密函数，我们可以在数据库中进行在线解密操作。在完成准备工作和遵循注意事项的前提下，你可以安全地解密加密的数据，并进行后续的操作或者数据分析。通过以上代码，我们实现了在MySQL中解密加密后的密码，并将解密后的密码一并查询出来。在实际应用中，我们可以将解密后的密码与用户输入的密码进行比对，以完成用户登录验证的过程。4. 使用解密函数: 现在，你可以在查询中使用解密函数来解密加密的数据。在此查询中，我们调用了之前创建的解密函数，并将解密后的数据返回为。
MySQL使用的AES加密/解密方法
netuser1937的博客
10-25 4985
MySQL使用的AES加密/解密方法
Python基础之数据加密（收藏这一篇就够了！）
weixin_47139649的博客
10-24 1万+
Python基础之数据加密 文章目录Python基础之数据加密1. 准备工作2. MD5加密2.1 加密规则2.2 应用场景2.3 代码实例2.4 sha1 加密3. Base64加密3.1 加密规则：3.2 应用场景：3.3 代码实例4. AES5. RSA5.1 加密原理： 1. 准备工作 重点：首先确保我们待加密的内容是Bytes格式！ 我们所说的加密方式，都是对二进制编码的格式进行加密的。所以，在加密之前，我们要首先确保我们待加密的内容是Bytes格式，否则会报错。 那么我们如何得到Bytes格式呢
【实战】mysql加密函数AES_ENCRYPT无缝迁移到磐维2.0的加密函数MY_ENCRYPT_AES128
最新发布
qyq88888的专栏
07-08 413
mysql加密函数AES_ENCRYPT无缝迁移到磐维2.0的加密函数MY_ENCRYPT_AES128
MYSQL 敏感数据加密后进行模糊查询
北地程序星域※巨猿星
10-09 964
对于一些敏感数据，比如用户的手机号、身份证号、银行卡号之类进行加密处理，是一些系统的常用处理方式。但是这保证了数据的安全之外又诞生了另外一个问题，就是搜索这些信息的时候，模糊查询变得困难。方法来处理数据的加密和解密。keywords 不能是。
mysql数据库
weixin_44559622的博客
03-01 139
1、怎样优化sql语句？ https://www.cnblogs.com/wxgblogs/p/5857116.html 1.Innerjoin和左连接，右连接，子查询 A. inner join内连接也叫等值连接是，left/rightjoin是外连接。 SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id; SE...
MySQL 5.1.58 安装教程
2. 创建用户组和用户：`groupadd mysql` 创建名为mysql的用户组，`useradd -g mysql mysql -s /sbin/nologin` 添加一个名为mysql的用户并将其添加到mysql组，且指定不登录 shell。 3. 解压MySQL源码包：`tar xzf ...
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值