MySQL万字基础总结

【全文精华无废话，觉得还不错可以加个关注点个赞！！！】

SQL

通用语法与分类

• DDL数据定义语言，定义数据库对象（数据库、表、字段）。

• DML数据操作语言，用来对数据库表中数据进行增删改。

• DQL数据查询语句，用来查询数据库中表的记录。

• DCL数据控制语言，用来创建数据库用户，控制数据库的访问权限。

DDL

数据库操作

# 查询所有数据库
SHOW DATABASES;
# 查询当前数据库
SELECT DATABASE();
#创建数据库
CREATE DATABASE IF NOT EXISTS  [数据库名] DEFAULT CHARSET utf8mb4;
#删除数据库
DROP DATABASE [IF EXISTS] [数据库名];
#使用某一个数据库
USE 数据库名;

数据库创建可选项：

• (如果不存在):IF NOT EXISTS

• (字符集) : DEFAULT CHARSET utf8mb4

• COLLATE排序规则

数据表操作（创建&查询）

#查询当前数据库所有表(前提是进入到这个数据库)
SHOW TABLES;
#根据表名查看表结构
DESC 表名;
#查询指定表的建表语句
SHOW CREATE TABLE 表名;
#表创建(例)
CREATE TABLE tb_user(
  id INT COMMENT  '编号',
  name VARCHAR(255) COMMENT '姓名',
  age INT COMMENT '年龄',
  gender VARCHAR(1) COMMENT '性别'
) COMMENT '用户表' ;

数据类型相关：

• DECIMAL:依赖于精度和标度的值,如123.45，精度是5，标度是2。

• DOUBLE用法 score double(4,1);表示成绩只有4位，其中一位小数。

• TINYBLOB描述不超过255个字符的二进制数据

• TINYTEXT描述的是超文本字符串

• BLOB二进制形式的长文本数据

• DATE:描述的日期值YYYY-MM-DD

• TIME:描述的时间值或持续时间HH:MM:SS

• DATETIME混合时间YYYY-MM-DD HH:MM:SS

• TIMESRAMP:混合日期和时间值,时间戳

数据表操作（修改&删除）

# 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
# 例
ALTER TABLE tb_user ADD nickname varchar(20) COMMENT '昵称';
# 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
# 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
# 删除字段
ALTER TABLE 表名 DROP 字段名;
# 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
# 删除表
DROP TABLE [IF EXISTS] 表名;
# 删除指定表，并重新创建该表
TRUNCATE TABLE 表名;

DML(增删改)

添加数据

# DML- 添加数据
# 给指定字段添加数据
INSERT INTO 表名(字段名1，字段名2，...)VALUES (值1,值2,...);
# 给全部字段添加数据
INSERT INTO 表名 VALUES (值1,值2,...);
# 批量添加数据
INSERT INTO 表名(字段名1，字段名2，...)VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...),;
INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);

更新和删除

# DML-修改数据
UPDATE tb_user SET 字段名1 = 值1,字段名2 = 值2,... [where 条件];
# 案例
UPDATE tb_user SET nickname = 'itheima' where id = 1;
# DML-删除数据
DELETE FROM tb_user [WHERE 条件];# 删除符合条件的数据

DQL数据查询语言

DQL语法

# DQL语法结构
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数
# ------------------------------------------------------------------------------------------
# 基本查询-查询返回所有字段
SELECT * FROM tb_user;
# 基本查询-查询返回多个字段
SELECT 字段1,字段2,... FROM tb_user;
# 设置别名
SELECT 字段1 AS 别名,字段2 AS 别名,... FROM 表名;
# 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

# DQL条件查询 条件 >, >=, <, <=, <>, BETWEEN...AND..., IN(...), LIKE 占位符(_匹配单个字符,%匹配任意个字符),...

# 聚合函数作用于表中某列数据(所有null不参与计算)
COUNT 统计数量
MAX   最大值
MIN   最小值
AVG   平均值
SUM   求和
[语法] SELECT 聚合函数(字段列表) FROM 表名;

# 分组查询-语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
#例-根据性别分组，统计男员工和女员工的数量
SELECT gender, COUNT(*) from emp group by gender;
#例-查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址。
SELECT workaddress, COUNT(*) FROM emp WHERE age < 45 GROUP BY workadress HAVING COUNT(*) >= 3;

# 排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
# 分页查询LIMIT
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
# 例
SELECT * FROM emp LIMIT 10;

where和having的区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件则不参与分组；而having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以。

分页查询相关

• 起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数。
• 若是查询第一页，起始索引可以省略，直接简写为limit 10。

DCL

管理数据库 用户，控制数据库的访问 权限

创建用户itcast，可以在任意主机访问数据库,修改主机名为%即可。

# 查询用户
USE mysql;
SELECT * FROM user;
# 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
# 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
# 删除用户
DROP USER '用户名'@'主机名';
###--例
# 创建用户itcast，只能在当前主机localhost访问，密码123456
CREATE USER 'itcast'@'localhost' identified by '123456';
# 创建用户itcast，可以在任意主机访问数据库，密码123456
CREATE USER 'itcast'@'%' identified by '123456';

权限控制

ALL、ALL PRIVILEGES所有权限；SELECT查询数据权限；INSERT/UPDATE/DELETE插入数据/修改数据/删除数据权限；ALTER修改表权限；DROP删除数据库/表/视图权限；CREATE创建数据库/表权限；

# 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
# 授予权限:给哪个用户授予那张数据库表的什么权限。
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
# 给某用户授予所有数据库的所有表权限就是*
GRANT ALL ON *.* TO 'itheima'@'%';
# 撤销权限
REVOKE 权限列表 ON 数据库.表名 FROM '用户名'@'主机名';

内置函数

字符串函数

# concat 字符串拼接
SELECT CONCAT('hello', ' MySQL');
# lower全部转为小写
SELECT LOWER('Hello');
# upper全部转为大写
SELECT UPPER('hello');
# lpad左填充,第一个参数是要填充的字符串，第二个是长度，第三个是用什么填充
SELECT LPAD('01', 5, '-');
# rpad
SELECT RPAD('01', 5, '-');
# TRIM 去掉字符串头部和尾部的空格
SELECT TRIM(' HELLO MYSQL ');
# substring,第二个参数是索引起始位置，第三个是截取几个
SELECT SUBSTRING('HELLO MYSQL',1,5);

mysql中索引从1开始

数值函数

SELECT CEIL(1.1);#向上取整
SELECT FLOOR(1.1);#向下取整
SELECT MOD(3,4);#取模余
SELECT RAND();#求随机数0-1之间
SELECT ROUND(2.345,2);#对第一个参数四舍五入保留2个参数（第二个参数指定保留的小数位数）
#例
# 生成六位随机验证码
SELECT LPAD(ROUND(RAND()*1000000,0),6,'0');

日期函数

SELECT CURDATE();#返回当前日期
SELECT CURTIME();#返回当前时间
SELECT NOW();# 返回当前日期和时间
SELECT YEAR(NOW());#返回当前时间对应的年份
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT DATE_ADD(now(),INTERVAL 10 DAY);#当前时间后推10天的日期
SELECT DATEDIFF('2021-12-01','2021-11-01');#两个日期相差的天数

流程控制函数

IF(value,value1,value2) 如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2) 如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END 相当于Java的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END 相当于Java的switch…case…

SELECT IF(1 > 0,'正确','错误'); 
->正确
SELECT IFNULL(null,'Hello Word');
->Hello Word    
SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END;
->1 > 0
SELECT (CASE workaddress 
　　WHEN '北京' THEN '一线城市'
　　WHEN '上海' THEN '一线城市'
ELSE '二线城市' END) as '工作地址'
FROM emp;
#---------------------------------
SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' 
				  WHEN salary>=10000 THEN '潜力股'  
				  WHEN salary>=8000 THEN '屌丝' 
				  ELSE '草根' END  "描述"
FROM employees; 
#---------------------------------
SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款' 
								   WHEN 2 THEN '已付款' 
								   WHEN 3 THEN '已发货'  
								   WHEN 4 THEN '确认收货'  
								   ELSE '无效订单' END 
FROM t_order;
#---------------------------------
SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
#---------------------------------
SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       			   ELSE      salary END     "REVISED_SALARY"
FROM   employees;
#---------------------------------
SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0))
FROM employees;

约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效和完整。

分类：非空约束、唯一约束、主键约束、默认约束、检查约束、外键约束等。

# ---------------约束---------------------
create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age > 0 && age <=120 ),
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
)comment '用户表';
#插入验证
insert into user(name, age, status, gender) values ('Tom1',19,'1','男'),('Tom2',25,'0','男');

# ---添加外键
create table 表名(
    字段名 数据类型,
    ...,
    constraint [外键名称] foreign key (外键字段名) reference 主表(主表列名)
);
# 添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 关联的主表(主表列名);
# ---删除外键
alter table 表名 drop foreign key 外键名称;
# 删除/更新行为
# 例
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;

多表查询

多表关系：一对一、一对多（多对一）、多对多

# 多对多
use test;
create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
)comment '学生表';
insert into student values (null,'戴琦丝','2000100101'),
                           (null,'谢逊','2000100102'),
                           (null,'殷天正','2000100103'),
                           (null,'韦一笑','2000100104');
create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名字'
)comment '课程表';
insert into course values (null, 'java'),(null, 'MySQL'),(null, 'Hadoop');
create table student_course(
    id int auto_increment comment '主键' primary key ,
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';
insert into student_course values (null,1,1),
                                  (null,1,2),
                                  (null,1,3),
                                  (null,2,2),
                                  (null,2,3),
                                  (null,3,3);

多表查询-概述

**笛卡尔积：**笛卡尔乘积是指在数学中，两个集合 A集合和B集合 的所有组合情况。（在多表查询时，需要消除笛卡尔积）

消除笛卡尔积：令A集合外键等于B集合主键。

连接查询

• 内连接：相当于查询A、B交集部分数据
• 外连接：
  • 左外连接，查询左表所有数据，以及两张表交集部分数据。
  • 右外连接，查询右表所有数据，以及两张表交集部分数据。
• 自连接：当前表与自身的连接查询，自连接必须使用表别名。

子查询

连接查询—内连接

# 隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
## -例 查询每一个员工的姓名，及关联的部门的名称（为表起了别名就不能用原名来限制字段了）
select emp.name,dept.name from emp, dept where emp.dept_id = dept.id;
select e.name,d.name from emp e, dept d where e.dept_id = d.id;
# 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
## -例 查询每一个员工的姓名，及关联的部门的名称
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

连接查询—外连接

# 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
## -例 查询emp表所有数据和对应的部门信息。emp表中有个员工对应的部门是null
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
# 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

连接查询—自连接

自连接查询，可以是内连接查询，也可以是外连接查询。

SELECT 字段列表 FROM 表A 别名 JOIN 表A 别名B ON 条件...;
# 例--查询员工及其所属领导的名字(涉及到一张表)(但是单表查询做不出来，要看成两张表去做)
select a.name, b.name from emp a, emp b where a.managerid = b.id;

LeetCode181子查询和自连接

题目和链接：超过经理收入的员工

select a.name as Employee
from Employee a, Employee b 
where a.managerId = b.id
and a.salary > b.salary;

联合查询-union,union all

对于union查询，就是把多次查询接合起来，形成一个新的查询结果集。

SELECT 	字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B...;
## -将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来。
select * from emp where salary < 5000
union all
select * from emp where age > 50;
# union all直接将查询结果合并
# union 会对查询结果去重再合并
# 联合查询要求多张表的列数及字段类型要保持一致

子查询（嵌套查询）

# 子查询
select * from t1 where column1 = (select column1 from 2);
## 分为标量子查询（查询结果为单个值），列子查询（查询结果为一列）、行子查询、表子查询（查询结果为多行多列）
## where之后出现，from之后出现、select之后出现

# 标量子查询
# --例-查询销售部所有员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');

# 列子查询
##常用操作符：IN、NOT IN、ANY、SOME、ALL
### ANY在子查询的列表中只要有一个满足就可以。
### SOME与ANY等同
# --例 查询销售部和市场部的所有员工信息
## a.查询销售部和市场部的部门id
## b.根据部门id查询员工信息
select * from emp 
where dept_id in 
(select id from dept where name = '销售部' or name = '市场部');

# 行子查询
## 常用操作符 =、<>、IN、NOT IN
# 例-查询与“张无忌”的薪资及直属领导相同的员工信息
## a.查询张无忌的薪资与直属领导
## b.查询与“张无忌”的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid) = (select salary,manager from emp where name = '张无忌');
## 注意：salary = 12500 and managerid = 1等价于 (salary,managerid) = (12500,1)

# 表查询
## 常用操作符 IN
# 例-查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息。
## a.查询“鹿杖客”，“宋远桥”的职位和薪资信息
## b.查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息。
select * from emp
where (jod,salary) in
(select job,salary from emp where name = "鹿杖客" or name = "宋远桥");

事务

是一组操作的集合，是一个不可分割的单位，要么同时成功，要么同时失败。

事务操作

# 查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit = 0; #	手动提交
# 提交事务
COMMIT;
# 回滚事务
ROLLBACK;
# 开启事务(所有事务如果执行成功，就提交事务,否则回滚事务)
START TRANSACTION;

四大特性

原子性

一致性

隔离性

永久性

并发事务问题

脏读：一个事务读到另一个事务还没有提交到数据。

不可重复读：一个事务先后读取同一条记录，但两次读取到数据不同。

幻读：一个事务按条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了‘幻影’。

事务隔离级别

读未提交、读已提交、可重复读（mysql默认）、串行化

# 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION
# 设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

存储引擎

体系结构图

存储引擎

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

# 查询当前数据库支持的存储引擎
show engines;
# 创建表并指定引擎 [例]
create table my_myisam(
	id int
) engine = MyISAM;

InnoDB引擎

是一种兼顾高可靠性和高性能的通用存储引擎。

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

InnoDB逻辑存储结构

表空间、段、区、页、行

表空间中包含多段，段中包含多区，区中包含多页，也中包含多行。行指的就是我们在表中的一行一行。

每row行中就是事务的Id、指针、一个一个的字段。一个页16k，一个区1M最多包含64个页。

MyISAM

MyISAM是MySQL早期的默认存储引擎。特点：不支持事务，不支持外键；支持表锁，不支持行锁；访问速度快。

Memory

Memory引擎的表数据存在内存中，受硬件问题或断电问题影响，只能作为临时表或缓存使用。

特点：内存存放、hash索引（默认）

索引

索引：帮助MySQL高效获取数据的数据结构(有序)。

优点：提高数据检索的效率，降低数据库的IO成本。

​ 通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗。

缺点：索引列也要占空间。

​ 大大提高了查询效率，但降低了更新表的速度。

索引结构

Btree

B+树相对于B树区别：

• 所有数据都会出现在叶子节点
• 叶子节点形成一个单向链表

B+树，数据的存储在叶子节点，索引节点不存储数据

hash索引

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

链表可以解决hash冲突。不支持范围查询。

支持hash索引的是Memory引擎，InnoDB具有自适应hash功能。

InnoDB存储引擎使用B+tree索引结构原因：

• 相对于二叉树，层级更少，搜索销量高；
• 对于B-tree，无论叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值更少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
• 相对于hash索引，B+tree支持范围匹配及排序操作；

索引分类

主键索引(只能有一个)、唯一索引（可以避免同一个变种某数据列中的值重复）、常规索引、全文索引FULLTEXT

聚集索引、二级索引

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一索引作为聚集索引。
• 如果没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询：先根据二级索引去找，找到对应的id值，再根据聚集索引值拿到对应的行数据。

索引语法

# 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,字段名...);
# 查看索引
SHOW INDEX FROM table_name;
# 删除索引
DROP INDEX index_name ON table_name;

SQL性能分析

根据SQL执行频率，判断此类数据库以什么为主（增删改查）。

# 查当前数据库的INSERT、UPDATE等访问频次
SHOW GLOBAL STATUS LIKE 'Com_____';
# 查服务器状态
SHOW [SESSION | GLOBAL] STATUS;

慢查询日志,记录了所有执行时间超过指定参数（long_query_time，单位秒，默认10秒）等所有SQL语句的日志。

MySQL慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 查询慢查询日志是否开启
show variables like 'slow_query_log';
## 开启（在my.cnf）
### 开启慢日志查询开关
slow_query_log = 1
### 设置慢日志时间,超过该时间就是慢查询，记录慢查询日志
long_query_time = 2

profile详情:有些操作执行时间可能达到慢查询时间的左边缘，比如1.95秒，这类SQL也是相对性能较低的，我们也要对这部分SQL进行优化，如何定位到该处sql，慢查询日志满足不了。这时候就要用到profile详情。

show profiles 能够帮助我们了解时间都耗费到哪里去了，通过have_profiling参数,能看到当前数据库是否支持profile操作:select @@have_profiling;

# 查询是否支持profile操作
select @@have_profiling;
# 查询是否开启profiling
select @@profiling;
# 开启session/global级别的profiling
SET [SESSION|GLOBAL] 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;

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

## 语法-直接在select语句之前加上关键字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

explain查询到的执行计划每列的含义:

索引使用

• 验证索引效率，在未建立索引之前，执行如下sql语句，查看sql的耗时。

# 根据id去查非常快
select * from tb_sku where id = 1;
# 根据sn查（sn这个字段没有索引，数据量多耗时很大）
select * from tb_sku where sn = '100000000003145001';
# 为sn字段创建索引(B+tree结构)
create index idx_sku_sn on tb_sku(sn);
# 再次执行select * from tb_sku where sn = '100000000003145001';耗时非常短
# 在select语句前加上explain可查看该语句执行计划

索引使用法则–最左前缀法则

该法则主要针对于联合索引。如果索引了多列（联合索引），要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳过了某一列，索引将部分失效（后面的字段索引失效）。

# profession、age、status是关联的索引，顺序依次是1、2、3
select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
# 如果查的时候将age这个条件删除，将出现部分失效，最后面的status字段将失效。
# 如果查询的时候删除profession条件，将不再走索引查询，不满足最左前缀法则

索引使用法则–索引失效情况

• 索引列运算，不要在索引列上进行运算操作，否则索引将失效。

• 字符串类型字段使用时不加引号，索引将失效。

• 如果头部模糊匹配，索引将失效。（仅仅尾部模糊匹配，索引不会失效）

• 用or分割开的条件，如果or前的条件的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。（只有两侧都有索引，索引才不会失效）

• 数据分布影响，如果mysql评估使用索引比全表更慢，则不适用索引。

SQL提示

简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index: # 告诉数据库用哪个索引
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
ignore index: # 告诉数据库不要用哪个索引
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
force index: # 告诉数据库必须走这个索引
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

覆盖索引

尽量使用覆盖索引(其实就是select后面怎么写)，减少使用select *；

注（执行explain后extra中出现）：using index condition:查找使用了索引，但是需要回表查询数据

​ using where; using index:查找使用了索引，但是需要的数据在索引列中都能找到，所以不需要回表查询数据。

如下就是覆盖索引，需要查的在索引列中都能找到，不需要回表。

前缀索引

# 语法 # 此处n表示要提取字符串的前几个字符来构建索引
create index idx_xxx on table_name(column(n)); 

前缀长度的确定；

## 计算公式
select count(distinct email)/ count(*) from tb_user;
select count(distinct substring(email,1,5))/ count(*) from tb_user;

单列索引&联合索引

一个索引包含单个列；一个索引包含多个列；

先按phone来排序，若phone相同，再按name排序

索引设计原则

更多内容详在：https://liyi243.cn/web/