# Hotspot of Nonclustered Index

![`smart`](HotspotofNonclusteredIndex_aa_004_20230906.png)


```
CREATE TABLE sbtest1(
 ~!@id int(11) NOT NULL AUTO_INCREMENT,@!~
 k int(11) NOT NULL DEFAULT '0',
 c char(120) NOT NULL DEFAULT '',
 pad CHAR(60) DEFAULT '' NOT NULL,
 ~!@PRIMARY KEY (id) NONCLUSTERED@!~
);

CREATE TABLE sbtest1(
 ~!@id int(11) NOT NULL,@!~
 k int(11) NOT NULL DEFAULT '0',
 c char(120) NOT NULL DEFAULT '',
 pad CHAR(60) DEFAULT '' NOT NULL,
 ~!@PRIMARY KEY (id) NONCLUSTERED@!~
);

CREATE TABLE sbtest1(
 id int(11) NOT NULL,
 k int(11) NOT NULL DEFAULT '0',
 c char(120) NOT NULL DEFAULT '',
 pad CHAR(60) DEFAULT '' NOT NULL,
 ~!@NONCLUSTERED@!~
);
```

----------------------------------------------------
As shown in the slide, in Step 1, we execute the SQL statement 'insert into T values ('Tony', 1391113134)'. Since the 'ID' column has the AUTO_INCREMENT attribute, the TiDB database will automatically assign it a value that is 1 higher than the last inserted 'ID' value. In the slide, the assigned 'ID' value is 4. 
In Step 2, we need to obtain the Region where '4, Tony, 1391113134' should be inserted from the PD (Placement Driver). However, this 'ID' value of 4 is different from the previous ones in the Clustered Index Table. It is not stored in the 'Key' of the key-value pair, but in the 'Value' like other columns. Therefore, it does not affect data distribution.
Step 3 is the actual data insertion process. In this process, we find that the key that affects data distribution is the 'system-generated RowID', which must be incremented each time. Since the key-value pairs in the Region are sorted by key, each write operation will go to the latest generated Region. Consequently, every data insertion follows the previous Region, and with concurrent writes, the TiKV node where this Region is located becomes a hotspot.
In conclusion, in Nonclustered Index Tables, regardless of whether we specify a primary key and whether we specify the AUTO_INCREMENT attribute for the primary key, there is always a possibility of creating hotspots when inserting data.