mysql优化方案
来源:互联网 发布:中世纪2优化九国家简介 编辑:程序博客网 时间:2024/05/19 23:56
总体优化建议】 2、当MyISAM使用动态表格式时,偶尔使用OPTIMIZE TABLE可以避免碎片。可以写一个脚本程序定时执行优化。 3、在Web服务器中,图象和其它二进制资源应该作为文件存储。也就是仅在数据库中存储的文件的引用地址而不是文件本身。大多数Web服务器在缓存文件方面比数据库内容要好得多,因此使用文件一般要快得多。 4、对经常访问的不重要数据(如session)使用内存表。 5、在MYSQL的主写从读的架构中,一般将主库的表类型使用innoDB类型,而从库则使用myisam的表结构。因为innoDB支持事务,而且它是行锁,对插入、更新、删除的影响比较小,更改数据的性能也更高(很多人测试过)。 6、用DELAY_KEY_WRITE=1选项声明MyISAM表可以使索引更新更快,因为在表关闭之前它们不刷新到硬盘上。不利之处是当表打开时 如果杀掉服务器,应确保用–myisam-recover选项运行服务器保证没有问题,或者在重启服务器之前运行myisamchk。 【查询优化】 2、使用explain EXTENDED SELECT …,可以得到重写并且执行优化规则后SELECT语句,其中查询时必须检查的行数[rows]的乘积(笛卡尔乘积)越小,查询效率越高。 3、MySQL用一遍扫描多次联接(single-sweep multi-join)的方式解决所有联接。这意味着MySQL从第一个表中读一行,然后找到在第二个表中的一个匹配行,然后在第3个表中等等。 4、join的条件字段考虑实际情况建立索引,条件字段的类型、长度要一致。 5、使用FOUND_ROWS和SQL_CALC_FOUND_ROWS来计算分页,它们的主要作用是计算SQL语句中除去LIMIT的查询结果集的行数,因为它是在查询表扫描行时计算出来的,所以性能会更高。[见MYSQL分页统计的新方法] 【插入优化】 2、尽量使用INSERT INTO TABLE (xxx,xxx),(xxx,xxx),这比使用单行INSERT语句快(在某些情况下快几倍)。 4、可以使用INSERT `pv_table` (page_id, pv) VALUES (1, 20) ON DUPLICATE KEY UPDATE PV=PV+20 的方式来代替先查询表记录是否存在再插入或者更新的业务逻辑。 【索引】 2、组合索引比单一索引更有效,但是需要合理使用,清楚你在干什么,组合索引采用B树类型,最左前缀的特性,创建组合索引的时候,要考虑到具体的使用场景和逻辑。 3、在需要创建索引的字段上,不能设置默认值为NULL,或者也不能插入NULL值,否则索引没有意义。(好像5.1后部分类型的表可以支持NULL的索引) 4、不是索引越多越好,过多索引容易导致更新和插入数据效率大大降低。 5、要理解最基本的索引概念,只有在where和join用到的字段上创建索引才有价值,另外Max()和Min()函数用到的字段也需要 6、Where条件里面,有些情况下也是用不到索引的,比如<>, NOT IN, LIKE语句以 %_开头的时候,这时,可以用 id>m or id,用 NOT EXISTS代替NOT IN,可以用到索引的WHERE条件有:<,<=,=,>,>=,BETWEEN,IN,不以%_开头的LIKE。 7、对于CHAR和VARCHAR字段,只用字段的一部分就可创建索引。创建索引时,使用col_name(length)语法,因为多数字段的前 10个字符通常不同,所以此索引不会比使用字段的全名创建的索引速度慢很多。另外,使用字段的一部分创建索引可以使索引文件大大减小,从而节省了大量的磁 盘空间,有可能提高INSERT操作的速度。尤其一些md5()后结果的字段比较适用。 【附录】 extra列返回的描述的意义
1、 在一般情况下,应该尝试以非冗余方式(查看数据库理论中的第三正则形式)保存数据,但是为了获得更快的速度,可以冗余字段或创建总结表。
注:一定要记得要将innodb的auto_commit的默认值设置成0或false,它是默认值是1
InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”
1、索引实际上是一个单独的、物理的数据库结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针 清单。理解了这个概念,对于合理使用索引很重要,比如单独查询一个索引列的时候速度非常快,因为mysql根本不去检索实际表,而只要查询排序好的索引表 就行了。
所以关联的表的顺序应该是记录数从多到少的顺序,这样它的笛卡尔乘积就越小,查询效率就越高。
如:
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM
tbl_name
WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
1、插入一条记录大致的时间使用情况是:
连接:(3)
发送查询给服务器:(2)
分析查询:(2)
插入记录:(1x记录大小)
插入索引:(1x索引)
关闭:(1)
表的大小以logN 的速度减慢索引的插入。
使用set bulk_insert_buffer_size=xxx,MyISAM 使用专用树状缓存来使INSERT … SELECT、INSERT … VALUES (…)、(…)、 …和LOAD DATA INFILE的大块插入更快。将它设置为0禁用优化。注释:只有向非空表添加数据时才使用该缓存。 默认值是8MB。
3、当不需要知道何时写入数据时,使用INSERT DELAYED。这样可以加快处理,因为很多记录可以通过一次性写入硬盘。比如记录日志。
注:DELAYED和LOW_PRIORITY的区别是LOW_ PRIORITY强迫客户端等待,直到那些数据行可以被插入数据表。DELAYED则允许客户端继续操作而无须等待。
1、一般来说,如果选择性超过 20% 那么全表扫描比使用索引性能更优。比如性别字段。也就是说如果字段值的可能性小于6个的话,是不用建立索引的。
最左前缀的特性:比如我们建立(filed_1,filed_2,filed_3)的索引的话,相当于我们建立了创建了(filed_1,filed_2,filed_3)、(filed_1,filed_2)以及(filed_1)这三种索引
有关EXPLAIN参数的说明
EXPLAIN列的解释:
- mysql 性能优化方案
- mysql 性能优化方案
- mysql 性能优化方案
- mysql优化方案
- mysql 性能优化方案
- mysql 性能优化方案
- mysql 性能优化方案
- mysql 性能优化方案
- mysql 性能优化方案
- mysql 性能优化方案
- MySQL limit 优化方案
- MYSQL - 性能优化方案
- mysql 性能优化方案
- mysql 性能优化方案
- mysql 性能优化方案
- mysql 性能优化方案
- mysql 性能优化方案
- mysql优化方案
- 几个漂亮按钮样式
- 明星软件工程师的10种特质
- 弹出窗口总结
- asp.net 关闭网页代码
- volatile
- mysql优化方案
- ListView 自定义item布局 android.widget.LinearLayout$LayoutParams
- 多国语言字库原理
- 数据绑定页面判断显示长度其余用点代替
- 实验之余,我是如何淘金的?[小木虫]
- Doxygen 配置学习笔记
- 几则故事
- Oracle行列转换
- shell中数组的使用