{"id":203,"date":"2019-06-19T00:00:00","date_gmt":"2019-06-19T00:00:00","guid":{"rendered":"https:\/\/en.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/"},"modified":"2023-09-12T19:14:03","modified_gmt":"2023-09-13T02:14:03","slug":"best-practices-for-developing-applications-with-tidb","status":"publish","type":"post","link":"https:\/\/www.pingcap.com\/ko\/blog\/best-practices-for-developing-applications-with-tidb\/","title":{"rendered":"Best Practices for Developing Applications with TiDB"},"content":{"rendered":"<p><strong>Author:<\/strong> <a href=\"https:\/\/github.com\/tshqin\">Tianshuang Qin<\/a> (Principal Solutions Architect at PingCAP)<\/p>\n<h2><span class=\"ez-toc-section\" id=\"1_Introduction_target_audiences_and_why_this_blog_exists\"><\/span>1. Introduction: target audiences and why this blog exists<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Target audience: Database architects, database administrators, infrastructure engineers, or application developers.<\/p>\n<p>As an open source distributed database, in most cases, TiDB can serve as a scale-out MySQL database without manual sharding. However, because of its distributed nature, there are some differences between traditional relational databases like MySQL and TiDB. For detailed information, see <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/mysql-compatibility\">TiDB Compatibility with MySQL<\/a>.<\/p>\n<p>This article shows you how to efficiently develop high-quality applications with TiDB. You&#8217;ll get an in-depth look at several TiDB features that will save you hours of work and prevent some common coding errors. You&#8217;ll also learn important best practices from the many TiDB users that have come before you.<\/p>\n<p>This article is also useful if you&#8217;re migrating your current applications to TiDB.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"2_Transaction_in_TiDB_and_what_it_means_to_you\"><\/span>2. Transaction in TiDB and what it means to you<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>This section is an in-depth look at transaction-related issues in TiDB and provides solutions to prevent or resolve them.<\/p>\n<h3>2.1 Snapshot isolation in TiDB: Jepsen tested! And a few caveats<\/h3>\n<p>TiDB supports snapshot isolation (SI) as is shown in <a href=\"https:\/\/www.pingcap.com\/ko\/blog\/tidb-passes-jepsen-test-for-snapshot-isolation-and-single-key-linearizability\/\">TiDB Passes Jepsen Test for Snapshot Isolation and Single-Key Linearizability<\/a>. For more information about the TiDB transaction model and translation level, see <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/transaction-overview\">Transaction Model<\/a> \uadf8\ub9ac\uace0 <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/transaction-isolation-levels\">TiDB Transaction Isolation Levels<\/a>.<\/p>\n<h3>2.2 Solution to hot record scenarios where the same row is modified concurrently<\/h3>\n<p>TiDB uses the optimistic locking mechanism. This means that TiDB automatically retries the transactions that meet conflicts when being committed and backs off when TiDB handles concurrent transactions. However, if you specify the <code>SELECT FOR UPDATE<\/code> statement or disable the <code>tidb_disable_txn_auto_retry<\/code> variable, this backoff mechanism becomes invalid, and the transactions committed later are rolled back.<\/p>\n<p><code>SELECT FOR UPDATE<\/code> is applied in hot record scenarios including the following:<\/p>\n<ul>\n<li>Counter, in which the value of a field is continuously increased by 1.<\/li>\n<li>Seckilling, in which newly-advertised goods quickly sell out.<\/li>\n<li>Account balancing in some financial scenarios with &#8220;hot Region&#8221;, in which the same row of data is modified concurrently.<\/li>\n<\/ul>\n<p>Generally, traditional standalone DBMSs use pessimistic locking to implement <code>SELECT FOR UPDATE<\/code>. After a transaction starts, they check locks. If the lock required by the transaction and the current lock on the data are incompatible, a lock wait occurs, and the transaction can be executed after the current lock is released. TiDB executes <code>SELECT FOR UPDATE<\/code> just like setting the lock wait time to 0 in a pessimistic locking system, and a transaction encountering lock conflicts fails to commit.<\/p>\n<p>To sum up, TiDB doesn&#8217;t apply to scenarios where the same row of data is modified concurrently. Using <code>SELECT FOR UPDATE<\/code> in a transaction can guarantee data consistency, but only the transaction committed earliest among the concurrently-executed transactions can be executed successfully. TiDB rolls back the remaining requests.<\/p>\n<p>The best practice to handle a hot record scenario is to transfer and implement the counter feature in the cache (like Redis and Codis).<\/p>\n<p>In a database with pessimistic locking applied, the concurrent <code>SELECT FOR UPDATE<\/code> transactions queue up and are executed serially. Therefore, the performance is not good. However, handling the counter with the cache improves performance.<\/p>\n<h3>2.3 &#8220;Nested transaction&#8221;<\/h3>\n<h4>2.3.1 Nested transactions in most RDBMS products<\/h4>\n<p>According to the <a href=\"https:\/\/en.wikipedia.org\/wiki\/ACID\">ACID<\/a> (Atomicity, Consistency, Isolation, Durability) theory, concurrent transactions should be isolated from each other to avoid mutual interference. This means that transactions cannot be &#8220;nested.&#8221;<\/p>\n<p>At the read committed (RC) isolation level, if multiple reads exist in the same transaction, the data is read each time the data is committed. When multiple transactions execute concurrently, multiple read results in a transaction may be very different. These are called &#8220;non-repeatable reads.&#8221;<\/p>\n<p>Most RDBMS products use RC as the default isolation level. However, sometimes database application developers don&#8217;t pay attention to the isolation level setting. They even treat non-repeatable reads as a feature, and develop applications based on &#8220;nested transactions.&#8221;<\/p>\n<h4>2.3.2 Nested transaction model does not apply to TiDB<\/h4>\n<p>This section gives an example to explain why the nested transaction model does not apply to TiDB.<\/p>\n<p><strong>2.3.2.1 Example for a set of nested transactions<\/strong><\/p>\n<p>The following diagram shows the implementation logic for a set of nested transactions. At the top, session 1 and session 2 are two sessions initiated by the application. Down the left side, T1- T8 constitute a timeline. The logic is as follows:<\/p>\n<ol>\n<li>The application opens session 1 at T1, and then performs a query. (Note that in the MySQL protocol, the first statement that follows <code>begin<\/code> and accesses the table data is the start of a transaction.)<\/li>\n<li>From T3 to T5, the application opens session 2, writes a row of data, and then commits the data.<\/li>\n<li>The application continues to manipulate session 1.\n<ol>\n<li>At T6, it tries to update the data just written.<\/li>\n<li>At T7, it commits the transaction opened at T2.<\/li>\n<li>At T8, session 1 executes a query statement to check the <code>val<\/code> value of the corresponding row for <code>k=1<\/code> that is written by session 2 at T4.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p>At the RC isolation level, the returned result for the query at T8 is <code>102<\/code>, which seems to meet the feature requirement for a nested transaction. But this process does not conform to reality. The diagram above only uses a single thread to simulate a nested transaction. But in the concurrent requests in practical application scenarios, multiple transactions are alternately executed and committed in the timeline. In this case, we can&#8217;t predict the execution result of the nested transactions.<\/p>\n<p>At the snapshot isolation (SI) or repeatable read (RR) isolation levels, the returned result of any reads before being committed or rolled back corresponds to the consistency status when the transaction starts. The data accessible to the transaction in session 1 at T2 is determined. This is just like taking a snapshot of the database at T2. Even though session 2 is open from T3 to T5, and some data is written in and committed, this does not affect the data read by session 1 at T6. At T6, the row with <code>k=1<\/code> has not been read, so Row 0 is updated. At T8, the returned value for the query is 2. At the SI or RR isolation levels, the degree of isolation is higher, and for concurrent requests, the results are predictable.<\/p>\n<p><strong>2.3.2.2 Solution to the example<\/strong><\/p>\n<p>For these cases of nested transactions, if you only require that session 1 update the table after session 2 writes data into the table, you only need to control the application logic by adding a commit step after querying the statement at T2. This commits the query transaction in a timely manner. We then perform the rest of the steps on the timeline after T2.<\/p>\n<h3>2.4 No support for the <code>PROPAGATION_NESTED<\/code> in the Java Spring Framework (relying on the savepoint mechanism)<\/h3>\n<p>The Java Spring Framework supports<code>PROPAGATION_NESTED<\/code> propagation, and it starts a nested transaction, which is a subtransaction started independently of the existing transaction. When a nested transaction starts, the database records a savepoint. If the nested transaction fails to be executed, the database rolls back the transaction to the savepoint status. The nested transaction is part of the outer transaction, and it is committed with the outer transaction. The following commands show a savepoint mechanism:<\/p>\n<pre><code>mysql&gt; BEGIN;\nmysql&gt; INSERT INTO T2 VALUES(100);\nmysql&gt; SAVEPOINT svp1;\nmysql&gt; INSERT INTO T2 VALUES(200);\nmysql&gt; ROLLBACK TO SAVEPOINT svp1;\nmysql&gt; RELEASE SAVEPOINT svp1;\nmysql&gt; COMMIT;\nmysql&gt; SELECT * FROM T2;\n+------+\n|  ID   |\n+------+\n|  100 |\n+------+\n<\/code><\/pre>\n<p>TiDB does not support the savepoint mechanism, and therefore it does not support the <code>PROPAGATION_NESTED<\/code> propagation behavior. If a <code>PROPAGATION_NESTED<\/code> propagation behavior is applied to an application based on the Java Spring Framework, you need to adjust the client by removing the nested transaction logic.<\/p>\n<h3>2.5 Large transactions<\/h3>\n<p>TiKV, the storage engine of TiDB is based on RocksDB which adopts the log-structured merge-tree (LSM-tree). For large transactions in a log-based database, you must manually set the available log capacity to a larger value to prevent a single transaction from filling the log.<\/p>\n<p>TiDB sets a hard limit for the number of transactions. Due to the two-phase commit in TiDB, modifying data in large transactions might cause some problems. Therefore, to reduce this impact, TiDB sets a limit for the transaction size.<\/p>\n<p>TiKV stores data in key-value pairs, and the transaction limit is based on the size and number of these pairs. One row of data a table in TiDB (or in the concept of other traditional relational databases) is mapped into a key-value pair, and so is an index. When a table has only two indexes, three key-value pairs are written to the database each time a row of data is inserted. Based on these assumptions, the transaction limit is as follows:<\/p>\n<ul>\n<li>The number of SQL statements in a transaction does not exceed 5,000. This is the default, but you can configure it.<\/li>\n<li>The size of each key-value pair does not exceed 6 MB.<\/li>\n<li>The total size of key-value pairs does not exceed 100 MB.<\/li>\n<li>The total number of key-value pairs does not exceed 300,000.<\/li>\n<\/ul>\n<p>To stay within these limits, you must reduce the number of transactions on certain operations. These include transactions with <code>Create<\/code>, <code>Delete<\/code>, \uadf8\ub9ac\uace0 <code>Update<\/code> operations on large amounts of data. The best practice is to rewrite large transactions as paged SQL statements and to commit these statements in phases. TiDB uses <code>Order by<\/code> along with the offset of <code>\ud55c\uacc4<\/code> to implement the paging feature. For example:<\/p>\n<pre><code>update tab set value='new_value' where id in (select id from tab order by id limit 0,10000);\ncommit;\nupdate tab set value='new_value' where id in (select id from tab order by id limit 10000,10000);\ncommit;\nupdate tab set value='new_value' where id in (select id from tab order by id limit 20000,10000);\ncommit;\n...\n<\/code><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"3_Auto-increment_IDs_in_TiDB_monotonically_increasing_but_not_necessarily_increasing_sequentially\"><\/span>3. Auto-increment IDs in TiDB: monotonically increasing but not necessarily increasing sequentially<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>This section introduces allocating principles for auto-increment IDs in TiDB, the best practice for designing auto-increment IDs, and how to use auto-increment IDs in TiDB.<\/p>\n<h3>3.1 Allocating principles for auto-increment IDs in TiDB<\/h3>\n<p>TiDB&#8217;s auto-increment ID feature is only guaranteed to be automatically monotonic(incremental and unique), but it is not guaranteed to be allocated sequentially. Currently, TiDB allocates IDs in batches. If data is inserted into multiple TiDB servers simultaneously, the allocated IDs are not sequential. When multiple threads concurrently insert data into multiple tidb-server instances, the data inserted later may have a smaller auto-increment ID. TiDB lets you specify <code>AUTO_INCREMENT<\/code> for the integer field, but allows only one <code>AUTO_INCREMENT<\/code> field in a single table.<\/p>\n<h3>3.2 Best practice for auto-increment ID design<\/h3>\n<p>Generally, the purpose for setting auto-increment IDs is to use them as the unique restriction for data in a table. Thus, auto-increment IDs are designed as the primary key or unique index, and the property of these columns should be <code>not null<\/code>.<\/p>\n<p>The type for auto-increment ID columns should be integer. Among multiple integer types, it is recommended to use <code>bigint<\/code>. The reason is that auto-increment IDs of the <code>int<\/code> type are usually used up even in a standalone database. TiDB handles much more data than a standalone database, and it allocates auto-increment IDs in a multi-threading framework. As a result, the <code>int<\/code> type can&#8217;t meet the demand. Generally, auto-increment IDs don&#8217;t store negative numbers, and adding the &#8220;unsigned&#8221; property for a column can double the ID storage capacity. The range for unsigned <code>int<\/code>s is from 0 to 4294967295, while the range for unsigned <code>bigint<\/code>s is from 0 to 18446744073709551615.<\/p>\n<p>In conclusion, the best practice for auto-increment ID design is:<\/p>\n<pre><code>`auto_inc_id` bigint unsigned not null primary key auto_increment comment 'auto-increment ID'\n<\/code><\/pre>\n<h3>3.3 How to auto-increment IDs in TiDB<\/h3>\n<p>As mentioned previously, auto-increment IDs are mostly designed as primary keys or unique indexes. You shouldn&#8217;t manually assign values for auto-increment IDs. This may cause frequent update requests for maximum values of many global auto-increment IDs, thereby affecting write performance.<\/p>\n<p>While writing data, you don&#8217;t have to specify the auto-increment ID column in TiDB. TiDB automatically assigns values for it. Of course, you can specify an incremental ID column for writing. Just set the value to write as <code>NULL<\/code>, and TiDB automatically assigns values for it.<\/p>\n<pre><code>mysql&gt; create table autoid(`auto_inc_id` bigint unsigned not null primary key auto_increment comment 'auto-increment ID', b int);\nQuery OK, 0 rows affected (0.25 sec)\n\nmysql&gt; insert into autoid(b) values(100);\nQuery OK, 1 row affected (0.19 sec)\n\nmysql&gt; insert into autoid(b) values(200);\nQuery OK, 1 row affected (0.03 sec)\n\nmysql&gt; insert into autoid(b) values(300);\nQuery OK, 1 row affected (0.06 sec)\n\nmysql&gt; insert into autoid values(null,1000);\nQuery OK, 1 row affected (0.04 sec)\n\nmysql&gt; insert into autoid values(null,2000);\nQuery OK, 1 row affected (0.03 sec)\n\nmysql&gt; insert into autoid values(null,3000);\nQuery OK, 1 row affected (0.03 sec)\n\nmysql&gt; select * from autoid;\n+-------------+------+\n| auto_inc_id | b    |\n+-------------+------+\n|           1 |  100 |\n|           2 |  200 |\n|           3 |  300 |\n|           4 | 1000 |\n|           5 | 2000 |\n|           6 | 3000 |\n+-------------+------+\n6 rows in set (0.01 sec)\n<\/code><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"4_Constraints_in_TiDB\"><\/span>4. Constraints in TiDB<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>This section describes constraints used in TiDB and digs deep into a constraint check issue in TiDB.<\/p>\n<h3>4.1 Primary key and unique index<\/h3>\n<p>TiDB uses primary keys and unique indexes as UNIQUE constraints for table data. This approach is similar to other database management systems. However, be aware of the following differences:<\/p>\n<ul>\n<li>In TiDB, when you create a table, you must state the primary key. As of the current version (V2.1.0 and earlier), after you create a table, you can&#8217;t add, modify, or delete the primary key. This constraint doesn&#8217;t affect unique indexes.<\/li>\n<li>The <code>Drop Column<\/code> operation doesn&#8217;t let you delete primary key columns.<\/li>\n<\/ul>\n<h3>4.2 Foreign keys, not supported by TiDB, but shown in <code>information_schema<\/code><\/h3>\n<p>TiDB doesn&#8217;t support foreign keys. However as a reference, the parser imports them and shows the relations in the <code>information_schema<\/code>. You can only perform cascade operations on multiple tables with foreign keys within an application.<\/p>\n<h3>4.3 By default, <code>INSERT<\/code> only performs UNIQUE constraint check upon committing<\/h3>\n<p>TiDB adopts an optimistic transaction model. During the commit phase, TiDB tries to land all write operations into the storage engine, which makes a heavier commit load compared to other databases that use pessimistic transaction models.<\/p>\n<p>The following DML statements involve read operations on table data:<\/p>\n<ul>\n<li><code>UPDATE<\/code><\/li>\n<li><code>DELETE<\/code><\/li>\n<li><code>MERGE\/UPSERT<\/code> (not supported by TiDB)<\/li>\n<li><code>INSERT<\/code> (<code>INSERT IGNORE<\/code>, <code>INSERT ON DUPLICATE KEY UPDATE<\/code>, <code>INSERT IGNORE ON DUPLICATE KEY UPDATE<\/code>)<\/li>\n<\/ul>\n<p>When the table contains a primary key or a unique index, SQL semantics imply a UNIQUE constraint check of the table. This requires the verification to be performed once the DML statements above read the data. TiDB implements this requirement.<\/p>\n<p>In DML, only <code>INSERT<\/code> statements are pure write operations. There are exceptions, however. The following special <code>INSERT<\/code> statements aren&#8217;t pure writes:<\/p>\n<ul>\n<li><code>INSERT IGNORE<\/code><\/li>\n<li><code>INSERT ON DUPLICATE KEY UPDATE<\/code><\/li>\n<li><code>INSERT IGNORE ON DUPLICATE KEY UPDATE<\/code><\/li>\n<\/ul>\n<p>A primary key or a unique index on the table implies the SQL semantics in the INSERT statement to read the corresponding record in the table. If nothing is returned for the read. This line of the record is available for the write; if any data is returned, it means this line doesn&#8217;t satisfy the UNIQUE constraint of the table.<\/p>\n<p>To improve execution efficiency, TiDB doesn&#8217;t compare the records in the table when it executes <code>INSERT<\/code> statements. Instead, it verifies that records are unique when it commits the transaction. This approach saves some read operations. The performance advantages are especially prominent when there are many records involved in the <code>INSERT<\/code> statement, such as a batch insert.<\/p>\n<p>However, this implementation is not flawless. When there are too many write records in a single transaction, if there is any conflict of primary key or unique index between the data for write and existing data in the table, TiDB waits until the commit to report the error and rolls back the whole transaction. In the meantime, some applications may capture the information returned by the <code>INSERT<\/code> statement. Based on the captured information, TiDB determines subsequent execution logics for the application. By default, TiDB does not return errors for duplicate primary keys or unique indexes during the execution of the <code>INSERT<\/code> statement. This type of information is only returned upon <code>COMMIT<\/code>.<\/p>\n<p>To conclude, TiDB by default doesn&#8217;t behave as shown in the screenshot below. The first error occurs because the transaction contains two records with the same primary key, which indicates that TiDB performs uniqueness verification on all records involved in the transaction. The second error happens when the transaction is committed, which indicates that TiDB by default only compares the <code>INSERT<\/code> records involved in the transaction with the records in the table when it commits the transaction.<\/p>\n<pre><code>mysql&gt; create table t1 (a int key);\nQuery OK, 0 rows affected (0.23 sec)\n\nmysql&gt; insert into t1 values(1);\nQuery OK, 1 row affected (0.15 sec)\n\nmysql&gt; begin;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql&gt; insert into t1 values(1);\nQuery OK, 1 row affected (0.00 sec)\n\nmysql&gt; insert into t1 values(1);\nERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'\nmysql&gt; commit;\nERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'\n<\/code><\/pre>\n<p>TiDB provides a switch \u2013 <code>tidb_constraint_check_in_place<\/code> \u2013 to control this behavior. The default value, <code>0<\/code>, means that uniqueness verification is only performed when the transaction is committed instead of while an <code>INSERT<\/code> statement is executed. If you&#8217;re sure about uniqueness verification during the <code>INSERT<\/code> execution, you can set this variable to <code>1<\/code>. It&#8217;s recommended that you set it at the session level for every application so that the write performance of batch insert operations won&#8217;t be affected, as shown below:<\/p>\n<pre><code>set @@session.tidb_constraint_check_in_place=1;\n<\/code><\/pre>\n<p>So far, we have only noticed that Oracle GoldenGate (OGG) users need to set this system variable, because OGG does the constraint check in place. For OGG users, update the Java Database Connectivity (JDBC) connection string as shown below:<\/p>\n<pre><code>jdbc:mysql:\/\/192.168.1.20:4000\/dbname?tidb_constraint_check_in_place=1\n<\/code><\/pre>\n<div class=\"trackable-btns\"><a href=\"\/ko\/download\/\"><button>Download TiDB<\/button><\/a><br \/>\n<a href=\"https:\/\/share.hsforms.com\/1e2W03wLJQQKPd1d9rCbj_Q2npzm\"><button>Subscribe to Blog<\/button><\/a><\/div>\n<h2><span class=\"ez-toc-section\" id=\"5_TiDB_supported_indexes_primary_key_indexes_unique_indexes_and_secondary_indexes\"><\/span>5. TiDB supported indexes: primary key indexes, unique indexes, and secondary indexes<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>This section describes how TiDB uses indexes and their current restrictions. It also describes how composite indexes are designed in TiDB.<\/p>\n<p>Indexes are also data that take up storage. Like the data in a table, indexes in TiDB are also stored as key-value (KV) pairs in the storage engine. An index row is a key-value pair. If we have a table with 10 indexes, 11 KV pairs are written each time we insert a row of data.<\/p>\n<p>TiDB supports primary key indexes, unique indexes, and secondary indexes. These indexes can be composed of either a single column or multiple columns (which is called a composite index).<\/p>\n<p>The current version of TiDB (V2.1.0) doesn&#8217;t support bi-directional indexes, full-text indexes, or global indexes for partitioned tables.<\/p>\n<p>TiDB lets you use indexes when the query predicate is among the following:<\/p>\n<pre><code>=, &gt;, &lt;, &gt;=, &lt;=, like '...%', not like '...%', in, not in, &lt;&gt;, !=, is null, is not null\n<\/code><\/pre>\n<p>The optimizer determines whether to use indexes.<\/p>\n<p>You can&#8217;t use an index when the query predicate is among the following:<\/p>\n<pre><code>like '%...', like '%...%', not like '%...', not like '%...%', &lt;=&gt;\n<\/code><\/pre>\n<blockquote><p><strong>Note:<\/strong><\/p>\n<ul>\n<li>The current version of TiDB (V2.1.0) hasn&#8217;t implemented <code>&lt;=&gt;<\/code> so it can&#8217;t use indexes as &#8220;is null.&#8221;<\/li>\n<li>The current version of TiDB doesn&#8217;t support using two indexes simultaneously in one table for the query against the same table. The related optimizations are still under development.<\/li>\n<\/ul>\n<\/blockquote>\n<h3>5.1 Design of composite indexes<\/h3>\n<p>A composite index in TiDB is structured as <code>key tablekeyname (a,b,c)<\/code>. Like other databases, the ground rule for designing composite indexes is to place data with a high degree of discrimination in front as much as possible. This approach facilitates SQL execution by filtering out fewer rows within a shorter time. It&#8217;s worth noting that in the current version of TiDB (V.2.1.0 or earlier), the range query for a front column in the composite index terminates the use of subsequent index columns. You can get a better idea about this feature by reviewing the following sample query:<\/p>\n<pre><code class=\"language-sql\">select a,b,c from tablename where a&lt;predicate&gt;'&lt;value1&gt;' and b&lt;predicate&gt;'&lt;value2&gt;' and c&lt;predicate&gt;'&lt;value3&gt;';\n<\/code><\/pre>\n<ul>\n<li>If the predicate of condition <strong>a<\/strong> is <code>=<\/code> \ub610\ub294 <code>in<\/code>, composite index <code>(a,b,c)<\/code> can be used with query condition <strong>b<\/strong>, for example:\n<pre><code class=\"language-sql\">select a,b,c from tablename where a=1 and b&lt;5 and c='abc'\n<\/code><\/pre>\n<\/li>\n<li>If the predicate is <code>=<\/code> \ub610\ub294 <code>in<\/code> for both condition <strong>a<\/strong> and condition <strong>b<\/strong>, composite index <code>(a,b,c)<\/code> can be used for condition <strong>c<\/strong>, for example:\n<pre><code class=\"language-sql\">select a,b,c from tablename where a in (1,2,3) and b=5 and c='abc'\n<\/code><\/pre>\n<\/li>\n<li>If the predicate of condition <strong>a<\/strong> is neither <code>=<\/code> nor <code>in<\/code> , composite index <code>(a,b,c)<\/code> is not available for condition <strong>b<\/strong>, in which case an indexless data scan per condition <strong>b<\/strong> is performed against the filtered data of condition <strong>a<\/strong>. For example:\n<pre><code class=\"language-sql\">select a,b,c from tablename where a&gt;1 and b&lt;5 and c='abc'\n<\/code><\/pre>\n<p>This is because in TiDB, if the front column in the composite index is used in a range query, queries in subsequent columns proceed as indexless scans within the filtered data of the previous column.<\/li>\n<\/ul>\n<p>To conclude, when you design composite indexes in TiDB, you should place columns with a high degree of discrimination in front as much as possible, and columns for frequent range queries in the back.<\/p>\n<p>Also, composite index <code>(a,b,c)<\/code> is available for the query structured as <code>select c, count(*) from tablename where a=1 and b=2 group by c<\/code>, and the <code>where<\/code> clause complies with the above principle.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"6_Write_optimization_in_batch_job_scenarios\"><\/span>6. Write optimization in batch job scenarios<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Write-back in batch job scenarios is a general cause of write hotspots. TiKV is a range-based key-value system, where the key determines which Region is written. The value of a key depends on the following:<\/p>\n<ul>\n<li>When the primary key is an integer (<code>int<\/code>, <code>bigint<\/code>&#8230;), the key is the primary key.<\/li>\n<li>When TiDB creates a hidden column (<code>_tidb_rowid<\/code>) for the table, the key is the hidden column.<\/li>\n<\/ul>\n<h3>6.1 Use <code>SHARD_ROW_ID_BITS<\/code> to scatter the write hotspot<\/h3>\n<p>You can use <code>SHARD_ROW_ID_BITS<\/code> to set the number of bits of the shards in the hidden column.<\/p>\n<p>For the tables with non-integer primary keys or without primary keys, TiDB uses an implicit auto-increment ROW ID. When a large number of <code>INSERT<\/code> operations occur, the data is written into a single Region, causing a write hotspot.<\/p>\n<p>To mitigate the hotspot issue, you can configure <code>SHARD_ROW_ID_BITS<\/code>. The ROW ID is scattered, and the data is written into multiple different Regions. However, setting an overly large value might lead to an excessively large number of remote procedure call (RPC) requests, which increase the CPU and network overheads.<\/p>\n<ul>\n<li><code>SHARD_ROW_ID_BITS = 4<\/code> indicates 16 shards<\/li>\n<li><code>SHARD_ROW_ID_BITS = 6<\/code> indicates 64 shards<\/li>\n<li><code>SHARD_ROW_ID_BITS = 0<\/code> indicates the default, 1 shard<\/li>\n<li><code>CREATE TABLE<\/code> sample statement: <code>CREATE TABLE t (c int) SHARD_ROW_ID_BITS = 4<\/code><\/li>\n<li><code>ALTER TABLE<\/code> sample statement: <code>ALTER TABLE t SHARD_ROW_ID_BITS = 4<\/code><\/li>\n<\/ul>\n<h3>6.2 Partitioned table<\/h3>\n<p>You can use a partitioned table to scatter the data from one table into multiple physical tables. With properly-designed partition rules, you can use the partitioned table to further avoid write hotspot issues.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"7_Some_notes_on_SQL_syntax\"><\/span>7. Some notes on SQL syntax<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>This section describes some SQL syntax best practices in TiDB.<\/p>\n<h3>7.1 <code>create table as select<\/code> is not supported<\/h3>\n<p>The current version of TiDB does not support the <code>create table as select ...<\/code> statement. To achieve the same result, you must modify two statements in combination: <code>create table like ...<\/code>\uadf8\ub9ac\uace0 <code>insert into select ...<\/code>. <code>create table like...<\/code> supports replicating the schema of the corresponding table.<\/p>\n<h3>7.2 Use full <code>GROUP BY<\/code>to guarantee stable result sets<\/h3>\n<p>For convenience, if you disable <code>ONLY_FULL_GROUP_BY<\/code>, MySQL lets the <code>SELECT<\/code> substatement reference the nonaggregated fields that are not stated in the <code>GROUP BY<\/code> substatements. This makes a non-full <code>GROUP BY<\/code> syntax. In other databases, this is deemed a syntax error that may cause unstable result sets.<\/p>\n<p>In the following three SQL statements, the first one uses the full <code>GROUP BY<\/code> syntax, with all the fields referenced in the <code>SELECT<\/code> substatement stated in the <code>GROUP BY<\/code> substaments. It has the most stable result sets with three combinations of <code>class<\/code> \uadf8\ub9ac\uace0 <code>stuname<\/code> fields.<\/p>\n<p>The second and third SQL statements are identical, but they yield different results. The statement only states a <code>class<\/code> field in the <code>GROUP BY<\/code> statement, so the result sets are only aggregated for <code>class<\/code>. Since there are two unique values for <code>class<\/code>, the result sets only contains two rows of data, while there are three combinations of <code>class<\/code> \uadf8\ub9ac\uace0 <code>stuname<\/code> fields. Class 2018_CS_03 has two students, and there is no semantic restriction as to which one is returned for each execution. Either one is semantically expected.<\/p>\n<pre><code>mysql&gt; select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class, a.stuname order by a.class, a.stuname;\n+------------+--------------+------------------+\n| class      | stuname      | max(b.courscore) |\n+------------+--------------+------------------+\n| 2018_CS_01 | MonkeyDLuffy |             95.5 |\n| 2018_CS_03 | PatrickStar  |             99.0 |\n| 2018_CS_03 | SpongeBob    |             95.0 |\n+------------+--------------+------------------+\n3 rows in set (0.00 sec)\n\nmysql&gt; select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname;\n+------------+--------------+------------------+\n| class      | stuname      | max(b.courscore) |\n+------------+--------------+------------------+\n| 2018_CS_01 | MonkeyDLuffy |             95.5 |\n| 2018_CS_03 | SpongeBob    |             99.0 |\n+------------+--------------+------------------+\n2 rows in set (0.01 sec)\n\nmysql&gt; select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname;\n+------------+--------------+------------------+\n| class      | stuname      | max(b.courscore) |\n+------------+--------------+------------------+\n| 2018_CS_01 | MonkeyDLuffy |             95.5 |\n| 2018_CS_03 | PatrickStar  |             99.0 |\n+------------+--------------+------------------+\n2 rows in set (0.01 sec)\n<\/code><\/pre>\n<p>Therefore, to guarantee stable result sets, use the full <code>GROUP BY<\/code> syntax.<\/p>\n<p>MySQL provides a <code>ONLY_FULL_GROUP_BY<\/code> SQL mode to control whether to check the full <code>GROUP BY<\/code> syntax, which is also supported in TiDB.<\/p>\n<pre><code>mysql&gt; select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname;\n+------------+--------------+------------------+\n| class      | stuname      | max(b.courscore) |\n+------------+--------------+------------------+\n| 2018_CS_01 | MonkeyDLuffy |             95.5 |\n| 2018_CS_03 | PatrickStar  |             99.0 |\n+------------+--------------+------------------+\n2 rows in set (0.01 sec)\n\nmysql&gt; set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';\nQuery OK, 0 rows affected (0.01 sec)\n\nmysql&gt; select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname;\nERROR 1055 (42000): Expression #2 of ORDER BY is not in GROUP BY clause and contains nonaggregated column '' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\n<\/code><\/pre>\n<h3>7.3 Use <code>ORDER BY<\/code> to guarantee the output sequence of result sets<\/h3>\n<p>According to SQL semantics, you must use the <code>ORDER BY<\/code> syntax to output result sets in a specified sequence. For standalone databases, because the data are all stored on one server, results are stably returned when data is not reorganized. Some databases can still output result sets in the order of primary keys or indexes. TiDB is a distributed database where data is stored in multiple servers, and the TiDB server layer does not cache data. Therefore, the display order of the result sets for SQL statements without <code>ORDER BY<\/code> may be unpredictable. To output the result sets in sequence, explicitly add the fields for sorting in the <code>ORDER BY<\/code> clause, as defined by SQL semantics.<\/p>\n<p>In the following example, the user only adds one field in the <code>ORDER BY<\/code> clause. Therefore, TiDB sorts the results by this field only.<\/p>\n<pre><code>mysql&gt; select a.class, a.stuname, b.course, b.courscore from stu_info a join stu_score b on a.stuno=b.stuno order by a.class;\n+------------+--------------+-------------------------+-----------+\n| class      | stuname      | course                  | courscore |\n+------------+--------------+-------------------------+-----------+\n| 2018_CS_01 | MonkeyDLuffy | PrinciplesofDatabase    |      60.5 |\n| 2018_CS_01 | MonkeyDLuffy | English                 |      43.0 |\n| 2018_CS_01 | MonkeyDLuffy | OpSwimming              |      67.0 |\n| 2018_CS_01 | MonkeyDLuffy | OpFencing               |      76.0 |\n| 2018_CS_01 | MonkeyDLuffy | FundamentalsofCompiling |      88.0 |\n| 2018_CS_01 | MonkeyDLuffy | OperatingSystem         |      90.5 |\n| 2018_CS_01 | MonkeyDLuffy | PrincipleofStatistics   |      69.0 |\n| 2018_CS_01 | MonkeyDLuffy | ProbabilityTheory       |      76.0 |\n| 2018_CS_01 | MonkeyDLuffy | Physics                 |      63.5 |\n| 2018_CS_01 | MonkeyDLuffy | AdvancedMathematics     |      95.5 |\n| 2018_CS_01 | MonkeyDLuffy | LinearAlgebra           |      92.5 |\n| 2018_CS_01 | MonkeyDLuffy | DiscreteMathematics     |      89.0 |\n| 2018_CS_03 | SpongeBob    | PrinciplesofDatabase    |      88.0 |\n| 2018_CS_03 | SpongeBob    | English                 |      79.0 |\n| 2018_CS_03 | SpongeBob    | OpBasketball            |      92.0 |\n| 2018_CS_03 | SpongeBob    | OpTennis                |      94.0 |\n| 2018_CS_03 | PatrickStar  | LinearAlgebra           |       6.5 |\n| 2018_CS_03 | PatrickStar  | AdvancedMathematics     |       5.0 |\n| 2018_CS_03 | SpongeBob    | DiscreteMathematics     |      72.0 |\n| 2018_CS_03 | PatrickStar  | ProbabilityTheory       |      12.0 |\n| 2018_CS_03 | PatrickStar  | PrincipleofStatistics   |      20.0 |\n| 2018_CS_03 | PatrickStar  | OperatingSystem         |      36.0 |\n| 2018_CS_03 | PatrickStar  | FundamentalsofCompiling |       2.0 |\n| 2018_CS_03 | PatrickStar  | DiscreteMathematics     |      14.0 |\n| 2018_CS_03 | PatrickStar  | PrinciplesofDatabase    |       9.0 |\n| 2018_CS_03 | PatrickStar  | English                 |      60.0 |\n| 2018_CS_03 | PatrickStar  | OpTableTennis           |      12.0 |\n| 2018_CS_03 | PatrickStar  | OpPiano                 |      99.0 |\n| 2018_CS_03 | SpongeBob    | FundamentalsofCompiling |      43.0 |\n| 2018_CS_03 | SpongeBob    | OperatingSystem         |      95.0 |\n| 2018_CS_03 | SpongeBob    | PrincipleofStatistics   |      90.0 |\n| 2018_CS_03 | SpongeBob    | ProbabilityTheory       |      87.0 |\n| 2018_CS_03 | SpongeBob    | Physics                 |      65.0 |\n| 2018_CS_03 | SpongeBob    | AdvancedMathematics     |      55.0 |\n| 2018_CS_03 | SpongeBob    | LinearAlgebra           |      60.5 |\n| 2018_CS_03 | PatrickStar  | Physics                 |       6.0 |\n+------------+--------------+-------------------------+-----------+\n36 rows in set (0.01 sec)\n<\/code><\/pre>","protected":false},"excerpt":{"rendered":"<p>This article shows how to efficiently develop high-quality applications with TiDB, an open-source NewSQL database that supports Hybrid Transactional and Analytical Processing (HTAP) workloads and can serve as a scale-out MySQL database without manual sharding.<\/p>","protected":false},"author":19,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ub_ctt_via":"","footnotes":""},"categories":[13],"tags":[39,29],"class_list":["post-203","post","type-post","status-publish","format-standard","hentry","category-product","tag-best-practice","tag-tutorial"],"acf":[],"featured_image_src":null,"author_info":{"display_name":"Tianshuang Qin","author_link":"https:\/\/www.pingcap.com\/ko\/blog\/author\/tianshuang-qin\/"},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Best Practices for Developing Applications with TiDB | TiDB<\/title>\n<meta name=\"description\" content=\"Learn about the benefits of TiDB and the TiDB Cloud solutions from PingCAP. Read our latest post &quot;Best Practices for Developing Applications with TiDB&quot; here.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pingcap.com\/ko\/blog\/best-practices-for-developing-applications-with-tidb\/\" \/>\n<meta property=\"og:locale\" content=\"ko_KR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Best Practices for Developing Applications with TiDB | TiDB\" \/>\n<meta property=\"og:description\" content=\"Learn about the benefits of TiDB and the TiDB Cloud solutions from PingCAP. Read our latest post &quot;Best Practices for Developing Applications with TiDB&quot; here.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pingcap.com\/ko\/blog\/best-practices-for-developing-applications-with-tidb\/\" \/>\n<meta property=\"og:site_name\" content=\"TiDB\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/facebook.com\/pingcap2015\" \/>\n<meta property=\"article:published_time\" content=\"2019-06-19T00:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-09-13T02:14:03+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/static.pingcap.com\/files\/2024\/09\/11005522\/Homepage-Ad.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1440\" \/>\n\t<meta property=\"og:image:height\" content=\"714\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Tianshuang Qin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:site\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tianshuang Qin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"24\ubd84\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/\"},\"author\":{\"name\":\"Tianshuang Qin\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/person\/90bdc23a4c335ed65b973876b9145b06\"},\"headline\":\"Best Practices for Developing Applications with TiDB\",\"datePublished\":\"2019-06-19T00:00:00+00:00\",\"dateModified\":\"2023-09-13T02:14:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/\"},\"wordCount\":3631,\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"keywords\":[\"Best Practice\",\"Tutorial\"],\"articleSection\":[\"Product\"],\"inLanguage\":\"ko-KR\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/\",\"url\":\"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/\",\"name\":\"Best Practices for Developing Applications with TiDB | TiDB\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/#website\"},\"datePublished\":\"2019-06-19T00:00:00+00:00\",\"dateModified\":\"2023-09-13T02:14:03+00:00\",\"description\":\"Learn about the benefits of TiDB and the TiDB Cloud solutions from PingCAP. Read our latest post \\\"Best Practices for Developing Applications with TiDB\\\" here.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/#breadcrumb\"},\"inLanguage\":\"ko-KR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.pingcap.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Best Practices for Developing Applications with TiDB\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.pingcap.com\/#website\",\"url\":\"https:\/\/www.pingcap.com\/\",\"name\":\"TiDB\",\"description\":\"TiDB | SQL at Scale\",\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.pingcap.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"ko-KR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.pingcap.com\/#organization\",\"name\":\"PingCAP\",\"url\":\"https:\/\/www.pingcap.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"width\":811,\"height\":232,\"caption\":\"PingCAP\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/facebook.com\/pingcap2015\",\"https:\/\/x.com\/PingCAP\",\"https:\/\/linkedin.com\/company\/pingcap\",\"https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/person\/90bdc23a4c335ed65b973876b9145b06\",\"name\":\"Tianshuang Qin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2022\/10\/17234942\/avatar.jpg\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2022\/10\/17234942\/avatar.jpg\",\"caption\":\"Tianshuang Qin\"},\"url\":\"https:\/\/www.pingcap.com\/ko\/blog\/author\/tianshuang-qin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Best Practices for Developing Applications with TiDB | TiDB","description":"Learn about the benefits of TiDB and the TiDB Cloud solutions from PingCAP. Read our latest post \"Best Practices for Developing Applications with TiDB\" here.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pingcap.com\/ko\/blog\/best-practices-for-developing-applications-with-tidb\/","og_locale":"ko_KR","og_type":"article","og_title":"Best Practices for Developing Applications with TiDB | TiDB","og_description":"Learn about the benefits of TiDB and the TiDB Cloud solutions from PingCAP. Read our latest post \"Best Practices for Developing Applications with TiDB\" here.","og_url":"https:\/\/www.pingcap.com\/ko\/blog\/best-practices-for-developing-applications-with-tidb\/","og_site_name":"TiDB","article_publisher":"https:\/\/facebook.com\/pingcap2015","article_published_time":"2019-06-19T00:00:00+00:00","article_modified_time":"2023-09-13T02:14:03+00:00","og_image":[{"width":1440,"height":714,"url":"https:\/\/static.pingcap.com\/files\/2024\/09\/11005522\/Homepage-Ad.png","type":"image\/png"}],"author":"Tianshuang Qin","twitter_card":"summary_large_image","twitter_creator":"@PingCAP","twitter_site":"@PingCAP","twitter_misc":{"Written by":"Tianshuang Qin","Est. reading time":"24\ubd84"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/#article","isPartOf":{"@id":"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/"},"author":{"name":"Tianshuang Qin","@id":"https:\/\/www.pingcap.com\/#\/schema\/person\/90bdc23a4c335ed65b973876b9145b06"},"headline":"Best Practices for Developing Applications with TiDB","datePublished":"2019-06-19T00:00:00+00:00","dateModified":"2023-09-13T02:14:03+00:00","mainEntityOfPage":{"@id":"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/"},"wordCount":3631,"publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"keywords":["Best Practice","Tutorial"],"articleSection":["Product"],"inLanguage":"ko-KR"},{"@type":"WebPage","@id":"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/","url":"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/","name":"Best Practices for Developing Applications with TiDB | TiDB","isPartOf":{"@id":"https:\/\/www.pingcap.com\/#website"},"datePublished":"2019-06-19T00:00:00+00:00","dateModified":"2023-09-13T02:14:03+00:00","description":"Learn about the benefits of TiDB and the TiDB Cloud solutions from PingCAP. Read our latest post \"Best Practices for Developing Applications with TiDB\" here.","breadcrumb":{"@id":"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/#breadcrumb"},"inLanguage":"ko-KR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.pingcap.com\/blog\/best-practices-for-developing-applications-with-tidb\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pingcap.com\/"},{"@type":"ListItem","position":2,"name":"Best Practices for Developing Applications with TiDB"}]},{"@type":"WebSite","@id":"https:\/\/www.pingcap.com\/#website","url":"https:\/\/www.pingcap.com\/","name":"\ud2f0DB","description":"TiDB | SQL at Scale","publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pingcap.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"ko-KR"},{"@type":"Organization","@id":"https:\/\/www.pingcap.com\/#organization","name":"PingCAP","url":"https:\/\/www.pingcap.com\/","logo":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/","url":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","width":811,"height":232,"caption":"PingCAP"},"image":{"@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/facebook.com\/pingcap2015","https:\/\/x.com\/PingCAP","https:\/\/linkedin.com\/company\/pingcap","https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA"]},{"@type":"Person","@id":"https:\/\/www.pingcap.com\/#\/schema\/person\/90bdc23a4c335ed65b973876b9145b06","name":"Tianshuang Qin","image":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/person\/image\/","url":"https:\/\/static.pingcap.com\/files\/2022\/10\/17234942\/avatar.jpg","contentUrl":"https:\/\/static.pingcap.com\/files\/2022\/10\/17234942\/avatar.jpg","caption":"Tianshuang Qin"},"url":"https:\/\/www.pingcap.com\/ko\/blog\/author\/tianshuang-qin\/"}]}},"grav_blocks":false,"card_markup":"<a class=\"card-resource bg-white\" href=\"https:\/\/www.pingcap.com\/ko\/blog\/best-practices-for-developing-applications-with-tidb\/\"><div class=\"card-resource__content-container\"><div class=\"card-resource__content-head\"><div class=\"card-resource__category\">Product<\/div><\/div><h5 class=\"card-resource__title\">Best Practices for Developing Applications with TiDB<\/h5><\/div><\/a>","_links":{"self":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts\/203","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/users\/19"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/comments?post=203"}],"version-history":[{"count":4,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts\/203\/revisions"}],"predecessor-version":[{"id":13938,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts\/203\/revisions\/13938"}],"wp:attachment":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/media?parent=203"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/categories?post=203"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/tags?post=203"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}