Http://neoremind.net/2011/06/%E4%B8%80%E4%B8%AAdba%E5%AF%B9%E4%BA%8E%E5%BC%80%E5%8F%91%E4%BA%BA%E5%91%98%E4%BD %bf%e7%94%a8mysql%e7%9a%84tips/ just entered the office shortly after a 3-year DBA departure, the last day to give us a training, the following are some key points of the record, MySQL use, table design learning is still very large, In the future will continue to learn the database part of the relevant knowledge. 1 Insert to write a field name, not light values, to avoid changes in the structure of the library table 2 do not use SELECT *, write the exact field, performance considerations, reduce Io,mysql optimization 3 Minimize the use of the table query 4 Cannot use the table update, as far as possible with the load DATA 5 data constraints as far as possible by the program to ensure that 6 large data volume sorting and other operations are best done by the program 7 all fields are positioned as NOT null, numeric type with 0 instead of the character type with "instead of 8 library, Table naming, by C-style, table name lowercase, two such as Beiker_sms_template 9 50 bytes below the direct use of char, do not use varchar (update delete will increase the cost, data file fragmentation, file system addressing efficiency is low) 10 different tables The same field needs to use the same field name, such as the User_id 11 field plus comment 12 to eliminate only the table, to periodically delete invalid data 13 Try to use the primary key 14 using the prefix index create table xxx (id int () name varchar (255) value VARCHR (255) Not NULL DEFAULT ' 0000-00-00 00:00:00′primary K EY (' id ') KEY ' userid ' (' userid, ' ' Value ', ' ID ')) select * from xxx where UserID = xx and value = improve operational efficiency 15 for InnoDB, using a primary key lock is a row, using an index lock may not be worth a row 16 for MyISAM to close the index before bulk insert, and then open the index after all inserts, index update alter table xxx Disable Keys; insert ... insert is highly efficient endable keys; Time-consuming is the analysis Index 17 single-table data volume recommended less than 2KW, otherwise there is a performance inflection point, need to split the table 18 union operation as little as possible, must be used in the case of union all, and do not use union, two times the result of repeated rows to remove, CPU IO consumption will be much less, the amount of data small words 19 not in will not use the index, it is recommended not to use in, try to do the program 20 don't use CTRL c stop a script with kill otherwise the master-slave synchronization will be inconsistent. 0
A DBA who uses MySQL tips for developers
