MYSQL09---SQL statement optimization

The optimization problem of the SQL statement itself is: How to quickly locate a slow-executing statement from a large project. (Locate slow query) First we understand how some of the running state of the MySQL database is queried (for example, to know the current MySQL run time/How many times did you execute select/Update/Delete:/Current connection) Show status common: Show statusLike‘Uptime‘ ;//Query enable time+---------------+-------+| Variable_name| Value|+---------------+-------+| Uptime|20492|+---------------+-------+mysql> Show StatusLike‘Com_select‘ ;//How many queries were executed+---------------+-------+| Variable_name| Value|+---------------+-------+| Com_select|0|+---------------+-------+show statusLike‘Com_insert‘How many times do you add, ... AnalogyUpdateDeleteShow[Session|global] StatusLike .... If you don't write[Session|global]The default is session sessions, which refer to the execution of the current window, if you want to see all (from MySQL boot to present, should global) show statusLike‘Connections‘;//Number of connections netstat-An;show statusLike‘Slow_queries‘;//Show slow query number ② How to locate a slow query build a large table (4 million)-Stored procedure build. By default, MySQL thinks 10 seconds is a slow query. Modify MySQL slow query: show variablesLike‘Long_query_time‘ ;//Can display the current slow query time+-----------------+----------+| Variable_name| Value|+-----------------+----------+| Long_query_time|0.010000|+-----------------+----------+Set Long_query_time=1;//Can modify slow query time build large table-Large table records are required, the record is different to use, otherwise the test effect and the real difference between the big. Create:CREATETABLE Dept (/*Department table*/Deptno Mediumint UNSIGNEDNotNullDEFAULT0,/*Number*/DnameVARCHAR (20)NotNullDEFAULT "",/*Name*/LocVARCHAR (13)NotNullDEFAULT ""/*Place*/) ENGINE=myisamDEFAULT CHARSET=UTF8;CREATETABLE EMP/*Employee table*/(Empno mediumint UNSIGNEDNotNullDEFAULT0,/*Number*/EnameVARCHAR (20)NotNullDEFAULT "",/*Name*/JobVARCHAR (9)NotNullDEFAULT "",/*Job*/Mgr Mediumint UNSIGNEDNotNullDEFAULT0,/*Superior number*/HireDate DATENotNull/*Time of entry*/SalDECIMAL (7,2) Not null,/* salary */comm DECIMAL (7,2) not null,/* dividend */deptno mediumint UNSIGNED not NULL DEFAULT 0/* Department number */) Engine=myisam D Efault Charset=utf8; CREATE Table Salgrade/* salary scale Table */(Grade Mediumint UNSIGNED not NULL DEFAULT 0,losal decimal (17,2) not null,hisal decimal (17, 2) not NULL) Engine=myisam DEFAULT Charset=utf8, test data insert into Salgrade values (1,700,1200), insert into Salgrade values (2 , 1201,1400); insert into salgrade values (3,1401,2000); insert into salgrade values (4,2001,3000); insert INTO Salgrade VALU ES (5,3001,9999); In order for the stored procedure to execute properly, we need to modify the command execution Terminator delimiter $$//Delete the custom function rand_string$$ the database can have various data objects (tables, stored procedures, views , functions, triggers), creating stored procedures and functions that can be directly invoked with PHP code. Creating a Stored procedure: CREATE PROCEDURE P4 (n int) Beginselect * from Orde where gid=n;end$ create functions rand_string:create function rand_string (n in T) #传一个整数returns varchar (255) #该函数会返回一个字符串begin #chars_str定义一个变量 chars_str, the type is varchar (100), the default value ' Abcdefghijklmnopqrstuvwxyzabcdefjhijklmnopqrstuvwxyz '; DECLARE chars_str varchar (+) Default ' ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFjhijklmnopqrstuvwxyz '; DECLARE return_str varchar (255) default '; declare i int default 0; While I < n do #从大写小写字母中随机取出n个, set Return_str =concat (return_str,substring (Chars_str,floor (1+rand () *52), 1)); Set i = i + 1; End while; return return_str; End $$ If you want to use it in your program, it is ok!mysql> select rand_string from dual$$+-----------------+| Rand_string (|+-----------------+| Lnfnalfrux |+-----------------+//dual is the sub-table, empty table, do not want to query from which table to use this meta-table

Be sure to specify the database, because functions, views, stored procedures are below the database.

#这里我们又自定了一个函数, returns a random numberCreatefunctionRand_num ()Returnsint5)BeginDECLARE IIntDefault0;Set I=Floor10+RAND ()*500);ReturnIEnd$$ Create a stored procedure: increase employee,CreateProcedure Insert_emp (In Startint10),In Max_numint10))BeginDECLARE IIntDefault0; #Set autocommit=0Set the auto-commit autocommit to 0 to not allow him to submit, and finally unified submit.Set autocommit=0; RepeatSet I= I+1;InsertInto EMPVALUES (start+i), rand_string (6),  ' salesman", 0001,curdate (), 2000,400,rand_num ()); Until I = max_num end repeat; commit; End $$ #调用刚刚写好的函数, 1.8 million records, beginning with number 100,001th, large table created. Call Insert_emp (100001, 4000000);          

Mysql>SelectCount*)Fromemp$$+----------+|Count*)|+----------+|4000000|+----------+① at this point, if a statement execution takes more than 1 seconds, it will be counted (SQL statements are not just query statements). ② If you log slow query SQL to one of our logs, first turn off MySQL (computer---Service--stop), start again,By default, our MySQL does not log slow query, need to start MySQL, specify the record slow query can, start in the following way: Bin\mysqld.exe--Safe-mode--slow-query-log #安全模式会写日志, the ability to recover data, [mysql5.5 can be specified in My.ini], the log file will be generated in data after the boot is completed bin\mysqld.exe–Log-slow-queries=d:\mysql\abc.Log[ If the slow query log is enabled, the file is placed in the My.ini file by default (if you just copy the database file, then the index is invalidated, to rebuild the index, the database migrates instead of copying the file directly.) (Preferably not modified here) #Path to the database  Rootdatadir= "C:/ Documents and settings/all Users /application data/mysql/ MySQL Server 5.5/data/" ③ test, you can see our MySQL slow SQL statement recorded in the log. Optimization problem. Through the explain statement can be analyzed, mysql how to execute your SQL statement, the use of this tool to put a bit, one will say. Add index "small suggestion:"        

mysql09---SQL statement optimization