MySQL 函数-事务-引擎-索引

DCL-权限管理

DCl:数据库控制语言，用来管理数据库用户，控制数据库访问权限。

权限控制：

1. 查询权限
SHOW GRANTS FOR '用户名' @ '主机名' ;

show grants for 'heima'@'%';

2.授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名' @ '主机名';

grant all on itcast.*to'heima'@'%';

3.撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名' @ '主机名';

revoke all on itcast.*from 'heima'@'%';

用户管理：

1.查询用户
USE mysql;
SELECT * FROM user;

2.创建用户
CREATE USER '用户名'@'主机名’ IDENTIFIED BY '密码' ;

--创建用户itcast ,只能够在当前主机LocaLhost访问，密码123456;
create user 'itcast'@'localhost' identified by '123456';

3.修改用户密码
ALTER USER ‘用户名’@'主机名’ IDENTIFIED WITH mysql native_ password BY ‘新密码' ;

--修改用户heima的访问密码为1234 ;
alter user 'heima'@'%' identified with mysql_ native_ password by '1234';

4.删除用户
DROP USER '用户名’@'主机名’;

drop user 'itcast'@'localhost';

函数

字符串函数：

concat(s1,s2.....sn)	字符串拼接，将S1, S2, .. Sn拼接成一一个字符串
lower(str)	将字符串str全部转为小写
upper(str)	将字符串str全部转为小写
lpad(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格(不会去除中间空格)
substring(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

select concat('hollow' , 'mysql');
--输出hollowmysql
select lpad('01',5,'-');
--输出---01
select rpad('01',5,'-');
--输出01---
select trim(' hellow mysql ');
--输出hellow mysql
select substring('hollow mysql',1,5);
--输出hollo

数值函数:

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的模
rand(x,y)	返回0~1内的随机数
round(x,y)	求参数x的四舍五入的值，保留y位小数

select  ceil(1.1);
--输出2
select floor(3.6);
--输出3
select mod(6,3);
--输出0
select rand();
--输出0.26579019731449915
select round(2.456,2);
--输出2.46

日期函数：

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日期
date_add(date,interval expr type)	返回一个日期/时间值加上一一个时间间隔expr后的时间值
datediff(date1,date2)	返回起始时间date1和结束时间date2之间的天数

select curdate();
--输出2023-10-22
select curtime();
--输出10:02:10
select  now();
--输出2023-10-22 10:02:26
select year(now());
--输出2023
select month(now());
--输出10
select day(now());
--输出22
select  date_add(now(),INTERVAL 70 day );
--输出2023-12-31 10:03:10
select  datediff('2023-12-16','2023-10-16');
--输出61

流程函数：

函数value,t,f)	功能
if（value,t,f)	如果value为true,则返回t，否则返回f
ifnull(value1,value2)	如果value1不为空，返回valuel, 否则返回value2
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默认值

select  if(false,'ok','error');
--error
select  ifnull('ok','default');
--ok
select  name,(case salary when '10000'then'高薪水'else '普通薪水' end) as '工作地址' from employee;

 事务

1、简介：
事务是一组操作的集合，它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

2、查看/设置事务提交方式：

SELECT @@autocommit ;  （结果为1则为自动提交）

SET @@autocommit= 0 ;    （修改成手动提交)
提交事务：    COMMIT ;
回滚事务：   ROLLBACK;

3、四大特性：

●原子性 :事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

●一致性 :事务完成时， 必须使所有的数据都保持一致状态。

●隔离性 :数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

●持久性:事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

4、并发事务问题：

脏读	一个事务读到另外一个事务还没有提交的数据。
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。

5、事务的隔离级别（解决事务的并发问题）：

Read uncommitted: 解决脏读/不可重复读/幻读问题。

Read committed：解决不可重复读/幻读。 

Repeatable Read(默认隔离级别)：解决幻读。

Serializable：均不可解决

事务的隔离级别越高，数据越安全，但是性能越低。

 引擎

1、MySQL体系结构主要有：

连接层（与客户端连接）

服务器（完成大多数的核心服务功能。有sql接口，解析器，查询优化器，查询缓存 ）

引擎层（提供各种存储引擎，真正的负责MySQL中数据的存储和提取）

存储层（ 数据存储层，并完成与存储引擎的交互）

2、 存储引擎简介：

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

3、在创建表时指定存储引擎

create table表名(

字段1 字段1 类型 [ COMMENT字段1注释],
.............
字段n 字段n类型[COMMENT 字段n注释]

) ENGINE = INNODB [ COMMENT表注释];

4、存储引擎选择：
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。

InnoDB :是Mysq|l的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存 储引擎是比较合适的选择。

MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

MEMORY: 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引

索引概述
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。 在数据之外，数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构，上实现高级查找算法,这种数据结构就是索引。

索引结构

B+Tree索引	最常见的索引类型，大部分引擎都支持B+树索引
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree(空间索引)	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
Full-text(全文索引)	是一种通过建立倒排索引,快速匹配文档的方式。

B-Tree（多路平衡查找树）

以一棵最大度数为5阶，B-tree为例，每个节点最多存储4个key，5个指针，度数指一个节点的子节点个数，当每个节点插入个数超过key，则中间元素向上分裂。

 B+Tree

与B-tree类似，特点是：1.所有元素都会出现在叶子节点，非叶子节点起索引作用，2.叶子节点形成一个单链表，每个节点通过指针指向下一节点。MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。（双链表）

Hash

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

如果两个(或多个)键值,映射到一个相同的槽位.上,他们就产生了hash冲突( 也称为hash碰撞)，可以通过链表来解决。

Hash索引特点：

1. Hash索引只 能用于对等比较(=，in)， 不支持范围查询(between, >，<, ..)。

2.无法利用索 引完成排序操作

3.查询效率高， 通常只需要-次检索就可以了， 效率通常要高于B+tree索引


在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

1.聚集索引(Clustered Index)：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。

（必须有，而且只有一个）

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

2.二级索引(Secondary Index)：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键
（可以存在多个）

可以进行回表查询：先走二级索引找到对应主键值，再根据主键值到聚集索引中拿到行数据。

索引语法：

●创建索引
CREATE  [ UNIQUE | FULLTEXT]  INDEX index_ name ON table_ name ( index_ col name,.. );
(关联一个字段称单列索引，关联多个字段为联合索引)

create index idx_user_pro_age_sta on tb_user(profession,age,statues);

●查看索引
SHOW INDEX FROM table_ name ;

●删除索引
DROP INDEX index name ON table_ name ;
 

SQL性能分析

1.SQL执行频率
SQL优化主要是对查询语句的优化，MySQL客户端连接成功后,通过show [session|global] status命令可以提供服务器状态信息。通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次。

SHOW GLOBAL STATUS LIKE 'Com_____';  （一个下划线为一个字符)

2.慢查询日志

在筛选出select较多的数据库后，就要确认那些select语句需要进行优化。慢查询日志记录了所有执行时间超过指定参数(long_ _query_ _time,单位:秒，默认10秒)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf) 中配置如下信息:

查询慢日志开关状态：

show variable like 'slow_query_log';

开启MySQL慢日志查询开关：
slow_query_ log=1

设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志：long_ query_time=2

配置完毕之后,通过以下指令（systemctl restart mysqld）重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysqllocalhost-slow.log。

3.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;
 

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

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

explain执行计划各字段含义:
id：
select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下; id不同,值越大,越先执行)。

select_type：
表示SELECT的类型，常见的取值有SIMPLE (简单表,即不使用表连接或者子查询)、PRIMARY (主查询，即外层的查询)、UNION (UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等。

type：
表示连接类型，性能由好到差的连接类型为NULL、system、 const、 eq _ref、ref、 range、index、 all 。

Key：
实际使用的索引，如果为NULL,则没有使用索引。

Key_ len：
表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度,在不损失精确性的前提下，长度越短越好。

rows ：
MySQL认为必须要执行查询的行数，在innodb引擎的表，是一个估计值，可能并不总是准确的。

filtered：
表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。