MySQL基础知识笔记

最新推荐文章于 2024-08-29 17:00:41 发布

weixin_51092636

最新推荐文章于 2024-08-29 17:00:41 发布

阅读量57

点赞数

文章标签： mysql 数据库 sqlserver

本文链接：https://blog.csdn.net/weixin_51092636/article/details/125466334

版权

本文详细讲解了MySQL的基础概念、启动与登录方法，涵盖DQL查询（包括基础、条件、排序、分页和多表查询）、DML操作（插入、修改和删除），以及DDL语句和数据类型。深入解析索引的原理、设计和使用，包括B+树的优势和常见索引类型。同时涵盖了范式理论和关键运维技巧。

摘要由CSDN通过智能技术生成

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

MySQL基础篇

MySQL基础篇

1.初始数据库

1.1 为什么要是用数据库？

持久化：将数据存储到可掉电式存储设备中以供日后使用。

将复杂的数据按照一定的规则进行存储，方便日后的维护，查询，修改等操作。

1.2 数据库的相关概念

数据库的常见概念 ★
1、DB（Database）：数据库，存储数据的容器
2、DBMS(Database Management System)：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB
3、SQL(Structured Query Language)：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言
数据库存储数据的特点
1、数据存放到表中，然后表再放到库中
2、一个库中可以有多张表，每张表具有唯一的表名用来标识自己
3、表中有一个或多个列，列又称为“字段”，相当于java中“属性”
4、表中的每一行数据，相当于java中“对象”
常见的数据库管理系统
mysql、oracle、db2、sqlserver

1.3 SQL的语言分类

DQL（Data Query Language）：数据查询语言

select

DML(Data Manipulate Language):数据操作语言

insert 、update、delete

DDL（Data Define Languge）：数据定义语言

create、drop、alter

TCL（Transaction Control Language）：事务控制语言

 commit、rollback

1.3 MySQL服务的启动和停止

方式1
如果当你输入命令后，提示“拒绝服务”，请以系统管理员身份打开命令提示符界面重新尝试.

net start 服务名
net stop 服务名

在这里插入图片描述

方式2
计算机——右击——管理——服务

1.4 登录和退出方式

登录方式：

mysql -h 主机名 -P 端口号 -u 用户名 -p密码

在这里插入图片描述

退出方式：

exit 或 ctrl+C

2. DQL语言（数据查询语言）

2.1 基础查询语句

# 1、查询单个字段
select 字段名 from 表名;
 
# 2、查询多个字段
select 字段名，字段名 from 表名;
 
# 3、查询所有字段
select * from 表名;
 
# 4、查询常量
# 注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
select 常量值;
 
# 5、查询函数
select 函数名(实参列表);
 
# 6、查询表达式
select 100/1234;
 
# 7、起别名
# ① as
# ② 空格
 
# 8、去重
select distinct 字段名 from 表名;
 
# 9、+
# 作用：做加法运算
select 数值+数值;  # 直接运算
select 字符+数值;  # 先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算
select null+值;  # 结果都为null
 
# 10、【补充】concat函数
# 功能：拼接字符
select concat(字符1，字符2，字符3,...);
 
# 11、【补充】ifnull函数
# 功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值
select ifnull(commission_pct,0) from employees;
 
# 12、【补充】isnull函数
# 功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0
ISNULL('a'),

2.2 条件运算符

2.2.1 算术运算符

加（+）、减（-）、乘（*）、除（/）和取模（%）运算。

SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5, 'a' + 5 FROM dual;

在这里插入图片描述

一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。
（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）

SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,100 + 2 * 5 / 2,100 /3, 100 DIV 0 FROM dual;

在这里插入图片描述

一个数乘以整数1和除以整数1后仍得原数；
一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。

SELECT 12 % 3, 12 MOD 5 FROM dual;

在这里插入图片描述

2.2.2 比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。
在这里插入图片描述

2.2.3 逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。
在这里插入图片描述

2.2.4 位运算符

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。
在这里插入图片描述

2.2.5 使用正则表达式查询

在这里插入图片描述
在fruits表中，查询f_name字段以字母‘b’开头的记录，SQL语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';

面试题
 SELECT * FROM customers;
# 可以查询到所有的结果
 
 SELECT * FROM customers WHERE NAME LIKE '%%';
 # 如果name为null的就查询不出来

2.3 排序查询

# 1. 排序查询的基本结构
select 查询列表
from 表
where 筛选条件
order by 排序列表 [asc}desc]
 
# 2. 特点
1、asc ：升序，如果不写默认升序
   desc：降序
2、排序列表 支持 单个字段、多个字段、函数、表达式、别名
3、order by的位置一般放在查询语句的最后（除limit语句之外）

多列排序

SELECT last_name, department_id, salary FROM employees ORDER BY department_id ASC, salary DESC;

在这里插入图片描述

2.4 分页查询

# 1. 分页查询
select 查询列表
from 表
[join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段]
limit [offset，] size;
 
# 2. 注意
offset代表的是起始的条目索引，默认从0开始
size代表的是显示的条目数
 
# 3. 公式
假如要显示的页数为page，每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;

2.5 多表查询（多表查询分类详解）

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
常见错误：笛卡尔积
在这里插入图片描述
笛卡尔积的错误会在下面条件下产生：
1、省略多个表的连接条件（或关联条件）
2、连接条件（或关联条件）无效
3、所有表中的所有行互相连接
解决笛卡尔积的方式：在 WHERE 加入有效的连接条件。

SELECT table1.column, table2.column 
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件

2.5.1 等值连接 VS 非等值连接

【等值连接案例】
部门表：
在这里插入图片描述
员工表：

SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id 
FROM employees, departments 
WHERE employees.department_id = departments.department_id;

在这里插入图片描述

拓展1：多个连接条件与 AND 操作符
拓展2：区分重复的列名
多个表中有相同列时，必须在列名之前加上表名前缀。
在不同表中具有相同列名的列可以用表名加以区分。
拓展3：表的别名
使用别名可以简化查询。
列名前使用表名前缀可以提高查询效率。
拓展4：连接多个表
连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件。

【非等值查询案例】
在这里插入图片描述

2.5.2 自连接 VS 非自连接

在这里插入图片描述

SELECT CONCAT(worker.last_name ,' works for ' , manager.last_name) 
FROM employees worker, employees manager 
WHERE worker.manager_id = manager.employee_id ;

2.5.3 内连接 VS 外连接

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的
行，这种连接称为左（或右）外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。
sql 99 语法：

内连接(INNER JOIN)的实现

# 两个表实现内连接
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id 
FROM employees e 
JOIN departments d 
ON (e.department_id = d.department_id);

# 三个表实现内连接
SELECT employee_id, city, department_name 
FROM employees e JOIN departments d 
ON d.department_id = e.department_id 
JOIN locations l 
ON d.location_id = l.location_id;

外连接(OUTER JOIN)的实现

# 左外连接
SELECT e.last_name, e.department_id, d.department_name 
FROM employees e 
LEFT OUTER JOIN departments d 
ON (e.department_id = d.department_id) ;

在这里插入图片描述

# 右外连接
SELECT e.last_name, e.department_id, d.department_name 
FROM employees e 
RIGHT OUTER JOIN departments d 
ON (e.department_id = d.department_id) ;

在这里插入图片描述

2.5.4 UNION的使用

#语法
SELECT column,... 
FROM table1 
UNION [ALL] 
SELECT column,... FROM table2

UNION 操作符返回两个查询的结果集的并集，去除重复记录。
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

2.5.5 7种SQL JOINS的实现

在这里插入图片描述
代码实现：

#中图：内连接 A∩B
 SELECT employee_id,last_name,department_name
  FROM employees e 
  JOIN departments d 
  ON e.`department_id` = d.`department_id`;

#左上图：左外连接
 SELECT employee_id,last_name,department_name
  FROM employees e 
  LEFT JOIN departments d 
  ON e.`department_id` = d.`department_id`;

#右上图：右外连接 
SELECT employee_id,last_name,department_name 
FROM employees e 
RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B 
SELECT employee_id,last_name,department_name
 FROM employees e 
 LEFT JOIN departments d 
 ON e.`department_id` = d.`department_id`
 WHERE d.`department_id` IS NULL

#右中图：B-A∩B 
SELECT employee_id,last_name,department_name 
FROM employees e 
RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE e.`department_id` IS NULL

#左下图：满外连接 # 左中图 + 右上图 A∪B 
SELECT employee_id,last_name,department_name 
FROM employees e
LEFT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE d.`department_id` IS NULL 
UNION ALL 
#没有去重操作，效率高 
SELECT employee_id,last_name,department_name 
FROM employees e 
RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id`;

#右下图 #左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B） 
SELECT employee_id,last_name,department_name 
FROM employees e 
LEFT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE d.`department_id` IS NULL 
UNION ALL 
SELECT employee_id,last_name,department_name 
FROM employees e 
RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

2.8 子查询

2.7 函数

2.7.1 单行函数

常见单行函数

# 字符函数
# concat:连接
 SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees;
# substr:截取子串，索引从1开始
SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put; # 截取从索引1到字符串最后
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put; # 截取从索引1到索引3位置的字符
# upper:变大写
# lower：变小写
SELECT UPPER('john');
SELECT LOWER('joHn');
# replace：替换
SELECT REPLACE('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
# length：获取字节长度
SELECT LENGTH('duanhengyi');
# trim:去前后空格
SELECT TRIM(' duanhengyi  ');
# lpad：左填充
# rpad：右填充
SELECT LPAD('殷素素',2,'*') AS out_put; # 中间的数字表示最终的字符串的长度
SELECT RPAD('殷素素',12,'ab') AS out_put;
# instr:获取子串第一次出现的索引
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷') AS out_put;

2.7.2 聚合函数

常见聚合函数

max 最大值
min 最小值
sum 和
avg 平均值
count 计算个数

2.8 SELECT的执行过程

#方式1： 
SELECT ...,....,...
 FROM ...,...,.... 
 WHERE 多表的连接条件 AND 不包含组函数的过滤条件 
 GROUP BY ...,... 
 HAVING 包含组函数的过滤条件 
 ORDER BY ... ASC/DESC
  LIMIT ...,... 
  #方式2： 
  SELECT ...,....,... 
  FROM ... 
  JOIN ... ON 多表的连接条件 
  JOIN ... ON ... 
  WHERE 不包含组函数的过滤条件 
  AND/OR 不包含组函数的过滤条件 
  GROUP BY ...,... 
  HAVING 包含组函数的过滤条件 
  ORDER BY ... ASC/DESC 
  LIMIT ...,... 
  #其中： 
  #（1）from：从哪些表中筛选 
  #（2）on：关联多表查询时，去除笛卡尔积 
  #（3）where：从表中筛选的条件 
  #（4）group by：分组依据 
  #（5）having：在统计结果中再次筛选 
  #（6）order by：排序 
  #（7）limit：分页

3 DML语言(数据操作语言)

插入：insert

修改：update

删除：delete

3.1 插入语句

# 方式1：
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);
# 方式2：
INSERT INTO beauty
SET id=19, NAME='刘涛', phone='999';

3.2 修改语句

# 单表修改
update 表名
set 列=新值,列=新值,...
where 筛选条件;
# 多表修改
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;

3.3 删除语句

# 方式一：
# 语法
# 1.单表的删除
# 案例：删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
 
# 2.多表的删除
# 案例：删除张无忌的女朋友的信息
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';
# 案例：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';

# 方式二：
# 语法：
truncate table 表名;

4. DDL语言（数据定义语言）

一、库的管理
创建、修改、删除

#1、库的创建
create database  [if not exists]库名;
#案例：创建库Books
CREATE DATABASE IF NOT EXISTS books ;
 
#2、库的修改
RENAME DATABASE books TO 新库名;
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
 
#3、库的删除
DROP DATABASE IF EXISTS books;

二、表的管理
创建、修改、删除

# 1.表的创建 ★
# 语法：
create table 表名(
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	...
	列名 列的类型【(长度) 约束】
)
# 案例：创建表Book
CREATE TABLE book(
	id INT,#编号
	bName VARCHAR(20),#图书名
	price DOUBLE,#价格
	authorId  INT,#作者编号
	publishDate DATETIME#出版日期
);
DESC book;
#案例：创建表author
CREATE TABLE IF NOT EXISTS author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)
)
DESC author;
 
 
# 2.表的修改
# 语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
# ① 修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
# ② 修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
# ③ 添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE; 
# ④ 删除列
ALTER TABLE book_author DROP COLUMN  annual;
# ⑤ 修改表名
ALTER TABLE author RENAME TO book_author;
 
 
# 3.表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;
# 通用的写法：
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE  表名();
 
 
# 4.表的复制
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
 
SELECT * FROM Author;
SELECT * FROM copy2;
# 1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
# 2.复制表的结构+数据
CREATE TABLE copy2 
SELECT * FROM author;
 
# 只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author 
WHERE nation='中国';
 
# 仅仅复制某些字段
CREATE TABLE copy4 
SELECT id,au_name
FROM author
WHERE 0;

5. 数据类型

整型
在这里插入图片描述
小数
浮点型

定点型
在这里插入图片描述

字符型
在这里插入图片描述
3、CHAR 和 VARCHAR 如何选择？
1）对于经常变更的数据来说，CHAR 比 VARCHAR更好，因为 CHAR 不容易产生碎片。

2）对于非常短的列或固定长度的数据（如 MD5），CHAR 比 VARCHAR 在存储空间上更有效率。

3）使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。

4）尽量避免使用 TEXT/BLOB 类型，查询时会使用临时表，导致严重的性能开销。

日期型
在这里插入图片描述

6. 约束

为什么需要约束？
数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
在这里插入图片描述
约束的分类
NOT NULL 非空约束，规定某个字段不能为空
UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一)约束
FOREIGN KEY 外键约束
CHECK 检查约束
DEFAULT 默认值约束

7.视图

含义：虚拟表，和普通表一样使用mysql5.1版本出现的新特性，是通过表动态生成的数据, 只保存了sql逻辑, 不保存查询结果。

视图的作用：
- 重用sql语句
- 简化复杂的sql操作，不必知道它的查询细节
- 保护数据，提高安全性（只提供对方需要的信息）
向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句

创建单表视图

CREATE VIEW myview AS
SELECT first_name,last_name FROM employees;

创建多表视图

CREATE VIEW empview AS
SELECT employee_id emp_id,last_name NAME,department_name 
FROM employees e,departments d 
WHERE e.department_id = d.department_id;

基于视图创建视图
联合“emp_dept”视图和“emp_year_salary”视图查询员工姓名、部门名称、年薪信息创建 “emp_dept_ysalary”视图。

CREATE VIEW emp_dept_ysalary AS
SELECT emp_dept.ename,dname,year_salary 
FROM emp_dept 
INNER JOIN emp_year_salary 
ON emp_dept.ename = emp_year_salary.ename;

更新视图中的数据会自动同步到原来的表中数据

UPDATE myview 
SET last_name= 'meisiqi' 
WHERE first_name = 'duan';

删除视图中的数据会自动删除原来表中的数据


DELETE FROM myview WHERE last_name = 'meisiqi';

修改视图和删除视图

# 修改视图操作
ALTER VIEW 视图名称 AS查询语句
# 删除视图操作
DROP VIEW IF EXISTS 视图名称;

8. 索引

8.1 索引的本质、优点和缺点

为什么需要索引？
索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。
索引的优点和缺点
优点：

降低数据库的IO成本
通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：
创建和维护索引需要耗费时间，这种时间随着数据量的增加而增加，这样就降低了数据的维护速度。
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

8.2 索引的分类

物理结构角度
聚簇索引
聚簇索引不是一种单独的索引类型，而是数据存储方式（所有的用户数据都存放在叶子节点中）索引即数据，数据即索引。
即每张表只能有一个聚簇索引，也就是我们常说的主键索引
特点：
目录页中采用单向链表的方式，用户记录页中采用双向链表的结构，同一层次中数据按照主键的大小顺序排序。
在这里插入图片描述
非聚簇索引（二级索引，辅助索引）
自己定义的辅助索引
回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根
据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。也就
是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！

从应用上可以划分

普通索引：MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建；

唯一索引：索引列中的值必须是唯一的，但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建；

主键索引：特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建；

组合索引：组合表中多个字段创建的索引，遵守最左前缀匹配规则；

全文索引：只有在 MyISAM 引擎上才能使用，同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。

8.3 索引的设计原则

适合加索引的情况

选择唯一性索引
为常作为查询条件的字段建立索引
为经常需要排序、分组和联合操作的字段建立索引
限制索引的数目
小表不建议索引（如数量级在百万以内）
尽量使用数据量少的索引
DISTINCT 字段需要创建索引

不适合加索引的情况

在where中使用不到的字段，不要设置索引
数据量小的表最好不要使用索引
有大量重复数据的列上不要建立索引
避免对经常更新的表创建过多的索引
不建议用无序的值作为索引
删除不再使用或者很少使用的索引
不要定义冗余或重复的索引

8.4 索引的数据结构

B+树的数据结构

在这里插入图片描述

索引的数据结构和具体存储引擎的实现有关，MySQL 中常用的是Hash 和 B+ 树索引。
Hash 索引底层就是 Hash 表，进行查询时调用 Hash 函数获取到相应的键值（对应地址），然后回表查询获得实际数据.
B+ 树索引底层实现原理是多路平衡查找树，对于每一次的查询都是从根节点出发，查询到叶子节点方可以获得所查键值，最后查询判断是否需要回表查询.
两者的区别：
Hash 和 B+ 树索引的区别？

（1）Hash 进行等值查询更快，但无法进行范围查询。
Hash 不支持模糊查询以及多列索引的最左前缀匹配,因为 Hash 函数的值不可预测，如 AA 和 AB 的算出的值没有相关性。
Hash 任何时候都避免不了回表查询数据. 虽然在等值上查询效率高，但性能不稳定，因为当某个键值存在大量重复时，产生 Hash 碰撞，此时查询效率反而可能降低。
（2）B+ 树本质是一棵查找树，自然支持范围查询和排序。
询效率比较稳定，因为每次查询都是从根节点到叶子节点，且为树的高度。

8.5 为何使用 B+ 树而非二叉查找树做索引？

8.6为何使用 B+ 树而非 B 树做索引？

先来了解一下 B+ 树和 B 树的区别：
B 树非叶子结点和叶子结点都存储数据，因此查询数据时，时间复杂度最好为 O(1)，最坏为 O(log n)。而 B+ 树只在叶子结点存储数据，非叶子结点存储关键字，且不同非叶子结点的关键字可能重复，因此查询数据时，时间复杂度固定为 O(log n)。

B+ 树叶子结点之间用链表相互连接，因而只需扫描叶子结点的链表就可以完成一次遍历操作，B 树只能通过中序遍历。
B+ 树减少了 IO 次数
B+ 树查询效率更稳定
B+ 树更加适合范围查找

8.6 索引失效的情况

全值索引我最爱当查询条件where中有多个条件的时候创建联合索引的效率更高一些，如果有部分索引和联合索引，优化器会使用联合索引进行查询，导致部分索引失效。
最佳左前缀的规则
最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、<、between、like）就会停止匹配。如建立 (a,b,c,d) 索引，查询条件 b = 2 是匹配不到索引的，但是如果查询条件是 a = 1 and b = 2 或 a=1 又或 b = 2 and a = 1 就可以，因为优化器会自动调整 a,b 的顺序。再比如 a = 1 and b = 2 and c > 3 and d = 4，其中 d 是用不到索引的，因为 c 是一个范围查询，它之后的字段会停止匹配。
索引失效1
计算、函数、类型转换(自动或手动)导致索引失效

# 索引有用
EXPLAIN SELECT SQL_NO_CACHE * 
FROM student 
WHERE student.name LIKE 'abc%'; 

# 下面效率低
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

索引失效2
类型转换导致索引失效

# 假设name上有索引
# 未使用到索引
 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
 # 使用到索引 
 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

索引失效3

不等于(!= 或者<>)索引失效

索引失效4

# 范围条件右边的列索引失效
# 联合索引中name失效
EXPLAIN SELECT SQL_NO_CACHE * 
FROM student 
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

索引失效5-6

is null可以使用索引，is not null无法使用索引
like以通配符%开头索引失效

什么是覆盖索引？

在 B+ 树的索引中，叶子节点可能存储了当前的键值，也可能存储了当前的键值以及整行的数据，这就是聚簇索引和非聚簇索引。在 InnoDB 中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

总结：一个索引包含了满足查询结果的数据就叫做覆盖索引。
简单说就是，索引列+主键包含 SELECT 到 FROM之间查询的列。

什么是索引下推？

9.范式

在这里插入图片描述

weixin_51092636

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础知识笔记

数据库
复制链接

扫一扫