Mysql optimization statement

After the mysql client is successfully connected, you can use the show [session | global] status command to provide server status information. The session indicates the statistical results of the current connection, and the global indicates the statistical results since the last time the database was started. The default value is the session level. Example of "show status like 'com _ % '" In www.2cto.com, where "Com_XXX" indicates the number of times the XXX statement is executed. NOTE: With these parameters Com_select, Com_insert, Com_update, and Com_delete, you can easily see whether the current database application is dominated by insert update or query operations, and the approximate execution ratio of various types of SQL statements. There are also several common parameters to help you understand the basic information of the database. Connections: Number of times the MySQL server is connected. show status like 'ons ons' Uptime: the server's working time (unit: seconds) show status like 'uptime' Slow _ queries: slow Query Count (10 by default) show status like 'slow _ queries 'How to query mysql Slow query time Show variables like 'long _ query_time '; www.2cto.com modify the mysql slow query time set long_query_time = 2 *** how to locate slow query Show variables like 'long _ query_time '; you can reset set long_query_time = 2 ***** test statement ***** select * from emp e, dept d where e. empno = 123451 Nd e. deptno = d. deptno; if order by e. empno is slower, sometimes more than 1 minute. * *** by default, mysql does not record slow query logs, you need to specify bin \ mysqld.exe--slow-query-log at startup. This slow query log will be placed under the data Directory [in mysql, It will be placed under the mysql installation directory/data/], in mysql5.5.19, You need to view my. ini datadir = "C:/Documents and Settings/All Users/Application Data/MySQL Server 5.5/Data/" to confirm. explain select * from emp where ename = "zrlcHd" generates the following information: select_type: indicates the type of the query. Table: type of the output result set table: indicating the table connection type possible_keys: indicates the index key that may be used for query: indicates the actually used index key_len: the length of the index field rows: number of scanned rows: www.2cto.com Extra: The execution description and description. To improve database performance, indexing is the best and inexpensive. There is no need to add memory, no need to change the program, no need to tune the SQL, as long as the correct 'create Index' is executed, the query speed may be improved by times, which is really tempting. However, there is no free lunch in the world, and the query speed is improved at the cost of the insert, update, and delete speeds. These write operations increase a lot of I/O. Is creating an index able to solve all the problems? What if no index is created on ename? Select * from emp where ename = 'axjxc '; index cost disk occupied by dml (update delete insert) statement efficiency impact ** four types of indexes you can see when you create an index using phpmyadmin ** differences between mysql four indexes PRIMARY Index = automatically create a UNIQUE index on the PRIMARY key => equivalent to INDEX + UniqueINDEX INDEX => is the general index fulltext => only supported by the MYISAM storage engine, the purpose is full-text indexing. It is used in many content systems and is used in many websites in full English (English words are independent ). chinese data is not commonly used. It is of little significance that domestic full-text indexes are usually completed using sphinx. ** compound index create index name on table name (column 1, column 2); index creation: create [UNIQUE | FULLTEXT] index index_name on tbl_name (col_na Me [(length)] [ASC | DESC],...); Alter table table_name add index [index_name] (index_col_name ,...) add primary key (INDEX) alter table name add primary key (column name ,..); joint primary key deletion index drop index index_name ON tbl_name; alter table table_name drop INDEX index_name; www.2cto.com Delete primary key (index) is special: alter table t_ B drop primary key; query INDEX (both can) show index from table_name; show keys from table_name; desc table_Name; The following table will not use an index: 1. If the condition contains or, it will not be used even if the condition contains an index. 2. If multiple-column indexes are not the first part, they are not used. 3. The like Query starts with 4%. If the column type is string, you must quote the data in the condition using quotation marks. Otherwise, no index is used. 5. If mysql estimates that full table scan is faster than indexing, no index is used. Add a primary key index alter table dept add primary key (deptno) -- Test statement explain select * from dept where deptno = 105 \ G; the result is: mysql> explain select * from dept where deptno = 105 \ G; * *************************** 1. row ************************* id: 1 www.2cto.com select_type: SIMPLE table: dept type: constpossible_keys: PRIMARY key: PRIMARY key_len: 3 ref: const rows: 1 Extra: 1 row in set (0.00 sec) -- create multi-column index alter table d Ept add index myind (dname, loc); -- proves that the leftmost column is used for the created multi-column index as long as the query condition uses the column, in general, the index will be used to explain select * from dept where dname = 'effectupqjzvf '\ G; the index myindexplain select * from dept where loc = 'msbdpmrx' \ G is used; the index myind is not displayed. -- For the like query, explain select * from dept where dname like '% effectupqjzvf' \ G; the index myindexplain select * from dept where dname like 'effectupqjzvf % '\ G is not displayed; the index myind is used. -- if there is or in the condition, it is not used even if there is a condition with an index. Show, we delete the composite index, and then only add the index to the dname. alter table dept drop index myind www.2cto.com alter table dept add index myind (dname) explain select * from dept where dname = 'aaa' or loc = 'A' \ G; // The dname column is not used. If the column type is string, you must quote the data in the condition using quotation marks. Otherwise, the index select * from dept from dname = 1234 \ G // The index select * from dept from dname = '000000' \ G // will be used to view the index. show status like 'handler _ read % '; handler_read_key: the higher the value, the better. The higher the value indicates the number of times the index is queried. Handler_read_rnd_next: a higher value indicates inefficient query. In some cases, you can use a connection to replace subqueries. Because join is used, MySQL does not need to create a temporary table in memory. If you want to use indexes in query statements containing or, indexes must be used for each condition column between or. If there is no index, you should consider adding indexes when inserting data, it is placed at the end by default ., the deleted data is not recycled. (transactions and Foreign keys are not supported.) InnoDB supports transactions and Foreign keys in applications with high precision requirements. We recommend that you use fixed points to store values, to ensure the accuracy of the results, create table temp1 (t1 float (1000000.32), t2 decimal (1000000.31); insert into temp1 values (,); it is found that t1 is, so there is a problem. for databases whose storage engine is MyISAM, If you often delete and modify records, you must regularly execute the optimize table table_name; function to fragment tables. Www.2cto.com create table temp2 (id int) engine = MyISAM; insert into temp2 values (1); insert into temp2 values (2); insert into temp2 values (3 ); insert into temp2 select * from temp2; -- Copy delete from temp2 where id = 1; find that the table's data files are not small and regularly execute optimize table temp2 to find the table size changes, fragment finishing & InnoDB's data will be stored in the data/ibdata1 directory, with only one data/database *. frm table structure file. if a table contains too many records, if I split it into 100 tables, each table has only 0.1 million records. A good splitting basis is the most important. In UNION, some tables have a small number of records, which may contain 2 or 30 thousand records, but the fields are very long. The table occupies a large amount of space and requires a large number of I/O operations during table retrieval, seriously reduces performance. In this case, you need to split the large field into another table, and the table has a one-to-one relationship with the original table. (JOIN) Author ljfbest