索引

show  databases;                          #查看所有数据库
show tables;                                          #查看当前库的所有表
SHOW TABLES FROM                        #查看某个指定库下的表
show create database world                #查看建库语句
show create table world.city                #查看建表语句
show  grants for  root@'localhost'       #查看用户的权限信息
show  charset；                                   #查看字符集
show collation                                      #查看校对规则
show processlist;                                  #查看数据库连接情况
show index from                                 #表的索引情况
show status                                         #数据库状态查看
SHOW STATUS LIKE '%lock%';         #模糊查询数据库某些状态
SHOW VARIABLES                             #查看所有配置信息
SHOW variables LIKE '%lock%';          #查看部分配置信息
show engines                                       #查看支持的所有的存储引擎
show engine innodb status\G               #查看InnoDB引擎相关的状态信息
show binary logs                                    #列举所有的二进制日志
show master status                                 #查看数据库的日志位置信息
show binlog evnets in                             #查看二进制日志事件
show slave status \G                             #查看从库状态
SHOW RELAYLOG EVENTS               #查看从库relaylog事件信息
desc  (show colums from city)               #查看表的列定义信息
http://dev.mysql.com/doc/refman/5.7/en/show.html

information_schema

介绍:
1. 虚拟库
2. 存的是元数据查询方法(定义好的视图)
3. 元数据: 数据字典,状态,权限,配置等
4. 元数据存储在"基表"中,基表是被保护的.不能任意修改和查询
5. 基表管理:
修改类: DDL DCL
查询类: show , information_schema(tables)

  1. information_schema.tables
    DESC information_schema.TABLES
    TABLE_SCHEMA ---->库名
    TABLE_NAME ---->表名
    ENGINE ---->引擎
    TABLE_ROWS ---->表的行数
    AVG_ROW_LENGTH ---->表中行的平均行（字节）
    INDEX_LENGTH ---->索引的占用空间大小（字节）

(1) "资产" 统计
表的个数
select count(*) from information_schema.tables
WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema');
每个库的表名字,和个数
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;

表的大小统计(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');

每个库的大小统计
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;

查询整个数据库中所有库和所对应的表信息

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

统计所有库下的表个数

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';

统计world数据库下每张表的磁盘空间占用

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

统计所有数据库的总的磁盘空间占用

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;"

生成整个数据库下的所有表的单独备份语句

模板语句：
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张表，都需要执行以下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 优秀,
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;
=================================================
索引及执行计划管理 *****

  1. 索引
    1.1 介绍
    相当于书本中的目录,能够起到优化查询.
    1.2 MySQL索引种类
    BTREE *****
    RTREE
    HASH
    FullText
    地理位置
    1.3 BTREE 查找算法简介
    B-tree
    B+Tree
    B*Tree


    b+tree.png

总结:
1. 会将所有值进行排序,均匀落到叶子节点(16K).
2. 会提取每个叶子节点最小值,生成枝节点,并存储对应叶子节点指针
3. 会提取枝节点的最小值,最终生成根节点,并存对应枝节点指针
4. 查找数据数,根据你所要的值,进入根进行判断,选择下层枝节点路径,进行判断,最终找叶子节点指针,取出数据.
5. B+tree 在叶子节点添加了双向指针,来优化范围查找(> < >= <= between and like)
6. B*tree 在枝节点添加了双向指针.

1.4 Btree 功能分类
1.4.1 聚簇索引(聚集索引)
前提: 表中创建了主键,MySQL会把主键作为聚簇索引
构建过程:
(1) 如果有主键,以主键为聚集索引列,如果没有,会选唯一键,都没有,会自动生成一个隐藏. (2) 所有数据录入时就有序存储了 (3) 叶子存储的整行的数据,直接把原表数据的数据页当做叶子节点 (4) 提取叶子节点中最小的ID值,生成枝节点,提取枝节点最小值,生成根节点 当查询条件是ID时,可有受到聚集索引的优化
1.4.2 辅助索引(非聚簇索引,二级索引)
前提: 按照业务需求构建合适索引
构建过程:
(1) 人为执行创建索引命令
(2) 提取索引列所有值+对应的主键值,按索引列进行排序,均匀落到叶子节点中
(3) 生成枝节点和根节点

当查询条件为辅助索引列时
(1) 遍历辅助索引,找到对应的ID值
(2) 根据ID值回表查询,找到数据行

聚集索引和辅助索引构成区别

聚集索引只能有一个,非空唯一,一般时主键
辅助索引,可以有多个,时配合聚集索引使用的
聚集索引叶子节点,就是磁盘的数据行存储的数据页
MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
辅助索引,只会提取索引键值,进行自动排序生成B树结构

1.5 索引树高度
越低越好:
(1) 数据行多
解决方案: 分区表, 分库分表(分布式架构)
(2) 索引的列值长
前缀索引,分库分表(分布式架构)
(3) 数据类型
char varchar() enum
(4) 主键值过长

1.6 辅助索引细分
单列
多列联合索引 *****
唯一索引
前缀索引

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

  1. 索引的管理
    2.1 模拟数据库数据
    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条数据：
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 创建索引
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 未优化前
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 优化后

  1. 执行计划获取及分析
    3.1 介绍
    desc
    explain
    获取的是优化器选择完的执行计划.

3.2 获取方法
mysql> desc select * from city where countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from city where countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

3.3 执行计划内容说明
table : 表名
type : 查询类型(全表扫描,索引扫描(多种级别),查找不到数据)
possible_keys : 可能会使用的索引
key : 真正使用的索引
key_len : 索引的覆盖长度(联合索引)
Extra : 额外的信息

3.4 type 详细说明
(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

数字类型: not null 没有not null
n1 int 4 bytes 最大存储长度是4字节 4 4+1
n2 tinyint 1 bytes 最大存储长度是1字节 1 1+1

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

联合索引应用长度判断:
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 联合索引应用的道道. *****

(1) 建立联合索引的顺序,唯一值多的列放在最左边
(2) 在联合条件查询中,中间遇到不等值,联合索引截止到不等值查询.
优化方式: 建立索引时,把不等值的列放在最后面.
(3) 多子句时,按照SQL的逻辑执行顺序,建立联合索引
where aa group by bb
where xx order by yy
(4) 在查询条件中,缺少联合索引中间缺少任意一列,都会影响后续所有条件列的索引应用

  1. extra
    using filesort ----> order by group by distinct union
    using temporary join on
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 217,509评论 6赞 504
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,806评论 3赞 394
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 163,875评论 0赞 354
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,441评论 1赞 293
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,488评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,365评论 1赞 302
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,190评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,062评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,500评论 1赞 314
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,706评论 3赞 335
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,834评论 1赞 347
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,559评论 5赞 345
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,167评论 3赞 328
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,779评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,912评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,958评论 2赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,779评论 2赞 354

推荐阅读更多精彩内容

  • MySQL索引
    1.前言： 索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。 索引是存储引擎用于快速查找...
    李雷是个程序员阅读 174评论 0赞 0
  • 2019-06-19 mysql索引
    1. distinct 去重复 select sum(单价*数量) from (select 牌子,单价,数量 ...
    山有木兮_8adb阅读 356评论 0赞 0
  • [译] MYSQL索引最佳实践
    你做了一个明智的选择 理解索引对开发和dba来说都是极其重要 差劲的索引对产品问题负相当大的一部分责任 索引不是多...
    零一间阅读 522评论 0赞 3
  • Mysql-多表查询as索引
    1、Mysql多表查询2、information_schema 虚拟库3、索引 1、多表查询 方法(1) 根据需求...
    你好_请关照阅读 1,029评论 0赞 2
  • 游戏总结
    起 突然发觉今年玩游戏的时间远超过去，才循着功利主义的思维，要写一篇这样的文章，把今年好好玩过的游戏并且喜欢的游戏...
    rubben阅读 1,864评论 0赞 8