Performance Tuning Best Practices

This document introduces some best practices for using TiDB databases.

DML best practices

This section describes the best practices involved when you use DML with TiDB.

Use multi-row statements

When you need to modify multiple rows of table, it is recommended to use multi-row statements:

  1. INSERT INTO t VALUES (1, 'a'), (2, 'b'), (3, 'c');
  3. DELETE FROM t WHERE id IN (1, 2, 3);

It is not recommended to use multiple single-row statements:

  1. INSERT INTO t VALUES (1, 'a');
  2. INSERT INTO t VALUES (2, 'b');
  3. INSERT INTO t VALUES (3, 'c');
  5. DELETE FROM t WHERE id = 1;
  6. DELETE FROM t WHERE id = 2;
  7. DELETE FROM t WHERE id = 3;

Use PREPARE

When you need to execute a SQL statement for multiple times, it is recommended to use the PREPARE statement to avoid the overhead of repeatedly parsing the SQL syntax.

  • Golang
  • Java
  1. func BatchInsert(db *sql.DB) error {
  2.     stmt, err := db.Prepare("INSERT INTO t (id) VALUES (?), (?), (?), (?), (?)")
  3.     if err != nil {
  4.         return err
  5.     }
  6.     for i := 0; i < 1000; i += 5 {
  7.         values := []interface{}{i, i + 1, i + 2, i + 3, i + 4}
  8.         _, err = stmt.Exec(values...)
  9.         if err != nil {
  10.             return err
  11.         }
  12.     }
  13.     return nil
  14. }
  1. public void batchInsert(Connection connection) throws SQLException {
  2.     PreparedStatement statement = connection.prepareStatement(
  3.             "INSERT INTO `t` (`id`) VALUES (?), (?), (?), (?), (?)");
  4.     for (int i = 0; i < 1000; i ++) {
  5.         statement.setInt(i % 5 + 1, i);
  7.         if (i % 5 == 4) {
  8.             statement.executeUpdate();
  9.         }
  10.     }
  11. }

Do not execute the PREPARE statement repeatedly. Otherwise, the execution efficiency cannot be improved.

Only query the columns you need

If you do not need data from all columns, do not use SELECT * to return all columns data. The following query is inefficient:

  1. SELECT * FROM books WHERE title = 'Marian Yost';

You should only query the columns you need. For example:

  1. SELECT title, price FROM books WHERE title = 'Marian Yost';

Use bulk delete

When you delete a large amount of data, it is recommended to use bulk delete.

Use bulk update

When you update a large amount of data, it is recommended to use bulk update.

Use TRUNCATE instead of DELETE for full table data

When you need to delete all data from a table, it is recommended to use the TRUNCATE statement:

  1. TRUNCATE TABLE t;

It is not recommended to use DELETE for full table data:

  1. DELETE FROM t;

DDL best practices

This section describes the best practices involved when using TiDB’s DDL.

Primary key best practices

See the rules to follow when selecting the primary key.

Index best practices

See Index Best Practices.

Add index best practices

TiDB supports the online index add operation. You can use ADD INDEX or CREATE INDEX statement to add an index. It does not block data reads and writes in the table. You can adjust the concurrency and the batch size during the re-organize phase of the index add operation by modifying the following system variables:

To reduce the impact on the online application, the default speed of add index operation is slow. When the target column of add index operation only involves read load or is not directly related to online workload, you can appropriately increase the value of the above variables to speed up the add index operation:

  1. SET @@global.tidb_ddl_reorg_worker_cnt = 16;
  2. SET @@global.tidb_ddl_reorg_batch_size = 4096;

When the target column of the add index operation is updated frequently (including UPDATE, INSERT and DELETE), increasing the above variables causes more write conflicts, which impacts the online workload. Accordingly, the add index operation might take a long time to complete due to constant retries. In this case, it is recommended to decrease the value of the above variables to avoid write conflicts with the online application:

  1. SET @@global.tidb_ddl_reorg_worker_cnt = 4;
  2. SET @@global.tidb_ddl_reorg_batch_size = 128;

Transaction conflicts

For how to locate and resolve transaction conflicts, see Troubleshoot Lock Conflicts.

For how to locate and resolve transaction conflicts, see Troubleshoot Lock Conflicts.

Best practices for developing Java applications with TiDB

See Best Practices for Developing Java Applications with TiDB.

See Best Practices for Developing Java Applications with TiDB.

See also