MySQL高级操作
mysql表复制
复制表结构+复制表数据
mysql> create table t3 like t1;
mysql> insert into t2 select * from t1;
如果两个表结构不完全相同时,需要指定拷贝的列
mysql索引
1-ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引
ALTER TABLE table_name ADD INDEXindex_name(column_list)
ALTER TABLE table_name ADD UNIQUEindex_name(column_list)
ALTER TABLE table_name ADD PRIMARY KEYcolumn_list
2-alter table tablename drop
ALTER TABLE table_name DROP INDEXindex_name
ALTER TABLE table_name DROP PRIMARY KEY
如果主键有自增,则无法删除,需要将auto_increment去掉
alter table t1 modify id intunsigned not null;
3-create index (无法创建主键索引)
CREATE INDEX index_name ONtable_name(column_list)
CREATE UNIQUE INDEX index_name ONtable_name(column_list)
4-drop index
DROP INDEX index_name ON table_name
mysql视图
创建视图
mysql>create view v_t1 as select * fromt1 where id>4 and id<11;
ALTER VIEW
CREATE VIEW
DROP VIEW
查看视图
show tables
mysql内置函数
字符串函数
concat() 连接字符串
lcase()
ucase()
length()
ltrim()
rtrim()
repeat(string, count)
replace(str, search_str, replace_str)
substring(str, position [,length])
space(count) 生成count个空格
数学函数
bin () 十进制转二进制
ceiling() 向上取整
floor() 向下取整
max()
min()
sort() 开平方
rand() 返回0-1内的随机值
可以用来随机排序
select* from t1 order by rand();
日期函数
curdate()
curtime()
now()
unix_timestamp(date)
from_unixtime()
week(date)
year(date)
datediff(expr, expr2) 返回起始时间expr和结束时间expr2间的天数
mysql预处理语句
设置stmt1预处理,传递一个数据作为一个where判断条件
mysql>prepare stmt1 from ‘select * fromt1 where id>?’;
set @i=1;
execute stmt1 using @i;
set @i=5;
execute stmt1 using @i;
drop prepare stmt1;
mysql事务处理
关闭自动提交功能
set autocommit=0;
从表t1中删除了一条记录
delete from t1 where id=11;
此时做一个p1还原点
savepoint p1;
再次从表t1中删除一条记录
delete from t1 where id=10;
再次做一个p2还原点
savepoint p2;
此时恢复到p1还原点,当然后面的p2这些还原点自动会失效
rollback to p1;
退回到最原始的还原点
rollback;
mysql存储
循环插入100条数据
\d //
create procedure p1()
begin
set @i=3;
while @i<=100 do
insert into t2(name)values(concat("user", @i));
set @i=@i+1;
end while;
end//
call p1()
show procedure status\G
show create procedure p1\G
mysql触发器
mysql>\d //
mysql>create trigger tg1 before inserton t1 for each row
>begin
>insert into t2(id) values(new.id);
>end//
删除t1后t2表中的记录也跟着删除
create trigger tg2 before delete on t1 foreach row
begin
delete from t2 where id=old.id;
end
更改t1后t2中的记录跟着更改
create trigger tg3 before update on t1 foreach row
begin
update t2 set id=new.id where id=old.id;l
end
重排auto_increment值
mysql数据库自动整长的ID如何恢复
清空表的时候不使用delete from tablename;
而是要用:
truncate table tablename;
这样auto_increment就恢复成1了
或者清空内容后直接用ALTER命令修改表:
alter table tablename auto_increment=1;
常见sql技巧
正则
mysql>select name,email from t whereemail REGEXP “@163[.,]com$”;
正则比like更消耗系统资源
使用rand()提取随机行
mysql>select * from std order by rand();
mysql>select * from stu order by rand()limit 3; 随机抽取3条数据样本
利用GROUPBY的WITH ROLLUP
使用group by的withrollup子句可以检索出更多的分组聚合信息
with rollup不可以和orderby同时使用
mysql>select cnam,pname,count(pname)from demo group by cname, pname with rollup;
使用rollup之后会再计算每次聚合后count(pname)的总数
cname | pname |
bj | hd |
bj | hd |
bj | xc |
bj | xc |
bj | hd |
sh | dh |
sh | rg |
sh | dh |
sh | dh |
用BITGROUP FUNCTIONS做统计
在使用group by 语句是可以同时使用bit_and、bit_or函数来完成统计工作。这两个函数的作用主要是做数值之间的逻辑位运算
mysql>select id, bit_or(kind) fromorder_rab group by id
对order_rab表中id分组时对kind做位与和或计算
mysql>select id, bit_and(kind) fromorder_rab group by id
使用外键需要注意的问题
创建外键的方式如下
mysql> create table temp(id int, namechar(20)), forgein key(id) references outTable(id) on delete cascade on updatecascade);
注意:Innodb类型的表支持外键,myisam类型的表,虽然创建外键可以成功,但是不起作用,主要原因是不支持外键
mysqlhelp使用
同时使用?来获取mysql命令的帮助信息
? %可以获得更多命令 如 ? create% , ? reg%
? contents 涵盖了mysql所有的帮助信息
\G反转行和列
MySQL数据库优化
数据库优化
1. 优化表的类型
2. 通过拆分提高表的访问效率
3. 使用中间表提高统计查询速度
SQL语句优化
1、通过showstatus命令了解各种SQL的执行频率
格式:mysql>show [session|global]status;
其中:session(默认)表示当前连接,
global表示自数据库启动至今
mysql>show status;
mysql>show global status;
mysql>show status like 'Com_%';
mysql>show global status like 'Com_%';
增删改查次数Com_select、Com_update、Com_delete、Com_insert
只针对InnoDB存储引擎的
InnoDB_rows_read执行select操作的影响行数
InnoDB_rows_updated执行update操作的影响行数
InnoDB_rows_inserted执行insert操作的影响行数
InnoDB_rows_deleted执行delete操作的影响行数
其他:
connections连接mysql的数量
Uptime服务器已经工作的描述
Slow_queries:慢查询的影响行数
2、定位执行效率较低的SQL语句
explain select * from table where id=1000;
desc select * from table where id=1000;
重点要看
影响行数rows
单表查询还是多表查询select_type
是否用到索引,可能用到索引
每一列的解释
id
select_type:
SIMPLE 简单表,不使用表连接或子查询
PRIMARY 主查询,即外层的查询
UNION UNION中的第二个或后面的查询语句
SUBQUERY 子查询中的第一个SELECT
table
type
性能由好到差
system 表仅一行
const 只一行匹配
eq_ref 对于前面的每一行使用主键和唯一
ref 同eq_ref,但没有使用主键和唯一
ref_or_null 同前面对null查询
index_merge 索引合并优化
unique_subquery 主键子查询
index_subquery 非主键子查询
range 表单中的范围查询
index 都通过查询索引来得到数据
all 通过全表扫描得到的数据
possible_keys 表查询是可能使用的索引
key 表实际使用的索引
key_len 索引字段的长度
ref
rows 扫描行的数量
Extra Using where;Using index执行情况的说明和描述
常用SQL的优化
1、大批量插入数据
普通导入导出
mysqldump -uroot -p123456 test>/tmp/test.sql //导出
mysql -uroot -p123456 test </tmp/test.sql //导入
大批量导出导入采用infile outfile,因为outfile导出的文件没有表结构,没有创建表的语句,执行的时候更快
?infile ?outfile查看导入导出帮助
mysql>select name from t1 into outfile"/tmp/test.txt"; //导出
mysql>load data infile "/tmp/test.txt"into table t1(name); //导入
更快速的方法是在导入的时候关闭非唯一索引
disable keys和enablekeys用来打开或关闭MyISAM表非唯一索引的更新,可以提高速度,注意:对InnoDB表无效
mysql> alter table t1 disable keys;
Query OK, 0 rows affected (0.00 sec)
mysql> load data infile"/tmp/test.txt" into table t1(name);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
mysql> alter table t1 enable keys;
Query OK, 0 rows affected (0.00 sec)
(1)针对InnoDB类型数据导入的优化
因为InnoDB表按照主键顺序保存,所以将导入的数据主键顺序排列,可以提高导入效率
(2)关闭唯一行校验可以提高导入效率
确保导入的数据里不会出现冲突值时可以关闭唯一索引
setunique_checks=0; //关闭
setunique_checks=1; //开启
mysql> set unique_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql> load data infile"/tmp/test.txt" into table t1(name);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
mysql> set unique_checks=1;
Query OK, 0 rows affected (0.00 sec)
(3)关闭自动提交可以提高导入效率
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> load data infile"/tmp/test.txt" into table t1(name);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
mysql> set autocommit=1;
Query OK, 0 rows affected (0.01 sec)
2、优化insert语句
尽量使用多个连值insert语句,缩短客户端与数据库的连接、关闭等损耗
可以使用insert delayed(马上执行)语句得到更高的效率
将索引文件和数据文件分别放在不同的磁盘上
可以增加bulk_insert_buffer_size变量值的方法来提高速度,但是只对MyISAM表使用
当从一个文件中装载一个表时,使用LOAD DATA INFILE比使用多个insert语句要快20倍
3、优化group by语句
如果查询包含group by但用户想要避免排序结果的损耗,则可以使用order by null来禁止排序
mysql> desc select * from t1 group byname\G
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using temporary; Using filesort
1 row in set (0.00 sec)
mysql> desc select * from t1 group byname order by null\G
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using temporary
1 row in set (0.00 sec)
4、优化嵌套查询
嵌套查询转换成多表联合 查询和关联查询
MySQL索引优化
1、索引的存储分类
MyISAM存储引擎的表的数据和索引是自动分开存储的,各自是独立的一个文件,InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面,但可以由多个文件组成。
MySQL不支持函数索引,但是能对lieder前面某一部分进行索引,例如name字段,可以只取name的前4个字符进行索引,这个特性可以大大缩小索引文件的大小。
mysql>create index ind_company2_name oncompany2(name(4));
(3)如果对大的文本进行搜索,使用全文索引而不是用like"%...%"
(4)如果列名是索引,使用column_nameis null将使用索引。
mysql>explain select * from company2where name is null\G
mysql> desc select * from t1 where nameis null\G
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: in_name
key: in_name
key_len: 93
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
2、存在索引但不使用索引
(1)如果MySQL估计使用索引比全表扫描更慢,就不使用索引。对进行范围类字段查询时,如果选在大于第一条,小于最后一条时,可能选择放弃索引。如key_part1均匀分布在1到100之间,mysql>select * from table_name wherekey_part1>1 and key_part1<90;
(2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列,那么不会用到索引。Heap表只有在“=”的条件下会使用索引。
(3)用or或and分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到
mysql> desc select * from t1 where id=1or name="user1"\G
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: t1
type: index_merge
possible_keys: PRIMARY,in_name
key: PRIMARY,in_name
key_len: 4,93
ref: NULL
rows: 2
Extra: Using union(PRIMARY,in_name); Using where
1 row in set (0.00 sec)
mysql> alter table t1 drop indexin_name;
mysql> desc select * from t1 where id=1or name="user1"\G
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 6
Extra: Using where
1 row in set (0.01 sec)
mysql> desc select * from t1 where id=1and name="user5"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)
(4) 对于创建的多列索引,只要查询条件中用到最左边的列,索引一般就会被使用
mysql>createindex in_sales2_com_mon onsales2(company_id,moneys);
mysql>explainselect * from sales2 where company_id=2006\G 使用了复合索引
mysql>explainselect * from sales2 where moneys=1\G 没有使用到复合索引
(5)使用like的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用
mysql>explainselect * from company2 where name like "%3"\G
mysql> desc select * from t1 where namelike "user%"\G
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: in_name //使用了in_name索引
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
1 row in set (0.00 sec)
mysql> desc select * from t1 where namelike "%5"\G
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL //未使用索引
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
1 row in set (0.00 sec)
(6)如果列类型是字符串,但在查询时把一个数值行常量复制给了一个字符型的列名name,那么虽然在name列上有索引,但是也没有用到。
mysql> desc select * from t1 wherename="123"\G
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: in_name
key: in_name
key_len: 93
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
mysql> desc select * from t1 wherename=123\G
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: in_name
key: NULL
key_len: NULL
ref: NULL
rows: 7
Extra: Using where
1 row in set (0.00 sec)
3、查看索引使用情况
如果索引正在工作,Handler_read_key的值将很高,这个值代表了一个行被索引值读的次数,Handler_read_rnd_next的值高则意味着查询运行低效,并且应该建立索引补救。
mysql> show status like"handler_read%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 3 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 52 |
+-----------------------+-------+
6 rows in set (0.00 sec)
表优化
1、定期分析表和检查表
ANALYZE[LOCAL|NO_WRITE_TO_BINLOG] TABLEtbl_name[,tbl_name]...
本语句用户分析和存储表的关键字分布,分析的结果将可以使得系统得到准确的统计信息,使得SQL能够生成正确的行计划。
检查表CHECK TABLE tbl_name;
mysql> check table v_t1;
+-----------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.v_t1 | check | status | OK |
+-----------+-------+----------+----------+
1 row in set (0.00 sec)
2、定期优化表
OPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG] TABLEtbl_name[,tbl_name]...
如果已经删除了表的一大部分,或者如果已经对含有可变长度行的表进行了很多的改动,则需要做定期优化。这个命令可以将表中的空间碎片进行合并,但是此命令值对MyISAM,BDB和InnoDB表起作用。
mysql> optimize table t1;
+---------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.t1 | optimize | status | OK |
+---------+----------+----------+----------+
1 row in set (0.00 sec)
MySQl服务器优化
myisam读锁定
1.locak table t1 read
2.开启另一个mysql连接终端,接着去尝试
select * from t1
3.再insert、update和delete t1这张表,会发现所有的数据都停留在终端上没有真正的去操作
4.读锁定对在做备份大量数据时非常有用
myisam写锁定
1.locak table t1 write
2.打开另一个mysql终端,尝试select、insert、update和delete,都不能操作,只有等第一个终端操作完毕,第二个终端才能真正执行
3.写锁比读锁更严格
4.一般情况很少显示的进行read和write锁定,myisam会自动进行锁定
四种字符集问题
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci 校验字符集,排序使用
showcharacter set;可以查看各种字符集的校验字符集
binarylog日志问题
log-bin=mysql-bin
showlog慢查询日志问题
开启和设置慢查询时间
vi/etc/my.cnf
log_slow_queries=slow.log
long_query_time=5
mysql>show variables like "%slow%";
+---------------------+-----------------------------------------+
|Variable_name | Value |
+---------------------+-----------------------------------------+
|log_slow_queries | OFF |
|slow_launch_time | 2 |
|slow_query_log | OFF |
|slow_query_log_file | /usr/local/mysql/var/localhost-slow.log |
+---------------------+-----------------------------------------+
mysql>show variables like "%long%";
+--------------------+-----------+
|Variable_name | Value |
+--------------------+-----------+
|long_query_time | 10.000000 |
|max_long_data_size | 1048576 |
+--------------------+-----------+
socket问题
有时无法用socket登录,可以换成tcp方式去登录,但是测试时可以这样用,但是必须要在php用之前把问题解决
mysql-uroot -p --protocol tcp -hlocalhost
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock
skip-locking
root密码丢失
1.service mysqld stop
2.mysqld_safe --skip-grant-tables--user=mysql &
跳过授权表mysql.user和mysql.db这些表
3.mysql -uroot
4.mysql>update mysql.user setpassword=password("pwd") where user='root' and host='localhost';
5.mysql>set password for root@localhost=password("pwd");
6.mysql>setpassword=password("pwd");