MySQL
文章平均质量分 54
拾光师
瞧一瞧,看一看,好吃还不贵。量大管饱
多年java开发经验,现征战于互联网广告行业
展开
-
MySQL双写机制
双写(doublewrite)由两部分组成,一部分是内存中的doublewrite buffer,大小为2M,另一部分是屋里磁盘上共享表空间中连续的128个页,大小也是2M。在发生数据库宕机时,可能Innodb正在写入某个页到表中,但是这个页只写了一部分,这种情况被称为部分写失效,虽然innodb会先写重做日志,在修改页,但是重做日志中记录的是对页的物理操作,但是如果这个页本身已经发生了损坏,对页进行重做是没有意义的。为了解决这个问题,提出了双写机制。原创 2024-02-13 19:14:39 · 915 阅读 · 0 评论 -
MySQL分组优化
MySQL在进行group by操作的时候要想利用索引,必须满足group by的字段必须同时存放于同一个索引,且该索引是一个有序索引,如果无法找到合适的索引可以利用的时候,就不得不先读取需要的数据,然后通过临时表来完成group by操作。紧凑索引与松散索引的区别主要在于需要在扫描索引的时候,读取所有满足条件的索引键,然后根据读取到的数据来完成group by操作得到相应的结果。当group by条件字段并不连续或者不是索引前缀部分的时候,无法使用松散索引扫描,才会尝试使用紧凑索引扫描来实现。原创 2024-02-11 20:14:38 · 461 阅读 · 0 评论 -
MySQL的MVCC机制
其中数据的隐藏列包括了隐含的自增主键(DB_ROW_ID),最近更改的事务ID(DB_TRX_ID)、undo log的指针(DB_ROLL_PTR)、索引删除标记(FLAG数据被删除时,并不是立即删除,而是打上删除标记,进行异步删除);在进行数据修改时,当前记录会进行加锁,把修改前的数据放入undo log中,通过undo log的指针与数据进行关联,如果修改失败,则恢复undo log中的数据。事务隔离级别REPEATABLEREAD时,对于快照数据,总是读取事务开始时的行数据版本。原创 2024-02-07 09:19:17 · 1861 阅读 · 1 评论 -
MySQL优化器
MySQL存储引擎中存在了一个可插拔的优化器OPTIMIZER_TRACE,可以看到内部查询计划的TRACE信息,从而可以知道MySQL内部执行过程。可以在information_schema数据库中的OPTIMIZER_TRACE查看sql执行情况。这里注意一下,我在Navicat中执行时如果两个语句分开执行会导致TRACE中内容为。需要选中两条sql语句一块执行才可以。内容很长,之后在详细说明。原创 2024-02-08 17:55:18 · 413 阅读 · 0 评论 -
MySQL查询状态
线程正在执行查询,并且将其结果都复制到一个临时表中,该状态要么是Group by操作,要么是文件排序操作,或者是union操作,如果这个状态有on disk标记,表示MySQL正在将一个内存临时表放到磁盘上。在一个查询周期中,MySQL任何时刻都有一个状态,该状态可能会变化很多次,可以使用。线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据。线程正在收集存储引擎的统计信息,并生成查询的执行计划。该线程正在等待表锁,行锁不会体现在线程状态中。线程正在执行查询或者正在将结果发送给客户端。原创 2024-02-14 09:32:35 · 416 阅读 · 0 评论 -
MySQL监控Innodb信息
Sort_merge_passes 依赖于sort_buffer_size服务器变量,sort_buffer_size来容纳排序的行块,当完成排序后,会将这些排序后的行合并到结果集中,此时就会增加Sort_merge_passes值。INNODB_LOCK_WAITS表 显示被阻塞的事务请求的锁的详细信息和统计信息,描述每个锁的状态、模式、类型和阻塞事务。Binlog_stmt_cache_use和Binlog_stmt_cache_disk_use表示非事务语句对应的度量值。原创 2024-02-12 11:36:04 · 1156 阅读 · 0 评论 -
数据备份和恢复
该命令会先lock住表,然后执行flush tables动作,该正常关闭的表正常关闭,该fsync的数据都fsync,然后通过执行OS级别的复制命令,将需要备份的表或数据库的所有物理文件都复制到指定的备份集位置。物理备份就是对数据库的物理对象所做的备份,数据库的物理对象主要由数据库的物理数据文件、日志文件以及配置文件等组成,由于不同的存储引擎存储的文件也不同,所以对于不同的存储引擎备份的文件也有所不同。存储表结构元数据的.frm文件,存储表数据的.MYD文件,存储索引数据的.MYI文件。原创 2024-02-10 19:00:39 · 1572 阅读 · 0 评论 -
MySQL分区
如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHAR,BLOB,或TEXT类型的列)作了许多修改,可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间,并整理分区数据文件的碎片。而对应用来说是透明的,从逻辑上看是只有一个表,但在物理上这个表可能是由多个物理分区组成的,每个分区都是一个独立的对象,可以进行独立处理。对于RANGE分区的表,只可以添加新的分区到分区列表的高端。提升性能,提高每个分区的读写速度,提高分区范围查询的速度。原创 2024-02-09 07:46:16 · 1278 阅读 · 0 评论 -
Innodb事务的实现
当执行事务时,会往Innodb_log_buffer日志缓冲区中插入事务日志(redo log);事务提交时不会将redo log buffer中日志写入到os buffer中,而是每秒写入os buffer并调用fsync()写入到redo log file中,如果系统崩溃,将丢失1秒的数据,性能最好,但是安全性最差。除了记录事务日志redo log外,还会记录回滚日志undo log,在进行数据修改时,由于某种原因失败了,需要进行回滚操作,可以利用undo log来将数据回滚到修改之前的样子。原创 2024-02-15 22:23:29 · 364 阅读 · 0 评论 -
Innodb索引页文件结构
为了检测页是否已经完整的写入磁盘,其只有FIL_PAGE_END_LSN部分,占了8个字节,前四个字节代表该页的checksum值,后4个字节和File Header中的FIL_PAGE_LSN相同,通过与File Header中的cheacksum和FIL_PAGE_LSN的值进行比较,来保证页的完整性。Innodb中,每个数据页中有两个虚拟的行记录,用来限定记录的边界,Infimum记录是比该页中任何主键值都小的值,Supremum指比任何可能大的值还要大的值,这两个值在页创建的时候被创建。原创 2024-02-06 10:04:51 · 782 阅读 · 0 评论 -
几个MySQL系统调优工具
基于Java的MySQL / Percona / MariaDB 索引进行插入性能测试工具。一个模块化,跨平台以及多线程的性能测试工具。Percona开发的TPC-C测试工具。几个MySQL系统调优工具。原创 2024-02-05 11:22:34 · 472 阅读 · 0 评论 -
MySQL索引结构
在默认MySQL的引擎索引中,只有MEMORY引擎支持散列数据结构,散列结构的强度可以表示为直接键查找的简单性,散列索引的相似度模式匹配查询比直接查询慢,Hash索引把数据以hash形式组织起来,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可,是无序的,因此当查找某一条记录的时候,速度非常快。根据两种结构的特性可以看到,与B树相比,B+树的数据全部都在叶子节点中,非叶子节点不保存数据,非叶子节点占用的内容更少,同样大小的文件可以存放更多的非叶子节点,原创 2024-02-04 10:38:40 · 837 阅读 · 0 评论 -
MySQL查询缓存
设置为DEMAND,可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存,这时只有加入SQL_CACHE的查询才会走缓存,其他查询则不会,这样可以非常自由地控制哪些查询需要被缓存。,Hash查找只能进行全值查找(sql完全一致),如果缓存命中,检查用户权限,如果权限允许,直接返回,查询不被解析,也不会生成查询计划,由于在缓存更新的时候会对数据加锁,所以对于读写比较频繁的系统,建议关闭缓存。如果查询结果可以被缓存,那么执行完成后,会将结果存入缓存,也会带来额外的系统消耗。原创 2024-02-03 16:24:59 · 974 阅读 · 0 评论 -
MySQL系统配置
缓冲池命中率=Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests+Innodb_buffer_pool_read_ahead+Innodb_buffer_pool_reads),在进行全表扫描时,mysql会按照数据的存储顺序依次读取数据块,每次读取的数据块首先会暂存在read_buffer_size中,当buffer空间被写满或者全部数据读取结束后,再将buffer中的数据返回给上层调用者,提高效率,其必须是4k的倍数。原创 2024-02-02 15:56:41 · 702 阅读 · 1 评论 -
MySQL数据存储
在MySQL5.5之前默认使用的是系统表空间,系统表空间是指每一个数据库所有的表数据,索引文件全部放在一个文件中,文件名为ibdataX(X代表第几个文件,从1开始),默认初始化是12M。在MySQL5.6中默认使用的是独立表空间,每一个表都会生成独立的文件来进行存储,以.frm表描述文件(存储表的结构),还有.ibd文件(存储一个表的数据内容以及索引内容)数据量非常大的时候,表做了大量删除操作后表空间中将会有大量的空隙,系统表空间无法简单的收缩文件大小,造成空间浪费,并会产生大量的磁盘碎片。原创 2024-02-01 10:21:06 · 468 阅读 · 0 评论 -
MySQL执行顺序
*order by :**按照什么样的顺序来查看返回的数据。**select:**查看结果集中的哪个列,或列的计算结果。**having:**对上面已经分组的数据进行过滤的条件。**group by:**如何将上面过滤出的数据分组。**from:**需要从哪个数据表检索数据。**where:**过滤表中数据的条件。原创 2024-01-31 09:56:11 · 494 阅读 · 0 评论 -
为什么MySQL推荐使用自增主键?
主键数据记录本身被存于主索引(一棵B+Tree)的叶子节点上,这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点),如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页;为什么MySQL推荐使用自增主键?原创 2024-01-30 10:04:48 · 477 阅读 · 1 评论 -
MySQL日志
事务提交的时候,一次性将事务中的sql语句(一个事务可能对应多个sql语句)按照一定的格式记录到binlog中,因此对于事务的提交,即便是较大的事务,提交(commit)都是很快的,但是在开启了bin_log的情况下,对于较大事务的提交,可能会变得比较慢一些,这是因为binlog是在事务提交的时候一次性写入的造成的。如果事务需要回滚,则直接利用undo log中的备份数据恢复到事务开始前的状态,根据undo log的内容做与之前相反的工作:对于每个insert,回滚时会执行delete;原创 2024-01-29 10:13:57 · 763 阅读 · 0 评论 -
查看MySql操作日志
今天在排查问题的时候发现数据对不上,怀疑是有些数据被人误删了,那如何知道是否该数据被人删除了呢。如果由于误操作数据,想要进行数据恢复的话,可以。看一下当前记录在哪个日志中了。也可以使用sql语句去查看。找到文件位置之后查看日志。查看MySql操作日志。先看一下日志是否开始了。原创 2024-01-28 16:21:44 · 463 阅读 · 0 评论 -
MySQL主从复制
由于数据的大量增加,访问量越来越复杂,有时候需要来部署多个数据库,通过数据库的复制策略,可以将一台mysql数据库服务器中的数据复制到其他的mysql数据库服务器之上,当各台数据库服务器上都包含相同数据的时候,前端应用通过访问mysql集群中任意一台服务器,都能够读取到相同的数据,这样,每台mysql服务器所需要承担的负载就会大大降低,从而提高整个系统的承载能力,达到系统扩展的目的。然后封锁并等待主服务器通知新的更新。所谓的同步复制,意思是master的变化,必须等待slave-1,slave-2,…原创 2024-01-27 14:42:00 · 920 阅读 · 0 评论 -
SQL 优化建议
当 where 子句中存在多个条件以“或”并存的时候,MySQL 的优化器并没有很好的解决其执行计划优化问题,再加上MySQL 特有的 SQL 与 Storage 分层架构方式,造成了其性能比较低下,使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果。对于同一份数据,当我们使用不同的方式去寻找其中的内容时,所需要读取的数据量可能是天壤之别,所消耗的资源相差也很大。join buffer的大小对整个join语句的消耗起到关键的作用。通过利用索引来排序的方式进行优化。原创 2024-01-26 12:19:25 · 580 阅读 · 0 评论 -
SQL优化
某条查询涉及到大表,无法进一步优化,如果返回的数据量不大且变化频率不高但访问频率很高,此时应该考虑将返回的数据放在应用端的缓存当中或者 Redis 这样的缓存当中,以提高存取速度。返回的数据需要通过网络数据包传回客户端,如果取出的Column越多,需要传输的数据量自然会越大。尽量在查询中加入一些可以提前过滤数据的条件,比如按照时间过滤数据等,可以减少数据的扫描量,对查询更友好;SQL的优化并不是一开始就进行优化的,而是需要先进行观察的,所以SQL优化的步骤应该是这样的。永远使用小结果集驱动大的结果集。原创 2024-01-25 10:43:21 · 386 阅读 · 0 评论 -
MySQL使用show profile调优
有时候使用explain执行计划发现其实是命中了索引的,但是还是很慢,此时可以使用profile功能,show profile是mysql提供的可以用来分析当前会话中语句执行的资源消耗情况,如CPU、IO、IPC、SWAP以及发生的PAGE FAULTS等,可以用于SQL调优的测量。来查询之外,还可以使用information_schema.PROFILING表来进行查询,且可以进行排序等操作。source 显示Source_function、Source_file、Source_line相关开销。原创 2024-01-24 11:05:15 · 431 阅读 · 0 评论 -
MySQL慢查询日志
可以把分析结果输出到文件中,分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,可以借助分析结果找出问题进行优化。MySQL的慢查询日志是用来记录MySQL响应时间超过阈值的语句,即为超过long_query_time值得sql语句。可以使用mysqldumpslow来进行日志分析,以一种汇总各式输出慢查询日志中的内容,将查询进行分组。查询慢查询阈值,超过该阈值才是慢查询。说明是缺少perl,进行安装即可。查询慢查询日志的存储格式。原创 2024-01-23 15:07:10 · 359 阅读 · 0 评论 -
MySQL排序优化
根据过滤条件一次取出排序字段以及客户端请求的所有其他字段的数据,并将不需要排序的字段存放在一块内存区域中,然后在sort Buffer中将排序字段和行指针信息进行排序,最后再利用排序后的行指针与存放在内存区域中的其他字段一起的行指针信息进行匹配合并结果集。4.1之前使用的是双路排序,即需要扫描两次磁盘,读取行指针和order by列,对他们进行排序,然后扫描已经排好序的列表,按照列表中的值重新从列表中读取对应的数据输出(回表),第二次读取数据的时候,是读取的排序后的所有记录,会产生大量的随机IO。原创 2024-01-22 10:30:05 · 357 阅读 · 0 评论 -
MySQL in和exists的取舍
in的参数是子查询时,会将子查询结果存储在一张临时的表中(内联视图),然后扫描整个视图。之前说过要小表驱动大表,即先遍历小表再遍历大表,接下来看一下in和exists的区别。将主查询数据放到子查询中做验证,根据验证结果来确定主查询结果的去留。根据执行顺序也就得知了什么时候该用in什么时候该用exists了。以外层表作为驱动表,外层表先被访问,适合于外表大而内表小的情况。先执行子查询,适合于外表大而内表小的情况。使用exists数据库不会生成临时的表。in和exists的取舍。原创 2024-01-21 10:35:03 · 447 阅读 · 0 评论 -
MySQL索引
查询索引时有一个Cardinality列,表示索引中不重复记录数量的预估值,该值可以用来判断是否有必要创建这个索引,Cardinality/表的行数应该尽可能的接近1,如果值太小,说明有太多的重复值,不太适合作为索引。在使用该索引查询时,要按照顺序,如果查询条件中没有使用class_id则该索引不会生效,而且在使用class_id时最好不好跳过name直接使用age条件。索引虽然提高了查询速度,但是会降低更新表的速度,在进行增删改的时候,不仅要对数据进行修改,还是对索引文件进行修改。原创 2024-01-20 14:11:28 · 797 阅读 · 0 评论 -
MySQL执行计划全面解析
using index 表示相应的select行使用了覆盖索引(覆盖索引为查询结果为索引列,不必读取数据行),防止访问数据行,速度提升。索引中使用的字节数,在不损失精度的情况下,长度越短越好(为索引值的最大可能长度,并非索引实际长度,是根据表结构计算得出,而非数据),对于确认索引的有效性以及多列索引中用到的列的数目很重要。using join buffer 在使用join进行链表查询的时候,如果表的连接条件没有用到索引,需要有一个缓冲区来存储中间结果,需要添加索引进行优化。原创 2024-01-19 10:37:29 · 444 阅读 · 0 评论 -
MySQL锁机制
由于Mysql中存在多种存储引擎,而每种存储引擎所应对的场景不同,所以各存储引擎的锁机制也有较大区别,总的来说MySQL中的锁按照类型上分为共享锁(读锁)、独占锁(写锁)、悲观锁(for update)、乐观锁(使用version,来进行CAS操作);表锁是MySQL各存储引擎中最大粒度的锁定机制,实现逻辑简单,带来的系统负面影响最小,所以获取锁和释放锁的速度很快,且由于表锁一次会将整个表锁定,所以可以很好的避免死锁,但是由于锁粒度大导致锁定资源争用的概率最高,致使并发度很低。原创 2024-01-18 10:40:33 · 338 阅读 · 0 评论 -
MySQL存储引擎
在插入值的时候,如果自动增长列不输入值,则插入的值为自动增长后的值;其中,静态型是MyISAM的默认存储格式,它的字段是固定长度的;MySQL有很多的存储引擎,每个存储引擎都有自己的特点,且存储引擎是基于表的,所以可以根据不同的应用来建立不同存储引擎表,虽然有很多存储引擎,但是使用最多的还是Innodb、MyISAM和Memory这三种存储引擎。中,每个基于MEMORY存储引擎的表实际上对应了一个磁盘文件,文件名称与表名相同,类型为frm,文件中只存储表结构,数据文件都存储在内存中,其默认使用的是。原创 2024-01-16 10:06:37 · 893 阅读 · 0 评论 -
MySQL的数据文件
mysql的数据都存放在datadir所指的位置,其中包含了mysql中创建的数据库,数据库中包含了表结构(frm文件)、表数据(myd文件)、表索引(myi文件).ibd和.ibdata 用于存储innodb存储引擎的表数据和索引信息。.myi 存储使用myisam存储引擎的表数据文件中任何索引的数据树。.frm 存放和表相关的数据信息,主要包括表结构的定义信息。.myd 存储myisam存储引擎的表数据信息。MySQL的数据文件。原创 2024-01-17 10:45:04 · 476 阅读 · 0 评论 -
MySQL体系结构
并对Query中的查询条件进行简化和转换,如去掉一些无用或者显而易见的条件,结构调整等,然后分析Query中的Hint信息,看显示Hint信息是否可以完全确定该Query的执行计划,如果没有Hint或者Hint信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据Query进行写相应的计算分析,然后得出最后的执行计划。将SQL语句进行语义和语法的分析,分解成数据结构,然后按照不同的操作类型进行分类,然后做出针对性的转发到后续步骤,以后SQL语句的传递和处理就是基于这个结构的。原创 2024-01-15 10:38:28 · 887 阅读 · 0 评论 -
MySQL如何存储表情符号?
MySQL中的utf8其实是utf8mb3,最多只用3个字节存储字符,存储不了表情。如果要支持表情,需要使用完整的utf8字符集utf8mb4,可用4个字节来存储。默认mysql的字符集是utf8,排序规则为 utf8_general_ci。utf8和utf8mb4的区别。在存储表情的时候会报。原创 2024-01-14 17:54:42 · 1068 阅读 · 0 评论 -
连接数问题
在使用mysql时有时候会报too many connections错误,这是连接数过多导致的,然后就会去修改max_connections参数,但是这个参数也不能无上限的增大,容易造成机器内存不足,还是需要找到为什么会有这么多连接的原因。原创 2024-01-12 23:42:49 · 386 阅读 · 0 评论 -
MySQL忽略大小写问题
然后查了一下资料,说是utf8_general_ci中的_ci是指忽略大小写Case-insensitive,但是我只设置了字符编码utf8,没有设置为utf8_general_ci呀,原来utf8默认的是就是utf8_general_ci。今天使用MySQL查询时遇到一个问题,使用一个字符串去查询时,查出来两条数据,但是查到的数据大小写并不一样。果然是utf8_general_ci,好吧,那么如何修改呢?我查到了name为a和A的两条数据,这就很奇怪,难道mysql会忽略大小写吗?原创 2024-01-11 11:22:46 · 506 阅读 · 0 评论 -
MySQL自动备份脚本
mysqldump命令将数据库中的数据备份成一个文本文件,表的结构和数据将存储在生成的文本文件中。将备份出来的数据还原到某个数据库中。备份一个数据库下的多个表。MySQL自动备份脚本。也可以登入数据之后进行。原创 2024-01-10 10:15:35 · 716 阅读 · 0 评论 -
MySQL配置文件读取顺序
在启动MySQL数据库时可以没有配置文件,这种情况下,MySQL会按照编译时的默认参数设置启动实例。按照顺序读取配置文件,且如果多个配置文件中有相同的参数,则以读取到的最后一个配置文件为准。原创 2024-01-09 10:13:47 · 437 阅读 · 0 评论 -
MySQL使用count进行行数统计
https://zhhll.icu/2021/数据库/关系型数据库/MySQL/基础/20.MySQL count/](https://zhhll.icu/2021/数据库/关系型数据库/MySQL/基础/20.MySQL count/)在使用count进行数据统计行数时,有时候会使用count(*),有时候会使用count(1),有时候会使用count(列名),那么这些有什么不同呢。当使用列名作为参数时,count函数会自动忽略null值,而使用*或者常量时,就会包含null值。原创 2024-01-08 10:04:54 · 415 阅读 · 0 评论 -
MySQL高级语句
【代码】MySQL高级语句。原创 2024-01-07 10:34:10 · 313 阅读 · 0 评论 -
MySQL数据类型
char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符,不管实际存放多长的数据,都会存放30个字符,不够通过空格补上。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。原创 2024-01-06 16:55:44 · 824 阅读 · 0 评论