超详细！ES 安装配置全攻略及 PHP 简单使用
遭遇了 “黑天鹅” 事件近期真是遭遇了 “黑天鹅” 事件，公司因卷入非法问题，面临一系列棘手处理，无奈之下只得遣散全体员工，我也因此瞬间失业。原本按部就班的工作节奏戛然而止，每天醒来，看着毫无安排的一天，心里满是茫然与失落。与其在这种不确定中焦虑徘徊，倒不如主动出击，给自己找点事儿做。我打算投身到文档撰写中，既能有效填补空闲时间，让生活重回正轨，也能借此梳理过往积累的知识与经验，为未来求职筑牢根基，让这段意外失业的日子，不至于虚度 。回顾上一篇我们一同探索了 Sphinx 这个强大的全文搜索引擎，从它的功能特性到安装配置，相信大家对如何利用 Sphinx 搭建高效搜索系统有了清晰认知。不过技术的世界从不止步，在搜索领域，还有一位 “大咖”——Elasticsearch（简称 ES），它凭借分布式、高扩展、实时搜索等卓越优势，在企业级应用中大放异彩。这一篇，就让我们把目光聚焦 ES，深入剖析它的安装配置流程，探寻如何在 PHP 开发环境里，巧妙借助 ES 的力量，实现更智能、更高速的数据检索，解锁不一样的搜索体验。什么是Elasticsearch？它有什么优缺点？和 sphinx 相比，差异在哪里？Elasticsearch 是基于 Lucene 的分布式开源搜索引擎，用于全文搜索、数据分析等。优点是分布式、实时性强、功能丰富。缺点是硬件要求高、索引维护成本高。与 Sphinx 相比，ES 分布式和扩展性更好，功能更全面；Sphinx 在特定场景性能较好，配置相对简单，更适合中小规模数据。安装和配置Elasticsearch官网下载地址服务器：CentOS7,Java版本21.0.2(Elasticsearch基于Java开发)没有Java环境的自行去下载合适的版本，并把java加到环境变量中。sudo vim /etc/profile export JAVA_HOME=/www/server/java/jdk-21.0.2 export PATH=$JAVA_HOME/bin:$PATH安装# 创建存放Elasticsearch软件目录 mkdir -p /usr/local/es/ # 进入/usr/local/es/文件夹 cd /usr/local/es/ # 下载对应的glibc版本的sphinx软件包 wget https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-8.17.2-linux-x86_64.tar.gz # 解压 tar -zxvf elasticsearch-8.17.2-linux-x86_64.tar.gz # es 禁止以root用户运行，所以我们创建一个组和用户，用于启动es # 创建组 elasticsearch groupadd elasticsearch # 创建用户，指定用户所在的组 useradd youer -g elasticsearch # 设置用户密码 passwd youer配置# 进入/usr/local/es/elasticsearch-8.17.2/config 配置目录，修改以下配置 cd /usr/local/es/elasticsearch-8.17.2/config vim elasticsearch.yml # 找到以下配置并且修改 =============================================================================== # 集群名称，用于标识一个 Elasticsearch 集群， # 同一集群内的节点需要具有相同的集群名称才能相互通信并组成集群。 # 这里设置为 "my-es"，你可以根据实际情况修改为合适的名称。 cluster.name: my-es # 节点名称，用于在集群中唯一标识一个节点。 # 每个节点都应有一个独特的名称，这里设置为 "node-1"， # 可以根据实际需求进行调整。 node.name: node-1 # 绑定的网络地址，"0.0.0.0" 表示该节点将监听所有可用的网络接口， # 使得其他机器可以通过网络访问该 Elasticsearch 节点。 # 这在生产环境中需要谨慎配置，以确保安全性。 network.host: 0.0.0.0 # HTTP 服务端口，Elasticsearch 通过此端口提供 HTTP 接口供外部访问， # 这里设置为 9200，是 Elasticsearch 默认的 HTTP 端口， # 可根据实际情况修改，但要避免与其他服务端口冲突。 http.port: 9200 # 种子节点列表，用于在节点启动时发现集群中的其他节点。 # 这里指定了 "127.0.0.1"，表示在本地寻找其他节点。 # 在分布式集群环境中，应填写其他实际节点的 IP 地址。 discovery.seed_hosts: ["127.0.0.1"] # 初始化主节点列表，用于在集群启动时确定哪些节点有资格成为主节点。 # 这里设置为 ["node-1"]，意味着 node-1 节点有资格成为主节点。 # 主节点负责管理集群的状态和元数据等重要任务。 cluster.initial_master_nodes: ["node-1"] # 这一行被注释掉了，原本是用于控制系统调用过滤器的配置。 # bootstrap.system_call_filter: false # 禁止节点在启动时锁定内存。 # Elasticsearch 默认会尝试锁定内存以提高性能，但在某些环境下可能会失败。 # 设置为 false 表示不进行内存锁定，可根据实际情况调整。 bootstrap.memory_lock: false # 启用 HTTP CORS（跨域资源共享）功能。 # 当设置为 true 时，允许来自不同域名的请求访问 Elasticsearch 的 HTTP 接口。 # 这在浏览器或其他跨域访问场景中很有用。 http.cors.enabled: true # 允许的跨域来源，这里设置为 "*" 表示允许所有来源的请求。 # 在生产环境中，建议明确指定具体的域名，以提高安全性。 http.cors.allow-origin: "*" ===============================================================================Elasticsearch配置JVM参数Elasticsearch配置JVM参数合理配置JVM参数对其性能和稳定性至关重要。-Xms 设定JVM堆内存初始大小，避免频繁内存分配；-Xmx 限制堆内存最大是多少，防止过度占用系统资源。建议二者设为相同值，依实际情况调整。cd /usr/local/es/elasticsearch-8.17.2/config vim jvm.options # 我的设置是根据我服务器的配置来的，你们根据实际情况而定 -Xms512m -Xmx512m增加ES最大文件、进程数限制，保其稳定运行/etc/security/limits.conf 通用资源限制配置文件，可全面设限。/etc/security/limits.d/20-nproc.conf 用于补充细化，配置优先级或更高。sudo vim /etc/security/limits.conf ============================================ # 指定用户设置软限制的最大打开文件描述符数量 youer soft nofile 65536 # 指定用户设置硬限制的最大打开文件描述符数量 youer hard nofile 65536 # 指定用户设置软限制的最大进程数量 youer soft nproc 4096 # 指定用户设置硬限制的最大进程数量 youer hard nproc 4096 ============================================sudo vim /etc/security/limits.conf ====================================== youer soft nproc 4096 youer hard nproc 4096 ====================================== 或者 ===================================== * soft nproc 4096 root soft nproc unlimited =====================================调整系统的内核参数,保证Elasticsearch应用跑起来sudo vim /etc/sysctl.conf ================================ # 设置虚拟内存中最大内存映射区域的数量 # 对于像 Elasticsearch 这类应用，在启动和运行过程中会进行大量的内存映射操作 # 若该值过小，可能会导致 Elasticsearch 启动失败或运行过程中出现内存映射错误 # 这里将其设置为 655360，为应用提供更充足的内存映射资源 vm.max_map_count=655360 # 设置系统允许的最大文件数 # 系统在运行时会涉及大量文件的打开、读写等操作 # 增大该值可以确保系统有足够的资源来处理更多的文件操作 # 避免因文件描述符耗尽而导致应用无法正常工作 # 这里设置为 65535 fs.file-max = 65535 ================================ # 读取配置并应用新的内核参数，使修改后的配置立即生效 sudo sysctl -p安装ik插件ik插件下载，下载与之对应的ik中文分词插件cd /usr/local/es/ wget https://release.infinilabs.com/analysis-ik/stable/elasticsearch-analysis-ik-8.17.2.zip unzip elasticsearch-analysis-ik-8.17.2.zip mv elasticsearch-analysis-ik-8.17.2 /usr/local/es/elasticsearch-8.17.2/plugins/权限# 给文件夹赋权和用户以及组 sudo chown -R youer:elasticsearch /usr/local/es/elasticsearch-8.17.2 sudo chmod -R 755 /usr/local/es/elasticsearch-8.17.2启动# 切换到youer用户 su youer # 启动es cd /usr/local/es/elasticsearch-8.17.2/bin ./elasticsearch -d查看端口和es服务# 查看端口 ps -ef | grep elasticsearch|grep -v "grep" netstat -an | grep 9200 # 对外防火墙放开端口 # 查询是否开启 sudo firewall-cmd --query-port=9200/tcp # 开启 sudo firewall-cmd --add-port=9200/tcp --permanent # 重启生效 sudo firewall-cmd --reloadphp使用安装类库 composer require elasticsearch/elasticsearchdeclare(strict_types=1); set_time_limit(-1); require __DIR__ . "./vendor/autoload.php"; use Elastic\Elasticsearch\ClientBuilder; try { $client = ClientBuilder::create() ->setHosts(['http://142.171.102.86:9200']) ->setSSLVerification(false) ->build(); // 定义用于创建 Elasticsearch 索引的参数数组 $t = $_GET['type'] ?? 1; switch ($t) { case 1://创建索引时配置 IK 分词器 $params = [ // 指定要创建的索引的名称为 my_ik_index // 在 Elasticsearch 里，索引是存储文档的逻辑容器，类似关系型数据库中的数据库 // 后续对该索引的操作（如添加文档、搜索等）都会使用此名称定位 'index' => 'my_ik_index', // 包含索引设置和映射信息的数组，是创建索引请求的核心部分 'body' => [ // 用于设置索引的各种参数，这里主要关注与分词分析相关的设置 'settings' => [ // 配置索引的分析器和分词器 // 分析器负责将文本转换为适合索引和搜索的词项，分词器是分析器的核心，负责拆分文本 'analysis' => [ // 定义自定义分析器的部分，可以配置多个分析器，每个有唯一名称 'analyzer' => [ // 自定义分析器 ik_max_word_analyzer 'ik_max_word_analyzer' => [ // 表示这是一个自定义分析器 // 除内置分析器外，可自定义组合分词器、过滤器等创建特定分析器 'type' => 'custom', // 指定该分析器使用 ik_max_word 作为分词器 // ik_max_word 是 IK 分词器的一种模式，会对文本做最细粒度拆分 'tokenizer' => 'ik_max_word' ], // 自定义分析器 ik_smart_analyzer 'ik_smart_analyzer' => [ // 表示这是一个自定义分析器 'type' => 'custom', // 指定该分析器使用 ik_smart 作为分词器 // ik_smart 是 IK 分词器的另一种模式，会对文本做最粗粒度拆分 'tokenizer' => 'ik_smart' ] ] ] ], // 定义索引中文档的结构和字段类型，以及每个字段使用的分析器 'mappings' => [ // 定义索引中各个字段的属性，每个字段有唯一名称，可配置类型、分词器等 'properties' => [ // 定义 title 字段 'title' => [ // 指定 title 字段的类型为 text，用于存储文本数据 // text 类型字段在索引时会进行分词处理以支持全文搜索 'type' => 'text', // 指定在对 title 字段进行索引时使用 ik_max_word_analyzer 分析器 // 这样在添加文档到索引时，title 字段文本会被 ik_max_word 分词器细粒度拆分 'analyzer' => 'ik_max_word_analyzer', // 指定在对 title 字段进行搜索时使用 ik_smart_analyzer 分析器 // 搜索时，搜索关键词会被 ik_smart 分词器粗粒度拆分以提高搜索准确性 'search_analyzer' => 'ik_smart_analyzer' ], // 定义 content 字段 'content' => [ // 指定 content 字段的类型为 text，用于存储文本数据 'type' => 'text', // 指定在对 content 字段进行索引时使用 ik_max_word_analyzer 分析器 // 索引时，content 字段文本会被 ik_max_word 分词器细粒度拆分 'analyzer' => 'ik_max_word_analyzer', // 指定在对 content 字段进行搜索时使用 ik_smart_analyzer 分析器 // 搜索时，搜索关键词会被 ik_smart 分词器粗粒度拆分以提高搜索准确性 'search_analyzer' => 'ik_smart_analyzer' ] ] ] ] ]; $response = $client->indices()->create($params); if ($response['acknowledged']) { echo "索引创建成功！"; } else { echo "索引创建失败！"; } break; case 2://添加文档 $params = [ 'index' => 'my_ik_index', 'id' => '1', 'body' => [ 'title' => '自然语言处理技术', 'content' => '自然语言处理是人工智能的一个重要领域。' ] ]; $response = $client->index($params); // 判断添加文档操作是否成功 if (isset($response['result']) && $response['result'] === 'created') { echo "文档添加成功，文档 ID 为: " . $response['_id']; } else { echo "文档添加失败，错误信息: " . json_encode($response); } break; case 3://搜索文档 $query = "领域"; $params = [ 'index' => 'my_ik_index', 'body' => [ 'query' => [ 'multi_match' => [ 'query' => $query, 'fields' => ['title', 'content'], 'analyzer' => 'ik_smart_analyzer' ] ] ] ]; $response = $client->search($params); if (isset($response['hits']['total']['value']) && $response['hits']['total']['value'] > 0) { echo "搜索成功，共找到 " . $response['hits']['total']['value'] . " 个匹配文档： "; foreach ($response['hits']['hits'] as $hit) { $source = json_encode($hit['_source'], JSON_UNESCAPED_UNICODE); $source = str_replace($query, "{$query}",$source); echo "文档 ID: " . $hit['_id'] . ", 得分: " . $hit['_score'] . ", 内容: " . $source . " "; } } elseif (isset($response['hits']['total']['value']) && $response['hits']['total']['value'] === 0) { echo "搜索成功，但未找到匹配的文档。"; } else { echo "搜索失败，错误信息: " . json_encode($response); } break; case 4://删除文档 $params = [ 'index' => 'my_index' ]; // 执行删除索引操作 $response = $client->indices()->delete($params); // 判断删除是否成功 if (isset($response['acknowledged']) && $response['acknowledged'] === true) { echo "索引删除成功。"; } else { echo "索引删除失败，错误信息: " . json_encode($response); } break; case 5://获取集群健康状态 // 调用 _cluster/health API 获取集群健康状态 $params = [ 'index' => null, // 不指定特定索引，获取整个集群的健康状态 ]; $response = $client->cluster()->health($params); // 解析和处理返回结果 $status = $response['status']; echo "Elasticsearch 集群健康状态: ". $status. "\n"; switch ($status) { case 'green': echo "集群状态良好，所有主分片和副本分片都可用。\n"; break; case 'yellow': echo "集群状态正常，但存在部分副本分片不可用。\n"; break; case 'red': echo "集群状态异常，存在主分片不可用的情况。\n"; break; default: echo "未知的集群健康状态。\n"; } break; } } catch (Exception $e) { echo "发生错误：" . $e->getMessage(); } 查询结果访问 http://142.171.102.86:9200查询
一步到位！Sphinx 安装配置全流程，PHP 轻松对接
遭遇了 “黑天鹅” 事件近期真是遭遇了 “黑天鹅” 事件，公司因卷入非法问题，面临一系列棘手处理，无奈之下只得遣散全体员工，我也因此瞬间失业。原本按部就班的工作节奏戛然而止，每天醒来，看着毫无安排的一天，心里满是茫然与失落。与其在这种不确定中焦虑徘徊，倒不如主动出击，给自己找点事儿做。我打算投身到文档撰写中，既能有效填补空闲时间，让生活重回正轨，也能借此梳理过往积累的知识与经验，为未来求职筑牢根基，让这段意外失业的日子，不至于虚度 。什么是Sphinx? 有什么优缺点呢？phinx 是开源全文搜索引擎。优点是高性能、功能丰富、可扩展性好、多语言支持、开源成本低；缺点是配置复杂、对内存要求高、实时性有限、缺乏图形化管理界面。安装和配置sphinx官网下载页面服务器：CentOS7，glibc版本2.17安装# 创建存放sphinx软件目录 mkdir -p /usr/local/sphinx/ # 进入/usr/local/sphinx/文件夹 cd /usr/local/sphinx/ # 下载对应的glibc版本的sphinx软件包 wget https://sphinxsearch.com/files/sphinx-3.7.1-da9f8a4-linux-amd64-glibc2.17.tar.gz # 解压 tar -zxvf sphinx-3.7.1-da9f8a4-linux-amd64-glibc2.17.tar.gz配置# 进入到配置目录 cd /usr/local/sphinx/sphinx-3.7.1/etc/ # 创建文件，把配置粘贴进去 vim sphinx.conf ====================================================== # 通用配置部分，定义一些全局的设置 common { # 数据目录，用于存放索引文件和其他相关数据 datadir = ./sphinxdata } # 数据源配置，这里以 MySQL 数据库为例 source names_1_source { # 数据源类型为 MySQL type = mysql # 数据库主机地址，使用 127.0.0.1 以确保端口配置生效 sql_host = 127.0.0.1 # 数据库用户名 sql_user = active # 数据库密码 sql_pass = PcjcrN53sR8bdZyx # 要连接的数据库名 sql_db = active # 数据库端口号，默认是 3306 sql_port = 3306 # SQL 查询语句，用于从数据库中获取要索引的数据 sql_query = SELECT id, surname, name, first, second, gender, type, score, details FROM names_1 # 在执行查询前设置字符集为 UTF-8，确保中文等字符能正确处理 sql_query_pre = SET NAMES utf8mb4 } # 索引配置 index names_1_index { # 索引类型为普通索引 type = plain # 关联的数据源 source = names_1_source # 要进行全文搜索的字段 field = surname, details # 定义属性，用于排序、过滤等操作 attr_uint = gender, type # 无符号整数类型的属性 attr_float = score # 浮点数类型的属性 attr_string = name, first, second # 字符串类型的属性 # 字符集表，涵盖了数字、英文字母（大小写统一）、中文、日文、韩文等字符范围 # charset_table = # 最小词长度，设置为 1 表示单字符也会被索引，适用于中文和单字母搜索 min_word_len = 1 # 词形处理方式，这里设置为不进行词形处理 morphology = none # N - gram 长度，设置为 1 表示按单字符进行 N - gram 索引，有助于中文搜索 ngram_len = 1 # 指定用于 N - gram 索引的字符范围，这里包含了中文、日文、韩文等字符 ngram_chars = U+4E00..U+9FBB, U+3400..U+4DB5, U+20000..U+2A6D6, U+FA0E, U+FA0F, U+FA11, U+FA13, U+FA14, U+FA1F, U+FA21, U+FA23, U+FA24, U+FA27, U+FA28, U+FA29, U+3105..U+312C, U+31A0..U+31B7, U+3041, U+3043, U+3045, U+3047, U+3049, U+304B, U+304D, U+304F, U+3051, U+3053, U+3055, U+3057, U+3059, U+305B, U+305D, U+305F, U+3061, U+3063, U+3066, U+3068, U+306A..U+306F, U+3072, U+3075, U+3078, U+307B, U+307E..U+3083, U+3085, U+3087, U+3089..U+308E, U+3090..U+3093, U+30A1, U+30A3, U+30A5, U+30A7, U+30A9, U+30AD, U+30AF, U+30B3, U+30B5, U+30BB, U+30BD, U+30BF, U+30C1, U+30C3, U+30C4, U+30C6, U+30CA, U+30CB, U+30CD, U+30CE, U+30DE, U+30DF, U+30E1, U+30E2, U+30E3, U+30E5, U+30E7, U+30EE, U+30F0..U+30F3, U+30F5, U+30F6, U+31F0, U+31F1, U+31F2, U+31F3, U+31F4, U+31F5, U+31F6, U+31F7, U+31F8, U+31F9, U+31FA, U+31FB, U+31FC, U+31FD, U+31FE, U+31FF, U+AC00..U+D7A3, U+1100..U+1159, U+1161..U+11A2, U+11A8..U+11F9, U+A000..U+A48C, U+A492..U+A4C6 } # 搜索服务配置 searchd { # 监听的地址和端口，客户端通过此端口连接搜索服务 listen = 9312 # 搜索服务的日志文件路径，记录服务的运行信息 log = no # 查询日志文件路径，记录用户的查询请求，方便后续分析和优化 query_log = no # 进程 ID 文件路径，用于管理搜索服务进程 # pid_file = "" # 启用无缝索引切换，在重建索引时不影响搜索服务的正常运行 seamless_rotate = 1 # 是否预先打开所有索引文件，根据服务器内存和性能情况调整 preopen_indexes = 0 # 自动删除旧的索引文件，节省磁盘空间 unlink_old = 1 # 客户端连接超时时间（秒） client_timeout = 30 } ======================================================测试 数据库active下的表names_1大概是这样的启动sphinx和停止# 可读权限 sudo chmod -R a+r /usr/local/sphinx/sphinx-3.7.1 # 启动 ./searchd -c /usr/local/sphinx/sphinx-3.7.1/etc/sphinx.conf # 停止 ./searchd --stop -c /usr/local/sphinx/sphinx-3.7.1/etc/sphinx.conf创建和更新索引sudo chmod -R a+r /usr/local/sphinx/sphinx-3.7.1 sudo chmod u+w /usr/local/sphinx/sphinx-3.7.1/bin/indexer cd /usr/local/sphinx/sphinx-3.7.1/bin/ ./indexer -c /usr/local/sphinx/sphinx-3.7.1/etc/sphinx.conf --all --rotate # ./indexer是 Sphinx 索引器程序。 # -c /usr/local/sphinx/sphinx-3.7.1/etc/sphinx.conf指定配置文件路径。 # --all对配置里所有索引操作。 # --rotate在searchd运行时无缝更新索引，不中断搜索服务。查看端口和sphinx服务# 查看端口 ps -ef | grep searchd|grep -v "grep" netstat -an | grep 9312 # 对外防火墙放开端口 # 查询是否开启 sudo firewall-cmd --query-port=9312/tcp # 开启 sudo firewall-cmd --add-port=9312/tcp --permanent # 重启生效 sudo firewall-cmd --reloadphp 使用首先复制一份/usr/local/sphinx/sphinx-3.7.1/api/sphinxapi.php 文件到你的项目目录。<?php declare(strict_types=1); require __DIR__.'./vendor/autoload.php'; require __DIR__."/sphinxapi.php"; use Fukuball\Jieba\Jieba; //use Fukuball\Jieba\Posseg; ini_set('memory_limit','512M'); //分词 Jieba::init(); //Posseg::init(); $searchQuery = 'Firaxis'; if (hasChinese($searchQuery)) { $cutWord = Jieba::cut($searchQuery); $searchQuery = implode('|', $cutWord); } echo '查找关键词：'.$searchQuery.' '; // 创建 Sphinx 客户端对象 $cl = new SphinxClient(); // 设置 Sphinx 服务器的主机和端口 $cl->SetServer('127.0.0.1',9312); $t = $_GET['type'] ?? 1; switch ($t) { case 1: // 设置搜索的偏移量和返回结果数量 $cl->SetLimits(0, 10); // 设置排序模式为按相关性排序 $cl->SetSortMode(SPH_SORT_RELEVANCE); $index = 'names_1_index'; // $cl->SetFilterString('details','Firaxis'); $result = $cl->query($searchQuery, $index); if ($result === false) { echo '查询错误: '. $cl->GetLastError(); } else { // 处理查询结果 if ($result['total'] > 0) { foreach ($result['matches'] as $id => $match) { echo '文档ID: '. $id. ',姓名：'.$match['attrs']['name'].',score：'.$match['attrs']['score']. ',相关性得分: '. $match['weight']. ' '; // 可以根据索引中的字段获取更多信息 } } else { echo '没有找到匹配的结果'; } } break; case 2: //TODO 更新数据库 $index = 'names_1_index'; $attrs = ['score']; $values = [ 4048551 => [100], // 文档 ID 为 1 的文档，将 'price' 属性更新为 100 4048552 => [200] // 文档 ID 为 2 的文档，将 'price' 属性更新为 200 ]; $updated = $cl->UpdateAttributes($index, $attrs, $values); if ($updated >= 0) { echo "成功更新 $updated 条文档的属性。"; } else { echo "更新属性时出错: ". $cl->GetLastError(); } break; } function hasChinese($str) { for ($i = 0; $i < mb_strlen($str); $i++) { $char = mb_substr($str, $i, 1); $ord = mb_convert_encoding($char, 'UCS-2BE', 'UTF-8'); $code = hexdec(bin2hex($ord)); if ($code >= 0x4E00 && $code <= 0x9FA5) { return true; } } return false; } 查询结果[post cid="388" /]
安装mysqlCentOS7 单机配置mysql多实例运行环境说明CentOS7、mysql5.7mysql服务器master host:127.0.0.1 port:3306slave 1 host:127.0.0.1 port:3307slave 2 host:127.0.0.1 port:3308主从配置原理① 当master节点接收到一个用户写请求时，这个写请求可能是增删改操作，此时会把写请求的操作都记录到binlog日志中。② 首先slave节点通过配置信息连接到master节点上，当slave节点连接到master节点上时，master节点会为每一个slave节点分别创建一个binlog dump线程，用于向每个slave节点发送binlog日志。③ 此时，binlog dump线程会读取master节点上的binlog日志，然后将binlog日志发送给slave节点上的I/O线程。④ slave几点上的I/O线程接收到binlog日之后，会将文件名、路径、密码等信息保存到master.info中，会将binlog日志先写入到本地的relaylog中，relaylog中就保存了master的binlog日志。⑤ 最后，slave节点上的SQL线程会读取relaylog中的biinlog日志，将其解析成具体的增删改操作，把这些在master节点上进行过的操作，重新在slave节点上也重做一遍，打到数据还原的效果，这样就可以保证master节点和slave节点的数据一致性了。master配置# 查看当前的二进制日志记录状态 root@(localhost) 01:23:33>SHOW VARIABLES like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) # 创建用于从服务器连接的用户 # 刷新权限 FLUSH PRIVILEGES; # 创建用户 CREATE USER 'synchronous'@'%' IDENTIFIED by 'synchronous'; # 授予'synchronous'用户复制从服务器的权限 GRANT REPLICATION SLAVE ON *.* TO 'synchronous'@'%' IDENTIFIED WITH mysql_native_password BY 'synchronous'; # 刷新权限 FLUSH PRIVILEGES; # 查看主服务器的状态，包括二进制日志文件名和位置 root@(localhost) 01:23:44>show MASTER status; +-----------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-----------------------+----------+--------------+------------------+-------------------+ | mysql-3306-bin.000003 | 1757 | | | | +-----------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)slave 1 从服务器 # 添加主服务器信息 CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3306, MASTER_USER="synchronous", MASTER_PASSWORD="synchronous", MASTER_LOG_FILE="mysql-3306-bin.000003", MASTER_LOG_POS=1757; # 开启从服务器 start SLAVE; # 查看从服务器状态 root@(localhost) 01:41:00>SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: synchronous Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-3306-bin.000003 Read_Master_Log_Pos: 1757 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 938 Relay_Master_Log_File: mysql-3306-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 1757 Relay_Log_Space: 1149 ..................................................................................... Master_Server_Id: 3306 Master_UUID: fcbfbd72-e0fc-11ee-8997-5254008afee6 Master_Info_File: /usr/local/mysql/data/3307/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 ..................................................................................... 1 row in set (0.00 sec) ERROR: No query specified slave 2 从服务器 # 添加主服务器信息 CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3306, MASTER_USER="synchronous", MASTER_PASSWORD="synchronous", MASTER_LOG_FILE="mysql-3306-bin.000003", MASTER_LOG_POS=1757; # 开启从服务器 start SLAVE; # 查看从服务器状态 root@(localhost) 01:41:00>SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: synchronous Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-3306-bin.000003 Read_Master_Log_Pos: 1757 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 938 Relay_Master_Log_File: mysql-3306-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 1757 Relay_Log_Space: 1149 ..................................................................................... Master_Server_Id: 3306 Master_UUID: fcbfbd72-e0fc-11ee-8997-5254008afee6 Master_Info_File: /usr/local/mysql/data/3308/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 ..................................................................................... 1 row in set (0.00 sec) ERROR: No query specified 测试主从# 主服务器创建test库，test表 CREATE DATABASE test; USE test; CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `username` char(30) NOT NULL COMMENT '姓名', `age` int(11) DEFAULT NULL COMMENT '昵称', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='测试表'; # slave1、slave2 查看 root@(localhost) 01:41:10>SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) root@(none) 01:49:25>USE test; Database changed root@test 01:49:44>SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.00 sec)[scode type="green"]相关文章[/scode][post cid="3207" /][post cid="3351" /][post cid="3359" /]
下载 MySQL 5.7 的安装包首先，需要从 MySQL 官方网站或其他可靠来源下载 MySQL 5.7 的安装包。确保选择与你的 CentOS 系统架构（如 x86_64）对应的版本。操作系统内核的版本号[root@localhost mysql]# uname -r 3.10.0-1160.108.1.el7.x86_64 操作系统的版本信息NAME="CentOS Linux" VERSION="7 (Core)" ID="centos" ID_LIKE="rhel fedora" VERSION_ID="7" PRETTY_NAME="CentOS Linux 7 (Core)" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:centos:centos:7" HOME_URL="https://www.centos.org/" BUG_REPORT_URL="https://bugs.centos.org/" CENTOS_MANTISBT_PROJECT="CentOS-7" CENTOS_MANTISBT_PROJECT_VERSION="7" REDHAT_SUPPORT_PRODUCT="centos" REDHAT_SUPPORT_PRODUCT_VERSION="7" 官网下载页面地址Product Version : 5.7.39Operating System : Linux GenericOS Version : Generic (glibc 2.12) (x86, 64-bit)mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz 下载链接 安装为mysql创建单独的属组、用户、创建安装目录# 查看用户、组 是否存在 cat /etc/passwd |grep mysql cat /etc/group |grep mysql # 删除用户 userdel mysql # 创建组 groupadd mysql # 创建一个名为mysql的系统用户，该用户属于mysql组，不能登录到系统，确保只有数据库服务器可以访问 MySQL 数据库 # -r 表示创建一个系统用户，该用户不能用于登录系统 # -g 指定用户所属的主组 # 指定用户的登录 shell 为 /sbin/nologin，这意味着该用户不能通过 SSH 或控制台登录到系统 useradd -r -g mysql mysql -s /sbin/nologin # 创建安装目录 mkdir /usr/local/mysql 下载、解压、移动至安装目录# 下载mysql 安装包到root 目录 wget -P /root/ https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz # 解压 cd /root/ && tar -zxvf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz # 移动到mysql安装目录 mv /root/mysql-5.7.39-linux-glibc2.12-x86_64/* /usr/local/mysql/mysql 配置创建所需目录# 创建数据存储目录 mkdir /usr/local/mysql/data/ # 创建数据日志目录 mkdir /usr/local/mysql/log/ # 创建临时文件目录 mkdir /usr/local/mysql/tmp/ # 创建运行文件目录 mkdir /usr/local/mysql/run/ # 创建启动错误日志 touch /usr/local/mysql/log/mysqld_safe_error.log # 创建默认的错误日志目的地 touch /usr/local/mysql/log/alert.log # 创建慢查询日志文件 touch /usr/local/mysql/log/slow.log # 创建通用查询日志文件的 touch /usr/local/mysql/log/general.log 创建、配置文件 vim /etc/my.cnf # mysqld_safe脚本启动时读取的配置 [mysqld_safe] # 存放 MySQL 后台程序 pid 的文件位置 pid-file=/usr/local/mysql/run/mysqld.pid # 启动错误日志 log-error=/usr/local/mysql/log/mysqld_safe_error.log # 本地 mysql 客户端程序的配置块 [mysql] # 本地 mysql 客户端连接的端口 port=3306 # 本地 mysql 客户端命令行提示信息 prompt=\\u@\\d \\r:\\m:\\s> # 本地 mysql 客户端字符集 default-character-set=utf8mb4 # 开启命令补全 no-auto-rehash # 所有mysql客户端程序读取的配置块 [client] # 连接端口 port=3306 # mysql的主机和客户机在同一host上的时候，使用unix domain socket做为通讯协议的载体文件 socket=/usr/local/mysql/run/mysql.sock # mysql服务端程序mysqld、mysqld_safe和mysqld_multi的配置文件 [mysqld] # 进程崩溃时生成core file dump文件，便于程序调试和问题排查 core-file # 该参数指定了安装 MySQL 的安装路径（mysql安装目录），填写全路径可以解决相对路径所造成的问题。 basedir=/usr/local/mysql # 该参数指定MySQL的数据文件的存放目录，数据库文件即我们常说的 MySQL data 文件。 datadir=/usr/local/mysql/data # 临时目录 tmpdir=/usr/local/mysql/tmp # 用于错误消息的区域设置。默认值是en_US。服务器将参数转换为语言名，并将其与lc_messages_dir的值结合，以生成错误消息文件的位置。 lc_messages=zh_CN # 错误消息所在的目录。服务器使用该值和lc_messages的值来生成错误消息文件的位置。 lc_messages_dir=/usr/local/mysql/share # 默认的错误日志目的地。如果目标是控制台，则值为stderr。否则，目标是一个文件，log_error值是文件名。 log-error=/usr/local/mysql/log/alert.log # 慢查询日志文件名。默认值是host_name-slow.log，但可以通过slow_query_log_file选项更改初始值。 slow_query_log_file=/usr/local/mysql/log/slow.log # 通用查询日志文件的名称。默认值是host_name.log，但初始值可以通过general_log_file选项更改。 general_log_file=/usr/local/mysql/log/general.log # mysql的主机和客户机在同一host上的时候，使用unix domain socket做为通讯协议的载体文件 socket=/usr/local/mysql/run/mysql.sock # 服务端字符集 character-set-server=utf8mb4 # 此变量控制写入错误日志的消息中的时间戳的时区，以及写入文件的一般查询日志和慢查询日志消息中的时间戳的时区。 log_timestamps=SYSTEM # 操作系统中可用于mysqld的文件描述符的数量。 open_files_limit=61535 # 同时允许的最大客户端连接数。 max_connections=1000 # mysql_stmt_send_long_data() C API函数发送的一个包或任何生成/中间字符串的最大大小，或任何参数的最大大小。默认是64MB。 max_allowed_packet=1G # 如果设置为0，表名将按指定的方式存储，并且比较区分大小写。如果设置为1，表名将以小写形式存储在磁盘上，比较不区分大小写。如果设置为2，则表名按给定值存储，但以小写进行比较。此选项也适用于数据库名称和表别名。 lower_case_table_names=1 # 慢查询日志是否开启。取值为0(或OFF)表示关闭日志，取值为1(或ON)表示打开日志。默认值取决于是否给出——slow_query_log选项。日志输出的目标由log_output系统变量控制;如果该值为NONE，则即使启用了日志，也不会写入任何日志项。 slow_query_log=1 # validate_password插件的加载方法 plugin-load-add=validate_password.so # validate-password在服务器启动时使用该选项来控制插件的激活 validate-password=FORCE_PLUS_PERMANENT初始化数据库 # 递归授予安装目录所有权 chown -R mysql:mysql /usr/local/mysql # 初始化数据库 cd /usr/local/mysql ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ # 重新递归授予安装目录所有权 chown -R mysql:mysql /usr/local/mysql # 递归授予安装目录用户添加读写和执行权限 chmod u+wrx -R /usr/local/mysql配置开机自启cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld # 启动mysql服务 service mysqld start # 其他相关命令 service mysqld stop # 停止mysql服务 service mysqld restart # 重启mysql服务 service mysqld status # 查看mysql服务是否启动登录MySQL进行初始化设置查看mysql初始密码grep -e 'temporary' /usr/local/mysql/log/alert.log # 如下所示初始密码：gayF7VntR5!A [root@localhost mysql-5.7.39-linux-glibc2.12-x86_64]# grep -e 'temporary' /usr/local/mysql/log/alert.log 2024-03-07T06:45:47.396472-00:00 1 [Note] A temporary password is generated for root@localhost: gayF7VntR5!A 登录到mysql/usr/local/mysql/bin/mysql -u root -pgayF7VntR5!A 修改root密码 SHOW VARIABLES LIKE 'validate_password%'; # 查看密码验证规则 set global validate_password_policy=LOW; # 设置密码验证安全级别 set global validate_password_length=6; # 设置密码验证最小长度 alter user root@localhost identified by '123456'; # 设置密码为123456 (自己设置，我这里是演示)设置远程访问权限 use mysql; update user set user.Host='%' where user.User='root'; flush privileges;防火墙放行MySQL监听的端口 # 防火墙放行3306/tcp端口 firewall-cmd --zone=public --add-port=3306/tcp --permanent firewall-cmd --reload # 重启防火墙重 # 其他命令 systemctl status firewalld # 查看防火墙服务服务的状态 systemctl enable firewalld # 启用自启动防火墙服务 systemctl start firewalld # 手动启动防火墙服务 firewall-cmd --list-ports # 查看已经开放的端口
[scode type="green"]mysql索引的本质是什么[/scode][collapse title="点击展开" status="false"]1、其实就相当于目录，是帮助mysql高效获取数据的数据结构。2、我们都知道，在mysql中数据最终存储在硬盘中的，访问磁盘相当于是I/O操作。3、在mysql中有一个page的概念，一个表都被分为若干个页面(page),每个页面(page)就是树中的一个节点，每次mysql就会取出一个页面(page)也就是一个节点的数据，而mysql默认一个页面(page)保存16k的数据。4、页面(page)的大小会直接影响到数据的存储和检索效率，因此我们也可以实际业务需求和硬件条件进行评估和调整，合理设置mysql的页面(page)大小，以达到最佳的性能表现。[/collapse][scode type="green"]mysql的索引常见分类以及操作索引的语法[/scode][collapse title="点击展开" status="false"]主键索引主键索引是一种特殊的索引类型，它是用于唯一标识每一行数据的索引，每个表只能有一个主键索引，索引列中的值必须是唯一的，不允许有空值。复合索引(又称为联合索引)复合索引也叫多列索引或联合索引，它是包含多个列的索引类型，能够加速多列查询和排序操作。需要遵循最左前缀匹配原则（最左匹配原则）普通索引MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。唯一索引唯一索引是用来保证列的唯一性的索引，一个表可以有多个唯一索引。索引列中的值必须是唯一的，但是允许为空值。全文索引全文索引是一种用于全文搜索的索引类型，能够对文本数据进行快速的模糊搜索和关键字搜索。只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。哈希索引哈希索引是基于哈希表实现的索引类型，能够对等值查询进行高效的处理，但不支持范围查询和排序，MySQL 中 Memory 引擎中支持哈希索引。索引操作新建表中添加索引CREATE TABLE 表名 ( 主键字段名 数据类型 [完整性约束条件] PRIMARY KEY, 字段名 数据类型 [完整性约束条件], [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名](字段名1 [(长度)] [ASC | DESC]) ); 注解： UNIQUE：可选。表示索引为唯一性索引。 FULLTEXT；可选。表示索引为全文索引。 SPATIAL：可选。表示索引为空间索引。 INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。 索引名：可选。给创建的索引取一个新名称。 字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。 长度：可选。指索引的长度，必须是字符串类型才可以使用。 ASC：可选。表示升序排列。 DESC：可选。表示降序排列。在已建表中添加索引CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名(字段名)以修改表的方式添加索引ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL | INDEX | KEY | PRIMARY KEY] 索引名(字段名)[/collapse][scode type="green"]介绍一个数据模型可视化网站有助于对mysql索引模型数据结构的了解 数据模型可视化网站[/scode][scode type="green"]索引的数据类型[/scode][tabs][tab type="primary" name="二叉树" active="true"]二叉树 (简称：BST树,全称：Binary Search Trees)一、特点1、左子树的所有值都小于根节点2、右子树的所有值都大于根节点3、每个根节点最多分列出两个子节点二、模型1、如图1所示，如果我们要查询9，首先从根节点10查询，9小于10，走左节点8，9大于8，走右节点9，因为每个节点就是一次I/O查询，相当于进行了3次I/O查询。2、如图2所示，二叉树有一种极端情况，假如从小到大依次向树中插入的情况就会形成一个链表的形式，我们这时候要查询9时，至少需要9次IO操作，相当于遍历了整个表，I/O次数可想而知。为了避免这个问题呢？就有了平衡二叉树。[/tab][tab type="success" name="平衡二叉树"]平衡二叉树 (简称：AVL树,全称：Balanced binary search trees)一、特点1、相对平衡，左右两个子树的深度差绝对值不能超过12、左右两个子树也必须是平衡二叉树3、避免了二叉树的极端情况二、模型三、缺点1、一个节点只有2个分支，一个节点只能保存一条数据，获取16k的页面(page)，只有一条数据，造成资源浪费。单个节点保存数据少，就会造成节点增多，树的深度就会增多，查询次数就会增多，I/O次数多。2、每次增删之后，都要进行平衡，会降低效率。为了避免这个问题呢？就有b-tree[/tab][tab type="warning" name="b-tree"]b-tree (一种多路自平衡搜索树，它类似普通的二叉树，但是b-tree允许每个节点有更多的子节点)一、特点1、所有键值分布在整个树中2、任何关键字出现且只出现在一个节点中3、性能逼近二分查找算法4、在关键字全集内做一次查找，搜索有可能在非叶子结点结束5、自动层次控制6、一个节点可以存储超过2个元素，可以拥有超过2个子节点；拥有二叉树的一些性质；平衡，每个节点的所有子树高度一致；比较矮。二、一棵m阶的B-Tree有如下特性1、每个节点最多有m个孩子。2、除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子(Ceil返回大于或者等于指定表达式的最小整数)。3、若根节点不是叶子节点，则至少有2个孩子4、所有叶子节点都在同一层，且不包含其它关键字信息5、每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）6、关键字的个数n满足：ceil(m/2)-1 <= n <= m-17、ki(i=1,…n)为关键字，且关键字升序排序。8、Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)三、模型(以m=3的b-tree举例如 图4 所示)1、每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。2、两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。3、以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。4、模拟查找关键字29的过程： ① 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】 ② 比较关键字29在区间（17,35），找到磁盘块1的指针P2。 ③ 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】 ④ 比较关键字29在区间（26,30），找到磁盘块3的指针P2。 ⑤ 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】 ⑥ 在磁盘块8中的关键字列表中找到关键字29。分析1、 由图4不难看出，b-tree单个节点可以保存多个数据,一次页面(page)可以获取更多的有效数据，同时因为分叉增多，数据层级肯定会更小，查询次数就会减少。2、b-tree 数据从小到大依次分布在树的不同层级中，进行范围查找时，获取范围越大，获取的节点就越多，I/O操作就增多。极端情况下可能遍历整个树结构。3、由于b-tree的数据是分布在整个树结构中，假设一条数据是1k，一个页面(page)即一个节点最多保存16条数据(16k)，以一个3阶的树结构来说，最多可以保存16^3=4096条数据的话，如果一个表有500万数据，数的层级还是很深的，I/O操作还是很多。针对b-tree出现的问题，就引出了b+tree[/tab][tab type="primary" name="b+tree"]b+tree一、特点1、叶子节点保存数据信息，非叶子节点不保存,只保存标识和指针信息 (其实占用空间最大的还是数据，而b+tree就解决了这个问题)2、节点保存的元素个数等于m，并且左闭右开3、叶子节点通过指针链接，方便范围查找，只需要遍历叶子节点二、模型图三、相比于b-tree，一个同m=3(三阶)的树可以保存多少数据？1、假设一条数据还是1k，一个叶子节点可以保存16条数据，第二层(非叶子节点不保存数据)保存标识和指针信息，而标识和指针是很小的。假设为6b,一个节点可以保存16k，那可以保存标识和指针的数量为(16×1024/6=2730)个，第三层的一个节点保存标识和指针的数量也为(16×1024/6=2730)个，那总的三层可以保存的数据为(2730×2730×16≈12亿)，一般3层数结构就可以支持上亿的，只需要3次I/O就可以了。当然一条数据也可能大于1k，所以这里只是算一下大概数量级而已做个参考。2、进行范围查询和排序时，叶子节点都是关联的，不需要遍历非叶子节点，只需要遍历排序叶子节点数据即可。四、为什么mysql使用b+tree，而不使用b-tree呢?1、叶子节点基于索引排序更优2、非叶子节点不保存数据，保存索引数据更多，一次I/O获取更多目标数据[/tab][/tabs][scode type="green"]mysql是如何使用索引和保存数据的呢？[/scode]1、myisam存储引擎① 它有三种文件组成，.frm (保存表的定义信息)、.myi (保存索引信息)、*.myd (保存数据信息)② 主键索引模型图，主键索引的叶子节点保存的是一个指针，指针指向 *.myd 文件中其中的一条数据。③ 非主键索引模型图，非主键索引的叶子节点保存的也是一个指针，指针指向 *.myd 文件中其中的一条数据。 2、innodb存储引擎① 它有两种文件组成，.frm (保存表的定义信息)、.ibd (保存索引和数据信息)② 主键索引模型图，主键索引的叶子节点保存的是真正的数据，当索引查到叶子节点的时候就可以直接取出真正的数据。③ 非主键索引模型图，非主键索引的叶子节点保存的主键的值，当拿到主键值时，回到主键索引中(回表)，依次查找，取出真正的数据。[scode type="green"]面试中mysql索引经常问到的一些问题总结[/scode]1、mysql为什么不用二叉树、平衡二叉树？① 二叉树有一种极端情况，假如从小到大依次向树中插入的情况就会形成一个链表的形式，查询最后一条数据的时候就要遍历整个链表。② 平衡二叉树虽然避免了链表的极端情况，但是一个节点只能保存一个数据，保存的数据比较少，I/O操作就会增多。另外，每次增删之后，都要进行平衡，会降低效率。2、mysql为什么用b+tree,不用b-tree？① b+tree 叶子节点之间有指针关联，做排序和范围查找时会很方便，效率也会更好高，它不需要查询遍历所有节点，基于索引的扫表会更优，基于索引的排序也会更优② 非叶子节点不保存数据信息，只保存标识和指针信息，这个每个非叶子节点(page)保存的数据就会很多，这样树的层级就很低，减少了I/O操作。3、mysql为什么不建议用uuid当主键，而建议主键ID是自增的，和b+tree有什么关系？① uuid占用更多的存储空间，uuid是一个128位的数字，通常以36个字符的字符串形式表示。相比之下，整型主键只需要4个字节即可表示。使用uuid作为主键会占用更多的存储空间，导致索引和数据的存储变得更加庞大。② uuid会导致索引性能下降， MySQL写入数据时，创建索引是依次从小到大创建的，会把数据存放到索引页中。使用uuid作为主键，新行的主键值不一定比之前的主键值大，所以innoDb无法做到总是把新行插入到索引的最后，而需要为新行寻找合适的位置来分配新的空间（因为是B+树方式存储的）。要分配新的空间，就要知道应该分到哪个页。如果用自增主键等，直接顺序增加在后面。而uuid就需要排序后这儿插一个，哪儿插一个。不够的话，还要页分裂，无法预测其值的范围，导致索引的分裂和碎片化，进一步降低索引的性能，增加树的深度。4、mysql中的聚集索引，非聚集索引(稀疏索引)如何理解？① 在innodb中，主键索引就是聚集索引，非主键索引就是非聚集索引。索引和数据存储在一起的就是聚集索引，不存在一起的就是非聚集索引② myisam只有非聚集索引5、为什么不建议写 select * from 进行查询？① 避免回表，减少一次查询 , 那什么是回表呢？其实就是innodb引擎下非主键索引的叶子节点保存的主键的值，当拿到主键值时，回到主键索引中，依次查找，取出真正的数据，就叫回表，如下图所示。② select id，name from 为什么可以避免回表呢？如果建立了id主键索引和name索引，只需要id,name数据，而sql 这样写(select * from table where name =? ) 需要回表，只能查到id和name，其他字段需要回表查询。如果sql这样写 (select id,name from table where name =? ) 就避免了回表。也是索引覆盖完全的结果。6、 like 'aaa%' 一定会用到索引吗？最左匹配原则怎么理解？① 假如创建了name,age,address的索引，b+tree 建立索引是严格按照从左到右依次建立的。② select * from user where name=? and age=? and address=? 查询方式是首先把符合的name数据拿出来，再从符合name数据基础上找出符合age的，最后再找出符合address的数据。从左到右依次查询的，用到了name、age、address 索引，这就是最左匹配原则。③ select * from user where name=? ，只用到了name索引。④ select * from user where name=? and address=? ，也只用到了name索引。⑤ like 'aaa%' 用到了索引， 而 like '%aaa' 没用到索引。 7、为什么innodb引擎要求一定要建立主键索引？① 主键索引下面直接保存的数据，非主键索引下面保存的是标识，之后还要回表到主键索引查询数据，所以一定要建立主键索引。[scode type="green"]mysql的事务和MVCC[/scode]一、mysql的事务1、事务，就是一个操作序列 ，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位， ACID又是事务的四大特性。2、ACID，是指在可靠数据库管理系统（DBMS）中，事务(transaction)所应该具有的四个特性：[tabs][tab type="primary" name="原子性（Atomicity）" active="true"]事务包含的所有操作要么全部成功，要么全部失败回滚。这意味着事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。[/tab][tab type="primary" name="一致性（Consistency)"]事务必须使数据库从一个一致性状态变换到另一个一致性状态。例如，转账场景中，无论用户之间的转账次数多少，事务结束后两个用户的资金总额应该保持不变。[/tab][tab type="primary" name="隔离性（Isolation）"]当多个用户并发访问数据库时，事务为每一个用户开启的事务，不能被其他事务的操作所干扰。即要达到的效果是，对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。[/tab][tab type="primary" name="持久性（Durability）"]一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。例如，用户在提交转账请求后，即使数据库出现问题，也应该保证转账已经成功执行。[/tab][/tabs]二、MVCC(多版本并发控制)① 在了解MVCC之前，先了解一下什么是当前读和快照读。undo log 是回滚日志。其中 select 触发的快照读，而 update、insert、delete 以及select ... lock in share mode (共享锁) 、select ... for update(排他锁)都是 当前读。② MVCC机制下的四种隔离级别,mysql默认的隔离级别是可重复读(简称RR) 脏读不可重复读幻读读未提交（Read uncommitted）√√√读已提交（Read committed）×√√可重复读 （Repeatable read）××√序列化（Serializable）×××[tabs][tab type="primary" name="读未提交" active="true"]当数据库系统使用READ UNCOMMITTED隔离级别时，一个事务在执行过程中可以看到其他事务没有提交的新插入的记录，而且还能看到其他事务没有提交的对已有记录的更新。会出现脏读的问题。[/tab][tab type="primary" name="读已提交"]当数据库系统使用READ COMMITTED隔离级别时，一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，而且还能看到其他事务已经提交的对已有记录的更新。会出现不可重复读的问题。[/tab][tab type="primary" name="可重复度"]当数据库系统使用REPEATABLE READ隔离级别时，一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，但是不能看到其他事务对已有记录的更新。会出现幻读的情况。[/tab][tab type="primary" name="序列化"]当数据库系统使用SERIALIZABLE隔离级别时，一个事务在执行过程中完全看不到其他事务对数据库所做的更新。当两个事务同时操作数据库中相同数据时，如果第一个事务已经在访问该数据，第二个事务只能停下来等待，必须等到第一个事务结束后才能恢复运行。因此这两个事务实际上是串行化方式运行。[/tab][/tabs]mysql 如何在不同的隔离级别下获取数据呢?下面我们来介绍一下MVCC的实现原理③ MVCC实现原理 mysql对于使用InnoDB存储引擎的表，该表的聚簇索引记录中都包含几个必要的隐藏字段。隐藏字段解释含义DB_TRX_ID创建或者最后修改记录的事务IDDB_ROW_ID隐藏主键DB_ROLL_PTR回滚指针,指向上一个undolog(历史日志)1、如上图所示，当第一次插入数据时，同时会插入事务ID、 隐藏主键 、 回滚指针为null;2、当事务二修改name时，会先在undolog中保存一份原始数据，然后修改name，同时修改隐藏字段 事务ID、 隐藏主键 、 回滚指针为修改前的undolog地址。3、当事务三修改age时,同理，也保存一份数据到undolog，然后修改age,同时修改隐藏字段 事务ID、 隐藏主键 、 回滚指针为修改前的undolog地址。4、当事务四获取数据，是获取到哪条数据呢？其实不一定，因为需要判断一下版本链中的哪个版本是当前事务可见的，这时候就引入了ReadView概念。④ ReadView (事务在快照读时产生的读视图) 保存了以下3个字段字段解释含义trx_list系统活跃的事务IDup_limit_id列表中事务最小的IDlow_limit_id系统尚未分配的下一个事务ID一、判断规则：1、比较DB_TRX_ID和up_limit id，如果小于，则当前事务能看到DB_TRX_ID的记录，如果大于和等于，则进入下一个判断。2、比较DB_TRX_ID和low_limit_id，如果大于等于则代表DB_TRX_ID的记录在readview生成后出现的，那么对于当前事务不可见。如果小于，则进入下一个判断。3、判断DB_TRX_ID是否在活跃事务中，如果在，代表readview生成时，事务还在活跃状态，修改的数据当前的事务是看不到的，如果不在，说明事务在readview之前就commit了，那么修改的结果就是可见的。二、不同隔离级别生成快照读的时间不同1、RC隔离级别 : 每次进行快照读时都生成读视图2、RR隔离级别 : 只有第一次生成读视图，之后每次都使用第一次时的读视图 (非必然情况，RR隔离级别下的幻读)三、在RR隔离级别，我们举例说明一下上面的规则1、第一种情况事务0事务1事务2事务3事务4insert开启开启开启开启 update and commit select 事务2可见事务4 更新的数据trx_list（系统活跃的事务ID）1,2,3up_limit_id（列表中事务最小的ID）1low_limit_id（系统尚未分配的下一个事务ID）5DB_TRX_ID （当前事务ID）4· DB_TRX_ID=4 > up_limit_id=1 (进入下一个判断)· DB_TRX_ID=4 < low_limit_id=5 (进入下一个判断)· DB_TRX_ID=4 不在 trx_list活跃列表[1,2,3]中，说明事务2可见事务4更新的数据。2、第二种情况事务0事务1事务2事务3事务4insert开启开启开启开启 select update and commit select ① 事务2不可见事务4 更新的数据,事务2第一次查询生成读视图，DB_TRX_ID=0，当事务4更新并提交了之后，DB_TRX_ID=4。trx_list（系统活跃的事务ID）1,2,3,4up_limit_id（列表中事务最小的ID）1low_limit_id（系统尚未分配的下一个事务ID）5DB_TRX_ID （当前事务ID）0---->4· DB_TRX_ID=4 > up_limit_id=1 (进入下一个判断)· DB_TRX_ID=4 < low_limit_id=5 (进入下一个判断)· DB_TRX_ID=4 在 trx_list活跃列表[1,2,3,4]中，说明事务2不可见事务4更新的数据。四、幻读 （同一个事务中，不同的时间，两次相同的查询获取到的数据不同）事务1事务2select(快照读) insert and commitselect(第一次的快照读) update(当前读) select 1、幻读是一种数据库事务处理中的现象，特别是在使用可重复读（Repeatable Read）隔离级别时。当一个事务（事务A）读取并锁定了某些数据行，而另一个事务（事务B）在事务A之后修改了这些数据行，并提交了修改，那么事务A在再次读取相同的记录时，可能会发现多出了一些原本不存在的行，这给人一种数据行凭空出现的幻觉。2、为了解决幻读问题，数据库管理系统（DBMS）提供了多种锁机制，如范围锁（Range Lock）和间隙锁（Gap Lock）。范围锁用于锁定一个检索范围，确保在这个范围内的数据行只能被读，而不能被修改或删除。间隙锁则用于锁定两个数据行之间的间隙，防止新数据行插入到这些间隙中。这些锁机制的组合被称为next-key lock，它能够确保事务A在读取时只能看到事务A开始读取时存在的行，而不能看到之后新插入的行。3、在数据库定义的四种隔离级别中，最高隔离级别SERIALIZABLE_READ可以保证不出现幻读的问题。而Repeatable Read（RR）隔离级别则通过锁定读取到的记录和范围来防止幻读现象。4、如何避免幻读？一、引入了间隙锁和next-key lock机制，去解决幻读问题。为了更清晰的说明这两种锁如何解决幻读的，我们来举一个例子辅助了解。① 假设此刻存在这样一个b+tree的索引结构，这个索引里面有4个索引元素分别是1，4，7，10，当我们通过主键索引查询一条数据并且对这条数据通过for update加锁，这时候会产生一个记录锁(行锁)，锁定id=1的这个索引，被锁定的记录在锁释放之前其他事务是无法对这一条记录做任何操作的。 ② 前面我们说过，对幻读的定义，幻读是指在同一个事务中，前后两次查询相同的范围的时候，得到的结果不一致。innodb 引擎要解决幻读问题，必须要保证一个点，就是如果一个事务通过这样语句1(如下图)进行锁定的时候，另一个事务再执行语句2(如下图) 时需要被阻塞，直到前面获得锁的事务被释放，所以在innodb 引擎中设计了一种间隙锁，它的功能是锁定一段范围内的索引记录，如下图所示。其他事务对这个开区间的数据进行插入更新删除都会被锁住。③ next-key lock (临界锁) 机制相当间隙锁 和 记录锁(行锁) 的合集, 记录锁锁定存在记录的行，间隙锁锁住的是记录行之间的间隙，next-key lock 锁住的是两者的和，是一个左开右闭区间的数据，锁定的是多个索引区间。④ 虽然mysql在innodb引擎下通过间隙锁解决了幻读的问题，但是加锁之后一定会影响到并发的性能，因此对于性能较高的业务场景，我们可以把隔离级别设置为RC。对于数据精度较高的业务场景，我们可以把隔离级别设置为RR。根据不同的业务场景设置不同的隔离级别，能有效的提高数据库的性能。五、redolog 和 binlog是什么？为什么有了redolog还需要binlog呢？1、redolog 是用来保证事务的持久性的，保证数据不会丢失的。redolog 是innodb独有的，binlog是整个mysql服务层有的。2、二阶段提交模型图六、事务的四大特性实现[scode type="green"]mysql的主从复制与读写分类[/scode]一、什么是读写分离?读写分离，基本的原理是让主数据库处理事务性增、改、删操作( INSERT、UPDATE、DELETE) ，而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。二、为什么要读写分离以及什么时候要读写分离?1、写操作锁表，影响读操作，影响业务。2、如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。三、主从复制1、 数据更安全：做了数据冗余，不会因为单台服务器的宕机而丢失数据2、性能大大提升：一主多从，不同用户从不同数据库读取，性能提升3、 扩展性更优：流量增大时，避免因为I/O操作增多，单机出险瓶颈。可以方便的增加从服务器并且不影响系统使用4、 负载均衡：一主多从相当于分担了主机任务，做了负载均衡5、数据的备份四、主从复制的原理图1、当master节点接收到一个用户写请求时，这个写请求可能是增删改操作，此时会把写请求的操作都记录到binlog日志中。2、首先slave节点通过配置信息连接到master节点上，当slave节点连接到master节点上时，master节点会为每一个slave节点分别创建一个binlog dump线程，用于向每个slave节点发送binlog日志。3、此时，binlog dump线程会读取master节点上的binlog日志，然后将binlog日志发送给slave节点上的I/O线程。4、slave几点上的I/O线程接收到binlog日之后，会将文件名、路径、密码等信息保存到master.info中，会将binlog日志先写入到本地的relaylog中，relaylog中就保存了master的binlog日志。5、最后，slave节点上的SQL线程会读取relaylog中的biinlog日志，将其解析成具体的增删改操作，把这些在master节点上进行过的操作，重新在slave节点上也重做一遍，打到数据还原的效果，这样就可以保证master节点和slave节点的数据一致性了。[scode type="green"]mysql的分库分表[/scode]应用场景高并发、低数据量如果系统面临的并发请求量很大，但需要处理的数据量相对较少，可以考虑采用分库策略，将数据分散存储在多个数据库中，以提高系统的并发处理能力。低并发、高数据量如果系统的并发请求量不高，但需要处理的数据量很大，可以考虑采用分表策略，将数据按照一定的规则分布在多个表中，以提高查询和数据管理的效率。高并发、高数据量如果系统同时面临高并发请求和大量的数据处理需求，那么可以同时采用分库和分表策略，将数据分散存储在多个数据库和表中，以提高系统的整体性能和扩展性。分库分表的方向和顺序方向垂直水平顺序在并发分库分表时，推荐按照先垂直分，再水平分的顺序进行。这种方式更简单，也更符合实际业务。垂直分库垂直分库是将一个系统中的不同业务模块或功能拆分为独立的数据库，每个数据库专注于处理特定的业务数据。这样可以提高数据库的性能、可伸缩性和数据管理的效率。以下是一些垂直分库的示例场景：用户数据库：存储用户相关的信息，如用户 ID、用户名、密码、电子邮件等。商品数据库：存储商品相关的信息，如商品 ID、商品名称、价格、库存等。订单数据库：存储订单相关的信息，如订单 ID、订单日期、订单状态、订单详情等。通过将不同的业务模块或功能拆分为独立的数据库，可以实现以下优势：提高性能：每个数据库专注于处理特定的业务数据，可以减少数据的冗余和查询的复杂度，从而提高数据库的查询性能。可伸缩性：随着业务的增长，可以轻松地对特定的数据库进行扩容，而不会影响其他业务模块的性能。数据管理效率：每个数据库可以根据其特定的业务需求进行优化和管理，例如索引维护、数据备份等。需要注意的是，在进行垂直分库时，需要考虑数据库之间的关联关系和数据一致性。通常需要通过主键或外键来建立数据库之间的关联，并确保数据的一致性和正确性。此外，垂直分库可能会增加数据库架构的复杂性，需要合理设计数据库表结构和查询语句来优化查询性能。垂直分表垂直分表是将一个表中的热点查询字段和非热点查询字段进行分表处理的方法。通过将热点字段和非热点字段分别存储在不同的表中，可以提高查询性能和数据管理的效率。垂直分表的原理是根据字段的访问频率和数据量来确定哪些字段应该被分离到单独的表中。热点查询字段通常是那些经常被查询、过滤或排序的字段，而非热点查询字段则是那些不经常被使用的字段。以下是一些垂直分表的示例场景：订单表：假设一个订单表包含了大量的订单信息，包括订单 ID、订单日期、客户 ID、商品 ID、商品数量、订单状态等字段。其中，订单 ID、订单日期和订单状态可能是热点查询字段，而其他字段可能不经常被查询。在这种情况下，可以将热点查询字段分离到一个单独的表中，如订单主表，而将其他非热点字段放在另一个表中，如订单详情表。用户表：一个用户表可能包含用户 ID、用户名、电子邮件、地址、电话号码等字段。其中，用户 ID 和用户名可能是热点查询字段，而其他字段可能不经常被查询。在这种情况下，可以将热点查询字段放在一个表中，如用户主表，而将其他非热点字段放在另一个表中，如用户详细信息表。通过垂直分表，可以将热点查询字段和非热点查询字段分别存储在不同的表中，从而提高查询性能。这样，在查询热点字段时，只需要访问热点表，而不需要扫描整个数据表，减少了 I/O 操作和数据传输量。同时，垂直分表还可以更好地管理和维护数据，例如对热点表进行更频繁的索引维护、数据备份等操作。需要注意的是，在进行垂直分表时，需要考虑表之间的关联关系和数据完整性。通常需要通过主键或外键来建立表之间的关联，并确保数据的一致性和正确性。此外，垂直分表可能会增加查询的复杂性，需要合理设计查询语句和索引来优化查询性能。水平分表水平分表是指将一个数据量巨大的单表按照某种规则拆分为多个表，这些表仍然存储在同一个数据库中。水平分表可以提高查询性能、数据管理和维护的效率。以下是一些常见的水平分表方式：范围(RANGE)拆分：根据数据的范围将其分配到不同的表中。例如，可以将一个包含大量用户数据的表按照用户 ID 的范围拆分为多个表，如 0-10000 一个表，10001 到 20000 一个表。HASH 取模：通过使用哈希函数对某一列的值进行取模运算，然后根据计算结果将数据分配到不同的表中。例如，可以对用户 ID 进行取模运算，将其分配到不同的表中。地理区域：根据数据的地理位置进行拆分。例如，可以将一个包含全国用户数据的表按照华北、东北、华东等区域进行拆分。时间拆分：根据数据的时间属性进行拆分。例如，可以将一个包含多年数据的表按照年份或月份拆分为多个表，将较旧的数据放在单独的表中。这种方式常用于实现冷热数据分离，因为较旧的数据通常查询频率较低。通过水平分表，可以将大表拆分为多个较小的表，从而提高查询性能、数据管理和维护的效率。同时，水平分表还可以更好地支持分布式数据库架构和大规模数据处理。水平分库水平分库是将一个数据库中的数据按照某种规则拆分为多个数据库，每个数据库包含一部分数据。这样可以将数据分散到多个数据库中，提高数据库的负载能力和读写性能。以下是一些水平分库的示例场景：用户数据库：可以根据用户 ID 将用户数据分布到多个数据库中，每个数据库负责一部分用户的数据。商品数据库：可以根据商品 ID 将商品数据分布到多个数据库中，每个数据库负责一部分商品的数据。订单数据库：可以根据订单 ID 将订单数据分布到多个数据库中，每个数据库负责一部分订单的数据。通过将数据分散到多个数据库中，可以实现以下优势：提高负载能力：多个数据库可以分担负载，提高数据库的并发处理能力。提高读写性能：数据分散到多个数据库中，可以减少单个数据库的读写压力，提高读写性能。可伸缩性：随着数据量的增长，可以轻松地增加数据库的数量来扩容。需要注意的是，在进行水平分库时，需要考虑数据的分布规则和数据的一致性。通常需要通过主键或分布式 ID 来保证数据的唯一性和一致性。此外，水平分库可能会增加数据库之间的数据同步和事务处理的复杂性，需要合理设计数据同步机制和事务处理策略。在完成水平分库后，可以根据业务需求和数据量的增长情况，进一步进行水平分表，以提高数据库的性能和可伸缩性。
1.python 基础知识[post cid="2855" title="python 变量与数据类型(1.0)" /][post cid="2859" title="python 函数、运算符以及运算符优先级(2.1)" /][post cid="2857" title="python 字符串(字符序列)和字节序列(2.2)" /][post cid="2858" title="python 四大基础数据结构()及操作(2.3)" /][post cid="2863" title="python 流程、循环、异常、函数的高级用法(3.0)" /][post cid="2866" title="python 内置、匿名、高阶、递归等函数(3.1)" /][post cid="2869" title="python 作用域、闭包、装饰器(3.2)" /]2.python 进阶知识[post cid="2870" title="python 模块、time、datetime 导入使用(4.0)" /][post cid="2871" title="python 类的创建, 实例化, 初始化以及面向对象(4.1)" /][post cid="2872" title="python 类属性和实例属性、类方法, 静态方法, 实例方法、接口, 协议和抽象基类 (4.2)" /][post cid="2899" title="python的requests模块的介绍、多线程和多进程(5.0)" /][post cid="2901" title="python 进程间通信(IPC)(5.1)" /][post cid="2905" title="python 迭代器和生成器(5.2)" /]3.python TCP-IP协议相关与抓包[post cid="2911" title="输入网址后发生了什么? TCP/IP五层协议?" /][post cid="2917" title="理解TCP/IP协议、三次握手、四次挥手、HTTPS" /][post cid="2921" title="抓包、模拟请求" /]4.python 数据库相关[post cid="2928" title="数据库的基本介绍" /][post cid="2932" title="mysql 常用的数据类型" /][post cid="2933" title="mysql CRUD操作" /][post cid="2939" title="Mysql约束、分组查询" /][post cid="2940" title="mysql变量声明、存储过程、触发器" /][post cid="2941" title="用python连接数据库、索引、优化、事务" /]5.python 前端基础知识与爬虫相关[post cid="2952" title="一个请求的组成、静态页面和动态页面、HTML, CSS和JS、浏览器渲染的过程" /][post cid="2953" title="了解元素定位css-selector 、Python库BeautifulSoup 等" /][post cid="2956" title="一个小又全的爬虫项目包含哪些部" /]6.python Scrapy框架相关[post cid="2960" title="python 路径管理以及常见报错" /][post cid="2961" title="Scrapy爬虫框架介绍" /][post cid="2962" title="Scrapy的启动和debug、 Item、设置、中间件" /][post cid="2972" title="Scrapy的Meta、异常处理" /][post cid="2973" title="redis介绍、安装以及数据类型" /][post cid="2976" title="python 分布式爬虫" /][post cid="2977" title="Selenium 简单介绍、安装、启动" /]7.python Django 框架相关8.python 服务器与项目部署9.python JavaScript相关10.python JS加密相关11.python 数据结构与常用算法12.python 面试内容13.python 爬虫进阶
用python的pymysql包连接数据库pymysqlpip install pymysql #如果让你装vs环境, 执行以下命令升级pip即可 python -m pip install --upgrade pip连接数据库数据库设置MYSQL_CONF = { "host": "127.0.0.1", "user": "root", "password": "qwe369", "db": "test" }连接# 连接数据库 mysql_con = pymysql.connect(**MYSQL_CONF) # 简单理解真正执行语句的线程 mysql_cursor = mysql_con.cursor()执行SQL语句单条执行执行时间15.31s@clock_it_deco def insert_one(): for i in range(10**4): store_name = f"店铺_{i}" amount = format(random.uniform(10**3, 10**6), '.2f') department = f"事业部_{random.randint(1, 10)}" sta_date = time.strftime("%Y-%m-%d") SQL = f"""INSERT INTO store_perf(store_name, amount, department, sta_date) VALUES ('{store_name}', {amount}, '{department}', '{sta_date}')""" print(SQL) mysql_cursor.execute(SQL) # 显示执行commit, 避免cursor执行, 但是数据库未收到的执行指令的情况 mysql_con.commit()多条执行如果其中有一条记录报错, 那么整个values插入都会失败@clock_it_deco def insert_many(): values = [] for i in range(10**4): store_name = f"店铺_{i}" amount = format(random.uniform(10**3, 10**6), '.2f') department = f"事业部_{random.randint(1, 10)}" sta_date = time.strftime("%Y-%m-%d") values.append((store_name, amount, department, sta_date)) SQL = """INSERT INTO store_perf(store_name, amount, department, sta_date) VALUES (%s, %s, %s, %s)""" print(values) mysql_cursor.executemany(SQL, values) mysql_con.commit()获取返回值def get_shops(): SQL = "select store_name, amount, sta_date from store_perf where department='事业部_1' LIMIT 2" mysql_cursor.execute(SQL) # 获取返回值 query_set = mysql_cursor.fetchall() data_dict = [] for field in mysql_cursor.description: data_dict.append(field[0]) mysql_cursor.close() print(query_set,data_dict)返回的是一个元组, 元组中表示记录的也是一个元组(('店铺_1', Decimal('823471.56'), '2021-02-06'), ('店铺_6', Decimal('726927.02'), '2021-02-06'))索引和优化创建索引未建索引: 0.287s建了索引后: 0.016s设置索引的字段不可以超过191个字符长度, 也就是767个bytesCREATE INDEX 索引名 ON 表名(字段1, 字段2...) CREATE INDEX store_name_index ON store_perf(store_name)创建联合索引CREATE INDEX store_name_sta_date_department_index ON store_perf(store_name, sta_date, department)查看索引SHOW INDEX FROM store_perf删除索引ALTER TABLE store_perf DROP INDEX store_name_index查看当前查询语句有没有命中索引EXPLAIN语句查看当前语句执行性能如果key有值, 说明命中了索引, 且key值为索引名EXPLAIN SELECT * from store_perf WHERE store_name = "店铺_224123"单个字段可以命中联合索引吗?联合索引涉及到一个叫左缀查询的规则如果想命中索引, 查询语句中涉及到字段必须是联合索引创建时从左到右顺序原理在a_b_c_index这样一个联合索引当中, 实质执行中是先查出a的结果集, 然后再查b或c的结果集索引的实现原理B+树, 一种特殊的链表, 用来实现二分查找.如何优化mysql合理地建立索引频繁作为查询条件的字段应该建立索引唯一性太差的字段不适合单独建立索引更新非常频繁的字段不适合建立索引避免不经过索引的操作not in, !=等反向逻辑BETWEEN范围查找or逻辑两边都必须命中索引才会走索引联合索引, 不按左缀查询规则加缓存数据库缓存show VARIABLES LIKE '%query_cache%'用redis做缓存请求 -> redis -> 未命中 -> mysql -> 返回换固态硬盘事务事务的提交, 回滚START TRANSACTION; INSERT INTO store_perf(store_name, amount, department, sta_date) VALUES ('店铺_1764', 753294.41, '事业部_8', '2021-02-05'); ROLLBACK; COMMIT;业务中使用事务将class_2中的同学转移到class_1, 如果SQL_2报错, 会导致class_2中的同学丢失.def transaction(): try: SQL = "DELETE FROM class_2 where name='name_13'" mysql_cursor.execute(SQL) SQL_2 = "INSERT INTO class_1 VALUES(name)" mysql_cursor.execute(SQL_2) except Exception as e: print("raise Exceptions", e.args[0]) print("rollback") mysql_con.rollback() finally: mysql_con.commit()
变量声明服务器系统变量通过@@来调用系统变量# 列出mysql所有系统变量 SHOW VARIABLES SELECT @@date_format用户变量通过@来调用用户变量# 输出变量yesterday SELECT @yesterday # 对变量yesterday进行赋值 SET @yesterday=subdate(CURRENT_DATE, 1); # 日期的格式化 SET @yesterday=DATE_FORMAT(@yesterday, "%Y/%m/%d"); # 小数的格式化 SET @amount=0.4; SET @amount=CAST(@amount as DECIMAL(15, 3)); SELECT @amount局部变量不需要@前缀需要事先进行变量类型的声明和初始化存储过程简单地认为是SQL中的函数声明一个存储过程创建存储过程每一句语句结束之后都要添加分号;CREATE PROCEDURE stat_store_perf(days INT) BEGIN DECLARE t_date VARCHAR(255); set t_date = DATE_FORMAT(subdate(CURRENT_DATE, days), '%Y-%m-%d'); SELECT count(*) as 店铺数, FORMAT(AVG(amount), 2) as 平均业绩, SUM(amount) as 总业绩, MAX(amount) as 最大业绩, MIN(amount) as 最小业绩, department as 部门 FROM store_perf WHERE sta_date=t_date GROUP BY department; END调用存储过程CALL stat_store_perf(1)删除存储过程DROP PROCEDURE stat_store_perf触发器和存储过程一样, 都是嵌入到mysql中的一段程序, 区别就是存储过程需要显式调用, 而触发器式根据对表的相关操作自动激活执行.创建触发器CREATE TRIGGER 触发器名 BEFORE[AFTER] [INSERT, UPDATE, DELETE] CREATE TRIGGER check_department BEFORE INSERT ON store_perf FOR each row BEGIN IF new.department not in ("男装事业部", "女装事业部") THEN SET new.department 'unknow'; END IF; END触发器的应用多用来检查字段查看触发器建议通过客户端删除SHOW TRIGGERS FROM store_perf删除触发器建议通过客户端删除DROP TRIGGERS check_department
创建表的时候尽量加上create_time字段, 为create_time设置默认值CURRENT_TIMESTAMPCRUD操作Create(增)单条插入INSERT INTO 表名(字段1, 字段2..) VALUES(值1, 值2) INSERT INTO class_1(name) VALUES('name_1')多条插入INSERT INTO 表名(字段1, 字段2..) VALUES(值1, 值2), (值1, 值2)Retrieve(查)获取所有记录SELECT 字段1, 字段2 FROM 表名 SELECT name FROM class_1 SELECT * FROM class_1条件查询(WHERE, AND , OR)SELECT 字段1, 字段2 FROM 表名 WHERE 表达式 SELECT * FROM class_1 WHERE name="name_1" AND id=8模糊匹配(LIKE, %)模糊匹配有性能问题, 表记录如果比较多, 查询速度很慢SELECT 字段1, 字段2 FROM 表名 WHERE 表达式 SELECT * FROM class_1 WHERE name LIKE "%m%" 限制返回条数(LIMIT)查询表达式 LIMIT 数量过滤重复值(DISTINCT)对查询的结果进行过滤.如果指定多个字段, 会对多个字段联合进行过滤SELECT DISTINCT 字段1, 字段2 FROM 表名 [条件语句] SELECT distinct name, create_time FROM class_1 WHERE name like '%m%'排序问题对查询的结果进行排序升序(ASC)数据库默认是升序的查询表达式 ORDER BY 字段 ASC降序(DESC)查询表达式 ORDER BY 字段 DESC获取查询结果的条数(COUNT)SELECT COUNT(*) FROM 表名 [条件语句]Update(更新)UPDATE 表名 SET 字段1=新值, 字段2=新值 WHERE 表达式 UPDATE class_1 SET name='name_0' WHERE id=8Delete(删除)DELETE FROM 表名 WHERE 表达式 DELETE FROM class_1 WHERE name='name_0'集合操作并集UNION子语句SELECT 必须拥有相同数量的列(字段), 且列的数据类型也相同SELECT name from class_1 WHERE name is not NULL UNION SELECT name from class_2 WHERE name is not NULL交集(JOIN, INNER JOIN)SELECT s1.name FROM (SELECT name from class_1 WHERE name is not NULL) as s1 JOIN (SELECT name from class_2 WHERE name is not NULL) as s2 ON s1.name=s2.name差集(LEFT JOIN, RIGHT JOIN)A对B的差集(LEFT JOIN)SELECT * FROM // s1对s2的差集, select就可以使用s1.name (SELECT name from class_1 WHERE name is not NULL) as s1 LEFT JOIN (SELECT name from class_2 WHERE name is not NULL) as s2 ON s1.name=s2.name WHERE s2.name is NULL // 限定s1有, s2没有的记录B对A的差集(RIGHT JOIN)SELECT * FROM (SELECT name from class_1 WHERE name is not NULL) as s1 RIGHT JOIN (SELECT name from class_2 WHERE name is not NULL) as s2 ON s1.name=s2.name WHERE s1.name is NULL补集A与B的补集 = A与B的并集 - A与B的交集A与B的补集 = A对B的差集 + B对A的差集 √SELECT s1.name FROM (SELECT name from class_1 WHERE name is not NULL) as s1 LEFT JOIN (SELECT name from class_2 WHERE name is not NULL) as s2 ON s1.name=s2.name WHERE s2.name is NULL UNION SELECT s2.name FROM (SELECT name from class_1 WHERE name is not NULL) as s1 RIGHT JOIN (SELECT name from class_2 WHERE name is not NULL) as s2 ON s1.name=s2.name WHERE s1.name is NULL
常用的数据类型int数字范围-2**32 ~ 2**32-1bigint数字范围-2**63 ~ 2 ** 63-1floatfloat(m,d), 其中m表示的是有效位, d表示小数位 有效位就是把当前的小数: 12345.12 转变成科学计数法: 1.234512 * 10**5 m最大值为7doubledouble(m, d), 其中m表示的是有效位, d表示小数位 m的最大值为15decimal涉及金额的时候使用decimal.double(m, d), 其中m表示的是有效位, d表示小数位 m的最大值为65 不会产生精度问题, 因为decimal没有精度损失的本质是因为它存的是字符串.char表示固定长度的字符串, 长度为255个字节中文字符占据3~4个字节varchar表示不定长的字符串, 长度为0~65535个字节TEXT长文本类型, 最大长度占据64kbdatetime如果当前时区发生更改, datetime类型不会发生更改, 与存入的日期保持一致.timestamp如果当前时区发生更改, timestamp类型会跟着时区更改.常用的运算符算术运算符+-*/div取商SELECT 5 div 3mod取余SELECT 5 mod 3比较运算符=类似python中的 == SELECT 1 =1!=> 与>=<与<=between 与not betweenSELECT 2 not between 1 and 3is null 与 is not null用来判断当前是否有记录逻辑运算符NOTANDORXOR(了解)异或, 两个值一个True, 一个为False, 当前表达式才为True常用的函数算术运算SUM和AVG平均数MAX和MIN对字符串进行操作时, 类似python的排序, 是根据ascii码来排序的.COUNT计算当前记录数 SELECT count(*) from test字符处理CHAR_LENGTHSELECT CHAR_LENGTH("test")FORMAT格式化SELECT FORMAT(0.333333,2)LEFT和RIGHTSELECT right("abcdefg", 2)TRIMSELECT trim("abcdefg ")