MYSQL进阶

最新推荐文章于 2024-07-25 13:22:51 发布
最新推荐文章于 2024-07-25 13:22:51 发布
阅读量1.4k 收藏 30
点赞数 49
文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_75269496/article/details/140220279
版权

MYSQL进阶

存储引擎特点

  • InnoDB

    • 介绍

    InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MSQL5.5之后，InnoDB是默认的存储引擎。

    • 特点

    DML操作遵循ACID模型，支持事务；

    行级锁，提高并发访问性能；

    支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

    • 文件（注：下面的xxx是你自己建立的表的表名）

    xxx.ibd：xxx代表的是表面，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi

    )、数据和索引。

    参数：innodb_file_per_table

  • MyISAM

    • 介绍

    MyISAM是MYSQL早期的默认存储引擎

    • 特点

    不支持事务，不支持外键

    支持锁表，不支持行锁

    访问速度快

    • 文件

    xxx.sdi:存储表结构信息

    xxx.MYD:存储数据

    xxx.MYI:存储索引

  • Memory

    • 介绍

    Memory引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

    • 特点

    内存存放

    hash索引（默认）

    • 文件

      xxx.sdi: 存储表结构信息

索引

  • 介绍

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

  • 索引优缺点

    优势劣势
    提高数据检索的效率，降低数据库IO成本索引列也是要占用空间的。（但对于现在的硬件来说，空间已经不成问题）
    通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗索引大大提高了查询效率，同时也降低了跟新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低（因为需要维护b+树的结构）

  • 索引结构

    • 二叉树

​ 二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。

​ 红黑树缺点：大数据量情况下，层级较深，检索速度慢。

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

    以一颗最大度数（max-degree)为5的b-tree为例（每个节点最多存储4个key，5个指针）：

  • B+Tree

    • B+Tree特点1：所有的元素都会出现在叶子结点，上面的分页子节点起到索引作用，数据存储在叶子结点。

    • B+Tree特点1：叶子结点间会构建一个单向链表。

    • 注：MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加了一个指向相邻叶子节点的链表指针，

      形成了带有顺序的B+Tree，提高区间访问的性能。

  • Hash

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

​ 如果两个（或多个）键值，映射到同一个槽位上，他们就产生了hash冲突(也称为hash碰撞)，可以在通过在hash表下建立链表来解决冲突。

  • ​

    • hash索引的特点

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

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

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

  • ​ 存储引擎支持

    在MYSQL中，支持hash索引的是Memory引擎，而Innodb中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

补充：为什么InnoDB存储引擎选择使用B+Tree索引结构？

  1. 相对于二叉树，层级更少，搜索效率更高，且不会出现（竖着的）类似单层列表结构。
  2. 相对于B-Tree，B+Tree只有叶子结点存放数据，上面的页/块空间增大，而B-Tree叶子和非叶子节点都会存放数据，这样会导致一页中存储的键值减少，指针跟着减少，同样保存大量数据，只能增加树的高度，从而导致性能下降。且在B+Tree的叶子结点形成双向链表，便于范围搜索和排序。
  3. hash索引只支持等值匹配，不支持范围匹配以及排序操作。

索引分类

分类含义特点关键字
主键索引针对于表中主键创建的索引默认自动创建，只能有一个PRIMARY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键词，而不是比较索引中的值可以有多个，倒排索引FULLTEXT

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

分类含义特点
聚集索引将数据存储与索引放到了一块，索引结构的叶子结点保存了行数据必须有，而且只有一个
二级索引（辅助索引）将数据与索引分开存储，索引结构的叶子结点关联的是对应的主键可以存在多个

聚集索引选取规则：

  1. 如果存在主键，逐渐索引就是聚集索引
  2. 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
  3. 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

索引语法

  • 创建索引
create [unique | fulltext](指定索引类型，若不指定为常规索引) index index_name(索引名) on table_name（表名） （index_col_name,..)(为表中的哪些字段创建索引，一个索引可以为多个字段创建，即创建联合索引)
  • 查看索引
show index from table_name(表名)
  • 删除索引
drop index index_name（索引名） on table_name(表名)

SQL性能分析

  • SQL执行频率

    MySQL客户端连接成功后，通过show [sessiong | global] (带中括号表示可省略，sessiong查询当前窗口的，global) status

命令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT 的访问频次：

例：

show global status like 'com_______'
image-20240705162446477

  • 慢查询日志

    慢查询日志记录了所有执行时间超过指定参数（long_query_time 单位:秒，默认10秒）的所有sql语句的日志。

    MYSQL的慢查询日志默认没有开启，需要在MYSQL的配置文件（/etc/my.cnf)中配置如下信息：

  • profile详情

show profiles 能够在做sql优化时帮我们了解时间耗费到哪里去了，通过having_profiling参数，能看到当前MYSQL是否支持profile操作。

  • explain执行计划

Explain中各个字段的含义：

  • id

    select查询的序列号，表示查询中执行的select子句或者是操作的表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）

  • select_type

    表示select的类型，常见的取值有SIMPLE（简单表，即不用表连接或者 子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（select/where之后包含的子查询）等

  • type（重要，优化sql，主要是把这个类型向前优化，但是在实际开发业务中一般不可能达到null类型）

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

  • posssible

    显示可能应用在这张表上的索引，一个或多个。

  • key

    实际使用的索引

  • key_len

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

  • rows

    MYSQL认为必须要执行查询的次数，在InnoDB引擎的表中，是一个估算值，可能并不总是准确的。

  • filtered

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

索引使用

实验：在一个具有一千万条左右数据的表中，一个number字段，若是在没有索引的情况下查询，查询一条数据耗时大约21s左右，但若是建立好索引后，查询耗时约为0.1s,由此可见对表中字段建立索引可以打打优化性能

使用原则

  • 最左前缀法则

    如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引最左列开始，并且不跳过索引中的列。如果跳过某一列，索引将部分失效（后面的字段索引失效）（注意where条件中字段的顺序，可以不必与索引顺序一致，但是要全部存在）

  • 范围查询

    联合索引中，出现范围查询（>,<),范围查询右侧的索引会失效

例：

explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0' 
#上面的sql语句中，status的索引会失效，与其在sql中where后的位置无关，若sql为：（explain select * from tb_user where profession = '软件工程' and status = '0' and age > 30 ）status也会失效，因为在所以表中status的顺序在age后面，而且age使用了范围查询

explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0'
这一句sql，索引都不会失效，因为范围查询指的是 > 或 < 而 >= 和<= 不算范围查询

优化思路：所以我们在使用sql查询时尽量使用 >= 和 <= 而不是使用 > 和 < ，以此来防止索引范围失效

例：>30 我们可以写为 >=31

  • 索引列运算

    不要在索引列上进行运算操作，索引将会失效

  • 字符串不加引号

    若查询列内容为字符串形式，不加引号索引将会失效

  • 模糊查询

    如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

    例：

    select * from tb_user where profession like '软件%'  索引不会失效

select * from tb_user where profession like '%工程'  索引失效

select * from tb_user where profession like '%工%'  索引失效（只要前边加了%就会使索引失效）

  • or连接的条件

    用or分割开的条件，如果or前的条件中的列列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

select * from tb_user where id=10 and age =23

select * from tb_user where phone='19558955136' and age =23

​ 由于age没有索引，所以即使id，phone都有索引，但是因为age没有，所以id，phone的索引也会失效。所以要想生效，需要给age也加上索引。

  • 数据分布影响

如果MySql评估使用索引比全表扫描 慢，则不使用索引。

  • SQL提示

    sql提示，是优化数据库的一个重要手段，简单来说，就是在sql语句中加入一些人为的提示来达到优化操作的目的。

    • use index:（建议后面跟的是要用的索引，mysql不一定执行）

      select * from tb_user use index(idx_user_pro) where profession = '软件工程'

    • ignore index:（后面跟的是要忽略的索引）

      select * from tb_user ignore index(idx_user_pro) where profession = '软件工程'

    • force index:（强制使用后面跟的索引）

      select * from tb_user force index (idx_user_pro) where profession = '软件工程'

  • 覆盖索引（重要）

    尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。

    假设只有profession、age、status建立了联合索引

    select id profession from tb_user where profession = '软件工程' and age = 31 and status ='0' 不需要回表

select id,profession,age,status from tb_user where profession = '软件工程' and age = 31 and status ='0' 不需要回表

select id,profession,age,status,name from tb_user where profession = '软件工程' and age = 31 and status ='0' 需要回表

select * from tb_user where profession='软件工程' and age =31 and status='0' 需要回表

  • 前缀索引

    当字段类型为字符串（varchar，text）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

    • 语法

      create index idx_xxx on table_name(column(n)) 提取该字段的前x个值建立索引

    • 前缀长度

      可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是11，这是最好的索引选择性，性能也是最好的。

  • 单列索引与联合索引

    • 单列索引：即一个索引只包含单个列

    • 联合索引：即一个索引包含了多个列

      在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。若有热点的单列查询问题时，可以就该列单独在创建一个单列索引。

举个例子，假设有一个用户表 tb_user，包含字段 username 和 status。如果经常需要按 username 进行查询，可以先为 username 字段创建一个简单索引。如果同时经常需要按照 username 和 status 的组合进行查询，可以再创建一个联合索引 (username, status)。

简单索引和联合索引的选择取决于查询的模式和频率。简单索引可以提高单个字段的查询效率，而联合索引则适合多个字段组合条件的查询。

索引设计原则

  1. 针对于数据量较大，且查询比较频繁的表建立索引。
  2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
  3. 尽量选择区分度高的列作为索引、尽量建立唯一索引，区分度越高，使用索引的效率越高。
  4. 如果是字符串类型的字段，字段的长度较大，可以针对于字段的特点，建立前缀索引。
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询，
每天努力学Java
关注
  • 49
    点赞
  • 踩
  • 30
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql进阶学习
10-15
MySQL 进阶学习 MySQL 进阶学习旨在帮助开发者更好地掌握 SQL 语言，提高数据库管理和开发能力。本资源摘要将涵盖 MySQL 进阶学习的主要知识点，包括数据库设计、SQL 优化、查询分析、索引优化、视图和存储过程等。...
MySQL进阶
12-14
DQL:查询语句 1. 排序查询 语法：order by 子句 order by 排序字段1 排序方式1 ， 排序字段2 排序方式2… 排序方式： ASC：升序，默认的。 DESC：降序。 注意： 如果有多个排序条件，则当前边的条件值一样时，才会...
mysql进阶知识整合
09-16
MySQL进阶知识整合涵盖了多个核心领域，包括事务管理、索引、SQL优化、视图、存储过程、锁、逻辑存储引擎和事务原理等。下面将逐一详细阐述这些知识点。 1. **事务**：事务是数据库操作的基本单位，确保一组操作的...
数据库MySQL进阶实战
03-31
《数据库MySQL进阶实战》 MySQL是一个广泛应用的关系型数据库管理系统，源自瑞典MySQL AB公司，现归Oracle公司所有。作为RDBMS（关系数据库管理系统）的杰出代表，MySQL因其开源、小巧、高效的特点，在Web应用领域...
mysql进阶（二）.pdf
09-01
MySQL是一种广泛使用的开源关系型数据库管理系统，其在企业级应用中占据重要地位。本篇主要探讨多表查询、交集、并集、差集、...总之，了解和熟练掌握这些MySQL进阶知识，对于提升数据库管理和应用开发的效率至关重要。
基于JSP的高校信息资源共享平台
heye0910032的博客
07-22 685
高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。通过使用JSP技术和MySQL数据库，本系统实现了一个稳定、安全、易用的高校信息资源管理平台。虽然在开发过程中遇到了一些技术挑战，但通过不断学习和实践，最终完成了一个能够满足用户需求的系统。未来，我们将继续优化系统功能，提升用户体验，以期达到更高的应用价值和社会效益。高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。
秒杀业务: 简单梳理
m0_59925573的博客
07-23 1552
（1）在一人一单优惠券秒杀中，先是解决了因线程安全导致的超卖问题，为了满足一人一单的业务需求需要给查询订单表查询库存表上锁，但是synchronized只能满足单机环境下线程安全，在分布式环境对多个JVM需要使用分布式锁，于是手写分布式锁,但是在极端情况下，例如业务超时，会出现“误删”问题，手写锁无法解决，于是引入redisson,借助看门狗机制可以有效解决这个问题。
redis常用指令
m0_62289159的博客
07-22 809
redis常用指令
04-用户画像+sqoop使用
weixin_46567476的博客
07-21 604
sqoop的作用是实现数据的导入和导出，主要是对数据库和数据仓库之间的操作。qoop脚本就是将sqoop指令写入shell文件 后缀是 .sh。只要是支持jdbc连接的数据库都可以使用sqoop操作。
Django cursor()增删改查和shell环境执行脚本
人生苦短，何妨一试
07-21 521
在Django中，cursor()方法是DatabaseWrapper对象（由django.db.connectio提供）的一个方法，用于创建一个游标对象。这个游标对象可以用来执行SQL命令，从而实现对数据库的增删改查操作。使用cursor.execute()方法执行SQL查询语句，可以获取数据库中的数据。查询your_table表中的所有记录。执行查询后，你可以使用cursor.fetchall()或cursor.fetchone()等方法来获取查询结果。
sqlalchemy使用json_unquote函数的mysql like查询
ithongchou的博客
07-23 1206
为您自己的数据库连接信息，并根据实际表结构修改模型类。函数查询MySQL JSON字段可以通过使用。下面是一个示例，假设有一个名为。请确保替换示例代码中的数据库连接字符串(在SQLAlchemy中使用。的表，其中包含一个名为。
MongoDB教程（十五）：MongoDB原子操作
菜鸟小码的博客
07-21 862
在多线程或分布式环境中，保证数据一致性是一项重大挑战。原子操作是解决这一问题的关键技术之一，它确保一系列操作要么全部成功，要么全部失败，从而维持数据的完整性和一致性。MongoDB 提供了一系列内置的原子操作，使开发者能够轻松地在数据库层面实现事务性和数据完整性。本文将深入探讨 MongoDB 中的原子操作，包括更新、插入和删除操作的原子性，并通过具体案例代码展示如何在实际应用中运用这些原子操作。
通过Docker安装KingbaseES V8并激活开发License
weimeilayer的博客
07-24 141
首先，我们可以去KingbaseES的官网（https://www.kingbase.com.cn/xzzx/index.htm ，在软件版本中，注意下载的镜像为x86版本）下载docker镜像，下载完成后导入即可。简单介绍一下在Docker中安装人大金仓（KingbaseES V9）的过程，以及如何更换默认的License为官方提供的365天有效期的开发License。下面的安装过程都是在WSL2中完成的。我们可以通过下面的命令从宿主来直接查看KingbaseES的版本。启动容器，可以直接下面的脚本。
SuperMap GIS基础产品FAQ集锦(20240715)
SuperMap技术控
07-22 877
SuperMap GIS基础产品FAQ集锦(20240715)
雪花算法及MP实现方式
最新发布
zhuge_long的专栏
07-25 606
不过，这个主键自增长的方案好不好呢？如果发现有时钟回拨，时间很短比如 5 毫秒，就等待，然后再生成。面试常问：如果是并发量高，同一台机器一毫秒有5000个id，那么id会不会重复，不会，根据源码如果一毫秒内超过4096个id，则会阻塞到下一毫秒再生成。原生的 Snowflake 算法是完全依赖于时间的，如果有时钟回拨的情况发生，会生成重复的 ID，市场上的解决方案也是不少。如果发现有时钟回拨，时间很短比如 3 毫秒（一般大于3毫秒就不建议等待），就等待（线程睡3秒再来生成id），然后再生成。
基于Java的高校校园点餐系统
heye0910032的博客
07-23 641
随着社会的发展与进步，信息科学化管理的重要性日益凸显。本文介绍了一个基于Java技术的高校校园点餐系统，系统设计目标明确，功能模块齐全，包括用户管理、食堂管理、菜单管理等。通过B/S结构和Mysql数据库，实现了一个高效、便捷的校园点餐平台。系统经过详细测试，运行稳定，具有良好的用户体验。高校校园点餐系统的开发，不仅提升了校园餐饮服务的效率，也为学生和教职工提供了便捷的点餐体验。系统通过科学的管理方法，实现了信息的快速处理和存储。经过测试，系统运行稳定，功能齐全，具有良好的发展前景。
Mysql基础
sixudhjj的博客
07-21 634
为了实现数据持久的保存起来；使用完整的管理系统统一管理，可以实现结构化查询，方便管理；
OCC 创建方管（拉伸操作）
cangqiongxiaoye的博客
07-21 237
OCC 创建方管（拉伸操作）
mysql表设计
wwwwwww31311的博客
07-23 158
status 状态 java代码中用一个枚举类，比如我的请假表还处于草稿状态，可以是新建态，提交后处于待审批状态，审批完成则是成功态，审批拒绝就是拒绝态。start_time 开始时间 dateTime类型。end_time 起始时间 dateTime类型。employee_id 外键，和员工信息表想管理。type 请假类型，对应一个枚举类，病假，事假。create_time 请假记录的创建时间。如果让你设计一个请假表，如何设计？approve_id 外键。
mysql 进阶实战 pdf
08-16
《MySQL 进阶实战》是一本探讨MySQL数据库高级应用和实战技巧的书籍。MySQL是一种广泛使用的开源关系型数据库管理系统，在Web开发和数据分析等领域有着重要的应用。 《MySQL 进阶实战》将帮助读者理解MySQL的高级...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值