MySql 高阶一

于 2024-08-19 17:06:51 发布
阅读量328 收藏 13
点赞数 19
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhanglinlang/article/details/141158334
版权

在这里插入图片描述
mysql 分为4层结构：
连接层：负责处理链接，鉴权，安全。
服务层：负责sql接口，sql分析，sql优化，sql缓存。
引擎层：负责执行服务层的操作，不同的引擎拥有不同的特性。（索引在该层）
存储层：将数据存储到文件上。

输入 show engine; 可以查询到mysql 支持的存储引擎：
在这里插入图片描述
目前只有 InnoDB 支持 事务 。

mysql 5.5之后默认使用的就是 innoDB
想设置其他引擎可以在创建表时末尾设置 engin = xxxx：
在这里插入图片描述
在这里插入图片描述
InnoDb:默认的引擎，如果需要支持事务和外键，那就是唯一的选择。
MyISAM: 读取速度快，更新速度慢，不支持事务，已经被 mogoDB取代。
Memory:数据只存在内存中，已经被redis 取代。

一、什么是数据库索引：

索引（index）是帮助数据库提高获取数据效率的数据结构

例如：下面的数据为例，当要查找45时。如果全文检索，从上到下需要比对7次，如果二叉树索引只需要2步就差查询到结果。
在这里插入图片描述

索引的数据结构

在这里插入图片描述

数据结构的特点

在这里插入图片描述

数据结构的进化：

1、二叉树： 顺序插入时，会导致层级较深。

在这里插入图片描述

2、红黑树：（平衡二叉树）会自平衡，但是在大数据量情况下，层级也会很深。

在这里插入图片描述

3、B-tree (多路平衡查找树)下面的例子是 5阶的B-tree,B树的缺点是，顺序查找速度慢。

在这里插入图片描述

4、B+树（重点）

1、所有数据都保存在叶子节点中：非叶子节点仅用于索引，不保存数据记录，这样可以提高查询性能，因为查找任何数据都必须从根节点遍历到叶子节点。
2、叶子节点之间通过链表连接：所有叶子节点按关键字的大小顺序链接，形成一个有序链表，这有利于范围查询和顺序访问。
3、更高的分支度：相较于B树，B+树每个节点可以存储更多的关键字，减少了树的高度，从而减少了磁盘I/O次数，提高了查询效率。
4、顺序访问性：由于叶子节点通过链表连接，B+树支持顺序遍历，这对于需要顺序访问数据的场景非常有用。
5、适应磁盘读写：B+树的节点大小通常与磁盘页的大小相同，这使得B+树能够很好地适应磁盘读写操作，减少磁盘I/O开销。

在这里插入图片描述
B+Tree在线演示地址：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

B+tree 存放数据的查询效率问题

高度为2 时： 如果主键是bigint 1171 *16 = 18736 条数据 如果不 int 就是4个字节还可以翻倍
高度为3 时： 1171 * 1171 *16 = 21939856 大概2千多万条数据。所以通过主键查询效率很高。

Hash 索引：

hash索引就是采用hash算法，将key value 存储到hash 表中，如果有hash冲突就，用链表的方式解决。
有点单个数据查询速度快，但是不支持范围查询，不支持排序操作。 同理于 hashMap 和 redis
在这里插入图片描述

索引的分类：

分类含义特点关键字
主键索引针对主键的索引使用PRIMARY 默认自动创建，只能又一个PRIMARY
唯一索引避免重复数据，当你使用UNIQUE字段就会创建该索引多个UNIQUE
常规索引快速定位特定数据多个
全文索引全文检索关键字多个FULLTEXT

Mysql 的InnoDB引擎中，根据存储形式，又分为如下两种

名称含义
聚集索引聚集索引的叶子节点直接包含了数据行本身，这意味着通过聚集索引可以直接访问到数据，无需再进行额外的查找
辅助索引 或 二级索引辅助索引的叶子节点包含索引键值和一个指向数据行的指针（在InnoDB存储引擎中，辅助索引的叶子节点包含的是主键值，通过主键值再访问实际的数据行）。

当使用辅助索引查询数据时就涉及到了回表查询：
select * from user where id = 11；
select * from user where name = ‘zhangsan’
第一个查询，使用的主键查询，数据直接定位到数据。取出数据。
第二个查询，使用的是辅助索引，在查询到数据后，会得到数据的id，再通过id去主键索引去查询数据再取出数据（回表）。

实践题

users 表结构如下

idnamephoneemailageprofessioncreate_time
1张三1123123123xxx@qq.com20软件2022-12-12:00:00:00
2李四223123123xbx@qq.com21土木2022-12-12:00:00:00
  1. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
  2. 删除name字段索引
  3. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
  4. 为profdssion、age、email创建联合索引。
--查看索引
show index from users

--1. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。(未指定索引默认是Btree 也就是 B+tree)
create index idx_user_name on users;

--2、删除name字段索引
drop index idx_user_name ON users;

--3.  phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
create unique index idx_user_phone on users(phone);

--4. 为profession、age、email 创建联合索引。
create index idx_user_pro_age_email on users(profession,age,email);

SQL 性能分析：

查看当前数据库的 增删改查的使用情况

show global status like 'Com_______';

在这里插入图片描述

慢查询日志：

-- 查看状态
show variables like 'slow_query_log'

目前是开启状态
目前是开启状态。如何开启，编辑my.cnf 文件 添加下面的语句，编辑完成后重启Mysql服务

vim /etc/my.cnf  #编辑my.cnf文件

#新增下面3句
slow_query_log=1  # 开启慢sql
slow-query-log-file=/www/mysql-slow.log  #存放日志的地方
long_query_time=3    # 表示超过3秒就记录下来。

#重启Mysql服务：
systemctl restart mysqld

之后如果定时查看 /www/mysql-slow.log 日志，就可以看是否有查过3秒的慢sql
下是一个例子。
在这里插入图片描述

查看sql 耗时情况

select @@have_profiling;  -- 查看数据库支付支持 
select @@profiling;   -- 查看是否开启
set profiling = 1;    -- 开启profiling

在这里插入图片描述

select * from a_img where id = 10
select * from a_img where img_url like '%24SN%'
show profiles;

通过 profiles就可以查看到最近 sql的执行时间
在这里插入图片描述

show profile cpu for query 126;   #查询执行过程耗时：

在这里插入图片描述

执行过程 Explain

 explain select * from student s, course c , student_coure sc where s.id = sc.studentid and c.id = sc.courseid;

在这里插入图片描述
EXPLAIN执行计划各字段含义:

【ld】 id相同，执行顺序从上到下; id不同，值越大，越先执行)。
【select_type】表示SELECT的类型，常见的取值有SIMPLE〈简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询）等
【key_len】 用到的索引长度，越短越好。

重点关注：【type】【possible_key】【key】
【type】表示连接类型，性能由好到差的连接类型为null > system > const > eq_ref > ref > range > index > all。
type = null 和 system: 一般不查询业务表，比如 select ‘A’
type = const: 主键或者unquie时，比如，select * from user where id= 1 或者 where idCard = 3423…
type = eq_ref 和 ref: 字段创建了索引 select * from user where name= ‘张三’ name 前提是建立了所以。
type = range ，常见于<、<=、>、>=、between等操作符（explain select * from user where age> 10 and user < 20）,前提 age 字段必须是 unique 或者时主键 对数据建立了数据结构，否则依然能是 ALL 全文扫描。
type = index: 创建了索引，但是也是全字段扫描，说明索引效果不佳。
type = all: 没有创建索引的字段查询都是ALL explain select * from user where name= ‘zhangsan’
【possible_key】 可能用的的索引，如果是null 说明没用索引
【key】实际用到的索引

没事偷着乐琅
关注
  • 19
    点赞
  • 踩
  • 13
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL高阶SQL语句
weixin_56669056的博客
07-14 905
1、按关键字排序（order by） 1、使用order by语句来实现排序 2、排序可针对一个或多个字段 3、ASC：升序，默认排序方式 4、DESC：降序 5、order by的语法结构 mysql> create school; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right
Java高阶面试题整理.pdf
12-18
本文档是Java高阶面试题的整理，涵盖了Java、JVM、MySQL、Spring等领域的知识点。 Java 1.幂等性：幂等性是指在多次执行相同的操作后，系统的状态保持不变。例如，在数据库中执行insert操作，如果第一次执行失败，...
MySQL高阶语句
Riky12的博客
07-19 1449
（2）ORDER BY 语句也可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按照第二个字段进行排序，ORDER BY 后面跟多个字段时，字段之间使用英文逗号隔开，优先级是按先后顺序而定，但order by 之后的第一个参数只有在出现相同值时，第二个字段才有意义。使用 SELECT 语句可以将需要的数据从 MySQL 数据库中查询出来，如果对查询的结果进行排序，可以使用 ORDER BY 语句来对语句实现排序，并最终将排序后的结果返回给用户。
MySQL高阶查询语句
weixin_46254171的博客
08-31 795
视图：优化操作+安全方案数据库中的虚拟表，这张虚拟表不包含真实数据。只是做了真实数据的映射。视图可以理解为镜花水月/倒影。动态保存结果集（数据）针对不同的人（不同权限），提供不同的结果集的“表”，（以表格的形式展示）功能简化查询结果集，灵活查询，可以针对不同用户呈现不同的结果集，相对有更高的安全性本质而言，视图是一种select（结果集的呈现）注意视图适合于多表连接浏览时使用，不适合增、删、改而存储过程适合于使用较频繁的sql语句，这样可以提高执行效率。
Mysql高阶语句及存储过程
2302_79748698的博客
12-30 976
mysql高阶语句case是 SQL 用来做为if，then，else 之类逻辑的关键字。select case '字段' when 值1或表达式 then 值或表达式when 值2或表达式 then 值或表达式.......else 值或表达式 end AS "字段别名"from 表;
MySQL高阶应用
qq_40391945的博客
03-21 1294
索引、视图、数据完整性约束、存储过程和存储函数、触发器、事件
MYSQL高阶语句(一)
AH99999的博客
03-26 1192
order by 排序select 字段1,字段2... from 表名 order by 字段1,字段2... asc | desc;结合where条件select 字段1,字段2... from 表名 where 字段名='字段值' order by 字段1,字段2... asc | desc;and/orselect * from kysw where score >值 and score
一篇就够mysql高阶知识总结
2401_83447580的博客
07-19 935
【代码】一篇就够mysql高阶知识总结。
mysql高阶
qq_28613337的博客
08-27 320
mysql底层数据结构及运行原理
MySQL高阶语句（一）
taoxiayu的博客
03-26 663
通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现 ，GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数（COUNT）、 求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。使用SELECT语句可以将需要的数据从MySQL数据库中查询出来，如果对查询的结果进行排序，可以使用order by语句来对语句实现排序，并最终将排序的结果返回给用户。
【檀越剑指大厂--mysql】mysql高阶篇
热门推荐
檀越的博客
08-17 1万+
为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了Write Ahead log策略即当事务提交时，先写重做日志，再修改页。当由干发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务ACID中D(Durability持久性)的要求。checkpoint的作用缩短数据库的恢复时间;缓冲池不够用时，将脏页刷新到磁盘;Lru列表不够用时,强制刷新脏页重做日志不可用时，刷新脏页。对于InnoDB存储引擎而言，其是通过LSN(LogSequence Number)来标记版本的。.........
实战派MySQL高阶应用指南.rar
12-29
《实战派MySQL高阶应用指南》是一本专为MySQL数据库管理员和开发人员设计的深度学习资料，涵盖了MySQL的高级特性和实践技巧。本书旨在帮助读者深入理解MySQL的内部机制，优化查询性能，以及掌握数据库的设计与管理...
游乐园系统 java高阶期末大作业 java gui（windowbuilder+mysql）
07-24
游乐园系统是一个基于Java GUI和MySQL数据库的高阶期末大作业，旨在让学生深入理解并实践Java编程、图形用户界面设计以及数据库管理。这个系统利用了WindowBuilder工具来创建用户友好的图形界面，使得非程序员也能...
MySQL面试题从基础到高阶66问
04-15
1. 存储结构：每个MyISAM在磁盘上存储成三个文件；InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。 2. 事务支持：...
mysql高级教程思维导图12
01-11
MySQL是一种广泛使用的开源关系型数据库管理系统（RDBMS），它以其高效、稳定和易于管理的特点在IT行业中占据了重要地位。本教程"mysql高级教程思维导图12"聚焦于MySQL的高级特性，旨在帮助开发者深化对数据库管理的...
MySQL中处理JSON数据:大数据分析的新方向
最新发布
Chujun123528的博客
08-18 754
灵活性：JSON数据类型允许你在单个列中存储复杂的数据结构，如数组和嵌套对象，这减少了需要创建多个表来存储相关信息的必要性。查询能力：MySQL为JSON数据类型提供了一系列内置的函数，如, , , , , 等，使得查询和修改JSON数据变得非常便捷。索引支持：MySQL支持为JSON文档中的特定路径创建虚拟列（Generated Columns），然后可以在这些虚拟列上创建索引，从而加速基于JSON内容的查询。性能优化：随着MySQL的不断改进，对JSON数据的处理性能也在不断提高。虽然与专门的NoSQL
MySQL的初步认识
2301_80706853的博客
08-14 321
MySQL的初步认识
MySQL源码安装与MySQL基础学习
2401_84618124的博客
08-15 250
​ 本次安装使用的是绿色硬盘版本，无需额外安装依赖环境，比较简单。：条件的筛选，可以跟多个条件，可用and或or连接，：表示0个或多个字符。：修改表名和修改表结构。
MySQL——单表查询（二）按条件查询（5）带 DISTINCT 关键字的查询
PAP
08-17 352
MySQL——单表查询（二）按条件查询（5）带 DISTINCT 关键字的查询
mysql 高阶函数
09-09
MySQL提供了许多高级函数，可以在查询和数据处理中使用。以下是一些常用的高级函数： 1. CONCAT()：用于将多个字符串连接成一个字符串。 示例：SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; 2. SUBSTRING()：用于提取字符串的子串。 示例：SELECT SUBSTRING(title, 1, 3) AS abbreviation FROM books; 3. DATE_FORMAT()：用于将日期和时间格式化为特定的格式。 示例：SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date FROM records; 4. IFNULL()：用于对 NULL 值进行处理，如果表达式为空，则返回指定的默认值。 示例：SELECT IFNULL(salary, 0) AS salary FROM employees; 5. COUNT()：用于计算满足条件的行数或非空值的个数。 示例：SELECT COUNT(*) AS total_employees FROM employees; 6. SUM()：用于计算指定列的总和值。 示例：SELECT SUM(sales) AS total_sales FROM transactions; 这些只是一些常见的高级函数，MySQL还提供了许多其他有用的函数，用于处理和转换数据。你可以参考MySQL官方文档以获取更多详细信息。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值