mysql advanced (1) lecture 1-13

1.mysql configuration file

I. linux:/etc/my.cnf file windows:my.ini file

Binary log logbin: master-slave copy error log: it is off by default, recording serious warning and error information, details of each startup and shutdown.

Query log: log

Data file:. myd (store data). myi (store index) frm (store table structure)

2, The logical structure of mysql

From top to bottom are connection layer (client connection), service layer (SQL analysis optimization cache, etc.), storage engine layer (really responsible for data storage and extraction) data storage layer (data is stored on file system, and the interaction with storage engine layer is completed)

3, Index optimization analysis

Possible reasons for performance degradation, long execution time and long waiting time: bad query statement, index failure, too many associated queries, server tuning parameters (buffer, number of threads)

SQL execution statement order:

The order of machine reading: start from; the following is the sequence diagram of SQL parsing;

Table structure (modified in notepad + +):

create table `tbl_dept` (
 `id` int(11) auto_increment,
  `deptName` varchar(30) default null,
  `locAdd` varchar(40) default null,
  primary key(`id`)
)engine=innodb auto_increment=1 default charset=utf8;

create table `tbl_emp` (
 `id` int(11) auto_increment,
  `name` varchar(20) default null,
  `deptId` int(11) default null,
   primary key(`id`),
   key `fk_dept_id` (`deptId`)
   #constraint `fk_dept_id` foreign key(`deptId`)  references `tbl_dept`(`id`)
)engine=innodb auto_increment=1 default charset=utf8;

insert into tbl_dept(deptName,locAdd) values('RD',11);
insert into tbl_dept(deptName,locAdd) values('HR',12);
insert into tbl_dept(deptName,locAdd) values('MK',13);
insert into tbl_dept(deptName,locAdd) values('MIS',14);
insert into tbl_dept(deptName,locAdd) values('FD',15);


insert into tbl_emp(Name,deptId) values('z3',1);
insert into tbl_emp(Name,deptId) values('z4',1);
insert into tbl_emp(Name,deptId) values('z5',1);

insert into tbl_emp(Name,deptId) values('w5',2);
insert into tbl_emp(Name,deptId) values('w6',2);


insert into tbl_emp(Name,deptId) values('s7',3);
insert into tbl_emp(Name,deptId) values('s8',4);
insert into tbl_emp(Name,deptId) values('s9',51);

 

Tags: SQL MySQL Linux Windows

Posted on Fri, 31 Jan 2020 16:28:53 -0500 by Cheers