Mysql Optimization in CentOS

Mysql Optimization in CentOS
1. debug mysql Command method: mysql> explain select * from t1 \ G or: mysql> desc select * from t1 \ G mainly to view the following attributes:(1) Whether index is used for type(2) key Index name(3) number of rows affected by rows query (the smaller the number, the better the optimization)2. Use the mysql command to obtain some service optimization information:(1) show status like 'com _ % '; # mainly refer to com_select, com_insert, com_update, and com_delete. All the results are session results after the service is started, of course, all the data is of the storage type.(2) You just want to view the innodb information: show status like 'innodb _ rows _ % '; The master wants to view innodb_rows_read, innodb_rows_inesrted, innodb_rows_updated, innodb_rows_deleted.(3) show status like 'connections'; # view the number of times the mysql server is connected(4) show status like 'uptime'; # working time of the mysql server(5) show status like 'slow _ queries; # The number of slow queries3. index optimization problems:(1) The common field to be indexed is: field after where(2) fields after order(3) The form of like 3% instead of % 3(4) indexes must be used for pre-and post-or fields.(5) The first field of the composite index(6) create an index: mysql> create index ind_name on t1 (name );(7) Check the index usage: mysql> show status like 'handler _ read % '; # If handler_read_rnd_next has a high value, the index is inefficient and needs to be optimized, if it is low, the index is efficient.4. Import and export Optimization of common SQL tables:(1) mysql> alter table t1 disable keys mysql> load data infile 'C:/t1.txt '; mysql> alter table t1 enable keys;(2) mysql> set unique_checks = 0; mysql> load data...; msyql> set unique_checks = 1;(3) mysql> autocommit = 0; mysql> load data...; mysql> autocommit = 1;5. Optimize the insert statement. It is best to use this form of a row of multi-value: insert into t1 (name) values (1), (2), (3)6. when a file loads a table, load data infile is 20 times faster than many insert statements, and mysqlimport is fast, because it uses a function interface such as load data infile.7. query includes group by but how to avoid consumption of sorting results: mysql> desc select id from t1 group by id order by null \ G8. Optimize nested queries:(1) mysql> desc select * from t1 where s_id not in (select id from comany2) \ G(2) mysql> desc select * form t1 left join company2 on t1.s _ id = comany2.id where t1.s _ id is null \ G # The above left join format is significantly faster than not in () this seed query, because join does not need to create a temporary table in the memory to complete the query in two steps logically.9. SQL index prompt:(1) use index mysql> desc select * from t1 use index (ind_id) where id = 3 \ G(2) ignore index mysql> desc select * from t1 ignore index (ind_id) where id = 3 \ G(3) force index mysql> desc select * from t1 force index (ind_id) where id> 0 \ G # note that the index of this field with range judgment after the where clause does not work, but the index can be forcibly used by humans, although it does not play a role in influencing rows, this is the right of mysql to users to choose their own plans.10. Optimization related to table deletion:1) Use delete to delete mysql> delete from t1; Query OK, 10 rows affected (0.01 sec)2) use truncate to delete mysql> truncate table t2; Query OK, 0 rows affected (0.00 sec) # use either of the following methods to clear the table data, however, the second method is faster than the first method, and saves memory.