数据库级别的优化

Optimizing at the Database Level

The most important factor in making a database application fast is its basic design:  --优化关键在于基本设置

• Are the tables structured properly? In particular, do the columns have the right data types, and does each table have the appropriate columns for the type of work? For example, applications that perform frequent updates often have many tables with few columns, while applications that analyze large amounts of data often have few tables with many columns.      --设置正确的列数据类型，不同应用设置不同的表列（数量），例如：频繁更新的应用要表多列少，大数据分析应用要表少列多。

• Are the right indexes in place to make queries efficient?   --index

• Are you using the appropriate storage engine for each table, and taking advantage of the strengths and features of each storage engine you use? In particular, the choice of a transactional storage engine such asInnoDB or a nontransactional one such as MyISAM can be very important for performance and scalability.                              --为每个表选择适当的存储引擎，事务性存储引擎--innodb, 非事务性存储引擎--myisam

• Does each table use an appropriate row format? This choice also depends on the storage engine used for the table. In particular, compressed tables use less disk space and so require less disk I/O to read and write the data. Compression is available for all kinds of workloads with InnoDB tables, and for read-onlyMyISAM tables.        --根据表所使用的存储引擎为表选择合适的行格式（compressed 表，节省表空间，从而读写磁盘I/O少，compressed 对所有Innodb表生效，对只读myisam表有效）

• Does the application use an appropriate locking strategy? For example, by allowing shared access when possible so that database operations can run concurrently, and requesting exclusive access when appropriate so that critical operations get top priority. Again, the choice of storage engine is significant. The InnoDB storage engine handles most locking issues without involvement from you, allowing for better concurrency in the database and reducing the amount of experimentation and tuning for your code.         --锁定策略

• Are all memory areas used for caching sized correctly? That is, large enough to hold frequently accessed data, but not so large that they overload physical memory and cause paging. The main memory areas to configure are theInnoDB buffer pool, the MyISAM key cache, and the MySQL query cache.  --合理设置缓存