2019-06-19 mysql索引

1. distinct 去重復(fù)?

select sum(單價(jià)*數(shù)量) from (select 牌子,單價(jià),數(shù)量 from 啤酒 union all select 牌子,單價(jià),數(shù)量 from 飲料union all select 牌子,單價(jià),數(shù)量 from 礦泉水);

2.別名

2.1.表別名

SELECT a.tname ,GROUP_CONCAT(d.sname)

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

WHERE a.tname='oldguo' AND c.score<60

GROUP BY a.tno;

2.2.列別名

selec count（distinct（name）） as? 個(gè)數(shù)? from? world.city;

--- 統(tǒng)計(jì)一下每個(gè)庫的真實(shí)數(shù)據(jù)量

每張表數(shù)據(jù)量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH

SELECT

table_schema,

COUNT(?table_name?),

SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024?AS?ToTAL_MB

FROM?information_schema.TABLES

GROUP?BY?table_schema;

--?information_schema

---?查詢整個(gè)數(shù)據(jù)庫中所有的庫對(duì)應(yīng)的表名

例如：

world???city

world???country

oldboy??oldguo

SELECT?table_schema,table_name

FROM?information_schema.tables;

---?查詢world和school庫下的所有表名

SELECT?table_schema,table_name

FROM?information_schema.tables

WHERE?table_schema='world'

UNION?ALL

SELECT?table_schema,table_name

FROM?information_schema.tables

WHERE?table_schema='school';

---?查詢整個(gè)數(shù)據(jù)庫中所有的庫對(duì)應(yīng)的表名呀酸，每個(gè)庫顯示成一行

SELECT?table_schema,GROUP_CONCAT(table_name)

FROM?information_schema.tables

GROUP?BY??table_schema;

---?統(tǒng)計(jì)一下每個(gè)庫下的表的個(gè)數(shù)

SELECT?table_schema,COUNT(table_name)

FROM?information_schema.tables

GROUP?BY??table_schema;

---?統(tǒng)計(jì)一下每個(gè)庫的真實(shí)數(shù)據(jù)量

每張表數(shù)據(jù)量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH

SELECT

SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024?AS?total_mb

FROM?information_schema.TABLES

---?information_schema.tables+CONCAT(),拼接命令

---?使用方法舉例

mysql>?SELECT?CONCAT(USER,"@","'",HOST,"'")?FROM?mysql.user;

---?生產(chǎn)需求1

mysqldump?-uroot?-p123??world?city?>/tmp/world_city.sql

---?模仿以上命令尝丐，對(duì)整個(gè)數(shù)據(jù)庫下的1000張表進(jìn)行單獨(dú)備份控嗜，

---?排除sys，performance喇潘，information_schema

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('sys','performance','information_schema')

INTO?OUTFILE?'/tmp/

bak.sh';

vim?/etc/my.cnf

secure-file-priv=/tmp

/etc/init.d/mysqld?restart

5.show

show databases; ##查看所有數(shù)據(jù)庫名

show tables；##查看當(dāng)前庫下的表名

show tables from world； ##查看world庫下的表名

show create database啄寡；##查看建庫語句

show create table； ##查看建表語句

show grants for root@‘localhost’哩照； ##查看用戶的權(quán)限信息

show charset挺物；##查看字符集

show collation；##查看校對(duì)規(guī)則

show full processlist飘弧； ##查看數(shù)據(jù)庫連接情況

show status识藤； ##查看數(shù)據(jù)庫的整體情況

show status like '%lock' ##模糊查詢數(shù)據(jù)庫的整體情況

show variables； ##查看數(shù)據(jù)庫的所有變量情況

show variables like ‘innodb’;##查看數(shù)據(jù)庫的所有變量情況

show engine; ##查看所有支持存儲(chǔ)引擎狀態(tài)

show engine innodb status; ##查看所有indodb存儲(chǔ)引擎狀態(tài)情況

show binary logs次伶；##查看二進(jìn)制日志情況

show binlog events in ##查看二進(jìn)制日志事件

show relaylog events in ##查看relay日志事件

show slave status ##查看從庫狀態(tài)

show master status ##查看數(shù)據(jù)庫binlog位置信息

show index from ##查看表的索引情況????

6.索引

6.1 索引的作用

提供了類似于書中目錄的作用痴昧，目的是為了優(yōu)化查詢

6.2 索引的種類算法

B樹索引

Hash索引

R樹

Full text

GIS

6.3 B樹算法普及

B-樹

B+樹

B*樹

6.4 在功能上的分類*****

6.4.1 輔助索引（s）怎么構(gòu)造B樹結(jié)構(gòu)？

1）輔助索引是基于表的列生成的

2）取出索引列的所有值（取出所有鍵值）

3）進(jìn)行所有鍵值的排序

4）將所有鍵值按順序落到Btree索引的葉子節(jié)點(diǎn)上

5）進(jìn)而生成枝節(jié)點(diǎn)和根節(jié)點(diǎn)

6）葉子節(jié)點(diǎn)除了儲(chǔ)存鍵值以外還會(huì)生成指向原表內(nèi)容的指針学少。

6.4.2 聚集索引（c）怎么構(gòu)建B樹結(jié)構(gòu)剪个？

1）一般情況下是主鍵

2）表中進(jìn)行數(shù)據(jù)存儲(chǔ)，會(huì)按照ID列的順序版确，有序的存儲(chǔ)一行一行的數(shù)據(jù)到數(shù)據(jù)頁上（這個(gè)動(dòng)作叫聚集組織索引表）

3）表中的數(shù)據(jù)頁被作為聚集索引的葉子節(jié)點(diǎn)

4）把葉子節(jié)點(diǎn)的主鍵值生成上層枝節(jié)點(diǎn)和根節(jié)點(diǎn)扣囊。

6.4.3? 聚集索引和輔助索引構(gòu)成區(qū)別總結(jié)

聚集索引只能有一個(gè)，非空唯一绒疗，一般是主鍵

輔助索引可以有多個(gè)侵歇，是配合聚集索引使用的

聚集索引葉子節(jié)點(diǎn)，就是磁盤的數(shù)據(jù)行存儲(chǔ)的數(shù)據(jù)頁

mysql是根據(jù)聚集索引吓蘑，組織存儲(chǔ)數(shù)據(jù)惕虑，數(shù)據(jù)存儲(chǔ)時(shí)就是按照聚集索引的順序進(jìn)行存儲(chǔ)數(shù)據(jù)

輔助索引，只會(huì)提取索引鍵值磨镶，進(jìn)行自動(dòng)排序的生成B樹結(jié)構(gòu)

6.5 輔助索引細(xì)分

單列的輔助索引

聯(lián)合多列輔助索引（覆蓋索引）

唯一索引

6.6 關(guān)于索引樹的高度受什么影響溃蔫？

1）數(shù)據(jù)行多

2）索引列字符長度，前綴索引

3）char? varchar 琳猫，表設(shè)計(jì)

4）enum 優(yōu)化索引高度