Mysql Engine Optimization

Mysql engine optimization [java] MySQL Data Base engine depends on how MySQL is compiled during installation. To add a new engine, you must recompile MYSQL. By default, MYSQL supports three engines: ISAM, MYISAM, and HEAP. The other two types of INNODB and BERKLEY (BDB) are also frequently used. If the technology is superb, you can use MySQL ++ API to build an engine. The following describes several database engines: ISAM is a well-defined and time-tested data table management method, it is designed to take into account that the number of queries to the database is much larger than the number of updates. Therefore, ISAM performs read operations quickly without occupying a large amount of memory and storage resources. The two major disadvantages of ISAM are that it does not support transaction processing or fault tolerance: If your hard disk crashes, data files cannot be recovered. If you are using ISAM in a key task application, you must always back up all your real-time data. With its copy feature, MYSQL can support such backup applications. MyISAM: MyISAM is the MySQL ISAM extension format and default database engine. In addition to providing a large number of functions for indexing and field management not available in ISAM, MyISAM also uses a table lock mechanism to optimize multiple concurrent read/write operations, the cost is that you need to run the optimize table command frequently to restore the space wasted by the update mechanism. MyISAM also has some useful extensions, such as the MyISAMCHK tool used to fix database files and the MyISAMPACK tool used to restore wasted space. MYISAM emphasizes fast read operations, which may be the main reason why MySQL is so favored by WEB development: In WEB development, a large number of data operations you perform are read operations. Therefore, most VM providers and INTERNET platform providers only allow MYISAM format. An important defect in MyISAM format is that data cannot be restored after the table is damaged. HEAP: the HEAP allows temporary tables that reside only in the memory. HEAP is faster than ISAM and MYISAM in the memory, but the data it manages is unstable. If it is not saved before shutdown, all the data will be lost. When a row is deleted, HEAP does not waste much space. HEAP tables are useful when you need to use SELECT expressions to SELECT and manipulate data. Remember to delete the table after the table is used up. InnoDB: the InnoDB database engine is a direct product that creates MySQL flexibility. This technology is MYSQL ++ API. When using MYSQL, almost every challenge you face comes from the fact that the ISAM and MyISAM database engines do not support transaction processing or foreign keys. Although it is much slower than ISAM and MyISAM engines, InnoDB includes support for transaction processing and Foreign keys, both of which are not available in the first two engines. As mentioned above, if your design requires one or both of these features, you will be forced to use one of the two engines. MySQL officially explains InnoDB as follows: InnoDB provides MySQL with a transaction security (ACID compatible) storage engine with the capabilities of commit, rollback, and crash recovery. InnoDB locks row-level and also provides an Oracle-style non-locked read in SELECT statements. These features increase multi-user deployment and performance. There is no need to expand locking in InnoDB, because row-level locking in InnoDB is suitable for very small space. InnoDB also supports foreign key forcing. In SQL queries, You can freely mix InnoDB tables with other MySQL tables, or even in the same query. InnoDB is designed for the maximum performance when processing a large amount of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine. The InnoDB Storage engine is fully integrated with the MySQL server. the InnoDB Storage engine maintains its own buffer pool to cache data and indexes in the main memory. InnoDB stores its tables and indexes in a tablespace. The tablespace can contain several files (or original disk partitions ). This is different from the MyISAM table. For example, in the MyISAM table, each table is in a separate file. InnoDB tables can be of any size, even on an operating system with a file size limited to 2 GB. InnoDB is included in MySQL binary distribution by default. Windows Essentials installer makes InnoDB the default MySQL table on Windows. InnoDB is used to generate large databases that require high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. innoDB stores more than 1 TB of data, and some other sites process an average of 800 inserts/updates per second on InnoDB. In general, MyISAM is suitable for :( 1) performing a lot of count calculations; (2) insertion is not frequent, queries are very frequent, and (3) no transactions. InnoDB is suitable for: (1) high reliability requirements or transactions; (2) Frequent table updates and queries, and a large chance of table locking. Generally, MySQL provides multiple storage engines by default. You can view the following information: (1) What storage engines does your MySQL currently provide: mysql> show engines; (2) check your current default MySQL storage engine: mysql> show variables like '% storage_engine %'; (3) you need to check the engine used by a table (the storage engine used for the table is displayed after the parameter engine): mysql> show create table name; all performance tests are performed on: Micrisoft window xp sp2, Intel (R) Pentinum (R) M processor 1.6 oGHz 1 GB memory computer. Test method: 10 queries are submitted consecutively, and the total number of table records is 0.38 million, time unit s engine type MyISAM InnoDB performance difference count 0.0008357 3.0163 3609 query primary key 0.005708 0.1574 27.57 query non-primary key 24.01 80.37 update primary key 3.348 0.008124 update non-primary key 0.8183 100.7 insert 0.004141 0.02625 6.338 (1) after the index is added, MyISAM queries can be accelerated: 4 to 206.09733 times, InnoDB queries can be accelerated by 510.72921 times, And MyISAM updates can be slowed down to 1/2 of the original speed, the update speed of InnoDB slowed down to 1/30. It depends on the situation to determine whether to add indexes, such as non-query log tables. do not perform any indexes. (2) If your data volume is millions and you do not have any transaction processing, MyISAM is the best choice for performance. (3) InnoDB tables are larger in size. Using MyISAM can save a lot of hard disk space. In the table we tested, the table occupies the following space: engine type MyISAM InnoDB data 53,924 KB 58,976 KB index 13,640 KB 21,072 KB occupies the total space 67,564 KB 80,048 KB another million records table, the table space usage is as follows: engine type MyIsam InnorDB data 56,166 KB 90,736 KB index 67,103 KB 88,848 KB occupied total space 123,269 KB 179,584 KB