MySQL基础知识

MySQL基础知识,入门知识

系统性能相关的MySQL变量

系统性能相关的MySQL变量

原文摘录自:http://forge.mysql.com/wiki/ServerVariables

Memory-Related Variables
内存相关变量

These server variables control the amount of memory allocated to the various buffers and caches within MySQL.
以下这些服务器变量控制这MySQL分配给各种缓冲或者换缓存的内存总数。

join_buffer_size
(PER SESSION) Controls the amount of memory allocated to perform joins on tables that have no keys which can be used to perform a condition filter. Allocated for each table joined without necessary filter conditions

FreeBSD 4.0 Release上编译MySQL通不过的解决方法

原文出处: http://www.chinabsd.com/2005/11/freebsd_40_releasemysql.html,版权归原著者(坠落,飞翔)所有。

在清华BBS上看到有些朋友在 FreeBSD 4.0 Release上编译MySQL时通不过,停留在编译sql/sql_yacc.cc文件处,很长时间都通不过,有网友说编译了三个多小时都通不过,我真的很佩服他的耐心了。我也遇到了同样的问题,还有过错误的判断。通过与清华BBS的网友交流,我相信找到了问题 所在。
有网友说用ports安装就没有什么问题,但并没有进一步说明 到底是因为什么。看了一下ports中对mysql-server的说明,原来用ports编译mysql需要 一个包:libtool-1.3.3。
请看FreeBSD对libtool这个包的描述
This is GNU Libtool, a generic library support script. Libtool hides the complexity of using shared libraries behind a consistent, portable interface.

技术相关: 

MySQL的技术架构介绍

原文来自塞迪网

金璞:各位网友大家好!我是赛迪网技术应用编辑金璞,今天本来要来的David Axmark先生和周总现在正在路上,预计可能和迟一点跟网友们见面现在我们请陈慧女士做一个自我介绍。

陈慧:我是万里开源的系统工程师陈慧,很高兴作客赛迪网。

金璞:因为David Axmark和周总还没有来,前天的时候MySQL在中国研发中心成立的时候,我当时听到您做了一个演讲,也讲了MySQL技术上的架构包括以后的发展方向之类的。今天先跟网友们讲一讲吧。

陈慧:我们万里开源是MySQL在中国唯一的代理,我们是基于Linux的平台进行发展,基于Linux开源软件的提供商。大家比较熟悉的是TurboLinux,我们这次跟MySQL的合作希望一方面向先进国家比较先进的开源软件学习一些先进经验,希望通过跟他们的合作建立一个更完善的企业级的服务器平台。

技术相关: 

派生表和视图的性能

Starting MySQL 4.1, MySQL had support for what is called derived tables, inline views or basically subselects in the from clause.
In MySQL 5.0 support for views was added.

从MySQL 4.1开始,它已经支持派生表、联机视图或者基本的FROM从句的子查询。

These features are quite related to each other but how do they compare in terms of performance ?

这些特性之间彼此相关,但是它们之间的性能比较如何呢?

Derived Tables in MySQL 5.0 seems to have different implementation from views, even though I would expect code base to be merged as it is quite the same task in terms of query optimization.

技术相关: 

MySQL备份和同步时使用LVM

If someone asks me about MySQL Backup advice my first question would be if they have LVM installed or have some systems with similar features set for other operation systems. Veritas File System can do it for Solaris. Most SAN systems would work as well.

如果有人问我关于MySQL备份的建议,我会先问操作系统是否安装了LVM或者其他相似功能的软件。Solaris下的Veritas文件系统也可以做到同样的功能。大多数SAN系统也可以。

What is really needed is ability to create atomic snapshot of the volume, which can be later mounted same as original file system

技术相关: 

MySQL Cluster(MySQL 集群) 初试

MySQL Cluster 是MySQL适合于分布式计算环境的高实用、高冗余版本。它采用了NDB Cluster 存储引擎,允许在1个 Cluster 中运行多个MySQL服务器。在MyQL 5.0及以上的二进制版本中、以及与最新的Linux版本兼容的RPM中提供了该存储引擎。(注意,要想获得MySQL Cluster 的功能,必须安装 mysql-server 和 mysql-max RPM)。
目前能够运行MySQL Cluster 的操作系统有Linux、Mac OS X和Solaris(一些用户通报成功地在FreeBSD上运行了MySQL Cluster ,但MySQL AB公司尚未正式支持该特性)。
一、MySQL Cluster概述

降序索引和减轻索引扫描

Descending indexing and loose index scan
降序索引和减轻索引扫描

Comments to my previous posts, especially this one by Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support. None of these are actially related to Innodb tables in general - these are features MySQL should get for all storage engines at some point.

MySQL 存储过程的字符集问题

由于存储过程中定义参数时,无法定义其字符集,因此调用存储过程的时候,会默认读取全局变量character_set_server,而且还是只读取mysqld启动时该全局变量的值作为存储过程中默认的传输字符集。因此,如果数据表/字段使用系统默认的字符集(比如latin1)的话,调用存储过程更新一些非英文的字符串字段时,就不会发生问题;但是,如果数据表/字段的字符集不是系统默认的字符集(比如默认是latin1,数据表使用的是utf8),就会出现问题了。解决办法是:在my.cnf(windows下是my.ini),增加一行:

default-character-set=utf8

或者

MySQL使用tips

  • 用mysql内置函数转换ip地址和数字
    利用两个内置函数
    inet_aton:将ip地址转换成数字型
    inet_ntoa:将数字型转换成ip地址

  • 充分利用mysql内置的format函数
    尤其是在处理字符格式的时候,例如将12345转换成12,345这样的,只要用:format(12345,0)即可,如果用format(12345,2)则显示的是12,345.00了...

  • 利用mysql的内置函数处理时间戳问题
    eg : select FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');
    结果: 2004 3rd August 03:35:48 2004

  • 利用mysql_convert_table_format转换表类型
    需要DBI和DBD的mysql相关模块支持才能用,例子:
    mysql_convert_table_format --user=root --password='xx' --type=myisam test yejr

技术相关: 

Improving Database Performance with Partitioning

come from:http://www.mysql.com

A few years ago, I wrote an article entitled "The Foundation of Excellent Performance" (still available at http://www.tdan.com/i016fe03.htm) where I argued against the notion that SQL code was the number one contributor to performance in a database-driven system. Instead, I stated in the article that I firmly believed how good physical database design was far and away the leading component of superior database performance. In addition, I showed that Oracle's own research illustrated how poor design was the main culprit behind database downtime (planned or unplanned). In the years since then, I've not changed my stance and still think that any DBA who wants a high-performance database has got to invest in intelligent and savvy physical design to produce the kind of response times that make end users smile instead of scream.

技术相关: 

页面

Subscribe to RSS - MySQL基础知识