前言
本篇博客主要是向大家分享MySQL相关知识，希望能帮助大家，O(∩_∩)O哈哈~
MySQL事务
MySQL事务主要用于处理操作量大,复杂度高的数据。比如银行转账,订单处理等。
MySQL事务有四个关键特征:
- 原子性(Atomicity):一个事务是一个不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做。
- 一致性(Consistency):事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
- 隔离性(Isolation):一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
- 持久性(Durability):持久性也称永久性(permanence),指一个事务一旦提交,其数据改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
MySQL中事务的使用步骤: - 开启事务:START TRANSACTION 或 BEGIN;
- 执行事务中的sql语句
- 提交事务:COMMIT;
- 回滚事务:ROLLBACK;
示例:START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice'; UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob'; COMMIT;这段代码执行两条更新语句,要么全部执行,要么全部不执行,保证数据的一致性。
mysql索引详解
MySQL索引主要用于优化查询效率,可以提高查询速度。
MySQL索引的工作原理: - 索引中存储着数据表的部分字段值以及对应数据的存储位置。
- 查询时,索引能快速定位到符合条件的记录存储位置,然后从数据表中读取记录。
- 而无索引时,会全表扫描数据表来找到需要的记录。
MySQL索引类型: - BTREE索引:最常用的索引类型,它基于B树数据结构。它可以用于常规的查询和排序。
- HASH索引:只有等值查询会使用HASH索引,它基于HASH算法。查询速度很快,但无法用于排序。
- R-Tree索引:用于空间数据查询,基于R-Tree数据结构。
- Full-Text索引:用于全文索引,基于词法分析。
MySQL索引的优点: - 大大加速数据的检索速度,这也是创建索引的最主要原因。
- 可以加速表与表之间的连接,特别是在相等连接和外部连接中。
- 可以让数据库查询器选择更好的查询计划,从而提高查询效率。
MySQL索引的缺点: - 索引也需要占用空间,过多的索引会影响插入和更新速度。
- 维护索引需要消耗时间,会影响插入、更新和删除速度。
- 索引并不一定用于所有查询,只有查询涉及到索引字段时才会使用索引。
总之,MySQL索引的使用要遵循"少而精"的原则,不要过多创建索引。只有在必要时,才会真正发挥作用。为什么查询速度会慢
MySQL查询速度慢的主要原因有:
- 缺少索引:如果查询字段不存在索引,MySQL会进行全表扫描来找到匹配的行,这显然会很慢。建立合适的索引可以极大提高查询速度。
- 索引失效:虽然在字段上创建了索引,但是MySQL还是无法使用索引,从而进行全表扫描。主要原因是:
- MySQL无法使用部分索引,索引字段不是查询的条件字段。
- 使用了MySQL无法使用的索引类型,如FULLTEXT索引。
- 查询条件型 tmp LIKE '%abc%' 无法使用索引。应使用 LIKE 'abc%' 或 LIKE '%abc'。
- 类型转换导致索引失效,如将字符串字段比较时使用了整数。
- 访问同一个表两次以上,后续访问无法使用索引。
- 关联查询太多:若关联的表越多,CPU和I/O负载就越大,查询效率就越低。应避免不必要的关联,可使用临时表等方式优化。
- 没有使用EXPLAIN进行分析:不熟悉MySQL如何处理查询,执行计划不优化,导致效率低下。使用EXPLAIN可以查看查询效率,并进行优化。
- 服务器调优不当:如缓存设置不当,未使用查询缓存,线程数量设置过高等会影响查询速度。
- 死锁产生:事务之间发生死锁,会导致查询停在等待锁释放的状态,显著降低速度。需要避免死锁的发生。
- SQL语句编写不良:SQL语句书写不规范,如使用*而不是指定字段名,嵌套子查询过深等,都会影响查询效率。
综上,提高MySQL查询速度最关键的是熟悉MySQL的工作原理,制定合理的索引及SQL语句,同时也需要对服务器参数进行适当优化。但优化也需要遵循"少即是多"的原则,不要过度优化。缓存表、视图、计数器表
MySQL中常用的三种提高查询效率的方法:
- 缓存表:将频繁查询但不经常改变的数据复制到一个缓存表中,查询时直接从缓存表读取,可以省去JOIN查询和I/O操作,显著提高查询速度。
缓存表的使用步骤:
1) 识别频繁查询但不经常改变的数据
2) 创建缓存表,表结构与被缓存表相同
3) 建立缓存 mechanisim,可以使用触发器或定时任务,当主表数据变更时,更新缓存表
4) 查询时,优先查询缓存表,若无数据则从主表查询并更新缓存表 - 视图:视图是一种虚拟表,它的内容由查询定义。使用视图的好处是:
1) 简化复杂的查询,使用视图隐藏查询细节。
2) 保护基表的数据,使用视图进行查询,限制用户只能访问视图中的数据。
3) 当基表结构变化时,不影响视图查询,提高系统灵活性。
视图的使用步骤:
1) 创建视图,将基于一个或多个表的查询结果创建为视图
2) 使用SELECT * FROM view_name进行查询
3) 当基表发生变化时,视图查询结果会自动更新 - 计数器表:某些统计数据的查询特别耗性能,使用计数器表可以避免重复统计,提高查询速度。
计数器表的使用步骤:
1) 识别统计查询及统计粒度(如按天、周、月统计)
2) 创建计数器表,包含统计字段(如日期)及对应统计结果字段
3) 建立更新mechanism,定期统计和更新计数器表
4) 查询时,直接从计数器表查询,不需要再进行统计计算
5) 当基表数据变更时,及时更新计数器表维持数据准确性
总之,这三种方法的目的都是重复利用历史数据,避免重复执行耗 CPU 的查询和统计运算,从而显著提高查询效率。但也需要权衡空间占用和数据准确性。查询的执行过程
MySQL查询的执行过程主要分为5个阶段:
- 分析查询字符串:MySQL先解析查询字符串,校验语法,然后生成一棵查询解析树。
- 优化查询解析树:MySQL根据统计信息和索引,对查询解析树进行优化,生成查询执行计划。目的是找到最优执行路径,提高效率。
- 查询缓存:MySQL会判断查询结果是否在查询缓存中,如果在则直接返回结果,否则继续执行。
- 准备查询:为查询执行做准备,如为临时表分配空间,为排序和连接准备内存等。
- 执行查询计划:按照生成的查询执行计划,MySQL会依次执行表读取,过滤,排序,聚合和连接等操作,直到获得最终结果。
我们可以通过EXPLAIN命令查看MySQL如何执行一个查询,包括查询优化器选择的执行计划。主要输出内容包括:- select_type:查询类型,如简单查询、联合查询、子查询等
- table:查询涉及的表
- type:表的访问类型,如ALL(全表扫描)、index(索引扫描)、ref(索引访问)等
- possible_keys:查询可能使用的索引
- key:查询实际使用的索引
- key_len:索引长度,表示使用的索引的字节数
- ref:列与索引的比较,即哪些列或常数被用于查找索引列
- rows:根据表统计信息及索引选用情况,查询可能要读取的行数
- filtered:查询后返回的行数占需要读取的行数的百分比
- Extra:额外的信息,如Using index(使用了覆盖索引)、Using temporary(使用临时表)等
所以,熟悉MySQL的查询执行过程以及EXPLAIN命令输出,有助于我们编写高效的SQL语句和选择合适的索引。
同时也为数据库运维监控MySQL查询性能提供依据。
总结,MySQL查询的执行过程相当复杂,但理解其内部工作机制十分重要。我们应该逐步掌握,加深对MySQL的理解,从而真正成为MySQL的熟练使用者。分区表详解
MySQL分区表是一种将整个表分成更小的部分的方法。每个部分称为一个分区,并且拥有自己的存储引擎和索引。
分区表的主要好处是:- 提高查询效率:MySQL可以仅搜索相关的分区,避免扫描整个表。
- 简化数据维护:可以对每个分区进行优化,甚至独立重构分区。也可以轻易删除或清空整个分区的数据。
- 边缘值使用:可以轻松删除旧数据或清理部分临时数据等。
MySQL支持多种分区类型: - RANGE分区:根据范围分区,如按日期范围分区。
- LIST分区:根据列表分区,指定的值列表决定分区。
- HASH分区:根据用户定义的表达式的hash值分区,hash值相同的记录在同一分区。
- KEY分区:类似HASH分区,但使用MySQL内置的hash函数,性能更好。
- 子分区:将一个分区再进行细分,多层分区。
分区表的使用步骤: - 创建分区表时,在CREATE TABLE语句中添加PARTITION BY子句指定分区类型。
- 添加PARTITION定义每个分区,包括名称和值范围或列表。
- 可以指定每个分区的存储引擎、数据目录位置、索引等。
- 插入数据时,MySQL会自动根据分区定义将数据存储在相应的分区。
- 查询、更新和删除也会自动在相关分区进行。
示例:CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT ) PARTITION BY RANGE (year(hired)) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN MAXVALUE );这是一个按雇佣年份范围分区的表,包含4个分区。MySQL会根据hired字段中的年份,自动将记录存入对应的分区。
总之,MySQL分区表是一个高级特性,适用于海量数据的高性能管理。但分区也增加了表的复杂度,使用时需要慎重。InnoDB和MyISAM的数据分布对比
InnoDB和MyISAM是MySQL最常用的两种存储引擎,其数据存储有很大不同。
- 表空间:
- InnoDB:每个表都有自己的表空间,存储整个表的数据和索引。表空间由多个段组成,纵向增长。
- MyISAM:所有表数据存储在一个大的表空间,每个表仅记录其数据位置指针。表空间是一块巨大的 datafile。
- 聚集索引:
- InnoDB:有主键的表会使用主键作为聚集索引,表数据会按主键顺序存储。没有主键的表使用第一个唯一索引作为聚集索引。
- MyISAM:没有聚集索引的概念,表数据按插入顺序存储。
- 二级索引:
- InnoDB:二级索引包含索引值和主键值,通过主键值在表空间中查找行。
- MyISAM:二级索引包含索引值和数据位置指针,通过指针在表空间中查找行。
- 行格式:
- InnoDB:使用紧凑行格式,行溢出的字段会存储到溢出页。支持行锁定。
- MyISAM:使用定长行格式,每行记录长度固定。不支持行锁定,只能锁定整张表。
- 自增列:
- InnoDB:表有主键时,自增列为主键列,使用记录锁定。无主键时,自增值插入后立即持久化,使用表锁定。
- MyISAM:使用一个特殊的计数器表记录每个表的自增值,增值后立即持久化。使用表锁定。
- 事务支持:
- InnoDB:支持事务,采用多版本并发控制(MVCC)机制。
- MyISAM:不支持事务,每次查询仅返回最新提交的数据行。
综上,InnoDB和MyISAM的表结构、数据分布和性能特性差别很大。选择存储引擎时,需要根据业务需求进行权衡。一般来说,如果需要事务、行锁和外键约束,选择InnoDB;如果更注重查询效率,选择MyISAM。
但目前,InnoDB已经逐渐成为MySQL的默认存储引擎。未来,MyISAM存储引擎可能会逐步被InnoDB替代。MySQL全文索引
MySQL全文索引是一种特殊类型的索引,它允许基于词、词组或短语进行查询,而不仅仅依靠单个关键词。
全文索引使用分词技术对文本进行分析,将文本拆分为多个词或词组,然后索引这些词和词组。在查询时,全文索引可以查找到包含其中某些词或词组的记录。
MySQL全文索引的使用步骤:
- 创建表时,为需要全文索引的文本字段指定FULLTEXT类型:
CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) ) - 选用合适的分词器,MySQL提供了多个语言的分词器:
CREATE TABLE articles( ... FULLTEXT (title,body) WITH PARSER ngram )ngram是中文分词器,适用于中文文本。
- 插入数据后,需要手动建立全文索引:
ALTER TABLE articles ADD FULLTEXT INDEX `title_body_ft_idx` (`title`, `body`) - 使用MATCH()进行全文检索:
SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('数据库 MySQL');MATCH()会搜索全文索引,AGAINST指定搜索词或词组。
- 也可以使用RANK()获取匹配度排名:
SELECT *, MATCH (title, body) AGAINST ('数据库 MySQL') AS rank FROM articles ORDER BY rank DESC;MySQL全文索引的主要特点:
- 支持基于词或词组的查询,而不仅限于完全匹配。
- 使用分词器解析文本,对不同语言提供了不同的分词器。
- 需要手动建立和更新全文索引。
- 全文索引会增加索引大小和更新时间,但只针对较长的文本字段有意义。
- 不能用于排序,范围 scans,聚合, extremum values 等操作。
- 匹配度较低时,全文索引的效果可能不如普通索引。
总之,MySQL全文索引是一个方便检索文本数据的功能,但也有一定局限性。在使用时还需要权衡性能影响,并理解其工作原理。查询缓存
MySQL查询缓存用于缓存SELECT语句的查询结果,当同样的SELECT语句再次执行时,MySQL会直接从缓存中返回结果,避免再次查询数据库。
查询缓存的主要好处是: - 减少数据库的查询次数,提高查询效率。
- 避免一些昂贵的查询,如文件排序、临时表创建和多表Join等。
- 在一定程度上减少数据库负载。
MySQL查询缓存的工作原理: - 当执行一条SELECT语句时,MySQL会先检查是否命中查询缓存。
- 如果没有命中,MySQL会正常执行查询,并将查询结果存入查询缓存中,键为SQL语句文本和查询上下文环境。
- 当相同的SELECT语句再次执行时,直接从查询缓存返回结果。
- 如果查询的基表发生变化,相关缓存会被删除。MySQL会跟踪这种依赖关系,确保查询缓存中的数据是最新的。
- 可以使用SQL_CACHE和SQL_NO_CACHE指定哪些查询使用查询缓存以及不使用查询缓存。
查询缓存的局限性: - 只能缓存SELECT语句的查询结果,其他语句不会缓存。
- 如果查询中使用了不确定性函数如RAND()、NOW()等,不会被缓存。
- 如果查询中使用了MySQL外部的数据 or 文件,不会被缓存。
- 如果查询结果集太大,不会被缓存。可以使用参数query_cache_size设置最大缓存大小。
- 频繁更新的表相关的查询结果不会被长期缓存。
- 嵌套查询或者复杂JOIN查询效果不佳。
- 查询缓存使用临时表进行存储,自身也需要消耗系统资源。
所以,查询缓存更适用于相对静态数据的简单查询。在使用查询缓存时,需要考虑查询性质和服务器负载,平衡查询速度和资源消耗。
当查询缓存无效或过期时,需要及时删除相关缓存,这可以通过FLUSH QUERY CACHE命令实现。同时,也应定期检查表缓存占用情况,避免过多无效缓存消耗资源。
总之,查询缓存是一个很有用但也有局限的功能。我们应理解其工作机制,规范使用,发挥其最大效用。
（MySQL）Java学习路线（漫漫人生路，步步架构梦）Java Learning Path (Long Life Path, Step by step Structure Dream)