MySQL数据库优化详解

mysql> create table t3 like t1;
mysql> insert into t3 select * from t1;

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADDPRIMARY KEY (column_list)
Create Index
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
drop index
DROP INDEX index_name ON talbe_name
alter table table drop
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

mysql> create view v_t1 as select * from t1 where id>4 and id<11;
Query OK, 0 rows affected (0.00 sec)

mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW

mysql> show tables;

mysql> drop view v_t1;

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个空格

BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
FLOOR (number2 ) //向下取整
MAX(num1 ,num2) //取最大值
MIN(num1,num2) //取最小值
SQRT(number2) //开平方
RAND() //返回0-1内的随机值

CURDATE() //返回当前日期
CURTIME() //返回当前时间
NOW() //返回当前的日期时间
UNIX_TIMESTAMP(date) //返回当前date的UNIX日间戳
FROM_UNIXTIME() //返回UNIX时间戳的日期值
WEEK(date) //返回日期date为一年中的第几周
YEAR(date) //返回日期date的年份
DATEDIFF(expr,expr2) //返回起始时间expr和结束时间expr2间天数

mysql> prepare stmt1 from 'select * from t1 where id>?';

--关闭自动提交功能
mysql> set autocommit=0;
--从表t1中删除了一条记录
mysql> delete from t1 where id=11;
--此时做一个p1还原点:
mysql> savepoint p1;
--再次从表t1中删除一条记录:
mysql> delete from t1 where id=10;
--再次做一个p2还原点:
mysql> savepoint p2;
--此时恢复到p1还原点，当然后面的p2这些还原点自动会失效: 
mysql> rollback to p1;
--退回到最原始的还原点:
mysql> rollback ;

mysql> \d //
mysql> create procedure p1()
-> begin
-> set @i=0;
-> while @i<10 do
-> select @i;
-> set @i=@i+1;
-> end while;
-> end;
-> //

mysql> \d ;
mysql> call p1();
--查看procedure p1()的status信息
mysql> show procedure status\G
--查看procedure p1()的具体信息:
mysql> show create procedure p1\G

修改delimiter为//
mysql> \d //
创建一个名字为tg1的触发器,当向t1表中插入数据时，就向t2表中插入一条数据
 
mysql> create trigger tg1 before insert on t1 for 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 t1 for each row 
>begin delete from t2 where id=old.id;
>end//
mysql>\d ;
如何制作更改表t1后t2表中的记录跟着个性呢
mysql>\d //
mysql> create trigger tg3 beforeupdate on t1 for each row 
>begin update t2 set id=new.id where id=old.id; 
>end//
mysql>\d ;
查看触发器
mysql> show triggers;

MYSQL数据库自动增长的ID如何恢复，清空表的时候。不能用

delete from tablename;
而是要用：

truncatetable tablename;
这样auto_increment 就恢复成1了

或者清空内容后直接用ALTER命令修改表:

altertable tablename auto_increment =1;

mysql> select * from demo; 
+-------+-------+ 
| cname | pname | 
+-------+-------+ 
| bj | hd | 
| bj | xc | 
| bj | hd | 
| sh | dh | 
| sh | rg | 
| sh | dh | 
+-------+-------+ 
9 rows in set (0.00 sec)

mysql> select cname,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 in set (0.00 sec)

ysql> select cname,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 in set (0.00 sec)

mysql>create table temp( id int, name char(20), foreign key(id) 
references outTable(id) on delete cascade on update cascade)；

mysql> show [session|global]status;

mysql>show status;
mysql>show global status;
mysql>show status like ‘Com_%';
mysql>show global status like ‘Com_%';

explain select * from table where id=1000;
desc select * from table where id=1000;

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 in set (0.00 sec)

mysql>create index ind_company2_name on company2(name(4)); 

mysql>create index ind_sales2_com_mon on sales2(company_id,moneys);

mysql>explain select * from sales2 where company_id=2006\G

mysql>explain select * from sales2 where moneys=1\G

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 in set (0.00 sec)

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 in set (0.00 sec)

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 in set (0.00 sec)

mysql>select * from table_name where key_part1>1 and key_part<90;

mysql>show index from sales\G 
*************************** 1. row *************************** 
…… 
key_name: ind_sales_year 
seq_in_index：1 
Column_name: year 
……

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 in set (0.00 sec)

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 in set (0.00 sec)

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 in set (0.00 sec)

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 in set (0.00 sec)

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 in set (0.00 sec)

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 in set (0.01 sec)

mysql> optimize table sales; 
+--------------+----------+----------+----------+ 
| Table | Op | Msg_type | Msg_text | 
+--------------+----------+----------+----------+ 
| sakila.sales | optimize | status | OK | 
+--------------+----------+----------+----------+ 
1 row in set (0.05 sec)

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 

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_test2 enable keys; 
Query OK,0 rows affected (12.25 sec) 

mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; 
Query OK, 1587168 rows affected (22.92 sec) 

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

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

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

mysql> explain select id,sum(moneys) from sales2 group by id\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 in set (0.00 sec)

mysql> explain select id,sum(moneys) from sales2 group by id 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 in set (0.00 sec)

mysql> explain select * from sales2 where company_id not in(select id 
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 in set (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 in set (0.00 sec)

mysql> explain select * from sales2 left join company2 on 
sales2.company_id = company2.id where 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 in set (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 in set (0.00 sec)

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 *************************** 
……

[root@localhost mysql]# mysql -uroot -pwei --protocol tcp -hlocalhost
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.0.77-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

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"); 
//和第五步一样,都可能成功修改密码