Caching for a month: Caching Architecture, One article is enough. Today, start writing the database.

In the first article, we will talk about the two most commonly used storage engines of MySQL, MyISAM and InnoDB. According to your own understanding, summarize some knowledge points, not only talk about knowledge points, but also talk about “why”. MyISAM stores the total number of rows. InnoDB does not.

The subtext is, for select count(*) from t; If there is a large amount of data, MyISAM returns it instantaneously, while InnoDB scans it line by line.

Practice: InnoDB should not easily select count(*) for tables with large data volume, which will consume a lot of performance. Common pitfall: MyISAM will only return the result if the total number of rows in the entire table is queried. When the WHERE condition is added, both storage engines handle the result similarly. For example, t_user(uid, uname, age, sex);

  • uid PK

  • age index

select count(*) where age<18 and sex=’F’; To query the number of teenage girls, the two storage engines perform index scanning. Lesson: Regardless of the storage engine, index well.

MyISAM supports full-text indexing. Before InnoDB5.6, full-text indexing was not supported. Practice: No matter what kind of storage engine, in the case of large data volume and large concurrency, should not use the full-text index of the database, which will lead to a small number of requests to occupy a large number of database resources, but to use the “index outside” architectural design method. Implications: In business scenarios with large data volumes and high concurrency, MyISAM is not the best choice for full-text indexing. MyISAM does not support transactions. InnoDB does. Practice: Transactions are one of the reasons InnoDB is a very attractive choice, it offers commit, rollback, crash repair and more. MyISAM has a chance of causing file corruption in the event of a system crash, which can be very annoying. However, transactions are also very performance intensive and affect throughput, and it is recommended to use complex transactions only for businesses that require high consistency. Can’t open file ‘xxx.myi ‘. Ever come across one? Tip: MyISAM can implement something similar to a transaction with a lock table lock, but it has a significant impact on database performance and is strongly discouraged. MyISAM does not support foreign keys. InnoDB does. Practice: Regardless of the storage engine, foreign keys should not be used in the case of large amounts of data and concurrency, and it is recommended that the application guarantee the integrity. MyISAM only supports table locking. InnoDB supports row locking. Analysis: MyISAM: The table will be locked when the SQL statement is being read or written. Therefore, the performance deteriorates sharply when the data volume is large and the concurrency is high. InnoDB: fine – grained row lock, good performance in large data volume and high concurrency. Practice: It is often said on the web that the select+ INSERT business uses MyISAM, because MyISAM is extremely fast at sequentially adding records at the end of a file. Most of the business is mixed read and write, as long as the amount of data and concurrency is large, all use InnoDB. Common pitfalls: InnoDB row locks are implemented on indexes, not on physical row records. The subtext is that if the access does not hit the index and the row lock cannot be used, it will degenerate to a table lock. Voiceover: Oracle implements row locks differently. T_user (uid, uname, age, sex) innodb;

  • uid PK

  • No other indexes

update t_user set age=10 where uid=1; Hit index, row lock.

update t_user set age=10 where uid ! = 1; Index not hit, table lock.

update t_user set age=10 where name=’shenjian’; No index, table lock. Note: InnoDB must be indexed, otherwise the lock granularity is large, affecting concurrency.

In the Internet business scenario with large amount of data and high concurrency, MyISAM and InnoDB are summarized

  • There is a where condition, count(*) the performance of the two storage engines is similar

  • Instead of using full-text indexes, use the index Outside design

  • Transactions affect performance and are required for strong consistency

  • Without foreign keys, the application guarantees integrity

  • InnoDB cannot use row locks without hitting indexes

Conclusion In Internet business scenarios with large data volume and high concurrency, Please use InnoDB:

  • Row locking helps greatly with concurrency

  • Transactions, which contribute greatly to data consistency

These two points are the most attractive parts of InnoDB. A few small knowledge points, I hope you have a harvest. If something is wrong, you are welcome to correct it and discuss it together. Thanks.

Related articles:

Caching Architecture, One Article enough

58 home MySQL Catch-22 Upgrade Version