mysql数据库
1:什么是mysql?
MySQL是一种开源的关系型数据库管理系统(RDBMS),它主要使用SQL(结构化查询语言)作为与数据库交互的标准查询语言。
以下是MySQL的一些重要特点和组成部分:
- 数据存储和管理: MySQL使用表格来存储数据,表格由行和列组成,类似于电子表格。它支持多种数据类型,如整数、浮点数、字符串、日期等,并提供了丰富的数据操作和管理功能。
- 查询和操作: 使用SQL语言,可以对MySQL数据库中的数据进行查询、插入、更新和删除操作。SQL提供了丰富的语法和功能,允许用户灵活地操作数据。
- 性能和可扩展性: MySQL以性能和可扩展性而闻名。它被广泛应用于各种规模的应用程序,从小型网站到大型企业级系统。MySQL通过优化查询执行、索引和缓存等技术,提供高效的数据访问和处理能力。
- 安全性: MySQL提供了许多安全功能,包括用户认证、权限管理和数据加密。管理员可以创建用户账户,并根据需要分配不同级别的权限,确保数据的安全性和机密性。
- 跨平台支持: MySQL可在多种操作系统上运行,包括Windows、Linux、macOS等。它也支持多种编程语言和开发框架,如Python、Java、PHP等,使开发人员可以方便地与数据库进行交互。
- 可扩展性和高可用性: MySQL支持主从复制和集群配置,以实现数据的可扩展性和高可用性。通过复制和分布式架构,可以将负载分摊到多个服务器上,并提供故障恢复和冗余备份。
2:mysql查询:
1:函数
- 聚合函数
count()
:查询有多少该表中有多少人max()
:计算指定列的最大值,如果指定列是字符串类型则使用字符串排序运算min()
:计算指定列的最小值,如果指定列是字符串类型则使用字符串排序运算sum()
:计算指定列的数值和,如果指定列类型不是数值类型则计算结果为0avg()
:计算指定列的平均值,如果指定列类型不是数值类型则计算结果为0
- 字符串函数(String Functions):
CONCAT()
:连接字符串。SUBSTRING()
:截取子串。UPPER()
:将字符串转换为大写。LOWER()
:将字符串转换为小写。LENGTH()
:返回字符串长度。
- 日期函数(Date Functions):
NOW()
:返回当前日期时间。CURDATE()
:返回当前日期。CURTIME()
:返回当前时间。DATE_FORMAT()
:格式化日期。DATEDIFF()
:计算日期差值。
- 数学函数(Mathematical Functions):
ROUND()
:四舍五入。CEIL()
:向上取整。FLOOR()
:向下取整。ABS()
:返回绝对值。RAND()
:返回随机数。
- 逻辑函数(Logical Functions):
IF()
:根据条件返回不同的值。CASE WHEN
:多条件判断。COALESCE()
:返回第一个非NULL值。
- 条件函数(Conditional Functions):
IFNULL()
:如果为NULL,则返回指定值。NULLIF()
:如果两个参数相等,则返回NULL。
- 集合函数(Set Functions):
UNION
:合并结果集。INTERSECT
:取交集。EXCEPT
:取差集。
2:条件查询
-
使用关系运算符
示例 1:等于(=)
SELECT * FROM table_name WHERE column_name = 'value';
示例 2:不等于(<>)
SELECT * FROM table_name WHERE column_name <> 'value';
示例 3:大于(>)
SELECT * FROM table_name WHERE column_name > 10;
示例 4:小于(<)
SELECT * FROM table_name WHERE column_name < 100;
示例 5:大于等于(>=)
SELECT * FROM table_name WHERE column_name >= 50;
示例 6:小于等于(<=)
SELECT * FROM table_name WHERE column_name <= 200;
-
使用IN关键字
示例 1:使用固定值集合
SELECT * FROM table_name WHERE column_name IN ('value1', 'value2', 'value3');
示例 2:使用子查询作为值集合
SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);
示例 3:使用数字值集合
SELECT * FROM table_name WHERE column_name IN (1, 2, 3, 4, 5);
示例 4:使用字符串值集合
SELECT * FROM table_name WHERE column_name IN ('apple', 'banana', 'orange');
示例 5:使用变量作为值集合
SET @values = 'value1, value2, value3'; SELECT * FROM table_name WHERE column_name IN (@values);
示例 6:使用动态生成的子查询作为值集合
SELECT * FROM table_name WHERE column_name IN ( SELECT column_name FROM another_table WHERE condition );
-
使用BETWEEN AND关键字
示例 1:数字范围查询
SELECT * FROM table_name WHERE column_name BETWEEN 10 AND 20;
该查询将返回
column_name
列中数值在 10 和 20 之间(包括 10 和 20)的所有记录。示例 2:日期范围查询
SELECT * FROM table_name WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31';
该查询将返回
date_column
列中日期在 2023 年 1 月 1 日和 2023 年 12 月 31 日之间(包括这两天)的所有记录。示例 3:字符范围查询
SELECT * FROM table_name WHERE column_name BETWEEN 'A' AND 'F';
该查询将返回
column_name
列中字符在 A 和 F 之间(包括 A 和 F)的所有记录。示例 4:结合其他条件查询
SELECT * FROM table_name WHERE column_name BETWEEN 1000 AND 2000 AND category = 'Electronics';
该查询将返回
column_name
列中数值在 1000 和 2000 之间,并且category
列为 ‘Electronics’ 的所有记录。示例 5:排除边界值
如果要排除范围内的边界值,可以使用下面的写法:
SELECT * FROM table_name WHERE column_name > 10 AND column_name < 20;
-
使用空值
示例 1:查询空值
SELECT * FROM table_name WHERE column_name IS NULL;
该查询将返回
column_name
列中值为空的所有记录。示例 2:查询非空值
SELECT * FROM table_name WHERE column_name IS NOT NULL;
-
使用AND关键字
示例 1:使用两个条件
SELECT * FROM table_name WHERE condition1 AND condition2;
该查询将返回同时满足
condition1
和condition2
的所有记录。示例 2:使用多个条件
SELECT * FROM table_name WHERE condition1 AND condition2 AND condition3;
该查询将返回同时满足
condition1
、condition2
和condition3
的所有记录。示例 3:结合比较运算符
SELECT * FROM table_name WHERE column_name > 10 AND column_name < 20;
该查询将返回
column_name
列中数值大于 10 且小于 20 的所有记录。示例 4:结合逻辑运算符
SELECT * FROM table_name WHERE (condition1 OR condition2) AND condition3;
该查询将返回满足
(condition1 或者 condition2) 且 condition3
的所有记录。示例 5:嵌套条件查询
SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);
该查询将返回满足子查询条件的所有记录。
示例 6:使用变量作为条件
SET @value1 = 10; SET @value2 = 20; SELECT * FROM table_name WHERE column_name > @value1 AND column_name < @value2;
该查询将返回
column_name
列中数值大于@value1
且小于@value2
的所有记录。 -
使用OR关键字
示例 1:使用两个条件
SELECT * FROM table_name WHERE condition1 OR condition2;
该查询将返回满足
condition1
或condition2
中任意一个条件的所有记录。示例 2:使用多个条件
SELECT * FROM table_name WHERE condition1 OR condition2 OR condition3;
该查询将返回满足
condition1
、condition2
或condition3
中任意一个条件的所有记录。示例 3:结合比较运算符
SELECT * FROM table_name WHERE column_name < 10 OR column_name > 20;
该查询将返回
column_name
列中数值小于 10 或大于 20 的所有记录。示例 4:结合逻辑运算符
SELECT * FROM table_name WHERE condition1 AND (condition2 OR condition3);
该查询将返回满足
condition1
和(condition2 或者 condition3)
中任意一个条件的所有记录。示例 5:使用 NOT 运算符
SELECT * FROM table_name WHERE NOT condition;
该查询将返回不满足
condition
条件的所有记录。示例 6:嵌套条件查询
SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM another_table WHERE condition1 OR condition2);
该查询将返回子查询结果中满足
condition1
或condition2
中任意一个条件的所有记录。 -
使用LIKE关键字
示例 1:匹配以特定字符串开头的记录
SELECT * FROM table_name WHERE column_name LIKE 'prefix%';
该查询将返回
column_name
列中以'prefix'
开头的所有记录。示例 2:匹配以特定字符串结尾的记录
SELECT * FROM table_name WHERE column_name LIKE '%suffix';
该查询将返回
column_name
列中以'suffix'
结尾的所有记录。示例 3:匹配包含特定字符串的记录
SELECT * FROM table_name WHERE column_name LIKE '%keyword%';
该查询将返回
column_name
列中包含'keyword'
字符串的所有记录。示例 4:使用通配符 _
SELECT * FROM table_name WHERE column_name LIKE 'a_c';
该查询将返回
column_name
列中第一个字符为'a'
,最后一个字符为'c'
,且总长度为3的所有记录。 -
使用LIMIT限制查询结果的数量
示例 1:返回前 N 条记录
SELECT * FROM table_name LIMIT N;
该查询将返回
table_name
表中的前 N 条记录。示例 2:返回从 M 记录开始的 N 条记录
SELECT * FROM table_name LIMIT M, N;
该查询将返回从
table_name
表中的第 M 条记录开始的 N 条记录。示例 3:结合排序使用 LIMIT
SELECT * FROM table_name ORDER BY column_name LIMIT N;
该查询将首先按照
column_name
列进行排序,然后返回排序后的前 N 条记录。示例 4:结合条件查询和 LIMIT
SELECT * FROM table_name WHERE condition LIMIT N;
该查询将根据
condition
条件筛选记录,并返回满足条件的前 N 条记录。示例 5:使用 OFFSET
SELECT * FROM table_name LIMIT N OFFSET M;
该查询将返回从
table_name
表中的第 M 条记录开始的 N 条记录。OFFSET
关键字用于指定偏移量,表示要跳过的记录数。 -
使用GROUP BY进行分组查询
示例 1:按单个列进行分组
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
该查询将根据
column_name
列的值对记录进行分组,并计算每个分组中的记录数量。示例 2:按多个列进行分组
SELECT column_name1, column_name2, COUNT(*) FROM table_name GROUP BY column_name1, column_name2;
该查询将根据
column_name1
和column_name2
列的值对记录进行分组,并计算每个分组中的记录数量。示例 3:结合聚合函数使用
SELECT column_name, SUM(sales) FROM table_name GROUP BY column_name;
该查询将根据
column_name
列的值对记录进行分组,并计算每个分组中sales
列值的总和。示例 4:使用 HAVING 子句
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 5;
该查询将根据
column_name
列的值对记录进行分组,并筛选出分组中记录数量大于 5 的分组。示例 5:结合排序使用
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name ORDER BY COUNT(*) DESC;
该查询将根据
column_name
列的值对记录进行分组,并按照记录数量的降序对分组进行排序。 -
使用ORDER BY对查询结果进行排序
示例 1:按单个列进行升序排序
SELECT * FROM table_name ORDER BY column_name ASC;
该查询将返回
table_name
表中的记录,并按照column_name
列的值进行升序排序。示例 2:按单个列进行降序排序
SELECT * FROM table_name ORDER BY column_name DESC;
该查询将返回
table_name
表中的记录,并按照column_name
列的值进行降序排序。示例 3:按多个列进行排序
SELECT * FROM table_name ORDER BY column_name1 ASC, column_name2 DESC;
该查询将返回
table_name
表中的记录,并先按照column_name1
列的值进行升序排序,然后在相同column_name1
值的情况下,按照column_name2
列的值进行降序排序。示例 4:结合聚合函数和排序
SELECT column_name, SUM(sales) FROM table_name GROUP BY column_name ORDER BY SUM(sales) DESC;
该查询将对
table_name
表中的记录按照column_name
列进行分组,并计算每个分组的sales
列值总和,然后按照总和值进行降序排序。示例 5:结合 LIMIT 使用
SELECT * FROM table_name ORDER BY column_name DESC LIMIT 10;
该查询将返回
table_name
表中按照column_name
列值降序排序的前 10 条记录。
3:mysql调优
1:使用合适的索引:
- 选择经常用于查询的字段:
- 确定哪些字段是经常用于查询的,然后在这些字段上创建索引会提高查询性能。
- 选择区分度高的字段:
- 区分度越高的字段,其值的唯一性越高,因此在这些字段上创建索引的效果更好。例如,一个字段有大量重复的值,那么在这个字段上创建索引的效果可能不如在另一个区分度更高的字段上创建索引。
- 选择常用于 WHERE 子句和 JOIN 条件中的字段:
- 在常用于 WHERE 子句和 JOIN 条件中的字段上创建索引可以提高查询性能,因为这些字段经常用于过滤数据和联结表。
- 避免过度索引:
- 过度索引会增加写入操作的开销,并且可能导致索引失效,因此需要避免过度索引。在选择索引时,需要考虑到查询语句的实际情况,选择最佳的索引,而不是创建太多索引。
- 注意多列索引的顺序:
- 在创建多列索引时,需要注意索引列的顺序。通常应该将区分度高的列放在索引前面,这样可以更好地利用索引。
- 避免在 WHERE 子句中使用函数:
- 避免在 WHERE 子句中对字段进行函数操作,这会导致无法利用索引,影响查询性能。如果需要对字段进行函数操作,可以考虑创建虚拟列来代替。
对索引进行补充:
- 主键索引(Primary Key Index):
- 主键索引是一种唯一索引,用于唯一标识表中的每一行数据。主键索引要求索引列的值在表中是唯一的,且不允许为空。一个表只能有一个主键索引。
- 唯一索引(Unique Index):
- 唯一索引要求索引列的值在表中是唯一的,但允许包含空值。通过唯一索引可以保证数据的唯一性,避免重复数据的插入。
- 普通索引(Normal Index):
- 普通索引是最基本的索引类型,可以加快查询速度。普通索引允许索引列的值重复,适用于经常用于查询和筛选的列。
- 全文索引(Full-Text Index):
- 全文索引用于全文搜索,可以在文本数据上进行高效地全文搜索。需要注意的是,全文索引只能创建在 CHAR、VARCHAR 或 TEXT 类型的列上。
- 组合索引(Composite Index):
- 组合索引是指在多个列上创建的索引,通过将多个列组合在一起建立索引,可以加快涉及这些列的查询速度。组合索引的顺序很重要,应根据查询的频率和条件选择合适的列顺序。
- 空间索引(Spatial Index):
- 空间索引用于在空间数据类型(如 GEOMETRY、POINT)上进行空间数据操作和查询。空间索引可以支持空间数据的快速检索和分析。
- 覆盖索引(Covering Index):
- 覆盖索引是指索引包含了查询所需的所有列,可以在索引中直接获取查询结果而无需访问表数据。覆盖索引可以减少对表的访问次数,提高查询效率。
2:避免在 WHERE 子句中使用函数:
- 避免对字段进行函数操作:
- 尽量避免在 WHERE 子句中对字段进行函数操作,比如使用函数对字段进行计算、格式化或转换。这样会导致 MySQL 无法使用索引,从而需要全表扫描,降低查询性能。
- 使用索引表达式:
- 在某些情况下,可以考虑使用索引表达式来替代在 WHERE 子句中使用函数。索引表达式可以在创建索引时对字段进行计算或格式化,从而使得查询可以使用索引。
- 考虑使用存储过程或触发器:
- 如果一定需要在查询中使用函数,可以考虑将函数逻辑移至存储过程或触发器中,在数据写入时对字段进行计算或处理。这样可以避免在查询时使用函数,提高查询性能。
- 使用虚拟列:
- 可以考虑使用虚拟列来代替在 WHERE 子句中使用函数。虚拟列是在表中并不真实存在的列,通过存储表达式的计算结果来模拟出一个列,可以在查询中直接使用虚拟列而不需要使用函数。
- 优化查询逻辑:
- 如果无法完全避免在 WHERE 子句中使用函数,可以尝试优化查询逻辑,将函数的应用范围缩小,减少函数的执行次数,从而减少对查询性能的影响。
3:避免 SELECT *:
- 尽量避免使用 SELECT *,而是明确列出需要查询的字段。这样可以减少数据传输量和提高查询效率。
4:优化 JOIN 操作:
-
mysql的JSON数据类型操作:
- JSON_EXTRACT:
- 用于从 JSON 字段中提取指定的数据。语法为 JSON_EXTRACT(json_field, path)。其中,json_field 为 JSON 数据列名,path 为 JSON 数据中需要提取的路径。
- JSON_CONTAINS:
- 用于判断 JSON 字段中是否包含指定的值。语法为 JSON_CONTAINS(json_field, value[, path])。其中,json_field 为 JSON 数据列名,value 是要判断的值,path 为可选参数,表示要查找的 JSON 数据路径。
- JSON_OBJECT:
- 用于创建一个 JSON 对象。语法为 JSON_OBJECT(key1, value1[, key2, value2, …])。其中,key1、value1 表示第一个键值对,可以根据需要添加更多的键值对。
- JSON_ARRAY:
- 用于创建一个 JSON 数组。语法为 JSON_ARRAY(value1[, value2, …])。其中,value1、value2 表示数组中的元素,可以根据需要添加更多的元素。
- JSON_MERGE:
- 用于合并两个或多个 JSON 对象。语法为 JSON_MERGE(json_object1, json_object2[, json_object3, …])。其中,json_object1、json_object2 表示要合并的 JSON 对象,可以根据需要合并更多的 JSON 对象。
- JSON_SET:
- 用于更新 JSON 字段的值。语法为 JSON_SET(json_field, path, value)。其中,json_field 为 JSON 数据列名,path 表示要更新的 JSON 数据路径,value 表示要更新的值。
- JSON_REPLACE:
- 用于替换 JSON 字段中指定路径下的值。语法为 JSON_REPLACE(json_field, path, value)。其中,json_field 为 JSON 数据列名,path 表示要替换的 JSON 数据路径,value 表示要替换的值。
- JSON_SEARCH:
- 用于查找 JSON 字段中包含指定值或键的路径。语法为 JSON_SEARCH(json_field, one_or_all, search_str[, escape_char[, path]])。其中,json_field 为 JSON 数据列名,one_or_all 表示查找一个还是所有匹配项,search_str 表示要查找的字符串,escape_char 是可选参数,表示转义字符,path 是可选参数,表示要搜索的 JSON 数据路径。
- JSON_EXTRACT:
-
如何优化?
- 选择合适的 JSON 函数:
- MySQL 提供了许多用于处理 JSON 数据的函数,如 JSON_EXTRACT、JSON_CONTAINS、JSON_OBJECT 等。在使用这些函数时,应根据具体需求选择合适的函数。例如,如果需要获取 JSON 字段中的某个值,可以使用 JSON_EXTRACT 函数;如果需要判断 JSON 字段是否包含某个值,可以使用 JSON_CONTAINS 函数。
- 避免多层嵌套的 JSON 结构:
- 当 JSON 数据结构嵌套过深时,会降低查询效率,因为需要逐层解析和扫描。因此,在设计数据库表时,应尽量避免多层嵌套的 JSON 结构,或者将其拆分为多个表以便于查询。
- 使用 JSON 子句限制查询范围:
- 在执行查询时,可以使用 JSON 子句限制查询范围,从而减少扫描量和提高查询效率。例如,可以使用 WHERE JSON_CONTAINS(col_name, ‘{“key”: “value”}’) 子句来查找包含特定键值对的 JSON 字段。
- 使用索引优化 JSON 查询:
- 在 MySQL 5.7 及以上版本中,支持在 JSON 字段上创建索引。通过在 JSON 字段上创建索引,可以快速定位到满足查询条件的数据行并提高查询效率。例如,可以使用 CREATE INDEX idx_json ON table_name (JSON_EXTRACT(col_name, ‘$.key’)) 来在 JSON 字段上创建索引。
- 选择合适的 JSON 函数:
5:合理使用子查询:*
-
用作过滤条件:
-
子查询可以用作 WHERE 子句中的过滤条件,帮助筛选出符合条件的数据。例如:
SELECT * FROM table_name WHERE column_name = (SELECT sub_column FROM sub_table WHERE condition);
-
-
用作表连接条件:
-
子查询可以用作表连接条件,帮助将两个或多个表连接起来进行查询。例如:
SELECT * FROM table1 JOIN table2 ON table1.column_name = (SELECT sub_column FROM sub_table WHERE condition);
-
-
用作计算字段值:
-
子查询可以用来计算字段的值,或者对查询结果进行进一步处理。例如:
SELECT column_name, (SELECT COUNT(*) FROM sub_table WHERE condition) AS count FROM table_name;
-
-
用作 IN 或 EXISTS 子查询:
-
子查询可以用在 IN 或 EXISTS 子句中,用来检查某个值是否存在于子查询的结果中。例如:
SELECT * FROM table_name WHERE column_name IN (SELECT sub_column FROM sub_table WHERE condition);
-
-
用作插入或更新操作:
-
子查询可以用在 INSERT INTO 或 UPDATE 语句中,用来根据子查询的结果插入新数据或更新现有数据。例如:
INSERT INTO table_name (column1, column2) VALUES ((SELECT value1 FROM sub_table), (SELECT value2 FROM sub_table));
-
6:分析查询执行计划
-
使用 EXPLAIN 命令查看 SQL 查询的执行计划,了解 MySQL 是如何执行查询的,从而发现可能存在的性能瓶颈。
-
假设我们有一个名为
users
的表,包含字段id
和name
,现在我们需要查询所有名字为 “John” 的用户记录,并检查该查询的执行计划。EXPLAIN SELECT * FROM users WHERE name = 'John';
执行以上语句后,MySQL 将返回一张表格,其中包含了关于查询执行计划的各种信息,例如执行顺序、使用的索引、扫描行数等。这样的信息可以帮助我们评估查询的性能,并优化查询语句和索引设计。
7:限制返回的数据量:
在 MySQL 查询中,可以使用 LIMIT
子句来限制返回的数据量。LIMIT
子句允许指定要返回的行数范围,以控制查询结果的数量。以下是一个详细的示例演示如何在 MySQL 查询中使用 LIMIT
子句限制返回的数据量:
假设我们有一个名为 products
的表,包含字段 id
、name
和 price
,现在我们需要查询价格最高的3个产品记录。
示例:
SELECT * FROM products ORDER BY price DESC LIMIT 3;
在上面的示例中:
SELECT * FROM products
:表示从products
表中选择所有列的数据。ORDER BY price DESC
:表示按照price
字段降序排序,即价格从高到低排序。LIMIT 3
:表示限制返回的数据量为3,即只返回前3条数据。
通过以上查询语句,我们可以获取价格最高的3个产品记录。在实际应用中,根据具体需求和排序条件,灵活使用 LIMIT
子句可以方便地控制返回的数据量,避免一次性返回大量数据。
8:定期清理无用数据:
- 定期清理无用的数据和索引,避免数据库中存在过多冗余数据影响查询性能。
9:优化存储引擎配置:
- 根据实际情况选择合适的存储引擎,并根据需求调整相关参数,如缓冲池大小、日志设置等。
10:使用缓存技术:
对于频繁查询且数据不经常变动的结果,可以考虑使用缓存技术,减轻数据库的压力。
- 查询结果缓存:
- 可以使用缓存存储已经查询过的结果,下次相同查询请求时直接从缓存中获取结果,减少对数据库的访问。MySQL 提供了查询缓存功能,但在 MySQL 8.0 版本中已被移除,因为可能会导致性能问题。可以考虑使用第三方缓存组件如 Redis、Memcached 等。
- 对象缓存:
- 将数据库中的对象(如数据行、对象实例)缓存在内存中,减少对数据库的读取次数。常用的对象缓存工具包括 Redis 和 Memcached。通过对象缓存,可以减少数据库查询的次数,提高系统性能。
- 页面缓存:
- 将页面内容缓存起来,减少数据库查询和页面渲染的开销。适用于静态页面或不经常变化的页面。可以使用类似 Varnish 或 CDN(内容分发网络)来缓存页面内容。
- 查询结果集缓存:
- 缓存查询语句的结果集,当相同查询再次执行时,直接返回缓存的结果,避免重新执行查询。可以利用 Redis 等内存数据库进行查询结果集的缓存。
- 数据库连接池:
- 使用数据库连接池来管理数据库连接,避免频繁地打开和关闭数据库连接,提高连接重用率,减少连接创建的开销。
- 分布式缓存:
- 在分布式系统中,可以使用分布式缓存技术来共享缓存数据,降低单个数据库的压力。常见的分布式缓存方案包括 Redis Cluster、Memcached 分布式模式等。
通过合理使用缓存技术,可以有效减轻 MySQL 数据库的压力,提高系统的性能和稳定性。但需要注意缓存的更新策略、缓存失效处理等问题,以确保缓存数据的一致性和有效性。
4:数据类型补充
基本数据类型:
基本数据类型 | 包装数据类型 | 字节大小 | 取值范围 |
---|---|---|---|
byte | Byte | 1 byte | -128 到 127 |
short | Short | 2 bytes | -32,768 到 32,767 |
int | Integer | 4 bytes | -2,147,483,648 到 2,147,483,647 |
long | Long | 8 bytes | -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 |
float | Float | 4 bytes | 约 ±3.40282347E+38F(6-7 有效数字) |
double | Double | 8 bytes | 约 ±1.79769313486231570E+308(15 位有效数字) |
char | Character | 2 bytes | 0 到 65,535 |
boolean | Boolean | 不适用 | true 或 false |
日期与时间类型:
数据类型 | 字节数 | 取值范围 | 日期格式 | 零值 |
---|---|---|---|---|
YEAR | 1 | 1901~2155 | YYYY | 0000 |
DATE | 4 | 1000-01-01~9999-12-31 | YYYY-MM-DD | 0000-00-00 |
TIME | 3 | -838:59:59~ 838:59:59 | HH:MM:SS | 00:00:00 |
DATETIME | 8 | 1000-01-01 00:00:00~9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 0000-00-00 00:00:00 |
TIMESTAMP | 4 | 1970-01-01 00:00:01~2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 0000-00-00 00:00:00 |
5:mysql事务
-
脏读(Dirty Read):
-
当一个事务读取到了另一个事务未提交的数据时,即读取到了尚未持久化的未确认数据。
防止脏读:
- 使用事务:将读取操作和写入操作放在同一个事务中,使用事务的 ACID 特性(原子性、一致性、隔离性、持久性)来确保数据的一致性。通过事务的隔离级别可以控制事务之间的可见性,从而避免脏读。
- 设置合适的隔离级别:MySQL 支持多种隔离级别,如 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。通过设置合适的隔离级别,可以控制事务之间的可见性,防止脏读。
- 使用行级锁:MySQL 支持行级锁,在事务中可以使用行级锁来锁定需要读取或修改的数据行,防止其他事务对该行数据进行修改,从而避免脏读。
- 提高事务处理速度:尽量减少事务处理的时间,减少事务中对数据库资源的占用时间,从而降低出现脏读的可能性。
- 合理设计数据库结构:通过合理的数据库设计,尽量避免出现需要同时读取和修改的数据,减少脏读的可能性。
-
-
不可重复读(Non-repeatable Read)也叫幻读:
-
在同一个事务中,多次读取同一行数据,但每次读取得到的结果不一致。可能是由于其他事务对数据进行了修改导致的。
防止不可重复读:
- 使用事务并设置合适的隔离级别: 在事务中执行读取和更新操作,并根据需求设置合适的隔离级别。不同的隔离级别可以控制事务之间的可见性,从而避免不可重复读的问题。常见的隔离级别包括 READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
- 使用行级锁: 在事务中使用行级锁来锁定需要读取或修改的数据行,防止其他事务对该行数据进行修改,从而避免不可重复读。行级锁可以确保在事务中读取的数据不会被其他事务修改。
- 使用范围锁(Range Locks): 在某些情况下,使用范围锁可以有效地避免不可重复读的问题。范围锁可以锁定一个范围内的数据,防止其他事务在这个范围内插入新数据或修改已有数据。
- 尽量减少事务中的查询操作: 不可重复读通常发生在一个事务中多次读取同一数据时,如果能够减少事务中的查询操作,可以降低不可重复读的可能性。
- 合理设计数据库结构: 通过合理的数据库设计,避免在事务中需要多次读取同一数据或涉及到复杂的关联查询,从而减少不可重复读的风险。
-
-
丢失更新(Lost Update):
-
当两个或多个事务同时对同一数据进行修改时,可能会导致其中一个事务的修改被另一个事务覆盖,从而造成数据更新的丢失。
防止丢失更新:
- 使用事务: 使用事务将多个操作包装在一起,并确保它们以原子方式执行。这样可以保证在事务提交之前,所有的修改都是暂时性的,直到事务成功提交后才会永久生效。如果在事务中发生错误或回滚,可以避免对数据的更新操作丢失。
- 使用乐观锁或悲观锁: 乐观锁和悲观锁是两种常见的并发控制机制。乐观锁基于版本号或时间戳进行冲突检测,在更新数据时会检查是否有其他事务已经修改了数据。悲观锁则是在事务中对数据进行加锁,防止其他事务同时修改同一数据。使用乐观锁或悲观锁可以确保数据更新不会被覆盖或丢失。
- 设置合适的隔离级别: 隔离级别定义了多个事务之间的可见性和交互方式。较高的隔离级别可以提供更强的数据一致性,但也可能导致并发性能下降。选择合适的隔离级别,如 REPEATABLE READ 或 SERIALIZABLE,可以防止丢失更新。
- 使用事务日志和备份: MySQL 提供了事务日志(transaction log)和备份机制,可以用于持久化记录数据的修改。通过定期备份数据库和使用事务日志进行恢复,可以防止意外丢失更新。
- 谨慎使用自动提交模式: 在 MySQL 中,默认情况下是开启了自动提交模式,即每个 SQL 语句都会自动提交为一个独立的事务。如果需要执行多个相关操作并保持原子性,应将自动提交模式关闭,并手动控制事务的开始和提交。
- 合理设计数据库和应用逻辑: 合理设计数据库结构和应用逻辑可以降低丢失更新的风险。例如,使用适当的约束、唯一索引和外键关系可以确保数据的完整性,避免意外修改或删除操作。
-
-
死锁(Deadlock):
-
当多个事务相互等待对方释放资源时,可能会出现死锁情况,导致事务无法继续执行,必须进行人工干预才能解除死锁。
防止死锁:
- 使用事务: 使用事务来包装多个操作,确保它们以原子方式执行。这样可以避免多个事务同时修改同一数据时发生死锁。由于 MySQL 默认使用自动提交模式,因此需要显式地开启事务。
- 使用索引: 使用索引可以提高查询效率,并帮助避免死锁的发生。索引可以减少表扫描的次数,从而降低了锁定数据的时间。需要注意的是,过多或者不合适的索引也可能会导致死锁的发生。
- 尽量减少事务内的操作: 在事务中执行的操作越多,需要锁定的资源就越多,死锁的可能性也就越大。因此,应该尽量减少事务内的操作,缩短事务的执行时间。
- 按照相同的顺序访问数据: 如果多个事务需要访问相同的数据,应该按照相同的顺序进行访问,这样可以避免死锁的发生。例如,如果一个事务需要先锁定 A 表再锁定 B 表,那么另一个事务也应该按照相同的顺序进行访问。
- 使用超时机制: 如果一个事务在等待资源的时候超过了一定的时间,可以中断当前事务并回滚操作。这样可以避免长时间的等待导致死锁。
- 监控和优化数据库性能: 监控数据库的性能指标,及时发现潜在的死锁问题,并进行优化。例如,调整缓冲池大小、优化查询语句、定期清理无用的连接等。
-