DBA logs

2009年10月22日星期四

数据库基础--索引

From baidu baike

使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，例如 employee 表的姓（lname）列。如果要按姓查找特定职员，与必须搜索表中的所有行相比，索引会帮助您更快地获得该信息。
　　索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
　　索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似：它搜索索引以找到特定值，然后顺指针找到包含该值的行。
　　在数据库关系图中，您可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。
　　注意并非所有的数据库都以相同的方式使用索引。作为通用规则，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引占用磁盘空间，并且降低添加、删除和更新行的速度。在多数情况下，索引用于数据检索的速度优势大大超过它的不足之处。但是，如果应用程序非常频繁地更新数据或磁盘空间有限，则可能需要限制索引的数量。
　　可以基于数据库表中的单列或多列创建索引。多列索引使您可以区分其中一列可能有相同值的行。
　　如果经常同时搜索两列或多列或按两列或多列排序时，索引也很有帮助。例如，如果经常在同一查询中为姓和名两列设置判据，那么在这两列上创建多列索引将很有意义。
　　确定索引的有效性：
　　检查查询的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以选择的对象。
　　对新索引进行试验以检查它对运行查询性能的影响。
　　考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。
　　检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引。
　　检查某列中唯一数据值的数量，并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性，这有助于确定该列是否适合建立索引，如果适合，确定索引的类型。
　　
建立索引的优点
　　1.大大加快数据的检索速度;
　　2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
　　3.加速表和表之间的连接;
　　4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
　　
索引的缺点
　　1.索引需要占物理空间。
　　2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

索引列
　　
　　可以基于数据库表中的单列或多列创建索引。多列索引使您可以区分其中一列可能有相同值的行。
　　
索引类型
　　根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息，请参见数据库文档。
　　提示尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。有关这些约束的更多信息，请参见主键约束和唯一约束。
　　唯一索引
　　唯一索引是不允许其中任何两行具有相同索引值的索引。
　　当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。
　　有关唯一索引的更多信息，请参见创建唯一索引。
　　主键索引
　　数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。
　　在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。有关主键的更多信息，请参见定义主键。
　　聚集索引
　　在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。
　　如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

2009年9月24日星期四

单机体验MySQL Cluster

mkdir /var/lib/mysql-cluster/

download package: mysql-cluster-gpl-7.0.7-linux-i686-glibc23.tar.gz

tar zcf mysql-cluster-gpl-7.0.7-linux-i686-glibc23.tar.gz
mv mysql-cluster-gpl-7.0.7-linux-i686-glibc23 7_0_7
chown -R mysql.mysql 7_0_7
cd 7_0_7/data
mkdir data1 data2 data3
mkdir data1/mysql data1/test data2/mysql data2/test data3/mysql data3/test
cd ..
scripts/mysql_install_db --basedir=/var/lib/mysql-cluster/7_0_7 --datadir=/var/lib/mysql-cluster/7_0_7/data/data1

scripts/mysql_install_db --basedir=/var/lib/mysql-cluster/7_0_7 --datadir=/var/lib/mysql-cluster/7_0_7/data/data2

scripts/mysql_install_db --basedir=/var/lib/mysql-cluster/7_0_7 --datadir=/var/lib/mysql-cluster/7_0_7/data/data3

cd /var/lib/mysql-cluster/7_0_7
mkdir conf ; cd conf

::::::::::::::
config.ini
::::::::::::::
[ndbd default]
noofreplicas=2

[ndbd]
hostname=localhost
id=2

[ndbd]
hostname=localhost
id=3

[ndb_mgmd]
id = 1
hostname=localhost

[mysqld]
id=4
hostname=localhost

[mysqld]
id=5
hostname=localhost

[mysqld]
id=6
hostname=localhost

::::::::::::::
my.1.conf
::::::::::::::
[mysqld]
ndb-nodeid=4
ndbcluster=true
datadir=/var/lib/mysql-cluster/7_0_7/data/data1
basedir=/var/lib/mysql-cluster/7_0_7
port=3306
server-id=1
#log-bin
::::::::::::::
my.2.conf
::::::::::::::
[mysqld]
ndb-nodeid=5
ndbcluster=true
datadir=/var/lib/mysql-cluster/7_0_7/data/data2
basedir=/var/lib/mysql-cluster/7_0_7
port=3307
server-id=2
log-bin

::::::::::::::
my.3.conf
::::::::::::::
[mysqld]
ndb-nodeid=6
ndbcluster=true
datadir=/var/lib/mysql-cluster/7_0_7/data/data3
basedir=/var/lib/mysql-cluster/7_0_7
port=3308
server-id=3
log-bin

cd /var/lib/mysql-cluster/7_0_7
bin/ndb_mgmd --initial -f conf/config.ini --configdir=/var/lib/mysql-cluster/7_0_7/conf

bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from localhost)
id=3 (not connected, accepting connect from localhost)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @localhost (mysql-5.1.35 ndb-7.0.7)

[mysqld(API)] 3 node(s)
id=4 (not connected, accepting connect from localhost)
id=5 (not connected, accepting connect from localhost)
id=6 (not connected, accepting connect from localhost)

ndb_mgm> quit

bin/ndbd --initial -c localhost:1186
bin/ndbd --initial -c localhost:1186

bin/ndbd --initial -c localhost:1186
2009-08-31 00:19:36 [ndbd] INFO -- Configuration fetched from 'localhost:1186', generation: 1
bin/ndbd --initial -c localhost:1186
2009-08-31 00:19:39 [ndbd] INFO -- Configuration fetched from 'localhost:1186', generation: 1

bin/mysqld --defaults-file=conf/my.1.conf --user=mysql &
bin/mysqld --defaults-file=conf/my.2.conf --user=mysql &
bin/mysqld --defaults-file=conf/my.3.conf --user=mysql &

[root@centOS02 conf]# netstat -ultn | grep 330
tcp 0 0 :::3306 :::* LISTEN
tcp 0 0 :::3307 :::* LISTEN
tcp 0 0 :::3308 :::* LISTEN

bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0, Master)
id=3 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)

[mysqld(API)] 3 node(s)
id=4 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)
id=5 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)
id=6 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)

ndb_mgm> quit
[root@centOS02 7_0_7]# bin/mysql -h localhost -P 3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.35-ndb-7.0.7-cluster-gpl-log MySQL Cluster Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table assets (name varchar(30) not null primary key,
-> value int) engine=ndb;
090831 0:23:33 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/assets
090831 0:23:33 [Note] NDB Binlog: logging ./test/assets (UPDATED,USE_WRITE)
090831 0:23:33 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$test/assets
090831 0:23:33 [Note] NDB Binlog: logging ./test/assets (UPDATED,USE_WRITE)
Query OK, 0 rows affected (1.98 sec)

mysql> insert into assets values ('Car','1900');
Query OK, 1 row affected (0.16 sec)

mysql> select * from assets;
+------+-------+
| name | value |
+------+-------+
| Car | 1900 |
+------+-------+
1 row in set (0.05 sec)

mysql> quit
Bye
[root@centOS02 7_0_7]# bin/mysql -h localhost -P 3307
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.35-ndb-7.0.7-cluster-gpl-log MySQL Cluster Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> select * from assets;
+------+-------+
| name | value |
+------+-------+
| Car | 1900 |
+------+-------+
1 row in set (0.00 sec)

mysql>

2009年8月26日星期三

oracle 用户密码为何不可以以数字开头

哪位了解详解的，来说说

xtrabackup - for innodb backup and restore

From Percona

Install:
$tar xzf xtrabackup-0.7.tar.gz
$cd xtrabackup-0.7
$./configure --innodb_file_io_threads=5 --innodb_file_per_table --innodb_force_recovery=4 --innodb_open_files=4096
$make
$cd innobase/xtrabackup/
$make

export PATH=$PATH:/usr/local/src/xtrabackup-0.7/innobase/xtrabackup

bakcup all databases:

innobackupex-1.5.1 --user=root --stream=tar /data/mysqlbak/ | gzip > /data/mysqlbak/bak`date +%F`_mysql.tar.gz

restore :

/etc/init.d/mysqld stop

tar zxvfi /data/mysqlbak/bak`date +%F`_mysql.tar.gz -C /data/mysql3306

/etc/init.d/mysqld start

verify

Note:
---> root@centOS01 (0.08) ^_^ # innobackupex-1.5.1 --help

Usage:
innobackup [--sleep=MS] [--compress[=LEVEL]] [--include=REGEXP] [--user=NAME]
[--password=WORD] [--port=PORT] [--socket=SOCKET] [--no-timestamp]
[--ibbackup=IBBACKUP-BINARY] [--slave-info] [--stream=tar]
[--defaults-file=MY.CNF]
[--databases=LIST] [--remote-host=HOSTNAME] BACKUP-ROOT-DIR
innobackup --apply-log [--use-memory=MB] [--uncompress] [--defaults-file=MY.CNF]
[--ibbackup=IBBACKUP-BINARY] BACKUP-DIR
innobackup --copy-back [--defaults-file=MY.CNF] BACKUP-DIR

--apply-log Prepare a backup for starting mysql server on the backup.
Expand InnoDB data files as specified in
backup-dir/backup-my.cnf, using backup-dir/ibbackup_logfile,
and create new log files as specified in
backup-dir/backup-my.cnf.

--copy-back Copy data and index files from backup directory back to
their original locations.

--remote-host=HOSTNAME
If this option is specified, backup files will be created
at the remote host by using ssh connection..

--include=REGEXP
This option is passed to the ibbackup child process.
It tells ibbackup to backup only those per-table data
files which match the given regular expression. For
each table with a per-table data file a string of the
form db_name.table_name is checked against the regular
expression. If the regular expression matches the
complete string db_name.table_name, the table is
included in the backup. The regular expression should
be of the POSIX 1003.2 "extended" form.
Try 'ibbackup --help' for more details on this option.

--databases=LIST
This option is used to specify the list of databases that
innobackup should backup. The list is of the form
"db_name[.table_name] db_name1[.table_name1] ...".
If this option is not specified all databases containing
MyISAM and InnoDB tables will be backed up.
Please make sure that --databases contains all of the
innodb databases & tables so that all of the innodb .frm
files are also backed up. In case the list is very long,
this can be specified in a file and the full path of the
file can be specified instead of the list.

--user=NAME This option is passed to the mysql child process.
It defines the user for database login if not current user.
Try 'mysql --help' for more details on this option.

--password=WORD
This option is passed to the mysql child process.
It defines the password to use when connecting to database.
Try 'mysql --help' for more details on this option.

--port=PORT This option is passed to the mysql child process.
It defines the port to use when connecting to local database
server with TCP/IP.
Try 'mysql --help' for more details on this option.

--slave-info
This option is useful when backing up a replication
slave server. It prints the binary log position and
name of the binary log file of the master server.
It also writes this information to the 'ibbackup_slave_info'
file as a 'CHANGE MASTER' command. A new slave for this
master can be set up by starting a slave server on this
backup and issuing a 'CHANGE MASTER' command with the binary
log position saved in the 'ibbackup_slave_info' file.

--socket=SOCKET
This option is passed to the mysql child process.
It defines the socket to use when connecting to local database
server with UNIX domain socket.
Try 'mysql --help' for more details on this option.

2009年7月24日星期五

how import an excel data into mysql safely

手上来了个任务，对一些表更新，更新相应的字段都存放在excel中，如果是直接更新相应的字段倒好办，直接拼SQL就可以了，却要关联到其他二个表

自然想到把文件内容导入到 database，再处理会相对快些
and do :open the excel file and save as csv file ,and load data local infile 'x' into table tbname fields terminated by ',' lines terminated by '\r\n' ignore 1 lines;
Unfortunately-^-^这个单词拼了半天，最后还是GG了，呵
sth error,there's many common in the last column

such as
1, 2003 ,44445, http:// ,here is ok
3 ,2002 ,45645, http:// ,here has, some ,

主要过程如下：
step 1:copy excel content into editplus or ue
Note: charset
step 2:change ' " ... 为 \' \"
step 3:tab -> ","
step 4: ^(reg)->(" $(reg)->"),
step 5: set names utf8;
drop table if exists tmp;
create table tmp
(
ChID int,
CID int,
word varchar(255),
index(ChID),
index(CID),
index(Kword)
);
select "start to insert word",now();
insert into tmp values

mysql < filename

END

如果你的文件比较规范，或是没有特殊字符，不妨考虑下第一种方法，另外注意一些项
如果table是myisam
mysql> show variables like 'con%insert';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| concurrent_insert | 1 |
+-------------------+-------+
1 row in set (0.00 sec)

如果table是innodb
mysql> show variables like '%checks%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
| innodb_checksums | ON |
| unique_checks | ON |
+--------------------+-------+
3 rows in set (0.00 sec)

good luck

订阅：博文 (Atom)