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、安装

注:以上两个链接有完整的安装方式,撸主也是参考他的安装的,安装完以后mysql.server start启动mysql服务

mysql操作

一、连接数据库

mysql  -u user -p                   例:mysql -u root -p

常见错误如下:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), it means that the MySQL server daemon (Unix) or service (Windows) is not running.

退出连接:

QUIT 或者 Ctrl+D

二、查看数据库,创建数据库,使用数据库查看数据库: show databases;

默认数据库:
             mysql - 用户权限相关数据
             test - 用于用户测试数据
             information_schema - MySQL本身架构相关数据
 
创建数据库:     
               create database db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;     # utf8编码                       create database db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # gbk编码
使用数据库:     use db1;

显示当前使用的数据库中所有表:SHOW TABLES;

三、用户管理

创建用户
    create user '用户名'@'IP地址' identified by '密码';
删除用户
    drop user '用户名'@'IP地址';
修改用户
    rename user '用户名'@'IP地址'; to '新用户名'@'IP地址'$$
修改密码
    set password for '用户名'@'IP地址' = Password('新密码')

注:用户权限相关数据保存在mysql数据库的user表中,所以也可以直接对其进行操作(不建议)

四、权限管理

mysql对于权限这块有以下限制:

all privileges          除grant外的所有权限
            select                  仅查权限
            select,insert           查和插入权限
            ...
            usage                   无访问权限
            alter                   使用alter table
            alter routine           使用alter procedure和drop procedure
            create                  使用create table
            create routine          使用create procedure
            create temporary tables 使用create temporary tables
            create user             使用create user、drop user、rename user和revoke  all privileges
            create view             使用create view
            delete                  使用delete
            drop                    使用drop table
            execute                 使用call和存储过程
            file                    使用select into outfile 和 load data infile
            grant option            使用grant 和 revoke
            index                   使用index
            insert                  使用insert
            lock tables             使用lock table
            process                 使用show full processlist
            select                  使用select
            show databases          使用show databases
            show view               使用show view
            update                  使用update
            reload                  使用flush
            shutdown                使用mysqladmin shutdown(关闭MySQL)
            super                   使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆
            replication client      服务器位置的访问
            replication slave       由复制从属使用

对于数据库及内部其他权限如下:

数据库名.*           数据库中的所有
            数据库名.表          指定数据库中的某张表
            数据库名.存储过程     指定数据库中的存储过程
            *.*                所有数据库

对于用户和IP的权限如下:

用户名@IP地址         用户只能在改IP下才能访问
            用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%表示任意)
            用户名@%             用户可以再任意IP下访问(默认IP地址为%)

1、查看权限:

show grants for '用户'@'IP地址'

2、授权

grant  权限 on 数据库.表 to   '用户'@'IP地址'

3、取消授权

revoke 权限 on 数据库.表 from '用户'@'IP地址'

授权实例如下:

grant all privileges on db1.tb1 TO '用户名'@'IP'

grant select on db1.* TO '用户名'@'IP'

grant select,insert on *.* TO '用户名'@'IP'

revoke select on db1.tb1 from '用户名'@'IP'

mysql表操作

1、查看表

show tables;                    # 查看数据库全部表

select * from 表名;             # 查看表所有内容

2、创建表

create table 表名(
    列名  类型  是否可以为空,
    列名  类型  是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8

来一个实例好详解

CREATE TABLE `tab1` (
  `nid` int(11) NOT NULL auto_increment,                   # not null表示不能为空,auto_increment表示自增
  `name` varchar(255) DEFAULT zhangyanlin,                 # default 表示默认值
  `email` varchar(255),
  PRIMARY KEY (`nid`)                                      # 把nid列设置成主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注:

  • 默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值
  • 自增,如果为某列设置自增列,插入数据时无需设置此列,默认将自增(表中只能有一个自增列)注意:1、对于自增列,必须是索引(含主键)2、对于自增可以设置步长和起始值
  • 主键,一种特殊的唯一索引,不允许有空值,如果主键使用单个列,则它的值必须唯一,如果是多列,则其组合必须唯一。

3、删除表

drop table 表名

3、清空表内容

delete from 表名
truncate table 表名

4、修改表

添加列:   alter table 表名 add 列名 类型
删除列:   alter table 表名 drop column 列名
修改列:
          alter table 表名 modify column 列名 类型;  -- 类型
          alter table 表名 change 原列名 新列名 类型; -- 列名,类型
  
添加主键:
          alter table 表名 add primary key(列名);
删除主键:
          alter table 表名 drop primary key;
          alter table 表名  modify  列名 int, drop primary key;
  
添加外键: alter table 从表 add constraint 外键名称(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);
删除外键: alter table 表名 drop foreign key 外键名称
  
修改默认值:ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值:ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

对于上述这些操作是不是看起来很麻烦,很浪费时间,别慌!有专门的软件能提供这些功能,操作起来非常简单,这个软件名字叫Navicat Premium ,大家自行在网上下载,练练手,但是下面的即将讲到表内容操作还是建议自己写命令来进行

5、基本数据类型

MySQL的数据类型大致分为:数值、时间和字符串

bit[(M)]
            二进制位(101001),m表示二进制位的长度(1-64),默认m=1

        tinyint[(m)] [unsigned] [zerofill]

            小整数,数据类型用于保存一些范围的整数数值范围:
            有符号:
                -128 ~ 127.
            无符号:
                0 ~ 255

            特别的: MySQL中无布尔值,使用tinyint(1)构造。

        int[(m)][unsigned][zerofill]

            整数,数据类型用于保存一些范围的整数数值范围:
                有符号:
                    -2147483648 ~ 2147483647
                无符号:
                    0 ~ 4294967295

            特别的:整数类型中的m仅用于显示,对存储范围无限制。例如: int(5),当插入数据2时,select 时数据显示为: 00002

        bigint[(m)][unsigned][zerofill]
            大整数,数据类型用于保存一些范围的整数数值范围:
                有符号:
                    -9223372036854775808 ~ 9223372036854775807
                无符号:
                    0  ~  18446744073709551615

        decimal[(m[,d])] [unsigned] [zerofill]
            准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。

            特别的:对于精确数值计算时需要用此类型
                   decaimal能够存储精确值的原因在于其内部按照字符串存储。

        FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
            单精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。
                无符号:
                    -3.402823466E+38 to -1.175494351E-38,
                    0
                    1.175494351E-38 to 3.402823466E+38
                有符号:
                    0
                    1.175494351E-38 to 3.402823466E+38

            **** 数值越大,越不准确 ****

        DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
            双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。

                无符号:
                    -1.7976931348623157E+308 to -2.2250738585072014E-308
                    0
                    2.2250738585072014E-308 to 1.7976931348623157E+308
                有符号:
                    0
                    2.2250738585072014E-308 to 1.7976931348623157E+308
            **** 数值越大,越不准确 ****


        char (m)
            char数据类型用于表示固定长度的字符串,可以包含最多达255个字符。其中m代表字符串的长度。
            PS: 即使数据小于m长度,也会占用m长度
        varchar(m)
            varchars数据类型用于变长的字符串,可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度,只要长度小于该最大值的字符串都可以被保存在该数据类型中。

            注:虽然varchar使用起来较为灵活,但是从整个系统的性能角度来说,char数据类型的处理速度更快,有时甚至可以超出varchar处理速度的50%。因此,用户在设计数据库时应当综合考虑各方面的因素,以求达到最佳的平衡

        text
            text数据类型用于保存变长的大字符串,可以组多到65535 (2**16 − 1)个字符。

        mediumtext
            A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

        longtext
            A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.


        enum
            枚举类型,
            An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
            示例:
                CREATE TABLE shirts (
                    name VARCHAR(40),
                    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
                );
                INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

        set
            集合类型
            A SET column can have a maximum of 64 distinct members.
            示例:
                CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
                INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

        DATE
            YYYY-MM-DD(1000-01-01/9999-12-31)

        TIME
            HH:MM:SS('-838:59:59'/'838:59:59')

        YEAR
            YYYY(1901/2155)

        DATETIME

            YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59    Y)

        TIMESTAMP

            YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)

mysql表内容操作

表内容操作无非就是增删改查,当然用的最多的还是查,而且查这一块东西最多,用起来最难,当然对于大神来说那就是so easy了,对于我这种小白还是非常难以灵活运用的,下面咱来一一操作一下

1、增

insert into 表 (列名,列名...) values (值,值,...)
insert into 表 (列名,列名...) values (值,值,...),(值,值,值...)
insert into 表 (列名,列名...) select (列名,列名...) from 表
例:
    insert into tab1(name,email) values('zhangyanlin','[email protected]')

2、删

delete from 表                                      # 删除表里全部数据
delete from 表 where id=1 and name='zhangyanlin'   # 删除ID =1 和name='zhangyanlin' 那一行数据

3、改

update 表 set name = 'zhangyanlin' where id>1

4、查

select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1

查这块的条件太多太多我给列举出来至于组合还得看大家的理解程度哈

a、条件判断where

select * from 表 where id > 1 and name != 'aylin' and num = 12;
    select * from 表 where id between 5 and 16;
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)

b、通配符like

select * from 表 where name like 'zhang%'  # zhang开头的所有(多个字符串)
    select * from 表 where name like 'zhang_'  # zhang开头的所有(一个字符)

c、限制limit

select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行

d、排序asc,desc

select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序

e、分组group by

select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
    select num from 表 group by num having max(id) > 10
 
    特别的:group by 必须在where之后,order by之前

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
possible_keys: idx_toprank_pid,idx_toprank_chg
          KEY: idx_toprank_pid
      key_len: 99
          REF: const
         ROWS: 2477
        Extra: USING WHERE; USING filesort
1 ROW IN SET (0.00 sec)

正如我们所料,这个查询用到了建立在partition_by1这个字段上的索引,匹配的目标行数为2477,然后还有一个在added_quantity3字段上的排序。由于added_quantity3没有索引,所以用到了filesort。

我们再看一下这条SQL在归档表上的EXPLAIN结果:

mysql> EXPLAIN
    -&gt; SELECT ... FROM  mysqlslap.rpt_topranks_v3_<strong>archive</strong>
    -&gt; WHERE ... AND partition_by1 = '50008090'
    -&gt; ORDER BY added_quantity3 DESC
    -&gt; LIMIT 500\G
*************************** 1. ROW ***************************
           id: 1
  select_type: SIMPLE
        TABLE: rpt_topranks_v3_archive
         TYPE: ALL
possible_keys: NULL
          KEY: NULL
      key_len: NULL
          REF: NULL
         ROWS: 2424753
        Extra: USING WHERE; USING filesort
1 ROW IN SET (0.00 sec)

EXPLAIN说:“我没有索引可用,所以只能全表扫描2424753行记录,然后再来个filesort。”你要追求性能,那显然是委屈MySQL了。

扩展阅读:

关于MYSQL的ORDER BY实现原理,请参考 http://isky000.com/database/mysql_order_by_implement

 

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

数据魔方需要的数据,一旦写入就很少或者根本不会更新。这种数据非常适合压缩以降低磁盘占用。MySQL本身提供了两种压缩方式——archive引擎以及针对MyISAM引擎的myisampack方式。今天对这两种方式分别进行了测试,对比了二者在磁盘占用以及查询性能方面各自的优劣。至于为什么做这个,你们应该懂的,我后文还会介绍。且看正文:

1. 测试环境:

  1. 软硬件
  2. 一台 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对测试结果造成干扰。

  3. 表结构
  4. 2424753条记录,生产环境某一个分片的实际数据;

    分别建立了(partition_by1,idx_rank) 和 (partition_by1,chg_idx)的联合索引,其中partition_by1为32长度的varchar类型,用于检索;其余两个字段均为浮点数,多用于排序;

    autokid作为子增列,充当PRIMARY KEY,仅作为数据装载时原子性保证用,无实际意义。

2. 测试目的:

  1. 压缩空间对比
  2. 压缩率越大,占用的磁盘空间越小,直接降低数据的存储成本;

  3. 查询性能对比
  4. 压缩后查询性能不应该有显著降低。Archive是不支持索引的,因此性能降低是必然的,那么我们也应该心里有个谱,到底降低了多少,能不能接受。

3. 测试工具:

  1. mysqlslap
  2. 官方的工具当然是不二之选。关于mysqlslap的介绍请参考 官方文档

  3. 测试query
  4. 截取生产环境访问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 2.308 30 15 50
ARCHIVE 75630745 >300 75 4 1
PACK 99302109 2.596 30 22 50

根据上面的表格给出的测试数据,我们简单得出以下结论:

  1. 针对测试表,Archive表占用空间约为之前的18.7%,myisampack后空间占用约为之前的24.6%;二者相差不多,单纯从空间利用情况来看,我们似乎需要选择archive表;
  2. 我们再看查询性能,与基准表进行对比。无论在总耗时还是系统负载方面,50并发下的pack表查询性能与基准表相当;而archive表在单并发情况下耗时超过了5分钟(实在等不了了,kill之)!

那么,我们似乎可以得出结论,针对需要在线查询的表,ARCHIVE引擎基本上可以不考虑了。

在下一篇文章中,我将会详细为什么这个测试过程中ARCHIVE引擎如此地慢。

 

五款常用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)

输出报表的内容排序(sorted by)

最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数.



Count, sql的执行次数及占总的slow log数量的百分比.

Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.

95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.

Lock Time, 等待锁的时间.

95% of Lock , 95%的慢sql等待锁时间.

Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.
Rows examined, 扫描的行数量.

Database, 属于哪个数据库

Users, 哪个用户,IP, 占到所有用户执行的sql百分比



Query abstract, 抽象后的sql语句

Query sample, sql语句



除了以上的输出, 官方还提供了很多定制化参数, 是一款不可多得的好工具.



mysql-explain-slow-log, 德国人写的一个perl脚本.
http://www.willamowius.de/mysql-tools.html







功能上有点瑕疵, 不仅把所有的 slow log 打印到屏幕上, 而且统计也只有数量而已. 不推荐使用.

mysql-log-filter, google code上找到的一个分析工具.提供了 python 和 php 两种可执行的脚本.
http://code.google.com/p/mysql-log-filter/


功能上比官方的mysqldumpslow, 多了查询时间的统计信息(平均,最大, 累计), 其他功能都与 mysqldumpslow类似.
特色功能除了统计信息外, 还针对输出内容做了排版和格式化, 保证整体输出的简洁. 喜欢简洁报表的朋友, 推荐使用一下.

myprofi, 纯php写的一个开源分析工具.项目在 sourceforge 上.
http://myprofi.sourceforge.net/


功能上, 列出了总的慢查询次数和类型, 去重后的sql语句, 执行次数及其占总的slow log数量的百分比.
从整体输出样式来看, 比mysql-log-filter还要简洁. 省去了很多不必要的内容. 对于只想看sql语句及执行次数的用户来说, 比较推荐.


总结








































工具/功能 一般统计信息 高级统计信息 脚本 优势
mysqldumpslow 支持 不支持 perl mysql官方自带
mysqlsla 支持 支持 perl 功能强大,数据报表齐全,定制化能力强.
mysql-explain-slow-log 支持 不支持 perl
mysql-log-filter 支持 部分支持 python or php 不失功能的前提下,保持输出简洁
myprofi 支持 不支持 php 非常精简

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 granularity


Table


Table


Page (8 KB)


Row


Storage


Split files


In-memory


Single file per table


Tablespace(s)


Isolation levels


None


None


Read committed


All


Portable format


Yes


N/A


No


Yes


Referential integrity


No


No


No


Yes


Primary key with data


No


No


Yes


Yes


MySQL caches data records


No


Yes


Yes


Yes


Availability


All versions


All versions


MySQL-Max


All Versions


 


提高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 employss
where
first_name||”||last_name =’Beill Cliton’

上面这条语句完全可以查询出是否有Bill Cliton这个员工,但是这里需要注意,系统优化器对基于last_name创建的索引没有使用。

当采用下面这种SQL语句的编写,Oracle系统就可以采用基于last_name创建的索引。

Select * from employee
where
first_name =’Beill’ and last_name =’Cliton’

遇到下面这种情况又如何处理呢?如果一个变量(name)中存放着Bill Cliton这个员工的姓名,对于这种情况我们又如何避免全程遍历,使用索引呢?可以使用一个函数,将变量name中的姓和名分开就可以了,但是有一点需要注意,这个函数是不能作用在索引列上。下面是SQL查询脚本:

select * from employee
where
first_name = SUBSTR(‘&&name’,1,INSTR(‘&&name’,’ ‘)-1)
and
last_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 ‘c%’

4. Order by语句

ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达式。

5. NOT

我们在查询时经常在where子句使用一些逻辑表达式,如大于、小于、等于以及不等于等等,也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子:

… where not (status =’VALID’)

如果要使用NOT,则应在取反的短语前面加上括号,并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中,这就是不等于(<&gt$$)运算符。换句话说,即使不在查询where子句中显式地加入NOT词,NOT仍在运算符中,见下例:

… where status <&gt$$’INVALID’

再看下面这个例子:

select * from employee where salary<&gt$$3000;

对这个查询,可以改写为不使用NOT:

select * from employee where salary<3000 or salary&gt$$3000;

虽然这两种查询的结果一样,但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引,而第一种查询则不能使用索引。

6. IN和EXISTS

有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。

第一种格式是使用IN操作符:

… where column in(select * from … where …);

第二种格式是使用EXIST操作符:

… where exists (select ‘X’ from …where …);

我相信绝大多数人会使用第一种格式,因为它比较容易编写,而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。

第二种格式中,子查询以‘select ‘X’开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作(这里假定在where语句中使用的列存在索引)。相对于IN子句来说,EXISTS使用相连子查询,构造起来要比IN子查询困难一些。

通过使用EXIST,Oracle系统会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间。Oracle系统在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。

同时应尽可能使用NOT EXISTS来代替NOT IN,尽管二者都使用了NOT(不能使用索引而降低速度),NOT EXISTS要比NOT IN查询效率更高。

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 ), iEnd = Locate( ‘>’, Dirty, Locate(‘<‘, Dirty ));
        SET iLength = ( iEnd – iStart) + 1;
        IF iLength > 0 THEN
          BEGIN
            SET Dirty = Insert( Dirty, iStart, iLength, ”);
          END;
        END IF;
      END;
    END WHILE;
    RETURN Dirty;
END;
|
DELIMITER ;
SELECT fnStripTags(‘this <html>is <b>a test</b>, nothing more</html>’);


Exploring Mysql CURDATE and NOW. The same but different.


Sometimes I see people attempting to use VARCHARS or CHARS to store dates in their MySQL database application. This is really fighting against MySQL, which has a variety of interchangeable date types. Internally MySQL is storing dates as numbers, which allows it to do all sorts of nice arithmetic and comparisons with very little effort on your part. For example, when you use a mysql DATE column to store a date, you don’t have to worry about sortation, or comparing that date to another DATE, because MySQL already understands how to do those things internally. A lot of people also don’t realize that they can output a DATE column in just about any way they choose using the DATE_FORMAT function. This causes people to shy away from using DATE, DATETIME, TIME, or TIMESTAMP columns, when they really should.


We can break down mysql DATE columns into 3 categories:

DATE: Has no Time component.

DATETIME and TIMESTAMP: Has both Date and Time component.

TIME: Has only Time component.


I tend to use DATETIME most often, and occassionally will use TIMESTAMP. I’ll talk about TIMESTAMP another day, and explain the pros and cons of using it.

In this entry I’m going to concentrate on two very important MySQL date functions: CURDATE() and NOW(). Take a look at these examples, and keep in mind that the semicolons are added to terminate the SQL statements as you would have to if you were using the mysql client application. If you’re using phpMyAdmin you don’t need the semicolons.


SELECT CURDATE();



CODE:

+————+
| CURDATE()  |
+————+
| 2004-11-30 |
+————+
1 row in set (0.00 sec)


CURDATE() returns you the date part of the mysql server’s datetime. The server gets this from the operating system, so basically it’s whatever the Date/Time is on the machine that is running your mysql server.

Notice that CURDATE() as its name implies has no TIME component. Let’s assume that what your application needs to do is find out what date it was “yesterday”. If Time really isn’t important, then CURDATE is the way to go, as it’s not concerned with TIME.

Although it’s not that intuitive, the way to get “YESTERDAY” is to use the DATE_ADD() function. The INTERVAL component allows us to Add a -1 DAY


SELECT DATE_ADD(CURDATE(), INTERVAL1 DAY);



CODE:

+————————————–+
| DATE_ADD(CURDATE(), INTERVAL -1 DAY) |
+————————————–+
| 2004-11-29                           |
+————————————–+
1 row in set (0.00 sec)


This is not to say that you can’t then use the DATE to compare against a DATETIME. You can! What happens is that mysql assumes for comparison purposes that your DATE is the equivalent to the “first second of that day” or that day at 12:00 am.

This little experiment illustrates the idea.


CODE:

mysql> create table onedate (onedate DATETIME);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into onedate VALUES(NOW());
Query OK, 1 row affected (0.00 sec)

mysql> select * from onedate where onedate >= CURDATE();
+———————+
| onedate             |
+———————+
| 2004-11-30 17:52:01 |
+———————+
1 row in set (0.00 sec)


Notice that a row was returned, because CURDATE() will always be less than or equal to NOW().

Compare CURDATE() with NOW(). Like CURDATE(), NOW() returns you the system DATE but also includes the time component. If you need TIME in your application, tracking logins, or the date and time a message was entered by a user, then you need a mysql DATETIME rather than a MYSQL date, or you will not be able to capture the important time component.


SELECT * FROM onedate WHERE onedate >= CURDATE();



CODE:

+———————+
| NOW()               |
+———————+
| 2004-11-30 18:00:34 |
+———————+
1 row in set (0.00 sec)


Notice the 24 Hour time component.

Putting a nail in the issue, here’s the same DATE_ADD function called against NOW().


SELECT NOW(), DATE_ADD(NOW(), INTERVAL1 DAY);



CODE:

+———————+———————————-+
| NOW()               | DATE_ADD(NOW(), INTERVAL -1 DAY) |
+———————+———————————-+
| 2004-11-30 18:07:17 | 2004-11-29 18:07:17              |
+———————+———————————-+
1 row in set (0.00 sec)


What we get is exactly the same time, yesterday.

The intrinsic DATE types in a relational database are always the way to go when you need to handle date and time in your application. Let the database do the heavy lifting, and you will make your application faster, more reliable and easier to maintain in the long run.