1.mysql基础知识：
①mysql安装目录下bin文件夹中，mysqld.exe表示mysql服务器程序
②登录：cmd-->mysql -uroot -p123/或则：cmd-->mysql.exe -hlocalhost -p3306 -uroot -p123退出：exit/quit\q
③安装目录下：my.ini这个文件里面存放mysql配置文件
④mysql的数据库位于：C:\ProgramData\MySQL\MySQL Server 5.5\data，数据库中所有的表都存放在这里
⑤什么是SQL：Structured Query Language(结构化查询语言)
⑥SQL的作用：客户端使用SQL来操作服务器(客户端和服务器的对话语言)
⑦mysql服务器内部的四层对象：系统(DBMS)-->数据库(DB)-->数据表(Table)-->字段(Field)
⑧查看mysql字符编码： show variables like '%char%'; 解决乱码问题：set names gbk/utf8;
2.SQL语言分类：
DDL：(数据库定义语言)数据库或表的结构操作
DML：(数据库操作语言)对表的记录进行更新(增、删、改)
DQL：(数据库查询语言)对表的记录的查询
DCL：(数据库控制语言)对用户的创建以及授权
3.mysql数据库数据类型：
标准类型：
double:浮点型，如double(5,2)表示最多5位，其中必须有两位小数，即最大值为999.99
decimal:浮点型，在表单钱方面使用该类型，因为不会出现轻度缺失问题(这个类型对十进制运算比较精确)
char:固定长度字符串类型，char(255)，数据长度不足指定长度，会补足到指定长度
varchar:可变长度字符串类型，varchar(65535),zhangsan
blob:二进制(存储图片、视频文件)
data:日期类型，格式为：yyyy-MM-dd
time:时间类型，格式为：hh:mm:ss
timestamp:时间戳类型，格式为：yyyy-MM-dd hh:mm:ss
mysql独有类型：
text(clob)：字符串类型
4.DQL部分
1>.DQL语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表 --分组之前过滤，不满足WHERE条件不参与分组
GROUP BY
分组字段列表 --分组之后查询的一般为聚合函数和分组字段，查询其他字段无意义
HAVING
分组后条件列表 --分组之后过滤
ORDER BY
排序字段列表
LIMIT
分页参数
WHERE与HAVING的不同：
1>>.执行时机不同,WHERE是分组之前过滤，不满足WHERE条件不参与分组，HAVING是对分组之后的条件进行过滤
2>>.判断条件不同：WHERE不能对聚合函数进行判断，而HAVING可以
3>>.执行顺序不同：WHERE > GORUP BY >HAVING
2>.条件查询
IS NULL/IS NOT NULL
SELECT * FROM TEST WHERE GENDER IS NULL; -- IS NULL
SELECT * FROM TEST WHERE GENDER IS NOT NULL; -- IS NOT NULL
!=和<>
SELECT * FROM TEST WHERE ID != 1; -- !=
SELECT * FROM TEST WHERE ID <> 1; -- <>
&&、AND和 BETWEEN...AND...
SELECT * FROM TEST WHERE ID >= 1 && ID <= 5; -- &&
SELECT * FROM TEST WHERE ID >= 1 && NAME = '李华';
SELECT * FROM TEST WHERE ID >= 1 AND ID <= 5; -- AND
SELECT * FROM TEST WHERE ID >= 1 AND NAME = '李华'; -- AND
SELECT * FROM TEST WHERE ID BETWEEN 1 AND 5; -- 包含最小值和最大值
OR和||
SELECT * FROM TEST WHERE ID = 1 OR ID = 2;
SELECT * FROM TEST WHERE ID = 1 || ID = 2;
LIKE(_和%)
_：匹配一个字符
%：匹配多个/任意个字符
SELECT * FROM TEST WHERE NAME LIKE '张_'; -- 查询姓名为两个字,而且是以 张开头的人
SELECT * FROM TEST WHERE NAME LIKE '_三'; -- 查询姓名为两个字,而且是以 三结尾的人
SELECT * FROM TEST WHERE NAME LIKE '__'; -- 查询姓名为两个字的人
SELECT * FROM TEST WHERE NAME LIKE '%'; -- 查询所有人
SELECT * FROM TEST WHERE IDCARD LIKE '%X'; -- 查询身份证号以X结尾的人
3>.聚合函数（将一列数据作为整体，进行纵向计算）
SELECT AVG(COMM) FROM EMP;
SELECT MAX(COMM) FROM EMP;
SELECT MIN(COMM) FROM EMP;
SELECT COUNT(COMM) FROM EMP;
SELECT SUM(COMM) FROM EMP;
4>.分组查询
注意：分组之后查询的一般为聚合函数和分组字段，查询其他字段无意义
SELECT NAME, COUNT(*) FROM TEST GROUP BY GENDER; -- 根据GENDER分组，并统计每一组里面有多少人
SELECT NAME,GENDER ,COUNT(*) 总数 FROM TEST GROUP BY GENDER; -- 根据GENDER分组，并统计每一组里面有多少人
SELECT SAL,COUNT(*) FROM EMP WHERE SAL >=3000 GROUP BY JOB,SAL HAVING COUNT(*) >1; -- 薪水大于等于3000，根据JOB分组，获取到员工数量大于1的员工
5>.排序查询
ASC/DESC
SELECT * FROM EMP ORDER BY SAL ASC; --根据SAL升序排序
SELECT * FROM EMP ORDER BY SAL DESC; --根据SAL降序排序
SELECT * FROM EMP ORDER BY SAL ASC,COMM DESC; --根据SAL降序排序,如果SAL相同,则按照COMM降序排序
6>.子查询Oracle
1>>.标量子查询：子查询的结果返回的是单个值(数字、字符串、日期等)最简单的形式,称为标量子查询
查询在SALES部门的所有员工信息
SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');
2>>.列子查询:子查询返回的结果是一列或者多列
查询所有比SALES部门所有人的SAL都高的人员信息
SELECT *
FROM EMP
WHERE SAL >
ALL(SELECT SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES'));
3>>.行子查询：查询结果返回一行,可以是一列或者多列
查询SAL、MGR和WARD相同的所有人员信息
注意:可以使用两个值和子查询的结果对比,通常都是使用一个值
SELECT * FROM EMP WHERE (SAL,MGR) = (SELECT SAL,MGR FROM EMP WHERE ENAME = 'WARD');
4>>.表子查询：子查询返回的结果是多行多列,称为表子查询
--查询与WARD和SCOTT职位和薪资相同的员工信息
SELECT *
FROM EMP
WHERE (JOB, SAL) IN
(SELECT JOB, SAL FROM EMP WHERE ENAME IN ('WARD', 'SCOTT'));
5.DDL部分(数据定义语言：主要用来操作表)
1>.数据库相关
1>>.查询所有的数据库：SHOW DATABASES; ;
2>>.使用数据库:
USER 数据库名称;
USE MYSQL;
3>>.查询某一数据库中所有表：SHOW TABLES；
4>>.查询当前所在的数据库：SELECT DATABASE();
5>>.创建数据库：
基本语法:
create database 数据库名[库选项];
库选项：用来约束数据库，分为两个选项
字符集设定：charset/character set 具体字符集(数据存储的编码格式)
校对集设定：collate 具体校对集(数据比较的规则)
create database [if not exists] mydb1 [CHARSET=utf8];
6>>.删除数据库：
DROP DATABASE 数据库名称;
DROP DATABASE TEST;
7>>.修改数据库编码：
ALTER DATABASE 数据库名称 CHARACTER SET utf8;
ALTER DATABASE TEST CHARACTER SET utf8;
8>>.查询表信息(数据长度、索引长度、创建时间、更新时间等)
USE information_schema;
SELECT * FROM TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'USER';
2>.表相关
1>>.查询表结构：desc 表名 ;
2>>.查看建表语句：show create table 表名;
3>.删除表命令
1>>.DROP命令
删除表的内容和结构：drop table 表名;(注意:drop databases 数据库名称是删除数据库)
删除表的所有内容而不删除结构：delete from 表名;
删除表的部分内容而不删除结构：delete from 表名 where ....;
2>>.TRUNCATE命令删除表：删除表结构和表中数据，然后再创建一个和之前表结构一模一样的表
用途：可以用于清空表中数据，而不破坏表结构，truncate不支持回滚
truncate table 表名;
TRUNCATE TABLE TEST;
3>>.truncate与drop,delete的对比：
truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚。
truncate只能作用于表；delete，drop可作用于表、视图等。
truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；drop会删除表的结构及其所依赖的约束、索引等。
truncate会重置表的自增值；delete不会。
truncate不会激活与表有关的删除触发器；delete可以。
truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。
4>.创建表：(注意：comment 用于加注释)
create table [if not exists] 表名
( 列名 列类型 comment 'xxx',
列名 列类型 comment 'xxx',
列名 列类型 comment 'xxx',
列名 列类型 comment 'xxx',
列名 列类型 comment 'xxx'
) comment 'xxx';
eg: create table test
( number char(11) comment '数字',
name varchar(50) comment '姓名',
age int comment '年龄',
gend varchar(10) comment '性别'
) comment '测试表';
5>.修改表(修改数据库/修改数据表)：
a.修改表本身：表名和选项
修改表名/重命名表：alter table 原表名 rename to 新表名
alter table emp rename to emp1;
注意：Oracle重命名表有两种方法，MySql只有一种方法
修改表选项：字符集 alter table 表名 charset = gbk;
alter table emp charset = gbk;
b.修改字段(修改列、修改表字段)：增加、修改、重命名、删除
默认的新增字段/增加字段/增加列：(不设置插入字段的位置，默认插入位置是最后一个字段后面)
语法一：在末尾添加字段
alter table 表名 add 列名 字段类型;
alter table test sid varchar(50);
语法二：在开头添加字段
alter table 表名 add 列名 字段类型 FIRST;
ALTER TABLE TEST ADD UNAME_FIRST VARCHAR(20) COMMENT '用户名_在开头添加字段' FIRST;
语法三：在中间添加字段
alter table 表名 add 列名 字段类型 AFTER UNAME_FIRST;
ALTER TABLE TEST ADD UNAME_MIDDLE VARCHAR(20) COMMENT '在中间添加字段' AFTER UNAME_FIRST;
注意：把新字段增加到指定位置是mysql独有
删除字段/删除字段/删除列：
ALTER TABLE 表名 DROP 列名;
ALTER TABLE TEST DROP NAME;
或：
ALTER TABLE 表名 DROP COLUMN 列名;
ALTER TABLE TEST DROP COLUMN NAME;
关于增加列和删除列的注意事项：
①操作一列add/drop 列名 数据类型，操作多列：add/drop (列名一 数据类型一,列名二 数据类型二...)
②add/drop后面可以加column也可以不加column
修改列字段类型和字段长度:
修改字段类型：
alter table 表名 modify 原来的列名 新的数据类型(新的长度);
alter table test modify name char(30);
修改字段长度：
alter table 表名 modify 原来的列名 原来的数据类型(新的长度);
alter table test modify name varchar(30);(修改数据长度，类型不变)
修改列名/字段名：(修改字段名的同时也可以修改字段类型/字段长度)
alter table 表名 change 原列名 新列名 列类型;(这个语法是mysql独有，oracle中并不适用)
alter table test change name name1 varchar(50);
6.DML部分(数据操作语言：对表的记录的操作，增、删、改)
①在数据库中插入数据：
给某几个字段赋值：
INSERT INTO 表名(字段1,字段2) VALUES（值1,值2）;
INSERT INTO TEST (ID,NAME,GENDER) VALUES (001,'张三','男');
给所有字段赋值：
INSERT INTO 表名 VALUES（值1，值2，值3）;
INSERT INTO TEST VALUES(004,'王五','男');
给某几个字段批量插入数据：
INSERT INTO 表名(字段1,字段2) VALUES（值1,值2），（值3,值4），（值5,值6）;
INSERT INTO TEST (ID,NAME) VALUES (01,'张三'),(02,'李四'),('03','王五') ; --注意：'03'是字符串之所以可以当成int类型数据插入进数据库,是因为插入之前先进行了隐式转换
给所有字段批量插入数据：
INSERT INTO 表名 VALUES (值1，值2，值3)，(值4，值5，值6)，(值7，值8，值9);
INSERT INTO TEST VALUES (0001,'张三','男'),(0002,'李四','男'),(0003,'王五','女');
②修改记录：
修改字段内容：
update 表名 set 字段名1=值1，字段名2=值2 where ...;
修改一个字段：
update test set number='001',age=19 ;
UPDATE TEST SET NAME = 'ZS' WHERE ID = '1';
修改多个字段：
UPDATE TEST SET NAME = 'ZS',GENDER = '男' WHERE ID = '2';
修改整张表的数据（不带where条件）：
UPDATE TEST SET NAME = 'ZS',GENDER = '男';
将某一个
③删除记录语句
删除某几条记录：
DELETE FROM 表名 WHERE 条件;
DELETE FROM TEST WHERE ID = '1';
DELETE FROM TEST WHERE ID IN (2,3); --关键词IN的使用
删除表中所有记录：
DELETE FROM 表名;
7.DCL部分
概述：DCL（数据库控制语言），用来管理数据库用户、控制数据库的访问权限
用户管理：管理哪些用户可以访问当前MYSQL数据库
权限控制：控制用户访问当前数据库后有有哪些操作权限
建议：一个项目创建一个用户，这个用户只能对这个数据库有权限，无法操作其他数据库
1>.查询可以访问数据库的所有用户信息
USE MYSQL;
SELECT * FROM USER;
2>.创建用户
create user '用户名'@'ip地址/主机名' identified by '密码';
用户名只能在指定的 ip地址/主机名 上登录
create user '用户名'@'%' identified by '密码';
用户名可以在任意的ip地址上登录
CREATE USER 'test'@'%' IDENTIFIED BY '1234567'; --创建test/1234567用户
3>.修改用户密码
ALTER USER '用户名'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '密码';
ALTER USER 'test'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '123456'; --修改test用户密码
4>.解决低版本Navicate连接高版本Mysql报错(CLINET DOSE NOT SUPPORT AUTHENTICATION PROTOCOL REQUEST BY SERVER ;CONSIDER UPGRADING MYSQL CLIENT)
修改root用户的host值为%,才能进行下一步更改加密方式；
use mysql;
select user,host from user;
update user set host ='%' where user = 'root';
更加加密方式:
ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;
4>.删除用户
DROP USER '用户名'@'ip地址/主机名';
DROP USER 'test'@'%';
5>.查询用户权限
SHOW GRANTS FOR '用户名'@'ip地址/主机名';
SHOW GRANTS FOR 'test'@'%';
6>.给用户授权
1>>.给用户分派在指定数据库上的权限
grant 权限1,权限2 ...(权限列表，所谓权限用ALL表示) on 数据库.* to '用户名'@ip'地址';
grant create ,alter,drop,insert,update,delete,select on mydb1.* to 'user'@'localhsot' ;
--给user用户分派在mydb1数据库上的create,alter,drop,insert,update,delete,select 权限;
2>>.给用户分配在指定数据库上的所有权限
grant all on 数据库.* to '用户名'@'ip地址'
GRANT ALL ON MYSQL.* TO 'test'@'%'; -- 给test用户授予操作MYSQL数据库的所有权限
7>.撤销授权
1>>.撤销用户在指定数据库上的权限
REVOKE 权限1,权限2 ...(权限列表，所谓权限用ALL表示) on 数据库.* FROM '用户名'@ip'地址';
REVOKE create ,alter,drop,insert,update,delete,select on mydb1.* FROM 'user'@'localhsot' ;
--撤销user用户在mydb1数据库上的create,alter,drop,insert,update,delete,select 权限;
2>>.撤销用户在指定数据库上的所有权限
grant all on 数据库.* FROM '用户名'@'ip地址'
GRANT ALL ON MYSQL.* FROM 'test'@'%'; -- 撤销test用户操作MYSQL数据库的所有权限
8.MYSQL函数
1>.字符串函数
1>>.CONCAT:拼接字符串
CONCAT(str1,str2…) ,注意：拼接时一般用单引号连接字符串
SELECT CONCAT('T','EST');
SELECT CONCAT('T','E','ST');
SELECT * FROM TEST WHERE NAME = CONCAT('张','三');
CONCAT(拼接字符,str1,str2…)
SELECT CONCAT_WS(',','First name','Second name','Last Name');
SELECT CONCAT_WS('-','First name','Second name','Last Name');
SELECT CONCAT_WS('*','First name','Second name','Last Name');
2>>.LOWER(str)：将字符串转为全部小写
SELECT LOWER('SsAa');
3>>.UPPER(str)：将字符串转为全部大写
SELECT UPPER('SsAa');
4>>.LPAD(str,len,填充符)：左填充
SELECT LPAD('00',5,'x'); --执行结果：xxx00
5>>.RPAD(str,len,填充符)：右填充
SELECT RPAD('00',5,'x'); --执行结果：00xxx
6>>.TRIM(str)：去掉字符串左右两端空格,不包含中间的空格
SELECT TRIM(' Hello World '); --执行结果：Hello World
7>>.SUBSTR(str,beginIndex,len)：截取指定长度的字符串(注意：Mysql和Oracle的索引都是从1开始)
SELECT SUBSTRING('Hello World',1,5); --执行结果：Hello
2>.数值函数
1>>.CEIL(x):向上取整
SELECT CEIL(1.5); --2
2>>.FLOOR(x):向下取整
SELECT FLOOR(1.5); --1
3>>.MOD(x,y)：返回x/y的模
SELECT MOD(5,2); --1
SELECT MOD(5,3); --2
4>>.RAND():返回0~1内的随机数
SELECT RAND(); --0.053181587767956395
SELECT RAND(); --0.77559073434732
5>>.ROUND(x,y):求参数x的四舍五入的值，保留y位小数点
SELECT ROUND(2.34,2); --2.34
SELECT ROUND(2.346,2); --2.35
综合案例：随机生成一个六位数的验证码
SELECT ROUND(RAND()*1000000,0); --错误示例，因为RAND()可能生成0.01xxx或0.001xxx,那么计算出的数只有5位或4位
SELECT LPAD(ROUND(RAND()*1000000,0),6,'0'); --正确示例，解决方式：左边补零或者右边补零
3>.日期函数
1>>.CURDATE():返回当前日期
SELECT CURDATE(); --2022-03-31
2>>.CURTIME():返回当前时间
SELECT CURTIME(); --18:31:14
3>>.NOW():返回当前日期和时间
SELECT NOW(); --2022-03-31 18:31:50
4>>.YEAR(date):获取指定date的年份
SELECT YEAR(NOW()); --2022
5>>.MONTH(date):获取指定date的月份
SELECT MONTH(NOW()); --3
6>>.DAY(date):获取指定date的日期
SELECT DAY(NOW()); --31
7>>.DATE_ADD(date,INTERVAL expr type):返回一个日期/时间值加上一个时间间隔expr后的时间值
SELECT DATE_ADD(NOW(),INTERVAL 365 DAY); --2023-03-31 18:35:37
SELECT DATE_ADD(NOW(),INTERVAL 12 MONTH); --2023-03-31 18:35:37
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR); --2023-03-31 18:35:37
8>>.DATEDITT(date1,date2):返回起始时间date1和结束时间date2之间的天数
SELECT DATEDIFF('2020-5-1','2020-4-1'); --30
SELECT DATEDIFF('2020-1-1','2020-1-3'); --2
综合案例：查询所有员工的入职天数，并按照入职天数倒序排列
SELECT DATEDIFF(CURDATE(),HIREDATE) AS HIREDATES FROM EMP ORDER BY HIREDATES DESC;
4>.流程函数
1>>.IF(value,t,f)：如果value为true,则返回t,否则返回f
SELECT IF(true,'OK','False'); --OK
SELECT IF(false,'OK','False'); --False
SELECT IF(1,'OK','False'); --Ok
SELECT IF(0,'OK','False'); --False
SELECT IF(10,'OK','False'); --Ok
SELECT IF(' ','OK','False'); --False
2>>.IFNULL(value1,value2):如果value1不为空则返回value1,否则返回value2
SELECT IFNULL(' ','DEFAULT'); --空字符串
SELECT IFNULL('1','DEFAULT'); --1
SELECT IFNULL(null,'DEFAULT'); --DEFAULT
3>>.CASE WHEN[value1] THEN [res1] ...ELSE[default] END
如果value1位true,则返回res1，...否则返回default默认值
SELECT
EMPNO,
ENAME,
SAL,
CASE WHEN SAL >3000 THEN '高工资'
WHEN SAL >1000 AND SAL <2000 THEN '中工资'
ELSE '低工资' END AS 工资等级
FROM EMP;
4>>.CASE[expr] WHEN [value1] THEN [res1]...ELSE[default] END
如果expr的值等于value1，则返回res1,...否则返回default默认值
查询员工编号、员工姓名和职位，如果职位是CLERK，则显示店员，如果职位是SALESMAN，则显示售货员，
如果职位是MANAGER，则显示管理人员，否则显示其他职位
SELECT
EMPNO,
ENAME,
JOB,
CASE JOB WHEN 'CLERK' THEN '店员'
WHEN 'SALESMAN' THEN '售货员'
WHEN 'MANAGER' THEN '管理人员'
ELSE '其他职位'
END AS 中文职位名称
FROM EMP;
9.limit(mysql独有)
limit用来限定查询结果的起始行以及总行数
如：查询起始行为第5行，一共查询3行记录
select * from emp limit 4,3;
其中4表示从第5行开始查询(mysq同Java，以0开始计数)，其中3表示一共查询3行，即第5、6、7行的记录
mysql分页：
如，每页十条记录，要求查询第3页记录
(当前页-1) * 每页记录数
(3-1) * 10
select * from emp limit (3-1)*10,10;
所以：每页n条记录，查询第currentPage页的记录：
select * from 表名 limit (currentPage-1)*n,n;
10.Mysql编码问题(字符编码/编码格式/数据库编码)
查看数据库中所采用的字符编码：show variables like 'char%';
查看数据库服务器(mysql服务器)所支持的字符编码：show charset;或 show character set ;(显示：39 rows in set (0.00 sec)说明共有39种编码格式)
查看数据库服务器(mysql服务器)默认的对外处理的字符编码：show variables like 'char%';
Mysql客户端编码：character_set_client = utf8/gbk
character_set_client = utf8的意思是：无论客户端发送的是什么编码的数据，mysql都当成utf8的数据，也就是说
如果客户端发送的是gbk格式的数据，服务器当成utf8对待，必然会出现乱码问题。
处理这个原因引起的乱码问题有两种方式：
①让客户端发送utf8的数据(这个行不通的)
②把character_set_client修改为gbk，使用语句：character_set_client = gbk或者set names utf8/gbk
Mysql查询结果编码：character_set_results = utf8/gbk
处理这个原因引起的乱码问题有两种方式：
①让服务器发送可以识别的编码格式的数据(这个行不通的)
②character_set_results = gbk/utf-8或者set names utf8/gbk;
注意：1.set character_set_results = utf8/gbk 、set character_set_client = gbk、set names utf8/gbk;这三个语句的作用
域都是在一个session之内，也就是说，重新登录mysql后，刚才的设置就会失效
2.set names utf8/gbk= set character_set_client = gbk + set character_set_connections= utf8/gbk +set character_set_results = utf8/gbk
一个效果相当于三个的效果，可以用来快捷的设置字符集
为了使修改对所有窗口都有效果，可以修改总配置文件my.ini,在这里面进行设置，可以一劳永逸。
[client]
port=3306
[mysql]
default-character-set=utf8-->这个语句可以修改：client、results、connection的编码
查询修改结果：show variables like 'char%';
创建含有中文的字符的数据库：
set names gbk;
create database 中国 charset utf8;
11.DOS命令控制mysql服务器的启动与停止
cmd启动mysql服务：net start mysql;
cmd停止mysql服务：net stop mysql;
12.mysql的备份与恢复
①使用SQLyog对mysql的备份与恢复：
备份: 数据库-->sql脚本：右键数据库-->备份/导出-->以SQL转储文件备份数据库-->导出
恢复：sql脚本-->数据库
②mysql原生
备份：先退出mysql(cmd-->exit),然后
cmd-->mysqldump -uroot -p123 数据库名>导出路径
cmd-->mysqldump -uroot -p123 mydb1>d:/a.sql
恢复：方式一：登录时恢复
create database mydb1;(先创建要恢复的数据库,因为只能恢复数据库内容，不能恢复数据库)先退出mysql，然后
cmd-->mysql -uroot -p213 数据库名<导出路径
cmd-->mysql -uroot -p123 mydb1<d:/a.sql
注意：①必须在登录的时候进行
②这是备份数据库内容，并不是备份数据库
③回复时文件要为xxx.sql才能恢复，xxx.txt不能恢复
方式二：登录后恢复(source)：
cmd-->登录mysql-->create database mydb1;-->use mydb1;-->source d:/a.sql
注意：①使用source命令恢复数据库之前也要先创建数据库
②创建数据库后要先使用/进入数据库，才能执行source语句恢复数据库
13.mysql的约束
1>.主键约束：
特点：非空、唯一、被引用
2>.添加约束的三种方法：
方法一：列级约束
CREATE TABLE test2
(
empno INT PRIMARY KEY,
ename VARCHAR(50)
);
方法二：表级约束
CREATE TABLE test2
(
empno INT,
ename VARCHAR(50),
PRIMARY KEY(empno)
);
示例二：创建表成功后追加主键约束：
CREATE TABLE test2
(
empno INT PRIMARY KEY,
ename VARCHAR(50)
);
ALTER TABLE test3 ADD PRIMARY KEY(empno);
如果违反主键约束，会报错：Duplicate entry '1' for key 'PRIMARY'
3>.删除主键约束
alter table 表名 drop primary key ;
alter table test3 drop primary key ;
4>.主键自增长
create table test4
(
id int primary key AUTO_INCREMENT,
name varchar(20),
age int,
gender varchar(10)
);
注意：primary key 前面必须为int类型数据才能自增长
测试主键自增长：
第一次插入记录：insert into test4 values(1111,'zs',28,'male');
第二次插入记录：insert into test4 values(null,'zs',28,'male');
第三次插入记录：insert into test4 values(null,'zs',28,'male');
查询结果为：
| id | name | age | gender |
+------+------+------+--------+
| 1111 | zs | 28 | male |
| 1112 | zs | 28 | male |
+------+------+------+--------+
可以发现主键已经自增长
注意：1>>.若是先执行删除表操作：delete from test4;
再执行插入操作：insert into test4 values(null,'zs',28,'male');
查询结果为：
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 1113 | zs | 28 | male |
| 1114 | zs | 28 | male |
+------+------+------+--------+
可以发现：id是从1113开始计数(上一次是从id=1112处结束的)，也就是说删除并不会影响主键自增长，类似于序列
2>>.若是执行插入语句:
insert into test4 values(1,'zs',28,'male');
insert into test4 values(99999,'zs',28,'male');
查询结果为：
+-------+------+------+--------+
| id | name | age | gender |
+-------+------+------+--------+
| 1 | zs | 28 | male |
| 1113 | zs | 28 | male |
| 1114 | zs | 28 | male |
| 99999 | zs | 28 | male |
+-------+------+------+--------+
可以发现：mysql会自动按照id值的升序排序记录，所以，1在1113前面，99999在1114后面
5>.非空约束、唯一约束：
create table test4
(
id int primary key AUTO_INCREMENT,
name varchar(20) NOT NULL UNIQUE,
age int,
gender varchar(10)
);
注意：非空约束(NOT NULL)+唯一约束(UNIQUE) 不等于 主键约束(PRIMARY KEY)
原因：主键约束所有三个特点：非空、唯一、被引用
6.外键约束
特点：1>>.外键的值必须是另一张表主键的值(外键要引用主键)
2>>.外键可以重复
3>>.外键可以为空
4>>.一张表中可以有多个外键/一张表中的外键可以引用多张表的主键
5>>.外键不能引用不存在主键
create table emp
(
empno int primary key AUTO_INCREMENT,
name varchar(20),
dno int,
CONSTRAINT fk_emp_dept FORENGN KEY(dno) REFERENCES dept(deptno)
);
15.概念模型
对象模型：可以双向关联，而且引用的是对象，而不是一个主键。
关系模型：只能多方引用一方，而且引用的只是主键，而不是一整行记录。
16.数据库一对一关系
一对一关系时一张是表，一张是从表
先创建一个主键自增长的表hasband：
create table hasband
(
hid int primary key auto_increment,
hname varchar(50)
);
给刚才建的表中插入数据：
insert into hasband(hid,hname) values(null,'zs');
insert into hasband(hid,hname) values(null,'ls');
insert into hasband(hid,hname) values(null,'ww');
再创建一个主键自增长并且带有外键约束的表hasband：
create table wife
(
wid int primary key auto_increment,
wname varchar(50),
constraint fk_wife_hasband foreign key(wid) references hasband(hid)
);
wid要求：唯一、非空、引用hid
再给wife表插入数据：
insert into wife values(1，'小红');
insert into wife values(1，'效率');
17.数据库多对多关系(需要使用中间表)
多对多关系时两张表都是主表
先创建两张没有外键的表：
create table student
(
sid int primary key auto_increment,
sname varchar(50)
);
create table teacher
(
tid int primary key auto_increment,
tname varchar(50)
);
创建一张表，里面有两个外键
create table stu_tea
(
sid int ,
tid int ,
constraint fk_studet foreign key(sid) references student(sid),
constraint fk__teacher foreign key(tid) references teacher(tid)
);
再给student表插入数据：
insert into student values(null,'aa');
insert into student values(null,'bb');
insert into student values(null,'cc');
再给teacher表插入数据：
insert into teacher values(null,'AA');
insert into teacher values(null,'BB');
insert into teacher values(null,'CC');
再给stu_tea表插入数据：
insert into stu_tea values(1,1);
insert into stu_tea values(2,1);
insert into stu_tea values(3,1);
insert into stu_tea values(1,2);
insert into stu_tea values(2,2);
insert into stu_tea values(3,2);
18.多表查询，分类：集合运算、连接查询、子查询
集合运算:
创建aa表:
create table aa(id int,name varchar(20));
insert into aa values(1,'aa');
insert into aa values(1,'aa');
insert into aa values(1,'aa');
创建bb表：
create table bb(id int,name varchar(20));
insert into aa values(2,'bb');
insert into aa values(2,'bb');
insert into aa values(2,'bb');
测试集合运算：
①union all不去除重复列：
select * from aa
union all
select * from bb;
②union去除所有重复的列
select * from aa
union
select * from bb;
连接查询
1>>.内连接
*方言：select * from 表1,别名1,表2,别名2, where 别名1.xx=别名2.xx;
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno;
*标准：select * from 表1,别名1 inner join 表2 别名2 on 别名1.xx=别名2.xx;
select e.ename,e.sal,d.dname from emp e inner join dept d on e.deptno=d.deptno;
*省略inner
select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno;
*自然：select * from 表1,别名1 natural join 表2 别名2;
select e.ename,e.sal,d.dname from emp e natural join dept d;
特点：自动查找两张表中相同的列
*隐式内连接
SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO;
2>>.外连接
*左外连接:查询左表数据以及两张表交集部分数据，所以左表数据会全部出现
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
*右外连接:查询右表数据以及两张表交集部分数据，所以右表数据会全部出现
SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
*全外连接(MySql不支持全外连接)
3>>.自连接：当前表与自身的连接查询,必须使用别名
查询员工信息及其领导信息
SELECT E1.ENAME,E2.ENAME FROM EMP E1 INNER JOIN EMP E2 ON E1.MGR = E2.EMPNO;
查询员工信息及其领导信息,即使该员工没有领导,也要查询出来(要完全包含员工信息,所以是左外连接)
SELECT E1.ENAME,E2.ENAME FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR = E2.EMPNO;
19.查询数据库时使用模糊查询，如：查询名字以information开头的数据库
show databases like 'information%';
20.查看创建数据库的语句：
例如：查看mydb1的创建语句：show create database mydb1;
查看创建数据库中表的语句：
例如，查看mydb1数据库中创建emp表的语句：show create table emp;
21.修改数据库的字符集：
例如：修改mydb1的字符集/字符编码：alter database mydb1 charset gbk;
22.校对集问题：
校对集：数据的比较方式
校对集有三种格式：
_bin:binary:二进制比较，取出二进制位，一位一位的比较
MySql查询练习：
分析方法：
列：xx
表：xx
条件：xx
1.列出所有员工的姓名及其直接上级的姓名
列：e.ename,m.ename
表：emp e,emp m
条件：e.mgr=m.empno
select e.empno, e.ename,m.empno ,m.ename from emp e,emp m where e.mgr=m.empno order by 1 asc;
2.查询出受雇日期早于直接上级的所有员工的编号、姓名、部门名称
分析方法：
列：e.epmno、e.ename、d.dname
表：emp e、dept d、emp m
条件：e.hiredate<m.hiredate
可以写出如下语句：
select distinct e.empno,e.ename,d.dname
from emp e,dept d,emp m
where e.hiredate<m.hiredate;
发现上面语句行不通，换一种思路继续分析，
先不查部门名称，只查部门编号:
列：e.empno,e.ename,e.deptno
表：emp e,emp m,dept d
条件：e.mgr = m.empno ,e.hirdate<m.hiredate
可以写出如下语句：
select e.hiredate ,m.hiredate ,e.ename,m.ename
from emp e,emp m,dept d
where e.mgr = m.empno and e.hiredate<m.hiredate and e.deptno=d.deptno;
3.列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级
select
from
where
Mysql存储引擎
1>.概念
存储数据、建立索引、更新、查询数据等技术的实现方式。存储引擎是基于表的，不是基于库的,所以存储引擎也可以被称为表类型。
注意：索引是在存储引擎中实现的
2>.查看某一张表的存储引擎
show create table 表名;查询出建表语句后查看ENGINE=后面的值
3>. 默认存储引擎
InnoDB
4>.创建表时指定存储引擎
CREATE TABLE 表名() ENGINE =INNODB;
5>.查看所有引擎
SHOW ENGINS;
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
6>.各种不同的引擎区别
1>>.InnoDB
介绍:
一种兼顾高可靠性和高性能的引擎,是Mysql(5.5版本之后)的默认引擎
特点:
DML遵循ACID模型,支持事务
行级锁,提高并发访问性能
支持外键FOREIGN KEY约束,保持数据的完整性和正确性
文件：
xxx.ibd,xxx代表的是表名,innoDB引擎的每张表都会对应这样一个表空间文件,存储该表的表结构(frm、sdi)、数据和索引，文件格式是二进制,不能直接打开查看
参数:innodb_file_per_table，决定是每一个表都有独立的表空间还是多张表共用一个表空间文件
查看innodb_file_per_table的值:ON代表每一张表都对应一个表空间文件,OFF代表多张表公用一个表空间文件
show variables like '%innodb_file_per_table%';
mysql> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set, 1 warning (0.00 sec)
查看test库中user表在磁盘中对应的表空间文件的位置:
C:\ProgramData\MySQL\MySQL Server 8.0\Data\test\user.ibd
查看ibd文件内容:ibd2sdi xx.ibd
C:\ProgramData\MySQL\MySQL Server 8.0\Data\test>ibd2sdi user.ibd
2>>.MyISAM
介绍:
MyISAM是Mysql早期的默认存储引擎
特点:
不支持事务,不支持外键
支持表锁,不支持行锁
访问速度快
文件：
xxx.sdi：存储表结构信息
xxx.MYD：存储数据
xxx.MYI：存储索引
补充:InnoDB和MyISAM引擎区别:事务、外键、行级锁
3>>.Memory
介绍:
Memory引擎的表数据是存储在内存中,由于受到硬件问题、或断电问题的英雄,只能将这些表作为临时表或缓存使用
特点：
内存存放
hash索引(默认)
文件：
xxx.sdi存储表结构信息
4>>.选择存储引擎
选择存储引擎时，应该根据应用系统的特点选择合适的引擎。对于复杂的应用系统,还以根据实际情况选择多种引擎进行组合。
InnoDB:是Mysql的默认引擎,支持事务、外键。如果应用对事务的完整性有较高的要求,在并发条件下要求数据的一致性,
数据操作除了查询之外,还包含很多的更新、删除操作、那么选择InndDB是比较合适的选择
MyISAM：如果应用是以读操作插入操作为主,是有很少的更新和删除操作,并且对事务完整性、并发性要求不是很高,那么可以选择这个存储引擎
如日志数据和评论数据。
Memory:将所有数据存储在内存中,访问速度快,通常用于临时表以及缓存。Memory表的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性
Mysql索引
1>.分类
按照数据结构类型分类:
B+Tree索引：
为什么不用二叉树?
二叉树顺序插入时,会形成一个链表,查询性能大大降低。大量数据情况下,层级较深 ,检索速度慢。
Hash索引：只支持根据key的hash值来进行精确查询,不支持范围查询,不支持排序,优点是查询效率高,不出现哈希碰撞的前
提下只需要一次检索就可以查询到需要的数据
R-Tree索引:主要用于地理空间数据
Full-Text:全文索引
按照主建用途分类:
主键索引、唯一索引、常规索引、全文索引
按照节点存储内容不同分类:
聚集索引:节点存储内容是ID和一整行的数据
二级索引(辅助索引)：节点存储内容是某几个字段的内容和ID
2>.创建索引:
语法：
CREATE [UNIQUE/FULLTEXT] IDNEX index_name ON table_name(index_colname,index_colname...);
单列索引:只为某一列创建的索引
组合索引/联合索引：为多列创建的索引
index_name命名规范：idx_字段名
eg:为emp表中ename,job,mgr这三个字段创建联合索引
CREATE INDEX IDX_ENAME_JOB_MGR ON EMP(ENAME,JOB,MGR);
为emp表中empno创建(单列索引):
CREATE INDEX IDX_EMPNO ON EMP(EMPNO);
3>.查看索引
语法：
SHOW INDEX FROM TABLE_NAME;
SHOW INDEX FROM USER;
4>.删除索引
语法：
DROP INDEX INDEX_NAME ON TABLE_NAME;
DROP INDEX IDX_NAME ON USER;
5>.回表查询
SELECT * FROM EMP WHERE ENAME = 'SCOTT';
回表查询过程:
1>.根据name的值在二级索引的B+Tree中查询到ID值
2>.根据得到的ID值在聚集索引中查询该行数据
二级索引叶子节点中存放的是字段的值和ID
聚集索引叶子节点中存放的是字段的ID和该ID对应的那一行数据
6>.索引使用法则(SQL优化：指的是建立索提高查询效率,但是索引会降低增删改的效率)
总的原则；建立索引并且避免索引失效，在使用了索引的前提下避免回表查询
创建测试用表:
#创建一个表,里面有四个字段ID,NAME,AGE,SCHOOL
CREATE TABLE USER(
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
AGE INT,
SCHOOL VARCHAR(20),
HOME VARCHAR(20)
);
#插入数据方式一:不使用主键自增长
INSERT INTO USER VALUES(1,'张三',20,'UFE','西安');
INSERT INTO USER VALUES(2,'张三',30,'UFE','北京');
INSERT INTO USER VALUES(3,'张三',40,'UFE','上海');
INSERT INTO USER VALUES(4,'李四',40,'XDU','上海');
#插入数据方式一:使用主键自增长
INSERT INTO USER(NAME,AGE,SCHOOL,HOME) VALUES('张三',20,'UFE','西安');
INSERT INTO USER(NAME,AGE,SCHOOL,HOME) VALUES('张三',30,'UFE','北京');
INSERT INTO USER(NAME,AGE,SCHOOL,HOME) VALUES('张三',40,'UFE','上海');
INSERT INTO USER(NAME,AGE,SCHOOL,HOME) VALUES('李四',40,'XDU','上海');
#为NAME、AGE、SCHOOL这三个字段创建索引
CREATE INDEX IDX_NAME_AGE_SCHOOL ON USER(NAME,AGE,SCHOOL);
#为NAME字段创建单独的索引
CREATE INDEX IDX_NAME ON USER(NAME);
测试表特别说明：
1>>.以下测试值针对于表中有ID,NAME,AGE,SCHOOL,HOME五个字段,仅为中间三个字段NAME,AGE,SCHOOL创建了索引的情况
如果为NAME,AGE,SCHOOL,HOME四个字段创建索引,则测试情况和下面测试结果有所不同
2>>.NAME索引长度为83,AGE索引长度为5,SCHOOL索引长度为83,索引总长度为83+5+83=171
索引使用特别说明:下面两种查询列顺序不同的SQL不会对索引产生影响
SELECT * FROM USER WHERE NAME = '张三' AND AGE = 30 AND SCHOOL = 'UFE';
SELECT * FROM USER WHERE AGE = 30 AND SCHOOL = 'UFE' AND NAME = '张三';
1>>.最左前缀法:查询返回结果中索引最左边的字段(NAME)必须出现在SQL中，在SQL中的位置无限制如:
SELECT * FROM USER WHERE NAME = '张三' AND AGE = 20 AND SCHOOL = 'UFE';和
SELECT * FROM USER WHERE AGE = 20 AND SCHOOL = 'UFE' AND NAME = '张三';
这两种写法索引都是可以生效的
如果索引了多列(使用了联合索引),最左前缀法则指的是查询从索引的最左列开始，且不跳过索引中的列，如果跳过了
某一列,则索引部分失效(后面的字段索引),即如果想要让索引生效,则最左边的列必须出现在查询字段中
#索引全部生效示例1:
SELECT * FROM USER WHERE NAME = '10' AND AGE = 20 AND SCHOOL = 'UFE';
#测试索引是否生效:索引全部生效,key字段的值不为NULL,ref有三个值，key_len=171
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 20 AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 20 AND SCHOOL = 'UFE';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | ref | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 171 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#索引部分生效示例1: (未使用SCHOOL字段索引)
SELECT * FROM USER WHERE NAME = '张三' AND AGE = 20;
#查看索引是否生效:索引部分生效,key字段的值不为NULL,ref有两个值,SCHOOL字段索引未生效，key_len=88
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 20;
#SQL执行结果
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 20;
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | ref | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 88 | const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#索引部分生效示例2:(跳过了AGE字段的索引,违反了最左前缀法则)
SELECT * FROM USER WHERE NAME = '张三' AND SCHOOL = 'UFE';
#查看索引是否生效:索引部分失效,key字段的值不为NULL,ref有一个值,AGE和SCHOOL字段的索引失效了,key_len=83
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND SCHOOL = 'UFE';
#SQL执行结果
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND SCHOOL = 'UFE';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | USER | NULL | ref | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 83 | const | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
#索引全部失效示例1:(跳过了NAME字段索引,违反了最左前缀法则,NAME、AGE和SCHOOL字段的索引失效了)
SELECT * FROM USER WHERE AGE = 20 AND SCHOOL = 'UFE';
#查看索引是否生效:索引全部失效,key字段的值为NULL,key_len=0,type=all代表全表扫描
EXPLAIN SELECT * FROM USER WHERE AGE = 20 AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE AGE = 20 AND SCHOOL = 'UFE';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#索引全部失效示例2:(跳过了NAME字段、AGE字段索引,违反了最左前缀法则,NAME、AGE和SCHOOL字段的索引失效了)
SELECT * FROM USER WHERE SCHOOL = 'UFE';
#查看索引是否生效:索引全部失效,key字段的值为NULL,key_len=0,type=all代表全表扫描
EXPLAIN SELECT * FROM USER WHERE SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE SCHOOL = 'UFE';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#索引全部失效示例3:(HOME字段未使用索引)
SELECT * FROM USER WHERE HOME = 'UFE';
#查看索引是否生效:索引全部失效,key字段的值为NULL,key_len=0
EXPLAIN SELECT * FROM USER WHERE HOME = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE HOME = 'UFE';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
2>>.范围查询
联合索引中,出现在范围查询(>,<)列右侧的列索引全部失效,
特别注意:
与范围查询条件在SQL中的位置无关,这个右侧指的是创建索引时创建顺序的右侧,即CREATE INDEX IDX_NAME_AGE_SCHOOL ON USER(NAME,AGE,SCHOOL);中AGE右侧的SCHOOL索引会失效,>=,<=范围查询右侧的列索引不会失效,如:
#key_len = 88 SQL写法1
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE > 30 AND SCHOOL = 'UFE';
#key_len = 88 SQL写法2
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND SCHOOL = 'UFE' AND AGE > 30;
#范围查询右侧的列索引全部失效示例1:
SELECT * FROM USER WHERE NAME = '张三' AND AGE > 30 AND SCHOOL = 'UFE';
#查看索引是否生效:索引部分生效,key_len=88，如果索引全部生效,则key_len=171
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE > 30 AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE > 30 AND SCHOOL = 'UFE';
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | USER | NULL | range | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 88 | NULL | 1 | 33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#范围查询右侧的列索引全部生效示例1:(在业务允许的情况下将SQL中的>改为>=,将<改为<=)
SELECT * FROM USER WHERE NAME = '张三' AND AGE >= 30 AND SCHOOL = 'UFE';
#查看索引是否生效:索引部分生效,key_len=171，如果索引全部生效,则key_len=171
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE >= 30 AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE >= 30 AND SCHOOL = 'UFE';
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | USER | NULL | range | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 171 | NULL | 2 | 33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
3>>.不要在索引列上进行运算或函数操作(等号左边),否则索引列将会失效,具体是在哪一列上进行了运算,则从那个列开始包括那个列上的索引就开始失效,这个失效的索引列与SQL的
书写顺序无关,只与索引创建顺序有关,索引的创建顺序为CREATE INDEX IDX_NAME_AGE_SCHOOL ON USER(NAME,AGE,SCHOOL);,即在AGE列上进行计算操作后,
SELECT * FROM USER WHERE NAME = '张三' AND SUBSTR(AGE,1,1) = 2 AND SCHOOL = 'UFE';
SELECT * FROM USER WHERE NAME = '张三' AND SCHOOL = 'UFE' AND SUBSTR(AGE,1,1) = 2;
上面两个SQL中的AGE,SCHOOL列的索引都会失效
1>>>.隐式类型转换有时也会导致索引失效
#原来的sql,age为INT类型,NAME为VARCHAR类型,索引生效
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 30 AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 30 AND SCHOOL = 'UFE';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | USER | NULL | ref | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 171 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#隐式类型转换:将VARCHAR类型AGE转为数据库需要的INT类型AGE,索引生效
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = '30' AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = '30' AND SCHOOL = 'UFE';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | USER | NULL | ref | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 171 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#隐式类型转换:将INT类型NAME转为数据库需要的VARCHAR类型NAME,索引会失效,由被操作的列决定索引全部失效还是部分失效
EXPLAIN SELECT * FROM USER WHERE NAME = 5 AND AGE = '30' AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = 5 AND AGE = '30' AND SCHOOL = 'UFE';
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | ALL | IDX_NAME_AGE_SCHOOL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
2>>>.模糊查询有时也会导致索引失效：尾部模糊匹配索引生效,头部模糊匹配索引会失效,由被操作的列决定索引全部失效还是部分失效
所以在大数据量查询的业务中要尽量避免使用头部模糊匹配
#尾部模糊匹配索引生效1
EXPLAIN SELECT * FROM USER WHERE NAME LIKE '张%' AND AGE = 30 AND SCHOOL AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME LIKE '张%' AND AGE = 30 AND SCHOOL AND SCHOOL = 'UFE';
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | USER | NULL | range | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 171 | NULL | 3 | 33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
#尾部模糊匹配索引生效2
EXPLAIN SELECT * FROM USER WHERE NAME LIKE '张_' AND AGE = 30 AND SCHOOL AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME LIKE '张_' AND AGE = 30 AND SCHOOL AND SCHOOL = 'UFE';
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | USER | NULL | range | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 171 | NULL | 3 | 33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
#头部模糊匹配索引会失效1,由被操作的列决定索引全部失效还是部分失效
EXPLAIN SELECT * FROM USER WHERE NAME LIKE '%三' AND AGE = 30 AND SCHOOL AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME LIKE '%三' AND AGE = 30 AND SCHOOL AND SCHOOL = 'UFE';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
#头部模糊匹配索引会失效2,由被操作的列决定索引全部失效还是部分失效
EXPLAIN SELECT * FROM USER WHERE NAME LIKE '_三' AND AGE = 30 AND SCHOOL AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME LIKE '_三' AND AGE = 30 AND SCHOOL AND SCHOOL = 'UFE';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
#头部尾部同时模糊匹配包括尾部模糊匹配1,所以索引会失效,由被操作的列决定索引全部失效还是部分失效
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 30 AND SCHOOL AND SCHOOL LIKE '%F%';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 30 AND SCHOOL AND SCHOOL LIKE '%F%';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | USER | NULL | ref | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 88 | const,const | 1 | 33.33 | Using index condition |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
#头部尾部同时模糊匹配包括尾部模糊匹配2,所以索引会失效,由被操作的列决定索引全部失效还是部分失效
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 30 AND SCHOOL AND SCHOOL LIKE '_F_';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 30 AND SCHOOL AND SCHOOL LIKE '_F_';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | USER | NULL | ref | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 88 | const,const | 1 | 33.33 | Using index condition |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
3>>>.在索引列上进行运算或函数操作
#在索引列上进行运算操作导致索引全部失效示例1:对NAME列进行了函数截取操作,导致NAME,AGE,SCHOOL列索引全部失效
SELECT * FROM USER WHERE SUBSTR(NAME,1,1) = '张';
#查看索引是否生效:索引全部失效
EXPLAIN SELECT * FROM USER WHERE SUBSTR(NAME,1,1) = '张';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE SUBSTR(NAME,1,1) = '张';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#在索引列上进行运算操作导致索引部分失效示例1:对SCHOOL列进行了函数截取操作,导致SCHOOL列索引失效
SELECT * FROM USER WHERE NAME = '张三' AND AGE = 20 AND SUBSTR(SCHOOL,1,1) = 'U';
#查看索引是否生效:索引部分失效
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 20 AND SUBSTR(SCHOOL,1,1) = 'U';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 20 AND SUBSTR(SCHOOL,1,1) = 'U';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | USER | NULL | ref | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 88 | const,const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#在索引列上进行运算操作导致索引部分失效示例2:(SQL中查询的字段按照创建索引的顺序进行排列):对AGE列进行了函数截取操作,导致AGE,SCHOOL列索引失效
SELECT * FROM USER WHERE NAME = '张三' AND SUBSTR(AGE,1,1) = 2 AND SCHOOL = 'UFE';
#查看索引是否生效:索引部分失效,key_len=83
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND SUBSTR(AGE,1,1) = 2 AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND SUBSTR(AGE,1,1) = 2 AND SCHOOL = 'UFE';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | USER | NULL | ref | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 83 | const | 3 | 33.33 | Using index condition |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#在索引列上进行运算操作导致索引部分失效示例3:(SQL中查询的字段没有按照创建索引的顺序进行排列):对AGE列进行了函数截取操作,导致AGE,SCHOOL列索引失效
SELECT * FROM USER WHERE NAME = '张三' AND SCHOOL = 'UFE' AND SUBSTR(AGE,1,1) = 2;
#查看索引是否生效:索引部分失效,key_len=83
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND SCHOOL = 'UFE' AND SUBSTR(AGE,1,1) = 2;
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND SCHOOL = 'UFE' AND SUBSTR(AGE,1,1) = 2;
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | USER | NULL | ref | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 83 | const | 3 | 33.33 | Using index condition |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#在索引列上进行运算操作导致索引部分失效示例4:对AGE列进行了加运算操作,导致AGE,SCHOOL列索引失效
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE+10 = 30 AND SCHOOL = 'UFE';
#查看索引是否生效:索引部分失效,在SCHOOL列上进行了运算,AGE列和SCHOOL列的索引失效，key_len=83
EXPLAIN EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE+10 = 30 AND SCHOOL = 'UFE';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE+10 = 30 AND SCHOOL = 'UFE';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | USER | NULL | ref | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 83 | const | 3 | 33.33 | Using index condition |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
4>>.OR连接的条件
用Or分割开的条件,如果OR前的条件中有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到,即Or会导致索引失效,
改进方式是使用UNION代替OR来完成查询
#使用OR索引依然生效1：OR连接两个主键索所在列
SELECT * FROM USER WHERE ID = 1 OR ID = 2;
#查看索引是否生效:索引生效
EXPLAIN SELECT * FROM USER WHERE ID = 1 OR ID = 2;
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE ID = 1 OR ID = 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#使用OR索引依然生效2：OR连接两个联合索引所在列
SELECT * FROM USER WHERE NAME = '张三' OR NAME = '李四';
#查看索引是否生效:索引生效
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' OR NAME = '李四';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' OR NAME = '李四';
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | USER | NULL | range | IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL | 83 | NULL | 4 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#使用OR索引依然生效3：OR连接主键索引所在列和联合索引首个字段所在列
SELECT * FROM USER WHERE ID = 1 OR NAME = '李四';
#查看索引是否生效:索引生效
EXPLAIN SELECT * FROM USER WHERE ID = 1 OR NAME = '李四';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE ID = 1 OR NAME = '李四';
+----+-------------+-------+------------+-------------+-----------------------------+-----------------------------+---------+------+------+----------+------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+-----------------------------+-----------------------------+---------+------+------+----------+------------------------------------------------------------+
| 1 | SIMPLE | USER | NULL | index_merge | PRIMARY,IDX_NAME_AGE_SCHOOL | IDX_NAME_AGE_SCHOOL,PRIMARY | 83,4 | NULL | 2 | 100.00 | Using sort_union(IDX_NAME_AGE_SCHOOL,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+-----------------------------+-----------------------------+---------+------+------+----------+------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
#OR导致索引全部失效1：OR连接主键索引所在列和非联合索引首个字段所在列，会全表扫描
SELECT * FROM USER WHERE ID = 1 OR AGE = 30;
#查看索引是否生效:索引全部失效,key的值为NULL
EXPLAIN SELECT * FROM USER WHERE ID = 1 OR AGE = 30;
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE ID = 1 OR AGE = 30;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | ALL | PRIMARY | NULL | NULL | NULL | 4 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#OR导致索引全部失效2：OR连接联合索引中不同字段所在列，会全表扫描
SELECT * FROM USER WHERE NAME = '张三' AND AGE = 30 OR SCHOOL = 'XDU';
#查看索引是否生效:索引全部失效,key的值为NULL
EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 30 OR SCHOOL = 'XDU';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE NAME = '张三' AND AGE = 30 OR SCHOOL = 'XDU';
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | ALL | IDX_NAME_AGE_SCHOOL | NULL | NULL | NULL | 4 | 39.06 | Using where |
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#OR导致索引全部失效2：OR连接的列中出现未建立索引的列，会全表扫描
SELECT * FROM USER WHERE ID = 1 OR HOME = '上海';
#查看索引是否生效:索引全部失效,key的值为NULL
EXPLAIN SELECT * FROM USER WHERE ID = 1 OR HOME = '上海';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE ID = 1 OR HOME = '上海';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | ALL | PRIMARY | NULL | NULL | NULL | 4 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#使用UNION关键字代替OR:解决了索引不生效的问题
SELECT * FROM USER WHERE ID = 1 UNION SELECT * FROM USER WHERE HOME = '上海';
#查看索引是否生效:索引生效
EXPLAIN SELECT * FROM USER WHERE ID = 1 UNION SELECT * FROM USER WHERE HOME = '上海';
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER WHERE ID = 1 UNION SELECT * FROM USER WHERE HOME = '上海';
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| 1 | PRIMARY | USER | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | UNION | USER | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)
5>>.数据分布影响
如果Mysql评估使用索引比全表扫描更慢,则不使用索引
#Mysql自动自动评估后,不使用索引,使用全表扫描
SELECT * FROM USER;
#查看索引是否生效:索引没有生效，key=NULL
EXPLAIN SELECT * FROM USER;
#SQL执行结果:
mysql> EXPLAIN SELECT * FROM USER;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | USER | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
6>>.sql提示
sql提示,是优化数据库的一个重要手段,就是在SQL语句中加入一些提示来达到优化操作的目的
例如为NAME字段创建一个索引:CREATE INDEX IDX_NAME ON USER(NAME);
这时NAME字段拥有两个索引,一个是普通索引IDX_NAME,一个是联合索引IDX_NAME_AGE_SCHOOL
#默认情况下,mysql会自动选择一个索引
SELECT * FROM USER WHERE NAME = '张三';
#查看mysql使用了哪个索引:key=IDX_NAME_AGE_SCHOOL
EXPLAIN SELECT * FROM USER WHERE NAME = '张三';
#SQL执行结果:
mysql> EXPLAIN SELECT NAME FROM USER;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | index | NULL | IDX_NAME | 83 | NULL | 4 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#使用SQL提示让Mysql使用联合索引,但是Mysql不一定使用我们指定的index,mysql还会判断一下,我们指定的索引速度的确高，mysql就会使用我们指定的索引
SELECT * FROM USER USE INDEX(IDX_NAME) WHERE NAME = '张三';
#查看mysql使用了哪个索引:key=IDX_NAME
EXPLAIN SELECT * FROM USER USE INDEX(IDX_NAME) WHERE NAME = '张三';
#SQL执行结果:
mysql> EXPLAIN SELECT NAME FROM USER USE INDEX(IDX_NAME);
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | index | NULL | IDX_NAME | 83 | NULL | 4 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#使用SQL提示让忽略某个索引:不忽略的情况下使用IDX_NAME这个索引,忽略了就使用IDX_NAME_AGE_SCHOOL
SELECT * FROM USER IGNORE INDEX(IDX_NAME) WHERE NAME = '张三';
#查看mysql使用了哪个索引:key=IDX_NAME_AGE_SCHOOL
EXPLAIN SELECT * FROM USER IGNORE INDEX(IDX_NAME) WHERE NAME = '张三';
#SQL执行结果:
mysql> EXPLAIN SELECT NAME FROM USER IGNORE INDEX(IDX_NAME);
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | index | NULL | IDX_NAME_AGE_SCHOOL | 171 | NULL | 4 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#强制Mysql使用某个索引:强制Mysql使用IDX_NAME_AGE_SCHOOL,不强制的话Mysql会使用IDX_NAME这个索引
SELECT * FROM USER FORCE INDEX(IDX_NAME) WHERE NAME = '张三';
#查看mysql使用了哪个索引:key=IDX_NAME_AGE_SCHOOL
EXPLAIN SELECT * FROM USER FORCE INDEX(IDX_NAME) WHERE NAME = '张三';
#SQL执行结果:
mysql> EXPLAIN SELECT NAME FROM USER FORCE INDEX(IDX_NAME_AGE_SCHOOL);
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | USER | NULL | index | NULL | IDX_NAME_AGE_SCHOOL | 171 | NULL | 4 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
7>>.覆盖索引(本质上是数据库底层只需要一次查询就可以直接返回数据,没有回表查询的过程，即只走二级索引,就称为覆盖索引)
尽量使用覆盖索引(查询使用了索引,并且需要返回的列在索引中全部能找到),尽量避免使用select *,使用select *查询很容易会触发回表查询
注意：覆盖索引不是没有使用索引，只是避免了回表查询
#没有经过回表查询,因为在PRIMARY这个聚集索引中就可以找到ID这个数据,直接返回即可
EXPLAIN SELECT ID FROM USER WHERE ID = 1;
#没有经过回表查询,因为在IDX_NAME_AGE_SCHOOL这个辅助索引中就可以找到ID和NAME这两个数据,直接返回即可
EXPLAIN SELECT ID,NAME FROM USER WHERE NAME = '张三';
#经过了回表查询这个过程,因为首先使用了WHERE条件中用到的辅助索引IDX_NAME_AGE_SCHOOL,在这个索引中只能找到ID和NAME字段,再经过回表查询可以得到HOME字段的值
EXPLAIN SELECT ID,NAME,HOME FROM USER WHERE NAME = '张三';
所以当用不到HOME这个字段的时候,就不要使用SELECT *去查询,这样可能会导致回表查询,不回表查询的效率比额外经历一次回表查询的效率要高
总结；第一步要建立索引,第二步要避免索引失效,第三步要尽量避免回表查询
8>>.前缀索引:主要针对大文本类型的数据
当字段类型为字符串(varchar,text等)时,有时需要索引很长的字符串,这会让索引变得很大,查询时浪费大量IO，影响查询效率。此时可以只将字符串的一部分前缀
建立索引,这样可以大大节约索引空间,从而提高索引效率。
语法:CREATE INDEX INDEX_NAME ON TABLE_NAME(Column(n));
n表示只取字段的前n个值建立索引
索引长度:可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1，这是最好的索引选择性,性能也是最好的
计算公式:
不截取时的选择性 : SELECT COUNT(DISTINCT NAME)/COUNT(*) FROM USER;
截取n个字符串时的选择性:SELECT COUNT(DISTINCT SUBSTR(NAME,1,n))/COUNT(*) FROM USER;
截取n-1个字符串时的选择性:SELECT COUNT(DISTINCT SUBSTR(NAME,1,n-1))/COUNT(*) FROM USER;
截取n-2个字符串时的选择性:SELECT COUNT(DISTINCT SUBSTR(NAME,1,n-2))/COUNT(*) FROM USER;
根据截取后的选择性和原始选择性做对比,同时看我们需要多大的选择性,来确定n的值,n的值就是建立索引时截取的字符前缀的长度
EG:CREATE INDEX INDEX_NAME5 ON TABLE_NAME(Column(5));
辅助索引查询过程；根据NAME字段的前5个值这个前缀在二级索引中查询到一个ID值,根据这个ID值回表一整行查询,得到一整行数据后不会直接返回,用这一整行数据中的
NAME字段的值和条件中NAME字段的值进行对比,如果相同,则一条数据就查询出来了,然后则将链表指针向后移动一位,然后根据ID回表查询,得到一整行数据,再次与
条件中NAME字段值进行对比,如果相同,则第二条数据被查询出来,如果不同,则查询过程结束。
因为要使用前缀索引,使用前缀来定位到B+TREE的某几个节点,所以前缀模糊匹配必然导致索引失效,因为如果前面的几个字符不确定,就无法定位到具体的某些个节点,索引必然无法生效
9>>.索引使用
单列索引：即一个索引只包含单个列
联合索引: 即一个索引包含了多个列
在业务场景中,如果存在多个查询条件,针对于查询字段建立索引时,建议联合索引,而非单列索引
10>>.索引设计原则
1>>>.针对数据量较大，且查询比较频繁的表建立索引
2>>>.针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引
3>>>.尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高
4>>>.如果是字符串类型的字段,字段的长度较长,可以针对字段的特点,建立前缀索引
5>>>.尽量使用联合索引,减少单列索引,查询时，联合索引很多时候可以覆盖 索引,节省存储空间
6>>>.要控制索引的数量,索引并不是多多益善,索引越多,维护索引的代价就会越大,会影响增删改的效率
7>>>.如果索引列不能存储NULL值,请在建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以知道哪个索引最有效的用于查询.
Mysql性能分析:
1>.查看当前数据库中执行INSERT、DELETE、UPDATE、DELETE的执行次数(或者这四种操作的执行权重):
SHOW SESSION STATUS LIKE 'Com_______';
查看所有数据库中执行INSERT、DELETE、UPDATE、DELETE的执行次数(或者这四种操作的执行权重):
SHOW GLOBAL STATUS LIKE 'Com_______';
2>.慢查询日志:查看哪些些SQL执行效率比较低
1>>.查询慢查询日志开关是否开启
#OFF关闭状态,ON打开状态
show variables like 'slow_query_log';
#查看慢查询日志的位置
show variables like 'slow_query_log_file';
#模糊匹配查询查询日志开关和慢查询日志的位置：
show variables like 'slow_quer%';
#将慢查询日志开关设置为开启状态
set global slow_query_log='ON';
#设置慢查询日志的存放位置
set global slow_query_log_file='C:\\slow_mysql.log';
2>>.在Mysql配置文件中加入如下配置:
Linux：/etc/my.cnf
Windows:my.ini
#开启Mysql慢查询日志开关
slow_query_log = 1;/slow_query_log = ON;
#设置慢日志的时间为2秒,sql语句执行时间超过2秒,就会记录慢查询日志
long_query_time=2;
#没有使用到索引的查询也将被记录在日志中
log-queries-not-using-indexes = ON;
#log-slow-queries:代表MYSQL慢查询的日志存储目录,此目录文件一定要有写权限
log-slow-queries="C:/Program Files (x86)/MySQL/MySQL Server 5.0/log/mysql-slow.log";
3>>.查看慢查询日志
Linuxri日志保存位置:/var/lib/mysql/localhost-slow.log
Windows日志保存位置:C:\ProgramData\MySQL\MySQL Server 8.0\Data
3>.使用show profiles;查看sql执行耗时情况
首先查看是否支持profiles操作:select @@have_profiling; 查询结果为yes表示支持profiles操作
然后打开profiles,set [global/session] profiling = 1;
#查看每一条sql的耗时基本情况:
show profiles;
#查看指定query_id的sql语句各个阶段的耗费时情况
show profile for query query_id;
#查看指定query_id的sql语句的cpu使用情况
show profile cpu for query query_id;
4.explain执行计划
#直接在任意select语句之前按加上explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
显示结果分析:
id字段: id相同:从上往下执行,id不相同:值越大执行的优先级越高
key：该sql使用到的索引名称,没有使用索引,则值为NULL
key_len:索引的长度
type:表示连接类型,性能从好到坏依次是null system const re_ref ref range index all
根据主键/唯一索引查询type为const,根据非唯一索引查询为type为ref,范围查询type为range,type为index，代表索引全扫描
889
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
