DCL&函数&索引

回顾

多表关系:
	一对多:
		一般在多表上添加一列外键字段,值为一表的主键
		若要保证数据的有效性和完整性,一般在建表之后,通过修改表关系添加外键约束
			alter table 多表表名 add foreign key (外键字段名) references 主表(主键字段名);
    多对多:
    	通过创建一张中间表,在中间表中保留另外两张表的主键作为外键,就会将多对多拆分成两个一对多
    	若要保证数据的有效性和完整性,可以给中间表的两个字段添加外键约束
    一对一:
		字段少的情况下可以合二为一
		唯一外键对应
		主键对应
多表查询:
	内连接(两张表的交集)
		隐式
    		select 字段们 from a ,b where 连接条件和查询条件
		显式
    		select 字段们 from a join b on 连接条件 where 查询条件
		隐式和显式查询的结果一样,只需要掌握其一即可
	外连接
		左外
    		select 字段们 from a left join b on 连接条件 where 查询条件
    		查询a的所有数据,根据连接条件去查询b,满足就展示
		右外
		两种只需要记住左外即可.可以等价转换

子查询:
	一个查询需要依赖另一个查询的结果,我们就把另一个查询叫做子查询
	子查询的结果有:
		单行单列 一般做查询条件
		多行单列 一般做查询条件
		多行多列 一般作为临时表使用
自关联
	特殊的多表查询,关联的表也是自己本身.
TCL:
	begin; 开启事务
	commit; 提交事务
	rollback; 回滚事务

	mysql默认事务是自动提交的
	事务概念:
	事务特性:ACID
		原子性
		一致性
		隔离性
		持久性
	不考虑隔离性会产生那些读问题 脏读,不可重复读,幻读
	可以通过设置数据库的隔离级别来解决(一般不用设,用数据库默认的即可)

隔离级别:
	read uncommitted 以上三个问题都会反生 安全性太差
	read committed 避免了脏读   oracle默认
	repeatable read 避免了脏读和不可重复读 mysql默认
	serialiazable 所有的避免了 效率低下

主键约束: 用来确定一张表中每条记录的唯一性

外键约束: 用来说明多表中某条记录是属于那个一表中记录的

今日内容介绍

DCL:用来添加用户,设置权限

mysql中一些常用的函数:

• 日期函数
• 数学函数
• 字符串函数
• case when 相当于 if elseif 多分支判断
• if

sql练习

mysql的拔高课:(针对应用或者面试)

• 建立索引:提高查询性能
• 索引的结构

一 DCL(了解)

作用:创建或者销毁用户,给用户授权或者撤销权限

使用root用户权限创建用户:

• create user ‘用户名’@‘域名或者ip’ identified by ‘密码’;

  CREATE USER 'tom'@'localhost' IDENTIFIED BY '1234';-- 在本机上 使用tom配合1234登录
  CREATE USER 'tom'@'%' IDENTIFIED BY '1234';-- 在任意电脑上使用tom配合1234登录
  

使用root用户给其他用户授权:

• grant 权限列表(使用’,'隔开)或者all on 哪个数据库.哪个表 to ‘用户名’@‘主机名’
• 例如: GRANT ALL ON dayxx.* TO ‘tom’@‘localhost’;

查看用户哪些权限

• SHOW GRANTS FOR ‘tom’@‘localhost’;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XMud5Iwy-1620640286261)(03.DCL&函数&索引.assets/image-20210116085520870.png)]

撤销权限:

• revoke 权限列表(使用’,'隔开)或者all on 哪个数据库.哪个表 from ‘用户名’@‘主机名’
• 例如: REVOKE DELETE ON dayxx.* FROM ‘tom’@‘localhost’;

修改用户密码:

• 不登陆修改密码

  -- 不用登录mysql,直接在cmd中输入 
  mysqladmin -uroot -p password 新密码
  -- 根据提示输入原始密码即可
  

• 登陆修改用户密码

  -- 登录mysql
  set password for '用户'@'域名或者ip' = '新密码'
  

删除用户:

• drop user ‘用户名’@‘域名或者ip’
• 例如: DROP USER ‘tom’@‘localhost’;

二 MySQL函数

• 为了简化操作，mysql提供了大量的函数给程序员使用（比如你想输入当前时间，可以调用now()函数）
• 函数可以出现的位置：插入语句的values()中，更新语句中，删除语句中，查询语句及其子句中。

0 准备数据

昨日资料中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JSDfH6H5-1620640286271)(03.DCL&函数&索引.assets/image-20210116090530685.png)]

1 日期函数

1.  函数：NOW() | CURDATE() | CURTIME()
	描述：获取系统当前日期时间、日期、时间
	实例：SELECT NOW();
	SELECT NOW() FROM DUAL;-- from dual 凑语法的
	
2.  函数：YEAR(DATE) | MONTH(DATE) | DAY(DATE)
	描述：从日期中选择出年、月、日
	实例：SELECT YEAR(NOW()); 
	
3.  函数：LAST_DAY(DATE)
	描述：返回月份的最后一天
	实例：SELECT LAST_DAY(NOW());
	
4.  函数：ADDDATE(DATE,n) | SUBDATE(DATE,n)
	描述：计算起始日期 DATE 加（减） n 天的日期
	实例：SELECT ADDDATE(NOW(),10);
	
5.  函数：QUARTER(DATE)
	描述：返回日期 DATE 是第几季节，返回 1 到 4
	实例：SELECT QUARTER(NOW());
	
6.  函数：DATEDIFF(d1,d2)
	描述：计算日期 d1->d2 之间相隔的天数
	实例：SELECT DATEDIFF('2019-08-01','2019-07-01');
	
7.  函数：DATE_FORMAT(d,f)
	描述：按表达式 f的要求显示日期 d
	实例：SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
	https://www.runoob.com/sql/func-date-format.html

练习

-- 1.统计每个员工入职的天数

-- 2.统计每个员工的工龄

-- 3.查询2011年入职的员工

-- 4.统计入职10年以上的员工信息

-- 1.统计每个员工入职的天数
SELECT ename,joindate,DATEDIFF(NOW(),joindate) FROM emp;

-- 2.统计每个员工的工龄
SELECT ename,joindate,DATEDIFF(NOW(),joindate)/365 FROM emp;

-- 3.查询2011年入职的员工
SELECT ename,joindate FROM emp WHERE joindate LIKE '2011-%';
SELECT ename,joindate FROM emp WHERE YEAR(joindate) = '2011';

-- 4.统计入职10年以上的员工信息
SELECT ename,joindate FROM emp WHERE DATEDIFF(NOW(),joindate)>=3650;

2 数学函数

1.  函数：ABS(x)
	描述：返回 x 的绝对值　　
	实例：SELECT ABS(-1);
	
2.  函数：CEIL(x) | FLOOR(x)
	描述：向上（下）取整
	实例：SELECT CEIL(1.5);
	
3.  函数：MOD(x,y)
	描述：返回x mod y的结果，取余
	实例：SELECT MOD(5,4);
	
4.  函数：RAND()
	描述：返回 0 到 1 的随机数 [0,1)
	实例：SELECT RAND();
	
5.  函数：ROUND(x)  round(x,y)  保留x的y位小数且四舍五入
	描述：四舍五入
	实例：SELECT ROUND(1.23456);
	
6.  函数：TRUNCATE(x,y) 舍尾法
	描述：返回数值 x 保留到小数点后 y 位的值
	实例：SELECT TRUNCATE(1.23456,3);

练习

-- 1.统计每个员工的工龄，超过半年的算一年

-- 2.统计每个部门的平均薪资,保留2位小数

-- 3.统计每个部门的平均薪资,小数向上取整

-- 4.统计每个部门的平均薪资,小数向下取整

-- 1.统计每个员工的工龄，超过半年的算一年
SELECT ename,ROUND(DATEDIFF(NOW(),joindate)/365) FROM emp;

-- 2.统计每个部门的平均薪资,保留2位小数
SELECT dept_id,TRUNCATE(AVG(salary),2) FROM emp GROUP BY dept_id;

-- 3.统计每个部门的平均薪资,小数向上取整
SELECT dept_id,CEIL(AVG(salary)) FROM emp GROUP BY dept_id;

-- 4.统计每个部门的平均薪资,小数向下取整
SELECT dept_id,FLOOR(AVG(salary)) FROM emp GROUP BY dept_id;

3 字符串函数

1.  函数：CONCAT(s1,s2...sn)
	描述：字符串 s1,s2 等多个字符串合并为一个字符串
	在mysql中参数个数没有限制,在oracle中只能是两个
	实例：SELECT CONCAT("传", "智", "播", "客");
	
2.  函数：CHAR_LENGTH(str)
	描述：返回字符串 str 的字符数
	实例：SELECT CHAR_LENGTH("传智播客");
	
3.  函数：LENGTH(str)
	描述：返回字符串 s 的字节数
	汉字在utf8编码下 一个汉字占3个字节;gbk编码一个汉字占2个字节
	实例：SELECT LENGTH("传智播客") ;
	
4.  函数：UCASE(s) | UPPER(s)
	描述：将字符串转换为大写
	实例：SELECT UCASE("itcast");
	
5.  函数：LCASE(s) | LOWER(s)
	描述：将字符串转换为小写
	实例：SELECT LCASE("ITCAST");
	
6.  函数：LOCATE(s1,s)
	描述：从字符串 s 中获取 s1 的开始位置(从1开始)
	实例：SELECT LOCATE('he','itheima');
	
7.  函数：TRIM(str) | LTRIM(str) | RTRIM(str)
	描述：字符串去空格
	实例：SELECT TRIM("  传智人");
	
8.  函数：REPLACE(s,s1,s2)
	描述：将字符串 s2 替代字符串 s 中的字符串 s1
	实例：SELECT REPLACE('abc','a','x');
	
9.  函数：SUBSTR(s, start, length)
	描述：从字符串 s 的 start 位置截取长度为 length 的子字符串;length若不写就是截取到末尾
	实例：SELECT SUBSTR("itcast", 2, 3);
	
10. 函数：STRCMP(str1,str2)
	描述：比较字符串大小,左大于右时返回1，左等于右时返回0，，左小于于右时返回-1，
	实例：SELECT STRCMP("a","b");

练习

-- 1.将所有员工的昵称改为大写


-- 2.显示所有员工的姓氏，截取


-- 3.显示所有员工姓名字符长度


-- 4.显示所有员工姓名字节长度


-- 5.将所有姓李的员工，姓氏替换为li


-- 6.将所有员工的姓名和昵称拼接在一起

-- 1.将所有员工的昵称改为大写
SELECT nickname,UPPER(nickname) FROM emp;

-- 2.显示所有员工的姓氏，截取
SELECT ename,SUBSTR(ename,1,1) FROM emp;

-- 3.显示所有员工姓名字符长度
SELECT ename,CHAR_LENGTH(ename) FROM emp;

-- 4.显示所有员工姓名字节长度
SELECT ename,LENGTH(ename) FROM emp;

-- 5.将所有姓李的员工，姓氏替换为li
SELECT ename,REPLACE(ename,'李','li') FROM emp;

-- 6.将所有员工的姓名和昵称拼接在一起
SELECT ename,nickname,CONCAT(ename,nickname) FROM emp;

4 小结

日期

• year(日期)
• now()
• date_format(日期,格式)
• datediff(大日期,小日期)

数学

• round(数字)
• truncate(数字,位数)
• ceil和floor
• rand()
  • SELECT * FROM emp ORDER BY RAND() LIMIT 1; – 随机获取一个

字符串

• substr(字符串,开始位置,截取长度)
• replace(字符串,要替换的字符串,新字符串)
• char_length()

三 MySQL高级函数

1 case表达式

在查询代码的过程中，可能我们需要对查询的结果进行判断。

-- 语法 
	SELECT 
		CASE [字段,值] 
			WHEN 判断条件1 
				THEN 希望的到的值1
			WHEN 判断条件2 
				THEN 希望的到的值2
			ELSE 前面条件都没有满足情况下得到的值 
		END
	FROM
		table_name;

练习

-- 查询每个员工的工资等级并排序
-- 工资等级在1显示为 '努力赚钱'
-- 工资等级在2显示为 '小康生活'
-- 工资等级在3显示为 '可以娶媳妇'
-- 工资等级在4显示为 '可以买车'
-- 工资等级在5显示为 '可以买房'
-- 工资等级不在以上列表中显示为  '土豪'

SELECT ename,s.grade,
	CASE s.grade
		WHEN 1 THEN '努力赚钱'
		WHEN 2 THEN '小康生活'
		WHEN 3 THEN '可以娶媳妇'
		WHEN 4 THEN '可以买车'
		WHEN 5 THEN '可以买房'
		ELSE '壕'
	END 'desc'
FROM emp e
JOIN salarygrade s
	ON e.salary BETWEEN s.losalary AND s.hisalary
ORDER BY s.grade ASC;

2 if表达式

-- 语法
SELECT IF(1 > 0,'true','false') from table_name;

练习

-- 工资+奖金大于20000的员工 显示家有娇妻，否则显示单身狗
SELECT ename,IF(salary+IFNULL(bonus,0)>20000,'家有仙妻','继续努力')FROM emp;

四 MySQL综合练习

-- 1.计算员工的日薪(按30天)，保留二位小数

-- 2.计算出员工的年薪，并且以年薪排序 降序

-- 3.找出奖金少于5000或者没有获得奖金的员工的信息

-- 4.返回员工职务名称及其从事此职务的最低工资

-- 5.返回工龄超过10年，且2月份入职的员工信息

-- 6.返回与 林冲 同一年入职的员工

-- 7.返回工资为二等级（工资等级表）的职员名字（员工表）、部门名称（部门表）

-- 8.发工资,在原有的基础上增加：董事长2000 经理1500 其他800

-- 1.计算员工的日薪(按30天)，保留二位小数
SELECT ename,TRUNCATE(salary/30,2) FROM emp;

-- 2.计算出员工的年薪，并且以年薪排序 降序
SELECT ename,salary*12 yearsalary FROM emp ORDER BY yearsalary DESC;

-- 3.找出奖金少于5000或者没有获得奖金的员工的信息
SELECT ename,bonus FROM emp WHERE bonus < 5000 OR bonus IS NULL;
SELECT ename,bonus FROM emp WHERE IFNULL(bonus,0) < 5000;

-- 4.返回员工职务名称及其从事此职务的最低工资
-- 方式1:多表查询
SELECT j.jname,MIN(salary)
FROM emp e
JOIN job j
	ON e.job_id = j.id
GROUP BY j.jname;

-- 方式2:子查询
-- a.先查询每个职务的最低薪资
SELECT job_id,MIN(salary) FROM emp GROUP BY job_id;
-- b.关联job表查询数据
SELECT j.jname,tmp.minsalary
FROM job j
JOIN (SELECT job_id,MIN(salary) minsalary FROM emp GROUP BY job_id) tmp
	ON j.id = tmp.job_id;


-- 5.返回工龄超过10年，且2月份入职的员工信息
SELECT ename,joindate FROM emp WHERE DATEDIFF(NOW(),joindate)>3650 AND MONTH(joindate) = 2;

-- 6.返回与 林冲 同一年入职的员工
-- a.先查询林冲入职的年份
SELECT YEAR(joindate) FROM emp WHERE ename = '林冲';-- 2011
-- b.根据年份查询其他员工
SELECT * FROM emp WHERE YEAR(joindate) = 2011;
-- 合二为一
SELECT * FROM emp WHERE YEAR(joindate) = (SELECT YEAR(joindate) FROM emp WHERE ename = '林冲');


-- 7.返回工资为二等级（工资等级表）的职员名字（员工表）、部门名称（部门表）
SELECT ename,dname
FROM emp e
LEFT JOIN dept d
	ON e.dept_id = d.id
LEFT JOIN salarygrade s
	ON e.salary BETWEEN s.losalary AND s.hisalary
WHERE s.grade = 2;


-- 8.发工资,在原有的基础上增加：董事长2000 经理1500 其他800

SELECT ename,salary,jname,
	CASE jname
		WHEN '董事长' THEN salary+2000
		WHEN '经理' THEN salary+1500
		ELSE salary+800
	END 实发工资
FROM emp e
LEFT JOIN job j
	ON e.job_id = j.id;

五 MySQL性能【了解】

1 分析-数据库查询效率低下

​ 我们企业开发往往关注的的业务需求和功能的实现，但是系统上线时间越来越长，数据随着也就增加了，会影响数据库的整体性能

数据库表优化

硬件优化:提高服务器硬件水平,在软件优化完之后,还没有达到预想的效果了再考虑硬件

软件优化:

• 建立索引–今天
• 将常见常用的数据放到非关系型数据库(缓存,工作在内存中)–年前
• 分库分表–mysql高级
• 读写分离–mysql高级

2 查看-sql语句的执行效率

我们先插入千万数据

-- 0.创建数据库
create database day03_1;
use  day03_1;

-- 1. 准备表
CREATE TABLE `user`(
	id INT,
	username VARCHAR(32),
	`password` VARCHAR(32),
	sex VARCHAR(6),
	email VARCHAR(50)
);

-- 2. 创建存储过程，实现批量插入记录
DELIMITER $$ -- 声明存储过程的结束符号为$$
CREATE PROCEDURE auto_insert()
BEGIN
    DECLARE i INT DEFAULT 1;
	START TRANSACTION; -- 开启事务
    WHILE(i<=10000000)DO
        INSERT INTO `user` VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'@itcast.cn'));
        SET i=i+1;
    END WHILE;
	COMMIT; -- 提交
END$$ -- 声明结束
DELIMITER ; -- 重新声明分号为结束符号

-- 3. 查看存储过程
-- SHOW CREATE PROCEDURE auto_insert;

-- 4. 调用存储过程
CALL auto_insert();

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FxYohk7n-1620640286280)(03.DCL&函数&索引.assets/image-20200827113217406.png)]

慢查询日志

-- 查看慢查询相关配置
show variables like '%query%'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OURMtL7H-1620640286284)(03.DCL&函数&索引.assets/image-20200827113714354.png)]

开启慢查询日志

set global  slow_query_log = on;

设置慢查询日志时间节点（阈值）

-- 全局设置
set global long_query_time=3;

-- 会话设置
set long_query_time=3;

慢查询日志文件分析

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vCwMils0-1620640286286)(03.DCL&函数&索引.assets/image-20200827114437219.png)]

六 MySQL索引

1 什么是索引

在现实生活中，我们经常去图书馆查阅图书。

现在我们将所有图书杂乱无章的摆放在一起，那么找一本书就像大海捞针一样效率非常低。

如果我们按分类整理排序后，根据类别去找对应的图书那么效率就很高了。其实这个整理排序的过程就是索引。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rCQwcF14-1620640286287)(03.DCL&函数&索引.assets/1566306728298.png)]

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HFnG9m0C-1620640286288)(03.DCL&函数&索引.assets/1566306864253.png)]

2 索引的优势与劣势

优势

• 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

3 索引分类和语法

单列索引（给一列字段增加索引） id

组合索引（给多列字段增加索引） id +name

分类

• 普通索引：仅加速查询
• 唯一索引：加速查询 + 列值唯一（可以有null）
• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
• 组合（联合）索引：多列值组成一个索引，注意：最左匹配原则

创建索引

直接创建(普通或唯一 理解)

-- 创建普通索引
create index 索引名 on 表名(列名);

-- 创建唯一索引
create unique index  索引名 on 表名(列名);

-- 创建普通组合索引
create index 索引号 on 表名(列名1,列名2);

-- 创建唯一组合索引
create unique index  索引号 on 表名(列名1,列名2);

例如:

create table i1(
	id int,
    name varchar(8),
    email varchar(8)
);

-- 给name添加普通索引
CREATE INDEX nameindex ON i1(NAME);

-- 给email添加唯一索引
CREATE UNIQUE INDEX emailindex ON i1(email);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2bA26qbM-1620640286289)(03.DCL&函数&索引.assets/image-20210116144013415.png)]

修改表时指定(理解)

-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(列名1,列名2)

-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(列名1,列名2)

-- 添加普通索引，索引值可以出现多次。
alter table 表名 add index(列名1,列名2)

例如:

create table i2(
	id int,
    name varchar(8),
    email varchar(8)
);
-- 给id添加主键索引
ALTER TABLE i2 ADD PRIMARY KEY(id);

-- 给name添加普通索引
ALTER TABLE i2 ADD INDEX(NAME);

-- 给email添加唯一索引
ALTER TABLE i2 ADD UNIQUE(email);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fXP112xf-1620640286291)(03.DCL&函数&索引.assets/image-20210116144407438.png)]

创建表时指定(推荐)

CREATE TABLE i3(
	id INT,
	username VARCHAR(20),
	email VARCHAR(20),
	
	PRIMARY KEY(id),
	UNIQUE(email),
	INDEX(username)
);

删除索引

-- 直接删除
drop index 索引名 on 表名;

-- 修改表时删除 【掌握】
alter table 表名 drop index 索引名字;

-- 删除主键
alter table 表名 drop primary key;

索引创建原则

1. 字段内数据的辨识度不能低于70%
2. 在经常需要 搜索 的列上建索引，这样会大大加快查找速度
3. 在经常需要 连接 的列上建索引，可以加快连接的速度。
4. 在经常需要 排序 的列上建索引，因为索引已经是排过序的，这样一来可以利用索引的排序，加快排序查询速度。

注意:索引这么好,那是不是在数据库表字段中尽量多建索引呢?

肯定是不是的。

1. 因为索引的建立和维护都是需要耗时的 , 添加记录、更新、删除时，也需要更新索引，会间接影响数据库的效率
2. 索引也需要保存起来,占用大量的磁盘空间.

4 效果演示

没有索引的查询

-- 根据id 
select * from user where id = 1234567;
-- 根据用户名
select * from user where username = 'jack1234567';
-- 根据邮箱模糊查询
select * from user where email like 'jack12345%';

在我的电脑上没有建立索引的时候,查询数据几乎都需要5秒钟左右

创建索引重新查询

-- id设置为主键
alter table user add primary key (id);

-- 邮箱设置为唯一
alter table user add unique(email);

-- username设置为普通
alter table user add index(username);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GO0R4rsv-1620640286292)(03.DCL&函数&索引.assets/image-20210116150649450.png)]

加上索引之后,重新执行上面的查询操作,时间几乎都可以忽略不计了

5 常见索引失效情况

• 模糊查询的时候,若在索引列上的左边加上了"%" ,索引失效

  • where username like ‘jack123%’ – 索引有效
  • where username like ‘%jack123%’ – 索引失效

• 使用or查询,其中一个条件上没有索引 也会降低查询效率

  • select * from user where id = 123456 or sex = ‘female’;

• 在索引列上进行计算的,索引失效

  • select * from user where id + 1 = 1234567;

• 使用 !=、 <> 、is not null、not 等也会失效

• 组合索引要注意最左匹配原则,否则也会失效

  • 例如: 给user表添加了组合索引 (id,name,email).就相当于添加了三个索引

    alter table user add index(id,name,email);

    • id
    • id,name
    • id,name,email

  • 若我们查询的时候

    • select * from user where id = 1;-- 有效
    • select * from user where id = 1 and name like ‘jack%’;-- 有效
    • select * from user where id = 1 and name like ‘jack%’ and email like ‘jack%’;-- 有效
    • select * from user where name like ‘jack%’;-- 无效
    • select * from user where id = 1 and email like ‘jack%’;-- 无效
    • select * from user where name like ‘jack%’ and email like ‘jack%’;-- 无效

6 索引数据结构

介绍

我们知道索引是帮助MySQL高效获取数据排好序的数据结构。

索引= 排序后的数据结构

为什么使用索引后查询效率提高很多呢？接下来我们来了解下。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7oBarHTK-1620640286293)(03.DCL&函数&索引.assets/1566372154562.png)]

在没有索引的情况下我们执行一条sql语句，那么是表进行全局遍历，磁盘寻址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。

为了加快的查找效率，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

索引数据结构

1. 二叉树
2. 红黑树（平衡二叉树）
3. Hash
4. B-Tree (多路搜索树)
5. B+Tree【MySQL使用】

https://www.cs.usfca.edu/~galles/visualization/

1. 二叉树

   • 每个节点最多有两个子节点
   • 左节点的数据小于根节点数据,右节点的数据大于根节点的数据
   • 当数据有顺序插入的时候,二叉树就会变成一个链表.
   • 即使不变成链表,1千万条数据需要二叉树的深度的多深(最少也需要24层)

2. 红黑树（平衡二叉树）

   • 也是一种二叉树,不过通过左旋和右旋达到一种平衡,不会生成链表

3. Hash

   • 存还是查询，都先对数据进行hash运算，运算后得出的值，就是要将数据放入的hash桶的值。
   • 使用此数据结果查询的时候也是比较高效的.
   • 但是若做范围查询那就需要一个一个值进行查询,就不如b+树的叶子节点的链表查询快了.
   • jdk1.8的 hashmap底层:每个桶中默认存储的时候使用的链表存储,若链表的长度>8的时候,链表就会变成树.当移除数据的时候,树的数据数量若<6了就会变成链表了

4. B-Tree (多路搜索树)

   • 一个节点上可以存放更多的数据,一个节点可以拥有更多的子节点

   • 每个节点上保存的数据既有数据内容,还有索引值和指针值

   • mysql中每个索引节点大小默认值为16k,例如:索引值大小固定(6Byte),指针值大小固定(8Byte),数据内容的大小为(1010B),一个数据加起来正好1kb,一个节点上最多可以存储16个数据,一个节点上可以有17个子点点,只需要6层,就可以将1kw数据存储起来.

     [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6PWHu8Hm-1620640286294)(03.DCL&函数&索引.assets/image-20210116023912331.png)]

5. B+Tree【MySQL使用】

   • 对B Tree的改进版,每个非叶子节点上只保存索引值和指针值,只有叶子节点才保存数据;所有的叶子节点使用链表串起来
   • mysql中每个索引节点大小默认值为16k,一个节点上可以存放1170个元素,只需要3层就可以将1kw数据存储起来.并且做范围查询的时候,只需在叶子节点上直接通过链表的形式就可以获取.
   • mysql为了更快的查询,还会将根节点的索引值预先加载到内存中.假如原先是3层的话,现在只需要2次io就可以找到指定的数据了

一个节点上存放的数据更多了（因为非叶子节点不存数当前节点的数据）,每个节点的子节点数量更多了,树更加扁平化了,叶子使用的链表结构,方便做范围查询

MySQL中的B+Tree

-- 查看mysql索引节点大小
show global status like 'innodb_page_size';

MySQL中的 B+Tree 索引结构示意图：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8A6gGElo-1620640286295)(03.DCL&函数&索引.assets/1566372947007.png)]

7 数据库存储引擎

MySQL存储引擎的不同，那么索引文件保存的方式也有所不同，常见的有二种存储引擎MyISAM和InnoDB。

myisam:msyql5.5版本之前的使用,不支持事务.

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Sen7Qj5R-1620640286296)(03.DCL&函数&索引.assets/image-20210116164347405.png)]

innodb:mysql5.5(含)之后的默认使用,支持事务.

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4vnLiMog-1620640286297)(03.DCL&函数&索引.assets/image-20210116164431523.png)]

• 聚集索引

  • innodb中使用的索引
  • 一张表中只能有一个聚集索引
    • 百科上说法:逻辑地址和物理地址是一致的.
    • b+tree中叶子节点存放的索引和数据
    • 聚集索引一般是主键索引;若没有主键,就找表中的第一个唯一索引;若也没有唯一索引,mysql会默认添加一列,我们看不到.
    • 聚集索引一般都是主键索引,主键索引的叶子节点存放了数据.表中其他索引就不再存储数据了,其他索引的叶子节点中存放的主键.若搜索的是其他索引,先找到主键,在通过主键索引找具体的值.

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lQOOjB2F-1620640286298)(03.DCL&函数&索引.assets/1574059330054.png)]

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KPOugWyo-1620640286299)(03.DCL&函数&索引.assets/1568705854433.png)]

• 非聚集索引

  • myisam中使用的索引
  • 索引文件和数据文件是分开的.
  • 所有的索引都是非聚集索引,且每个索引中存放的都是数据的地址.查询的时候,先找到的数据的地址,然后通过地址定位到每条数据上.

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hr5BLBF0-1620640286299)(03.DCL&函数&索引.assets/1574059144094.png)]