"20180329" MySQL optimized sql A and thinking

Wire network environment

1. MySQL 5.6.21-log MySQL Community Server
2. Innodb_buffer_pool_size 1G
3. Turn off QC
4. Table exists partition

Table structure and SQL to be optimized

  mysql> Show CREATE table articles \g*************************** 1. Row *************************** Tab Le:articlescreate table:create Table ' articles ' (' id ' int (one) not null, ' user_id ' int (one) ' NOT null, ' Pic_urls ' Varch AR (+) not null, ' vote_id ' int (one) not null default ' 0 ', ' topic_id ' int (one) not null default ' 0 ', ' type ' int (one) not N ULL, ' content ' varchar (+) default NULL, ' location ' varchar (+) NOT null default ' ', ' longitude ' varchar (a) NOT NULL , ' latitude ' varchar (a) is not null, ' status ' int (one) not null, ' Created_at ' datetime is NOT NULL, ' user_ip ' int (one) unsign Ed NOT null default ' 0 ', ' Review ' tinyint (4) is not null default ' 0 ', PRIMARY key (' ID '), key ' topic_id ' (' topic_id '), KE Y ' user_id ' (' user_id ', ' status ', ' Created_at '), Engine=innodb DEFAULT charset=utf8mb4/*!50100 PARTITION by HASH (ID) partitions */ 

SELECT COUNT (1) as C from articles WHERE topic_id = A and status = 1 and ID not in (17080670,17079098,17109846,17110577, 17126923,17117892) and user_id not in ( 33824249,33828294,32833143,33834489,33841022,33841121,33841156,33844937,33847129,33827047,33848560,32720750,33833941,3384 8573,33810491,33838021,33847010,33851748,33853093,33853111,33853156,33841943,33853408,33857041,33854233,33846742,33846748 , 33854255,33857064,33885581,33864476,33934278,34280131,34185367,34280088,34346710,34385075,34385237,34077681,34280171,343 46732,34346738,34332490,34335274,34181694,34386339,34250847,34346757,34346752,34346813,34346945,7490639,34346927,34010356 , 34347049,33940626,34341461,34347061,34347081,34341381,34347085,34347126,34332537,34347117,34394447,34327303,31607732,341 73783,34353368,34353330,34150466,34070811,34353413,34353371,34392543,34110905,34332506,34353421,34068582,34368645,3371151 0,34353619,33888980,34353584,34353617,34366773,34068768,34367479,34370650,34353698,34353721,34360131,34360154,34237758,34366508,34360254,34360246,34376763,34360248,34360276,33965629,34360269,34392397,34327406,34360307,34360402,343604 31,27855780,34360467,34029547,34360448,34392561,34360453,34397244,34453559,34453531,34360480,34224620,34397270,34340166,3 4453534,34462520,1060754,34070596,34335225,33464686,34360505,34360611,34413592,20244349,34367391,34367528,34367558,344969 04,34494346,32992426,34367521,34367506,34397284,34075167,34397236,34367508,34502838,34367511,34367671,34367672,34256721,3 4510895,34511335,34220701,34511556,34370188,34520818,34521120,34521121,34521099,34452468,34530058,34534558,34533294,34541 331,34060856,34543797,34082416,34500366,34545190,34515713,34417773,34367802,34553311,34367862,34367832,34127282,34083939 , 33691298,34373694,34559793,34373689,34545953,34373783,34500833,34573568,34525236,34576425,34373688,34585516,34415308,131 64849,34574639,34568298,34558301,34583203,34580301,12135076,5485129,34574974,34552389,34567808,34589428,34586104,34596708 , 34587929,34589613,34592242,32710709,33996135,34589622,34590003,34579522,34603444,33471864,34104130,34494174,34026755,34335083,34637732,34608155,31270655 , 34313290,34373760,34373757,32717182,34396802,32568247,31832477,34210659,34653651,34653681,34102064,34414116,34422038,345 74228,34422010,34584690,34156339,34422218,34413816,34413818,34554514,30371762,34405772,34397948,34398306,33934125,3440464 8,34085531,34670672,32578081,34633739,34768345,34454576,34093578,34671375,34790367,34369280,34799915,34799934,34791716,34 791710,34791767,34169856,34911914,34887431,34887393,34853976,34915775,34993982,34961184,34993983,35005710,34913662,325554 45,35093709,35097894,35146899,35147077,35170956,31163038,33971212,35160092,35183839,35256282,35196189,34399233,35092356,3 4405546,35168649,31809004,35270433,35168810,35666711,35300151,35710861,18054773,35905428,35534509,35301706,35741153,35367 969,30990616,3204057,35970676,33681952,35970746,35369209,35369417,35985122,36020270,35900701,9316493,35553853,35633504,35 633617,36232496,35191850,35880221, 36415016,36296697,36529238,35786342,36659949,36405256,36422717,36661338,36661297,36614751,36440561,35861881,14752030,327 36870,36458571,36689504,36461801,36795506,36796410,37054128,37054123,37054054,37054399,37074768,37090630,37090391,3668080 9,37231542,37110709,36681004,37158764,37158828,37158887,37167540,37167539,37175339,37175275,37175465,37279948,32873902,37 286818,37317499,37345029,37344970,4222498,36483504);

Optimization ideas

1. First: Add a federated index on topic_id and status.
   • Create a federated index in the topic_id status
     
   • View the execution plan for SQL
     
   • SQL first time Request time consuming
     
   • SQL Second request time consuming
     
2. Second time: Add a federated index above topic_id,status and ID.
   • Create a federated index in the TOPIC_ID status ID
     
   • View the execution plan for SQL
     
   • The first time the SQL request is time consuming
     
   • Second SQL request time consuming
     
3. Create a federated index on Topic_id,status,id and user_id
   • Create a federated index TOPIC_ID status ID user_id
     
   • View SQL Execution Plan
     
   • The first time the SQL request is time consuming
     
   • Second SQL request time consuming
     
4. Fourth time: Create a federated index on Topic_id,status and user_id
   • Create a federated index TOPIC_ID status user_id
     
   • View SQL Execution Plan
     
   • The first time the SQL request is time consuming
     
   • Second SQL request time consuming
     

Thinking

1. For the first and second optimizations and the third, why is the fourth time difference so big?
   • The main reason is that the optimization is not significant because the key value data for the first and second optimization user_id needs a back table. The third and fourth times user_id do not need to return the table, so the optimization is obvious.
2. Why is the first SQL and second execution SQL for the fourth time time consuming so slowly?
   • Because of the innodb_buffer_pool_size cache?

"20180329" MySQL optimized sql A and thinking