1 Blob field up to 64k requires greater use of Mediumblob (16M) or Longblob (4G) 2 to understand the performance and pros and cons of Fulltext like InStr 3 indexes help improve query efficiency, but can affect insertions and deletions, and comprehensive considerations when using indexes 4 When importing data, the post-build index is more efficient than the first-built index 5 can use specific columns, do not use select *6 to execute stored procedures faster than one of the 7 use the union of Select or high efficiency 8 learn how to use explain parsing statements to optimize 9 to view service log files frequently. Error log, slow query log 10 can specify a column character set 11 backup Mysqldump,mysqlhotcopy,backup Table,select into outfile to ensure that the data is fully written before the backup, it is necessary to perform the flush TABLES12 Because MySQL default writes take precedence over read operations, which can cause some fields with very frequent updates to affect the speed of queries, so for fields like clicks, you can use low_priority to lower the priority of this update and make the query operation take precedence, which increases the speed of Web site reads. For example: UPDATE low_priority table SET click=click+1 where id=123;13 when a client uses insert delayed, it gets a determination from the server immediately. And rows are queued, and the row is inserted when the table is not being used by another thread. Another important benefit of using insert delayed is that insertions from many clients are lumped together and written into a block. This is much faster than performing many separate inserts. There are some limitations when using delayed: The INSERT delayed is only available for MyISAM, memory, and archive tables. For MyISAM tables, if there are no free blocks in the middle of the data file, both SELECT and INSERT statements are supported. In these cases, you do not need to use Insert DELAYED for MyISAM. Insert delayed should be used only for INSERT statements that specify a value list. Server ignored for insert DELAYED ... The delayed of the SELECT statement. Server ignored for insert DELAYED ... The delayed of the on DUPLICATE UPDATE statement. Because the statement returns immediately before the row is inserted, you cannot use last_insert_id () to get the auto_increment value. Auto_incThe rement value may be generated by the statement. For SELECT statements, the delayed rows are not visible until the rows are actually inserted. Delayed is ignored in the subordinate replication server because delayed does not produce data that is not the same as the primary server in the secondary server. Note that the rows currently in the queue are only saved in memory until they are inserted into the table. This means that if you forcibly abort the mysqld (for example, using kill-9) or if the mysqld stops unexpectedly, all rows that are not written to the disk will be lost. Note that when the table is not being used, the insert delayed is slower than the regular insert. It is also an additional overhead for the server to manipulate a separate thread for each table that contains deferred rows. This means that you should only use Insert DELAYED when you confirm that you need it. Reference: http://www.cnblogs.com/chenmh/archive/2016/04/05/5355517.html
MySQL Learning notes
