Analysis of the influence of string index on update in Mysql _mysql

This paper analyzes the effect of string index on update in MySQL. Share to everyone for your reference, specific as follows:

When you add a prefix index to a field that has a type of varchar, the conditional query time is substantially reduced based on the child segment, but the time spent on the update operation has increased dramatically, mainly because MySQL performs an update of the index while updating the data.

Here's a simple experiment.

(1) Perform an update on all records in a table field of an billion-level record first:

For IDX in range (1, count+1):
  sql = "Update tbl_name set platforms= '" +datetime.now (). Strftime ('%y%m%d%h%m%s%f ') + "' where id= "+str (IDX) +";
  Cursor.execute (SQL)

Statistics are available about 4 hours.

(2) Then add an index to the table segment of an billion-level record:

sql = "ALTER TABLE Tbl_name Add index Platforms_index (platforms (8));"
Cursor.execute (SQL)

It then executes the previous UPDATE statement, which is approximately 9 hours in the count.

(3) This shows that the string index has a great effect on the performance of such operations as update.

Although the small experiment is not of much practical significance, but you can extend it, and instead of adding an index to the field, store the field data directly into another small table B, and then add an ID field to the table to map to small table B, and then perform the update operation;

Theoretically, the performance should be related to the repetition of the record, if the repetition rate is high, small table B will be very small (unique to heavy), and the time spent updating the query Table B field through the original table mapping time + Update table B records time, overall should be more than the time to update the record + update the index less time.

But only estimates, the results of how interested friends can be hands-on verification.

For more information about MySQL interested readers can view the site topics: "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL common function large summary"

I hope this article will help you with the MySQL database meter.