A database optimization method and terminal for storing game props

Abstract

This invention provides a database optimization method and terminal for storing game items. The method involves obtaining general attributes of item data and extended attributes for each type of item data; constructing a general table based on the general attributes and an identifier field; and constructing an extended table based on the extended attributes and the identifier field. An extensible key-value pair field is added to the extended table to store variable attributes. This invention avoids the redundancy problem of distributing general attributes of different types of item data across multiple tables by storing them in a general table, thus improving retrieval speed. Furthermore, the addition of a key-value pair field to the extended table to store variable attributes allows for direct addition of new attributes using the key-value pair field without modifying the extended table structure, further reducing the impact on the original table structure, lowering the difficulty of extending attributes, and increasing the flexibility of modifying item data.

Description

Technical Field

[0001] This invention relates to the field of database design, and in particular to a database optimization method and terminal for storing game items. Background Technology

[0002] In most game development processes, database queries for item-related information (such as experience orbs) typically employ simple structured query languages ​​(such as SQL). For example, a basic SELECT statement is used to retrieve data from a database table based on specific conditions (such as item name or ID). Furthermore, the database table structure is usually quite conventional, storing various item attributes (such as name, required level, and whether it is exclusive) as separate columns in a table, with tables linked by primary keys and foreign keys (e.g., the experience orb table and the character table are linked by character ID).

[0003] This traditional approach can generally meet the needs of game operation when the data volume is small and the query requirements are simple. However, as the complexity of games increases and the number of players grows, the drawbacks of the traditional database model gradually become apparent, as shown in the following examples.

[0004] Inefficient querying: When frequent item information queries are required in the game, especially in high-concurrency scenarios (such as a large number of players querying simultaneously or concentrated queries during specific in-game events), simple SQL queries can lead to slow database responses. Because each query requires traversing the entire table or performing complex join operations, the load on the database server increases significantly, impacting game smoothness and user experience.

[0005] Data redundancy and inconsistency: Conventional data structure design leads to data redundancy. For example, if multiple different types of items have "name" and "required level" attributes, the traditional approach would repeatedly store these attribute columns in each item table, wasting storage space and increasing the risk of inconsistency during data updates. When it is necessary to modify the definition of an attribute (such as changing the calculation method of "required level"), operations need to be performed in multiple tables, which can easily lead to omissions or errors, resulting in data inconsistency and affecting the stability and accuracy of the game system.

[0006] Lack of flexibility and scalability: Traditional database designs struggle to adapt quickly to situations requiring new functionalities or query scenarios. For example, adding new query conditions (such as querying based on the range of energy core usage effects) or enabling more complex data interactions with other system modules (such as in-game social and quest systems) would require large-scale modifications to the existing database structure and query statements if the data is built using a traditional approach. This results in high development costs and long development cycles, hindering the game's continuous updates and expansion. Summary of the Invention

[0007] The technical problem to be solved by the present invention is to provide a database optimization method and terminal for storing game items, so as to improve the flexibility of item data storage and improve query efficiency.

[0008] A database optimization method for storing game items, the method comprising: Obtain the general attributes of the item data and the extended attributes of the item data for each type; A general table is constructed based on the general attributes and the identifier field, and an extended table is constructed based on the extended attributes and the identifier field; Add an extensible key-value pair field to the extended table, which stores variable attributes.

[0009] To solve the above-mentioned technical problems, another technical solution adopted by the present invention is as follows: A database optimization terminal for storing game items includes a memory, a processor, and a computer program stored in the memory and running on the processor. When the processor executes the computer program, it performs the following steps: Obtain the general attributes of the item data and the extended attributes of the item data for each type; A general table is constructed based on the general attributes and the identifier field, and an extended table is constructed based on the extended attributes and the identifier field; Add an extensible key-value pair field to the extended table, which stores variable attributes.

[0010] The beneficial effects of this invention are as follows: Common attributes in item data are extracted as general attributes and stored uniformly in a general table. Extended attributes that distinguish each type of item from the general attributes are stored in a dedicated extended table. Therefore, if new attributes are added to different types of items, only the corresponding extended table needs to be modified, without affecting the general table. This reduces the amount of modification and minimizes the impact on other item data that does not require modification. Simultaneously, storing general attributes in a general table avoids the redundancy problem of general attributes for different types of item data being scattered across multiple tables, improving retrieval speed. Furthermore, a key-value pair field is added to the extended table to store variable attributes. When a new attribute needs to be added, it can be directly added using the key-value pair field without modifying the extended table structure, further reducing the impact on the original table structure, lowering the difficulty of extending attributes, and increasing the flexibility of modifying item data. Attached Figure Description

[0011] Figure 1 A flowchart illustrating the steps of a database optimization method for storing game items, provided in an embodiment of the present invention; Figure 2 This is a schematic diagram of the structure of a database optimization terminal for storing game items, provided in an embodiment of the present invention; Label Explanation: 1. A database-optimized terminal for storing game items; 2. A processor; 3. A memory. Detailed Implementation

[0012] To explain in detail the technical content, objectives, and effects of the present invention, the following description is provided in conjunction with the embodiments and accompanying drawings.

[0013] Please refer to Figure 1 A database optimization method for storing game items, the method comprising: Obtain the general attributes of the item data and the extended attributes of the item data for each type; A general table is constructed based on the general attributes and the identifier field, and an extended table is constructed based on the extended attributes and the identifier field; Add an extensible key-value pair field to the extended table, which stores variable attributes.

[0014] As can be seen from the above description, the beneficial effects of this invention are as follows: Common attributes in item data are extracted as general attributes and stored uniformly in a general table. Extended attributes that distinguish each type of item from the general attributes are stored in a dedicated extended table. Therefore, if new attributes are added to different types of items, only the corresponding extended table needs to be modified, without affecting the general table. This reduces the amount of modification and minimizes the impact on other item data that does not require modification. Simultaneously, storing general attributes in a general table avoids the redundancy problem of general attributes of different types of item data being scattered across multiple tables, improving retrieval speed. Furthermore, a key-value pair field is added to the extended table to store variable attributes. When a new attribute needs to be added, it can be directly added using the key-value pair field without modifying the extended table structure, further reducing the impact on the original table structure, lowering the difficulty of extending attributes, and increasing the flexibility of modifying item data.

[0015] Furthermore, the general attributes, unique identifiers, and extended attributes of the item data for each type include: Obtain the attributes of prop data using entity-relationship model tools; The attribute that all types of the aforementioned item data possess is marked as a common attribute; In the item data of each type, the attributes other than the general attributes are marked as extended attributes.

[0016] As described above, after obtaining the attributes corresponding to the item data using the model tool, the corresponding attributes can be automatically obtained based on the item data. By comparison, the common attributes shared by all types of item data can be obtained. The common attributes are stored together in a common table, which includes the common attributes corresponding to all item data for easy retrieval. The amount of data stored in the common table is reduced, thereby reducing the amount of data that needs to be retrieved. At the same time, the extended attributes unique to each type are stored separately in an extended table to ensure data integrity.

[0017] Further, the step of constructing a general table based on the general attribute and the identifier field, and then constructing an extended table based on the extended attribute and the identifier field, includes: Establish a database transaction, and update the general table and the extended table according to the database transaction.

[0018] As described above, since the general table and the extended table are related, updating the general table and the extended table through database transactions can ensure data consistency between the two tables and guarantee data quality.

[0019] Furthermore, adding expandable key-value pair fields to the extended table includes: Determine whether the database described in the extended table supports JSON data type; If so, add a key-value pair field of type JSON to the extended table; Otherwise, create a key field and a value field as key-value pair fields.

[0020] As described above, when setting key-value pairs, different matching methods are used depending on whether the database supports JSON type. If the database itself supports JSON type, JSON type key-value pairs are directly added for adding new attributes later. If the database itself does not support JSON type, the key field and value field are combined as key-value pairs for adding new attributes later. Thus, key-value pairs can be added in various types of databases.

[0021] Furthermore, establishing the key field and value field as key-value pair fields includes: Create a unique index on the key field.

[0022] As described above, since the value needs to be uniquely identified by the key in a JSON structure, it is usually necessary to ensure the uniqueness of the key. For databases that do not support JSON structures, a unique index is created on the key field to restrict access and facilitate retrieval operations, based on the separate creation of key and value fields.

[0023] Furthermore, it also includes: Obtain historical query data and build an index based on the historical query data.

[0024] As described above, by building an index on frequently queried data based on historical query data, subsequent query requests do not require querying multiple tables; the query can be performed directly on the built index, further improving the efficiency of querying item data.

[0025] Furthermore, it also includes: The system receives a query request, retrieves target data from the cache based on the query request, and if retrieving the target data fails, it accesses the database, retrieves the target data from the database, and stores the target data in the cache.

[0026] As described above, by introducing caching, the target data is first queried in the cache. If the target data cannot be obtained, the database is then accessed, and the target data obtained from the database is stored in the cache. If the target data exists in the cache, there is no need to connect to the database to obtain the data, thereby further speeding up the query.

[0027] Furthermore, the database access includes: Obtain a database connection from the database connection pool and access the database based on the database connection.

[0028] As described above, establishing a connection to the database only when accessing it requires handshake and other verification steps, which is cumbersome and time-consuming. By using a database connection pool, a certain number of database connections are created and stored during system initialization. When an application needs to access the database, it can directly obtain an established connection from the database connection pool, avoiding the overhead of creating a connection every time the database is accessed, thus greatly improving system performance.

[0029] Furthermore, it also includes: Obtain the target attribute upon which the computed attribute depends, wherein the computed attribute is calculated based on the target attribute; The calculated attributes are pre-calculated based on the target attributes, and the pre-calculation results are stored in the extended table; Monitor whether the target attribute has changed. If so, recalculate the calculated attribute based on the changed target attribute and store the precalculation result in the extended table.

[0030] As described above, for game items, there are also calculated attributes that need to be calculated based on other attributes. If the stored value of this type of attribute is only represented by calculation, the specific value still needs to be calculated before being returned during the query, which increases the calculation steps and prolongs the query operation time. By pre-calculating the results and storing the pre-calculated results in an extended table, the pre-calculated results can be returned directly, skipping the step of starting the calculation during the query, thereby speeding up the query process. At the same time, in order to avoid errors in the returned pre-calculated results, a trigger mechanism is set up. When the target attribute associated with the calculated attribute is detected to change, the calculated attribute is automatically retrieved again based on the changed target attribute. At this time, the query request can obtain the calculated attribute corresponding to the changed target attribute.

[0031] Please refer to Figure 2 A database optimization terminal for storing game items includes a memory, a processor, and a computer program stored in the memory and running on the processor. When the processor executes the computer program, it implements the various steps of the aforementioned database optimization method for storing game items.

[0032] The database optimization method and terminal for storing game items described above are applicable to optimizing database storage performance, especially for optimizing the access to game item data. The following is a detailed description of the specific implementation method.

[0033] Please refer to Figure 1 A database optimization method for storing game items, comprising the following steps: S1. Obtain the general attributes of the item data and the extended attributes of the item data for each type.

[0034] In one alternative implementation, S1 includes S11-S13.

[0035] S11. Obtain the attributes of the prop data through the entity-relationship model tool.

[0036] Based on the design principles of attribute separation and standardized storage, the database table structure is designed using entity-relational (ER) modeling tools (such as ERWin or the ER design function of MySQL Workbench). A general item information table (e.g., "item_info") is created, containing basic attribute fields such as "item_id" (primary key, used to uniquely identify each item), "name" (item name), and "description" (item description). For specific attributes of the energy core, a separate extended table (e.g., "exp_ball_ext") is created, containing special fields related to experience balls, such as "role_type_effect" (the effect for different character types, which can be stored in JSON format for easy expansion) and "scene_related_attr" (attributes related to specific game scenes), and is associated with the general item information table through the "item_id" foreign key (the "item_id" field is defined in the "exp_ball_ext" table, and the foreign key constraint "FOREIGN KEY (item_id) REFERENCESitem_info(item_id)" is set). During the design process, database normalization theory (such as the third normal form) is followed to ensure data standardization and reduce data redundancy. For example, all non-primary attributes that do not depend on the primary key are fully dependent on the primary key to avoid partial and transitive dependencies, thereby improving data consistency and maintainability. Through reasonable table structure design, the core energy data is classified and stored according to commonalities and characteristics, laying the foundation for subsequent data operations and management.

[0037] S12. Mark the attribute that all types of the item data possess as a common attribute.

[0038] S13. Mark the attributes other than the general attributes in the item data of each type as extended attributes.

[0039] S2. Construct a general table based on the general attributes and the identifier field, and construct an extended table based on the extended attributes and the identifier field.

[0040] For example, for Energy Cores (i.e., experience orbs, items used to level up game characters), common attributes shared by all Energy Cores (such as name, ID, and description) can be extracted into a general table, while their unique extended attributes (such as effects on different character types or attributes related to specific game scenarios) can be stored in separate extended tables. These tables can be linked via foreign keys. This reduces data redundancy and improves data consistency and maintainability. For instance, the general table stores the basic name "name" and unique identifier "ID," while the extended tables store unique extended attributes such as "Effect for Character Type A" and "Effect for Character Type B," linked to the general table via the ID.

[0041] In one optional implementation, after S2, the process includes: establishing a database transaction and updating the general table and the extended table according to the database transaction. When inserting or updating energy core data, a database transaction is used (e.g., in Java, using JDBC's transaction management mechanism, starting a transaction through the `setAutoCommit(false)` method of the Connection object, committing the transaction using the `commit()` method after a series of operations, and rolling back the transaction using the `rollback()` method if an exception occurs) to ensure data integrity and consistency. When inserting data, basic attribute data is first inserted into the general item information table (e.g., "INSERT INTO item_info (item_id, name, description) VALUES(123,'Energy Core','Special item used to upgrade Qili level')"), and the inserted "item_id" value is obtained (which can be obtained using the database's auto-incrementing primary key or through a query). Then, based on "item_id", specific attribute data is inserted into the extended table (e.g., "INSERTINTO exp_ball_ext (item_id, role_type_effect, scene_related_attr) VALUES(123, '{"role_type_a": "Increase attack power", "role_type_b": "Increase magic resistance"}', '{"scene_a": "Increase experience gain speed", "scene_b": "Reduce damage received"}')"). In the update operation, the relevant fields in the general item information table are updated first, and then the specific attribute fields in the extended table are updated as needed to ensure data consistency across different tables. Simultaneously, database data integrity constraints (such as foreign key constraints, NOT NULL constraints, etc.) are used to prevent the insertion and updating of illegal data, ensuring data quality. That is, basic attribute data is first inserted into the general item information table to obtain the "item_id" value, and then specific attribute data is inserted into the extended table. The update operation follows the same principle, and data integrity constraints are used to prevent illegal data operations.

[0042] S3. Add an expandable key-value pair field to the extended table, whereby the key-value pair field stores mutable attributes. In this way, a key-value pair storage method or a similar expandable data structure is used to handle changing or newly added attributes. For new attributes appearing in the energy core (such as special attributes added in future game updates or attributes related to new gameplay), there is no need to modify the already designed database table structure; new key-value records can be added directly to the key-value pair storage area. This makes the database structure more flexible and can easily adapt to the continuous development and functional expansion needs of the game.

[0043] In one alternative implementation, S3 includes S31-S33.

[0044] S31. Determine whether the database in the extended table supports JSON data type. If yes, proceed to S32; otherwise, proceed to S33. In this way, appropriate column types are selected in different types of databases to implement a key-value pair storage structure, enabling scalability across different database types.

[0045] S32. Add a key-value pair field of type JSON to the extended table. For example, databases like PostgreSQL or MySQL 8.0 and above support the JSON type, and JSON type columns can be directly used to store the dynamic attributes of energy cores. For instance, add a "dynamic_attrs" field of type JSON to the extended table to store newly added or changed attributes. When a new dynamic attribute needs to be added, in databases that support the JSON type, the database's JSON operation functions (such as the JSON_SET function in MySQL) can be used to insert or update the attribute key-value pairs into the "dynamic_attrs" field. For example, "UPDATE exp_ball_ext SET dynamic_attrs = JSON_SET(dynamic_attrs, '$.new_attr_name', 'new_attr_value') WHERE item_id = 123" means adding a new attribute named "new_attr_name" with a value of "new_attr_value" to the experience ball with ID 123.

[0046] S33. Create a key field and a value field as key-value pair fields.

[0047] In an optional implementation, S33 includes: creating a unique index on the key field. In this way, even when the database itself does not support the JSON format, the effect of storing data in a simulated key-value pair format can be achieved through other methods; for example, creating two additional columns, "attr_key" and "attr_value," and managing the storage and retrieval of key-value pairs through program logic. When inserting or updating dynamic attributes, the attribute key and value are inserted into these two columns respectively, ensuring data consistency and uniqueness (e.g., by creating a unique index on the "attr_key" column).

[0048] In one alternative implementation, if the query request needs to include variable attributes, corresponding query conditions are constructed. If the extended table is established through step S32, the database's JSON query functions (such as the JSON_CONTAINS function in MySQL to determine if a JSON field contains a specific value, and the JSON_EXTRACT function to extract a specific attribute value from a JSON field) are used to construct the query conditions. For example, to query an experience ball with a specific dynamic attribute value, one could use "SELECT FROM exp_ball_ext WHERE JSON_CONTAINS(dynamic_attrs, 'new_attr_value', '$.new_attr_name')". If the extended table is established through step S33, the lookup is performed directly in the key field. After retrieving the query results from the database, a JSON parsing library (such as Jackson or Gson) is used to parse the JSON-formatted dynamic attribute data into objects or data structures that the program can process (such as a Map in Java or a custom class object). For the case of simulating key-value pair storage, the values ​​of the "attr_key" and "attr_value" columns are extracted from the query results and converted into a suitable key-value pair data structure through program logic, so that they can be processed and used in the game system, enabling flexible querying and manipulation of dynamic attributes.

[0049] In an optional implementation, the method further includes S4: acquiring historical query data and constructing an index based on the historical query data. For example, if common query scenarios for game item energy cores are identified during the query process, such as searching by name and filtering by required level range, then appropriate indexes are created in the database table accordingly. Specifically, a unique index is created on the name field of the energy core to ensure fast and accurate record location by name; a range index is created on the level field req_level to optimize the performance of level range queries. When creating query statements, these indexes can be fully utilized, such as using "WHERE name = 'specific name'" or "WHERE req_level BETWEEN minimum value AND maximum value," allowing the database to directly locate data rows that meet the conditions without a full table scan, greatly improving query speed.

[0050] In a specific implementation scenario, S4 includes: analyzing the Energy Core database tables using database design tools (such as MySQL Workbench, Oracle SQL Developer, etc.). Based on common query scenarios (such as exact queries by name, filtering by desired level range, searching by exclusive attributes, etc.), determining the fields that need indexing. For example, for the "name" field, since it is frequently used to precisely find a single experience ball, creating a unique index (using database-specific syntax, such as "CREATE UNIQUE INDEX index_name ON table_name (name)" in MySQL) ensures data uniqueness and speeds up queries. For the "req_level" field, considering frequent range queries (such as querying experience balls suitable for specific level range roles), creating a range index (such as "CREATE INDEX index_req_level ON table_name (req_level)").

[0051] During index creation, performance metrics such as query execution plans, index coverage, and selectivity are used to evaluate index effectiveness. By examining query execution plans (using the database's EXPLAIN command, such as "EXPLAIN SELECT FROM table_name WHERE name = 'specific name'"), analyze how the database engine chooses which queries to execute, ensuring the index is used correctly. Simultaneously, regularly maintain indexes, including rebuilding them (e.g., "REPAIRTABLE table_name QUICK" or "OPTIMIZE TABLE table_name" in MySQL) and monitoring index usage, promptly addressing unused or inefficient indexes. For example, calculate index selectivity (the ratio of unique index values ​​to the total number of table records). Higher selectivity indicates better and more efficient index performance. Optimize and adjust the index design based on the evaluation results, modifying or deleting indexes with low selectivity.

[0052] After determining the index design scheme, execute the index creation command in the database management system (DBMS) according to the index design scheme. In addition to the basic index types mentioned above, other index optimization strategies can be considered based on data distribution and query characteristics. For example, if the "monopoly" field has only a few distinct values ​​(such as "yes" or "no"), but is frequently used in combination with other conditions in some queries, a bitmap index can be created to improve query performance (e.g., "CREATE BITMAP INDEX index_monopoly ON table_name (monopoly)"). For compound query conditions (such as querying by both name and level range), a composite index can be created (e.g., "CREATE INDEX index_name_req_level ON table_name (name, req_level)"), ensuring that the index order matches the field order in the query conditions to fully utilize the index.

[0053] The example mentioned here uses the monopoly field to identify whether an item is a gift or not. If it is a gift, it is a non-tradable item; if it is not a gift, it is a tradable item. It is stored in binary format, for example, 0 indicates tradable and 1 indicates non-tradable.

[0054] After creating an index, it should be maintained regularly. This includes rebuilding the index using database-specific commands to optimize its structure, such as using MySQL's "REPAIR TABLE table_name QUICK" or "OPTIMIZE TABLE table_name" to rebuild the index and improve query efficiency. Index usage should also be monitored using database system performance monitoring tools or query system views to promptly identify and address unused or inefficient indexes, preventing excessive indexes from negatively impacting data update and insertion performance. In an optional implementation, the method further includes S5: receiving a query request, retrieving target data from the cache according to the query request, and if retrieving the target data fails, accessing the database, retrieving the target data from the database, and storing the target data in the cache. In this way, a caching layer (such as using Redis caching) is introduced to store frequently queried core energy information. When a client initiates a query request, it first checks if the required data exists in the cache. If the cache hits, the cached data is returned directly, avoiding database query operations and significantly reducing response time. For cache misses, a database query is executed, and the query result is stored in the cache, while a reasonable cache expiration time is set to ensure data timeliness and validity.

[0055] This involves selecting a suitable server, such as a Redis caching server, and using a corresponding caching client library (such as Jedis for Java projects to interact with Redis) on the game server side to establish a connection with the caching server. Configure a connection pool (e.g., using the Apache Commons Pool library to manage the Jedis connection pool), setting parameters such as maximum number of connections, minimum number of connections, and connection timeout to ensure efficient and stable acquisition and release of cached connections. Initialize the Jedis connection pool when the application starts, configuring a maximum number of connections of 100, a minimum number of connections of 10, and a connection timeout of 5000 milliseconds. When interaction with the caching server is needed, a connection is obtained from the connection pool, and the connection is promptly returned after the operation is completed to avoid resource waste and connection leaks.

[0056] In the caching server, naming rules for cache keys are designed to ensure fast and accurate storage and retrieval of energy core data. For example, the unique identifier ID field of the experience ball can be used as the cache key (e.g., "exp_ball:123" represents the experience ball with ID 123), or a cache key can be generated based on common query conditions (e.g., "exp_ball:name:specific name" represents the cache key for querying by name). Before storing data in the cache, serialization techniques (such as Kryo or Jackson libraries in Java to serialize objects into byte arrays) are used to convert energy core objects into a format suitable for storage in the cache, improving storage efficiency and retrieval speed. When a client initiates a query request, the server first generates a cache key based on the query conditions and then attempts to retrieve data from the cache. If the cache is hit (using the get method of the cache client library, such as "jedis.get(cacheKey)"), the data in the cache is directly deserialized (using the corresponding deserialization library method) into an object and returned to the client, avoiding database query operations. If the cache misses, a database query is performed. After the query is completed, the result is serialized and stored in the cache (using the set method of the cache client library, such as "jedis.set(cacheKey, serializedData)"). At the same time, a reasonable cache expiration time is set (such as 60 seconds to 600 seconds depending on the data update frequency) to ensure the timeliness and effectiveness of the cached data.

[0057] When a cache miss necessitates a database query, a SQL query statement is constructed based on the client's query request parameters. A database connection is obtained using a database connection pool (such as HikariCP or C3P0) via the pool's `getConnection` method, ensuring efficient and stable database access. For example, if the query condition is to find energy cores by name, the constructed SQL statement would be "SELECT FROM exp_ball_table WHERE name = 'specific name'"; if it's a query by level range, it would be "SELECT FROM exp_ball_table WHERE req_level BETWEENmin_level AND max_level". A Statement or PreparedStatement object is created using the database connection object (e.g., "Connection conn=connectionPool.getConnection(); PreparedStatement stmt= conn.prepareStatement(sql)"), and the query operation is executed (using the `stmt.executeQuery()` method) to obtain the query result set (ResultSet).

[0058] After retrieving the query results, the result set is iterated through, and the data in the result set is converted into a format suitable for client use (such as JSON format or a custom object model). During the iteration, based on the database table structure and field definitions, the value of each field is extracted and assigned to the corresponding object property (e.g., creating an ExpBall object, assigning the "name" field value from the result set to the ExpBall object's name property, and the "req_level" field value to the reqLevel property, etc.). For data requiring special processing (e.g., converting date type fields to strings in a specific format), appropriate conversion functions are used (e.g., the SimpleDateFormat class formats dates into "yyyy-MM-dd HH:mm:ss" format). The processed result data is encapsulated into a response object (e.g., creating a Response object containing a list of query results and status codes), and returned to the client via a network communication framework (e.g., Netty or Spring MVC's RESTful interface), ensuring that the client can correctly receive and parse the query results, completing a full query operation.

[0059] In one alternative implementation, for large-scale game systems, a distributed database architecture (such as a Hadoop-based distributed database or other distributed database solutions) is employed. Energy core data is distributed and stored across multiple nodes. Query tasks are assigned to the appropriate nodes for parallel processing based on query conditions, and the results are then aggregated and returned to the client. For example, data can be sharded according to different types or level ranges of experience points, and these shards can be stored on different nodes. When querying for a specific type or level range of experience points, the query operation is performed directly on the corresponding node, improving the parallel processing capability and overall performance of the query.

[0060] In one optional implementation, accessing the database in S5 includes: obtaining a database connection through a database connection pool and accessing the database based on the database connection.

[0061] In an alternative implementation, steps S61-S63 are also included.

[0062] S61. Obtain the target attribute upon which the calculated attribute depends, wherein the calculated attribute is calculated based on the target attribute. In the business logic layer of the game server, pre-calculation logic is designed based on the usage rules of the energy core and the relevant attributes of the game character. For example, based on factors such as the level of the experience orb, the character's current level, and the character type, the pre-calculation logic calculates the character's level increase and attribute change after using the experience orb. This involves the character's level increase attribute and attribute change value attribute associated with the experience orb, and the pre-calculation function is implemented using appropriate calculation algorithms (such as calculation models based on mathematical formulas or empirical algorithms designed according to game balance). For example, a formula for calculating the level increase of the character's power is designed as follows: Character level increase = Experience orb level × Character type coefficient + Random adjustment value (where the random adjustment value is randomly generated within a certain range to increase the uncertainty of the game). The pre-calculation logic is encapsulated into an independent function or method (e.g., creating a method named "calculateExpBallEffect" in Java to store the above formula).

[0063] S62. Pre-calculate the calculated attribute based on the target attribute, and store the pre-calculation result in the extended table. This method is called during game initialization to perform the pre-calculation. The calculation result can be stored in a dedicated pre-calculation result table (such as the "exp_ball_precalc" table), or corresponding fields (such as "precalc_level_increase", "precalc_attr_changes", etc.) can be added to the experience ball extended table to store the pre-calculation result, so that it can be directly used in subsequent queries, avoiding the performance overhead of real-time calculation.

[0064] S63. Monitor whether the target attribute has changed. If so, recalculate the calculated attribute based on the changed target attribute and store the pre-calculation result in the extended table. To ensure the accuracy and timeliness of the pre-calculation results, a data update triggering mechanism is established. When data related to the energy core changes (such as adjustments to experience ball levels, changes in character attributes, modifications to game balance parameters, etc.), an event-driven mechanism (such as using the observer pattern or message queue) is used to trigger the update operation of the pre-calculation results. For example, when the experience ball level is modified, an "exp_ball_updated" event is published. After the relevant listener receives the event, it calls the pre-calculation function to recalculate and update the pre-calculation results. At the same time, a scheduled task (such as using Quartz or Spring's scheduled task function) is set to periodically check and update the pre-calculation results. The scheduled task can run at a certain time interval (such as 2:00 AM every day) to check whether there is any pre-calculation data that needs to be updated (such as judging based on the last modification time of the data). If so, the pre-calculation logic is triggered to update, ensuring that the pre-calculation results are always consistent with the actual game data, providing accurate and efficient data support for the game system.

[0065] In this way, for frequently calculated query results (such as calculating the increase in power level after use based on the current character level and experience orb level), the calculations are performed in advance and stored in the database. In terms of data structure design, a new field is added to the extended table to store these pre-calculated results. When a query is performed, the pre-calculated value is returned directly, avoiding the performance overhead of real-time calculations, improving query response speed, and ensuring data accuracy and consistency.

[0066] Steps S4-S6 are only for distinguishing different execution steps and are not used to restrict the execution order between steps.

[0067] Please refer to Figure 2 A database optimization terminal 1 for storing game items includes a memory 3, a processor 2, and a computer program stored on the memory 3 and running on the processor 2. When the processor 2 executes the computer program, it implements each step of the aforementioned database optimization method for storing game items.

[0068] In summary, the database optimization method and terminal for storing game items provided by this invention divides item data into general attributes and extended attributes. A general table stores general attributes, while an extended table stores extended attributes. A JSON-structured key-value pair field is introduced into the extended table. This optimized data structure design reduces data redundancy, saves significant storage space, and lowers database storage costs. When inserting and updating data into the general and extended tables, the basic attribute is first inserted into the general table to obtain the primary key, and then specific attributes are inserted into the extended table based on the primary key. This ensures data integrity and consistency throughout the process, effectively avoiding consistency issues caused by scattered data storage. Furthermore, attribute separation, standardized storage, and dynamic attribute extension design make data updates and maintenance easier, effectively preventing data inconsistency. When the game development team needs to adjust the basic attributes of an energy core, they only need to modify the general item table; the related extended table data remains correctly linked, ensuring the accuracy and stability of the entire game system data and reducing game malfunctions and player complaints caused by data issues. The dynamic attribute extension design and optimized data pre-computation and storage allow the database to easily adapt to future functional expansions and gameplay changes in the game. Whether adding new energy core attributes, new query conditions, or engaging in deeper data interaction with other system modules, all can be achieved without large-scale modifications to the database structure. This significantly reduces game development and maintenance costs, accelerates game updates and iterations, and enables the game to respond promptly to market demands and player feedback, maintaining long-term competitiveness and appeal. When creating indexes, not only are indexes created for common single-field queries, but composite indexes are also created based on complex query conditions, ensuring that the index order matches the query condition field order to improve query performance. Regarding caching, carefully designed caching key-value rules enable fast and accurate storage and retrieval of item data, reducing unnecessary database queries. For large-scale game systems, distributed query processing is employed, rationally sharding data across multiple nodes and intelligently allocating tasks to the appropriate nodes for parallel processing based on query conditions, effectively alleviating database pressure during high-concurrency queries and improving overall query efficiency. For example, when a new character class or game mode is introduced, the system can quickly add corresponding attributes and calculation logic to the energy core without requiring long downtime for database maintenance, providing players with a continuously rich gaming experience. Furthermore, by optimizing query commands and utilizing caching mechanisms, database query response time is significantly reduced, especially under high-concurrency query conditions, enabling the system to quickly and accurately provide players with energy core information. For instance, in the in-game shop interface, when players frequently query the attributes and prices of different experience orbs, the system can respond instantly, improving the player's shopping experience; when viewing a preview of the effect before a character uses an experience orb, the fast query speed allows players to make timely decisions, enhancing the game's smoothness and interactivity.Furthermore, the use of distributed query processing further enhances the system's query processing capabilities, enabling it to handle complex query demands when a large number of players are online simultaneously, ensuring that the game system remains stable under high load.

[0069] The above description is merely an embodiment of the present invention and does not limit the patent scope of the present invention. Any equivalent modifications made based on the content of the present invention specification and drawings, or direct or indirect applications in related technical fields, are similarly included within the patent protection scope of the present invention.

Claims

1. A database optimization method for storing game items, characterized in that, The method includes: Obtain the general attributes of the item data and the extended attributes of the item data for each type; A general table is constructed based on the general attributes and the identifier field, and an extended table is constructed based on the extended attributes and the identifier field; Add an extensible key-value pair field to the extended table, which stores variable attributes.

2. The database optimization method for storing game items according to claim 1, characterized in that, The general attributes, unique identifiers, and extended attributes of each type of item data obtained include: Obtain the attributes of prop data using entity-relationship model tools; The attribute that all types of the aforementioned item data possess is marked as a common attribute; In the item data of each type, the attributes other than the general attributes are marked as extended attributes.

3. The database optimization method for storing game items according to claim 1, characterized in that, The step of constructing a general table based on the general attributes and the identifier field, and then constructing an extended table based on the extended attributes and the identifier field, includes: Establish a database transaction, and update the general table and the extended table according to the database transaction.

4. The database optimization method for storing game items according to claim 1, characterized in that, The addition of expandable key-value pair fields to the extended table includes: Determine whether the database described in the extended table supports JSON data type; If so, add a key-value pair field of type JSON to the extended table; Otherwise, create a key field and a value field as key-value pair fields.

5. The database optimization method for storing game items according to claim 4, characterized in that, The establishment of the key field and value field as key-value pair fields includes: Create a unique index on the key field.

6. The database optimization method for storing game items according to claim 1, characterized in that, Also includes: Obtain historical query data and build an index based on the historical query data.

7. The database optimization method for storing game items according to claim 1, characterized in that, Also includes: The system receives a query request, retrieves target data from the cache based on the query request, and if retrieving the target data fails, it accesses the database, retrieves the target data from the database, and stores the target data in the cache.

8. The database optimization method for storing game items according to claim 7, characterized in that, The database access includes: Obtain a database connection from the database connection pool and access the database based on the database connection.

9. The database optimization method for storing game items according to claim 1, characterized in that, Also includes: Obtain the target attribute upon which the computed attribute depends, wherein the computed attribute is calculated based on the target attribute; The calculated attributes are pre-calculated based on the target attributes, and the pre-calculation results are stored in the extended table; Monitor whether the target attribute has changed. If so, recalculate the calculated attribute based on the changed target attribute and store the precalculation result in the extended table.

10. A database optimization terminal for storing game items, comprising a memory, a processor, and a computer program stored in the memory and running on the processor, wherein the processor executes the computer program to implement any one of the database optimization methods for storing game items as described in 1-9.

Images