• 1. MySQL优化—— zhys9
  • 2. 1.1 建表 – 表的类型MySQL中表的类型表现为不同的存储引擎(Storage Engine),同一种存储引擎在不同的应用中表现出的性能差异也是不容忽略的重要因素。因此,在建表之初应当正确选择表的类型。鉴于我们的应用范围熟悉MyISAM和InnoDB两种类型的性能区别即可,MyISAM类型在select操作多的应用中优势明显;InnoDB在insert、update操作多的应用中优势明显。
  • 3. 1.2 建表 – 数据类型 选择数据类型来帮助提高查询运行速度 把数据列定义成不能为空(NOT NULL)。这会使处理速度更快,需要的存储更少。它有时候还简化了查询,因为在某些情况下不需要检查值的NULL属性。 考虑使用ENUM数据列。如果某个数据列的基数很低(包含的不同的值数量有限),那么可以考虑把它转换为ENUM列。ENUM值可以被更快地处理,因为它们在内部表现为数值。
  • 4. 1.2 建表 – 数据类型 以下是几种常用数据类型在MySQL中的性能顺序(从高到低) INT > CHAR > VARCHAR > TEXT ENUM介于INT与CHAR性能行之间; 在相同数据类型的情况下,长度短的性能更高,因此在建表时要充分考虑每个字段最合适的类型及长度。
  • 5. 1.3 建表 – 索引索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用,但是一般来说引起最大性能差异的都是索引的正确使用与否。 如果要考虑给已经索引过的表添加索引,那么就要考虑将要增加的索引是否是已存在于多列索引的最左前缀。如果是已存在的就不用新增加索引了。
  • 6. 1.3 建表 – 索引最左前缀(Leftmost Prefixing):多列索引的一个优点,比如在用户表上简历了一个firstname、lastname、birth列上的多列索引,我们称这个索引为fname_lname_ birth 。当搜索条件是以下各种列的组合时,MySQL将使用fname_lname_ birth索引: firstname,lastname, birth firstname,lastname firstname 从另一方面理解,它相当于创建了(firstname,lastname, birth)、(firstname,lastname)以及(firstname)这些列组合上的索引。 普通索引、唯一性索引、主键、全文索引
  • 7. 1.3 建表 – 索引普通索引 这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建: 创建索引,例如CREATE INDEX <索引的名字> ON tablename (列的列表); 修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表); 创建表的时候指定索引,例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );
  • 8. 1.3 建表 – 索引唯一性索引 这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。唯一性索引可以用以下几种方式创建: 创建索引,例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表); 修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表); 创建表的时候指定索引,例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
  • 9. 1.3 建表 – 索引主键 主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。我们常用的ID字段通常是这种类型的索引,且通常会增加AUTO_INCREMENT属性。主键一般在创建表的时候指定,例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但也可以通过修改表的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。
  • 10. 1.3 建表 – 索引全文索引 MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。要了解更多信息,请参见MySQL documentation。
  • 11. 2.1 导入数据批量载入比单行载入的效率高,因为在每条记录被载入后,键缓存(key cache)不用刷新(flush);可以在这批记录的末尾刷新键缓存。键缓存刷新的频率减少得越多,数据载入的速度就越快。 没有索引的数据表的载入速度比有索引的要快一些。如果存在索引,不但要把记录添加到数据文件中,还必须修改索引来反映新增的记录,因此要合理添加索引。
  • 12. 2.1 导入数据较短的SQL语句比较长的SQL语句快,因为它们所涉及到服务器端分析过程较少,同时通过网络把它们从客户端发送到服务器上的速度也更快。 其中有些因素看起来是次要的(尤其是最后一个),但是如果导入的数据很多,那么即使很小的效率差异也会导致一定的性能差别
  • 13. 3.1 查询虽然索引对查询有着至关重要的作用,但查询语句同样对性能有很大影响。查询中条件的罗列及顺序会影响到MySQL查询优化器对索引的利用。查询原则:在多个条件情况下最先使用能够得到最小子集且已建立索引的字段;以逐步缩小查询范围,减少磁盘I/O操作。
  • 14. 3.1 查询大多情况下应该索引WHERE子句和join子句中出现的每一个列,但并不完全。还必须考虑到对列进行比较的操作符类型。MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形。例如,“SELECT id FROM `table` WHERE `words` LIKE 'ddes%'”这个查询将使用索引,但“SELECT id FROM `table` WHERE `words` LIKE '%ddes';”这个查询不会使用索引。
  • 15. 3.1 查询在PHP提供的操作MySQL的API中有一系列可用函数,但对于这些函数的不同使用也会造成程序在性能上的差异。 Mysql_pconnect与mysql_connect Mysql_fecth_array与mysql_fetch_assoc: 前者会比后者多耗用一倍的内存,因此在无特别需求的情况下尽量使用后者; 临时表的使用;CREATE TEMPORARY TABLE IF NOT EXISTS xxx LIKE x;
  • 16. Thank you! 2007-11-29
  • 17. 附录.InnoDB vs MyISAM vs Falcon benchmarks