TiDB实战篇-基于索引的SQL优化

简介

TiDB实战篇-基于索引的SQL优化。

Online DDL

允许直接在线执行ddl操作，不会锁表，但是执行多个ddl的时候会要排队。

原理

 

控制创建索引的速度

 

查看影响

标红的是加了多少行索引。 

添加索引的线上的影响（读写频繁）

 

 

只读场景

 

不涉及读写

 

索引扫描的方式

 

直接从索性计算

 

索引选择的规则

 

实操

优化创建索引的是时间

mysql -h192.168.66.10 -P4000 -uroot -ptidb
#创建数据库
create database test2;
create table t1(a int,b int);
#导入数据
for i in `seq 10000`; do mysql -uroot -P4000 -ptidb -h192.168.66.10 -e "insert into test2.t1 values($i,floor(rand()*10000000))";done;
for i in `seq 33`; do mysql -uroot -P4000 -ptidb -h192.168.66.10 -e "insert into test2.t1 select * from test2.t1 limit 100000";done;
#调节创建索引速度的参数
set global tidb_ddl_reorg_worker_cnt=1;
set global tidb_ddl_reorg_batch_size=32;

#创建索引
mysql -h192.168.66.10 -P4000 -uroot -ptidb
use test2;
create index index_t1_a on t1(a);
#查看索引创建的速度
admin show ddl jobs where table_name = 't1';

mysql>admin show ddl jobs where table_name = 't1';
+--------+---------+------------+--------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------+
| JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE     | SCHEMA_STATE         | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME         | START_TIME          | END_TIME            | STATE   |
+--------+---------+------------+--------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------+
|     86 | test2   | t1         | add index    | write reorganization |        82 |       84 |    479488 | 2023-04-30 14:44:51 | 2023-04-30 14:44:51 | NULL                | running |
|     85 | test2   | t1         | create table | public               |        82 |       84 |         0 | 2023-04-30 14:39:45 | 2023-04-30 14:39:45 | 2023-04-30 14:39:45 | synced  |
|     80 | test2   | t1         | create table | public               |        77 |       79 |         0 | 2023-04-30 12:59:23 | 2023-04-30 12:59:23 | 2023-04-30 12:59:23 | synced  |
+--------+---------+------------+--------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------+
3 rows in set (0.01 sec)

Query OK, 0 rows affected (3 min 20.92 sec)

#调节创建索引速度的参数
set global tidb_ddl_reorg_worker_cnt=32;
set global tidb_ddl_reorg_batch_size=512;
#删除掉之前的索引
alter table t1 drop index index_t1_a;
#重新创建索引
create index index_t1_a on t1(a);

用时Query OK, 0 rows affected (2 min 44.09 sec)

Point_Get 

#point get触发
mysql -h192.168.66.10 -P4000 -uroot -ptidb
create database test3;
use test3;
create table t1(a int,b int);
#导入实验数据
for i in `seq 10000`; do mysql -uroot -P4000 -ptidb -h192.168.66.10 -e "insert into test3.t1 values($i,floor(rand()*10000000))";done;
for i in `seq 33`; do mysql -uroot -P4000 -ptidb -h192.168.66.10 -e "insert into test3.t1 select * from test2.t1 limit 100000";done;
#查看导入数据和执行计划
select count(*) from t1;
explain select * from t1 where a=888;

mysql> explain select * from t1 where a=888;
+-------------------------+---------+-----------+---------------+--------------------------------+
| id                      | estRows | task      | access object | operator info                  |
+-------------------------+---------+-----------+---------------+--------------------------------+
| TableReader_7           | 3.91    | root      |               | data:Selection_6               |
| └─Selection_6           | 3.91    | cop[tikv] |               | eq(test3.t1.a, 888)            |
|   └─TableFullScan_5     | 3906.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo |
+-------------------------+---------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

#添加索引(非唯一索引)
create index idx on t1(a);
explain select * from t1 where a=888;

mysql> explain select * from t1 where a=888;
+-------------------------------+---------+-----------+------------------------+-----------------------------------+
| id                            | estRows | task      | access object          | operator info                     |
+-------------------------------+---------+-----------+------------------------+-----------------------------------+
| IndexLookUp_10                | 1.00    | root      |                        |                                   |
| ├─IndexRangeScan_8(Build)     | 1.00    | cop[tikv] | table:t1, index:idx(a) | range:[888,888], keep order:false |
| └─TableRowIDScan_9(Probe)     | 1.00    | cop[tikv] | table:t1               | keep order:false                  |
+-------------------------------+---------+-----------+------------------------+-----------------------------------+
3 rows in set (0.00 sec)

#添加唯一索引
alter table t1 drop index idx;
create unique index idx on t1(a);
explain select * from t1 where a=888;

mysql> explain select * from t1 where a=888;
+-------------+---------+------+------------------------+---------------+
| id          | estRows | task | access object          | operator info |
+-------------+---------+------+------------------------+---------------+
| Point_Get_1 | 1.00    | root | table:t1, index:idx(a) |               |
+-------------+---------+------+------------------------+---------------+
1 row in set (0.00 sec)

#测试非等值查询
explain select * from t1 where a>888;

mysql> explain select * from t1 where a>888;
+-------------------------+----------+-----------+---------------+---------------------+
| id                      | estRows  | task      | access object | operator info       |
+-------------------------+----------+-----------+---------------+---------------------+
| TableReader_7           | 9112.00  | root      |               | data:Selection_6    |
| └─Selection_6           | 9112.00  | cop[tikv] |               | gt(test3.t1.a, 888) |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1      | keep order:false    |
+-------------------------+----------+-----------+---------------+---------------------+
3 rows in set (0.01 sec)

#测试空值是否会触发
insert into t1 values(null,10000);

explain select * from t1 where a is null;

mysql> explain select * from t1 where a is null;
+-------------------------------+---------+-----------+------------------------+-------------------------------------+
| id                            | estRows | task      | access object          | operator info                       |
+-------------------------------+---------+-----------+------------------------+-------------------------------------+
| IndexLookUp_10                | 1.00    | root      |                        |                                     |
| ├─IndexRangeScan_8(Build)     | 1.00    | cop[tikv] | table:t1, index:idx(a) | range:[NULL,NULL], keep order:false |
| └─TableRowIDScan_9(Probe)     | 1.00    | cop[tikv] | table:t1               | keep order:false                    |
+-------------------------------+---------+-----------+------------------------+-------------------------------------+
3 rows in set (0.00 sec)