mysql优化和主从

mysql是关系型数据库管理系统，是传统的RDBMS.

配置文件：

二进制日志log-bin=mysql-bin 主要用于主从复制

错误日志log-err 默认是关闭

查询日志log 默认关闭

数据文件：

两系统

frm文件    存放表结构

myd文件  存放表数据

myi文件    存放表索引

查看数据存储引擎

show engines;

查看默认引擎

show variables like '%storage_engine%';

常用：MyISAM和InnoDB

SQL执行顺序

机度顺序：

多表连接：

索引

定义：索引是帮助MySQL高效获取数据的数据结构。

简单理解为“排好序的快速查找数据结构”.

索引本身也很大，不能全部存在内存中，索引文件的形式存在磁盘。

一般都是B树结构组织的索引。

优势：

1.索引提供数据检索的效率，降低数据库的IO成本

2.通过索引列队数据进行排序，降低数据排序成本，降低CPU的消耗。

劣势：

1.索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，索引列也要占用空间。

2.提供了查询速度，降低了更新表的速度。

索引结构：

1.BTree索引

2.Hash索引

3.full-text全文索引

4.R-Tree索引

索引分类：

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

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

3.复合索引：一个索引包含多个列

4.基本语法

创建索引：

create index idx_xx_xx on table(字段名,字段名)；

alter table 表名 add index idx_xx_xx (字段名,字段名)；

drop index idx_xx_xx on 表名；

show index from 表名；

explain使用：

explain sql语句。

Extra 不要出现 using filesort和using temporary

type:system>const>eq_ref>ref>range>index>all 最差也要达到range

select_type :simple、primary、subquery、derived、union

2.复合索引多个的时候，从左开始哪里没有就从哪里断开。如果没有第一个就不会使用索引。

5.查询select 索引名，索引名   比select *效率高。 查询字段和查询条件都只有索引字段性能更好，有Using index。

8.like后面如果%在后面索引不完全失效。 like 'xxx%'效果最好。

如果要实现'%cxc%'索引有效，select 索引名，索引名 from 不使用select *或者select 没有索引的字段名也无效。

总结：

数据调试的步骤：

查询优化原则：

小表驱动大表，即小的数据集驱动大的数据集。

 order by

 支持2个排序方式，using index和using filesort。

**例子（所有情况）：

group by 

和上面一样不过还会出现using filesort,using temporary效率低。

慢查询日志：

如果不是调优需要，一般不开启。

查看是否开启状态：

show variables like '%slow_query_log%';

slow_query_log   OFF  关闭

slow_query_log_file   日志路径

开启慢日志：set global slow_query_log=1;

关闭慢日志：set global slow_query_log=0;

查询大于多少秒开启慢语句的设置：

show variables like 'long_query_time%';

设置记录慢语句的设置（大于5秒的语句）：

set  long_query_time=5;

set global long_query_time=5;重新连接或者新开才会显示修改的值。

查询慢语句条数：

show global status like '%slow_queries%';

长期有效在配置文件中的设置[mysqld]下设置：

slow_query_log=1;

slow_query_log_file=日志路径

long_query_time=3;//慢语句开启时间

log_output-FILE;

mysql提供的日志分析工具：mysqldumpslow

大量数据数据插入

2设置参数

3创建函数

4创建存储过程：

5调用（delimiter; 设置以什么符号结束为语句提交）

show profiles

mysql提供可以提供用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。默认处理关闭状态，并且保存最近15次运行结果。

测试环境使用。

show variables like 'profiling';

set profiling=on;

运行sql

show profiles;

show profile cpu,block io for query 上面显示的第几条sql语句;

出现下面情况就有问题了。

全局查询日志（永远不要在生存环境中使用）：

set global general_log=1;

set global log_output='TABLE'; 

执行的sql语句将会记录在mysql的general_log表。

查询命令：

select * from mysql.general_log;

MySql锁机制

锁的分类：

1.从对数据操作的类型（读\写）分。

2.锁的粒度分类：表锁 MyISAM、行锁InnoDB、页锁

查看表是否被锁：

show open tables;

show status like 'table%'；

手动锁命令：

lock table 表名 read/write，表名2 read/write

解锁所有表：

unlock tables;

读锁的一个例子

写锁的例子：

总结：

读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

行锁

偏向InnoDB，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突概率低，并发最高。

set autocommit=0;

提交commit;

默认开启新的连接set autocommit=1;

2个会话都设置了set autocommit=0; 左边提交了右边也要提交才能看到值。

间隙锁

*事务

更新丢失：覆盖

脏读：已修改但未提交的数据

不可重复读：A读取了B已经提交修改的数据，不符合隔离性。

幻读：读到了新增数据

事务隔离级别

默认级别：可重复读

面试题：如何锁定一行

主从复制：

每个slave只有一个master,只能有唯一的服务器ID.

每个master可以有多个slave.

常见配置（一主一从）：

在配置文件[mysqld]下。

主机：

从机：

server-id=2

log-bin=mysql-bin

一、手动开启的配置：

主机上授权：

show master status;

grant replication slave on *.* to 'panhui'@'%' identified by '99209974';

从机上的授权：

CHANGE MASTER TO MASTER_HOST='IP地址',

MASTER_USER='',

MASTER_PASSWORD='',

MASTER_LOG_FILE='mysqlbin.数字',

MASTER_LOG_POS=具体值;

CHANGE MASTER TO MASTER_HOST='192.168.0.13',

MASTER_USER='panhui',

MASTER_PASSWORD='99209974',

MASTER_LOG_FILE='mysqlbin.000782',

MASTER_LOG_POS=106;

上面命令查询的结果值。

二、配置文件要自动运行的配置：

主机配置相同

从机配置：

server-id=2

log-bin=mysql-bin

master-host=主机ip地址

master-user=panhui

master-password=xxxxx

master-port=3306

可选：

expire_logs_day=10

mastrt-connect-retry=60

#需要复制的数据库

replicate-do-db=数据库名字

#不复制的数据库

replicate-ignore-db=数据库名字

命令里面运行的：

开始：start slave;

关闭：stop slave;

查看主从复制状态：show slave status\G

stop slave;

reset slave all;

  

mysql