基于ThinkPHP框架MySQL事务的使用

一个事务是一个连续的一组数据库操作,就好像它是一个单一的工作单元进行。换言之,永远不会是完整的事务,除非该组内的每个单独的操作是成功的。如果在事务的任何操作失败,则整个事务将失败。

实际上,很多项目的连贯操作都需要依赖事务来保证数据的完整性。将许多SQL查询到一个组中,将执行所有的人都一起作为事务的一部分。


事务的特性: 

事务有以下四个标准属性的缩写ACID,通常被称为:


原子性: 确保工作单元内的所有操作都成功完成,否则事务将被中止在故障点,和以前的操作将回滚到以前的状态。


一致性: 确保数据库正确地改变状态后,成功提交的事务。


隔离性: 使事务操作彼此独立的和透明的。


持久性: 确保提交的事务的结果或效果的系统出现故障的情况下仍然存在。


MySQL的存储引擎

关系数据库表是用于存储和组织信息的数据结构,可以将表理解为由行和列组成的表格,类似于Excel的电子表格的形式。有的表简单,有的表复杂,有的表根本不用来存储任何长期的数据,有的表读取时非常快,但是插入数据时去很差;而我们在实际开发过程中,就可能需要各种各样的表,不同的表,就意味着存储不同类型的数据,数据的处理上也会存在着差异,那么。对于MySQL来说,它提供了很多种类型的存储引擎,我们可以根据对数据处理的需求,选择不同的存储引擎,从而最大限度的利用MySQL强大的功能。这篇博文将总结和分析各个引擎的特点,以及适用场合,并不会纠结于更深层次的东西。


在mysql客户端中,使用以下命令可以查看MySQL支持的引擎。

show engines;

MyISAM

MyISAM表是独立于操作系统的,这说明可以轻松地将其从Windows服务器移植到Linux服务器;每当我们建立一个MyISAM引擎的表时,就会在本地磁盘上建立三个文件,文件名就是表明。例如,我建立了一个MyISAM引擎的tb_Demo表,那么就会生成以下三个文件:


1.tb_demo.frm,存储表定义;
2.tb_demo.MYD,存储数据;
3.tb_demo.MYI,存储索引。

MyISAM表无法处理事务,这就意味着有事务处理需求的表,不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用:

1.选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速,这是它最突出的优点。
2.插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如:MyISAM存储引擎很适合管理邮件或Web服务器日志数据。


InnoDB

InnoDB是一个健壮的事务型存储引擎,这种存储引擎已经被很多互联网公司使用,为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的MySQL 5.6.13版,InnoDB就是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束,在以下场合下,使用InnoDB是最理想的选择:

1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
3.自动灾难恢复。与其它存储引擎不同,InnoDB表能够自动从灾难中恢复。
4.外键约束。MySQL支持外键的存储引擎只有InnoDB。
5.支持自动增加列AUTO_INCREMENT属性。

一般来说,如果需要事务支持,并且有较高的并发读取频率,InnoDB是不错的选择。


MEMORY

使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间,采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能,但当mysqld守护进程崩溃时,所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式,这意味着不能使用BLOB和TEXT这样的长度可变的数据类型,VARCHAR是一种长度可变的类型,但因为它在MySQL内部当做长度固定不变的CHAR类型,所以可以使用。

一般在以下几种情况下使用Memory存储引擎:

1.目标数据较小,而且被非常频繁地访问。在内存中存放数据,所以会造成内存的使用,可以通过参数max_heap_table_size控制Memory表的大小,设置此参数,就可以限制Memory表的最大大小。

2.如果数据是临时的,而且要求必须立即可用,那么就可以存放在内存表中。

3.存储在Memory表中的数据如果突然丢失,不会对应用服务产生实质的负面影响。

Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是,可以使用部分查询和通配查询,也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快,但是对“范围比较”的速度就慢多了,因此散列索引值适合使用在=和<>的操作符中,不适合在<或>操作符中,也同样不适合用在order by子句中。


MERGE

MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同,尽管其使用不如其它引擎突出,但是在某些情况下非常有用。说白了,Merge表就是几个相同MyISAM表的聚合器;Merge表中并没有数据,对Merge类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行操作。Merge存储引擎的使用场景。

对于服务器日志这种信息,一般常用的存储策略是将数据分成很多表,每个名称与特定的时间端相关。例如:可以用12个相同的表来存储服务器日志数据,每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表,这意味着需要编写并更新多表查询,以反映这些表中的信息。与其编写这些可能出现错误的查询,不如将这些表合并起来使用一条查询,之后再删除Merge表,而不影响原来的数据,删除Merge表只是删除Merge表的定义,对内部的表没有任何影响。


ARCHIVE

Archive是归档的意思,在归档之后很多的高级功能就不再支持了,仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前,Archive是不支持索引,但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制,它使用zlib压缩库,在记录被请求时会实时压缩,所以它经常被用来当做仓库使用。


如何选择合适的存储引擎?
(1)选择标准可以分为:
(2)是否需要支持事务;
(3)是否需要使用热备;
(4)崩溃恢复:能否接受崩溃;
(5)是否需要外键支持;
然后按照标准,选择对应的存储引擎即可。


下面是基于ThinkPHP框架的事务使用方式

//启动事务:
$User->startTrans(); 
//提交事务:
$User->commit();
//事务回滚:
$User->rollback();

QQ截图20170419161754.png

thinkphp隐藏默认显示的index.php入口配置

一、在Apache根目录conf文件夹下的httpd.conf配置文件中加载mod_rewrite.so模块;

#LoadModule rewrite_module modules/mod_rewrite.so把前面的井号注释去掉

QQ截图20170419152141.png


二、将AllowOverride None 的None全部改为 All     

在APACHE里面去配置 (注意其他地方的AllowOverride也统统设置为ALL)

即:AllowOverride none  改   AllowOverride ALL

保存httpd.conf,重启Apache服务器;

QQ截图20170419152310.png


三、配置配置项文件config.php

/*隐藏index.php主入口*/
'URL_CASE_INSENSITIVE' => true,
//REWRITE模式
'URL_MODEL' => 2,

QQ截图20170419152453.png


四、在根目录下的.htaccess文件添加:

Rewritebase /index.php

注意:如果在Linux的服务器下,.htaccess文件使用下面的配置

#伪静态和泛域名 
 #此文件禁止在行内注释
 <IfModule mod_rewrite.c>
 RewriteEngine on
 #禁止对图片等文件重写:没有这一条,所有的404都会执行index.php脚本,耗费大量资源。
 RewriteCond %{REQUEST_URI} !((.*).jpg|.jpeg|.bmp|.gif|.png|.js|.css)$
 #禁止对/public文件夹内重写,作用同上
 RewriteCond %{REQUEST_URI} !(^/public/(.*))$
 #如果是一个物理存在的目录,禁止重写
 RewriteCond %{REQUEST_FILENAME} !-d
 #如果是一个物理存在的文件,禁止重写
 RewriteCond %{REQUEST_FILENAME} !-f
 #上面2条不识别REQUEST_FILENAME时的替代写法
 #RewriteCond %{DOCUMENT_ROOT}%{REQUEST_URI} !-d
 #RewriteCond %{DOCUMENT_ROOT}%{REQUEST_URI} !-f
 #php api模式,服务器能识别PATH_INFO
 #RewriteRule ^(.*)$ index.php/$1 [QSA,PT,L]
 #php fastcgi模式 服务器不识别PATH_INFO
 RewriteRule ^(.*)$ index.php [E=PATH_INFO:$1,QSA,PT,L]
 </IfModule>

微信截图_20170523164843.png

至此配置完成。。。

web性能优化 - Apache-ab压力测试

Apache的ab命令模拟多线程并发请求,测试服务器负载压力,ab命令对发出负载的计算机要求很低,既不会占用很多CPU,也不会占用太多的内存,但却会给目标服务器造成巨大的负载,因此是某些DDOS攻击之必备良药,老少皆宜。自己使用也须谨慎。否则一次上太多的负载,造成目标服务器直接因内存耗光死机,而不得不硬重启,得不偿失。在带宽不足的情况下,最好是本机进行测试,建议使用内网的另一台或者多台服务器通过内网进行测试,这样得出的数据,准确度会高很多。远程对web服务器进行压力测试,往往效果不理想(因为网络延时过大或带宽不足)
Apache附带的压力测试工具ab,非常容易使用,并且完全可以摸你各种条件对Web服务器发起测试请求。ab可以直接在Web服务器本地发起测试请求,这对于需要了解服务器的处理性能至关重要,因为它不包括数据的网络传输时间以及用户PC本地的计算时间。。

11.png


   参数:-n 在测试会话中所执行的请求总数
         -c 一次产生的请求个数,并发数。默认是一次一个。
         -t测试所进行的最大秒数。其内部隐含值是-n 50000,它可以使对服务器的测试限制在一个固定的总时间以内。默认时,没有时间限制。
         -p包含了需要POST的数据的文件。
         -P对一个中转代理提供BASIC认证信任。用户名和密码由一个:隔开,并以base64编码形式发送。无论服务器是否需要(即, 
是否发送了401认证需求代码),此字符串都会被发送。
         -T POST数据所使用的Content-type头信息。
         -v设置显示信息的详细程度-4或更大值会显示头信息,3或更大值可以显示响应代码(404,200等),2或更大值可以显示警告和其他信息。
         -V显示版本号并退出。
         -w以HTML表的格式输出结果。默认时,它是白色背景的两列宽度的一张表。
         -i执行HEAD请求,而不是GET。
         -x设置<table>属性的字符串。
         -X对请求使用代理服务器。
         -y设置<tr>属性的字符串。
         -z设置<td>属性的字符串。

22.png


This is ApacheBench, Version 2.3 <$Revision: 655654 $> 
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/ 
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 192.168.0.10 (be patient) 
Completed 100 requests 
Completed 200 requests 
Completed 300 requests 
Completed 400 requests 
Completed 500 requests 
Completed 600 requests 
Completed 700 requests 
Completed 800 requests 
Finished 800 requests

Server Software:        Microsoft-HTTPAPI/2.0 
Server Hostname:        192.168.0.10 
Server Port:            80
Document Path:          / 
Document Length:        315 bytes        //HTTP响应数据的正文长度
Concurrency Level:      800 
Time taken for tests:   0.914 seconds    //所有这些请求处理完成所花费的时间 
Complete requests:      800              //完成请求数 
Failed requests:        0                //失败请求数 
Write errors:           0                
Non-2xx responses:      800 
Total transferred:      393600 bytes     //网络总传输量 
HTML transferred:       252000 bytes     //HTML内容传输量 
Requests per second:    875.22 [#/sec] (mean) //吞吐量-每秒请求数 
Time per request:       914.052 [ms] (mean)   //服务器收到请求,响应页面要花费的时间 
Time per request:       1.143 [ms] (mean, across all concurrent requests) //并发的每个请求平均消耗时间 
Transfer rate:          420.52 [Kbytes/sec] received //平均每秒网络上的流量,可以帮助排除是否存在网络流量过大导致响应时间延长的问题

网络上消耗的时间的分解: 
Connection Times (ms) 
              min  mean[+/-sd] median   max 
Connect:        0    1   0.5      1       3 
Processing:   245  534 125.2    570     682 
Waiting:       11  386 189.1    409     669 
Total:        246  535 125.0    571     684


Thinkphp3.2.3使用PHPqrcode生成二维码(附带插件下载链接)

前段时间用jquery.qrcode给每个页面自动生成二维码,却发现和前端页面的jQuery冲突导致某些js效果失效,后来换成使用PHPqrcode生成二维码,前端直接调用接口


PHPqrcode下载链接:http://pan.baidu.com/s/1qY0I4jU

提取密码:2asa


一:下载PHPqrcode并且解压


二:将PHPqrcode文件夹放入ThinkPHP\Library\Vendor中

QQ截图20170113153109.png


三:在控制器中写入下方代码

/**
* 生成链接二维码
* @param $id : 文章主键
* @param $level : 容错级别
* @param $size : 点大小
*/
public function qrcode($id=9,$level=3,$size=4){
			
	Vendor('phpqrcode.phpqrcode');					//导入phpqrcode组件
	$url = "http://www.isblog.cn/isC/".$id.".html";		//跳转链接
	$errorLevel = intval($level);					//容错级别:L、M、Q、H
	$imgSize = intval($size);					//点的大小:1到10,用于手机端4就可以了
	$object = new \QRcode();					//实例化
	$object->png($url, false, $errorLevel, $imgSize, 2);
			
}

22.png


四:在前端页面需要调用该二维码的img标签中直接调用该方法即可

33.png

ThinkPHP3.2.3路由设置,短链接优化站点url,更利于搜索引擎优化

众所周知,搜索引擎对于动态路径的网站抓取是相当不友好的,因此,网站该如何设置静态、伪静态页面呢?


下面说一下ThinkPHP3.2.3框架的静态路由设置(静态路由其实属于规则路由的静态简化版,又称之为URL映射)。


一:在Application\Common\Conf\config.php中配置路由配置项

return array(
//'配置项'=>'配置值'
//模块化
'MODULE_ALLOW_LIST'      =>  ['Home','Admin'],                   //设置URL不显示的模块名
'MODULE_DENY_LIST'       =>  ['Module'],                         //设置禁止访问的模块列表
//路由设置
'URL_MODEL'              =>  2,                                 //URL模式,去除index.php
'URL_ROUTER_ON'         =>  TRUE,                               //开启路由
'URL_ROUTE_RULES'        =>  [                                   //定义路由规则
           'Blog/list'           =>     'enty/index',            //静态路由
           'Blog/cont/:id\d'     =>     'user/editArticle',      //动态路由
],
);


1.png

2.png

至此路由配置完成。

点击下面打赏一个呗~

WordPress博客系统外部主题安装

如果你在WordPress官方主题目录无法找到你喜欢的WordPress主题,你还可以尝试使用 谷歌。 想创建一个软件主题博客? 搜索”software wordpress theme” ;想创建一个在线商店出售产品? 搜索“online store wordpress theme” ……。

在这里,推荐几个第三方WordPress主题收藏站:

1.wopus.org——目前国内最大的WordPress主题站,提供大量的国内外WordPress主题

2.wpued.com——相对wopus,wpued提供的WordPress主题较少,但不失是个找主题的好地方

3. boygj.com——提供大量的国外免费和收费WordPress主题,是笔者最喜欢关注的国外博客之一。除了推荐WordPress主题,它还推荐Joomla模板、Drupal主题和其他一些建站素材。

如果你的预算充足,你还可以考虑购买一些优秀的专业WordPress,国外的专业WordPress主题价格一般在20-60美金之间,质量对于免费的WordPress主题好很多。或者请专业的WordPress主题设计者为了量身定做一个,价格会相对较高,视主题的功能和制作难度而定。

一旦你找到一个合适的WordPress主题,从网站下载.zip文件到你的电脑。

点击”外观—主题”进入主题管理面板,然后点击添加新主题,在下一页选择“上传”,并选择你计算机上要上传的WordPress主题.zip文件进行上传并进行安装。

如果由于某些原因,使用WordPress管理面板可以出现主题安装失败,这种情况下你可以尝试使用FTP工具直接上传WordPress主题。将主题.zip压缩包解压至你的电脑, 然后用FTP工具把它上传到您的服务器。 我们推荐你使用FlashFXP。使用FlashFXP上传WordPress主题,需要设置你的主机信息,比如网站主机的IP,FTP用户名和密码。将主题(整个文件夹)上传到wp-content/themes/文件夹下即可。

20161223114724161.png

使用前端插件jquery.qrcode生成网页二维码(附带插件下载链接)

使用jquery.qrcode插件,给不同的网页自动生成二维码。


jquery.qrcode插件下载链接:http://pan.baidu.com/s/1eSDocG6

提取密码:dn5p


jquery.qrcode支持使用table和canvas方式渲染

canvas性能还是非常不错的,但是如果用table方式,性能不太理想,特别是IE9以下的浏览器,所以需要自行优化一下渲染table的方式,这里就不细述了。


一:引入依赖jQuery库


二:引入jquery.qrcode.min.js插件库


三:在页面底部添加下方js代码

<script type="text/javascript">
var url = "http://www.isblog.cn/index.html";//页面链接
   $('#qrcode').qrcode({//绑定显示二维码图片的div
      width: 150,//二维码宽度
      height: 150,//二维码高度
      text: url//传入url
   });
</script>

QQ截图20170112111752.png

MySQL实用命令及使用innodb_force_recovery处理表损坏

一、非常实用的MySQL语句

1. 计算年数

你想通过生日来计算这个人有几岁了。

SELECTDATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y') + 0;
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now())- TO_DAYS('1987-02-28 14:23:56')),'%Y') + 0;


2. 两个时间的差

取得两个 datetime 值的差。假设 dt1  dt2 datetime 类型,其格式为 ‘yyyy-mm-dd hh:mm:ss’,那么它们之间所差的秒数为:

UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 ) 除以60就是所差的分钟数,除以3600就是所差的小时数,再除以24就是所差的天数。


3. 显示某一列出现过N次的值

SELECT id
FROM tbl
GROUP BY id
HAVING COUNT(*) = N;


4. 计算两个日子间的工作日

所谓工作日就是除出周六周日和节假日。

SELECT COUNT(*)
FROM calendar
WHERE d BETWEEN Start ANDStop
AND DAYOFWEEK(d) NOTIN(1,7)
AND holiday=0;


5. 查找表中的主键

SELECT k.column_name
FROMinformation_schema.table_constraints t
JOINinformation_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHEREt.constraint_type='PRIMARY KEY'
AND t.table_schema='db'
AND t.table_name=tbl'


6. 查看你的数库有多大

ELECT
table_schema AS 'Db Name',
Round( Sum( data_length +index_length ) / 1024 / 1024, 3 ) AS 'Db Size (MB)',
Round( Sum( data_free ) /1024 / 1024, 3 ) AS 'Free Space (MB)'
FROMinformation_schema.tables
GROUP BY table_schema ;

 

二、参数innodb_force_recovery

IP修改后,环境遇到一个问题,MySQL重启后启动失败,进程不存在。经后续定位分析,MySQL的Undo表空间遭到损坏,MySQL重启后就会导致进程起不来。

日志部分如下:

The manual page athttp://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find outwhat is causing the crash.
130609 18:03:51 mysqld_safe mysqld from pidfile /home/mysql/data/rcs.pid ended
130609 18:11:19 mysqld_safe Starting mysqlddaemon with databases from /home/mysql/data
130609 18:11:19 [Note] Failed to executemysql_file_stat on file '../binlog/mysqlbinlog.000020'
130609 18:11:19 [Note] Plugin 'FEDERATED'is disabled.
130609 18:11:19 InnoDB: The InnoDB memoryheap is disabled
130609 18:11:19 InnoDB: Mutexes andrw_locks use GCC atomic builtins
130609 18:11:19 InnoDB: Compressed tablesuse zlib 1.2.3
130609 18:11:19 InnoDB: Using Linux nativeAIO
130609 18:11:19 InnoDB: Initializing bufferpool, size = 11.5G
130609 18:11:20 InnoDB: Completedinitialization of buffer pool
130609 18:11:20 InnoDB: highest supportedfile format is Barracuda.
130609 18:11:20  InnoDB: Error: page 2 log sequence number498375142
InnoDB: is in the future!Current system log sequence number 495237684.
InnoDB: Your database maybe corrupt or you may have copied the InnoDB
InnoDB: tablespace butnot the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
130609 18:11:20  InnoDB: Error: page 489 log sequence number495468639
InnoDB: is in the future! Current systemlog sequence number 495237684.
InnoDB: Your database may be corrupt or youmay have copied the InnoDB
InnoDB: tablespace but not the InnoDB logfiles. See
InnoDB:http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.

 

检查其他正常启动MySQL数据库的系统参数时,发现:

mysql> show variables like'innodb_force_recovery';                                                                         
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_force_recovery | 0     |
+-----------------------+-------+

 

发现innodb_force_recovery参数的默认值为0,意思就是当需要恢复时执行所有的恢复操作。当不能进行有效的恢复操作时,mysql就有可能无法启动。.

于是手动在my.cnf文件里面配置innodb_force_recovery=6,再重启MySQL,就可以了。

 

注意:

只有在需要从错误状态的数据库进行数据备份时,才建议设置innodb_force_recovery的值大于0。

0 —正常的关闭和启动,不会做任何强迫恢复操作;

1 —跳过错误页,让mysqld服务继续运行。跳过错误索引记录和存储页,尝试用

SELECT * INOT OUTFILE ‘../filename’ FROM tablename;方式,完成数据备份;

2 —阻止InnoDB的主线程运行。清理操作时出现mysqld服务崩溃,则会阻止数据恢复操作;

3 —恢复的时候,不进行事务回滚;

4 —阻止INSERT缓冲区的合并操作。不做合并操作,为防止出现mysqld服务崩溃。不计算表的统计信息

5 — mysqld服务启动的时候不检查回滚日志:InnoDB引擎对待每个不确定的事务就像提交的事务一样;

6 —不做事务日志前滚恢复操作;

 

具体细节可参考MySQL官方网址:http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html




MySQL主从及Linux下MySQL的设置

1、复制

一种高可用、高性能的解决方案,一般用来建立大型的应用。

三个步骤:

(1)主服务器把数据更新记录到二进制日志中

(2)从服务器把主服务器的二进制日志拷贝到自己的中继日志中

(3)从服务器重做日志中的时间,把更新应用到自己的数据库

0.png

1.png


MySQL的复制是异步同步的,并非完全的主从同步。
从服务器上可以启动read-only选项:
[mysqld]
read-only
快照+复制的备份架构

2.png

4.png

3.png

 

2、性能调优

5.png


(1)选择合适的CPU

数据库应用一般分为两类:

OLTP:在线事务处理,日常事务处理应用中,如银行交易,在线商品交易,blog等

OLAP:在线分析处理,多用于数据仓库或数据集市中,一般需要执行复杂的SQL语句来获取查询

 

InnoDB存储引擎一般都使用于OLTP的数据库应用:

用户操作的并发量大

事务处理的时间一般比较短

查询的语句较简单,一般都走索引

复杂的查询较少

 

OLAP是CPU密集型的操作

OLTP是IO密集型的操作

 

采购设备时要注意提高IO的配置

 

如果CPU支持多核,InnoDB Plugin是更好的选择。另外,如果你的CPU是多核,可以通过改变参数innodb_read_io_threads和innodb_write_io_threads来增大IO的线程,这样也能更充分利用CPU的多核性能。


 

(2)内存的重要性

6.png

7.png


 

(3)硬盘对数据库性能的影响

传统机械硬盘

固态硬盘

(4)合理地设置RAID

RAID基本思想:就是把多个相对便宜的磁盘组合起来,成为一个磁盘数组,使性能达到甚至超过一个价格昂贵、容量巨大的硬盘。

RAID的作用是:

增强数据集成度

增强容错功能

增加处理量或容量

8.png

9.png

10.png

11.png

12.png


 

(5)操作系统的选择也很重要

(6)不同文件系统对数据库性能的影响

(7)选择合适的基准测试工具

MySQL提供了一些比较优秀的工具:sysbench和mysql-tpcc

 



3、Linux的大小写
Linux下mysql安装完后是默认:
1、区分表名的大小写,不区分列名的大小写;
2、用root帐号登录后,在/etc/my.cnf 中的[mysqld]后添加添加lower_case_table_names=1,重启MYSQL服务,这时已设置成功:不区分表名的大小写;
lower_case_table_names参数详解:lower_case_table_names = 0其中 0:区分大小写,1:不区分大小写。
MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的:
1、数据库名与表名是严格区分大小写的;
2、表的别名是严格区分大小写的;
3、列名与列的别名在所有的情况下均是忽略大小写的;
4、变量名也是严格区分大小写的;
MySQL在Windows下都不区分大小写。
3、如果想在查询时区分字段值的大小写,则:字段值需要设置BINARY属性,设置的方法有多种:
A、创建时设置:
CREATE TABLE T(A VARCHAR(10) BINARY);
B、使用alter修改:

ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR(45) BINARY;


C、mysql table editor中直接勾选BINARY项。


在Linux环境中的MySQL默认设置下,数据库和表名是区分大小写的,但在Windows中,MySQL的数据库和表名是不区分大小写的。
(1)在任何系统中可以使用lower_case_tables_name=1。使用该选项的不利之处是当使用SHOW TABLES或SHOW DATABASES时,看不出名字原来是用大写还是小写。
(2) 在Linux中使用lower_case_tables_name=0,在Windows中使用lower_case_tables_name=2。这样了可以保留数据库名和表名的大小写。不利之处是必须确保在Windows中查询总是用正确大小写引用数据库名和表名。如果将查询转移到Linux中,由于在Linux中大小写很重要,如果大小写不正确,它们不工作。
例外:如果使用InnoDB表,在任何平台上均应将lower_case_tables_name设置为1,以强制将名转换为小写。
请注意在Linux中将lower_case_tables_name设置为1之前,重启mysqld之前,必须先将旧的数据库名和表名转换为小写。


MySQL调优脚本tuning-primer.sh使用说明

SLOW QUERIES 慢查询检查
SLOW QUERIESThe slow query log is enabled. 说明我已经启用了慢查询记录功能。也就是参数
slow_query_log = 1
Current long_query_time = 5.000000 sec. 慢查询的阀值时间。也就是参数
long_query_time = 5
You have 17 out of 638844 that take longer than 5.000000 sec. to complete 说明慢查询日志中记录了17条查询时间超过5秒的语句。
slow_query_log_file=/data/ats_db/mysql-slow.log设置慢查询日志路径。使用

mysqldumpslow命令查询慢日志

Your long_query_time seems to be fine 慢查询阀值时间设置得在推荐的范围内
BINARY UPDATE LOG 更新二进制日志文件
The binary update log is enabled 这项说明启用了bin-log日志功能。参数
log-bin = /data/ats_db/mysql-bin
Binlog sync is not enabled, you could loose binlog records during a server crash 没有启用 sync_binlog 选项。也即是将二进制日志实时写入到磁盘通过 sync_binlog=1来指定
WORKER THREADS 工作线程
Current thread_cache_size = 8 当前线程缓存大小。
thread_concurrency = 8
Current threads_cached = 7 Show status like ‘threads_cached’
Current threads_per_sec = 0 脚本先执行Show status like ‘Threads_cached’查看当前的线程创建情况,然后sleep 1后在执行相同的命令,最终后者减去前者的数就是每秒线程创建数。
Historic threads_per_sec = 0 该值是使用Threads_cached /uptime获得的。
Your thread_cache_size is fine
MAX CONNECTIONS 最大连接数
Current max_connections = 1024 当前配置文件中设置的并发连接数
Current threads_connected = 2 当前线程连接诶数。
show status like ‘Threads_connected’
Historic max_used_connections = 4 show status like ‘Max_used_connections’;
The number of used connections is 0% of the configured maximum. 这个值使用 Max_used_connections*100/ max_connections得出。
You are using less than 10% of your configured max_connections.Lowering max_connections could help to avoid an over-allocation of memory

See “MEMORY USAGE” section to make sure you are not over-allocating

Max_used_connections的值不足max_connections值的10%。设置合适的max_connections值有助于节省内存。
MEMORY USAGE 内存使用
Max Memory Ever Allocated : 841 M Max Memory Ever Allocated = max_memory
Configured Max Per-thread Buffers : 28.40 G Configured Max Per-thread Buffers per_thread_buffers
Configured Max Global Buffers : 586 M Configured Max Global Buffers = global_buffers
Configured Max Memory Limit : 28.97 G Configured Max Memory Limit = total_memory
这一项很重要,他是将各个缓存的大小累加,然后同max_connections相乘,从而得出当达到max_connections后需要分配的内存有多少。我这里由于max_connections写得很大,造成了最大内存限制超过了真实内存很多,所以建议不要随意增大max_connections的值。减小 max_connections的值,最终保证最大内存限制在真实内存的90%以下。
Physical Memory : 7.79 G 实际物理内存
Max memory limit exceeds 90% of physical memory
per_thread_buffers
(read_buffer_size+read_rnd_buffer_size +sort_buffer_size+thread_stack+
join_buffer_size+binlog_cache_size)*max_connections

per_thread_max_buffers
(read_buffer_size+read_rnd_buffer_size +sort_buffer_size+thread_stack
+join_buffer_size+binlog_cache_size)*max_used_connections

max_used_connections=$(mysql -Bse "show status like 'Max_used_connections'" | awk '{ print $2 }')


global_buffers
innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+
key_buffer_size+query_cache_size


max_memory
=global_buffers+per_thread_max_buffers


total_memory
=global_buffers+per_thread_buffers

KEY BUFFER Key 缓冲
Current MyISAM index space = 222 K 当前数据库MyISAM表中索引占用磁盘空间
Current key_buffer_size = 512 M MySQL配置文件中key_buffer_size 设置的大小
Key cache miss rate is 1 : 3316 Key_read_requests/ Key_reads 这里说明3316次读取请求中有1次丢失(也就是说1次读取磁盘)
Key buffer free ratio = 81 % key_blocks_unused * key_cache_block_size / key_buffer_size * 100
Your key_buffer_size seems to be fine
QUERY CACHE Query 缓存
Query cache is enabled 该项说明 我们指定了query_cache_size 的值。如果query_cache_size=0的话这里给出的提示是:
Query cache is supported but not enabled
Perhaps you should set the query_cache_size
Current query_cache_size = 64 M 当前系统query_cache_size 值大小 [F]
Current query_cache_used = 1 M query_cache_used =query_cache_size-qcache_free_memory
Current query_cache_limit = 128 M 变量 query_cache_limit 大小
Current Query cache Memory fill ratio = 1.79 % query_cache_used/query_cache_size *100%
Current query_cache_min_res_unit = 4 K show variables like ‘query_cache_min_res_unit’;
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
这项给出的结论是query_cache_size的值设置的有些过高。其比对标准是 “Query cache Memory fill ratio”的值如果小于<25%就会给出这个提示。可以将这些资源应用到其他的地方
MySQL won’t cache query results that are larger than query_cache_limit in size MySQL不会将大于query_cache_limit的查询结果进行缓存
show status like ‘Qcache%’;Qcache_free_blocks        10
Qcache_free_memory        65891984
Qcache_hits            14437
Qcache_inserts            707
Qcache_lowmem_prunes    0
Qcache_not_cached        216
Qcache_queries_in_cache    540
Qcache_total_blocks        1191
SORT OPERATIONS SORT 选项
Current sort_buffer_size = 6 M show variables like ’sort_buffer%’;
Current read_rnd_buffer_size = 16 M show variables like ‘read_rnd_buffer_size%’;
Sort buffer seems to be fine
JOINS JOINS
Current join_buffer_size = 132.00 K show variables like ‘join_buffer_size%’;join_buffer_size=join_buffer_size+4kb
You have had 6 queries where a join could not use an index properly 这里的6是通过 show status like ‘Select_full_join’; 获得的
You should enable “log-queries-not-using-indexes”
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
你需要启用 “log-queries-not-using-indexes” 然后在慢查询日志中看是否有取消索引的joins语句。如果不优化查询语句的话,则需要增大join_buffer_size
OPEN FILES LIMIT 文件打开数限制
Current open_files_limit = 1234 files show variables like ‘open_files_limit%’;
The open_files_limit should typically be set to at least 2x-3xthat of table_cache if you have heavy MyISAM usage. 如果系统中有很多的MyISAM类型的表,则建议将open_files_limit 设置为2X~3X的table_open_cache
show status like ‘Open_files’;open_files_ratio= open_files*100/open_files_limit
如果open_files_ratio 超过75% 则需要加大open_files_limit
Your open_files_limit value seems to be fine
TABLE CACHE TABLE 缓存
Current table_open_cache = 512 tables show variables like ‘table_open_cache’;
Current table_definition_cache = 256 tables show variables like ‘ table_definition_cache ‘;
You have a total of 368 tables SELECTCOUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’
You have 371 open tables. show status like ‘Open_tables’;
The table_cache value seems to be fine Open_tables /table_open_cache*100% < 95%
You should probably increase your table_definition_cache value. table_cache_hit_rate =open_tables*100/opened_tables
TEMP TABLES 临时表
Current max_heap_table_size = 16 M show variables like ‘max_heap_table_size’;
Current tmp_table_size = 16 M show variables like ‘tmp_table_size’;
Of 285 temp tables, 11% were created on disk Created_tmp_tables=285created_tmp_disk_tables*100/
(created_tmp_tables+created_tmp_disk_tables)=11%
Created disk tmp tables ratio seems fine
TABLE SCANS 扫描表
Current read_buffer_size = 6 M show variables like ‘read_buffer_size’;
Current table scan ratio = 9 : 1 read_rnd_next =show global status like ‘Handler_read_rnd_next’;
com_select= show global status like ‘Com_select’;
full_table_scans=read_rnd_next/com_select
Current table scan ratio = full_table_scans : 1″
如果表扫描率超过4000,说明进行了太多表扫描,很有可能索引没有建好,增加read_buffer_size值会有一些好处,但最好不要超过8MB。
read_buffer_size seems to be fine
TABLE LOCKING TABLE LOCKING
Current Lock Wait ratio = 0 : 5617 show global status like’Table_locks_waited’;
show global status like

‘Questions’;
如果 Table_locks_waited=0
Current Lock Wait ratio = 0: Questions

Your table locking seems to be fine


获取脚本的路径为:

https://code.launchpad.net/mysql-tuning-primer

脚本内容如下:

#!/bin/sh


# vim: ts=8
#########################################################################
# #
# MySQL performance tuning primer script#
# Writen by: Matthew Montgomery#
# Report bugs to: https://bugs.launchpad.net/mysql-tuning-primer#
# Inspired by: MySQLARd (http://gert.sos.be/demo/mysqlar/)#
# Version: 1.6-r1Released: 2011-08-06#
# Licenced under GPLv2                                            #
# #
#########################################################################


#########################################################################
# #
# Usage: ./tuning-primer.sh [ mode ] #
# #
# Available Modes: #
# all : perform all checks (default)#
# prompt : prompt for login credintials and socket#
# and execution mode#
mem, memory : run checks for tunable options which  #
# effect memory usage#
# disk, file :run checks for options which effect#
# i/o performance or file handle limits#
# innodb :  run InnoDB checks /* to be improved */
# misc : run checks for that don't categorise#
# well Slow Queries, Binary logs,#
# Used Connections and Worker Threads#
#########################################################################
# #
# Set this socket variable ONLY if you have multiple instances running
# or we are unable to find your socket, and you don't want to to be#
# prompted for input each time you run this script.#
# #
#########################################################################
socket=


export black='\033[0m'
export boldblack='\033[1;0m'
export red='\033[31m'
export boldred='\033[1;31m'
export green='\033[32m'
export boldgreen='\033[1;32m'
export yellow='\033[33m'
export boldyellow='\033[1;33m'
export blue='\033[34m'
export boldblue='\033[1;34m'
export magenta='\033[35m'
export boldmagenta='\033[1;35m'
export cyan='\033[36m'
export boldcyan='\033[1;36m'
export white='\033[37m'
export boldwhite='\033[1;37m'




cecho ()


## -- Function to easliy print colored text -- ##

# Color-echo.
# Argument $1 = message
# Argument $2 = color
{
local default_msg="No message passed."


message=${1:-$default_msg} # Defaults to default message.


#change it for fun
#We use pure names
color=${2:-black} # Defaults to black, if not specified.


case $color in
black)
printf "$black" ;;
boldblack)
printf "$boldblack" ;;
red)
printf "$red" ;;
boldred)
printf "$boldred" ;;
green)
printf "$green" ;;
boldgreen)
printf "$boldgreen" ;;
yellow)
printf "$yellow" ;;
boldyellow)
printf "$boldyellow" ;;
blue)
printf "$blue" ;;
boldblue)
printf "$boldblue" ;;
magenta)
printf "$magenta" ;;
boldmagenta)
printf "$boldmagenta" ;;
cyan)
printf "$cyan" ;;
boldcyan)
printf "$boldcyan" ;;
white)
printf "$white" ;;
boldwhite)
printf "$boldwhite" ;;
esac
  printf "%s\n"  "$message"
  tput sgr0 # Reset to normal.
  printf "$black"


return
}




cechon ()


## -- Function to easliy print colored text -- ##


# Color-echo.
# Argument $1 = message
# Argument $2 = color
{
local default_msg="No message passed."
# Doesn't really need to be a local variable.


message=${1:-$default_msg} # Defaults to default message.


#