mysql复合函数索引_mysql高级

1.1、启动MySQL服务器

service MySQL start#启动服务器

service mysql stop #关闭服务器

1.2、登录mysql

mysql -u root -p #登录root用户 然后输入密码，

cat /root/.mysql_secret#查看随机生成的密码

进入mysql后输入：set password =password("新密码")#设置新的密码

设置远程访问：grant all privileges on *.* to 'root' @'%' identified by 'itcast'

关闭防火墙：service iptables stop

2.1索引结构

BTREE索引：最常见索引类型，大部分索引都支持B树索引

HASH索引：只有memory引擎支持，使用场景简单

R-tree索引：主要用于地理空间数据类型

Full-text(全文索引)：主要用于全文

2.2索引分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：即一个索引包含多个列

2.3 索引语法

创建索引：

create index 索引名称 on 表名(某列)；

查看索引：

show index from 表名；

删除索引：

drop index 索引名称 on表名;

alter命令;

alter table 表名 add unique 索引名称(某列)；#创建唯一索引

3.1创建或者修改视图

create view 视图名称 as(select语句);#创建视图

select * from视图名称；#查看视图

3.2查看视图和删除视图

show tables就可以查看表和视图

drop view if exists 视图名称；

4.1创建存储过程

首先要修改结束语句的符号：delimiter $ #将；换成了$符号

create procedure 存储过程名称()

begin

sql语句；

end $

4.2调用存储过程

call 存储过程名称();

4.3查询存储过程

select name from mysql.proc where db='demo_01';#通过表来查询

show procedure status\G；查看存储过程状态信息

show create procedure test.存储过程名称\G;

4.4 删除存储过程

drop procedure 存储过程名称

4.5.1 存储过程语法-变量

delimiter $

create procedure 存储过程名称()

begin

declare num int default 10;#declare声明变量，default设置默认值

set num =num +10#set 可以赋值给常量

select count(*) into num from city;#select into #可以对变量进行赋值，

select concat('num值：',num)；

end $

4.5.2 存储过程语法-if条件判断

delimiter $

create procedure 存储过程名称()

begin

declare num int default 10;

declare content varchar(50) default ' ';

if num>= 10 then

set content='大于10'；

end if；

select concat('num值：',num,'大小为'，content)；

end $

4.5.3 存储过程语法-传递参数

1、输入参数：

delimiter $

create procedure 存储过程名称(in number int)#in表示输入参数

begin

declare num int default 10;

declare content varchar(50) default ' ';

if num>= 10 then

set content='大于10'；

end if；

select concat('num值：',num,'大小为'，content)；

end $

调用时 :call 存储过程名称(num)

2、输出参数：

delimiter $

create procedure 存储过程名称(in number int,out content varchar(50))#in表示输入参数

begin

if num>= 10 then

set content='大于10'；

end if；

end $

调用时：call 存储过程名称(num,@content)$#将返回结果存到了@content中

查看select @content$

4.5.4 存储过程语法-case结构

delimiter $

create procedure 存储过程名称(mon int)

begin

declare result varchar(10);

case

when mon >=1 and mon<=4 then

set result = '第一季度'；

when mon >=5 and mon<=8 then

set result = '第二季度'；

else

set result = 'null'；

end case;

end $

4.5.4 存储过程语法-while循环

delimiter $

create procedure 存储过程名称(num int)

begin

declare total int default 0;

declare number int default 1;

while numer <=num do;#满足条件执行语句

set total =total+number;

set number=number +1;

end while;

end $

4.5.5 存储过程语法-repeat结构

delimiter $

create procedure 存储过程名称(num int)

begin

declare total int default 0;

repeat

set total =total+num;

set num=num -1;

until num=0;#until 设置条件，repeat 满足条件者退出循环

end repeat ;

end $

4.5.6 存储过程语法-loop语句

delimiter $

create procedure 存储过程名称(num int)

begin

declare total int default 0;

c:loop #c:为取得别名

set total =total+num;

set num=num -1;

if num<=0 then

leave c;

end loop c ;

end $

4.5.7 存储过程语法-游标

delimiter $

create procedure 存储过程名称()

begin

declare id int(11);

declare name varcher(50);

declare has_data int default 1;

declare 游标名字 sursor for select * from 表名；#创建游标

declare exit handler for not found set has_data=0#当游标拿不到数据时会触发，必须在创建游标下一行

open 游标名字;#打开游标

repeat

fetch 游标名字 into id ,name;

select concat ('id=',id,'name=','name')

end repeat;

close 游标名字;#关闭游标

end $

4.6 存储函数

delimiter $

create procedure 存储过程名称(num int)

returns int

begin

declare cnum int(11);

select count(*) into id from 表名 where id = num;

return cnum;

end $

调用：select 存储过程名称(num)

5.1创建触发器

插入(insert)、修改 (update)、删除(delete)

delimiter $

create trigger 触发器名称

after insert#插入

on 表名1

for each row#表示行级触发器

begin

insert into 表名2(列名1，列名2) values()

end $

5.2查看触发器

show trigger\G;#查看全部触发器

5.3删除触发器

drop trigger 触发器名字；

6.1查看存储引擎

进入数据库中后：show engines；#查看数据库支持的存储引擎

7.1定位低效sql语句

可有使用：show processlist；查看

7.2 explain分析执行计划

explain select * from 表名 where 查询条件；#使用explain查看

7.2.1 explain之id

id字段时select查询的序号，是一组数字，表示查询中执行select子句或者是操作表的顺序

id相同表示加载表的顺序从上到下

id不同id值越大，优先级越高，越先执行

id有相同也有不同，同时存在。id相同的认为是一组，从上往下顺序执行；id的值越大，优先级越高，越先执行

7.2.2 explain之select_type

表示select的类型：

simple：简单的select查询，查询中不包含子查询或者umion

primary：查询中若包含任何复杂的子查询，最外层查询标记为该标识

subquery：在select或where列表中包含了子查询

derived：在from列表中包含的子查询，被标记为derived mysql会递归执行这些子查询，把结果放在临时表中

union：若第二select出现在union之后，则标记union；若union包含from子句的子查询中，外层select将被标记为：derived

union result：从union表获取结果select

7.2.3 explain之table

展示这一行数据是关于哪张表的

7.2.4 explain之type

type显示是访问类型，是交为重要的一个指标，可取值为：

null：mysql不访问任何表，索引，直接返回

system：表示只有一行记录，这是const类型的特例，一般不会出现

const：表示通过索引一次就找到了，const用于比较primary key 或者unique索引。

因为只匹配一行数据，所以很快，如果将主键置于where列表中，mysql就将该查询转换为一个常量

const于将主键或唯一索引的所有部分与常量值进行比较

eq_ref：类似ref，区别在使用的是唯一索引，使用主键的关联查询，关联查询的记录只有一条

ref：非唯一索引查询扫描，返回匹配某个单独值的所有行。

range：只检索给定返回的行，使用一个索引来选择行。

index：index与all的区别为index类型只是遍历了索引树，通常比all快，all是遍历数据文件

all：将遍历全表找到匹配的行

7.2.5 explain之key

possible_keys：显示可能应用在这张表的索引，一个或多个

key：实际使用的索引，如果为null ，则没有使用索引

key_len：表示索引中使用的直接数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

7.2.6 explain之rows

扫描的数量。

7.2.7 explain之extra

using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引进行读取，称为文明排序

using temporary：使用了临时表保存中间结果，mysql在对查询结果排序使用临时表。

using index：表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错，

7.3 show profile分析sql

select @@have_profiling#查看是否支持profiling

select @@profiling#查看profiling是否开启

set profiling=1#开启profiling

在操作完一系列指令后使用：show profiling;查看所有指令的操作时间

show profile (CPU) for query 5；#查看第五条指令操作每一个阶段所用的时间，加上CPU可以查看cup耗损时间

7.4 trace分析优化器执行计划

通过trace文件能够进一步了解优化器选择A计划，而不是选择B计划

set optimizer_trace="enabled=on",end_markers_in_json=on;#开启优化器

set optimizer_trace_max_mem_size=1000000;#设置内存

运行sql语句

select * from information_schema.optimizer_trace\G;#查看

8.1避免索引失效

1、全值匹配：对索引中所以列都指定具体值

2、最左前缀法则：指查询从索引的最左前列开始，并且不跳过索引中的列

3、范围查询右边的列，不能使用索引

4、不要在索引列上进行运算操作，造成索引失效

5、字符串不加单引号，造成索引失效

6、尽量使用覆盖索引，避免select *

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

8、以%开头的立刻模糊查询，索引失效

9、如果mysql评估使用索引比全表更忙，则不使用索引

10、is null、is not null 有时索引失效

11、in走索引，not in索引失效

12、单列索引和复合索引，尽量使用复合索引而少使用单列索引

8.2查看索引使用情况

show status like‘handler_read%’;

9.1 sql优化-大量插入数据

使用load命令导入数据的时候，适当的设置可以提高导入的效率

1、顺序插入

2、关闭唯一性校验，在导入数据前执行set unique_checks=0,导入后执行set unique_checks=1恢复唯一性校验

3、手动提交事务，在导入数据前执行set autocommit=0，导入后执行set autocommit=1恢复手动提交事务

9.2 优化insert语句

insert into 表名 values(1,"##");

insert into 表名 values(2,"##");

方法一：

insert into 表名 values(1,"##"),(2,"##");

方法二 在事务进行数据插入：

start transaction;

insert into 表名 values(1,"##");

insert into 表名 values(2,"##");

commit

方法三 数据有序插入

9.3 优化order by语句

1、通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序叫做filesort排序

2、通过有序索引顺序扫描直接返回有序数据，这种情况即为using index 不需要额外排序，操作效率高

9.4 优化group by 语句

可以使用索引提高效率

select age，count(*) from 表名 group by age order by null；#后面讲不需要的查询语句关闭

9.5 优化嵌套查询

用多表连接(join)查询代替子查询可更高效

9.6 优化or条件

对于包含or的查询子句，如果要利用索引则or之间条件都必须用到索引，而且不能使用复合索引

可有使用union代替or来提高效率，

9.7 优化分页查询

优化方法一：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

select id from 表名 order by id limt 2000,10；#查询从2000开始的后10位的内容

优化方法二：该方案使用于主键自增长的表，可以把limit查询转换成某个位置的查询

9.8 使用sql提示

sql提示，是优化数据库的一个重要的手段，简单来说，就是在sql语句中加入一些人为的提示来达到优化的效果

1)、use index：在查询语句表名的后面。添加use index来提供希望mysql去参考的索引列表

select * from 表名 use index (索引名)where 查询条件；

2)、ignore index 如果用户单纯的想让mysql忽略一个或多个索引，则可以使用ignore index

select * from 表名 ignore index (索引名)where 查询条件；

3)、force index：为强制mysql使用一个特点的索引，可以查询中使用force index作为hint

select * from 表名 force index (索引名)where 查询条件；

10.1 应用优化-使用连接池

对于访问的数据库来说，建立连接的代价比较昂贵的，因为我们频繁的创建关闭连接，是比较消耗资源的，我们有必须要建立数据库

连接池，以提高访问的性能

10.2 减少对mysql的访问

1)、避免对数据进行重复检索：在编写应用代码时，需要能够理清对数据的访问逻辑。能够一次连接就获取结果

2)、增加ceche层：在应用中，我们可以在应用中增加缓存层来达到减轻数据库负担的目的。

3)、负载均衡：它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上，以此来降低单台服务器的负载，达到优化的效果

11.1 mysql中查询缓存优化

开启mysql的查询缓存，当执行完全相同的sql语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效

修改比较频繁的表不适合做查询缓存。

11.2 查询缓存的配置

1)、查看当前的mysql数据库是否支持查询缓存：

show variables like 'have_query';

2)、查看当前mysql是否开启了查询缓存：

show variables like 'query_cache_type';

3)、查看查询缓存的占用大小；

show variables like 'query_cache_size';

4)、查看查询缓存的状态变量；

show variables like 'Qcache%'

11.3 开启查询缓存

linux中打开my.cnf 文件：vi /usr/my.cnf

在最后一行添加：query_cache_type = 1

然后重启mysql：service mysql restart

11.4 查询缓存select选项

sql_cache：如果查询结果是可缓存的，并且query_cache_type系统变量的值为no或者demand，则缓存查询结果

sql_no_cache：服务器不使用查询缓存，它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果

11.4 查询缓存失效的情况

1)、sql语句不一致的情况，想要命中查询缓存，查询sql语句必须一致。

2)、当查询语句中有一些不确定的时，则不会缓存。如：now(),current_date(),curdate()

3)、不使用任何表查询语句。

4)、查询mysql，information_schema或者performance_schema数据库中的表时，不会走查询缓存

5)、在存储的函数，触发器或事件的主体内执行的查询。

6)、如果表更改，着该表的所有高速缓存查询都将变为无效并从高速缓存中删除。

12.1 内存优化的原则

1)、将尽量多的内存分配给mysql做缓存，但要给操作系统和其他程序预留足够的内存

2)、myisam存储引擎的数据文件读取依赖于操作系统自身的io缓存

3)、排序区、连接区等缓存是分配给每个数据库专用的。

12.2 myisam内存优化

key_buffer_size决定myisam索引块缓存区的大小，直接影响到myisam表的存取效率

在/usr/my.cnf中添加：key_buffer_size=512M

read_buffer_size:如果需要经常顺序扫描myisam表，可以增大read_buffer_size的值来改善性能

read_rnd_buffer_size:对于需要做排序的myisam表的查询，如果有order by子句的sql，适当增加read_rnd_buffer_size的值

12.3 innoDB内存优化

innoDB用一块内存区做io缓存池，该缓存池不仅用来缓存innoDB的索引块，而且也用来缓存innoDB的数据块

innodb_buffer_pool_size:该变量决定了innoDB存储引擎表数据和索引数据的最大缓存区大小，innodb_buffer_pool_size的值越大，缓存命中率越高，

在/usr/my.cnf中添加设置：innodb_buffer_pool_size=512M

innodb_log_buffer_size:决定了innodb重做日志缓存的大小，

在/usr/my.cnf中添加设置：innodb_log_buffer_size=10M

13.1 mysql并发参数调整

max_connections：

采用max_connections控制允许连接到mysql数据库的最大值，默认值位151，可以根据系统的性能来调整最大连接数。

back_log:

back_log参数控制mysql监听TCP端口时设置的积压请求栈大小，如果mysql的连接数达到max_connections时，新的请求将会被存在堆栈中，以等待某一连接

释放资源。如果需要数据库在较短时间内处理大量连接请求，可以考虑将back_log的值增大。

table_open_cache:

该参数用于控制所有sql语句执行线程可打开表缓存的数量，而在执行sql语句时，每一个sql执行线程至少要打开一个表缓存。该参数的值应该根据设置最大连接

数来设定

thread_cache_size:

为了加快连接数据库的速度，mysql会缓存一定数量的客户服务线程以备重用，通过参数thread_cache_size可控制mysql缓存客户服务线程的数量

innodb_lock_wait_timeout:

该参数是用来设置innodb事务等待行锁的时间，默认值是50ms，可以根据需要的进行动态设置

查看上述参数：show variable like '##'

14.1 mysql 锁的问题

锁是计算机协调多个进程或线程并发访问某一资源的机制。(避免资源的争抢)

从数据操作的粒度分为表锁和行锁

表锁：操作时，会锁定整个表

行锁：操作时，会锁定当前操作行

从对数据操作的类型分为读锁和写锁

读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁(排它锁)：当前操作没有完成之前，它会阻断其他写锁和读锁

14.2 mysql锁

表级锁：偏向myisam存储引擎，开销小，加锁块；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低

行级锁：偏向innodb存储引擎，开销大，加锁慢；会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高

页面锁：开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发一般

14.3 myisam表锁

myisam存储引擎只支持表锁，这也是mysql开始几个版本唯一支持的锁类型

14.3.1 如何加表锁

在执行语句时系统会自动添加写锁和读锁

显示加表锁语法：

加读锁：lock table 表名 read；

加写锁：lock table 表名 write；

对于myisam表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求

对于myisam表的写操作，则会阻塞其他用户对同一表的读和写操作；

14.3.2 查看锁的争用情况

查看命令：show open tables；

in_user：表当前被查询使用的次数。

name_locked：表名称是否被锁定。

查看表的锁定情况命令：show status like 'tale_locks%';

tale_locks_immediate:指的是能够立即获得表级锁的次数

tale_locks_waited：指的是不能立即获取表级锁而需要等待的次数，没等待一次，该值加1

14.4 innodb行锁

行锁特点：偏向innodb存储引擎，开销大，加锁慢；会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高

innodb于myisam的最大不同有两点：一是支持事务；二是采用了行级锁

14.4.1innodb行锁模式

要展示行锁模式先要关闭数据库的自动提交：set autocommit = 0

再执行select语句

14.4.2 无索引行锁升级为表锁

查看当前表的索引：show index from test_innodb_lock

14.4.3 间隙锁的危害

当我们用范围条件，而不是使用条件检索数据，并请求共享或排他锁时，innodb会给复合条件的数据进行加锁。

14.4.4 innodb行锁争用情况

查看指令：show status like ' innodb_row_lock%'

15.1 sql执行顺序

编写顺序：

select distinct

from

join

where

group by

having

order by

limiy

执行顺序：

from

not

join

where

group by

having

select distinct

order by

limit

15.2 正则表达式使用

^：在字符串开始处进行匹配

$：在字符串末尾处进行匹配

.：匹配任意单个字符，包括换行符

[...]：匹配出括号内的任意字符

[^...]：匹配不出括号内的任意字符

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

a+：匹配-一个或者多个a(不包括空串)

a?：匹配零个或者一-个a

a1|a2：匹配a1或a2

a(m)：匹配m个a

a(m,)：至少匹配m个a

a(m,n) ：匹配m个a到n个a

a(,n)：匹配0到n个a

(...)：将模式元素组成单一元素

15.3 mysql常用的函数

主要有四类函数：数字函数，字符串函数，日期函数和复合函数

数字函数：

ABS：求绝对值

SQRT：求二次方根

MOD：求余数

CEIL和CEILING：两个函数功能相同,都是返回不小于参数的最小整数,即向上取整

FLOOR：向下取整,返回值转化为- -个BIGINT

RAND：生成-一个0~1之间的随机数,传入整数参数是，用来产生重复序列

ROUND：对所传参数进行四舍五入

SIGN：返回参数的符号

POW和POWER：两个函数的功能相同,都是所传参数的次方的结果值

SIN：求正弦值

ASIN：求反正弦值,与函数SIN互为反函数

COS：求余弦值

ACOS：求反余弦值,与函数CoS互为反函数

TAN：求正切值

ATAN：求反正切值,与函数TAN互为反函数

COT：求余切值

字符串函数：

LENGTH：计算字符串长度函数,返回字符串的字节长度

CONCAT：

合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一一个或多个

INSERT：替换字符串函数

LOWER：将字符串中的字母转换为小写

UPPER：将字符串中的字母转换为大写

LEFT：从左侧字截取符串，返回字符串左边的若干个字符

RIGHT：从右侧字截取符串,返回字符串右边的若干个字符

TRIM：删除字符串左右两侧的空格

REPLACE)：字符串替换函数,返回替换后的新字符串

SUBSTRING：截取字符串，返回从指定位置开始的指定长度的字符换

REVERSE：字符串反转(逆序)函数,返回与原始字符串顺序相反的字符串

日期函数：

CURDATE和CURRENT, DATE：两个函数作用相同,返回当前系统的日期值

CURTIME和CURRENT_TIME：两个函数作用相同，返回当前系统的时间值

NOW和SYSDATE：两个函数作用相同,返回当前系统的日期和时间值

MONTH：获取指定日期中的月份

MONTHNAME：获取指定日期中的月份英文名称

DAYNAME：获取指定日期对应的星期几的英文名称

DAYOFWEEK：获取指定日期对应的一周的索引位置值

WEEK：获取指定日期是一年中的第几周,返回值的范围是否为0~52或1~53

DAYOFYEAR：获取指定日期是一年中的第几天 ，返回值范围是1 -366

DAYOFMONTH：获取指定日期是一一个月中是第几天，返回值范围是1~31

YEAR：获取年份,返回值范围是1970- 2069

TIME_ TO SEC：将时间参数转换为秒数

SEC. .TO. _TIME：将秒数转换为时间,与TIME. .TO. SEC互为反函数

DATE_ ADD和ADDDATE：两个函数功能相同，都是向日期添加指定的时间间隔

DATE. SUB和SUBDATE：两个函数功能相同,都是向日期减去指定的时间间隔

复合函数：

MAX：查询指定列的最大值

MIN：查询指定列的最小值

COUNT：统计查询结果的行数

SUM !：求和，返回指定列的总和

AVG：求平均值,返回指定列数据的平均值

16.1 mysql中常用工具-mysql

该mysql不是指mysql服务，而是指mysql的客户端工具

1)、连接

mysql -h 127.0.0.1-P3306 -u root -p

-u：用户

-p：密码

-h：服务器IP

-P：端口号

2)、执行选项

-e：执行sql语句并退出

16.2 mysqlladmin

mysqladmin是-个执行管理操作的客户端程序。可以用它来检查服务器的配和当前状态、创建并删除数据库等。

可以通过: mysqladmin -help指令查看帮助文档

mysqladmin -u root -p123123 create '数据库名称'

16.3 mysqllbinlog

由于服务器生成的二进制日志文件以二进制格式保存,所以如果想要检查这些文本的文本格式,就会使用到mysqlbinlog日志管理工具。

-d：指定数据库名称，

-o：忽略掉日志的前n行命令

-r：将输出的文本格式日志输出到指定文件

-s：显示简单格式，省略掉一下信息

16.4 mysqldump

mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表,及插入表的SQL语句。

连接选项：

-u：用户

-p：密码

-h：服务器IP

-P：端口号

输出选项：

-- add- dr op- dat abase：在每个数据库创建语句前加上Drop database 语句

--add-drop-table：在每个表创建语句前加上Drop table语句，默认开启;不开启(--skip- add- dr op-tab1e)

-n，--no-create-db ：不包含数据库的创建语句

一t，--no-cr eate-info：不包含数据表的创建语句

-d --no-data：不包含数据：

-T，--t ab=name：自动生成两个文件: -个.sq1文件，创建表结构的语句;一个.txt文件，数据文件，相当于select into outfile

16.5 mysqlimport/source

mysqlimport是客户端数据导入工具，

mysqlimport -uroot -p123123 text /tmp/book.txt

然后导入sql文件：

source sql文件位置

16.6 mysqlshow

mysqlshow客户端对象查找工具,用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

- -count：显示数据库及表的统计信息(数据库，表均可以不指定)

-i：显示指定数据库或者指定表的状态信息

mysqlshow -uroot -p123123 --count

17.1 mysql日志

在任何一种数据库中,都会有各种各样的日志,记录着数据库工作的方方面面,以帮助数据库管理员追踪数据库曾经发生过的各种事件。MySQL 也

不例外,在MySQL中,有4种不同的日志,分别是错误日志、= 进制日志( BINLOG日志)、查询日志和慢查询日志,这些日志记录着数据库在不同方面的踪迹。

17.2 错误日志

错误日志是MySQL中最重要的日志之- - ，它记录了当mysqld启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库

出现任何故障导致无法正常使用时,可以首先查看此日志。

该日志是默认开启的，默认存放目录为mysql的数据目录( var1/ib/mysql ) ,默认的日志文件名为hostname.err ( hostname是主机名)。

查看日志命令：show variables like 'log_error%'

查看日志内容：tail -f /var/lib/mysql/xaxh-sever.err

17.3 二进制日志

二进制日志( BINLOG )记录了所有的DDL (数据定义语言)语句和DML (数据操纵语言)语句，但是不包括数据查询语句。此日志对于灾难时的

数据恢复起着极其重要的作用, MySQL的主从复制，就是通过该binlog实现的。

二进制日志,默认情况下是没有开启的,需要到MySQL的配文件中开启,并配IMySQL日志的格式。

配置文件位置: /usr/my.cnf

日志存放位置:配置时,给定了文件名但是没有指定路径,日志默认写入Mysql的数据目录。

日志格式：

STATEMENT

该日志格式在日志文件中记录的都是SQL语句( statement) , 每一条对数据进行修改的SQL都会记录在日志文件中,通过Mysq|提供的

mysqbinlog工具,可以清晰的查看到每条语句的文本。主从复制的时候,从库( slave )会将日志解析为原文本,并在从库重新执行一-次。

ROW

该日志格式在日志文件中记录的是每一-行的数据变更,而不是记录SQL语句。比如,执行SQL语句: update tb. ,book set status='1',如果是

STATEMENT日志格式,在日志中会记录一行SQL文件 ;如果是ROW ,由于是对全表进行更新,也就是每一-行记录都会发生变更 , ROW格式的日志

中会记录每一行的数据变更。

MIXED

这是目前MySQL默认的日志格式,即混合了STATEMENT和ROW两种格式。默认情况下采用STATEMENT ,但是在一一些特殊情况下采用ROW来进行

记录。MIXED 格式能尽量利用两种模式的优点,而避开他们的缺点。

二进制日志读取：mysqlbinlog log-file;

查看row格式日志：mysqlbinlog -w mysqlbin.000001

日志的删除：reset master

17.4 查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含数据的sql语句

配置：

#该选项用来开启查询日志，可选值 : 0或者1 : 0代表关闭，1代表开启

genera1_1og=1

#设置日志的文件名，如果没有指定 ，默认的文件名为host_ name. 1og

genera1_1og. filelfile. .name

17.5慢查询日志

慢查询日志记录了所有执行时间超过参数long. query, _time设置值并且扫描记录数不小于min, examined. row. Jimit的所有的SQL语句的日志。

long, _query. time默认为10秒,最小为0,精度可以到微秒。

配置：

#该参数用来控制慢查询日志是香开启， 可取值: 1和0，1代表开启，0代表关闭

slow_ query_1og=1

#该参数用来指定慢查询日志的文件名

s1ow_ query_1og. file=s low. query.1og

#该选项用来配置查询的时间限制，超过这个时间将认为值慢查询， 将需要进行日志记录，默认10s

long_query_time=10

18.1 mysql复制

复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做) , 从而使得从库和主

库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库,实现链状复制。

18.2 mysql复制原理

复制分成三步:

Master 主库在事务提交时,会把数据变更作为时间Events记录在二进制日志文件Binlog中。

主库推送二进制日志文件Binlog中的日志事件到从库的中继日志Relay Log。

slave重做中继日志中的事件,将改变反映它自己的数据。