Category Archives: MySQL

MYSQL分页存储过程及事务处理

最近给客户做的一小系统是SQLSERVER的数据库，因为特殊原因要切换到MYSQL上去，切换数据库确实让人头疼的，SQLSERVER和MYSQL的存储过程还是有很大差别的，下面是我做切换时转换的MYSQL版的分页过程，和事务处理的一个测试过程，事务处理也不像SQLSERVER那样。不多说了，供学习MYSQL的兄弟们参考下，我用的MYSQL5.5版本，经过测试都是可行的。  

from:http://www.cnblogs.com/peaceli/archive/2013/08/14/MYSQL.html

MariaDB 存储过程返回 汉字有关问题

MariaDB 存储过程返回 汉字问题创建代码如下：CREATE DEFINER=root@localhost PROCEDURE test(IN id INT, OUT result VARCHAR(50))LANGUAGE SQLNOT DETERMINISTICCONTAINS SQLSQL SECURITY DEFINERCOMMENT "BEGIN  if id = 1 then  set result = '红字';  else  set result = 'ghj';     end if;ENDset names utf8;set @a=";  CALL test(0, @a);select @a;没有问题；set names utf8;set @a=";  CALL test(1, @a);select @a;报错！运行总是无法成功返回汉字，郁闷——解决方案——————--create procedure t ( aa char(10) charset 'gbk') from:http://www.myexception.cn/mysql/744652.html

第18章：分区

本章讨论MySQL 5.1.中实现的分区。关于分区和分区概念的介绍可以在18.1节，“MySQL中的分区概述”中找到。MySQL 5.1 支持哪几种类型的分区，在18.2节，“分区类型” 中讨论。关于子分区在18.2.5节，“子分区” 中讨论。现有分区表中分区的增加、删除和修改的方法在18.3节，“分区管理” 中介绍。 和分区表一同使用的表维护命令在18.3.3节，“分区维护” 中介绍。 请注意：MySQL 5.1中的分区实现仍然很新（pre-alpha品质），此时还不是可生产的（not production-ready）。 同样，许多也适用于本章：在这里描述的一些功能还没有实际上实现(分区维护和重新分区命令），其他的可能还没有完全如所描述的那样实现(例如, 用于分区的数据目录（DATA DIRECTORY）和索引目录（INDEX DIRECTORY）选项受到Bug #13520) 不利的影响). 我们已经设法在本章中标出这些差异。在提出缺陷报告前，我们鼓励参考下面的一些资源： MySQL 分区论坛这是一个为对MySQL分区技术感兴趣或用MySQL分区技术做试验提供的官方讨论论坛。来自MySQL 的开发者和其他的人，会在上面发表和更新有关的材料。它由分区开发和文献团队的成员负责监控。 分区缺陷报告已经归档在缺陷系统中的、所有分区缺陷的一个列表，而无论这些缺陷的年限、严重性或当前的状态如何。根据许多规则可以对这些缺陷进行筛选，或者可以从MySQL缺陷系统主页开始，然后查找你特别感兴趣的缺陷。 Mikael Ronström’s BlogMySQL分区体系结构和领先的开发者Mikael Ronström 经常在这里贴关于他研究MySQL 分区和MySQL簇的文章。 PlanetMySQL一个MySQL 新闻网站，它以汇集MySQL相关的网誌为特点，那些使用我的MySQL的人应该对此有兴趣。我们鼓励查看那些研究MySQL分区的人的网誌链接，或者把你自己的网誌加到这些新闻报道中。 MySQL 5.1的二进制版本目前还不可用；但是，可以从BitKeeper知识库中获得源码。要激活分区，需要使用--with-分区选项编译服务器。关于建立MySQL 的更多信息，请参见2.8节，“使用源码分发版安装MySQL”。如果在编译一个激活分区的MySQL 5.1创建中碰到问题，可以在MySQL分区论坛中查找解决办法，如果在论坛中已经贴出的文章中没有找到问题的解决办法，可以在上面寻找帮助。 18.1. MySQL中的分区概述 本节提供了关于MySQL 5.1.分区在概念上的概述。 SQL标准在数据存储的物理方面没有提供太多的指南。SQL语言的使用独立于它所使用的任何数据结构或图表、表、行或列下的介质。但是，大部分高级数据库管理系统已经开发了一些根据文件系统、硬件或者这两者来确定将要用于存储特定数据块物理位置的方法。在MySQL中，InnoDB存储引擎长期支持表空间的概念，并且MySQL服务器甚至在分区引入之前，就能配置为存储不同的数据库使用不同的物理路径(关于如何配置的解释，请参见7.6.1节，“使用符号链接”)。 分区又把这个概念推进了一步，它允许根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数，也不能是任意数。它不能包含任何查询，但是实际上可以使用MySQL 中任何可用的SQL表达式，只要该表达式返回一个小于MAXVALUE（最大可能的正整数）的正数值。分区函数的例子可以在本章后面关于分区类型的讨论中找到 (请参见18.2节，“分区类型” )，也可在13.1.5节，“CREATE TABLE语法”的分区语法描述中找到。 当二进制码变成可用时（也就是说，5.1 -max 二进制码将通过--with-partition 建立），分区支持就将包含在MySQL 5.1的-max 版本中。如果MySQL二进制码是使用分区支持建立的，那么激活它不需要任何其他的东西 (例如，在my.cnf 文件中，不需要特殊的条目)。可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区，例如：

在如上列出的一个正确的SHOW VARIABLES 命令所产生的输出中，如果没有看到变量have_partition_engine的值为YES，那么MySQL的版本就不支持分区。（注意：在显示任何有关分区支持信息的命令SHOW ENGINES的输出中，不会给出任何信息；必须使用SHOW VARIABLES命令来做出这个判断）。 对于创建了分区的表，可以使用你的MySQL 服务器所支持的任何存储引擎；MySQL 分区引擎在一个单独的层中运行，并且可以和任何这样的层进行相互作用。在MySQL 5.1版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用MyISAM，而对另一个使用InnoDB。但是，这并不妨碍在同一个 MySQL 服务器中，甚至在同一个数据库中，对于不同的分区表使用不同的存储引擎。 要为某个分区表配置一个专门的存储引擎，必须且只能使用[STORAGE] ENGINE 选项，这如同为非分区表配置存储引擎一样。但是，必须记住[STORAGE] ENGINE（和其他的表选项）必须列在用在CREATE TABLE语句中的其他任何分区选项之前。下面的例子给出了怎样创建一个通过HASH分成6个分区、使用InnoDB存储引擎的表：

（注释：每个PARTITION 子句可以包含一个 [STORAGE] ENGINE 选项，但是在MySQL 5.1版本中，这没有作用）。 创建分区的临时表也是可能的；但是，这种表的生命周期只有当前MySQL 的会话的时间那么长。对于非分区的临时表，这也是一样的。 注释：分区适用于一个表的所有数据和索引；不能只对数据分区而不对索引分区，反之亦然，同时也不能只对表的一部分进行分区。 可以通过使用用来创建分区表的CREATE TABLE语句的PARTITION子句的DATA DIRECTORY（数据路径）和INDEX DIRECTORY（索引路径）选项，为每个分区的数据和索引指定特定的路径。此外，MAX_ROWS和MIN_ROWS选项可以用来设定最大和最小的行数，它们可以各自保存在每个分区里。关于这些选项的更多信息，请参见18.3节，“分区管理”。注释：这个特殊的功能由于Bug #13250的原因，目前还不能实用。在第一个5.1二进制版本投入使用时，我们应该已经把这个问题解决了。 分区的一些优点包括： ·         与单个磁盘或文件系统分区相比，可以存储更多的数据。 ·         对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。 通常和分区有关的其他优点包括下面列出的这些。MySQL 分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。 ·         一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。 ·         涉及到例如SUM() 和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”， 这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。 ·         通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。 要经常检查本页和本章，因为它将随MySQL 5.1后续的分区进展而更新。 18.2. 分区类型 18.2.1. RANGE分区 18.2.2. LIST分区 18.2.3. HASH分区 18.2.4. KEY分区 18.2.5. 子分区 18.2.6. MySQL分区处理NULL值的方式 本节讨论在MySQL 5.1中可用的分区类型。这些类型包括： ·         RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。参见18.2.1节，“RANGE分区”。 […]

MYSQL表分区

范围分区： CREATE TABLE BIGTABLE ( ID INT, SNPTIME DATETIME NOT NULL, VALUE VARCHAR(20), PRIMARY KEY (SNPTIME, ID) ) ENGINE=InnoDB partition by range (TO_DAYS(SNPTIME)) ( PARTITION p1 VALUES LESS THAN (to_days('2009-1-31')), PARTITION p2 VALUES LESS THAN (to_days('2009-2-28')), PARTITION p3 VALUES LESS THAN (to_days('2008-3-31')), PARTITION p4 VALUES LESS THAN (to_days('2008-4-30')), PARTITION p5 VALUES LESS THAN (to_days('2008-5-31')), PARTITION p6 VALUES LESS THAN (to_days('2008-6-30')), PARTITION p7 VALUES LESS THAN (to_days('2008-7-31')), PARTITION p8 VALUES LESS THAN (to_days('2008-8-31')), PARTITION p9 VALUES LESS THAN (to_days('2008-9-30')), PARTITION p10 VALUES LESS THAN (to_days('2008-10-31')), PARTITION p11 VALUES LESS THAN (to_days('2008-11-30')), PARTITION p12 VALUES LESS THAN (to_days('2008-12-31')), PARTITION p13 VALUES LESS THAN MAXVALUE ) ; 注意一点：一定要有主键，并且主键要包括分区键。 但是，如果必须要分区，而分区中的分区键不想使用（业务不允许）主键的时候，可以采用两步走的办法。 1、建立表，带有主键。 2、删除主键，建立独立索引。 这样在插入数据的时候还是能够按部就班地进入各自所属的分区表。   给已存在的表加分区 ALTER TABLE SNP_SWITCH partition by RANGE (TO_DAYS(RPTTIME)) (PARTITION P1210 VALUES LESS THAN (735172) ENGINE = MYISAM, PARTITION P1211 VALUES LESS THAN (735202) ENGINE = MYISAM, […]

MySQL索引分类和各自用途

一、 MySQL: 索引以B树格式保存 Memory存储引擎可以选择Hash或BTree索引，Hash索引只能用于=或<=>的等式比较。 1、普通索引：create index on Tablename(列的列表) alter table TableName add index (列的列表) create table TableName([…], index [IndexName] (列的列表) 2、唯一性索引：create unique index alter … add unique 主键：一种唯一性索引，必须指定为primary key 3、全文索引：从3.23.23版开始支持全文索引和全文检索，FULLTEXT， 可以在char、varchar或text类型的列上创建。 4、单列索引、多列索引： 多个单列索引与单个多列索引的查询效果不同，因为： 执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。 5、最左前缀(Leftmost Prefixing)：多列索引，例如：fname_lname_age索引，以下的搜索条件MySQL都将使用 fname_lname_age索引：firstname,lastname,age；firstname,lastname；firstname，其他情况将不使用。 二、根据sql查询语句确定创建哪种类型的索引，如何优化查询 选择索引列： a.性能优化过程中，选择在哪个列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有 两种类型的列：在where子句中出现的列，在join子句中出现的列。 b.考虑列中值的分布，索引的列的基数越大，索引的效果越好。 c.使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。 d.利用最左前缀 e.不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。 在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。 MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)。 from:http://database.51cto.com/art/200905/122789.htm

浅谈MySQL存储引擎选择 InnoDB还是MyISAM

MyISAM 是MySQL中默认的存储引擎，一般来说不是有太多人关心这个东西。决定使用什么样的存储引擎是一个很tricky的事情，但是还是值我们去研究一下，这里的文章只考虑 MyISAM 和InnoDB这两个，因为这两个是最常见的。 下面先让我们回答一些问题： ◆你的数据库有外键吗？ ◆你需要事务支持吗？ ◆你需要全文索引吗？ ◆你经常使用什么样的查询模式？ ◆你的数据有多大？ 思考上面这些问题可以让你找到合适的方向，但那并不是绝对的。如果你需要事务处理或是外键，那么InnoDB 可能是比较好的方式。如果你需要全文索引，那么通常来说 MyISAM是好的选择，因为这是系统内建的，然而，我们其实并不会经常地去测试两百万行记录。所以，就算是慢一点，我们可以通过使用Sphinx从InnoDB中获得全文索引。 数据的大小，是一个影响你选择什么样存储引擎的重要因素，大尺寸的数据集趋向于选择InnoDB方式，因为其支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。而MyISAM可能会需要几个小时甚至几天来干这些事，InnoDB只需要几分钟。 您操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如： COUNT() 在 MyISAM 表中会非常快，而在InnoDB 表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快，但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts 语句在MyISAM下会快一些，但是updates 在InnoDB 下会更快一些——尤其在并发量大的时候。 所以，到底你检使用哪一个呢？根据经验来看，如果是一些小型的应用或项目，那么MyISAM 也许会更适合。当然，在大型的环境下使用MyISAM 也会有很大成功的时候，但却不总是这样的。如果你正在计划使用一个超大数据量的项目，而且需要事务处理或外键支持，那么你真的应该直接使用InnoDB方式。但需要记住InnoDB 的表需要更多的内存和存储，转换100GB 的MyISAM 表到InnoDB 表可能会让你有非常坏的体验。   from:http://database.51cto.com/art/200905/122382.htm

通过分区（Partition）提升MySQL性能

什么是数据库分区？ 数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。 分区主要有两种形式：//这里一定要注意行和列的概念（row是行，column是列） 1. 水平分区（Horizontal Partitioning）这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。 举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。（朋奕注：这里具体使用的分区方式我们后面再说，可以先说一点，一定要通过某个属性列来分割，譬如这里使用的列就是年份） 2. 垂直分区（Vertical Partitioning） 这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列 被划分到特定的分区，每个分区都包含了其中的列所对应的行。 举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。 在数据库供应商开始在他们的数据库引擎中建立分区（主要是水平分区）时，DBA和建模者必须设计好表的物理分区结构，不要保存冗余的数据（不同表中同时都包含父表中的数据）或相互联结成一个逻辑父对象（通常是视图）。这种做法会使水平分区的大部分功能失效，有时候也会对垂直分区产生影响。 在MySQL 5.1中进行分区 MySQL5.1中最激动人心的新特性应该就是对水平分区的支持了。这对MySQL的使用者来说确实是个好消息，而且她已经支持分区大部分模式： Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980’s）的数据，90年代（1990’s）的数据以及任何在2000年（包括2000年）后的数据。 Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。 Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。 List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。 Composite（复合模式） – 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。 分区带来的好处太多太多了，有多少？俺也不知道，自己猜去吧，要是觉得没有多少就别用，反正俺也不求你用。不过在这里俺强调两点好处： 性能的提升（Increased performance） – 在扫描操作中，如果MySQL的优化器知道哪个分区中才包含特定查询中需要的数据，它就能直接去扫描那些分区的数据，而不用浪费很多时间扫描不需要的地方了。需要举个例子？好啊，百万行的表划分为10个分区，每个分区就包含十万行数据，那么查询分区需要的时间仅仅是全表扫描的十分之一了，很明显的对比。同时对十万行的表建立索引的速度也会比百万行的快得多得多。如果你能把这些分区建立在不同的磁盘上，这时候的I/O读写速度就“不堪设想”（没用错词，真的太快了，理论上100倍的速度提升啊，这是多么快的响应速度啊，所以有点不堪设想了）了。 对数据管理的简化（Simplified data management） – 分区技术可以让DBA对数据的管理能力提升。通过优良的分区，DBA可以简化特定数据操作的执行方式。例如：DBA在对某些分区的内容进行删除的同时能保证余下的分区的数据完整性(这是跟对表的数据删除这种大动作做比较的)。 此外分区是由MySQL系统直接管理的，DBA不需要手工的去划分和维护。例如：这个例如没意思，不讲了，如果你是DBA，只要你划分了分区，以后你就不用管了就是了。 站在性能设计的观点上，俺们对以上的内容也是相当感兴趣滴。通过使用分区和对不同的SQL操作的匹配设计，数据库的性能一定能获得巨大提升。下面咱们一起用用这个MySQL 5.1的新功能看看。 下面所有的测试都在Dell Optiplex box with a Pentium 4 3.00GHz processor, 1GB of RAM机器上（炫耀啊……），Fedora Core 4和MySQL 5.1.6 alpha上运行通过。 如何进行实际分区 看看分区的实际效果吧。我们建立几个同样的MyISAM引擎的表，包含日期敏感的数据，但只对其中一个分区。分区的表（表名为part_tab）我们采用Range范围分区模式，通过年份进行分区： mysql> CREATE TABLE part_tab -> ( c1 int default NULL, -> c2 varchar(30) default NULL, -> c3 date default NULL -> -> ) […]

MySQL分区(mysql partition)

一、概述 相信有很多人经常会问同样的一个问题：当 MySQL 的总记录数超过了100万后，会出现性能的大幅度下降吗？答案是肯定的，但是性能下降>的比率不一而同，要看系统的架构、应用程序、还有>包括索引、服务器硬件等多种因素而定。当有网友问我这个问题的时候，我最常见的回答>就是：分表，可以根据id区间或者时间先后顺序等多 种规则来分表。分表很容易，然而由此所带来的应用程序甚至是架构方面的改动工作却不>容小觑，还包括将来的扩展性等。 在以前，一种解决方案就是使用 MERGE 类型，这是一个非常方便的做饭。架构和程序基本上不用做改动，不过，它的缺点是显见的： 只能在相同结构的 MyISAM 表上使用 无法享受到 MyISAM 的全部功能，例如无法在 MERGE 类型上执行 FULLTEXT 搜索 它需要使用更多的文件描述符 读取索引更慢 这个时候，MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了： 与单个磁盘或文件系统分区相比，可以存储更多的数据 很容易就能删除不用或者过时的数据 一些查询可以得到极大的优化 涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行 IO吞吐量更大 分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。 二、分区的类型 RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。参见18.2.1节，RANGE分区 LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。参见18.2.2节，LIST分区 HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。参见18.2.3节，HASH分区 KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。 参见18.2.4节，KEY分区 三、分区例子： RANGE 类型 CREATE TABLE users (      uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,      name VARCHAR(30) NOT NULL DEFAULT ",      email VARCHAR(30) NOT NULL DEFAULT " ) PARTITION BY RANGE (uid) (      PARTITION p0 VALUES LESS THAN (3000000)      DATA DIRECTORY = '/data0/data' […]

mysql查看数据库表的数量命令

有时候导入SQL文件后，表很多。想看看表的个数时候和服务器上表的个数是否相同。 在mysql中可以用如下命令查看表的个数： SELECT count(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA=’dbname'; from:http://www.2cto.com/database/201403/287813.html