Using PHP + MySQL to handle overloaded database issues thinking and summarizing

The following is my use of PHP + MySQL to deal with overloaded database problem thinking and summary, experience must have a lot of deficiencies in the place, welcome to the experts to teach:

In the design of a reasonable table structure and the corresponding approximate how many access pressure to bear, need not to be a table, if you need to table each table how many records appropriate;

The implementation of the table can be handled either by PHP processing or by MySQL's own MERGE table type (i.e. Mrg_myisam):

⑴ If you are working with PHP, there are two things:

① new Library new table with no data to prepare for compression, for example, a chat room at the outset to consider the possibility of more than a child chat room, can be based on a number of conditions, such as by the primary key or the scope of the ID to be stored in accordance with the corresponding algorithm allocation stored in the same structure but different names of the table;

② If you have a database that has a lot of data to decompress, you can follow certain rules, for example, according to the frequency of access to the table, the commonly used data into a table, rarely accessed data into another table, access to the data first access to the common table, and then access the very table when not found.

⑵ if the MySQL comes with the table in the form of processing, is the use of MySQL's MERGE table type (that is, Mrg_myisam). This time is generally for the teaching of more data, this approach is used when a single datasheet is unable to withstand the load, and you can use tools such as phpMyAdmin to modify the type of the related table, as well as to determine the number of records in each table in the list, but this operation requires caution, In addition to the first table, future data will be added to the last child table, and the need to manually process the next time, edit the relevant "show." MRG "file, and also handle the" ALTER table tablename auto_increment = xxx "of the new child table.

When using the merge table, you need to be aware of some of the problems mentioned in the manual: ① If you use ALTER table to change the MERGE table to another table type, the mapping to the underlying table is lost. Instead, rows from the underlying MyISAM table are copied into the replaced table, which is then assigned the new type.

②replace does not work.

③ there is no WHERE clause, or REPAIR table,truncate table,optimize table or ANALYZE table on any table that is mapped to an open MERGE table, you cannot use DROP table,a Lter Table,delete from. If you do, the MERGE table will still point to the original table, resulting in unexpected results. The easiest way to solve this problem is to send a FLUSH tables statement before performing any of these operations to make sure that no MERGE tables remain open.

④ a MERGE table cannot maintain a UNIQUE constraint on the entire table. When you perform an INSERT, the data enters the first or last MyISAM table (depending on the value of the Insert_method option). MySQL ensures that unique key values remain unique in that MyISAM table, but not across all tables in the collection.

⑤ when you create a MERGE table, there is no check to make sure that the underlying table exists and that there are identical bodies. When the MERGE table is used, MySQL checks that the record length of each mapped table is equal, but this is not very reliable. If you never create a MERGE table from a similar MyISAM table, you are likely to bump into strange questions.

⑥ the order of the indexes in the MERGE table and the indexes in its underlying table should be the same. If you use ALTER TABLE to add a unique index to a table that is used in the merge table, and then use ALTER TABLE to add a non unique index to the merge table, the table's index is sorted differently if there is already a non unique index on the underlying table. (This is because ALTER TABLE places a unique index before a unique index to facilitate fast detection of duplicate keys). Therefore, queries against tables that use such indexes may return unexpected results.

⑦ in Windows, DROP table on a table used by the merge table does not work because the merge engine's table mappings are hidden from the top of MySQL. Because Windows does not allow the deletion of open files, you must first refresh all the merge tables (using FLUSH tables) or remove the merge table before removing the table.