search

xuanyuanaosheng / xuanyuanaosheng.github.io

个人网站：灵梦缘
https://xuanyuanaosheng.github.io/
0 stars 0 forks source link

数据库管理员基础知识 #16

Open xuanyuanaosheng opened 1 week ago

xuanyuanaosheng commented 1 week ago

数据库基础知识

1. 什么是数据库范式？请列出常见的范式类型？

答案：

2. 数据库的索引是什么？它的作用是什么？

答案：

3. 什么是事务？事务的四大特性（ACID）是什么？

答案：

4. 数据库中的锁机制是什么？锁的类型有哪些？

答案：

5. 如何优化 SQL 查询的性能？

答案：

  1. 使用合适的 索引。
  2. 避免 SELECT *，只查询所需的列。
  3. 使用 EXPLAIN 分析查询的执行计划，优化其查询路径。
  4. 避免过多的连接（JOIN），尤其是大表连接。
  5. 使用适当的 分区 或 分表 来减少数据扫描量。
  6. 定期更新统计信息，以便优化器能选择最优的执行计划。

6. 什么是视图？视图和表的区别是什么？

答案：

7. 什么是数据库的备份策略？你会如何设计数据库的备份策略？

答案：

8. 如何保证数据库的高可用性？

答案：

9. Explain SQL 的作用是什么？如何使用 EXPLAIN 分析查询性能？

答案：

10. MySQL 中 InnoDB 和 MyISAM 的区别是什么？

答案：

11. 如何处理数据库的死锁问题？

答案：

12. 什么是冷热数据分离？

答案：

13. 什么是分区表？什么时候使用分区？

答案：

14. 你如何确保数据库的安全性？

答案：

  1. 使用 强密码策略，确保用户密码复杂且定期更换。
  2. 配置 最小权限原则，确保用户只拥有完成任务所需的最低权限。
  3. 使用 防火墙 和 网络隔离，确保数据库只能通过授权的 IP 地址访问。
  4. 定期进行 数据备份 并将备份文件加密。
  5. 使用 SSL/TLS 加密数据库的网络通信，防止数据泄露。
xuanyuanaosheng commented 1 week ago

关系型数据库和大数据的区别

总结

根据不同的业务需求和场景选择适合的技术方案，将大大提升系统的效率和稳定性。

关系型数据库（RDBMS）和大数据系统是两种截然不同的技术，主要用于处理和存储数据，它们的设计理念、使用场景、架构、性能和数据处理方式都存在较大差异。以下是它们的区别：

1. 数据类型和结构

2. 数据存储方式

3. 数据处理模式

4. 扩展性

5. 处理数据量的能力

6. 性能优化

7. 使用场景

8. 维护和管理

xuanyuanaosheng commented 1 week ago

数据库索引什么情况下会失效？

数据库中的索引可以显著提高查询效率，但在某些情况下，索引可能会失效，导致数据库无法利用它来加快查询。这种索引失效的情况会导致查询效率下降，使用全表扫描而不是索引扫描。以下是常见的导致索引失效的情况：

总结

导致数据库索引失效的情况有很多，常见的包括使用OR、函数或运算、范围查询、隐式类型转换等。要确保索引有效，建议在设计表结构和查询语句时遵循以下原则：

通过合理的索引设计与查询优化，能够显著提高数据库的查询效率。

1. 使用OR条件

当查询中的多个条件通过OR连接时，可能会导致索引失效，尤其是当这些条件中的某些字段没有索引时。数据库在这种情况下无法有效使用索引，而是会执行全表扫描。

例子：

   SELECT * FROM users WHERE name = 'John' OR age = 30;

其中，name有索引，但age没有索引。这样OR条件可能会导致索引失效。

解决方法： 将查询分成多个使用UNION的子查询。例如：

   SELECT * FROM users WHERE name = 'John'
   UNION
   SELECT * FROM users WHERE age = 30;

2. 使用%号开头的LIKE查询

当使用LIKE进行模糊查询时，如果查询条件以%开头，会导致索引失效，因为无法确定前缀来进行索引扫描。

例子：

   SELECT * FROM users WHERE name LIKE '%John%';

在这种情况下，MySQL无法通过索引来查找相关记录，而是需要对全表进行扫描。

解决方法： 避免%放在字符串开头。例如：

   SELECT * FROM users WHERE name LIKE 'John%';

3. 隐式数据类型转换

如果查询中使用的字段类型和索引字段的类型不一致，会发生隐式类型转换，导致索引失效。

例子： 假设age字段是整数类型，查询中传入了字符串类型：

   SELECT * FROM users WHERE age = '30'; -- 索引失效

解决方法： 确保查询中的条件类型与索引字段的类型一致：

   SELECT * FROM users WHERE age = 30; -- 索引有效

4. 函数或运算操作

当对索引列使用函数或进行计算时，索引将失效，因为数据库需要对每一行的结果进行计算，而无法利用预排序的索引。

例子：

   SELECT * FROM users WHERE YEAR(birthdate) = 1980;
   SELECT * FROM users WHERE age + 1 = 30;

解决方法： 直接在原始字段上做比较，而不是使用函数或运算：

   SELECT * FROM users WHERE birthdate BETWEEN '1980-01-01' AND '1980-12-31';
   SELECT * FROM users WHERE age = 29;

5. 使用!=、<>、NOT IN、NOT LIKE

当使用这些不等或NOT运算符时，通常会导致索引失效，因为数据库无法有效利用索引来排除特定的值。

例子：

   SELECT * FROM users WHERE age != 30;

解决方法： 尽量避免使用!=、<>等运算符，考虑使用正向逻辑或重构查询。

6. 使用IS NULL或IS NOT NULL

某些数据库引擎在使用IS NULL或IS NOT NULL判断时，无法使用索引。

例子：

   SELECT * FROM users WHERE age IS NULL;

解决方法： 可以通过额外的字段来标记数据的存在性，或通过适当的索引选项（例如稀疏索引）优化这种查询。

7. 前导列没有使用到（复合索引）

对于复合索引（多列索引），如果查询中没有使用到索引的前导列，索引会失效。

例子： 假设有一个复合索引 (name, age)，如果只查询age，索引可能无法使用。

   SELECT * FROM users WHERE age = 30; -- 索引失效

解决方法： 确保查询条件中包含复合索引的最左边的列。例如：

   SELECT * FROM users WHERE name = 'John' AND age = 30; -- 索引有效

8. 数据更新频繁，索引碎片过多

如果表数据频繁更新或删除，索引可能会产生碎片，导致查询效率下降。

解决方法： 定期对数据库进行重建索引或优化表操作。例如：

   OPTIMIZE TABLE users;

9. 范围查询导致复合索引失效

如果使用了范围查询（例如<, >, BETWEEN），对于复合索引来说，后面的列将不会被使用。

例子： 假设有一个复合索引 (name, age)，查询中包含范围条件：

   SELECT * FROM users WHERE name = 'John' AND age > 30;

由于age是范围查询，age索引将失效。

解决方法： 通过优化查询条件，避免使用范围查询。或重构索引顺序，保证最左列为需要精确匹配的字段。

10. 表中数据量小

当表中的数据量非常小（例如仅有几行数据），数据库可能认为扫描整个表比使用索引更高效。因此，即使有索引，数据库也可能不使用索引。

解决方法： 无需处理。数据库系统自动优化这种情况。

xuanyuanaosheng commented 1 week ago

数据库视图

什么是数据库视图？

视图（View）是数据库中一种虚拟表，它基于数据库中已经存在的一个或多个表（或视图）创建的，用户可以像使用表一样使用视图进行查询。视图本身不存储数据，它是一个逻辑结构，当你查询视图时，系统会通过视图的定义（通常是一条SELECT语句）从基础表中获取数据并返回。

视图的主要作用是简化复杂查询、增强数据安全性和提高复用性。它让用户只需通过视图查询，而不必关注底层数据的复杂结构。

视图的特点

  1. 虚拟表：视图并不直接存储数据，它从基础表中动态获取数据。
  2. 提高数据安全性：视图可以控制用户能看到的表的数据，隐藏敏感信息，限制对表的直接访问。
  3. 简化查询：将复杂的查询封装成视图，方便开发人员和用户复用。
  4. 逻辑隔离：视图使得用户与表结构隔离，表结构的变化对视图不会直接产生影响。

视图的使用场景

  1. 数据安全：将敏感数据隐藏在视图之外，用户只能通过视图访问部分数据。
  2. 简化复杂查询：把复杂的多表联接、汇总等操作定义为视图，用户可以直接查询视图而不必每次编写复杂的SQL语句。
  3. 逻辑分离：将底层数据库的复杂逻辑封装在视图中，上层应用程序无需感知数据源的复杂性。
  4. 数据汇总和报表：视图可以用于汇总数据，创建报表视图，方便生成定制化的报表。

创建视图的语法

视图可以通过 CREATE VIEW 语句来创建。视图的定义是一个SELECT查询，可以包含各种查询操作，包括连接、聚合、子查询等。

基本语法：

CREATE VIEW 视图名称 AS
SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件;

示例：创建视图

假设有两个表 employees（员工表）和 departments（部门表）。

employees 表结构：

id | name  | department_id | salary
---+-------+---------------+--------
 1 | Alice |             1 |   5000
 2 | Bob   |             2 |   6000
 3 | Carol |             1 |   5500

departments 表结构：

id | department_name
---+-----------------
 1 | HR
 2 | IT

创建视图：查看员工和部门信息

CREATE VIEW employee_department_view AS
SELECT e.name AS employee_name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

现在，employee_department_view 视图相当于一个虚拟表，执行以下查询会返回员工及其部门名称：

SELECT * FROM employee_department_view;

返回结果：

employee_name | salary | department_name
--------------+--------+----------------
Alice         |   5000 | HR
Bob           |   6000 | IT
Carol         |   5500 | HR

修改视图

要修改视图，可以使用 CREATE OR REPLACE VIEW 语句，这允许你重新定义视图，而不必先删除它。

示例：修改视图

CREATE OR REPLACE VIEW employee_department_view AS
SELECT e.name AS employee_name, e.salary
FROM employees e
WHERE e.salary > 5000;

删除视图

如果不再需要某个视图，可以使用 DROP VIEW 删除它。

删除视图语法：

DROP VIEW 视图名称;

示例：

DROP VIEW employee_department_view;

视图的优缺点

优点：

  1. 数据安全：视图可以限制用户访问数据的权限，通过视图暴露部分数据，而隐藏敏感数据。
  2. 简化复杂查询：视图可以封装复杂的SQL查询，使得复杂查询变得简单易用。
  3. 提高可维护性：通过视图分离逻辑层和数据层，如果底层表结构发生变化，可以只调整视图而不必修改所有查询。
  4. 数据抽象：视图为应用程序提供了数据的抽象，减少了对表结构的依赖性。

缺点：

  1. 性能问题：由于视图是动态查询，查询视图时会执行视图中的SQL，复杂的视图可能会带来性能开销。
  2. 不适合频繁更新：视图通常用于只读数据的抽象，而不是频繁的更新和插入操作。
  3. 无法包含索引：视图是虚拟表，本身不能创建索引。如果底层查询性能不佳，视图不会自动优化。

总结

视图是数据库中非常有用的虚拟表结构，可以简化查询、提高数据安全性，并且有助于更好的逻辑隔离。通过视图，开发人员能够创建一个更易用、抽象化的查询接口，从而减少复杂度并提高代码的可读性和可维护性。

xuanyuanaosheng commented 1 week ago

参考：

  1. mysql学习https://awesome-programming-books.github.io/mysql/MySQL%E5%BF%85%E7%9F%A5%E5%BF%85%E4%BC%9A.pdf
  2. http://ourmysql.com/archives/1461

工具：

  1. 一个可以根据需要自动生成mysql 配置文件的网站： https://imysql.com/my-cnf-wizard.html
xuanyuanaosheng commented 4 days ago

常用脚本

  1. 安装Mysql 8.0的脚本，对于生产环境推荐使用：Percona系列，其官方网站：https://www.percona.com/ 
    
#!/bin/bash

设置数据目录位置，端口，server_id ，内存等。

BASICDIR=/opt/mysql PORT=3306

BASEDIR=${BASICDIR}/percona8.0 DATADIR=${BASICDIR}/data BACKDIR=${BASICDIR}/backup DBADIR=${BASICDIR}/.dba

BASEDIR=${BASICDIR}/percona8.0

DATADIR=/data/mysql/data${PORT}

BACKDIR=/data/backup

DBADIR=${BASICDIR}/.dba

SERVERID=101 INNODBMEMORY=4G DATETIME=`date "+%Y%m%d%H%M%S"`

设置账号、密码

ROOTPASSWD=admin123 REPLUSER=repl REPLUSERPASSWD=repl123 MONITORUSER=db_monitor MONITORPASSWD=monitor123

删除系统自带的mariadb包

MARIADB=rpm -qa | grep mariadb if [[ ${MARIADB} != "" ]];then rpm -e --nodeps ${MARIADB} fi

下载二进制包，并创建相关文件夹

echo "----------开始创建数据目录，下载MySQL二进制包----------" mkdir -p ${BASICDIR} mkdir -p ${BACKDIR} mkdir -p ${DBADIR} cd ${BASICDIR} wget http://10.67.208.68:80/Percona-Server-8.0.15-6-Linux.x86_64.ssl101.tar.gz >/dev/null 2>&1 tar -zxf Percona-Server-8.0.15-6-Linux.x86_64.ssl101.tar.gz mv Percona-Server-8.0.15-6-Linux.x86_64.ssl percona8.0 echo "----------开始创建数据目录，下载MySQL二进制包成功----------"

创建相关用户，更改文件夹权限

groupadd mysql useradd -g mysql -s /sbin/nologin -d ${BASICDIR} -MN mysql mkdir -p ${DATADIR}/{data,logs,tmp,audit,binlogs} chown -R mysql:mysql ${BASEDIR} chown -R mysql:mysql ${DATADIR} chown -R mysql:mysql ${BACKDIR} echo "----------创建mysql用户及用户组，更改文件夹权限成功----------"

生成配置文件

cat > /etc/my.cnf << EOF [client] port = ${PORT} socket = /tmp/mysql.sock

[mysql] prompt="\u@\h:\p [\d]>\ "

prompt="\u@ \R:\m:\s [\d]> "

no-auto-rehash

[mysqld] user = mysql port = ${PORT} basedir = ${BASEDIR} datadir = ${DATADIR}/data socket = /tmp/mysql.sock pid-file = .pid character-set-server = utf8mb4 skip_name_resolve mysqlx = 0

若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数

default_time_zone = "+8:00"

open_files_limit = 65535 back_log = 1024 max_connections = 512 max_connect_errors = 1000000 table_open_cache = 128 table_definition_cache = 128 table_open_cache_instances = 64 thread_stack = 512K external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 768 interactive_timeout = 600 wait_timeout = 600 tmp_table_size = 32M max_heap_table_size = 32M slow_query_log = 1 log_timestamps = SYSTEM slow_query_log_file = ${DATADIR}/logs/slow.log log-error = ${DATADIR}/logs/error.log long_query_time = 0.1 log_queries_not_using_indexes =1 log_throttle_queries_not_using_indexes = 60 min_examined_row_limit = 100 log_slow_admin_statements = 1 log_slow_slave_statements = 1 server-id = 122 log-bin = ${DATADIR}/binlogs/binlog sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G

注意：MySQL 8.0开始，binlog_expire_logs_seconds选项也存在的话，会忽略expire_logs_days选项

expire_logs_days = 7

master_info_repository = TABLE relay_log_info_repository = TABLE gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN' binlog_format = row binlog_checksum = 1 relay_log_recovery = 1 relay-log-purge = 1 key_buffer_size = 32M read_buffer_size = 8M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 lock_wait_timeout = 3600 explicit_defaults_for_timestamp = 1 innodb_thread_concurrency = 0 innodb_sync_spin_loops = 100 innodb_spin_wait_delay = 30

transaction_isolation = REPEATABLE-READ

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = ${INNODB_MEMORY} innodb_buffer_pool_instances = 4 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 2G innodb_log_files_in_group = 2 innodb_max_undo_log_size = 4G innodb_undo_tablespaces = 95

根据您的服务器IOPS能力适当调整

一般配普通SSD盘的话，可以调整到 10000 - 20000

配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000

innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 innodb_flush_sync = 0 innodb_flush_neighbors = 0 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 4000 innodb_checksum_algorithm = crc32 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_online_alter_log_max_size = 4G innodb_stats_on_metadata = 0

some var for MySQL 8

log_error_verbosity = 3 innodb_print_ddl_logs = 1 binlog_expire_logs_seconds = 604800

innodb_dedicated_server = 0

innodb_status_file = 1

注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快

innodb_status_output = 0 innodb_status_output_locks = 0

performance_schema

performance_schema = 1 performance_schema_instrument = '%memory%=on' performance_schema_instrument = '%lock%=on'

innodb monitor

innodb_monitor_enable="module_innodb" innodb_monitor_enable="module_server" innodb_monitor_enable="module_dml" innodb_monitor_enable="module_ddl" innodb_monitor_enable="module_trx" innodb_monitor_enable="module_os" innodb_monitor_enable="module_purge" innodb_monitor_enable="module_log" innodb_monitor_enable="module_lock" innodb_monitor_enable="module_buffer" innodb_monitor_enable="module_index" innodb_monitor_enable="module_ibuf_system" innodb_monitor_enable="module_buffer_page" innodb_monitor_enable="module_adaptive_hash"

mgr confir

binlog_checksum=NONE

open parallel slave

slave_parallel_workers=8 slave_preserve_commit_order=1 slave_parallel_type=LOGICAL_CLOCK

IF VERSION BELOW 8.0.3

master_info_repository=TABLE relay_log_info_repository=TABLE transaction_write_set_extraction=XXHASH64

[mysqldump] quick max_allowed_packet = 32M

EOF

echo "----------配置文件创建成功----------"

初始化MySQL

${BASEDIR}/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure >/dev/null 2>&1 echo "----------数据库初始化成功----------"

启动数据库

${BASEDIR}/bin/mysqld_safe --defaults-file=/etc/my.cnf >/dev/null 2>&1 & sleep 10 echo "----------数据库启动成功----------"

设置root用户密密码，以及复制,监控账号等

${BASEDIR}/bin/mysql -uroot << EOF alter user root@localhost identified by '${ROOTPASSWD}' ; create user ${REPLUSER}@'%' identified with mysql_native_password by '${REPLUSERPASSWD}' ; grant replication slave on . to repl@'%'; create user ${MONITORUSER}@'%' identified with mysql_native_password by '${MONITORPASSWD}' ; grant SELECT, PROCESS, REPLICATION CLIENT,GROUP_REPLICATION_ADMIN ON . TO db_monitor@'%'; flush privileges; EOF

echo "----------用户创建成功----------"

cat > ${DBADIR}/init.sql << EOF -- 设置root密码 alter user root@localhost identified by '${ROOTPASSWD}' ;

-- 复制用户 create user ${REPLUSER}@'%' identified with mysql_native_password by '${REPLUSERPASSWD}' ; grant replication slave on . to repl@'%';

-- 监控用户 create user ${MONITORUSER}@'%' identified with mysql_native_password by '${MONITORPASSWD}' ; grant SELECT, PROCESS, REPLICATION CLIENT,GROUP_REPLICATION_ADMIN ON . TO db_monitor@'%'; flush privileges; EOF

echo "----------初始化用户备份成功----------"

设置环境变量

echo "export PATH=$PATH:$BASEDIR/bin" >> /etc/profile source /etc/profile

设置备份脚本

cat > ${DBADIR}/backup.sh << EOF

!/bin/bash

DATETIME=date "+%Y%m%d_%H%M%S" mysqldump -uroot -p$ROOTPASSWD -A -R -E --triggers --single-transaction >> ${BACKDIR}/backup_\${DATETIME}.sql EOF echo "----------备份脚本创建成功----------" echo "------------------------------------" echo "----------mysql 安装成功！----------" echo "------------------------------------"

设置本地快捷登录

echo "alias dba='mysql --login-path=root'" >> /etc/profile

echo "----------设置快捷登录，请执行以下命令，并输入密码----------" echo "----------mysql root用户密码为: ${ROOTPASSWD} ----------" echo "mysql_config_editor set --login-path=root --user=root --password"

xuanyuanaosheng commented 4 days ago

参考文档

  1. https://meethigher.top/blog/2024/no-common-used-sql/