mysql学习笔记_mysql 把某一行的某个列的数据赋值给另一行

MYSQL概要

查看已有数据库：
    show databases；
创建数据库：
    create database 数据库名字 default charset utf8 collate utf8_general_ci;
删除数据库：
    drop database 数据库名字；
进入数据库（进入文件夹）
    use 数据库名字；
查看文件夹下所有的数据表（文件）:
    show tables；
1
2
3
4
5
6
7
8
9
10

数据表的管理（文件）

创建表：
    create table user(
    user_id  int  not null primary keyauto_increment,                       
    username varchar(16)  not null,  
    password char(8)      not null,      
    sexy     char(4)      not null
    )default charset=utf8;
查看表详细信息：desc 表名称;
查看表：select * from 表名称；
删除表：
    drop table 表名称;
插入数据：
    insert into 表名（字段名1，字段名2） values （1000，18）；
    insert into 表名（字段名1，字段名2） values （1000，18），（133，55）；
1
2
3
4
5
6
7
8
9
10
11
12
13
14

常用数据类型：

整型：
tinyint：
有符号，取值范围-128~127【默认】
无符号，0~255【 tinyint unsigned】
int：
有符号：-2147483648~2147483647
无符号：…
bigint

小数：
float
double
decimal：
准确的小数值，m是数字总个数（负号不算），d是小数点后个数，m最大值65，d最大30
如：
create table 表名称{
salary decimal（8，2） 不超过8个数字，小数点后2个
}default charset=utf8；

字符串：
char（m）：查询速度快 m最大255个字符
定长字符串：char（11） 固定11个字符串进行存储，哪怕没有11个，也会存储11个
varchar（m）：节省空间 m最大是65535字节
变长字符串：varchar（11）
text（m）： m最大 65535个字符
mediumtext
longtext

时间：
datetime：
YYYY-MM-DD HH:MM:SS
date：
YYYY-MM-DD

数据行操作

新增数据：
    insert into 表名（字段名1，字段名2） values （值1，值2），（值1，值2）；
删除数据：
    delete from 表名；
    delete from 表名 where 条件；id in（1，3）等于id=1 or id=3；
修改数据：
    update 表名 set 列=值，列=值 where 条件； 
查询数据：
    select * from 表名称；
    select 字段名，字段名 from 表名称  where 条件；
1
2
3
4
5
6
7
8
9
10

数据库范式

1. 第一范式：有主键，具有原子性，字段不可分割
2. 第二范式：前提是满足第一范式，表中非主键列对主键列是完全依赖，要求每一个表只描述一件事情。也就是说在一个数据库表中，一个表只能保存一种数据，不可用把多种数据保存在同一个数据库表中。表的每个字段都必须与主键具有直接关系，而非间接关系
3. 第三范式：前提是满足第二范式，表与表之间的关系通过主外键进行关联，总而言之，第三范式就是属性不依赖于其它非主属性(一张表的非主关键字来自另一张表的主关键字或者本张表的主关键词）。表中的字段与主键不能有传递依赖，必须是直接依赖，不满足第三范式会出现大量的数据冗余

SELECT语法

查询执行顺序

这才是完整的SELECT查询
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;
1
2
3
4
5
6
7
8
9
10

1. FROM 和 JOINs
FROM 或 JOIN会第一个执行，确定一个整体的数据范围. 如果要JOIN不同表，可能会生成一个临时Table来用于 下面的过程。总之第一步可以简单理解为确定一个数据源表（含临时表)
2. WHERE
我们确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选col属性 只能来自FROM圈定的表. AS别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式
3. GROUP BY
如果你用了 GROUP BY 分组，那GROUP BY 将对之前的数据进行分组，统计等，并将是结果集缩小为分组数.这意味着 其他的数据在分组后丢弃.
4. HAVING
如果你用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选。AS别名也不能在这个阶段使用.
5. SELECT
确定结果之后，SELECT用来对结果col简单筛选或计算，决定输出什么数据.
6. DISTINCT
如果数据行有重复DISTINCT 将负责排重.
7. ORDER BY
在结果集确定的情况下，ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了。此时可以用AS别名.
LIMIT / OFFSET
最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据.

特殊

• distinct
  DISTINCT 查询不重复的内容
  select distinct 字段 from 表;

• FULL JOIN、LEFT JOIN、RIGHT JOIN

    - FULL JOIN：将两个表数据1-1连接，保留A或B的原有行，如果某一行在另一个表不存在，会用 NULL来填充结果数据。
  
    - LEFT OUTER JOIN, RIGHT OUTER JOIN, 或 FULL OUTER JOIN, 和 LEFT JOIN, RIGHT JOIN, and FULL JOIN 等价.
  
    - 所有在用这三个JOIN时，你需要单独处理 NULL.你计算一些行的平均值的时候，如果是0会参与计算导致平均值差错，是NULL则不会参与计算。可以用IS NULL和 IS NOT NULL 来选在某个字段是否等于 NULL.
  1
  2
  3
  4
  5

• AS 和 表达式筛选
  AS不仅用在表达式别名上，普通的属性列甚至是表（table）都可以取一个别名

    1. as
  
    SELECT column AS better_column_name, …
    FROM a_long_widgets_table_name AS mywidgets
    INNER JOIN widget_sales
    ON mywidgets.id = widget_sales.widget_id;
    
    2. 表达式
  
    
    SELECT  particle_speed / 2.0 AS half_particle_speed (对结果做了一个除2）
    FROM physics_data
    WHERE ABS(particle_position) * 10.0 >500
            （条件要求这个属性绝对值乘以10大于500）;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

• 分组统计：GROUP BY

    GROUP BY 数据分组语法可以按某个col_name对数据进行分组，如：GROUP BY Year指对数据按年份分组， 相同年份的分到一个组里。如果把统计函数和GROUP BY结合，那统计结果就是对分组内的数据统计了.
  
    GROUP BY 分组结果的数据条数，就是分组数量，比如：GROUP BY Year，全部数据里有几年，就返回几条数据， 不管是否应用了统计函数.
  1
  2
  3

• HAVING
  HAVING 和 WHERE 语法一样，只不过作用的结果集不一样. 在我们例子数据表数据量小的情况下可能感觉 HAVING没有什么用，但当你的数据量成千上万属性又很多时也许能帮上大忙 .
  小贴士?
  如果你不用GROUP BY语法, 简单的WHERE就够用了.

• mysql中的when语句

    SELECT role ,count(role),(case when Building is null then 0 else 1 end )  bn 
    from Employees group by Role,bn;
  1
  2

必须要记住的

条件：数字(where)

当查找条件col是数字
select * from table where col = 1;

条件：文本(where)

当查找条件col是文本
select * from table where col like ‘%jin’;

排序(rows)

需要对结果rows排序和筛选部分rows
select * from table where col > 1 order by col asc limit 2 offset 2

join:连表(table)

当查找的数据在多张关联table里
select * from table1 left join table2 on table1.id = table2.id where col > 1

算式(select / where)

当需要对select的col 或 where条件的col 经过一定计算后才能使用
select * , col*2 from table where col/2 > 1

统计（select）

对查找的rows需要按col分组统计的情况
select count(*),avg(col),col from table where col > 1 group by col

DML（针对表中数据的操作）

增

插入值必须按照顺序、数量、类型、约束条件，否则语法不通过

• 如果表名后不加列，表示插入所有列记录

    INSERT INTO table_name[(column[,column...])] VALUES (value[,value...])
  1

• INSERT INTO 子查询

删

删除表中记录,DELETE后可加FROM，也可省略，删除记录，应从子表先开始删除，否则报错，不能删除被其他表引用的数据，删除还应注意联合外键的情况（Oracle可以不加from）

• 删除表中所有数据

    DELETE FROM 表名;
  1

• 删除满足条件的数据

    DELETE FROM 表名 WHERE 条件;
  1

改

更新表中记录,不加WHERE子句，表示这字段所有记录的内容均被修改

• 改所有

    UPDATE TABLE_NAME SET 字段=’‘
  1

• 将此字段满足条件的记录修改

    UPDATE TABLE_NAME SET 字段=’‘WHERE 条件
  1

事务

指一系列DML操作的集合，不同窗口进行DML操作，不会直接作用到数据库服务，相当于SVN中的本地操作，只有事务结束才会作用到数据库服务。

• 开始：从执行第一条DML语句开始。
• 结束：
  - 正常结束
  commit操作（结束事务的同事，相当于另一个事务的开始）
  - 异常结束
  关闭sql、命令窗口、关闭plsql客户端、异常结束进程
• 优点：不同窗口进行DML操作，不会直接作用到数据库服务，相当于SVN中的本地操作，只有事务结束才会作用到数据库服务
  - 保存回滚点
  savepoint 命名回滚点的名称
  - 回滚到指定位置
  rollback to 回滚点的名称
  注意:回滚只能从后向前，不能从前向后，后指的是后设置的后滚点，前指的是前一个设置的回滚点
• 特点：
  1. 原子性（不可分性）
  指所包含的操作要么全做，要么全不做
  2.永久性
  执行结束永久有效
  3.隔离性
  事务结束前对事务的操作只对当前操作窗口可见
  4.一致性
  事务结束后，数据保持一致

TRUNCATE 与 DELETE区别

    - TRUNCATE不能回滚，DELETE可以回滚
    - 在删除大量数据时，TRUNCATE比DELETE快
    - TRUNCATE可以释放表空间，DELETE不可以释放表空间(表空间为逻辑空间)
    - TRUNCATE操作对象只能是表，DELETE可以是表、视图、同义词
    = TRUNCATE是DDL语句，DELETE是DML语句
1
2
3
4
5

DDL(针对表的)

表命令规则

表命名规则：以字母开头、长度限制为30字符、内容组成为：A-Z、a-z、0-9、_、$和#、不能为oracle保留字、同一用户下不能重名、不能和用户定义的其他对象重名

字段的数据类型

--VARCHAR2(size)        内存空间大小是随着存储值的长度而变化，其长度需要小于等于size的值，varchar数据类型会自动转换成varchar2类型
--CHAR(size)       内存空间大小为size的值，是固定的，不会随着存储的值长度变化而变化，不建议使用
--NUMBER(size)       数值有效位数为size的值 
--NUMBER(p、s)       p为数值的有效数字位，s为小数位数
--DATE         日期类型
--CLOB        可变的内存空间，存储大数据字符串，最高可存储2GB
--BLOB        存储较大的二进制数据，如图片等
1
2
3
4
5
6
7

约束constraints

--PRIMARY KEY    主键约束 PK
--FOREIGN KEY    外键约束  FK
--UNIQUE 	   唯一约束   表名_列名_UK
--NOT NULL 	   非空约束   NN
--CHECK 	   检查约束   表名_列名_CK
--不命名约束，oracle会自动产生特定的约束，名字以sys_为前缀
CONSTRAINT自定义约束(行业规范，必须自定义)
	优点：通过约束的名称了解此约束是作用在哪张表、哪个字段，以及是什么类型的约束
--约束可以在表级别和列级别上定义
	--列级别：约束类型紧跟在列后，约束和列间没有逗号分隔
	--表级别：约束和列间通过逗号分隔，NOT NULL不能加在表级别上


自定义约束名
	表名_字段名_约束缩写  约束名
外键约束
	外键加在表级别上
		constraint  自定义的约束名 foreign key(字段名) references 主表名(主表的主键)
	外键加在列级别上
		constraint  自定义的约束名 references 主表名(主表的主键)
联合主键
	需要两个字段遵循  联合唯一 、联合非空
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

MYSQL数据类型概述

• 数据类型

• 数据类型属性

• 整数
  
  • 可选属性
  1. M表示显示宽度，注意：1.显示宽度与类型可存储的范围无关。2.后面结合使用ZEROFILL,表示显示宽度不足的前面用0补齐
• 浮点
  
  • M不能超出宽度,D超出宽度会四舍五入
  • 浮点数是不准确的，所以避免使用 “=”来判断两个数是否相等。所以精确的数据类型使用decimal
• 定点数
  
• 日期和时间类型
  
  • 使用CURRENT_TIME()或者NOW()插入当前系统的时间
• 文本字符串类型
  
• 二进制字符串类型
  
  • TEXT和BLOB的使用注意事项
    • BLOB和TEXT在删除表时会留下很大的“空洞”，以后填入这些空洞的记录可能长度不同，为了提升性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理
    • 对大文本字段进行模糊查询时，mysql提供了前缀索引，但是仍然要在不必要的时候避免检索大型的BLOB或TEXT值。
    • 把BLOB和TEXT列分离到单独的表中

具体操作

# 库操作（增、删、改、查）
create database 库名
drop  database 库名
alter 库名
show databases
1
2
3
4
5

# 表操作
create table ...(
        username varchar;
        password varchar;
);
1
2
3
4
5

mysql架构篇

数据存放位置

windows：C:\ProgramData\Mysql\Mysql Server8.0\Data
linux: /var/lib/mysql/
mac os: /usr/local/mysql/data

linux下的操作

docker部署mysql

# 点击进入阿里云进行镜像加速后
# 再进行如下操作
docker search mysql
docker pull mysql
docker images
1
2
3
4
5

1. 启动快捷方式
docker run --name mysql5.7 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
2. (配置容器MySQL数据、配置、日志挂载宿主机目录)
# 宿主机创建数据存放目录映射到容器
mkdir /usr/local/docker_data/mysql/data
# 宿主机创建配置文件目录映射到容器 
mkdir /usr/local/docker_data/mysql/conf # (建议在此目录创建my.cnf文件并进行相关MySQL配置)
# 宿主机创建日志目录映射到容器
mkdir /usr/local/docker_data/mysql/logs

docker run --name mysql5.7 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d  -v /usr/local/docker_data/mysql/data:/var/lib/mysql -v /usr/local/docker_data/mysql/conf:/etc/mysql/ -v /usr/local/docker_data/mysql/logs:/var/log/mysql mysql:5.7
1
2
3
4
5
6
7
8
9
10
11

docker exec -it mysqlserver bash
mysql -uroot -p
1
2

开启远程访问权限

use mysql;
select host,user from user;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges; 

# 下载ifconfig
apt update
apt install net-tools
1
2
3
4
5
6
7
8

ubuntu部署mysql服务器

#更新apt-get库
sudo apt-get update

#安装mysql
sudo apt-get install mysql-server (-y)

#初始化配置mysql
sudo mysql_secure_installation

sudo mysql -u root -p

use mysql;

select host,user,plugin from user;
# 配置远程连接
update user set host='%' , plugin='mysql_native_password' where user='root';

# 授予root用户全部权限
grant all privileges on *.* to 'root'@'%' with grant option;

flush privileges;

vim /etc/mysql/mysql.conf.d/mysqld.cnf
#注释掉#bind-address =127.0.01，或者将其修改为0.0.0.0

# 查看mysql的服务状态
systemctl status mysql
netstat -ntlp (可看运行的端口号)
netstat -tap | grep mysql
# 查看版本号
sudo mysql --version
mysql -V
# 如果没有设置密码，查看密码,password
sudo cat /etc/mysql/debian.cnf

# 配置远程连接
sudo mysql -u root -P
use mysql;
select host,user,plugin from user;
update user set host='%' , plugin='mysql_native_password' where user='root';
flush privileges;
## 修改配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf
将bind-address= 127.0.0.1注释，或修改为0.0.0.0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

字符集

# 查看默认使用的字符集
show variables like 'character%';
# mysql5.7默认是latin，mysql8默认是utf8mb4
1
2
3

• 字符集的与比较规则（了解）

  • utf8mb3:阉割过的utf8字符集，只使用1-3个字节表示字符
  • utf8mb4：正宗的utf8字符集，使用1-4个字节表示字符
  • 在mysql中utf8mb3是utf8的别名，正常使用mb3就够了，但是一些emoji表情，就使用的是mb4.
  • 比较规则show charset;
    

  #查看服务器的字符集和比较规则
  show variables like '%_server';
  # 查看数据库的字符集和比较规则
  show variables like '%_database';
  # 查看具体数据库的字符集
  show create database ....;
  # 修改具体数据库的字符集
  alter database ... default character set 'utf8' collate 'utf8_general_ci'
  1
  2
  3
  4
  5
  6
  7
  8

  

基础-多表查询

多表关系

一对多：在多的一方设置外键，关联一的一方的主键
多对多：建立第三张表中间表，中间表至少包含两个外键，分别关联两个表的主键
一对一：第二张表相当于第一张表的拓展表，第二张表的外键有unique属性。

多表查询

1. 内连接
   隐式，显式（inner join）
2. 外连接
   左外：left join on条件
   右外：right join on条件
3. 自连接
   一定要给表取别名
4. 联合查询
   注意：
   1.对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
   2.union all 会将全部的数据直接合并在一起，union会对合并之后的数据去重

sql 语句
union（union all）
sql语句
1
2
3

5. 子查询
   标量子查询：=、<>、>、>=、<、<=
   列子查询：in、not in、any=some、all
   行子查询：=、<>、in、not in（多条件查询用元组一一对应）
   表子查询：in

基础-事务

是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

查看/设置事务提交方式：SELECT @@autocommit;
SET @@autocommit=0;
开启事务：START TRANSACTION 或 BEGIN
提交事务： COMMIT
回滚事务： ROLLBACK
1
2
3
4
5

事务的四大特性（ACID）

1. 原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
2. 一致性（consistency）：事务完成时，必须使所有的数据都保持一致。
3. 隔离性（isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
4. 持久性（durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

1.脏读：一个事务读到另外一个事务还没有提交的数据
2.不可重复读：一个事务先后读取到同一条记录，但两次读取的数据不同，称之为不可重复读
3.幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。

事务隔离级别

--查看事务隔离级别
select @@transaction_isolation;

--设置事务隔离级别
set [session|global] transaction isolation level [read uncommitted | read committed | repeatable read | serializable]
1
2
3
4
5

进阶- 存储引擎

mysql体系结构图

存储引擎

存储引擎就是存储数据、建立索引】更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

- 查看表存储引擎（默认是innoDB）
show create table table_name;

- 查看数据库所有支持的引擎
show engines;
1
2
3
4
5

存储引擎特点

• InnoDB

  • 介绍：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在mysql5.5之后，InnoDB是默认的mysql存储引擎。
  • 特点：DML操作遵循ACID模型，支持事务；行级锁，提高并发访问性能；支持外键FOREIGN KEY约束，保证数据的完整性和正确性。
  • 文件：xxx_ibd（二进制文件，想要查看，cmd->ibd2sdi account.ibd），xxx代表的是表名，InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。参数innodb_file_per_table,on表示每张表都有一个引擎。 使用show variables like 'innodb_file_per_table';查看
  • 

• MyISAM

  • 介绍：mysql早期的默认存储引擎
  • 特点：不支持事务，不支持外键；支持表锁，不支持行锁；访问速度快
  • 文件：xxx.sdi存储表结构信息；xxx.MYD存储数据；xxx.MYI存储索引

• Memory

  • 介绍：Memory引擎的表数据存储在内存中，由于收到硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。
  • 特点：内存存放、hash索引（默认）
  • 文件：xxx.sdi存储表结构信息

• Archive引擎：用于数据存档（不可修改）

进阶- 索引

索引概述

索引（index）是帮助mysql高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引结构

mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构

• B+tree树
  所有的元素都会出现在叶子节点。
  mysql索引数据结构对经典的b+tree进行了优化。在原b+tree的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的b+tree，提高区间访问的性能。
  

• hash
  哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
  如果两个（或多个）键值，映射到了一个相同的槽位上，他们就产生了hash冲突（碰撞），可以采用链表来解决

  • 特点：1.hash索引只能用于对等比较（=，in），不支持范围查询（between、>,<,…）。2.无法利用索引完成排序操作。3.查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引
  • 存储引擎支持：在mysql中，支持hash索引的是memory引擎，而innodb中具有自适应hash功能，hash索引是存储引擎根据b+tree索引在指定条件下自动建的。
    

面试问题

为什么InnoDB存储引擎选择使用B+tree索引结构

1. 相对于二叉树，层级更少，搜索效率高
2. 对于B-tree，其无论是叶子节点还是非叶子节点，都会保存数据，但是因为一页的大小是固定的（16k），这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
3. 相对hash索引，b+tree支持范围匹配即排序操作

索引分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
分类|含义|特点
聚集索引（clustered index）|将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据|必须有，而且只有一个
二级索引（secondary）|将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键|可以存在多个

所以根据主键字段进行查询的效率，大于根据一般字段进行查询的效率（一般查询，先查询到主键，再进行回表查询）

索引语法

• 创建索引（索引名规范：idx_表名_字段名）
  create [UNIQUE | FULLTEXT] index_name on table_name (index_col_name,…);
• 查看索引
  show index from table_name;
• 删除索引
  drop index index_name on table_name;

sql性能分析

查看语句的执行频次

查看当前数据库的insert、update、delete、select的访问频次。
show [session | global] status like ‘com_______’;

慢查询日志

慢查询日志记录了所有执行时间超过指定指定参数（long_query_time,单位：秒，默认10秒）的所有sql语句的日志。mysql的慢查询日志默认没有开启，需要在mysql的配置文件（/etc/mysql/my.cnf）中配置如下信息(slow_query_log = 1
long_query_time =2 ,但是我失败了，所以我用了下面的方法)

# 查看慢查询日志是否开启，且日志位置
show variables like '%slow_query_log%'; 
show variables like '%long_query_time%';

# 开启慢查询日志
set global slow_query_log=ON;

# 设置慢查询日志阈值时间
SET GLOBAL long_query_time = 2;
每次修改之后，都需要重新开一个新的会话查询，因为旧的会话查询出来的始终是打开时的值，不会直接同步。

# 设置未使用index的数据也添加到慢查询日志
# 先查看是否打开
show variables like 'log_queries_not_using_indexes';
# 再进行设置
set global log_queries_not_using_indexes=on;
注意：log_queries_not_using_indexes参数要跟参数log_throttle_queries_not_using_indexes配合使用，（og_throttle_queries_not_using_indexes：该参数决定每分钟记录未使用索引的SQL的数量上限，因为未使用索引的SQL可能会非常多，导致慢日志空间增长飞快。）
# 打开记录管理的语句
show variables like '%log_slow_admin_statements%';
set global log_slow_admin_statements=1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

MySQL5.7慢查询日志时间与系统时间差8小时原因详解
查看日志的时间（显示为UTC）
show [global] variables like ‘log_timestamps’;
设置为系统时间
SET GLOBAL log_timestamps = SYSTEM;

show profile

show profilen能够在做sql优化时帮助我们了解时间都耗费到哪里去了。

# 查看当前数据库是否支持profile操作
select @@hava_profiling;(但是我执行报错了)
# 查看是否开启profiling
select @@profiling;
# 默认profiling是关闭的，可以通过set语句在session/global级别开启profiling
set profiling = 1; 
1
2
3
4
5
6

执行一系列的业务sql的操作，然后通过如下指令查看指令的执行耗时：(好像不支持了)

# 查看每一条sql的耗时基本情况
show profiles;
# 查看指定query_id的sql语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的sql语句cpu的使用情况
show profile cpu for query_id;
1
2
3
4
5
6

explain执行计划

explain 或者desc 命令获取mysql如果执行select语句的信息，包括select语句执行过程中表如何连接和连接的顺序。
语法：直接在select 语句之前加上关键字explain/desc
explain执行计划各字段含义：

• id
  select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行。）
• select_type（意义不大）
  表示select的类型，常见的取值有simple（简单表，既不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、union（union中的第二个或者后面的查询语句）、subquery（select/where之后包含了子查询）等
• type
  表示连接类型，性能由好到差的连接类型为null、system、const（根据主键或唯一索引访问）、eq_ref、ref、range、index、all
• possible_key
  显示可能应用在这张表上的索引，一个或多个
• KEY
  实际使用的索引，如果为null，则没有使用索引
• key_len
  表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精度的前提下，长度越短越好
• rows
  mysql认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
• filtered
  表示返回结果的行数占需要读取行数的百分比，filtered的值越大越好。

索引使用规则

验证索引效率

#在未建立索引之前，执行如下sql语句，查看sql的耗时,耗时时间为10秒
select * from tb_sku where sn='1232';
# 针对sn字段创建索引
create index idx_name on tb_sku(sn);
# 然后再进行查询，时间变为0.01sec。
1
2
3
4
5

索引失效情况

• 最左前缀法则
  如果索引了多列（联合索引）,要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。
• 范围查询
  联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效。（但是不包含>=,<=，这样的范围查询右侧列索引不失效）
• 索引列运算
  不要在索引列上进行运算操作，索引将失效
• 字符串不加引号
  字符串类型索引字段使用时，不加引号，索引将失效。
• 模糊查询
  如果仅仅时尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
• or连接的条件
  用or分割开的条件，如果or的只有一边的列有索引，那么涉及的索引都不会被用到。（解决办法就是给没有索引的列添加索引）
• 数据分布影响
  如果mysql评估使用索引比全表（不使用索引）更慢，则不使用索引。
• sql提示
  sql提示，是优化数据库的一个重要手段，简单来说，就是在sql语句中加入一些人为的提示来达到优化操作的目的

use index:建议用此索引
ignore index：忽略
force index：强制
explain select * from tb_user force_index(idx_user_id) where id='1';
1
2
3
4

覆盖索引和回表查询

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引（二级索引）中已经全部能够找到），减少select 。
例如：extra字段中-using index condition/null表示查找使用了索引，但是需要回表查询数据。
using where；using index表示查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。
面试题：一张表，有四个字段（id，username，password，status），由于数据量大，需要对以下sql语句进行优化，该如何进行才是最优方案:
** select id,username,password from tb_user where username=‘sober’;*
对username和password建立一个联合索引，避免回表操作

前缀索引

当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘io，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法：create index idx_xxx on table_name(column(n));
前缀长度：
可以根据索引的选择性来决定，而选择性时指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

求选择性的数值
select count(distinct name)/count(*) from sale_client;
select count(distinct substring(name,1,4)) / count(*) from sale_client;
1
2
3

单列索引与联合索引

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

索引设计原则

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

进阶- sql优化

插入数据（insert优化）

• 批量插入
• 手动提交事务
• 主键顺序插入
• 大批量插入数据时
  如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用mysql数据库提供的load指令进行插入。操作如下：

# 客户端连接服务端时，加上参数--local-infile
mysql --local-infile -u root -P
# 查看local_infile 的参数
select @@local_infile;
# 设置全局参数local_infile 为 1，开启从本地加载文件导入数据的开关
set global local_infile =1 ;
# 查看数据有几行
wc -l xxx
# 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/xxxx' into table 'table_name' fields teminated by ',' lines teminated by '\n';
1
2
3
4
5
6
7
8
9
10

主键顺序插入性能高于乱序插入

主键优化

• 数据组织方式
  在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）
• 页分裂（主键乱序插入的情况下）
  也可以为空，也可以填充一半，也可以填充100%，每个页包含了2-n行数据（如果一行数据过大，会行溢出），根据主键排列。
  • 
  • 
• 页合并
  当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到merge_threshold(默认为页的50%)，InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
  merge_threshold:合并页的阈值，可以自己设置，在创建表或者创建索引时指定
• 主键设计原则
  1.满足业务需求的情况下，尽量降低主键的长度
  2.插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键
  3.尽量不要使用uuid做主键或者是其他自然主键，如身份证号（会出现页分裂）
  4.业务操作时，避免对主键的修改。

order by优化

• Using filesort:
  通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫filesort排序
• Using index:
  通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

# 创建一个升序，一个降序的索引
create index idx_age_ptone_ad on tb_user(age asc ,phone desc);
1
2

1.根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
2.尽量使用覆盖索引
3.多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
4.如果不可避免的出现filesort，大数据排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k),(show variables like ‘sort_buffer_size’