一本性无码专区,尤物一级在线

show  databases;                          #查看所有數(shù)據(jù)庫
show tables;                                          #查看當(dāng)前庫的所有表
SHOW TABLES FROM                        #查看某個(gè)指定庫下的表
show create database world                #查看建庫語句
show create table world.city                #查看建表語句
show  grants for  root@'localhost'       #查看用戶的權(quán)限信息
show  charset；                                   #查看字符集
show collation                                      #查看校對規(guī)則
show processlist;                                  #查看數(shù)據(jù)庫連接情況
show index from                                 #表的索引情況
show status                                         #數(shù)據(jù)庫狀態(tài)查看
SHOW STATUS LIKE '%lock%';         #模糊查詢數(shù)據(jù)庫某些狀態(tài)
SHOW VARIABLES                             #查看所有配置信息
SHOW variables LIKE '%lock%';          #查看部分配置信息
show engines                                       #查看支持的所有的存儲引擎
show engine innodb status\G               #查看InnoDB引擎相關(guān)的狀態(tài)信息
show binary logs                                    #列舉所有的二進(jìn)制日志
show master status                                 #查看數(shù)據(jù)庫的日志位置信息
show binlog evnets in                             #查看二進(jìn)制日志事件
show slave status \G                             #查看從庫狀態(tài)
SHOW RELAYLOG EVENTS               #查看從庫relaylog事件信息
desc  (show colums from city)               #查看表的列定義信息
http://dev.mysql.com/doc/refman/5.7/en/show.html

information_schema

介紹:
1. 虛擬庫
2. 存的是元數(shù)據(jù)查詢方法(定義好的視圖)
3. 元數(shù)據(jù): 數(shù)據(jù)字典,狀態(tài),權(quán)限,配置等
4. 元數(shù)據(jù)存儲在"基表"中,基表是被保護(hù)的.不能任意修改和查詢
5. 基表管理:
修改類: DDL DCL
查詢類: show , information_schema(tables)

information_schema.tables
DESC information_schema.TABLES
TABLE_SCHEMA ---->庫名
TABLE_NAME ---->表名
ENGINE ---->引擎
TABLE_ROWS ---->表的行數(shù)
AVG_ROW_LENGTH ---->表中行的平均行（字節(jié)）
INDEX_LENGTH ---->索引的占用空間大?。ㄗ止?jié)）

(1) "資產(chǎn)" 統(tǒng)計(jì)
表的個(gè)數(shù)
select count(*) from information_schema.tables
WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema');
每個(gè)庫的表名字,和個(gè)數(shù)
select table_schema,group_concat(table_name),count(table_name)
from information_schema.tables
WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema')
group by table_schema;

表的大小統(tǒng)計(jì)(TABLE_ROWSAVG_ROW_LENGTH+INDEX_LENGTH)
select
table_schema,
table_name ,
(TABLE_ROWSAVG_ROW_LENGTH+INDEX_LENGTH)/1024 AS table_size_KB
from information_schema.tables
WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema');

每個(gè)庫的大小統(tǒng)計(jì)
select
table_schema,
sum((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH))/1024 AS db_size_KB
from information_schema.tables
WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema')
group by table_schema;

(2) 語句拼接
mysql> select concat("alter table ",table_schema,".",table_name," discard tablespace;") from information_schema.tables;

mysql> select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," >/backup/",table_schema,"_",table_name,".sql") from information_schema.tables;

查詢整個(gè)數(shù)據(jù)庫中所有庫和所對應(yīng)的表信息

SELECT table_schema,GROUP_CONCAT(table_name)
FROM information_schema.tables
GROUP BY table_schema;

統(tǒng)計(jì)所有庫下的表個(gè)數(shù)

SELECT table_schema,COUNT(table_name)
FROM information_schema.TABLES
GROUP BY table_schema

查詢所有innodb引擎的表及所在的庫

SELECT table_schema,table_name,ENGINE FROM information_schema.TABLES
WHERE ENGINE='innodb';

統(tǒng)計(jì)world數(shù)據(jù)庫下每張表的磁盤空間占用

SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS size_KB
FROM information_schema.tables WHERE TABLE_SCHEMA='world';

統(tǒng)計(jì)所有數(shù)據(jù)庫的總的磁盤空間占用

SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWSAVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;
mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWSAVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"

生成整個(gè)數(shù)據(jù)庫下的所有表的單獨(dú)備份語句

模板語句：
mysqldump -uroot -p123 world city >/tmp/world_city.sql
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
FROM information_schema.tables
WHERE table_schema NOT IN('information_schema','performance_schema','sys')
INTO OUTFILE '/tmp/bak.sh' ;

CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )

107張表，都需要執(zhí)行以下2條語句

ALTER TABLE world.city DISCARD TABLESPACE;
ALTER TABLE world.city IMPORT TABLESPACE;
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/dis.sql';

===============================================
SELECT a.tname,
COUNT(CASE WHEN sc.score>=60 THEN sc.score END )/COUNT(sc.score) AS 及格率
FROM teacher AS a
JOIN course AS b
ON a.tno=b.tno
JOIN sc
ON b.cno=sc.cno
GROUP BY a.tno;
================================================
SELECT a.tname,
GROUP_CONCAT(CASE WHEN c.score >= 85 THEN d.sname END) AS 優(yōu)秀,
GROUP_CONCAT(CASE WHEN c.score >= 70 AND c.score <85 THEN d.sname END) AS 良好,
GROUP_CONCAT(CASE WHEN c.score >= 60 AND c.score <70 THEN d.sname END) AS 一般,
GROUP_CONCAT(CASE WHEN c.score <60 THEN d.sname END) AS 不及格
FROM teacher AS a
JOIN course AS b
ON a.tno=b.tno
JOIN sc AS c
ON b.cno=c.cno
JOIN student AS d
ON c.sno=d.sno
GROUP BY a.tno;
=================================================
索引及執(zhí)行計(jì)劃管理 *****

索引
1.1 介紹
相當(dāng)于書本中的目錄,能夠起到優(yōu)化查詢.
1.2 MySQL索引種類
BTREE *****
RTREE
HASH
FullText
地理位置
1.3 BTREE 查找算法簡介
B-tree
B+Tree
B*Tree

b+tree.png

總結(jié):
1. 會將所有值進(jìn)行排序,均勻落到葉子節(jié)點(diǎn)(16K).
2. 會提取每個(gè)葉子節(jié)點(diǎn)最小值,生成枝節(jié)點(diǎn),并存儲對應(yīng)葉子節(jié)點(diǎn)指針
3. 會提取枝節(jié)點(diǎn)的最小值,最終生成根節(jié)點(diǎn),并存對應(yīng)枝節(jié)點(diǎn)指針
4. 查找數(shù)據(jù)數(shù),根據(jù)你所要的值,進(jìn)入根進(jìn)行判斷,選擇下層枝節(jié)點(diǎn)路徑,進(jìn)行判斷,最終找葉子節(jié)點(diǎn)指針,取出數(shù)據(jù).
5. B+tree 在葉子節(jié)點(diǎn)添加了雙向指針,來優(yōu)化范圍查找(> < >= <= between and like)
6. B*tree 在枝節(jié)點(diǎn)添加了雙向指針.

1.4 Btree 功能分類
1.4.1 聚簇索引(聚集索引)
前提: 表中創(chuàng)建了主鍵,MySQL會把主鍵作為聚簇索引
構(gòu)建過程:
(1) 如果有主鍵,以主鍵為聚集索引列,如果沒有,會選唯一鍵,都沒有,會自動生成一個(gè)隱藏. (2) 所有數(shù)據(jù)錄入時(shí)就有序存儲了 (3) 葉子存儲的整行的數(shù)據(jù),直接把原表數(shù)據(jù)的數(shù)據(jù)頁當(dāng)做葉子節(jié)點(diǎn) (4) 提取葉子節(jié)點(diǎn)中最小的ID值,生成枝節(jié)點(diǎn),提取枝節(jié)點(diǎn)最小值,生成根節(jié)點(diǎn) 當(dāng)查詢條件是ID時(shí),可有受到聚集索引的優(yōu)化
1.4.2 輔助索引(非聚簇索引,二級索引)
前提: 按照業(yè)務(wù)需求構(gòu)建合適索引
構(gòu)建過程:
(1) 人為執(zhí)行創(chuàng)建索引命令
(2) 提取索引列所有值+對應(yīng)的主鍵值,按索引列進(jìn)行排序,均勻落到葉子節(jié)點(diǎn)中
(3) 生成枝節(jié)點(diǎn)和根節(jié)點(diǎn)

當(dāng)查詢條件為輔助索引列時(shí)
(1) 遍歷輔助索引,找到對應(yīng)的ID值
(2) 根據(jù)ID值回表查詢,找到數(shù)據(jù)行

聚集索引和輔助索引構(gòu)成區(qū)別

聚集索引只能有一個(gè),非空唯一,一般時(shí)主鍵
輔助索引,可以有多個(gè),時(shí)配合聚集索引使用的
聚集索引葉子節(jié)點(diǎn),就是磁盤的數(shù)據(jù)行存儲的數(shù)據(jù)頁
MySQL是根據(jù)聚集索引,組織存儲數(shù)據(jù),數(shù)據(jù)存儲時(shí)就是按照聚集索引的順序進(jìn)行存儲數(shù)據(jù)
輔助索引,只會提取索引鍵值,進(jìn)行自動排序生成B樹結(jié)構(gòu)

1.5 索引樹高度
越低越好:
(1) 數(shù)據(jù)行多
解決方案: 分區(qū)表, 分庫分表(分布式架構(gòu))
(2) 索引的列值長
前綴索引,分庫分表(分布式架構(gòu))
(3) 數(shù)據(jù)類型
char varchar() enum
(4) 主鍵值過長

1.6 輔助索引細(xì)分
單列
多列聯(lián)合索引 *****
唯一索引
前綴索引

=================================================

索引的管理
2.1 模擬數(shù)據(jù)庫數(shù)據(jù)
drop database if exists oldboy;
create database oldboy charset utf8mb4 collate utf8mb4_bin;
use oldboy;
create table t100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);

delimiter //
create procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()61),1),substring(str,1+floor(rand()61),1));
set str4=concat(substring(str,1+floor(rand()61),2),substring(str,1+floor(rand()61),2));
set i=i+1;
insert into t100w values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter ;

插入100w條數(shù)據(jù)：
call rand_data(1000000);
commit;

2.2 命令
2.1.1 查詢表的索引
use world;
desc city;

Key

PRI 主鍵索引
MUL 輔助索引
UNI 唯一索引

mysql> show index from city;

2.2.2 創(chuàng)建索引
mysql> alter table city add index idx_name(name);
mysql> alter table city add index idx_sub_anme(name(4));
mysql> alter table city add index idx_c_p(countrycode,population);
mysql> alter table aaa add unique index idx_name(xxxx);

2.2.3 刪除索引
mysql> alter table city drop index idx_name;
mysql> alter table city drop index idx_sub_anme;
mysql> alter table city drop index idx_c_p;

2.3 壓力測試
2.3.1 未優(yōu)化前
mysqlslap --defaults-file=/etc/my.cnf
--concurrency=100 --iterations=1 --create-schema='oldboy'
--query="select * from oldboy.t100w where k2='780P'" engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose

2.3.2 優(yōu)化后

執(zhí)行計(jì)劃獲取及分析
3.1 介紹
desc
explain
獲取的是優(yōu)化器選擇完的執(zhí)行計(jì)劃.

mysql>

3.3 執(zhí)行計(jì)劃內(nèi)容說明
table : 表名
type : 查詢類型(全表掃描,索引掃描(多種級別),查找不到數(shù)據(jù))
possible_keys : 可能會使用的索引
key : 真正使用的索引
key_len : 索引的覆蓋長度(聯(lián)合索引)
Extra : 額外的信息

3.4 type 詳細(xì)說明
(1) ALL 全表掃描
mysql> desc select * from world.city;
mysql> desc select * from t100w where k1='AA';
mysql> desc select * from t100w where k2 like '%AA';
mysql> desc select * from t100w where k2 <> 'BBAA';
注意: 主鍵的不等值查詢不會全表掃描

(2) index 全索引掃描
mysql> desc select k2 from t100w;

(3) range 索引范圍

< >= <= like
mysql> desc selectfrom world.city where id <10;
mysql> desc selectfrom world.city where countrycode like 'CH%';

or , in
mysql> desc select*from world.city where countrycode in ('CHN','USA');

改寫:
desc
selectfrom world.city where countrycode = 'CHN'
union all
selectfrom world.city where countrycode = 'USA';

(4) ref 輔助索引等值查詢
select*from world.city where countrycode = 'CHN'

(5) eq_ref 多表連接查詢中,被連接的表的連接條件列是主鍵或者唯一鍵

mysql> desc
select a.name,b.name,a.population ,b.surfaceArea
from city as a
join country as b
on a.countrycode=b.code
where a.population=42;

alter table city add index idx_po(population);

(6) const(system)
主鍵或者唯一鍵的等值查詢

3.5 key_len

數(shù)字類型: not null 沒有not null
n1 int 4 bytes 最大存儲長度是4字節(jié) 4 4+1
n2 tinyint 1 bytes 最大存儲長度是1字節(jié) 1 1+1

字符串類型:
utf8mb4 : 4 not null 沒有not null
char(10) 104 40 410+1
varchar(10) 104 104+2 10*4+2+1

聯(lián)合索引應(yīng)用長度判斷:
mysql> create table t1 (id int not null ,c1 char(10) not null ,c2 varchar(10))charset utf8mb4;
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| c1 | char(10) | NO | | NULL | |
| c2 | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table t1 add index idx_lh(id,c1,c2);
4+40+43

mysql> desc format=json select * from t1 where id=10 and c1='a' and c2='a';
mysql> desc format=json select * from t1 where c1='a' or id=10 or c2='a';

3.6 聯(lián)合索引應(yīng)用的道道. *****

(1) 建立聯(lián)合索引的順序,唯一值多的列放在最左邊
(2) 在聯(lián)合條件查詢中,中間遇到不等值,聯(lián)合索引截止到不等值查詢.
優(yōu)化方式: 建立索引時(shí),把不等值的列放在最后面.
(3) 多子句時(shí),按照SQL的邏輯執(zhí)行順序,建立聯(lián)合索引
where aa group by bb
where xx order by yy
(4) 在查詢條件中,缺少聯(lián)合索引中間缺少任意一列,都會影響后續(xù)所有條件列的索引應(yīng)用

extra
using filesort ----> order by group by distinct union
using temporary join on

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

索引

索引

information_schema

聚集索引和輔助索引構(gòu)成區(qū)別

Key

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

索引

information_schema

聚集索引和輔助索引構(gòu)成區(qū)別

Key

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av