网上学习笔记–>黑马
一.sql 分类
- DDL(数据库定义语言) :用来定义数据库对象(数据库,表–跟表数据无关,字段)
- DML(数据库操作语言):用来对数据库表数据进行增删改
- DQL(数据库查询语言):用来查询数据库表的记录
- DCL(数据库控制语言):用来创建数据库用户,控制数据库的访问权限
二.DDL–定义数据库对象(数据库,表–跟表数据无关,字段)
DDL-数据库操作
- 查询所有数据库
show databases;
- 查询当前数据库 (就是看当前在那个数据库)
select database();
- 创建
create database [if not exists] 数据库名 []
- 删除
drop database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
- 使用
use 数据库名;
DDL-表操作-查询
- 查询当前数据库所有表
show tables;
- 查询表结构
desc 表名;
- 查询指定表的建表语句
show create table 表名;
DDL-表操作
1. 表创建
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT字段1注释] ,
字段2 字段2类型 [COMMENT 字段2注释] ,
字段3 字段3类型 [COMMENT字段3注释] ,
字段n 字段n类型 [COMMENT字段n注释]
)[ COMMENT 表注释];
2. 字段类型
- 数值类型
类型 | 大小(byte) | 有符号范围 | 无符号范围 | 描述 |
---|---|---|---|---|
tinyint | 1 | (-128,127) | (0,255) | 小整数值 |
smallint | 2 | (-32768,32767) | (0,65535) | |
mediumint | 3 | (-8388608,8388607) | ||
int / integer | 4 | (-2147483648,2147483647) | ||
bigint | 8 | (-2^63 , 2^63 -1) | ||
float | 4 | |||
double | 8 | score double(4,1)就是小数一位(99.5分) | ||
decimal |
- 字符串类型
类型 | 大小(byte) | 描述 |
---|---|---|
char | 0-255 | 定长字符串(不够的用空客补充,性能好)性别只有男或女用这个 |
VARCHAR | 0-65535 bytes | 变长字符串(不够的不会用空客补充,性能较差)年龄字数不同用这个 |
TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65535 bytes | 长文本数据 |
MEDIUMBLOB | 二进制形式的中等长度文本数据 | |
MEDIUMTEXT | 中等长度文本数据 | |
LONGBLOB | 二进制形式的极大文本数据 | |
LONGTEXT | 极大文本数据 |
- 日期类型
类型 | 大小 | 范围 | 格式 | 描述 |
---|---|---|---|---|
DATE | 3 | 1000-01-01 至9999-12-31 | YYY-MM-DD | 日期值 |
TIME | 3 | -838:59:59 至838:59:59 | HH:MM: SS | 时间值或持续时间 |
YEAR | 1 | 1901 至2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-0100:00:00至9999-12-31 23:59:59 | YYYY-MM-DD HH :MM:SS | 混合日期和时间值 |
TIMESTAME | 4 | 1970-01-01 00:00:01至2038-01-19 03:14:07 | YYYY-MM-DD HH :MM:SS | 混合日期和时间值,时间戳 |
3. 表修改
- 增加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT '注释'] [约束];
- 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
- 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
4. 表删除
- 删除表==全删
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 表名 SET 字段名1=值1,字段名2=值2,....[ WHERE 条件 ];
DML-修改数据
DELETE FROM 表名 [ WHERE条件 ]
四.DQL–查询数据库表的记录
SELECT>4
字段列表
FROM>1
表名列表
WHERE>2
条件列表
GROUP BY>3
分组字段列表
HAVING
分组后条件列表
ORDER BY>5
排序字段列表
LIMIT>6
分页参数
住 123....6是执行顺序
DQL-基本查询
- 查询多个字段
SELECT 字段1,字段2,字段3... FROM 表名;
SELECT * FROM 表名;
- 设置别名
SELECT 字段1 [AS 别名1] , 字段2 [AS 别名2] ... FROM 表名;
- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
DQL-条件查询
- 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
- 条件
> >= < <= = <>或!=
between and
in()
like 占位符----- _——单个字符 %——多个字符
is null
and或&&
or或||
not或!
DQL-聚合函数
count()——统计数量
max()——最大值
min()——最小值
avg()——平均值
sum()——求和
DQL-分组查询
SELECT 字段列表 FROM 表名 [ WHERE 条件] GROUP BY 分组字段名 [ HAVING 分组后过滤条件];
where与having区别
执行时机不同: where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
判断条件不同: where不能对聚合函数进行判断,而having可以。
例:
查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
select workaddress,count(*) from emp where age< 45 group by wonkaddress having count(*) >= 3;
DQL-排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 ,字段2 排序方式2;
ASC:升序(小的在第一行)默认值
DESC:降序
DQL-分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
五.DCL–创建数据库用户,控制数据库的访问权限
DCL-管理用户
- 查询用户
首先进入mysql数据库,
USE mysql;
SELECT * FROM user;
- 创建用户
CREATEUSER ‘用户名'@'主机名’ IDENTIFIED BY ‘密码’;
- 修改用户密码
ALTER USER ‘用户名'@'主机名’ IDENTIFIED with mysql_native_password BY‘新密码';
- 删除用户
DROP USER ‘用户名'@'主机名';
DCL-权限控制
- 查询权限
SHOW GRANTS FOR ‘用户名'@'主机名’;
- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名'@'主机名';
六.函数
字符串函数
函数 | 功能 |
---|---|
CONCAT(S1,S2.….Sn) | 字符串拼接,将S1,S2,…Sn拼接成一个字符串 |
LOWER(str) | 将字符串str全部转为小写 |
UPPER(str) | 将字符串str全部转为大写 |
LPAD(str,n,pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
RPAD(str,n,Rad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
TRIM(str) | 去掉字符串头部和尾部的空格 |
SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
由于业务需求变更,企业员工的工号,统一次5位数,目前不足5位数的全部在前面补0。比如:1号员工的工号应该为00081。
update emp set workno = lpad( workno , 5 , ' 0 ' ) ;
数值函数
函数 | 功能 |
---|---|
CEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
MOD(xy) | 返回x/y的模 |
RAND() | 返回0~1内的随机数 |
ROUND(x,y) | 求参数x的四舍五入的值,保留y位小数 |
日期函数
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(date) | 获取指定date的年份 |
MONTH(date) | 获取指定date的月份 |
DAY(date) | 获取指定date的日期 |
DATE_ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
流程函数
函数 | 功能 |
---|---|
IF(value , t, f) | 如果value为true,则返回t,否则返回f |
IFNULL(value1 , value2) | 如果value1不为空,返回value1,否则返回value |
CASE WHEN [ val1 ] THEN [res1] …ELSE [ default ] END | 如果val1为true,返回res1,…否则返回default默认值 |
CASE [ expr ] WHEN [ val1 ] THEN [res1] … ELSE [ default] END | 如果expr的值等于val1,返回res1,…否则返回default默认值 |
七.约束
概述
1.概念: 约束是作用于表中字段上的规则,用于限制存储在表中的数据。
2.目的: 保证数据库中数据的正确、有效性和完整性。
3.分类:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | DEFAULT |
使用
create table user(
id int primary key auto_increment comment '主键 ,
name varchar(10) not null unique comment '姓名',
age int check ( age > 0 && age <= 120 ) comment '年龄' ,
status char(1) default '1' comment '状态',
gender char(1)comment ‘性别'
)comment '用户表;
外键约束
1.建表时添加外键
CREATE TABLE表名(
字段名数据类型 ,
…
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);
2.建表之后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
3.删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
删除 / 更新行为
行为 | 说明 |
---|---|
NO ACTION | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与RESTRICT一致) |
RESTRICT | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与NO ACTIO |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(这就要求该外键允许取nul)。 |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持) |
举例:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGNKEY(外键字段) REFERENCES 主表名(主表字段名)ON UPDATE CASCADE ON DELETE CASCADE;
八.多表查询
连接查询–内连接
隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件..;
>显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件... ;
连接查询-外连接
左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件...;
相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件...;
相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据
连接查询-自连接
自连接查询语法:
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
联合查询-union , union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
SELECT 字段列表 FROM 表A....
UNION [ ALL ]
SELECT 字段列表 FROM 表B .... ;
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
将薪资低于5000的员工,和年龄大于50的员工查出来
select * from emp where salary<5000
union
select * from emp where age>50
子查询
概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 =(SELECT column1 FROM t2) ;
查询在“方东白”入职之后的员工信息
a.查询方东白 的入职日期
select entrydate from emp where name = '方东白';
b.查询指定入职日期之后入职的员工信息
select * from emp where entrydate >方东白的entrydate
select * from emp where entrydate > (select entrydate from emp where name = '方东白');
子查询–列子查询
列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:IN .NOT IN、ANY 、SOME 、 ALL
操作符 | 描述 |
---|---|
IN | 在指定的集合范围之内,多选一 |
NOT IN | 不在指定的集合范围之内 |
ANY | 子查询返回列表中,有任意一个满足即可 |
SOME | 与ANY等同,使用SOME的地方都可以使用ANY |
ALL | 子查询返回列表的所有值都必须满足 |
查询比财务部所有人工资都高的员工信息
a.查询所有财务部人员工资
select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');
b.比财务部所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id = (select id fron dept where nane= ‘财务部'〕);
子查询–行子查询
行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用的操作符 : =、<>、IN、NOT IN
查询与"张无忌”的薪资及直属领导相同的员工信息;
a,查询“张无忌”的薪资及直属领导
select salary, managerid from emp where name = '张无忌';
b.查询与"张无忌”的薪资及直属领导相同的员工信息;
select * from emp where (salary,managerid) = (select salary, managerid from emp where name= ‘张无忌');
子查询–表子查询
表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:IN
查询入职日期是“2806-01-01”之后的员工信息,及其部门信息
a,入职日期是“2086-01-01”之后的员工信思
select * from emp where entrydate > '2006-01-01';
b、查询这部分员工,对应的部门信悬;
select e.* , d.* from (select * from emp where entrydate > '2086-81-01') e left join dept d on e.dept_id = d.id ;
九.事务
事务操作
方式一
- 开启事务
START TRANSACTION或 BEGIN ;
- 提交事务
COMMIT;
- 回滚事务
ROLLBACK;
方式二
- 查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit =0;(0/false是手动提交,1/true是自动提交)
- 提交事务
COMMIT;
- 回滚事务
ROLLBACK;
并发事务问题
问题 | 描述 | 解读 |
---|---|---|
脏读 | 一个事务读到另外一个事务还没有提交的数据。 | 事务A读到事务B修改的数据,但事务B未提交,事务B回滚 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。 | 事务A两次读取数据,事务B对数据修改,事务A再次读取,数据前后不一样 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了’ | 事务A两次读取数据,事务B添加数据,事务A再次读取,数据不一致(删除不是幻读) |
脏写 | 两个事务,一个事务修改了另一个未提交的修改事务 | 事务A修改数据时,未提交前,事务B也去修改这条数据,事务B未提交,而这时事务A提交了,然后事务B回滚了,发现事务A修改的不存在了 |
更新丢失 | 两个事务对同一个数据进行更新,一个事务修改的值覆盖另一个事务 | 事务A更新了一条数据并提交了,事务B更新了一条数据并提交了,然后事务A继续查询更新的数据,发现与自己更新的不一样 |
事务四大特性
- 原子性(Atomicity)∶事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency) : 事务完成时,必须使所有的数据都保持一致状态(全成功或全失败)。
- 隔离性(lsolation) : 数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability) : 事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
事务隔离级别
隔离级别(从上到下,级别越高,性能越低) | 赃读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | 不能 | 不能 | 不能 |
Read committed | 不能 | 不能 | |
Repeatable Read(默认) | 不能 | ||
Serializable |
注:以上隔离级别,脏写都能解决
- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
- 设置事务隔离级别
SET [ SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READUNCOMMITTED | READ COMMITED│ REPEATABLEREAD | SERALZABLE}
十.存储引擎
存储引擎简介
- 在创建表时,指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段n 字段n类型 [COMMENT 字段n注释]
)ENGINE = INNODB [ COMMENT 表注释];
- 查看当前数据库支持的存储引擎
SHOW ENGINES;
存储引擎特点
lnnoDB
-
介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在MySQL 5.5之后,InnoDB是默认的MySQL存储引擎。
-
特点
1. DML操作遵循ACID模型,支持事务;
2. 行级锁,提高并发访问性能;
3. 支持外键FOREIGN KEY约束,保证数据的完整性和正确性;
MylSAM
-
介绍
MyISAM是MySQL早期的默认存储引
-
特点
1. 不支持事务,不支持外键
2. 支持表锁,不支持行锁
3. 访问速度快
引擎对比
特点 | InnoDB | MyISAM | Memory |
---|---|---|---|
存储限制 | 64TB | 有 | 有 |
事务安全 | 支持 | – | – |
锁机制 | 行锁 | 表锁 | 表锁 |
B+tree索引 | 支持 | 支持 | 支持 |
Hash索引 | – | – | 支持 |
全文索引 | 支持(5.6版本之后) | 支持 | – |
空间使用 | 高 | 低 | N/A |
内存使用 | 高 | 低 | 中等 |
批量插入速度 | 低 | 高 | 高 |
支持外键 | 支持 | – | – |
存储引擎选择
- InnoDB : 是Mysql的默认存储引擎,支持事务、外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包含很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择。
- MyISAM : 如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
- MEMORY : 将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。
十一.索引
索引概述
介绍
- 索引是数据库中的一种数据,用于指向实际的数据。
- 索引 (index)是帮助MySQL高效获取数据的数据结构(有序),没有索引查找数据是全盘检索的方式。
索引优缺点
优势 | 劣势 |
---|---|
提高数据检索的效率,降低数据库的IO成本 | 索引列也是要占用空间的。 |
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消 | 索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE时,效率降低。 |
索引结构
B-Tree ---- MyISAM 支持
B-Tree是一种平衡搜索多叉树,树是排序过的,采用是二分查找方式,查找的效率跟树的高度有关
B+Tree ---- MyISAM,InnoDB 支持
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。
相对于B-Tree区别:
①.所有的数据都会出现在叶子节点
②.叶子节点形成一个单向链表
为什么InnoDB存储引擎选择使用B+tree索引结构?
- 相对于二叉树,层级更少,搜索效率高;
- 对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
- 相对Hash索引,B+tree支持范围匹配及排序操作;
索引语法
- 创建索引
CREATE [ UNIQUE|FULLTEXT ] INDEX index_name ON table_name ( index_col_name...);
- 查看索引
SHOW INDEX FROM table_name ;
- 删除索引
DROP INDEX index_name ON tableiname ;
drop table if exists employee;
create table employee
(
emp_id int primary key auto_increment, -- 主键索引
emp_no varchar(20) not null unique, -- 唯一索引
emp_name varchar(20) not null,
emp_info text not null,
emp_age int not null,
emp_gender varchar(2) not null
)ENGINE = myisam;
create index ix_emp_name on employee(emp_name); -- 普通索引
create fulltext index ix_emp_text on employee(emp_info);-- 全文索引
create index ix_emp_name_age on employee(emp_name,emp_age);-- 组合索引
drop index ix_emp_name on employee;-- 删除索引
SQL性能分析
SQL执行频率
MySQL客户端连接成功后,通过show[sessiongloball status命令可以提供服务器状态信息。通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:
SHOW GLOBAL STATUS LIKE 'Com___' ;
慢查询日志
慢查询日志记录了所有执行时间超过指定参数( long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
#查看慢查询日志是否开启
show variables like 'slow_ query 1og' ;
#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
配置完毕之后,通过以下指令重新启动MySQL服务器进行测试,查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。
profile详情
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数,能够看到当前MySQL是否支持profile操作:
SELECT @@have_profiling;
默认profiling是关闭的,可以通过set语句在session/global级别开启profiling:
SET profiling=1;
执行一系列的业务SQL的操作,然后通过如下指令查看指令的执行耗时:
#查看每一条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 select * from tb_user where profession= "软件工程 and age = 31 and status = '0'; (没有失效,先后顺序可以改变)
explain select * from tb_user where profession= "软件工程' and age = 31;(部分失效)
explain select * from tb_user where profession='软件工程";(部分失效)
explain select * from tb_user where age = 31 and status = '0';(全失效)
explain select * from tb_user where profession= "软件工程 and status = '0'; (部分失效)
explain select * from tb_user where status = '0';(全失效)
范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效
explain select * from tb_user where profession= '软件工程' and age > 30 and status = '0';(status 失效)
explain select * from tb_user where profession= '软件工程' and age >= 30 and status = '0';(没有失效)
索引列运算
不要在索引列上进行运算操作,索引将失效。
explain select * from tb_user where substring(phone,10,2)= '15';
字符串不加引号
字符串类型字段使用时,不加引号,索引将失效。
explain select * from tb_user where profession='软件工程' and age = 31 and status = 0;
explain select * from tb_user where phone = 17799990015;
模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
explain select * from tb_user where profession like '软件%';(没有失效)
explain select* from tb_user where profession like '%工程';(失效)
explain select * from tb_user where profession like '%工% ';(失效)
or连接的条件
用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。两边都有索引就不会失效
explain select* from tb_user where id= 10 or age = 23;(全失效)
explain select * from tb_user where phone = '17799990017' or age = 23;(全失效)
数据分布影响
如果MySQL评估使用索引比全表更慢,则不使用索引。
select *from tb_user where phone >='17799990005';
select * from tb_user where phone >= '17799990015';
极少数据走索引,大多数放弃索引走全表检索
SQL提示
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 *。
using index condition:查找使用了索引,但是需要回表查询数据
using where; using index:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据
- select * from tb_user where id = 2 :id是主键,为聚集索引,此语句可以直接走聚集索引
- select id, name from tb_user where name =‘Arm’:id是主键,为聚集索引。name是辅助索引,此语句走辅助索引,发现select 后面需要的列都能在这找到,就不需要走聚集索引
- select id,name,gender from tb_user where name =‘Arm’:id是主键,为聚集索引。name是辅助索引,gender不是索引,先走name所在的辅助索引(二级索引),发现select 后面需要的gender列不能在这找到,就需要走聚集索引,因为聚集索引包含所有的列,如果name和gender是联合索引,就不需要走聚集索引
前缀索引
当字段类型为字符串(varchar, text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘lO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
- 语法
create index idx_xxoxx on table_name(column(n));
-
前缀长度
可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email, 1,5)) / count(*) from tb_user ;( 越接近1 distinct substring(email, 1,5) 越好)