DBA之路 5_MySQL_外连接及information_schema及索引

1.distinct

处理过程:
将列里面所有的值进行排序,然后将连续的一段进行匹配,直到匹配到值不相同的一个,然后进行计数。去重排序,此上针对8,0以前版本
select distinct(列) from 表
mysql> select distinct(school.student.sname) from school.student;
+---------+
| sname   |
+---------+
| zhang3  |
| zhang4  |
| li4     |
| wang5   |
| zh4     |
| zhao4   |
| ma6     |
| oldboy  |
| oldgirl |
| oldp    |
+---------+
10 rows in set (0.00 sec)
实际用法:
mysql> select count(*) from world.city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

mysql> select count(distinct(world.city.name)) from world.city;
+----------------------------------+
| count(distinct(world.city.name)) |
+----------------------------------+
|                             3998 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> 


2.别名

2.1表别名

SELECT stu.sname,co.cname
FROM student as stu
JOIN score as sc
ON stu.sno = sc.sno
JOIN course as co
ON sc.cno = co.cno
WHERE stu.sname = 'zhang3';

2.2列别名

mysql> select count(distinct(world.city.name)) as a from world.city;
+------+
| a    |
+------+
| 3998 |
+------+
1 row in set (0.01 sec)

3.外连接

左连接:left join
SELECT
    a. NAME,
    b. NAME,
    b.surfacearea
FROM
    city AS a
LEFT JOIN country AS b ON a.countrycode = b. CODE
AND a.population < 100
左边是小数据,右边是大数据
右连接(right join)
USE world;

SELECT
    a. NAME,
    b. NAME,
    b.surfacearea
FROM
    city AS a
right JOIN country AS b ON a.countrycode = b. CODE
AND a.population < 100
HAVING b.surfacearea<'50';

4.information_schema.tables

元数据:存放在基表中,也叫元数据表,ibdata(数据词典,不能查看,存放了数据信息相当于数据的inode),基表无法直接查询和修改。
---修改通过DDL进行元数据修改
---show语句查询,desc(show),information_schema(全局类的统计和)
mysql root@10.0.0.68:information_schema> desc `TABLES`   先切到information_shema库然后通过desc 'tables' 查询                                  
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | <null>  |       |
| VERSION         | bigint(21) unsigned | YES  |     | <null>  |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | <null>  |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | <null>  |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | <null>  |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | <null>  |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | <null>  |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | <null>  |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | <null>  |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | <null>  |       |
| CREATE_TIME     | datetime            | YES  |     | <null>  |       |
| UPDATE_TIME     | datetime            | YES  |     | <null>  |       |
| CHECK_TIME      | datetime            | YES  |     | <null>  |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | <null>  |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | <null>  |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | <null>  |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
常用:
TABLE_SCHEMA 表所在库 
TABLE_NAME 表名
ENGINE 表的存储引擎
TABLE_ROWS 表的行数
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引长度
实例:
1.  统计所有库下表的总大小
select table_schema,count(table_name),sum(avg_row_length*table_rows+index_length)/1024/1024 as table_MB
from information_schema.tables
group by table_schema;
2.统计库下总大小
select 
sum(avg_row_length*table_rows+index_length)/1024/1024 as table_MB
from information_schema.tables


councat()
mysql> select concat(user,"|",host) from mysql.user;
+-------------------------+
| concat(user,"|",host)   |
+-------------------------+
| oldguo|10.0.0.%         |
| root|10.0.0.%           |
| zhihu|10.0.0.%          |
| oldboy|172.16.1.%       |
| mysql.session|localhost |
| mysql.sys|localhost     |
| root|localhost          |
+-------------------------+
7 rows in set (0.00 sec)

mysql> select concat("廖婉丽是傻子");
+------------------------------+
| concat("廖婉丽是傻子")       |
+------------------------------+
| 廖婉丽是傻子                 |
+------------------------------+
1 row in set (0.00 sec)

show

show databases;          查看所有数据库
show tables;             查看当前库下所有表
show tables from world;   查看world数据库下的所有表
show create database 库; 查看建库语句
show create table 表;    查看建表语句
show grants for root@'localhost';查看用户权限
show charset;            查看字符集
show collation;          校对
show full processlist;   查看连接情况
show status;             查看数据库状态
show engines;            查看存储引擎
show status like '%lock%'模糊
show variables;          查看变量
show engine innodb status 查看所有innodb引擎状态情况
show binary logs;        查看二进制日志情况
show binlog events in    查看二进制日志事件
show relaylog events in  查看relay日志事件
show slave status        查看从库状态
show master status       查看数据库binlog位置信息
show privileges;         查看权限
show index from 表;      查看表的索引
show errors;             查看上一条报错
show warnings;           查看上一条警告
show colums from city    查看表的列定义信息

索引(称为index或key)

作用

提供了类似于书中目录的作用,目的是为了优化查询

种类(算法)

**B树**
Hash索引
R树
Full text 全文索引
GIS

B树索引

image.png
B-tree
B+Tree 在范围查询方面提供了更好的性能(> < >= <= like)
B*Tree
向上生成父节点的时候,总是取子节点的最小值
B+数针对范围查询进行优化,在B数索引的基础上在叶节点上添加双向指针。B*数在枝节点上添加了双向指针。

6.在功能上进行分类******

辅助索引(S)
[secondary][index]

聚集索引(C)
[clustered][index]


6.1辅助索引(S)怎么构建B树结构的?
(1)辅助索引基于表的列进行生成
(2)取出索引列的所有值(取出所有键值)
(3)进行所有键值的排序
(4)将所有的键值按顺序落到BTree索引的叶子节点上
(5)进而生成枝节点和根节点
(6)叶子节点除了存储键值之外还存储指向相邻叶的指针,
另外还会保存指向原表数据的指针。
6.2聚集索引(C)怎么构建B树结构的?
聚集索引组织存储,聚集索引相当于将数据行整行存储在叶子节
点上,依据于聚集索引列。
(1)建表时有主键列
(2)表中数据存储时,会按照主键列顺序有序的存储数据行的内容,
在数据页上(这个动作叫做聚集索引组织表)。
(3)表中的数据页被作为聚集索引的叶子节点。
(4)把叶子节点的主键值生成上层的枝节点和根节点
6.3聚集索引和辅助索引构成区别
(1)聚集索引只有一个,非空,唯一,一般是主键。
(2)辅助索引可以有多个,是配合聚集索引使用。
(3)聚集索引叶子节点,就是磁盘的数据行存储的数据页。
(4)MySQL是根据聚集索引,组织存储数据,数据存储的时候就是按照聚集索引的顺序进行存储。
(5)辅助索引,只会提取索引键值,进行自动排序生成B树结构。
6.4辅助索引细分
1.单列的辅助索引
2.联合多列辅助索引(覆盖索引)
3.唯一索引
6.5关于索引树的高度受什么影响
(1)数据行个数(方案:分表)
(2)索引列的字符长度(方案:前缀索引)
(3)char,varchar(方案:表设计方案)
(4)enum(生成下标索引),在数据数量上优化索引高度,能用则用

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 222,681评论 6 517
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 95,205评论 3 399
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 169,421评论 0 362
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 60,114评论 1 300
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 69,116评论 6 398
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 52,713评论 1 312
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 41,170评论 3 422
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 40,116评论 0 277
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 46,651评论 1 320
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 38,714评论 3 342
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,865评论 1 353
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 36,527评论 5 351
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 42,211评论 3 336
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 32,699评论 0 25
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,814评论 1 274
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 49,299评论 3 379
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 45,870评论 2 361

推荐阅读更多精彩内容