-- 查看数据库的最大连接数
-- 默认连接是151个 最大连接数是十万个
show variables like 'max_connections';
-- 查看数据库连接到超时时间 
-- 默认连接超时时间是 28800 S = 8 H
show variables like 'wait_timeout';

-- 开启慢查询日志
show variables like 'slow_query%';
-- MySQL 5.7 
-- 通过修改配置文件 - my.cnf - 但是貌似无效
slow_query_log=on
long_query_time=2
slow_query_log_file=/var/lib/mysql/ali-mysql-slow.log

-- MySQL 8.0
Show variables like '%slow_query%'; -- 可以用这个查询所有的变量

-- 第一步
set global log_output='TABLE'; -- 开启慢日志,纪录到 mysql.slow_log 表
set global long_query_time=2; -- 设置超过2秒的查询为慢查询
set global slow_query_log='ON';-- 打开慢日志记录

-- 第二步 运行一下比较慢的功能,执行下面的语句
select convert(sql_text using utf8) sql_text from mysql.slow_log -- 查询慢sql的日志
-- 第三步 记得关上日志
set global slow_query_log='OFF'; -- 如果不用了记得关上日志

-- 找到工具位置
[root@iZ8vb7i30dbuk6b1jof0zpZ mysql]#  whereis mysqldumpslow
mysqldumpslow: /usr/bin/mysqldumpslow /usr/share/man/man1/mysqldumpslow.1.gz
-- mysqldumpslow --help 查看参数信息
[root@iZ8vb7i30dbuk6b1jof0zpZ mysql]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

[root@iZ8vb7i30dbuk6b1jof0zpZ mysql]#

-- 查看服务端的一些状态
SHOW GLOBAL STATUS

-- 查看服务端的线程
SHOW PROCESSLIST -- 连接的线程

-- 查看搜索引擎的状态
SHOW ENGINE INNODB STATUS

DROP TABLE IF EXISTS course;
CREATE TABLE `course` (
    `cid` int(3) DEFAULT NULL,
    `cname` varchar(20) DEFAULT NULL,
    `tid` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS teacher;
CREATE TABLE `teacher` (
    `tid` int(3) DEFAULT NULL,
    `tname` varchar(20) DEFAULT NULL,
    `tcid` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS teacher_contact;
CREATE TABLE `teacher_contact` (
    `tcid` int(3) DEFAULT NULL,
    `phone` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `course` VALUES ('1', 'mysql', '1');
INSERT INTO `course` VALUES ('2', 'jvm', '1');
INSERT INTO `course` VALUES ('3', 'juc', '2');
INSERT INTO `course` VALUES ('4', 'spring', '3');

INSERT INTO `teacher` VALUES ('1', 'qingshan', '1');
INSERT INTO `teacher` VALUES ('2', 'jack', '2');
INSERT INTO `teacher` VALUES ('3', 'mic', '3');

INSERT INTO `teacher_contact` VALUES ('1', '13688888888');
INSERT INTO `teacher_contact` VALUES ('2', '18166669999');
INSERT INTO `teacher_contact` VALUES ('3', '17722225555');

-- 查询mysql课程的老师手机号
EXPLAIN SELECT tc.phone
FROM teacher_contact tc
WHERE tcid = (
    SELECT tcid
    FROM teacher t
    WHERE t.tid = (
        SELECT c.tid
        FROM course c
        WHERE c.cname = 'mysql'
    )
);


-- 查询ID为1或2的老师教授的课程
EXPLAIN SELECT cr.cname
FROM (
    SELECT * FROM course WHERE tid = 1
    UNION
    SELECT * FROM course WHERE tid = 2
) cr;


-- ALTER TABLE teacher_contact DROP PRIMARY KEY;
ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid); 


-- ALTER TABLE teacher DROP INDEX idx_tcid;
ALTER TABLE teacher ADD INDEX idx_tcid (tcid);


explain SELECT * FROM teacher where tcid = 3;

explain select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;3

-- ALTER TABLE teacher DROP INDEX idx_tid;
ALTER TABLE teacher ADD INDEX idx_tid (tid);

EXPLAIN SELECT * FROM teacher t WHERE t.tid <3;
-- 或
EXPLAIN SELECT * FROM teacher t WHERE tid BETWEEN 1 AND 2;
-- 或
EXPLAIN SELECT * FROM teacher_contact t WHERE tcid in (1,2,3);

EXPLAIN SELECT tid FROM teacher;

EXPLAIN select 1 from dual where 1=1

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

explain select phone from user_innodb where phone='126';

EXPLAIN SELECT tid FROM teacher ;

EXPLAIN select * from user_innodb where phone ='13866667777';

EXPLAIN select * from user_innodb where name ='青山' order by id; 

EXPLAIN select DISTINCT(tid) from teacher t
EXPLAIN select tname from teacher group by tname;
EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid;