Mysql：操作笔记

授权

给从服务器设置授权用户

1.     mysql>grantall on *.* to user@192.168.10.2 identified by "pass";

#授权users在10.2具有所有的权限登录所有库所有表

2.     mysql>grantreplication slave on *.* user@192.168.10.2 identified by "pass";

#只有复制权限

查看用户授权表

selectuser,host,password from mysql.user;

开启Mysql bin-log日志

vi /etc/my.cnf

[mysqld]

port = 3306

socket =/var/lib/mysql/mysql.sock

log-slow-queries=mysql-slow.log

log-error=mysql.err

log=mysql.log

log-bin=mysql-bin

 

查看

Show variableslike “%log%”;

Mysql中查看

ls /var/lib/mysql(systemls)

与bin-log有关的日志刷新

 

mysql>flushlogs;

此时就会多一个最新的bin-log日志

mysql>showmaster status;

查看最后一个bin日志.

mysql>resetmaster;

清空所有的bin-log日志

[root@localhost#]mysqlbinlog --no-defaults mysql-00001.bin(查看)

        /usr/local/mysql/bin/mysqlbinlog  --no-defaults bin-log-path(使用绝对路径)

mysqlbinlog--no-defaults /alidata/server/mysql-5.1.57/var/mysql-bin.000404 | mysql –uroot–p123 test(恢复)

备份数据:

 

mysqldump -uroot-pwei test -l -F '/tmp/test.sql'

-F即flush logs，可以重新生成新的日志文件，当然包括log-bin日志

查看binlog日志用mysql>show master status

 

Mysql恢复与bin-log日志

 

mysql -uroot -pweitest -v -f < /tmp/test.sql

-v查看导入的详细信息

-f是当中间遇到错误时，可以skip过去，继续执行下面的语句

 

mysqlbinlog --no-defaults binlog-file | mysql -uroot - pwei

恢复binlog-file二进制日志文件

日志恢复：

 

用bin-log来恢复:

[root@localhost#]mysqlbinlog--no-defaults mysql-bin.000004|more

#120102 23:55:35 server id 1 end_log_pos 617 Query

insert into t1 values(6)

#120102 23:55:35 server id 1 end_log_pos 644 Xid = 55

COMMIT/*!*/;

#找到要恢复的position.

 

开始恢复:

# mysqlbinlog--no-defaults --stop-position="644" mysql-bin.000004|mysql -uroot-pwei test

 

是mysqlbinlog后跟的主要参数:

--stop-position="100"

--start-position="50"

--stop-date="2012-01-0421:17:50"

--start-date="2012-01-0419:10:10"

主从配置

主服务器配置：

 

1)给从服务器设置授权用户

mysql>grant all slave on *.* to user@192.168.10.2identified by "pass";

或

mysql>grant replication slave on *.* user@192.168.10.2identified by "pass";

       查看授权用户：show grants for user@192.168.10.2;

2)修改主数据库服务器的配置文件my.cnf，开启binlog，并设置server-id的值

log-bin=mysql-bin

server-id=1

3)在主服务器上设置读锁定有效,确保没有数据库操作，以便获得一个一致性的快照:

mysql>flush tables with read lock;

 

4)查看主服务器上当前的二进制日志名和偏移量值

mysql>show master status;

 

5)目前主数据库服务器已经停止了更新操作,生成主数据库的备份,备份的方式有两种:

(1)cp全部的数据

(2)mysqldump备份数据方法

如果主数据库的服务可以停止，那么直接cp数据文件最快捷

tar –cvf data.tar data/

 

6)主数据库备份完毕后，主数据库可以恢复写操作，剩下的操作只需要在从服务器上去执行:

mysql>unlock tables;

7)把主数据库的一致性备份恢复到从数据库上,把以上的压缩包解压后放到相应的目录即可.

从服务器配置

1)修改从数据库的server-id，注意server-id的值必须是唯一的，不能和主数据库的配置相同，如果有多个从服 务器，每个从服务器必须有自己唯一的server-id值.

 

2)在从服务器上的配置文件中:

server-id = 2

master-host=192.168.10.1

master-user=user

master-password=pass

master-port=3306

log-bin=mysql-bin

#replicate-do-db=test

#replicate-do-table=test.t1

3)重新启动mysqld服务:

pkill mysqld

/usr/local/mysql/bin/mysqld_safe --user=msyql &

 

4)查看相应的主从复制进程列表有两种:

(1)processlist

mysql>show processlist \G

如出现:

state:waiting formaster to send event

//连接主数据为成功，而且成功获取bin-log

state:has read allready log;waiting for the slave i/o thread to update it

//成功执行bin-log日志,正在等待着去再次连接主数据库并更新获取bin-log日志.

 

(2)status;

mysql>show slave status\G

如出现:

Slave_IO_Running: Yes

//此进程负责从服务器从主服务器上读取binlog日志， 并写入从服务器上的中继日志中.

Slave_SQL_Running: Yes

//此进程负责读取并且执行中继日志中的binlog日志,

#注以上两个都为yes则表明成功，只要其中一个进程的状态是no，则表示复制进程停止，错误原因可以从"last_error"字段的值中看到.

 

4)从数据库常用命令:

(1)start slave

#启动复制线程

(2)stop slave

#停止复制线程

(3)show slavestatus

#查看从数据库状态

(4)show masterlogs

#查看主数据库bin-log日志

(5)change master to

#动态改变到主服务器的配置

(6)show processlist

#查看从数据库运行进程

Mysql常见错误

从数据库无法同步:

Show slave status显示Slave_SQL_Running为No,Seconds_Behind_Master为null

 

原因:

a.程序可能在slave上进行了写操作

b.也可能是slave机器重起后，事务回滚造成的.

解决:方法一

Mysql>slavestop;

Mysql>setGLOBAL SQL_SLAVE_SKIP_COUNTER=1;

Mysql>slavestart;

 

解决:方法二

Slave 库，MySQL> slave stop; --停掉slave服务

Master 库，MySQL> show master status;

得到主服务器上当前的二进制日志名和偏移量

+------------------+----------+--------------+------------------+

| File | Position| Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000005| 106 | | |

+------------------+----------+--------------+------------------+

 

查看状态，然后到slave服务器上执行手动同步

mysql>changemaster to

master_host="192.168.10.1",

master_user="user",

master_password='pass',

master_port=3306,

master_log_file="mysql-bin.000005",

master_log_pos=106;

启动slave服务，

Mysql>slavestart;

通过show slave status查看Slave_SQL_Running为

Yes,Seconds_Behind_Master为0 即为正常

Mysql分区介绍

MySQL的分区技术不同与之前的分表技术，它与水平分表有点类似，但是它是在逻辑层进行的水平分表，对与应用程序而言它还 是一张表，MySQL5.1有5种分区类型:

 

RANGE分区:       基于属于一个给定连续区间的列值，把多行分配给分区(10,20,30)

优：适合日期类型 支持复合分区  

缺：有限分区

 

LIST分区:     类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择([1,2,3,4], [6,7,8,9], [10,12,13])

优：适合有固定取值的列 支持复合分区

缺：有限分区，插入的值不在list中则数据丢失

HASH分区: 

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。它可以基于用户定义的表达式的返回值来进 行选择的分区，该表达式使用将要插入到表中的这些行 的列值进行计算

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算，这 个函数可以包含MySQL中有效的、产生非负整数值的任何表达式( hash(field) )

优：线性hash使得增加，删除和合并分区更快捷

缺：线性hash数据分布不均匀，而一般hash的数据较均匀

KEY分区:    

与HASH分区类似，但它的key可以不是整数类型，如字符串等类型的字段。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表， 服务器使用其自己内部的哈希函数，这些函数是基于 与PASSWORD()一样的运算法则

类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数

优：列可以为字符类型等其他非int型

缺：效率较之前的低，md5 sha等

 

Showplugins 查看mysql的插件 ACTIVE为激活中

Innodb分区：

 

独立表空间： 在配置文件（my.cnf）中设置：innodb_file_per_table 

优点:

1.每个表都有自已独立的表空间。

2.每个表的数据和索引都会存在自已的表空间中。

3.可以实现单表在不同的数据库中移动。

4.空间可以回收（除drop table操作处，表空不能自已回收）

a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

c) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理.

缺点:

单表增加过大，如超过100个G

相比较之下，使用独占表空间的效率以及性能会更高一点

 

innodb_file_per_table=1为使用独占表空间

innodb_file_per_table=0为使用共享表空间

 

innodb_data_home_dir= "/usr/local/mysql/data/"

innodb_log_group_home_dir="/usr/local/mysql/data/"

innodb_data_file_path=ibdata1:10M:autoextend

innodb_file_per_table=1

参数说明:

这个设置配置一个可扩展大小的尺寸为10MB的单独文件，名为ibdata1，没有给出文件的位置，所以默认的是在MySQL的数据目录内.

innodb_data_home_dir代表为数据库文件所存放的目录

innodb_log_group_home_dir为日志存放目录

innodb_file_per_table是否使用共享以及独占表空间来

以上的几个参数必须在一起加入

Mysql 基本操作：

复制表结构+复制表数据

mysql> createtable t3 like t1;

mysql> insertinto t3 select * from t1;

修改索引：

1.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 ADD PRIMARY KEY(column_list)

2.create index

CREATE INDEX index_name ON table_name(column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

3.drop index

DROP INDEX index_name ON talbe_name

4.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;

 

view视图的帮助信息:

mysql> ? view

ALTER VIEW

CREATE VIEW

DROP VIEW

 

查看视图:

mysql> show tables;

 

删除视图v_t1:

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事务处理

 

mysql> set autocommit=0;// 关闭自动提交功能

 

mysql> delete from t1 where id=11;// 从表t1中删除了一条记录

 

mysql>savepoint p1;// 此时做一个p1还原点:

 

mysql> delete from t1 where id=10;// 再次从表t1中删除一条记录:

 

mysql>savepoint p2;// 再次做一个p2还原点:

mysql> rollback to p1;//此时恢复到p1还原点,当然后面的p2这些还原点自动会失效:

mysql> rollback;// 退回到最原始的还原点:

mysql存储

 

创建一个存储p1();

mysql> \d //

mysql> create procedure p1()

-> begin

-> set @i=0;

-> while @i<10 do

-> select @i;

-> set @i=@i+1;

-> end while;

-> end;

-> //

执行存储p1();

mysql> \d ;

mysql> call p1();

查看procedure p1()的status信息

mysql> show procedure status\G

查看procedure p1()的具体信息:

mysql> show create procedure p1\G

mysql触发器

 

修改delimiter为// mysql> \d //

创建一个名字为tg1的触发器,当向t1表中插入数据时，就向t2表中插入一条数据

mysql> create trigger tg1 before insert on t1 for each row

>begin

>insert into t2(id) values(new.id);

>end//

准备两个空表t1和t2

mysql> select *from t1;

mysql> select *from t2;

向t1表中插入多条数据:

mysql> insertinto t1 values(1),(2),(3),(4);

 

如何制作删除表t1后t2表中的记录也会跟着删除呢?

mysql>\d //

mysql> create trigger tg2 before delete on t1 for each row

>begin delete from t2 where id=old.id;

>end//

mysql>\d ;

如何制作更改表t1后t2表中的记录跟着更新

mysql>\d //

mysql> create trigger tg3 before update on t1 for each row

>begin update t2 set id=new.id where id=old.id;

>end//

mysql>\d ;

查看触发器:

mysql> show triggers;

 

重排auto_increment值

 

MYSQL数据库自动增长的ID如何恢复

清空表的时不用deletefrom tablename; 

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

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

alter table tablename auto_increment =1;

正则表达式的使用

MySQL利用REGEXP命令提供给用户扩展的正则表达式功能,具体模式序列如下:

 

^

在字符串的开始处进行匹配

a?

匹配1个或零个a

$

在字符串的末尾处进行匹配

a1|a2

匹配a1或a2

.

匹配任意单个字符,包括换行符

a(m)

匹配m个a

[…]

匹配出括号内德任意字符

a(m,)

匹配至少m个a

[^…]

匹配不出现括号内的任意字符

a(m,n)

匹配m到n个a

a*

匹配零个或多个a(包括空串)

a(,n)

匹配0到n个a

a+

匹配1个或多个(不包括空串)

(…)

将模式元素组成单一元素

使用正则表达式”$”和”[…]”进行匹配:

mysql>select name,email from t where email REGEXP“@163[.,]com$”

使用like方式查询:

mysql>select name,email from t where email like“[e-mail]” or email like “%@163,com”

巧用RAND( )提取随机行

 

MySQL数据库中有一个随机函数rand( )是获取一个0—1 之间的数，利用这个函数一起和order by能够把数据随机排序。

下面是通过limit随机抽取了3条数据样本。(性能严重受影响 少用)

mysql>select *from stu order by rand( );

mysql>select *from stu order by rand( ) limit 3;

 

使用with rollup关键字

统计出更多的信息，如下。注意：with rollup不可以和ordery by同时使用

select cname,pname,count(pname) from demo group by cname,pname with rollup;

用BIT GROUP FUNCTIONS做统计

在使用group by语句时可以同时用使用bit_and、bit_or函数来完成统计工作。这两个函数的作用主要是做数值之间的逻 辑位运算

对order_rab表中id分组时对kind做位与和或计算。

mysql>select id，bit_or(kind) from order_rab group by id

mysql>select id，bit_and(kind) from order_rab group by id

 

使用外键需要注意的问题

创建外键的方式如下

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

注意：Innodb类型的表支持外键，myisam类型的表，虽然创建外键可以成功，但是不起作用，主要原因是不支持外键。

 

 

mysql help使用

在mysql中那么多的命令如何才能记得住是个问题，这里有一个特别好的获得帮助的好方法，当然是在mysql>的提示下的操作:

1.? % 可以获得所有的mysql>里的命令，这个是最多的,那么这里的东西如何去进一步获得帮助呢?

2.? create

3.? opti% 因为记不住optimize的全称，这个时候可以用%来替代

4.? reg% 获得了记不住了的regexp用法.

5.查看所有用? contents可以得到所有的帮助大纲,通过这个目录再用?继续往下细查.

6 show plugins; 查看插件

 

优化SQL语句的一般步骤：

 

通过show status命令了解各种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_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)explain select *from table where id=1000;

2)desc select *from table where id=1000;

 

通过EXPLAIN分析较低效SQL的执行计划

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.00sec)

 

字段解释：

id: 1

select_type:

SIMPLE  简单SELECT(不使用UNION或子查询)  、

PRIMARY（主查询，即外层的查询）、

DEPENDENT UNION（UNION中的第二个或者后面的SELECT语句，取决于外面的查询）、

UNION RESULT   UNION的结果。

SUBQUERY（子查询中的第一个SESECT）

DEPENDENT SUBQUERY    子查询中的第一个SELECT，取决于外面的查询

DERIVED  导出表的SELECT(FROM子句的子查询)

 

table: stu 输出结果集的表

 

type: 表示表的连接类型，性能有好到差：

system   表仅有一行(=系统表)。这是const联接类型的一个特例。

const（ 表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次）、

eq_ref（对于前面的每一行使用主键和唯一 eq_ref可以用于使用= 操作符比较的带索引的列）、

ref（同eq_ref，但没有使用主键和唯一  ref可以用于使用=或<=>操作符的带索引的列）、

ref_or_null（同前面对null查询 IS NULL）、

index_merge（索引合并优化）、

unique_subquery（主键子查询   unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。）、

index_subquery（非主键子查询）、

range（表单中的范围查询）、

index（都通过查询索引来得到数据）、

all（通过全表扫描得到的数据）

 

possible_keys:name,ind_stu_name 表查询时可能使用的索引。

key: name 表示实际使用的索引。

key_len: 50 索引字段的长度

ref: NULL

rows: 8 扫描行的数量

Extra: Usingwhere; Using index 执行情况的说明和描述

索引问题

 

索引的存储分类

MyISAM存储引擎的表的数据和索引是自动分开存储的，各自是独一的一个文件；

InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。

 

MySQL目前不支持函数索引，但是能对列的前面某一部分进行索引，例如name字段，可以只取name的前4个字符进行索引，这个特性可以大大缩小索引文件的大小，用户在设计表结构的时候也可以对 文本列根据此特性进行灵活设计。

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

其中company表名ind_company2_name索引名

MySQL如何使用索引

 

1、使用索引

(1)对于创建的多列索引，只要查询的条件中用到最左边的列， 索引一般就会被使用。如下创建一个复合索引。

(2) 使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用

(3)如果对大的文本进行搜索，使用全文索引而不使用like“%...%”.

(4)如果列名是索引，使用column_name is null将使用索引。

 

2、存在索引但不使用索引

(1)如果MySQL估计使用索引比全表扫描更慢，则不使用索引。例如如果列key_part1均匀分布在1到100之间，查询时使用索引就不是很好

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

 

(2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。

 

(3)用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

 

(4)如果不是索引列的第一部分，如下例子:可见虽然在money上面建有复合索引，但是由于money不是索引的第一列，那么在查询中这个索引也不会被MySQL采用。

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

 

(5)如果like是以%开始，可见虽然在name上面建有索引，但是由于where 条件中like的值的“%”在第一位了，那么MySQL也会采用这个索引。

      

(6)如果列类型是字符串，但在查询时把一个数值型常量赋值给了一个字符型的列名name，那么虽然在name列上有索引，但是也没有用到。

mysql> explain select * from company2 where name=294\G

 

查看索引使用情况

 

如果索引正在工作，

Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数。

Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。

mysql>show status like "handler_read%";

修改表名：rename table t2 tot1;

  

检查一个或多个表是否有错误:

mysql>check table tablename;

优化表的语法格式：

OPTIMIZE [LOCAL |NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]

 

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多的改动，则需要做定期优化。这个命令 可以将表中的空间碎片进行合并，但是此命令只对MyISAM、BDB和InnoDB表起作用。

mysql> optimize table tablename;

常用SQL的优化：

 

大批量插入数据

当用load命令导入数据的时候，适当设置可以提高导入的速度。

对于MyISAM存储引擎的表，可以通过以下方式快速的导入大量的数据。

ALTERTABLE tbl_name DISABLE KEYS

loadingthe data

ALTERTABLE tbl_name ENABLE KEYS

DISABLE KEYS 和ENABLEKEYS 用来打开或关闭MyISAM表非唯一索引的更新，可以提高速度，注意：对InnoDB表无效。

 

（1）针对于InnoDB类型表数据导入的优化

因为InnoDB表的按照主键顺序保存的，所以将导入的数据主键的顺序排列，可以有效地提高导入数据的效率。

       Select namefrom t1 order by id into outfile ‘/temp/name.txt’;

load data infile ‘/temp/name.txt’into table t1;

 

（2）关闭唯一性效验可以提高导入效率

在导入数据前先执行setunique_checks=0，关闭唯一性效验，在导入结束后执行set unique_checks=1，恢复唯一性效验，可以提高导入效率。

 

（3）关闭自动提交可以提高导入效率

在导入数据前先执行setautocommit=0，关闭自动提交事务，在导入结束后执行set autocommit=1，恢复自动提交，可以提高导入效率。

优化insert语句

 

尽量使用多个值表的insert语句，这样可以大大缩短客户与数据库的连接、关闭等损耗。

可以使用insert delayed（马上执行）语句得到更高的效率。

将索引文件和数据文件分别存放不同的磁盘上。

可以增加bulk_insert_buffer_size变量值的方法来提高速度,但是只对MyISAM表使用

当从一个文件中装载一个表时，使用LOAD DATAINFILE。这个通常比使用很多insert语句要快20倍。

 

优化group by语句

 

如果查询包含group by但用户想要避免排序结果的损耗，则可以使用使用order bynull来禁止排序

 

优化嵌套查询

链接查询（Join） 替代嵌套查询 

 

优化表的类型：

 

在MySQL中，可以使用函数PROCEDURE ANALYSE()对当前应用的表进行分析， 该函数可以对数据表中列的数据类型提出优化建议，用户可以根据应用的实际情况酌情考虑是否实施优化。

mysql> select *from duck_cust procedure analyse()\G

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

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会自动进行锁定的. 

 

字符集设置

[client]

#password= your_password

port =3306

socket =/var/lib/mysql/mysql.sock

default-character-set=utf8#客户端和链接字符集

[mysqld]

port =3306

socket =/var/lib/mysql/mysql.sock

character-set-server=utf8

collation-server=utf8_general_ci#校验字符集 排序用

 

二进制日志

1.log-bin=mysql-bin

查看bin-log日志:

mysql> showbinary logs;

 

查看最后一个bin-log日志:

mysql>show master status;

 

慢查询日志

1.有关慢查询

开户和设置慢查询时间:

vi/etc/my.cnf

log_slow_queries=slow.log

long_query_time=2

 

2查看设置后是否生效

mysql>show variables like "%quer%";

 

3慢查询次数:

mysql>show global status like "%quer%";

http://blog.csdn.net/fb408487792/article/details/41869025

 

mysql socket无法登录

 

 有时登录mysql时提示不能用socket登录，此时可以换成tcp方式去登录，但是可以测试时可以这样用，但是必须要在php去用之前把这个事情解决了.

[root@localhostmysql]# mysql -uroot -pwei --protocol tcp -hlocalhost

这样就可以登录,这样就不用mysql.sock来登录，而mysql.sock是启动mysqld服务时产生的

 

root密码丢失找回：

http://blog.csdn.net/fb408487792/article/details/41120539

mysql定时备份

http://blog.csdn.net/fb408487792/article/details/41316315