sql学习笔记（18）-----------数据库创建过程，创建数据库的sql语句
手动创建数据库的步骤： 第一步：决定数据库实例的SID 数据库实例的SID用来将当前实例和以后可能创建的实例进行区分 % setenv ORACLE_SID mynewdb 第二步：建立数据库管理员认证方法 第三步：创建初始化参数文件 实例（由内存结构SAG和后台进程组成）启动过程中要读取初始化参数文件。得到初始化参数文件的一个好办法是修改一个已有的初始化参数文件。为了简化操作，将初始化参数文件放在Oracle默认的位置上，这样的话，当数据库启动时，就没有必要再指定pfile参数了，因为Oracle会自动寻找在默认位置的初始化参数文件。 Platform Default Name Default Location UNIX init$ORACLE_SID.ora $ORACLE_HOME/dbs Windows init$ORACLE_SID.ora $ORACLE_HOME/database 如果一个数据库的名字是mynewdb，下面就是该数据库的初始化文件： # Cache and I/O DB_BLOCK_SIZE=4096 DB_CACHE_SIZE=20971520 # Cursors and Library Cache CURSOR_SHARING=SIMILAR OPEN_CURSORS=300 # Diagnostics and Statistics BACKGROUND_DUMP_DEST=/vobs/oracle/admin/mynewdb/bdump CORE_DUMP_DEST=/vobs/oracle/admin/mynewdb/cdump TIMED_STATISTICS=TRUE USER_DUMP_DEST=/vobs/oracle/admin/mynewdb/udump # Control File Configuration CONTROL_FILES=("/vobs/oracle/oradata/mynewdb/control01.ctl", "/vobs/oracle/oradata/mynewdb/control02.ctl", "/vobs/oracle/oradata/mynewdb/control03.ctl") # Archive LOG_ARCHIVE_DEST_1='LOCATION=/vobs/oracle/oradata/mynewdb/archive' LOG_ARCHIVE_FORMAT=%t_%s.dbf LOG_ARCHIVE_START=TRUE # Shared Server # Uncomment and use first DISPATCHES parameter below when your listener is # configured for SSL # (listener.ora and sqlnet.ora) # DISPATCHERS = "(PROTOCOL=TCPS)(SER=MODOSE)", # "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)" DISPATCHERS="(PROTOCOL=TCP)(SER=MODOSE)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)", (PROTOCOL=TCP) # Miscellaneous COMPATIBLE=9.2.0 DB_NAME=mynewdb # Distributed, Replication and Snapshot DB_DOMAIN=us.oracle.com REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE # Network Registration INSTANCE_NAME=mynewdb # Pools JAVA_POOL_SIZE=31457280 LARGE_POOL_SIZE=1048576 SHARED_POOL_SIZE=52428800 # Processes and Sessions PROCESSES=150 # Redo Log and Recovery FAST_START_MTTR_TARGET=300 # Resource Manager RESOURCE_MANAGER_PLAN=SYSTEM_PLAN # Sort, Hash Joins, Bitmap Indexes SORT_AREA_SIZE=524288 # Automatic Undo Management UNDO_MANAGEMENT=AUTO UNDO_TABLESPACE=undotbs 第四步：连接到实例 $ SQLPLUS /nolog CONNECT SYS/password AS SYSDBA 第五步：启动实例 不mount数据库，仅仅启动实例。通常，只是在创建和维护数据库的时候才这样做。 STARTUP NOMOUNT 此时，数据库并不存在，只有SGA和后台进程被创建。 第六步：调用create database语句 CREATE DATABASE mynewdb USER SYS IDENTIFIED BY pz6r58 USER SYSTEM IDENTIFIED BY y1tz5p LOGFILE GROUP 1 ('/vobs/oracle/oradata/mynewdb/redo01.log') SIZE 100M, GROUP 2 ('/vobs/oracle/oradata/mynewdb/redo02.log') SIZE 100M, GROUP 3 ('/vobs/oracle/oradata/mynewdb/redo03.log') SIZE 100M MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 MAXINSTANCES 1 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 DATAFILE '/vobs/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE EXTENT MANAGEMENT LOCAL DEFAULT TEMPORARY TABLESPACE tempts1 DATAFILE '/vobs/oracle/oradata/mynewdb/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE undotbs DATAFILE '/vobs/oracle/oradata/mynewdb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED; 数据库创建之后，数据库名称是mynewdb,它的全局数据库名是mynewdb.us.oracle.com。控制文件通过初始化参数CONTROL_FILES来创建。SYS用户的密码是pz6r58，SYSTEM用户的密码是y1tz5p。这两个子句在Oracle9i中是可选，但是如果设定了任何一个密码，那么两个密码都必须设定。该数据库有三个在线重做日志组，通过LOGFILE子句来指定，MAXLOGFILES, MAXLOGMEMBERS, and MAXLOGHISTORY都是对日志文件的限制。MAXDATAFILES指定了数据库可以打开的数据文件的最大数量，该数量会影响到控制文件的大小。在创建数据库的过程中所设定的参数，有些会受到其他因素的影响。比如，设定了MAXDATAFILES之后，Oracle在控制文件中要分配空间存放数据文件的文件名，即使数据库只有一个数据文件。然而，因为控制文件本身也有大小的限制（跟操作系统有关），很有可能不能把所有CREATE DATABASE的参数都设置为他们理论上的最大值。 MAXINSTANCES指定了只有一个实例能够mount和打开数据库。US7ASCII字符集被指定为数据库保存数据使用的字符集。系统表空间由/vobs/oracle/oradata/mynewdb/system01.dbf组成，如果该文件已经存在，将会被重写。系统表空间是本地化管理的表空间。 DEFAULT_TEMPORARY_TABLESPACE子句创建并命名了一个默认的临时表空间。UNDO_TABLESPACE子句创建并命名了一个撤销表空间，用来存储撤销记录，前提是在初始化参数文件中把UNDO_MANAGEMENT设定为AUTO。因为ARCHIVELOG子句在CREATE DATABASE语句中没有调用，因此重做日志文件并不会被归档，这是数据库创建过程的通常做法，ALTER DATABASE语句可以用来切换到ARCHIVELOG模式。初始化文件中的LOG_ARCHIVE_DEST_1,LOG_ARCHIVE_FORMAT, and LOG_ARCHIVE_START三个参数与归档有关。 第七步：创建额外的表空间 为了使数据局效率更高，需要创建额外的表空间和文件 CONNECT SYS/password AS SYSDBA -- create a user tablespace to be assigned as the default tablespace for users CREATE TABLESPACE users LOGGING DATAFILE '/vobs/oracle/oradata/mynewdb/users01.dbf' SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; -- create a tablespace for indexes, separate from user tablespace CREATE TABLESPACE indx LOGGING DATAFILE '/vobs/oracle/oradata/mynewdb/indx01.dbf' SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; EXIT 第八步：创建服务器参数文件 Oracle建议创建一个服务器参数文件，作为维护初始化参数的动态方式。下面的脚本根据初始化参数文件创建了一个服务器初始化参数文件，并把该初始化参数文件放在默认位置。实例关闭后重新启动时，就会使用默认位置的服务器初始化参数文件。 CONNECT SYS/password AS SYSDBA -- create the server parameter file CREATE SPFILE='/vobs/oracle/dbs/spfilemynewdb.ora' FROM PFILE='/vobs/oracle/admin/mynewdb/scripts/init.ora'; SHUTDOWN -- this time you will start up using the server parameter file CONNECT SYS/password AS SYSDBA STARTUP EXIT 数据库的优化mysql表复制
复制表结构+复制表数据
| 1 2 |
mysql> createtable
t3 liket1;
mysql> insertinto
t3 select*
from t1;
|
mysql索引
| 1 2 3 4 5 6 7 8 9 10 11 12 |
ALTER
TABLE用来创建普通索引、UNIQUE索引或PRIMARYKEY索引
ALTER
TABLE table_name ADDINDEX
index_name (column_list)
ALTER
TABLE table_name ADDUNIQUE
(column_list)
ALTER
TABLE table_name ADDPRIMARY KEY(column_list)
Create
Index
CREATE
INDEX index_name ONtable_name (column_list)
CREATE
UNIQUE INDEX index_nameON
table_name (column_list)
drop
index
DROP
INDEX index_name ONtalbe_name
alter
table table drop
ALTER
TABLE table_name DROPINDEX
index_name
ALTER
TABLE table_name DROPPRIMARY
KEY
|
mysql视图
创建视图
| 1 2 |
mysql> createview
v_t1 asselect
* fromt1
where id>4
and id<11;
Query OK, 0
rows affected (0.00 sec)
|
view视图的帮助信息
| 1 2 3 4 |
mysql> ? view
ALTER
VIEW
CREATE
VIEW
DROP
VIEW
|
查看视图
| 1 |
mysql> show tables;
|
删除视图v_t1
| 1 |
mysql> dropview
v_t1;
|
mysql内置函数
字符串函数
| 1 2 3 4 5 6 7 8 9 10 |
CONCAT (string2 [,… ]) //连接字串
LCASE (string2 ) //转换成小写
UCASE (string2 ) //转换成大写
LENGTH (string ) //string长度
LTRIM (string2 ) //去除前端空格
RTRIM (string2 ) //去除后端空格
REPEAT (string2 ,count) //重复count次
REPLACE
(str ,search_str ,replace_str ) //在str中用replace_str替换search_str
SUBSTRING
(str , position [,length ]) //从str的position开始,取length个字符
SPACE(count) //生成count个空格
|
数学函数
| 1 2 3 4 5 6 7 |
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
FLOOR (number2 ) //向下取整
MAX(num1 ,num2) //取最大值
MIN(num1,num2) //取最小值
SQRT(number2) //开平方
RAND() //返回0-1内的随机值
|
日期函数
| 1 2 3 4 5 6 7 8 |
CURDATE() //返回当前日期
CURTIME() //返回当前时间
NOW() //返回当前的日期时间
UNIX_TIMESTAMP(date) //返回当前date的UNIX日间戳
FROM_UNIXTIME() //返回UNIX时间戳的日期值
WEEK(date) //返回日期date为一年中的第几周
YEAR(date) //返回日期date的年份
DATEDIFF(expr,expr2) //返回起始时间expr和结束时间expr2间天数
|
mysql预处理语句
设置stmt1预处理,传递一个数据作为一个where判断条件
| 1 |
mysql> preparestmt1
from 'select * from t1 where id>?';
|
设置一个变量
mysql> set @i=1;
执行stmt1预处理
mysql> execute stmt1 using @i;
设置@i为5
mysql> set @i=5;
再次去执行stmt1
mysql> execute stmt1 using @i;
如何删除预处理stmt1
mysql> drop prepare stmt1;
mysql事务处理
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
--关闭自动提交功能
mysql> setautocommit=0;
--从表t1中删除了一条记录
mysql> deletefrom
t1 whereid=11;
--此时做一个p1还原点:
mysql> savepoint p1;
--再次从表t1中删除一条记录:
mysql> deletefrom
t1 whereid=10;
--再次做一个p2还原点:
mysql> savepoint p2;
--此时恢复到p1还原点，当然后面的p2这些还原点自动会失效:
mysql> rollbackto
p1;
--退回到最原始的还原点:
mysql> rollback;
|
mysql存储
创建一个存储p1()
| 1 2 3 4 5 6 7 8 9 10 |
mysql> \d //
mysql> create procedure p1()
-> begin
-> set@i=0;
-> while@i<10
do
-> select@i;
-> set@i=@i+1;
-> end while;
-> end;
-> //
|
执行存储p1()
| 1 2 3 4 5 6 |
mysql> \d ;
mysql> call p1();
--查看procedure p1()的status信息
mysql> show procedure status\G
--查看procedure p1()的具体信息:
mysql> show create procedure p1\G
|
mysql触发器
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
修改delimiter为//
mysql> \d //
创建一个名字为tg1的触发器,当向t1表中插入数据时，就向t2表中插入一条数据
mysql> create trigger tg1 before insert on t1for
each ro
>begin
>insert into t2(id) values(new.id);
>end//
--准备两个空表t1和t2
mysql> select* from t1;
mysql> select* from t2;
--向t1表中插入多条数据:
mysql> insert into t1 values(1),(2),(3),(4);
如何制作删除表t1后t2表中的记录也会跟着删除呢
mysql>\d
//
mysql> create trigger tg2 beforedelete on t1for
each row
>begin delete from t2 whereid=old.id;
>end//
mysql>\d ;
如何制作更改表t1后t2表中的记录跟着个性呢
mysql>\d
//
mysql> create trigger tg3 beforeupdate on t1for
each row
>begin update t2set
id=new.idwhere
id=old.id;
>end//
mysql>\d ;
查看触发器
mysql> show triggers;
|
重排auto_increment值
| 1 2 3 4 5 6 7 8 9 10 11 |
MYSQL数据库自动增长的ID如何恢复，清空表的时候。不能用
delete from tablename;
而是要用：
truncatetable tablename;
这样auto_increment 就恢复成1了
或者清空内容后直接用ALTER命令修改表:
altertable tablename auto_increment =1;
|
利用GROUP BY的WITH ROLLUP
| 1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select* from demo;
+-------+-------+
| cname | pname |
+-------+-------+
| bj | hd |
| bj | xc |
| bj | hd |
| sh | dh |
| sh | rg |
| sh | dh |
+-------+-------+
9 rows inset
(0.00 sec)
|
对demo表按照cname、pname列分组对pname列进行聚合计算如下
| 1 2 3 4 5 6 7 8 9 10 11 |
mysql> selectcname,pname,count(pname) from demo group by
cname,pname;
+-------+-------+--------------+
| cname | pname | count(pname) |
+-------+-------+--------------+
| bj | hd | 3 |
| bj | xc | 2 |
| sh | dh | 3 |
| sh | rg | 1 |
+-------+-------+--------------+
4 rows inset
(0.00 sec)
|
同样使用with rollup关键字后，统计出更多的信息，如下。注意：with rollup不可以和ordery by同时使用
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
ysql> selectcname,pname,count(pname) from demo group by cname,pname
with rollup;
+-------+-------+--------------+
| cname | pname | count(pname) |
+-------+-------+--------------+
| bj | hd | 3 |
| bj | xc | 2 |
| bj | NULL | 5 |
| sh | dh | 3 |
| sh | rg | 1 |
| sh | NULL | 4 |
| NULL | NULL | 9 |
+-------+-------+--------------+
7 rows inset
(0.00 sec)
|
使用外键需要注意的问题
创建外键的方式
| 1 2 |
mysql>create table temp(id
int, name char(20), foreign key(id)
references outTable(id) on delete cascade on update cascade)；
|
注意：Innodb类型的表支持外键，myisam类型的表，虽然创建外键可以成功，但是不起作用，主要原因是不支持外键。
优化SQL语句的一般步骤
通过show status命令了解各种SQL的执行频率
| 1 |
mysql> show [session|global]status;
|
其中：session（默认）表示当前连接，global表示自数据库启动至今
| 1 2 3 4 |
mysql>show status;
mysql>show global status;
mysql>show status like ‘Com_%';
mysql>show global status like ‘Com_%';
|
参数说明：
Com_XXX表示每个XXX语句执行的次数如：
Com_select 执行select操作的次数，一次查询只累计加1
Com_update 执行update操作的次数
Com_insert 执行insert操作的次数，对批量插入只算一次。
Com_delete 执行delete操作的次数
只针对于InnoDB存储引擎的：
InnoDB_rows_read 执行select操作的次数
InnoDB_rows_updated 执行update操作的次数
InnoDB_rows_inserted 执行insert操作的次数
InnoDB_rows_deleted 执行delete操作的次数
其他：
connections 连接mysql的数量
Uptime 服务器已经工作的秒数
Slow_queries:慢查询的次数
定位执行效率较低的SQL语句
| 1 2 |
explain select*
from table
where id=1000;
desc
select * fromtable
where id=1000;
|
通过EXPLAIN分析较低效SQL的执行计划
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain
select count(*) from stu where name like"a%"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: stu
type: range
possible_keys: name,ind_stu_name
key: name
key_len: 50
ref: NULL
rows: 8
Extra: Using where; Using index
1 row inset
(0.00 sec)
|
每一列的简单解释
id: 1
select_type: SIMPLE 表示select的类型，常见的取值有SIMPLE（）简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SESECT）等
table: stu 输出结果集的表
type: range 表示表的连接类型，性能有好到差：system（表仅一行）、const（只一行匹配）、eq_ref（对于前面的每一行使用主键和唯一）、ref（同eq_ref，但没有使用主键和唯一）、ref_or_null（同前面对null查询）、index_merge（索引合并优化）、unique_subquery（主键子查询）、index_subquery（非主键子查询）、range（表单中的范围查询）、index（都通过查询索引来得到数据）、all（通过全表扫描得到的数据）
possible_keys: name,ind_stu_name 表查询时可能使用的索引。
key: name 表示实际使用的索引。
key_len: 50 索引字段的长度
ref: NULL
rows: 8 扫描行的数量
Extra: Using where; Using index 执行情况的说明和描述
索引问题
MyISAM存储引擎的表的数据和索引是自动分开存储的，各自是独一的一个文件；InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。MySQL目前不支持函数索引，但是能对列的前面某一部分进行索引，例如name字段，可以只取name的前4个字符进行索引，这个特性可以大大缩小索引文件的大小，用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。
| 1 |
mysql>create index ind_company2_name on company2(name(4));
|
--其中company表名ind_company2_name索引名
MySQL如何使用索引
1、使用索引
(1)对于创建的多列索引，只要查询的条件中用到最左边的列，索引一般就会被使用。如下创建一个复合索引。
| 1 |
mysql>create index ind_sales2_com_mon on sales2(company_id,moneys);
|
然后按company_id进行查询，发现使用到了复合索引
| 1 |
mysql>explain
select * from sales2 where company_id=2006\G
|
使用下面的查询就没有使用到复合索引。
| 1 |
mysql>explain
select * from sales2 where moneys=1\G
|
(2) 使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用，如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain
select * from company2 where name like"%3"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row inset
(0.00 sec)
|
如下这个使用到了索引，而下面例子能够使用索引，区别就在于“%”的位置不同，上面的例子是吧“%”放在了第一位，而下面的例子则没有
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain
select * from company2 where name like"3%"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: range
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: NULL
rows: 103
Extra: Using where
1 row inset
(0.00 sec)
|
(3)如果对大的文本进行搜索，使用全文索引而不使用like“%...%”.
(4)如果列名是索引，使用column_name is null将使用索引。如下
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain
select * from company2 where name is null\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: const
rows: 1
Extra: Using where
1 row inset
(0.00 sec)
|
存在索引但不使用索引
(1)如果MySQL估计使用索引比全表扫描更慢，则不使用索引。例如如果列key_part1均匀分布在1到100之间，查询时使用索引就不是很好
| 1 |
mysql>select* from table_name where key_part1>1 and key_part<90;
|
(2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。
(3)用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
| 1 2 3 4 5 6 7 |
mysql>show index from sales\G
*************************** 1. row ***************************
……
key_name: ind_sales_year
seq_in_index：1
Column_name: year
……
|
从上面可以发现只有year列上面有索引。来看如下的执行计划。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain
select * from sales where year=2001 or country=‘China'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: ind_sales_year
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
1 row inset
(0.00 sec)
|
(4)如果不是索引列的第一部分，如下例子:可见虽然在money上面建有复合索引，但是由于money不是索引的第一列，那么在查询中这个索引也不会被MySQL采用。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain
select * from sales2 where moneys=1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row inset
(0.00 sec)
|
(5)如果like是以%开始，可见虽然在name上面建有索引，但是由于where 条件中like的值的“%”在第一位了，那么MySQL也会采用这个索引。
(6)如果列类型是字符串，但在查询时把一个数值型常量赋值给了一个字符型的列名name，那么虽然在name列上有索引，但是也没有用到。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain
select * from company2 where name name=294\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: ind_company2_name
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row inset
(0.00 sec)
|
而下面的sql语句就可以正确使用索引。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain
select * from company2 where name name=‘294'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 23
ref: const
rows: 1
Extra: Using where
1 row inset
(0.00 sec)
|
查看索引使用情况
如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数。
Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。
| 1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> show status like'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 5 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 2055 |
+-----------------------+-------+
6 rows inset
(0.00 sec)
|
两个简单实用的优化方法
分析表的语法如下:(检查一个或多个表是否有错误)
| 1 2 3 4 5 6 7 8 9 |
mysql> CHECK TABLE tbl_name[,tbl_name] …[option] …option =
{ QUICK | FAST | MEDIUM| EXTENDED | CHANGED}
mysql> check table sales;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| sakila.sales | check | status | OK |
+--------------+-------+----------+----------+
1 row inset
(0.01 sec)
|
优化表的语法格式：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]
如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多的改动，则需要做定期优化。这个命令可以将表中的空间碎片进行合并，但是此命令只对MyISAM、BDB和InnoDB表起作用。
| 1 2 3 4 5 6 7 |
mysql> optimize table sales;
+--------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| sakila.sales | optimize | status | OK |
+--------------+----------+----------+----------+
1 row inset
(0.05 sec)
|
常用SQL的优化
大批量插入数据
当用load命令导入数据的时候，适当设置可以提高导入的速度。
对于MyISAM存储引擎的表，可以通过以下方式快速的导入大量的数据。
ALTER TABLE tbl_name DISABLE KEYS
loading the data
ALTER TABLE tbl_name ENABLE KEYS
DISABLE KEYS 和ENABLE KEYS 用来打开或关闭MyISAM表非唯一索引的更新，可以提高速度，注意：对InnoDB表无效。
--没有使用打开或关闭MyISAM表非唯一索引：
| 1 2 3 |
mysql> load data infile ‘/home/mysql/film_test.txt'into table film_test2;
Query OK,529056 rows affected (1 min 55.12 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0
|
--使用打开或关闭MyISAM表非唯一索引：
| 1 2 3 4 5 6 7 |
mysql> alter table film_test2 disable keys;
Query OK,0 rows affected (0.0 sec)
mysql> load data infile ‘/home/mysql/film_test.txt'into table film_test2;
Query OK,529056 rows affected (6.34 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0
mysql> alter table film_test2enable
keys;
Query OK,0 rows affected (12.25 sec)
|
--以上对MyISAM表的数据导入，但对于InnoDB表并不能提高导入数据的效率
（1）针对于InnoDB类型表数据导入的优化
因为InnoDB表的按照主键顺序保存的，所以将导入的数据主键的顺序排列，可以有效地提高导入数据的效率。
--使用test3.txt文本是按表film_test4主键存储顺序保存的
| 1 2 |
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4;
Query OK, 1587168 rows affected (22.92 sec)
|
Records:1587168 Deleted:0 Skipped:0 Warnings:0
--使用test3.txt没有任何顺序的文本（效率慢了1.12倍）
| 1 2 3 |
mysql> load data infile ‘/home/mysql/film_test4.txt'into table film_test4;
Query OK, 1587168 rows affected (31.16 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
|
（2）关闭唯一性效验可以提高导入效率
在导入数据前先执行set unique_checks=0，关闭唯一性效验，在导入结束后执行set unique_checks=1，恢复唯一性效验，可以提高导入效率。
--当unique_checks=1时
| 1 2 3 |
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
|
--当unique_checks=0时
| 1 2 3 |
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4;
Query OK,1587168 rows affected (19.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
|
（3）关闭自动提交可以提高导入效率
在导入数据前先执行set autocommit=0，关闭自动提交事务，在导入结束后执行set autocommit=1，恢复自动提交，可以提高导入效率。
--当autocommit=1时
| 1 2 3 |
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
|
--当autocommit=0时
| 1 2 3 |
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4;
Query OK,1587168 rows affected (20.87 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
|
优化insert语句
尽量使用多个值表的insert语句，这样可以大大缩短客户与数据库的连接、关闭等损耗。
可以使用insert delayed（马上执行）语句得到更高的效率。
将索引文件和数据文件分别存放不同的磁盘上。
可以增加bulk_insert_buffer_size 变量值的方法来提高速度，但是只对MyISAM表使用当从一个文件中装载一个表时，使用LOAD DATA INFILE。这个通常比使用很多insert语句要快20倍。
优化group by语句
如果查询包含group by但用户想要避免排序结果的损耗，则可以使用使用order by null来禁止排序：
如下没有使用order by null来禁止排序
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain
select id,sum(moneys) from sales2 group byid\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using temporary;Using filesort
1 row inset
(0.00 sec)
|
如下使用order by null的效果：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain
select id,sum(moneys) from sales2 group byid
order by null\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using temporary
1 row inset
(0.00 sec)
|
优化嵌套查询
下面是采用嵌套查询的效果（可以使用更有效的链接查询（Join）替代）。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
mysql> explain
select * from sales2 where company_id notin(selectid
from company2)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row inset
(0.00 sec)
*************************** 2. row ***************************
id: 2
select_type: SIMPLE
table: company2
type: index_subquery
possible_keys: ind_company2_id
key: ind_company2_id
key_len: 5
ref: func
rows: 2
Extra: Using index
1 row inset
(0.00 sec)
|
下面是使用更有效的链接查询（Join）
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
mysql> explain
select * from sales2 leftjoin
company2 on
sales2.company_id = company2.idwhere sales2.company_id is null\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: ind_sales2_companyid_moneys
key: ind_sales2_companyid_moneys
key_len: 5
ref: count
rows: 1
Extra: Using where
1 row inset
(0.00 sec)
*************************** 2. row ***************************
id: 2
select_type: SIMPLE
table: company2
type: index_subquery
possible_keys: ind_company2_id
key: ind_company2_id
key_len: 5
ref: func
rows: 1
Extra:
1 row inset
(0.00 sec)
|
从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。连接（JOIN）子所以更有效率一些，是因为MySQL不需要再内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
数据库优化
优化表的类型
在MySQL中，可以使用函数PROCEDUREANALYSE（）对当前应用的表进行分析，改函数可以对数据表中列的数据类型提出优化建议，用户可以根据应用的实际情况酌情考虑是否实施优化。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> select* from duck_cust procedure analyse()\G
*************************** 1. row ***************************
Field_name: sakila.duch_cust.cust_num
Min_value: 1
Max_value: 6
Min_length: 1
Max_length: 1
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 3.5000
Std: 1.7078
Optimal_fieldtype: ENUM(‘1',‘2',‘3',‘4') NOT NULL
*************************** 2. row ***************************
……
|
大存储量解决
1.分库分表
2.分区
主要目的:
1.减少表的记录数
2.减小对操作系统的负担压力
中间表
中间表的产生:
1.view 视图
2.重新生成一个新表
Mysql服务器优化
myisam读锁定
1.
lock table t1 read
2.开启另一个mysql连接终端,接着去尝试:
select * from t1
3.再insert、update和delete t1这张表，你会发现所有的数据都停留在终端上没有真正的去操作
4.读锁定对我们在做备份大量数据时非常有用.
mysqldump -uroot -p123 test >test.sql
myisam写锁定
1.
lock table t1 write
2.打开另一个mysql终端,尝试去select、insert、update和delete这张表t1,你会发现都不能操作，都会停留在终端上，只有等第一个终端操作完毕,第二个终端才能真正执行.
3.可见表的写锁定比读锁定更严格
4.一般情况下我们很少去显式的去对表进行read 和write锁定的,myisam会自动进行锁定的.
Mysql服务器优化
二进制日志
1.log-bin=mysql-bin
查看bin-log日志:
mysql> show binary logs;
查看最后一个bin-log日志:
mysql> show master status;
慢查询日志
开户和设置慢查询时间:
vi /etc/my.cnf
log_slow_queries=slow.log
long_query_time=5
慢查询次数:
mysql> show global status like "%quer%"
socket问题
mysql socket无法登录
1. 有时登录mysql时提示不能用socket登录，此时可以换成tcp方式去登录，但是可以测试时可以这样用，但是必须要在php去用之前把这个事情解决了.
| 1 2 3 4 5 6 |
[root@localhost mysql]# mysql -uroot -pwei --protocol tcp -hlocalhost
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connectionid
is 34
Server version: 5.0.77-log Source distribution
Type 'help;'or
'\h' for
help. Type '\c'to
clear the buffer.
mysql>
|
这样就可以登录,这样就不用mysql.sock来登录，而mysql.sock是启动mysqld服务时产生的
root密码丢失
root密码丢失破解
| 1 2 3 4 5 6 7 8 9 10 11 |
1.service mysqld stop
2. mysqld_safe --skip-grant-tables --user=mysql &
//跳过授权表mysql.user和mysql.db这些表
3. mysql -uroot
4. set password=password("wei");
//用这一条语句结果报错,就是因为加了--skip-grant-tables
4. mysql>update user set password=password("wei") where user='root'
and host='localhost';
5. mysql> set password for root@localhost=password("wei");
6. mysql> set password=password("wei");
//和第五步一样,都可能成功修改密码
|