Shulou(Shulou.com)06/01 Report--

1. SQL statement optimization (1) View the table structure MariaDB [oldboy] > desc test1 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | id | int ( 4) | NO | | NULL | | name | char (16) | NO | | NULL | | age | int (2) | YES | | NULL | | +-+-+ (2) explain to check whether it contains creation The statement MariaDB [oldboy] > explain select * from test1 where name= "kaka"\ Graph * 1. Row * * id: 1 select_type: SIMPLE table: test1 type: ALLpossible_keys: NULL key: NULL key_ Len: NULL ref: NULL rows: 5 # query rows Indicates that 5 rows of Extra are currently queried: Using where1 row in set (0.00 sec) (3) create the index MariaDB [oldboy] > create index index_name on test1 (name) Query OK 0 rows affected (0.05sec) Records: 0 Duplicates: 0 Warnings: 0 (4) re-query MariaDB [oldboy] > explain select * from test1 where name= "kaka"\ gateway * 1. Row * * id: 1 select_type: SIMPLE table: test1 Type: refpossible_keys: index_name key: index_name key_len: 48 ref: const rows: 1 # query rows Indicates that only one row of Extra: Using index condition1 row in set (0.00 sec) is currently queried. As you can see from the above example, using the index, you can query the required information more quickly. 2. Use explain command to optimize the basic flow of SQL statements (select statements) 1. Slow query SQL syntax method every 2 seconds input: SHOW FULL PROCESSLIST; if it appears twice, it means that there is a slow query MariaDB [oldboy] > show full processlist +-+-- +-+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-+-- +-+ | 9 | root | localhost | oldboy | Query | 0 | NULL | show full processlist | 0. 000 | +-+-- +-+ 1 row in set (0.00 sec) 2. Analyze slow log configuration parameters record slow query statement log_query_time = 2 # execute statements recorded in log for more than 2 seconds log_queries_not_using_indexes # has no index Record the location of log-slow-queries = / data/3306/slow.log # log in log explain select * from test from where name='oldboy'\ G # to see whether to index explain select SQL_NO_CACHE * from test where name='oldboy'\ G # remove cache 3, index the conditional columns that need to be indexed

In production scenarios, large tables cannot be indexed during peak hours, for example: 3 million records

4, analyze the slow query SQL tool mysqlsla (send email every morning) cut the slow query log, send it to everyone after re-analysis: 1) mv,reload process 2) cp, > clear 2) scheduled task mv / data/3306/slow.log / opt/$ (date +% F) _ slow.logmysqladmin-uroot-p123456 flush-logs mysqlsla Analysis: http://blog.itpub.net/7607759/viewspace-692828/ optimization cause: 1) there is something wrong with the website and the access is slow. A.web server load, storage, db (load, io, cpu) login db:show full processlist2) slow query statement (log file) long_query_time=2 # executes statements recorded in log for more than 2 seconds that log_queries_not_using_indexs # does not index, record the location of log-slow-queries=/data/3306/slow.log # log in log, analyze, and send it to the administrator for case analysis: 1. Check to see if there is a slow query in db: show full processlist;2.explain analysis: explain slow query statement 3. View the table structure: desc test1;4. Locate which column to index and which table 5. View the number of unique values of the conditional field column select count (distinct ader) from ad_oldboy_detail6. Build an index create index. Third, use profile to optimize SQL statements to optimize the content, and advanced DBA to use help show profile;select @ @ profiling;set profiling = 1 position select @ @ profiling;show profile;show profile for query 2; http://www.cnblogs.com/adforce/archive/2012/06/02/2532287.html

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.