MySQL : , , ( )

MySQL : , , ( )

.

1

MySQL MySQL MySQL long_query_time SQL long_query_time SQL long_query_time 10 10 SQL sql 5 SQL 5 sql explain MySQL

1.

slow_query_log OFF slow_query_log
SHOW VARIABLES LIKE '%slow_query_log%';

set global slow_query_log=1 MySQL

my.cnf my.cnf [mysqld] slow_query_log slow_query_log_file MySQL my.cnf
slow_query_log =1 slow_query_log_file=/var/lib/mysql/atguigu-slow.log
slow_query_log_file host_name-slow.log slow_query_log_file

2. SQL

long_query_time long_query_time 10
SHOW VARIABLES LIKE 'long_query_time%';

my.cnf long_query_time mysql long_query_time

3.

set global long_query_time=1
1 sql

long_query_time

SHOW VARIABLES LIKE 'long_query_time%';
set session long_query_time=1 session ;

4. SQL

sql

5.

show global status like '%Slow_queries%';

6.

mysqld slow_query_log=1; slow_query_log_file=/var/lib/mysql/atguigu-slow.log long_query_time=3; log_output=FILE

2 mysqldumpslow

SQL MySQL mysqldumpslow mysqldumpslow
mysqldumpslow --help

:

10 SQL mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 10 SQL mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log 10 mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log | more mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

3

1.

# create database bigData; use bigData; #1 dept CREATE TABLE dept( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20) NOT NULL DEFAULT "", loc VARCHAR(13) NOT NULL DEFAULT "" ) ENGINE=INNODB DEFAULT CHARSET=UTF8 ; #2 emp CREATE TABLE emp( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /* */ ename VARCHAR(20) NOT NULL DEFAULT "", /* */ job VARCHAR(9) NOT NULL DEFAULT "",/* */ mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/* */ hiredate DATE NOT NULL,/* */ sal DECIMAL(7,2) NOT NULL,/* */ comm DECIMAL(7,2) NOT NULL,/* */ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /* */ )ENGINE=INNODB DEFAULT CHARSET=UTF8 ;

2. log_bin_trust_function_creators

This function has none of DETERMINISTIC...... # bin-log, function show variables like 'log_bin_trust_function_creators'; set global log_bin_trust_function_creators=1; # mysqld windows my.ini[mysqld] log_bin_trust_function_creators=1 linux /etc/my.cnf my.cnf[mysqld] log_bin_trust_function_creators=1

3. ,

:

DELIMITER $$ CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) BEGIN ## DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; ## 100 chars_str , DECLARE return_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; ## WHILE i < n DO SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); ##concat substring(a,index,length) index SET i = i + 1; END WHILE; RETURN return_str; END $$ # #drop function rand_string;

:

# DELIMITER $$ CREATE FUNCTION rand_num( ) RETURNS INT(5) BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(100+RAND()*10); RETURN i; END $$ # #drop function rand_num;

4.

emp :

DELIMITER $$ CREATE PROCEDURE insert_emp10000(IN START INT(10),IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; #set autocommit =0 autocommit 0 SET autocommit = 0; REPEAT ## SET i = i + 1; INSERT INTO emp10000 (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num()); UNTIL i = max_num ## END REPEAT; ## COMMIT; ## END $$ # # DELIMITER ; # drop PROCEDURE insert_emp;

dept :

# dept DELIMITER $$ CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO dept (deptno ,dname,loc ) VALUES (START +i ,rand_string(10),rand_string(8)); UNTIL i = max_num END REPEAT; COMMIT; END $$ # # DELIMITER ; # drop PROCEDURE insert_dept;

5.

dept:

DELIMITER ; CALL insert_dept(100,10);

emp:

# emp 50 DELIMITER ; # ; CALL insert_emp(100001,500000); CALL insert_emp10000(100001,10000);

6.

4 Show Profile

mysql SQL http://dev.mysql.com/doc/refman/5.5/en/show-profile.html 15

1. mysql

Show variables like 'profiling'; #

2.

show variables like 'profiling'; set profiling=1;

3. SQL

select * from emp group by id%10 limit 150000; select * from emp group by id%20 order by 5

4. show profiles;

5. SQL show profile cpu,block io for query n (n SQL );

:

type: | ALL -- | BLOCK IO -- IO | CONTEXT SWITCHES -- | CPU -- CPU | IPC -- | MEMORY -- | PAGE FAULTS -- | SOURCE -- Source_function Source_file Source_line | SWAPS --

6.

converting HEAP to MyISAM Creating tmp table : ,
1 select * from emp group by id%20 limit 120000; 2 select * from emp group by id%20 order by 5
Copying to tmp table on disk

locked

5

:

mysql my.cnf # general_log=1 # general_log_file=/path/logfile # log_output=FILE

:

set global general_log=1; # Mysql set global log_output='TABLE'; sql mysql general_log select * from mysql.general_log;

.MySql

CPU RAM I/O

1.

/:

( ) (

:

Lock granularity

2. ( )

MyISAM ,

1

SQL:
-- SQL create table mylock( id int not null primary key auto_increment, name varchar(20) )engine myisam; insert into mylock(name) values('a'); insert into mylock(name) values('b'); insert into mylock(name) values('c'); insert into mylock(name) values('d'); insert into mylock(name) values('e'); select * from mylock; lock table 1 read(write) 2 read(write) ; show open tables; unlock tables;
: mylock read ( )
session_1session_2
mylock READ
session
session
session
session
session
session
Session2
: mylockwrite(MyISAM)
session_1session_2
mylock WRITE Session1 session2
session + + session
Session2

2

MyISAM SELECT MySQL Table Read Lock Table Write Lock
MyISAM 1. MyISAM 2. MyISAM

3. ( )

InnoDB , InnoDB MyISAM TRANSACTION

1

SQL:
create table test_innodb_lock ( a int(11), b varchar(16) )engine=innodb; insert into test_innodb_lock values(1,'b2'); insert into test_innodb_lock values(3,'3'); insert into test_innodb_lock values(4,'4000'); insert into test_innodb_lock values(5,'5000'); insert into test_innodb_lock values(6,'6000'); insert into test_innodb_lock values(7,'7000'); insert into test_innodb_lock values(8,'8000'); insert into test_innodb_lock values(9,'9000'); insert into test_innodb_lock values(1,'b1'); create index test_innodb_a_ind on test_innodb_lock(a); create index test_innodb_lock_b_ind on test_innodb_lock(b); select * from test_innodb_lock;
:
Session_1Session_2
commit;Session_2
commit
1 a =1 1 a =1
:
Session_1Session_2
2000 3000
column b

2

Innodb MyISAM Innodb MyISAM Innodb Innodb MyISAM

3 Select

1. -- (Share Lock):

T A A :
SELECT ... LOCK IN SHARE MODE;
LOCK IN SHARE MODE Mysql

2. eXclusive Lock

T A A :
SELECT ... FOR UPDATE;
FOR UPDATE Mysql

4

1. InnoDB GAP) InnoDB GAP Lock

2. Query

3.

Session_1Session_2
commit;

5

: InnoDB_row_lock

show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits Innodb_row_lock_time Innodb_row_lock_time_avg Innodb_row_lock_time_max Innodb_row_lock_waits

5 Innodb_row_lock_time_avg Innodb_row_lock_waits Innodb_row_lock_time

SELECT * FROM information_schema.INNODB_TRX;

sql

6

,

4.