注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

我的博客

 
 
 

日志

 
 

mysql 学习笔记  

2011-08-19 15:00:19|  分类: 数据库类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

mysql, 笔记, 学习

看到CSDN上,有篇日志写的不错,就转过来了,适合新手,有点像操作说明。

正文如下:


好久没有整理东西,感觉自己越来越懒,正好近期有个MYSQL的项目(一个ORACLE的应用迁移到MYSQL),以前没有接触过MYSQL,但是也知道MYSQL数据库目前广泛的应用在各种个人、商务系统中,各种技术都比较成熟。把自己学习的一些过程总结一下,该文章设计到的内容都没有做太详细的阐述,只是一个简单的入门手册,如果想看更多内容可以参看MYSQL的联机手册。

1  MYSQL安装
先下载安装包:


下载了2个版本:一个安装版;一个6.0.0非安装版。本人测试用的为安装版,安装过程不再赘述。各个操作系统平台的安装可以看MYSQL联机文档。

2  登录及一些基本操作
本章的主要目的是让我们对MYSQL的基础框架有个大概的了解。
1  连接与断开MYSQL服务器
安装完毕登陆MYSQL(有过一些其他数据库基础的人都应该很容易使用这几步):

我们可以利用如下参数查看MYSQL命令的帮助:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql –help

联接MYSQL服务器:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -h localhost -uroot -p888888
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22 to server version: -community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;

 


3 rows in set ( sec)

安装完毕,都会有这几个默认的数据库。

注意到目前没有连接到任何数据库。

mysql> SELECT DATABASE();

mysql> QUIT
Bye

 

我们也可以在连接MYSQL服务器的时候指定想要连接的数据库,如下:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --user=root -p mysql
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: -community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select database();

INFORMATION_SCHEMA数据库

我们在介绍MYSQL基本操作的同时顺便把INFORMATION_SCHEM数据库做个简单介绍:

类似其他数据库的数据字典,各个字典含义不做详述,以下摘自MYSQL联机文档:

INFORMATION_SCHEMA提供了访问数据库元数据的方式。
元数据是关于数据的数据,如数据库名或表名,列的数据类型,或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。INFORMATION_SCHEMA是信息数据库,其中保存着关于MySQL服务器所维护的所有其他数据库的信息。在INFORMATION_SCHEMA中,有数个只读表。它们实际上是视图,而不是基本表,因此,你将无法看到与之相关的任何文件。

2  使用数据库

mysql> USE INFORMATION_SCHEMA;
Database changed

mysql> SELECT DATABASE();

mysql> SELECT VERSION(), CURRENT_DATE, CURDATE(), NOW(), USER();

mysql> SHOW VARIABLES LIKE 'version';

3  MYSQL的SHOW命令

前边的例子中我们已经用过了MYSQL的SHOW命令:

mysql> SHOW DATABASES;

SHOW命令可用于获取关于INFORMATION_SCHEMA本身结构的信息。
一些SHOW语句允许使用FROM、WHERE子句,这样,在指定需要显示的行时,可更为灵活。下边给出部分例子:

mysql> SHOW TABLES FROM MYSQL;

mysql> SHOW TABLES;


SHOW TABLES命令显示了当前用数据库中的数据库对象列表,而从TABLES视图的查询我们将得到所有数据库下的对象列表。这个例子就是给出了一个查询MYSQL的表相关的系统视图,类似ORACLE中的(DBA_TABLES、USER_TABLES)和SYBASE中的SYSOBJECTS。

mysql> SELECT TABLE_NAME, TABLE_TYPE, ENGINE FROM TABLES;

mysql> SHOW COLUMNS FROM TABLES;

mysql> SHOW CHARACTER SET;
mysql> SHOW CHARACTER SET like 'big5';

mysql> SELECT * FROM COLLATIONS WHERE COLLATION_NAME LIKE '%big5%';

mysql> SHOW GRANTS;

mysql> SHOW GRANTS FOR ROOT;

我们也可以通过查询系统表来获得用户的权限:

mysql> SELECT * FROM USER_PRIVILEGES;

下边给出了MYSQL的权限列表功参考:

Privilege        Meaning
ALL [PRIVILEGES]        Sets all simple privileges except GRANT OPTION
ALTER        Enables use of ALTER TABLE
ALTER ROUTINE        Enables stored routines to be altered or dropped
CREATE        Enables use of CREATE TABLE
CREATE ROUTINE        Enables creation of stored routines
CREATE TEMPORARY TABLES        Enables use of CREATE TEMPORARY TABLE
CREATE USER        Enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW        Enables use of CREATE VIEW
DELETE        Enables use of DELETE
DROP        Enables use of DROP TABLE
EXECUTE        Enables the user to run stored routines
FILE        Enables use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
INDEX        Enables use of CREATE INDEX and DROP INDEX
INSERT        Enables use of INSERT
LOCK TABLES        Enables use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS        Enables use of SHOW FULL PROCESSLIST
REFERENCES        Not implemented
RELOAD        Enables use of FLUSH
REPLICATION CLIENT        Enables the user to ask where slave or master servers are
REPLICATION SLAVE        Needed for replication slaves (to read binary log events from the master)
SELECT        Enables use of SELECT
SHOW DATABASES        SHOW DATABASES shows all databases
SHOW VIEW        Enables use of SHOW CREATE VIEW
SHUTDOWN        Enables use of mysqladmin shutdown
SUPER        Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached
UPDATE        Enables use of UPDATE
USAGE        Synonym for “no privileges”
GRANT OPTION        Enables privileges to be granted

关于SHOW命令我们就简单介绍这么几个,如果想知道更多的SHOW命令可以得到的信息内容可以执行如下命令来获取帮助或者参看MYSQL的联机文档第23章:INFORMATION_SCHEMA信息数据库。

mysql> HELP SHOW
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION funcname
SHOW CREATE PROCEDURE procname
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW FUNCTION STATUS [LIKE 'pattern']
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW PROCEDURE STATUS [LIKE 'pattern']
SHOW [BDB] LOGS
SHOW MUTEX STATUS
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW TRIGGERS
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
SHOW WARNINGS [LIMIT [offset,] row_count]

The SHOW statement also has forms that provide information about
replication master and slave servers and are described in [HELP PURGE
MASTER LOGS]:

SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW MASTER STATUS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL `%' and `_'
wildcard characters. The pattern is useful for restricting statement
output to matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See

4  创建用户数据库

mysql> CREATE DATABASE MYTEST;
Query OK, 1 row affected ( sec)

如果想改变MYSQL数据文件的默认路径,我们可以关闭MYSQL实例,修改配置文件””或””(WINDOWS系统)中的datadir对应的参数值,然后把MYSQL默认安装的datadir下的内容拷贝到新的数据文件路径下,启动MYSQL实例这样我们再次创建数据库的时候数据文件就放在新的路径下了。

mysql> SHOW DATABASES;

mysql> USE MYTEST;
Database changed


mysql> SELECT DATABASE();

mysql> SHOW TABLES;
Empty set ( sec)

5  创建数据库用户
可以用两种方式创建MySQL账户:

1、直接操作MySQL授权表user(不推荐)

2、CREATE USER
mysql> create user test_cr identified by 'test_cr';
Query OK, 0 rows affected ( sec)

3、直接使用GRANT语句

我们可以不用第二步的CREATE语法来执行,而直接GRANT就可以创建用户。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'TEST'@'LOCALHOST' IDENTIFIED BY 'TEST' WITH GRANT OPTION;
Query OK, 0 rows affected ( sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'TEST'@'%' IDENTIFIED BY 'TEST' WITH GRANT OPTION;
Query OK, 0 rows affected ( sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON MYSQL.* TO TEST_NORMAL@'LOCALHOST' IDENTIFIED BY 'TEST_NORMAL';
Query OK, 0 rows affected ( sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON MYSQL.* TO TEST_NORMAL@'%' IDENTIFIED BY 'TEST_NORMAL';
Query OK, 0 rows affected ( sec)

mysql> SELECT USER FROM ;

注意:其中两个账户有相同的用户名TEST和密码TEST。两个账户均为超级用户账户,具有完全的权限可以做任何事情。一个账户 ('TEST'@'localhost')只用于从本机连接时。另一个账户('TEST'@'%')可用于从其它主机连接。请注意TEST的两个账户必须能从任何主机以TEST连接。没有localhost账户,当TEST从本机连接时,mysql_install_db创建的localhost的匿名用户账户将占先。
结果是,TEST将被视为匿名用户。原因是匿名用户账户的Host列值比'TEST'@'%'账户更具体,这样在user表排序顺序中排在前面。

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uTEST -p mytest
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14 to server version: -community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

注意:用户名和密码区分大小写。

mysql> SELECT DATABASE();

mysql> SELECT USER();

6  创建数据库对象
1  创建表

在测试MYSQL创建的时候还遇到了一些小问题,这里整理出来供大家参考:

mysql> CREATE TABLE TEST(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> MC VARCHAR(60),DT DATE DEFAULT NOW());
ERROR 1067 (42000): Invalid default value for 'DT'

注意: AUTO_INCREMENT为MYSQL的自增类型。我们可以利用如下函数查询最后一个序列号的值:

mysql> SELECT LAST_INSERT_ID();

本来想在创建表的时候给DT字段一个DEFAULT值(当前时间),但是出现错误,尝试了几个MYSQL的函数(如CURDATE()等)都是如此,查阅了一下资料得出结论:

MYSQL的DEFAULT值只能是常量,如果想实现上述功能只有表中第一个TIMESTAMP类型字段可以做到。可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。如果你有多个TIMESTAMP列,只有第一个自动更新。自动更新第一个TIMESTAMP列在下列任何条件下发生:
A、列没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。   
B、列没有明确地在一个UPDATE语句中指定且一些另外的列改变值。(注意一个UPDATE设置一个列为它已经有的值,这将不引起TIMESTAMP列被更新,因为如果你设置一个列为它当前的值,MySQL为了效率而忽略更改。)   
C、明确地设定TIMESTAMP列为NULL或NOW()。   

格式:TIMESTAMP[(M)]
   
MySQL可以以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD、YYMMDD格式来显示TIMESTAMP值,这主要取决于M值,它们分别为14(缺省值)\12\8\6。

mysql> CREATE TABLE TEST(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> MC VARCHAR(60),DT TIMESTAMP);
Query OK, 0 rows affected ( sec)

mysql> SHOW COLUMNS FROM TEST;

mysql> insert into test(mc) values('ZhangSan');
Query OK, 1 row affected ( sec)

mysql> select * from test;
注意:为表TEST新增加TIMESTAMP类型字段,我们发现不是表的第一个TIMESTAMP类型的字段的DEFAULT值不是系统时间。

mysql> ALTER TABLE TEST ADD RQ TIMESTAMP;
Query OK, 1 row affected ( sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM TEST;

mysql> INSERT INTO TEST(MC) VALUES('LiSi');
Query OK, 1 row affected ( sec)

 

mysql> SELECT * FROM TEST;

mysql> INSERT INTO TEST(MC,RQ) VALUES('LiSi',NOW());
Query OK, 1 row affected ( sec)

mysql> SELECT * FROM TEST;

3 rows in set ( sec)

注意:执行UPDATE操作,表中第一个TIMESTAMP字段自动修改为系统时间。

mysql> UPDATE TEST SET MC='WangWu' WHERE ID=3;
Query OK, 1 row affected ( sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM TEST;

2 创建索引

MYSQL索引类型:

1、普通索引

这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:

创建索引,例如CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表的时候指定索引,例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

2、唯一性索引

这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。唯一性索引可以用以下几种
方式创建:

创建索引,例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引,例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );

3、主键

主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列,你可能已经熟悉主键之类的概念了。
主键一般在创建表的时候指定,例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是,我们也可以通过修改表
的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

4、全文索引

MySQL从版开始支持全文索引和全文检索。在MySQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型
的列上创建。它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER
TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引,如要了解更多信息,请参见MySQL documentation。


创建MYSQL索引

mysql> create procedure p_test()
-> begin
-> declare counter int;
-> set counter = 1000;
-> while counter >= 1 do
-> insert into test(id,mc) values(counter,'test');
-> set counter = counter - 1;
-> end while;
-> end;//
Query OK, 0 rows affected ( sec)

mysql> call p_test();
-> //
Query OK, 1 row affected ( sec)

mysql> show columns from test;

mysql> select * from test where id=500;

mysql> create index idx_test on test(id);
Query OK, 1000 rows affected ( sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> show columns from test;

mysql> select * from test where id=800;

我们可以看出上边的例子创建索引前后SQL执行时间的变化(红色字体部分)。

删除索引

mysql> drop index idx_test on test;
Query OK, 3 rows affected ( sec)
Records: 3  Duplicates: 0  Warnings: 0

3 创建存储过程

MYSQL存储过程大致格式如下:

CREATE PROCEDURE procedure1                           /* name存储过程名*/
(IN parameter1 INTEGER)                              /* parameters参数*/
BEGIN                                               /* start of block语句块头*/
DECLARE variable1 CHAR(10);                           /* variables变量声明*/
IF parameter1 = 17 THEN                            /* start of IF IF条件开始*/
SET variable1 = 'birds';                        /* assignment赋值*/
ELSE
SET variable1 = 'beasts';                   /* assignment赋值*/
END IF;                                                                                                 /* end of IF IF结束*/
INSERT INTO table1 VALUES (variable1);                /* statement SQL语句*/
END                                                                                                 /* end of block语句块结束*/

首先说明一点,在MYSQL的控制台执行创建过程的脚本时,要选择一个分隔符 DELIMITER,

给出一个最简单的MYSQL存储过程示例:

mysql> DELIMITER //
mysql> CREATE PROCEDURE P()SELECT * FROM TEST; //
Query OK, 0 rows affected ( sec)

mysql> DELIMITER ;
mysql> CALL P();

给出一个带有输入输出参数的存储过程示例:

mysql> DELIMITER //
mysql> CREATE PROCEDURE P_WITH_PARA(IN PARA_IN INT,OUT PARA_OUT INT)
    -> BEGIN
    -> DECLARE PARA INT;
    -> SET PARA = 20;
    -> SET PARA_OUT = PARA_IN + PARA;
    -> END
    -> //
Query OK, 0 rows affected ( sec)

mysql> CALL P_WITH_PARA(10,@SUM);//
Query OK, 0 rows affected ( sec)

mysql> SELECT @SUM//
+------+
| @SUM |
+------+
| 30   |
+------+
1 row in set ( sec)

存储过程内容的查看:

mysql> select body from proc where name='P_WITH_PARA';

MYSQL存储过程中的一些常用控制结构:

选择结构:
IF ... THEN
        ...
ELSE
        ...
END IF;

CASE ...
WHEN ... THEN
WHEN ... THEN
END CASE;

 

循环结构:
WHILE ...
        ...
END WHILE;

LOOP_LABEL:LOOP
        ...
        ITERATE LOOP_LABEL;
        ...
        LEAVE LOOP_LABEL;
END LOOP;

REPEAT
        ...
        UNTIL ...
END REPEAT;

LABEL LABEL_NAME;
...
GOTO LABEL_NAME;

4 创建函数

各个数据库包括各种开发工具都为用户提供了创建函数的功能,这里关于函数的语法我们不再做更多解释了,给出一个例子:

mysql> delimiter //
mysql> CREATE FUNCTION fn_test (n DECIMAL(3,0))
    -> RETURNS DECIMAL(20,0)
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE v_tmp DECIMAL(20,0) DEFAULT 1;
    -> DECLARE counter DECIMAL(3,0);
    -> SET counter = n;
    -> factorial_loop: REPEAT
    -> SET v_tmp = v_tmp * counter;
    -> SET counter = counter - 1;
    -> UNTIL counter = 1
    -> END REPEAT;
    -> RETURN v_tmp;
    -> END
    -> //
Query OK, 0 rows affected ( sec)

mysql> delimiter ;
mysql> select fn_test(10);

mysql> select fn_test(3);

显示一个定义好的函数的内容:

mysql> show create function fn_test;

 

5 创建视图

给出一个创建视图的例子:

注意:下边例子红色字体部分为MYSQL数据库取前n条记录的方法,不同于其他数据库。

mysql> select count(*) from test;

mysql> create view v_test as select * from test limit 20;
Query OK, 0 rows affected ( sec)

mysql> select * from v_test;
和显示函数内容类似,我们也可以按照如下方法获得创建视图的语法内容:

mysql> show create view v_test;

删除视图

mysql> drop view v_test;
Query OK, 0 rows affected ( sec)

6 创建触发器

给出一个简单的创建触发器的例子:

mysql> delimiter //
mysql> create trigger tr_test before insert on test for each row
    -> begin
    -> insert into test1(id,mc) values(,);
    -> end
    -> //
Query OK, 0 rows affected ( sec)
mysql> delimiter ;

mysql> select count(*) from test1;

mysql> insert into test (id,mc) values(50000,'trigger_to_test1');
Query OK, 1 row affected ( sec)

mysql> select id,mc from test1;

3  MYSQL存储引擎和表类型
MYSQL支持数个存储引擎作为对不同表的类型的处理器。

mysql> SHOW ENGINES;

MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎:

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。

例如,如果你在研究大量的临时数据,你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者,你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。 这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎,可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎,以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

选择如何存储和检索你的数据的这种灵活性是MySQL为什么如此受欢迎的主要原因。其它数据库系统(包括大多数商业选择)仅支持一种类型的数据存储。遗憾的是,其它类型的数据库解决方案采取的“一个尺码满足一切需求”的方式意味着你要么就牺牲一些性能,要么你就用几个小时甚至几天的时间详细调整你的数据库。使用MySQL,我们仅需要修改我们使用的存储引擎就可以了。

默认存储引擎

MYSQL的默认存储引擎为:MyISAM,除非我们显示的指定存储引擎。如下例:

mysql> CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;
Query OK, 0 rows affected ( sec)

为了做出选择哪一个存储引擎的决定,我们首先需要考虑每一个存储引擎提供了哪些不同的核心功能。这种功能使我们能够把不同的存储引擎区别开来。我们一般把这些核心功能分为四类:支持的字段和数据类型、锁定类型、索引和处理。一些引擎具有能过促使你做出决定的独特的功能

下边我们详细讲述一下MYSQL各个存储引擎:

1  MyISAM
每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

MyISAM引擎是大多数MySQL安装程序的默认引擎,起源于早期版本MySQL支持的ISAM引擎。这种引擎提供了最佳的性能和功能的组合,尽管它缺少事务处理功能(使用InnoDB或者BDB引擎)并且使用表级锁定。

但是执行一下查询发现,我在测试的时候使用的两个测试表在创建的时候没有指定引擎,但是发现这两个表的存储引擎都为InnoDB。(当然我们修改配职文件中的default-storage-engine=INNODB来修改)。

mysql> select table_name,engine from tables where table_name like 'test%';

找了一下MYSQL文档,发现如下解释:

第15章:存储引擎和表类型:当MySQL被用MySQL配置向导安装在Windows平台上,InnoDB存储引擎替代MyISAM存储引擎作为替代,请参阅2.3.5.1节,“介绍”。


为了测试MyISAM引擎表级锁定,我们是用MyISAM引擎创建测试表TEST_ISAM。
测试中打开两个数据库连接,一个连接执行call p_tst_isam();另外一个执行单条mysql> insert into test_isam(id,mc) values(1,'1');结果在第一个连接还没有执行完的时候,第二个就完毕,没有发现MyISAM引擎锁表,这个问题我们暂时不再继续测试下去。测试中发现一个问题,MyISAM引擎的表的INSERT速度远远大于InnoDB引擎:

mysql> CREATE TABLE TEST_ISAM(ID INTEGER,MC VARCHAR(60)) ENGINE=MyISAM;       
Query OK, 0 rows affected ( sec)                                          
                                                                              
mysql> select table_name,engine from information_ where table_name like 'test%';                                                                 

创建存储过程p_test_isam

delimiter //
create procedure p_test_isam()
begin
declare counter int;
set counter = 1000000;
while counter >= 1 do
insert into test_isam(id,mc) values(counter,'test');
set counter = counter - 1;
end while;
end
//

delimiter ;

我们在以前的测试例子中: InnoDB引擎 INSERT 1000条数据花费34秒

mysql> call p_test();
Query OK, 1 row affected ( sec)

MyISAM引擎INSERT 1000000 条数据花费时间20多秒:

mysql> call p_test_isam();
Query OK, 1 row affected ( sec)

所以我们如果在使用非事物处理的表(也就是一些只有单用户使用的表)的时候可以采用MyISAM引擎来提高速度,当然了INSERT的时候可以利用MYSQL的BULK INSERT功能来出也是能大大提高性能的,这些我们将在MYSQL数据库优化一章中详细说明。BULK INSERT的语法:

INSERT INTO TEST VALUES(VAL11,VAL12),(VAL21,VAL22)……

测试完毕,翻看一下MYSQL文档,的确有下面一段话,和我们的测试结果吻合:

MyISAM管理非事务表。它提供高速存储和检索,以及全文搜索能力。MyISAM在所有MySQL配置里被支持,它是默认的存储引擎,除非你配置MySQL默认使用另外一个引擎。

2  MERGE引擎
MERGE引擎类型允许你把许多结构相同的表合并为一个表。然后,你可以执行查询,从多个表返回的结果就像从一个表返回的结果一样。每一个合并的表必须有同样的表定义。
MERGE存储引擎在下面这种使用场合会最为有用,如果需要把日志纪录不停的录入MySQL数据库,并且每天、每周或者每个月都创建一个单一的表,而且要制作来自多个表的合计查询,MERGE表这时会非常有效。然而,这项功能有局限性。你只能合并MyISAM表而且必须严格遵守相同的表定义的限制。虽然这看起来好像是一个大问题,但是,如果你使用另外一种表类型(例如InnoDB),这种合并可能就不需要了。

3  MEMORY(内存)存储引擎

MEMORY(内存)存储引擎(以前称作HEAP存储引擎)在内存中存储全部数据。一旦MySQL服务器关闭,存储在内存中的任何信息都将丢失。然而,单个表的格式将保留,使你能够创建一个用于存储信息的临时表。这样,每次数据库服务器启动时,你不需要重新创新这个表就可以快速地访问信息。
  长期使用MEMORY存储引擎一般来说不是一个好主意,因为数据很容易丢失。然而,如果你有足够的内存,使用基于MEMORY的表在大型数据集中执行复杂的查询是一种非常有效的方法,它能够很大程度的提高性能。
使用MEMORY表的最佳方法是使用一个“select”语句从你原来的基于磁盘的表中选择一个大型的数据集,然后对你需要的具体部分进一步分析那些信息。我过去曾经使用这个技术提取了一个月的网络记录数据,实际上就是从使用ARCHIVE存储引擎制作的表中提取的数据,然后对具体的URL、网站和其它重点进行查询。

  评论这张
 
阅读(303)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017