使用mysql federated引擎构建MySQL分布式数据库访问层_MySQL
bitsCN.com
使用mysql federated引擎构建MySQL分布式数据库访问层
本文所描述的 federated属于 MySQL的一种特殊引擎,利用它可将本地数据表映射至远程 MySQL 数据表,从而就可以解决应用程序中繁多的跨机器连接数据库问题,拓扑图如下:
如此就可以构造出一个统一的数据访问入口,就大大提高了整个数据库系统的可维护性。 Federated引擎是基于表级别的,只能将本地数据表定义为 Federated 引擎并映射至远程实体表,无法实现基于库级别的整体映射。
在本文中,我们将启用Federated 引擎的数据库访问入口服务器称为本地数据库,而将本地数据表对应的远程数据表,称之为实体表。
本地数据库需要启用Federated 引擎支持,而远程数据表无须 Federated 引擎支持。 Federated 引擎表使用标准的 MySQL 客户端协议与远程数据库建立 TCP 连接。
创建Federated 表的过程:
以root 登录远程 MySQL ,上创建合适的访问账号 grant all on DB1.* to 'federated'@'%' identified by 'federated'; flush privileges;
在远程MySQL 找到对应实体表的创建命令(如果是新表,请先建立好数据表,再执行此命令) 假设在远程mysql 上有库名 DB1, 表名 tag, 执行以下命令找到远程表的结构: show create table DB1.tag 输出: CREATE TABLE tag ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(128) NOT NULL, frequency int(10) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
假设我们要将远程的DB1.tag 映射至本地 DB.TableA 表上。那么我们应该保持本地虚拟表与远程实体表结构一致(结构可以有所差异,但会造成使用,管理上的麻烦)。根据远程实体表的创建命令,创建本地虚拟表 ( 结构部分完全一样,创建表选项有所差异 ) :
登录本地Mysql 服务器,创建相应的数据库及表: create database DB; use DB; CREATE TABLE TableA ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(128) NOT NULL, frequency int(10) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (id) ) ENGINE=federated connection="mysql://federated:federated@127.0.0.1 :3306/DB1/tag";
这时,即建立好了federated 虚拟表,实际上本地 MySQL 只创建了表定义文件 , 而没有数据文件。我们对本地虚拟表的数据修改,均会发送到远程机器上执行。
本地虚拟表名与远程表名,可不相同。
经过测试,这个引擎的一些额外特点: 1. 本地虚拟表与远程实体表之间是 TCP 长连接,并且是多个客户端利用的。所以不用担心因频繁建立连接带来的网络开销。 2. 本虚拟表表与远程实体表之间的网络连接断开后,当对虚拟表发起查询时,它会尝试重新连接远程实体表,所以我们不用担心网络连接断开造成的永久中断问题。 3. 如果无时间未对本地虚拟表作任何操作,虚拟表与实体表之间的连接将在远程主机的 wait_timeout 秒后自动断开,当对虚拟表发起查询时,连接又会重新建立。
一些注意事项: 1. 对本地虚拟表的结构修改,并不会修改远程表的结构 2. truncate 命令,会清除远程表数据 3. drop命令只会删除虚拟表,并不会删除远程表 4. 不支持 alter table 命令 目前使用federated 最大的缺点: 1. select count(*), select * from limit M, N 等语句执行效率非常低,数据量较大时存在很严重的问题,但是按主键或索引列查询,则很快,如以下查询就非常慢(假设 id 为主索引) select id from db.tablea where id >100 limit 10 ; 而以下查询就很快: select id from db.tablea where id >100 and id
如果虚拟虚拟表中字段未建立索引,而实体表中为此字段建立了索引,此种情况下,性能也相当差。但是当给虚拟表建立索引后,性能恢复正常。
类似 where name like "str%" limit 1 的查询,即使在 name 列上创建了索引,也会导致查询过慢,是因为 federated引擎会将所有满足条件的记录读取到本,再进行 limit 处理。
这几个问题已经严重影响了federated 在实际环境中的应用。
bitsCN.com
InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.

Key metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as Usingfilesort prompts that it needs to be optimized.

Usingtemporary indicates that the need to create temporary tables in MySQL queries, which are commonly found in ORDERBY using DISTINCT, GROUPBY, or non-indexed columns. You can avoid the occurrence of indexes and rewrite queries and improve query performance. Specifically, when Usingtemporary appears in EXPLAIN output, it means that MySQL needs to create temporary tables to handle queries. This usually occurs when: 1) deduplication or grouping when using DISTINCT or GROUPBY; 2) sort when ORDERBY contains non-index columns; 3) use complex subquery or join operations. Optimization methods include: 1) ORDERBY and GROUPB

MySQL/InnoDB supports four transaction isolation levels: ReadUncommitted, ReadCommitted, RepeatableRead and Serializable. 1.ReadUncommitted allows reading of uncommitted data, which may cause dirty reading. 2. ReadCommitted avoids dirty reading, but non-repeatable reading may occur. 3.RepeatableRead is the default level, avoiding dirty reading and non-repeatable reading, but phantom reading may occur. 4. Serializable avoids all concurrency problems but reduces concurrency. Choosing the appropriate isolation level requires balancing data consistency and performance requirements.

MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

The MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Dreamweaver CS6
Visual web development tools

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SublimeText3 Linux new version
SublimeText3 Linux latest version

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

WebStorm Mac version
Useful JavaScript development tools