七、MySQL数据库Schema设计的性能优化 高效的模型设计 适度冗余-让Query尽两减少Join 大字段垂直分拆-summary表优化 大表水平分拆-基于类型的分拆优化 统计表-准实时优化 合适的数据类型 时间存储格式总类并不是太多，我们常用的主要就是DATETIME，DATE和TIMESTAMP这三种了。从存储空间来看TIMESTAMP最少，四个字节，而其他两种数据类型都是八个字节，多了一倍。而TIMESTAMP的缺点在于他只能存储从1970年之后的时间，而另外两种时间类型可以存放最早从1001年开始的时间。如果有需要存放早于1970年之前的时间的需求，我们必须放弃TIMESTAMP类型，但是只要我们不需要使用1970年之前的时间，最好尽量使用TIMESTAMP来减少存储空间的占用。 字符存储类型 CHAR[(M)]类型属于静态长度类型，存放长度完全以字符数来计算，所以最终的存储长度是基于字符集的，如latin1则最大存储长度为255字节，但是如果使用gbk则最大存储长度为510字节。CHAR类型的存储特点是不管我们实际存放多长数据，在数据库中都会存放M个字符，不够的通过空格补上，M默认为1。虽然CHAR会通过空格补齐存放的空间，但是在访问数据的时候，MySQL会忽略最后的所有空格，所以如果我们的实际数据中如果在最后确实需要空格，则不能使用CHAR类型来存放。 VARCHAR[(M)]属于动态存储长度类型，仅存占用实际存储数据的长度。TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT这四种类型同属于一种存储方式，都是动态存储长度类型，不同的仅仅是最大长度的限制。 事务优化 1. 脏读:脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。 2. 不可重复读:是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。 3. 幻读:是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。 Innodb在事务隔离级别方面支持的信息如下: 1.READ UNCOMMITTED 常被成为Dirty Reads(脏读)，可以说是事务上的最低隔离级别:在普通的非锁定模式下SELECT的执行使我们看到的数据可能并不是查询发起时间点的数据，因而在这个隔离度下是非Consistent Reads(一致性读); 2.READ COMMITTED 这一隔离级别下，不会出现DirtyRead，但是可能出现Non-RepeatableReads(不可重复读)和PhantomReads(幻读)。 3. REPEATABLE READ REPEATABLE READ隔离级别是InnoDB默认的事务隔离级。在REPEATABLE READ隔离级别下，不会出现DirtyReads，也不会出现Non-Repeatable Read，但是仍然存在PhantomReads的可能性。 4.SERIALIZABLE SERIALIZABLE隔离级别是标准事务隔离级别中的最高级别。设置为SERIALIZABLE隔离级别之后，在事务中的任何时候所看到的数据都是事务启动时刻的状态，不论在这期间有没有其他事务已经修改了某些数据并提交。所以，SERIALIZABLE事务隔离级别下，PhantomReads也不会出现。 八、可扩展性设计之数据切分 数据的垂直切分 数据的垂直切分，也可以称之为纵向切分。将数据库想象成为由很多个一大块一大块的“数据块”(表)组成，我们垂直的将这些“数据块”切开，然后将他们分散到多台数据库主机上面。这样的切分方法就是一个垂直(纵向)的数据切分。 垂直切分的优点 ◆数据库的拆分简单明了，拆分规则明确; ◆应用程序模块清晰明确，整合容易; ◆数据维护方便易行，容易定位; 垂直切分的缺点 ◆部分表关联无法在数据库级别完成，需要在程序中完成; ◆对于访问极其频繁且数据量超大的表仍然存在性能平静，不一定能满足要求; ◆事务处理相对更为复杂; ◆切分达到一定程度之后，扩展性会遇到限制; ◆过读切分可能会带来系统过渡复杂而难以维护。 数据的水平切分 数据的垂直切分基本上可以简单的理解为按照表按照模块来切分数据，而水平切分就不再是按照表或者是功能模块来切分了。一般来说，简单的水平切分主要是将某个访问极其平凡的表再按照某个字段的某种规则来分散到多个表之中，每个表中包含一部分数据。 水平切分的优点 ◆表关联基本能够在数据库端全部完成; ◆不会存在某些超大型数据量和高负载的表遇到瓶颈的问题; ◆应用程序端整体架构改动相对较少; ◆事务处理相对简单; ◆只要切分规则能够定义好，基本上较难遇到扩展性限制; 水平切分的缺点 ◆切分规则相对更为复杂，很难抽象出一个能够满足整个数据库的切分规则; ◆后期数据的维护难度有所增加，人为手工定位数据更困难; ◆应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。 数据切分与整合中可能存在的问题 1.引入分布式事务的问题 完全可以将一个跨多个数据库的分布式事务分拆成多个仅处于单个数据库上面的小事务，并通过应用程序来总控各个小事务。当然，这样作的要求就是我们的俄应用程序必须要有足够的健壮性，当然也会给应用程序带来一些技术难度。 2.跨节点Join的问题 推荐通过应用程序来进行处理，先在驱动表所在的MySQLServer中取出相应的驱动结果集，然后根据驱动结果集再到被驱动表所在的MySQL Server中取出相应的数据。 3.跨节点合并排序分页问题 从多个数据源并行的取数据，然后应用程序汇总处理。 九、可扩展性设计之Cache与Search的利用 通过引入Cache(Redis、Memcached)，减少数据库的访问，增加性能。 通过引入Search(Lucene、Solr、ElasticSearch)，利用搜索引擎高效的全文索引和分词算法，以及高效的数据检索实现，来解决数据库和传统的Cache软件完全无法解决的全文模糊搜索、分类统计查询等功能。 以上就是本文的全部内容，希望大家可以喜欢。 from:http://www.jb51.net/article/70530.htm
View Details数据库操作是当今 Web 应用程序中的主要瓶颈。 不仅是 DBA(数据库管理员)需要为各种性能问题操心，程序员为做出准确的结构化表，优化查询性能和编写更优代码，也要费尽心思。 在本文中，我列出了一些针对程序员的 MySQL 优化技术。 在我们开始学习之前，我补充一点:你可以在 Envato Market 上找到大量的 MySQL 脚本和实用程序。 1.优化查询的查询缓存 大部分MySQL服务器都有查询缓存功能。这是提高性能的最有效的方法之一，这是由数据库引擎私下处理的。当同一个查询被多次执行，结果会直接从缓存里提取，这样速度就很快。 主要的问题是，这对程序员来说太简单了，不容易看到，我们很多人都容易忽略。我们实际上是可以组织查询缓存执行任务的。 ? 1 2 3 4 5 6 // query cache does NOT work $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); // query cache works! $today = date("Y-m-d"); $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'"); 查询缓存在第一行不执行的原因在于CURDTE()功能的使用。这适用于所有的非确定性功能，就像NOW()和RAND()等等。。。因为功能返回的结果是可变的。MySQL决定禁用查询器的查询缓存。我们所需要做的是通过添加一额外一行PHP，在查询前阻止它发生。 2. EXPLAIN你的选择查询 使用EXPLAIN关键词可以帮助了解MySQL是怎样运行你的查询的。这有助于发现瓶颈和查询或表结构的其它问题。 EXPLAIN的查询结果会展示哪一个索引被使用过，表示怎样扫描和储存的，等等。。。 选择一个SELECT查询(一个有连接的复杂查询会更好)，在它的前面添加关键词EXPLAIN，这样就可以直接使用数据库了。结果会以一个漂亮的表来展示。例如，就好比我执行连接时忘了添加一栏的索引: 现在它只会从表2里面扫描9和16行，而非扫描7883行。经验法则是乘以所有“行”那一栏的数字，你的查询性能会跟结果数字成比例的。 3. 获取唯一行时使用LIMIT 1 有时当你查表时，你已经知道你正在查找的结果只有一行。你可能正在获取唯一记录，或者你可能只是查询是否存在满足你的WHERE子句条件的记录。 在这种情况下，将LIMIT 1添加到查询条件中可以提高性能。这样，数据库引擎将在找到刚刚第一个记录之后停止扫描记录，而不是遍历整个表或索引。 ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 // do I have any users from Alabama? // what NOT to do: $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'"); if(mysql_num_rows($r) > 0) { // … } // much better: $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1"); if(mysql_num_rows($r) > 0) { // … } 4. 索引搜索字段 索引不仅仅是为了主键或唯一键。如果你会在你的表中按照任何列搜索，你就都应该索引它们。 正如你所看到的，这个规则也适用于如 last_name LIKE a%' 的部分字符串搜索。当从字符串的开头搜索时，MySQL就可以使用那一列的索引。 你也应该明白什么样搜索可以不使用有规律的索引。例如，当搜索一个单词时(例如， WHERE post_content LIKE %apple%' […]
View Details最近遇到这样的问题,在MySQL的存储过程中,游标操作时,需要执行一个conitnue的操作.众所周知,MySQL中的游标循环操作常用的有三种,LOOP,REPEAT,WHILE.三种循环,方式大同小异.以前从没用过,所以记下来,方便以后查阅. 1.REPEAT 复制代码代码如下: REPEAT Statements; UNTIL expression END REPEAT demo DECLARE num INT; DECLARE my_string VARCHAR(255); REPEAT SET my_string =CONCAT(my_string,num, , ); SET num = num +1; UNTIL num <5 END REPEAT; 2.WHILE 复制代码代码如下: WHILE expression DO Statements; END WHILE demo DECLARE num INT; DECLARE my_string VARCHAR(255); SET num =1; SET str = ; WHILE num < span>10DO SET my_string =CONCAT(my_string,num, , ); SET num = num +1; END WHILE; 3.LOOP(这里面有非常重要的ITERATE,LEAVE) 复制代码代码如下: DECLARE num INT; DECLARE str VARCHAR(255); SET num =1; SET my_string = ; loop_label: LOOP IF […]
View DetailsMySQL中 concat 函数 使用方法: CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。 注意: 如果所有参数均为非二进制字符串，则结果为非二进制字符串。 如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。 一个数字参数被转化为与之相等的二进制字符串格式;若要避免这种情况，可使用显式类型 cast, 例如: SELECT CONCAT(CAST(int_col AS CHAR), char_col) MySQL的concat函数可以连接一个或者多个字符串,如 mysql> select concat( 10 ); + + | concat( 10 ) | + + | 10 | + + 1 row in set (0.00 sec) mysql> select concat( 11 , 22 , 33 ); + + | concat( 11 , 22 , 33 ) | + + | 112233 | + + 1 row in set (0.00 sec) MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL mysql> select concat( 11 , 22 ,null); + + […]
View Details做项目，需要测试数据，而现成的其他表有类似数据，为了方便，就将想将查询结果直接放到新表里。具体操作如下: INSERT INTO TableA(c1,c2,c3) SELECT TableB.c1,TableB.c2,TableB.c3 FROM TableB 参考:http://wenda.so.com/q/1372470880061763 如果表存在: insert into tab1 select * from tab2; 如果表不存在: create table tab1 as select * from tab2; from:http://blog.csdn.net/chark_leo/article/details/39525633
View DetailsMySQL 的官网下载地址：http://www.mysql.com/downloads/ 在这个下载界面会有几个版本的选择。 1. MySQL Community Server 社区版本，开源免费，但不提供官方技术支持。 2. MySQL Enterprise Edition 企业版本，需付费，可以试用30天。 3. MySQL Cluster 集群版，开源免费。可将几个MySQL Server封装成一个Server。 4. MySQL Cluster CGE 高级集群版，需付费。 5. MySQL Workbench（GUITOOL）一款专为MySQL设计的ER/数据库建模工具。它是著名的数据库设计工具DBDesigner4的继任者。MySQLWorkbench又分为两个版本，分别是社区版（MySQL Workbench OSS）、商用版（MySQL WorkbenchSE）。 MySQL Community Server是开源免费的，这也是我们通常用的MySQL的版本。根据不同的操作系统平台细分为多个版本，下面我们以windows平台为例来说明。 例如下载mysql5.5.19的界面如下： 说明： 我们选择Generally Available（GA）Release 去下载。GA是指软件的通用版本，一般指正式发布的版本。 mysql-5.5.19-win32.msi，windows安装包，msi安装包是用msiexec安装完成的。windows下双击根据向导安装即可，简单方便。 mysql-5.5.19.zip，这个是windows源文件，需要编译。 mysql-5.5.19-win32.zip，这个文件解包后即可使用，是编译好的windows32位MySQL。需要手工配置。 带 "winx64" 的则是对应的64位版本，在64位操作系统下使用。 一般做后台开发，我们就下载mysql-5.5.19-win32.msi。 例如下载mysql5.1.60的界面如下： 说明： 选择Generally Available（GA）Release 去下载。GA 是指软件的通用版本，一般指正式发布的版本。 “essentials” 是指精简版，不包含 embedded server and benchmarksuite，有自动安装程序和配置向导，没有MySQL文档。 “noinstall” 是指非安装的压缩包的。包含 embedded server and benchmarksuite，没有自动安装程序和配置向导，需手动安装配置，有MySQL文档。 mysql-essential-5.1.60-win32.msi 是精简版，如果只需要mysql服务，就选择此版本。 mysql-5.1.60-win32.msi 是完整版，包含安装程序和配置向导，有MySQL文档。 mysql-noinstall-5.1.60-win32.zip是非安装的zip压缩包，没有自动安装程序和配置向导，需手动安装配置，有MySQL文档。 mysql-5.1.60.zip 是用于windows的Mysql源码压缩包 带 "winx64" 的则是对应的64位版本，在64位操作系统下使用。 一般做后台开发，我们就下载mysql-essential-5.1.60-win32.msi 即可。 什么是MySQL企业版（MySQL Enterprise）？ MySQL企业版是一个已被证明和值得信赖的平台，这个平台包含了MySQL企业级数据库软件,、监控与咨询服务，以及确保您的业务达到最高水平的可靠性、安全性和实时性的技术支持。 MySQL企业版包括： MySQL企业级服务器，这是全球最流行的开源数据库最可靠、最安全的最新版本。 MySQL企业级系统监控工具，它可以提供监控和自动顾问服务，以此来帮助您消除安全上的隐患、改进复制、优化性能等。 MySQL技术支持，可以使您最棘手的技术问题得到快速解答。 MySQL咨询支持，只有购买了MySQL企业级银质或金质服务的客户才能得到此项支持。MySQL技术支持团队将为您的系统提供针对性的建议，告诉您如何恰当地设计和调整您的MySQL服务器、计划、查询和复制设定，以获得更好的性能。 什么是MySQL社 区版（MySQL Community Server）？ MySQL公司一直专注于向开源社区发布全球最流行的开源数据库——MySQL […]
View Details1.使用show binlog events 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 a.获取binlog文件列表 mysql> show binary logs; +—————————+—————–+ | Log_name | File_size | +—————————+—————–+ | mysql–bin.000005 | 1288 | | mysql–bin.000006 | 120 | +—————————+—————–+ mysql> b.查看当前正在写入的binlog文件 mysql> show master status\G; *************************** […]
View Detailsmysql默认root用户没有密码，输入mysql –u root 进入mysql 1、初始化root密码 进入mysql数据库 1 mysql>update user set password=PASSWORD(‘123456’) where User=’root'; 2、允许mysql远程访问,可以使用以下三种方式: a、改表。 1 2 3 4 mysql -u root –p mysql>use mysql; mysql>update user set host = '%' where user = 'root'; mysql>select host, user from user; b、授权。 例如，你想root使用123456从任何主机连接到mysql服务器。 1 mysql>GRANT ALL PRIVILEGES ON *.* TO 'root’@’%' IDENTIFIED BY '123456' WITH GRANT OPTION; 如果你想允许用户jack从ip为10.10.50.127的主机连接到mysql服务器，并使用654321作为密码 1 2 mysql>GRANT ALL PRIVILEGES ON *.* TO 'jack’@’10.10.50.127’ IDENTIFIED BY '654321'WITH GRANT OPTION; mysql>FLUSH RIVILEGES c:在安装mysql的机器上运行: 1 2 3 4 5 6 7 8 //进入MySQL服务器 d:\mysql\bin\>mysql -h localhost -u root […]
View Details一:实验环境 已搭建好MySQL cluster(一个管理节点，2个sql节点及2个数据节点),搭建步骤请参考:http://blog.csdn.net/yabingshi_tech/article/details/47952135，现要在此基础上添加 192.168.6.70,192.168.6.71作为数据节点。(由于副本集参数已经配置成了2，所以必须一次添加2个数据节点) 二:实验步骤 2.1:解压cluster安装包 tar -xvf mysql-cluster-gpl-7.2.15-linux2.6-x86_64.tar.gz mv mysql-cluster-gpl-7.2.15-linux2.6-x86_64 /usr/local/mysql 2.2:修改管理节点配置文件 cd /data/mysql/mysql-cluster 修改全局配置文件vi mgmd.cnf，修改后的配置文件如下: [plain] view plain copy [ndbd default] NoOfReplicas=2 DataMemory=80M IndexMemory=18M [ndb_mgmd] hostname=192.168.6.66 datadir=/data/mysql/mysql-cluster [ndbd] hostname=192.168.6.68 datadir=/usr/local/mysql/data [ndbd] hostname=192.168.6.69 datadir=/usr/local/mysql/data [ndbd] hostname=192.168.6.70 datadir=/usr/local/mysql/data [ndbd] hostname=192.168.6.71 datadir=/usr/local/mysql/data [mysqld] hostname=192.168.6.66 [mysqld] hostname=192.168.6.67 2.3:重新启动管理节点服务 2.3.1 停止管理节点 ndb_mgm> 1 stop Node 1 has shutdown. Disconnecting to allow Management Server to shutdown 停止管理节点不影响sql节点的正常访问。 2.3.2 重新加载配置文件 [root@ser6-66 mysql-cluster]# ndb_mgmd -f mgmd.cnf initial MySQL Cluster Management Server mysql-5.5.35 ndb-7.2.15 /* 用initial或者reload参数重新加载 如果只在配置文件中新增一个数据节点，会报错: [root@ser6-66 mysql-cluster]# ndb_mgmd -f mgmd.cnf initial MySQL Cluster Management Server mysql-5.5.35 ndb-7.2.15 2015-08-18 16:18:37 [MgmtSrvr] ERROR at line 38: Nodegroup 1 has 1 members, NoOfReplicas=2 2015-08-18 16:18:37 [MgmtSrvr] ERROR Could not load configuration from mgmd.cnf */ 2.3.4 查看集群状态 [plain] view plain copy [root@ser6-66 mysql-cluster]# ndb_mgm 192.168.6.66 NDB Cluster Management Client ndb_mgm> show Connected to Management Server at: 192.168.6.66:1186 Cluster Configuration [ndbd(NDB)]4 node(s) id=2@192.168.6.68 (mysql-5.5.35 ndb-7.2.15, Nodegroup: 0) id=3@192.168.6.69 (mysql-5.5.35 ndb-7.2.15, Nodegroup: 0, *) id=4 (not connected, accepting connect from 192.168.6.70) id=5 (not connected, accepting connect from 192.168.6.71) [ndb_mgmd(MGM)]1 node(s) id=1@192.168.6.66 (mysql-5.5.35 ndb-7.2.15) […]
View Details一、定义MySQL Cluster的TCP/IP连接 TCP/IP是MySQL集群用于建立连接的默认传输协议，正常情况下不需要定义连接。可使用“[TCP DEFAULT]”或“[TCP]”进行定义。 1. SendBufferMemory TCP传输缓存。默认值为 256KB。 2. SendSignalId 通过网络传输消息ID。默认禁止该特性(取值: Y/N或1/0)。 3. Checksum 启用该参数将在所有消息置于发送缓冲之前，为所有参数计算校验和。默认禁止该特性(取值: Y/N或1/0)。 4. ReceiveBufferMemory 指定从TCP/IP Socket接收数据时所使用的缓冲大小。几乎不需要更改该参数的默认值，默认值为64KB。 二、定义数据结点默认行为 NoOfReplicas为必要参数，使用“[NDBD DEFAULT]”进行定义。 1. NoOfReplicas 定义集群中每个表保存的拷贝数，另外还指定结点组的大小。结点组指保存相同信息的结点集合。通常情况下不需要为该参数指定值。NoOfReplicas没有默认值，最大的可能值为 4。 三、定义管理服务器(MGM) 用于配置管理服务器的行为。下面的参数均可以被忽略，如果是这样，将使用其默认值。如果没有定义ExecuteOnComputer或HostName，则会指定为localhost。可使用“[NDB_MGMD]”定义单个管理结点的行为，也可使用“[NDB_MGMD DEFAULT]”定义多个管理结点的默认行为。 1. NodeId 集群中结点的唯一标识，取值 1~63。 2. HostName 指定结点主机名或IP。 3. ExecuteOnComputer 引用在“[COMPUTER]”部分中定义的计算机之一。 4. PortNumber 管理服务器监听端口(默认值: 2202)。 5. LogDestination 结点日志出处理方式，可取下述值: 5.1. CONSOLE 将日志输出到标准输出设备(stdout)。 5.2. SYSLOG:facility=syslog 将日志发送到syslog(系统日志)软设备，可能值: auth, authpriv, cron, daemon, ftp, kern, lpr, mail, news, syslog , user, uucp, local0, local12~7 5.3. FILE:filename=/var/log/mgmd.log,maxsize=1000000,maxfiles=6 讲日志输出到文件,可指定一下值: filename:日志文件名称。 maxsize:日志文件最大尺寸,大于该尺寸时自动创建新日志文件。 maxfiles:日志文件最大数量。 6. ArbitrationRank 指定哪个结点扮演决策角色，只有MGM结点和SQL结点可以使用(默认值:1)。通常情况下，应将值设为 1，并将所有SQL结点设为 0，以MGM服务器作为决策程序。可取下述值之一: 0:该结点永远不用作决策。 1:该结点具有高优先级。 2:该结点具有低有限级。 7. ArbitrationDelay 指定管理服务器对决策请求的延迟时间，毫秒为单位，默认为 0。通常情况下不需要改变它。 8. DataDir […]
View Details