Shanghai MySQL Conference Summary

[Tan junqing: Comparison between MyISAM and InnoDB] The most commonly used MySQL storage engine diagram is MyISAM. InnoDB is generally recommended to customers. MyISAM Storage Structure: three files (tablename. frm, tablename. MYD, tablenamemyi), do not support transactions, table locks.

 

[Tan junqing: Comparison between MyISAM and InnoDB] MyISAM Row Storage Format 1 fixed length-the table structure does not contain variable length fields. By default, it is fixed length storage. Benefits (high performance, better recovery capability, no need to organize) 2. Variable Length-if the table structure contains fields such as varchar, varbinary, blob, and test, the data is stored as variable length. Disadvantage (fragment may be caused and the recovery difficulty is increased) 3 compression format-read-only format

 

Cheng Jiangdong: How many questions does Shanda candy community have? 1 is the concurrency pressure increasing rapidly? 2. Is demand changing fast? 3. Data horizontal split? Can nosql databases solve these problems.

 

Cheng Jiangdong: Topic: What is MongoDB and its features, applicable scenarios, and comparison with other databases. Nosql databases are non-relational databases and are mainly used for community-type Web websites. It mainly solves the needs of 1 High-concurrency databases, 2 high-efficiency storage and access to massive data, and 3 High-scalability and high-availability databases.

 

What is MongoDB? Cap theory-the fish and the bear's paw cannot be used simultaneously. Consistency (C), partition adequacy (p), and availability (). A Distributed Database can only meet two requirements at the same time. 1) Ca traditional relational database 2) AP: Key-value Database

 

Therefore, according to cap theory. MongoDB cannot solve: Database Transaction consistency requirement 2 database write timeliness and so on. MongoDB is a product between relational databases and non-relational databases. It has the most abundant functions and features like relational databases. The supported data structure is very loose and is similar to the JSON bjson format. Therefore, it can store complicated data types.

 

MongoDB uses the JSON format document database, document, and set. Free data mode: supports embedding other objects and arrays in objects and arrays. A key problem in the Mongo mode design is that it is worthwhile to create a set for this object? Or embed this object into other sets.

 

MongoDB index design is very important and supports a wide range of features. Including basic indexes, unique indexes, keys in embedded documents, documents themselves, composite indexes, and online indexes.

 

High Availability of MongoDB replication and automatic sharding. If one failure occurs, it is automatically switched to another available server. Several servers form sharded clusters, with more than two shards, more than one config servers, and any number of mongos servers, the application connects to mongos servers.

 

I found five servers for a simulated test, a PHP script. In the initial stage, the distribution was uneven. There were more than 1 million rows in the first database, and about 0.3 million rows in the other two databases. After adding one server to 5 million rows, it is found that the data has become very even.

 

MongoDB's rich query statements: In query, sorting U, exists, querying shards, Count, regular expressions, cursors, type matching, and number of array elements.

 

MongoDB MAP/reduce is a tool for aggregating and filtering data. MongoDB has excellent performance (concurrency can be more than 15 thousand seconds, no foreign key constraints, five transactions, asynchronous disk writing .) Other features (gridfs, easy to use, will automatically create database dB and collection, without explicit execution .)

 

Applicable scenarios of MongoDB 1 the structure is not fixed, and data nesting 2 requires high concurrency 3 Regular horizontal data splitting 4 memory is greater than data volume (recommended) deficiency 1 occupies disk space, performance is affected by memory 2 performance depends on memory, and memory size cannot be specified at the same time, it is easy to be occupied by other programs 3 does not support transactions, does not support join 4 each document limit cannot exceed 4 MB

 

Why MongoDB? Excellent performance, strong scalability, document-oriented, simple deployment, comprehensive functions, easy development, and comprehensive support. The Contact Group is Google groups MongoDB-user, and the Douban group is MongoDB.

 

Brief Analysis of other nosql databases: redis can be considered as an enhanced version of memcache, redis and Tokyo cabinet. Without the automatic sharding Technology of MongoDB, Tokyo cabinet is maintained by individuals, performance problems occurred during the grand over 0.1 billion record tests. Some applications are used in Shanda candy. Cassandra query is not rich enough, and the stability is poor.

 

There are a few applications in the candy community that are using Tokoy cabinet. I have tried MongoDB and will increase the application deployment. Of course, MySQL is mainly used. It should be combined with MySQL to complement each other and obtain comprehensive benefits.

 

Optimization includes: Table design, index planning, statement optimization, stored procedures, triggers, and views. 1 Table Design: naming rules (no reserved words, multi-byte characters), field types (values, characters, binary, time, and others) should be appropriate, focusing on performance, disk storage, make a balance between data accuracy and so on. For example, the exact floating point number must be decimal, but it is actually memory-type storage with poor performance.

 

1. Table Design continued: Engine selection, MyISAM (Fast Read, high compression ratio table lock); InnoDB (transaction support, foreign key support, row lock, hot backup support); memory (memory data, table lock); InnoDB is the only single-host engine that supports transactions. Memory Security check results in memory errors, but I/O performance is certainly high.

 

1. Table Design-engine Introduction: the most reliable engine NDB (MySQL cluster): supports transactions, highly concurrent writes, key-value persistency, and high reliability of 99.999%. Archive: a higher compression ratio than MyISAM, with many disadvantages (only insert, not support primary keys ). CSV is a bridge between EXCEL and MySQL. blackhole: relieves the pressure on the master in replication.

 

1. Table Design continued: [encoding selection] single-byte, multi-byte; garbled characters often occur because the exported file is not supported by the character set of the terminal system and then imported to the new database, it becomes garbled. [Index planning] unique indexes, common indexes, partial indexes, clustered indexes, and foreign keys. Foreign keys are only supported by InnoDB. Try not to use foreign keys randomly, which may affect performance.

 

[Statement optimization] focuses on the response time. The whole time includes the execution time and transfer time, which requires constant research and optimization. Some good habits of statement optimization: 1) do not use internal functions if SQL statements are no longer used; 2) use join statements with better performance than where statements; 3) Pay attention to type Matching and integer expressions '', for example, '15' and 15; 4) replace column (length) by adding a field such as column_length );

 

Statement optimization 5) Like 'de' can be written to> = 'de' and <= 'df '; 6) Remove unnecessary temporary tables, unnecessary fields, and sorting; 7) There is a lack of necessary filtering conditions; 8) The subquery processing is not optimized enough before mysql5.5, which is not as fast as the join statement; 9) Different Types of fields need to be modified to be consistent in the associated query; 10) record a specific record for slow locating.

 

Statement optimization: 11) in a complex query, multiple fields are selected from one table. The number of table scans is too large, and Io operations are too many, affecting the speed;

 

Advantages of using a summary table: reduces access to the original disk table. Disadvantages: Make appropriate updates. Data that is updated frequently may be inappropriate. For example, the report table is suitable.

 

Optimize the trigger for writing statement optimization. Features: Row-level, temporary row table, front and back. The view should not be used as far as possible. The performance is poor because no index is needed. Even if you want to use a view, there are many restrictions and the time relationship is not detailed.

 

MySQL replication asynchronous data replication, load balancing, online Hot Standby, horizontal scaling, cross-network data replication, fail over --- manual switch. There may be six replication schemes: one master and one slave, one master and two slave, and two master and one slave (not good, not recommended ), 1 master 1 slave 2 slave 1 master 1 master 1 master (may be faulty) 3 slave 1 master 1 master 1 master 1 master (may be faulty)

 

MySQL replication has good performance in scale out, and can be implemented on multiple slave machines step by step. Slave can be used as an online Hot Standby function. When there is a problem with the primary database, you can manually set it to switch all the operations on the primary database to the slave database to ensure external access.

 

MySQL Cluster Features 1) data storage mode without shared storage; 2) Real-time Data Synchronization; 3) high performance and high transaction throughput for special applications; 4) no special hardware requirements; 5) memory storage data & hard disk storage data (Version 5.1) 6) fail over-fast and automatic; 7) heartbeat mechanism between data nodes;

 

MySQL cluster data is stored on the master server. On each data node in the master, another data node is used to provide copies. A node has a problem, the Service will continue. In extreme cases, the two dots are broken, or the parts of one node and other nodes are broken. What should we do?

Generally, two management nodes can meet the needs of the management cluster. However, the two nodes are broken and a third high availability solution is required. MySQL cluster with replication also serves as a disaster recovery backup for remote backup.

 

Heartbeat & MySQL replication 1) heartbeat mechanism-security management of resource failover; 2) virtual IP management-transparent application access to the database; heartbeat software is added between the master and slave nodes to provide automatic failover.

 

Heartbeat, block and MySQL replication distributed block replication (drbd) runs on the standard network protocol. Data transmission through network I/O is sometimes faster than synchronization. After a problem occurs in the active state, data must be synchronized. After the problem is fixed, the system switches back to the repaired server.

 

MySQL with shared storage 1) active/passive multi-instance access to the same data file 2) features: high cost and storage of data in the shared storage San.

 

The above content comes from Sina @ thinkinlamp's bib