MYSQL入门全套

mysql简介 1、什么是数据库 ? 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距今六十多年前,随着信息技术和市场的发展,特别是二十世纪九十年代以后,数据管理不再仅仅是存储和管理数据,而转变成用户所需要的各种数据管理的方式。数据库有很多种类型,从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统都在各个方面得到了广泛的应用。 主流的数据库有:sqlserver,mysql,Oracle、SQLite、Access、MS SQL Server等,本文主要讲述的是mysql 2、数据库管理是干什么用的? a. 将数据保存到文件或内存 b. 接收特定的命令,然后对文件进行相应的操作 PS:如果有了以上管理系统,无须自己再去创建文件和文件夹,而是直接传递 命令 给上述软件,让其来进行文件操作,他们统称为数据库管理系统(DBMS,Database Management System) mysql安装 MySQL是一种开放源代码的关系型数据库管理系统(RDBMS),MySQL数据库系统使用最常用的数据库管理语言–结构化查询语言(SQL)进行数据库管理。在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。 使用mysql必须具备一下条件   a. 安装MySQL服务端   b. 安装MySQL客户端   b. 【客户端】连接【服务端】   c. 【客户端】发送命令给【服务端MySQL】服务的接受命令并执行相应操作(增删改查等) 1、下载地址:http://dev.mysql.com/downloads/mysql/ 2、安装 windows安装请参考:http://www.cnblogs.com/lonelywolfmoutain/p/4547115.html linux下安装:http://www.cnblogs.com/chenjunbiao/archive/2011/01/24/1940256.html 注:以上两个链接有完整的安装方式,撸主也是参考他的安装的,安装完以后mysql.server start启动mysql服务 mysql操作 一、连接数据库 mysql -u user -p 例:mysql -u root -p 常见错误如下: ERROR 2002 … Continue reading "MYSQL入门全套"

mysql数据压缩性能对比(二)

在上一篇文章中,我们用生产环境的真实数据与真实SQL测试了archive和myisampack两种方式下的性能对比情况。我们得到一个对这个测试case有效的结论,那就是在240万数据量的情况下,采用archive引擎将使得某些查询慢得无法忍受! 那么,原因是什么呢? 我们知道,mysql提供archive这种存储引擎是为了降低磁盘开销,但还有一个前提,那就是被归档的数据不需要或者很少被在线查询,偶尔的查询慢一些也是没关系的。鉴于上述原因,archive表是不允许建立自增列之外的索引的。 有了这个共识,我们拿一条测试SQL来分析一下不用索引前后的查询性能差别为什么这么大。在我们的测试SQL中有这么一条: SELECT c1,c2,...,cn FROM mysqlslap.rpt_topranks_v3 WHERE ... AND partition_by1 = '50008090' ORDER BY added_quantity3 DESC LIMIT 500 我们前边说过,测试的这个表在partition_by1这个字段上建立了索引,那么,我们初步判断在基准表和myisampack表上,这个查询应该用到了partition_by1的索引;EXPLAIN一下: mysql> EXPLAIN -> SELECT ... FROM mysqlslap.rpt_topranks_v3 -> WHERE ... AND partition_by1 = '50008090' -> ORDER BY added_quantity3 DESC -> LIMIT 500\G *************************** 1. ROW *************************** id: 1 select_type: SIMPLE TABLE: rpt_topranks_v3 TYPE: REF … Continue reading "mysql数据压缩性能对比(二)"

mysql的数据压缩性能对比(一)

数据魔方需要的数据,一旦写入就很少或者根本不会更新。这种数据非常适合压缩以降低磁盘占用。MySQL本身提供了两种压缩方式——archive引擎以及针对MyISAM引擎的myisampack方式。今天对这两种方式分别进行了测试,对比了二者在磁盘占用以及查询性能方面各自的优劣。至于为什么做这个,你们应该懂的,我后文还会介绍。且看正文: 1. 测试环境: 软硬件 一台 64位 2.6.18-92 内核Linux开发机,4G内存,4个2800Mhz Dual-Core AMD Opteron(tm) Processor 2220 CPU。 MySQL放在一块7200转SAT硬盘,未做raid; MySQL未做任何优化,关闭了query cache,目的在于避免query cache对测试结果造成干扰。 表结构 2424753条记录,生产环境某一个分片的实际数据; 分别建立了(partition_by1,idx_rank) 和 (partition_by1,chg_idx)的联合索引,其中partition_by1为32长度的varchar类型,用于检索;其余两个字段均为浮点数,多用于排序; autokid作为子增列,充当PRIMARY KEY,仅作为数据装载时原子性保证用,无实际意义。 2. 测试目的: 压缩空间对比 压缩率越大,占用的磁盘空间越小,直接降低数据的存储成本; 查询性能对比 压缩后查询性能不应该有显著降低。Archive是不支持索引的,因此性能降低是必然的,那么我们也应该心里有个谱,到底降低了多少,能不能接受。 3. 测试工具: mysqlslap 官方的工具当然是不二之选。关于mysqlslap的介绍请参考 官方文档。 测试query 截取生产环境访问topranks_v3表的实际SQL共9973条,从中抽取访问量较大的7条,并发50,重复执行10次。命令如下: ./mysqlslap --defaults-file=../etc/my.cnf -u**** -p**** -c50 -i10 -q ../t.sql --debug-info 4.测试结论 比较项 磁盘空间 耗时(秒) CPU Idle LOAD 并发 基准表(MyISAM) 403956004 … Continue reading "mysql的数据压缩性能对比(一)"

五款常用mysql slow log分析工具的比较

mysql slow log 是用来记录执行时间较长(超过long_query_time秒)的sql的一种日志工具. 启用 slow log 有两种启用方式:1, 在my.cnf 里 通过 log-slow-queries[=file_name] 2, 在mysqld进程启动时,指定–log-slow-queries[=file_name]选项 比较的五款常用工具mysqldumpslow, mysqlsla, myprofi, mysql-explain-slow-log, mysqllogfilter mysqldumpslow, mysql官方提供的慢查询日志分析工具. 输出图表如下: 主要功能是, 统计不同慢sql的 出现次数(Count),  执行最长时间(Time),  累计总耗费时间(Time),  等待锁的时间(Lock),  发送给客户端的行总数(Rows),  扫描的行总数(Rows),  用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示). mysqlsla, hackmysql.com推出的一款日志分析工具(该网站还维护了 mysqlreport, mysqlidxchk 等比较实用的mysql工具) 整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等. 格式说明如下: 总查询次数 (queries total), 去重后的sql数量 (unique) … Continue reading "五款常用mysql slow log分析工具的比较"

MySQL Storage Engine 小记

这段时间在看《High Performance MySQL》,看到存储引擎这个地方感到很多细节比较陌生,所以总结小记一些为了适应各种不同的运行环境,MYSQL提供了多种不同的存储引擎(Storage Engine ),在应用程序开发这个层面上,开发者可以根据不同的需求选择适合的Storage Engine 方案,更为灵活的是,你可以根据每张表将要存储数据的特点,选择不同的Storage Engine,也就是说,在一个MYSQL数据库中,可以混合使用多种不同的Storage Engine首先小瞥一下MySQL的体系结构,在最高抽象层度下,可以用Garlan & Shaw的分层结构体系来表示(左) 其中应用层为所有RDBMS用户提供用户接口,逻辑层包括了所有核心功能的实现,物理层则负责将数据存储在硬件设备上。图中右侧更为具体的描述了逻辑层的组成,查询处理子系统、事务管理子系统、恢复管理子系统和存储管理子系统共同组成了MySQL的逻辑层。相信Storage Engine的位置是在Storage Management处,既Storage Engine属于Storage Management子系统的一部分为了让思路更清晰一些,下面给出一幅比较全面的体系结构图(或更确切的说是流程图,只是忽略了反馈) 上面三幅图来自于一篇非官方(不保证百分百的正确)的MySQL体系结构的报告,与《High Performance MySQL》一书中给出的MySQL大体结构(下图,基本对应于Logic Layer,从第一幅图右侧可以看出MySQL logic layer同样遵从分层体系结构)还是比较吻合的。 连接上图中第二层和第三层之间的接口是并不针对任何存储引擎的单一API,.大概由20个基本的类似“启动事务,返回结果集”等函数组成。存储引擎并不处理SQL,相互之间也不通信,它们的任务只是简单的响应高层传来的请求。存储引擎各自的一些特点 上面提到的四种存储引擎都有各自适用的环境,这取决于它们独有的一些特征。主要体现在性能、事务、并发控制、参照完整性、缓存、 故障恢复,备份及回存等几个方面目前比较普及的存储引擎是MyISAM和InnoDB.而MyISAM又是绝大部分Web应用的首选。MyISAM与InnoDB的主要的不同点在于性能和事务控制上。MyISAM是早期ISAM(Indexed Sequential Access Method,我现在用的MySQL5.0已经不支持ISAM了)的扩展实现,ISAM被设计为适合处理读频率远大于写频率这样一种情况,因此ISAM以及后来的MyISAM都没有考虑对事物的支持,排除了TPM,不需要事务记录,ISAM的查询效率相当可观,而且内存占用很少。MyISAM在继承了这类优点的同时,与时俱进的提供了大量实用的新特性和相关工具。例如考虑到并发控制,提供了表级锁,虽然MyISAM本身不支持容错,但可以通过myisamchk进行故障恢复。而且由于MyISAM是每张表使用各自独立的存储文件(MYD数据文件和MYI索引文件),使得备份及恢复十分方便(拷贝覆盖即可),而且还支持在线恢复。所以如果你的应用是不需要事务,处理的只是基本的CRUD操作,那么MyISAM是不二选择InnoDB被设计成适用于高并发读写的情况.使用MVCC(Multi-Version Concurrency Control)以及行级锁来提供遵从ACID的事务支持。InnoDB支持外键参照完整性,具备故障恢复能力。另外 InnoDB的性能其实还是不错的,特别是在处理大数据量的情况下,用官方的话说就是: InnoDB的CPU效率是其他基于磁盘的关系数据库存储引擎所不能比的。不过InnoDB的备份恢复要麻烦一点,除非你使用了4.1以后版本提供的Mulit-tablespace支持,因为InnoDB和MyISAM不同,他的数据文件并不是独立对应于每张表的。而是使用的共享表空间,简单的拷贝覆盖方法对他不适用,必须在停掉MYSQL后对进行数据恢复。使用Per-Table Tablespacesd,使其每张表对应一个独立的表空间文件,则情况要简单很多。一般来说,如果需要事务支持,并且有较高的并发读写频率,InnoDB是不错的选择。要是并发读写频率不高的话,其实可以考虑BDB,但由于在MySQL5.1及其以后版本中,将不再提供BDB支持。这个选项也就没有了至于Heap和BDB(Berkeley DB),相对来说,普及率不如前两种,但在有些情况下,还是挺适用的Heap存储引擎就是将数据存储在内存中,由于没有磁盘I./O的等待,速度极快。但由于是内存存储引擎,所做的任何修改在服务器重启后都将消失。 Heap挺适合做测试的时候使用BDB是MySQL第一款事务安全的存储引擎。在Berkeley DB database library的基础上建立,同样是事务安全的,但BDB的普及率显然不及InnoDB,因为大多数在MySQL中寻找支持事务的存储引擎的同时也在找支持MVCC或是行级锁定存储引擎,而BDB只支持Page-level Lock。     附上一张《High Performance MySQL》 中的各存储引擎的特性表 Attribute MyISAM Heap BDB InnoDB Transactions No No Yes Yes Lock … Continue reading "MySQL Storage Engine 小记"

提高mysql性能的方法!

提高mysql性能的方法! 来自:chinaunix  作者:uunguymadman007风云使者 一、问题的提出在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应用系统提交实际应用后,随着数据库中数据的增加,系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据,劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍,可见对于一个系统不是简单地能实现其功能就可,而是要写出高质量的SQL语句,提高系统的可用性。在多数情况下,Oracle使用索引来更快地遍历表,优化器主要根据定义的索引来提高性能。但是,如果在SQL语句的where子句中写的SQL代码不合理,就会造成优化器删去索引而使用全表扫描,一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引,这有助于写出高性能的SQL语句。二、SQL语句编写注意问题下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中,即使某些列存在索引,但是由于编写了劣质的SQL,系统在运行该SQL语句时也不能使用该索引,而同样使用全表扫描,这就造成了响应速度的极大降低。1. IS NULL 与 IS NOT NULL不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。2. 联接列对于有联接的列,即使最后的联接值为一个静态值,优化器是不会使用索引的。我们一起来看一个例子,假定有一个职工表(employee),对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME),现在要查询一个叫比尔.克林顿(Bill Cliton)的职工。下面是一个采用联接查询的SQL语句,select * from employsswherefirst_name||”||last_name =’Beill Cliton’ 上面这条语句完全可以查询出是否有Bill Cliton这个员工,但是这里需要注意,系统优化器对基于last_name创建的索引没有使用。当采用下面这种SQL语句的编写,Oracle系统就可以采用基于last_name创建的索引。Select * from employeewherefirst_name =’Beill’ and last_name =’Cliton’ 遇到下面这种情况又如何处理呢?如果一个变量(name)中存放着Bill Cliton这个员工的姓名,对于这种情况我们又如何避免全程遍历,使用索引呢?可以使用一个函数,将变量name中的姓和名分开就可以了,但是有一点需要注意,这个函数是不能作用在索引列上。下面是SQL查询脚本:select * from employeewherefirst_name = SUBSTR(‘&&name’,1,INSTR(‘&&name’,’ ‘)-1)andlast_name = SUBSTR(‘&&name’,INSTR(‘&&name’,’ ‘)+1) 3. 带通配符(%)的like语句同样以上面的例子来看这种情况。目前的需求是这样的,要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:select * from employee where last_name like ‘%cliton%’ 这里由于通配符(%)在搜寻词首出现,所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况,但是一定要心中有底,通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时,优化器就能利用索引。在下面的查询中索引得到了使用:select * from employee where last_name like … Continue reading "提高mysql性能的方法!"

Removing HTML tags using mysql

Try this (ported from a T-SQL func by Robert Davis): SET GLOBAL log_bin_trust_function_creators=1;DROP FUNCTION IF EXISTS fnStripTags;DELIMITER |CREATE FUNCTION fnStripTags( Dirty varchar(4000) )RETURNS varchar(4000)DETERMINISTIC BEGIN  DECLARE iStart, iEnd, iLength int;    WHILE Locate( ‘<‘, Dirty ) > 0 And Locate( ‘>’, Dirty, Locate( ‘<‘, Dirty )) > 0 DO      BEGIN        SET iStart = Locate( ‘<‘, Dirty … Continue reading "Removing HTML tags using mysql"