MySQL performance optimization (vi)

First, order by generation using Filesort detailed

1. First Build tables and indexes

/*Timetable*/Create TableCourse (IDint Primary KeyAuto_increment,/*primary key self-increment*/titlevarchar( -) not NULL,/*title*/category_idint  not NULL,/*which category does it belong to?*/school_idint  not NULL,/*which school belongs to?*/Buy_timesint  not NULL,/*Number of purchases*/Browse_timesint  not NULL/*Browse Times*/);Insert  intoCourse (title,category_id,school_id,buy_times,browse_times)Values('Java Course',1,1, -,8680);Insert  intoCourse (title,category_id,school_id,buy_times,browse_times)Values('Android Courses',2,1, -,8030);Insert  intoCourse (title,category_id,school_id,buy_times,browse_times)Values('MySQL Course',3,2, $,2902);Insert  intoCourse (title,category_id,school_id,buy_times,browse_times)Values('Oracle Courses',2,2, -,6710);Insert  intoCourse (title,category_id,school_id,buy_times,browse_times)Values('C # Course',1,3,620,2890);Insert  intoCourse (title,category_id,school_id,buy_times,browse_times)Values('PS Course',4,4, About,4300);Insert  intoCourse (title,category_id,school_id,buy_times,browse_times)Values(' CADCourses',5,1,403,6080);/*set up a composite index on category_id and Buy_times*/Create IndexIdx_cate_buy onCourse (category_id,buy_times);

What is the 2.order by that will produce using Filesort?

(1) Explain select ID from course where category_id>1 order by category_id;

Based on the leftmost prefix principle, the category_id of the order by will use the combined index

(2) Explain select ID from course where category_id>1 order by Category_id,buy_times;

Based on the leftmost prefix principle, the category_id buy_times behind order by will use the combined index, because the index is the two fields

(3) Explain select ID from course where category_id>1 order by Buy_times;

Based on the leftmost prefix principle, the field following order by is missing the leftmost category_id, so a using Filesort is generated

(4) Explain select ID from course where category_id>1 order by buy_times,category_id;

The order by field does not conform to the order in the combined index, so the index after the order by does not follow, which results in the using Filesort

(5) Explain select ID from course order by category_id;

Based on the leftmost prefix principle, the order by is preceded by the leftmost column in the index, so the index

(6) Explain select ID from course order by Buy_times;

According to the leftmost prefix principle, the field following the order by does not have the leftmost column in the index, so the index will not be indexed and will produce a using filesort

(7) Explain select ID from course where Buy_times > 1 order by Buy_times;

According to the leftmost prefix principle, the field following the order by does not have the leftmost column in the index, so the index will not be indexed and will produce a using fillesort

(8) Explain select ID from course where Buy_times > 1 order by category_id;

Based on the leftmost prefix principle, the field following order by is present in the leftmost column of the index, so the index is gone

(9) Explain select ID from course ORDER by Buy_times desc,category_id ASC;

Based on the most left-hand prefix principle, the order by field and the non-conformance in the index result in a using Filesort

(ten) explain select ID from course ORDER by category_id desc,buy_times ASC;

This is the same as if the fields in the order by are the same as the fields in the index, but one is descending and the other is ascending, so a using Filesort is generated, and the using Filesort is not produced in ascending and descending order.

Two, in and exists which performance is better SQL script:

/*Build Library*/Create DatabaseTESTDB6; UseTESTDB6;/*User Table*/Drop Table if existsusers;Create TableUsers (IDint Primary KeyAuto_increment,namevarchar( -));Insert  intoUsers (name)Values('A');Insert  intoUsers (name)Values('B');Insert  intoUsers (name)Values('C');Insert  intoUsers (name)Values('D');Insert  intoUsers (name)Values('E');Insert  intoUsers (name)Values('F');Insert  intoUsers (name)Values('G');Insert  intoUsers (name)Values('H');Insert  intoUsers (name)Values('I');Insert  intoUsers (name)Values('J');/*Order Form*/Drop Table if existsorders;Create TableOrders (IDint Primary KeyAuto_increment,/*Order ID*/Order_novarchar( -) not NULL,/*Order Number*/titlevarchar( -) not NULL,/*Order title*/Goods_numint  not NULL,/*Order Quantity*/ Money decimal(7,4) not NULL,/*Order Amount*/user_id int  not NULL    /*The User ID of the order*/) Engine=MyISAMdefaultCharSet=UTF8;d elimiter $$Drop procedurebatch_orders $$/* Stored Procedures*/Create procedureBatch_orders (inch Max int)beginDeclareStartint default 0;DeclareIint default 0;SetAutocommit= 0;  whileI< Max DoSetI=I+ 1;Insert  intoOrders (Order_no,title,goods_num, Money,user_id) Values(Concat ('ncs-', Floor(1 + Rand()*1000000000000)), concat ('Order title-', i), I% -,(100.0000+(I% -)), I%Ten); End  while;Commit;End$ $delimiter;/*Insert 10 million order data*/Call batch_orders (10000000); /* The process of inserting data varies depending on the performance of the machine, some may be 3 minutes, some may be 10 minutes */

There is user_id in the Order table (orders) in the SQL above, and we have the user table (users), so we use the IDs from the user_id and user tables in the Orders table to be in and exists.

Results analysis

1.where followed by a small table

(1) Select COUNT (1) from the orders O where o.user_id in (select U.id from users u);

　　

(2) Select COUNT (1) from the orders O where exists (select 1 from users u where u.id = o.user_id);

　　

Three, slow query

Four, locking mechanism

MySQL performance optimization (vi)