【转】MySql官方建议:Innodb表最佳实践
来源:互联网 发布:制作linux系统镜像img 编辑:程序博客网 时间:2024/06/07 23:40
原文:http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html
Best Practices for InnoDB Tables
If you have been using InnoDB for a long time, you already know about features like transactions and foreign keys. If not, read about them throughout this chapter. To make a long story short:
1) Specify a primary key for every table using the most frequently queried column or columns, or an auto-increment value if there isn't an obvious primary key.
指定主键:使用最频繁查询的一个或者多个列作为主键,如果没有,就使用自增id作为主键
2) Embrace the idea of joins, where data is pulled from multiple tables based on identical ID values from those tables. For fast join performance, define foreign keys on the join columns, and declare those columns with the same datatype in each table. The foreign keys also propagate deletes or updates to all affected tables, and prevent insertion of data in a child table if the corresponding IDs are not present in the parent table.
拥抱join:当数据从多个表里面按照相同的ID值取出来的时候,优先使用join,可以使用外键来提高join的性能。
3) Turn off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).
关闭autocommit:每秒提交几百次会限制性能。
(译者注:业务系统建议不要关闭,否则编程比较麻烦,批量导入数据的时候可以关闭,然后定时或者定量commit)
4) Bracket sets of related changes, logical units of work, with START TRANSACTION and COMMIT statements. While you don't want to commit too often, you also don't want to issue huge batches of INSERT, UPDATE, or DELETEstatements that run for hours without committing.
使用事务提交,避免频繁提交
5) Stop using LOCK TABLE statements. InnoDB can handle multiple sessions all reading and writing to the same table at once, without sacrificing reliability or high performance. To get exclusive write access to a set of rows, use theSELECT ... FOR UPDATE syntax to lock just the rows you intend to update.
不要使用LOCK TABLE操作,Innodb的机制能够支持高并发操作,且不损失可靠性和性能。推荐使用SELECT....FOR UPDATE
6) Enable the innodb_file_per_table option to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace. (This setting is required to use some of the other features, such as table compression and fast truncation.)
打开innodb_file_per_table选项
Best Practices for InnoDB Tables
If you have been using InnoDB for a long time, you already know about features like transactions and foreign keys. If not, read about them throughout this chapter. To make a long story short:
1) Specify a primary key for every table using the most frequently queried column or columns, or an auto-increment value if there isn't an obvious primary key.
指定主键:使用最频繁查询的一个或者多个列作为主键,如果没有,就使用自增id作为主键
2) Embrace the idea of joins, where data is pulled from multiple tables based on identical ID values from those tables. For fast join performance, define foreign keys on the join columns, and declare those columns with the same datatype in each table. The foreign keys also propagate deletes or updates to all affected tables, and prevent insertion of data in a child table if the corresponding IDs are not present in the parent table.
拥抱join:当数据从多个表里面按照相同的ID值取出来的时候,优先使用join,可以使用外键来提高join的性能。
3) Turn off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).
关闭autocommit:每秒提交几百次会限制性能。
(译者注:业务系统建议不要关闭,否则编程比较麻烦,批量导入数据的时候可以关闭,然后定时或者定量commit)
4) Bracket sets of related changes, logical units of work, with START TRANSACTION and COMMIT statements. While you don't want to commit too often, you also don't want to issue huge batches of INSERT, UPDATE, or DELETEstatements that run for hours without committing.
使用事务提交,避免频繁提交
5) Stop using LOCK TABLE statements. InnoDB can handle multiple sessions all reading and writing to the same table at once, without sacrificing reliability or high performance. To get exclusive write access to a set of rows, use theSELECT ... FOR UPDATE syntax to lock just the rows you intend to update.
不要使用LOCK TABLE操作,Innodb的机制能够支持高并发操作,且不损失可靠性和性能。推荐使用SELECT....FOR UPDATE
6) Enable the innodb_file_per_table option to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace. (This setting is required to use some of the other features, such as table compression and fast truncation.)
打开innodb_file_per_table选项
- MySql官方建议:Innodb表最佳实践
- 【转】MySql官方建议:Innodb表最佳实践
- 14.1.2 InnoDB表最佳实践:
- android官方最佳实践
- Android开发最佳实践建议
- mysql-innodb引擎优化建议
- Mysql 'like' 最佳实践
- MySQL最佳实践
- MYSQL索引最佳实践
- MySQL Innodb数据库性能实践
- CI入门 : 一些建议和最佳实践
- [uwsgi]使用建议(类似最佳实践)
- UI最佳实践的N条建议
- UI最佳实践的N条建议
- 常用数据库性能优化最佳实践建议
- Javascript 最佳实践 10 条建议
- android性能优化最佳实践建议
- 《Android和PHP最佳实践》官方站
- win7中vs2005启动调试问题总结
- 【技术类】【栅格常识(四E) 】栅格金字塔
- Sivlerlight中图片平铺
- Visual Basic 2008 中 Listbox列表框使用方法
- Struts2概述及工作流程分析
- 【转】MySql官方建议:Innodb表最佳实践
- 【应用类】【在线应用】导读篇:ArcGIS影像技术概览
- error MSB3073
- Linux wget 命令用法详解
- 【应用类】【在线应用】篇一:LandSat触摸控,影像显示随心而控
- 学摄影:烟花摄影入门技巧
- 使用函数CSAP_MAT_BOM_MAINTAIN维护BOM
- MySQL创建外键时出现error121解决办法
-