Shulou(Shulou.com)05/31 Report--

How to update the optimization strategy in MySql, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Simulation scenario 1:

To adjust the structure of a table in the database, add several fields, and then refresh the previous data, the refresh content is to match one of the existing fields url, and then update the newly added fields type and typeid.

Later, I wrote a shell script to brush the data, but I was confused after running the shell script. Why is it so slow?

Scene reproduction

Copy the code as follows: CREATE TABLE `fuckSpeed` (`uin` bigint (20) unsigned NOT NULL DEFAULT 0, `id` int (11) unsigned NOT NULL DEFAULT 0, `url` varchar (255) NOT NULL DEFAULT'', `type` int (11) unsigned NOT NULL DEFAULT 0, `typeid` varchar (64) NOT NULL DEFAULT',. KEY `uin_ id` (`uin`, `id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

The table structure looks something like this (many fields are omitted). There is only one federated index uin_id in the table, and I have the following ideas when updating:

First, a certain amount of data is obtained according to an id range.

The copy code is as follows: select id,url from funkSpeed where id > = 101 and id=101 and id select id,url from funkSpeed where id > = 0 and id explain id,url from funkSpeed where id > = 0 and id select uin,id from funkSpeed where uin=10023 and id=162 +-+-+ | uin | id | +-+-+ | 10023 | 10023 | +-+-+ 1 row in set (0.00 sec) mysql > explain select uin,id from funkSpeed where uin=10023 and id=162 +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -+-+ | funkSpeed | ref | uin_id | uin_id | 12 | const Const | 4 | Using index | +-+-+ 1 row in set (0.00 sec)

You can see that it is almost a second check, and at this time, we can basically conclude that the problem occurs in the index.

I select less often, with a 10000 id difference between every two select, so it can be ignored here, and there is no way to optimize it unless an index is added to the id.

The problem occurs in

The copy code is as follows: update fuckSpeed set type= [type], typeid= [typeid] where id= [id]

Query will also be used here when updating. My mysql version is 5.5, so I can't explain update, otherwise I can definitely verify what I said. Here, 32w + pieces of data will be updated, each piece of data will be updated, each piece of data will be updated for about 0.2s, which is too scary.

If the problem is solved, it will be much easier to solve.

When select, a field uin is added, which is changed to the following

The copy code is as follows: select uin,id,url from funkSpeed where id > = 101 and id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.