MySQL--SQL基础应用及索引--Day5
1、distinct：去重复
SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city ;
1.1、联合查询- union all
-- 中国或美国城市信息
SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'
说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION 去重复
UNION ALL 不去重复
2、别名
列别名,表别名
SELECT
a.Name AS an ,
b.name AS bn ,
b.SurfaceArea AS bs,
a.Population AS bp
FROM city AS a JOIN country AS b
ON a.CountryCode=b.Code
WHERE a.name ='shenyang';
3、外连接
SELECT a.name,b.name ,b.surfacearea
FROM city AS a
LEFT JOIN country AS b
ON a.countrycode=b.code
WHERE a.population<100
4、information_schema.tables视图
元数据？
----> ''基表''（无法直接查询和修改的）
----> DDL 进行元数据修改
----> show，desc(show),information_schema(全局类的统计)
DESC information_schema.TABLES
TABLE_SCHEMA ---->库名
TABLE_NAME ---->表名
ENGINE ---->引擎
TABLE_ROWS ---->表的行数
AVG_ROW_LENGTH ---->表中行的平均行（字节）
INDEX_LENGTH ---->索引的占用空间大小（字节）
例子
-- 查询整个数据中所有的库对应的表名
例如：
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';
--- 查询整个数据库中所有的库对应的表名，每个库显示成一行
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;
-- 统计一下每个库的真实数据量
SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;
#工具中用
每张表数据量=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;
4.1、information_schema.tables+CONCAT(),拼接命令
--- 使用方法举例
mysql> SELECT CONCAT(USER,"@","'",HOST,"'") FROM mysql.user;
--- 生产需求1
mysqldump -uroot -p123 world city >/tmp/world_city.sql
--- 模仿以上命令，对整个数据库下的1000张表进行单独备份，
--- 排除sys，performance，information_schema
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
--- 例子：模仿以下语句，批量实现world下所有表的操作语句生成
alter table world.city discard tablespace;
select concat("alter table" ",table_sc")
select concat("alter table world.",table_name," discard tablespace;")
FROM information_schema.tables
where table_schema='world';
5、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 status like '%lock%'; 模糊查看数据库的整体状态
show variables; 查看数据库所有变量情况
show variables like '%innodb%'; 查看数据库所有变量情况
show engines; 查看所有支持存储引擎
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 index from； 查看表的索引情况
6、索引
6.0 环境准备
压力测试环境准备
#创建一个oldboy库
create database oldboy charset utf8mb4;
#进入oldboy库
use oldboy;
#在oldboy库里面创建一个t100w的表
create table t100w (id int,num int,k1 char(2),k2 char(4),dt timestamp) charset utf8mb4 collate utf8mb4_bin;
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;
查看创建数据创建了多少
mysql> select count(*) from t100w;
压力测试
mysql> select * from t100w limit 10;
命令行执行此条命令
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t100w where k2='klVW '" engine=innodb \
--number-of-queries=2000 -uroot -p123456 -verbose;
6.1、索引作用
提供了类似于书中目录的作用,目的是为了优化查询
6.2、索引的种类(算法)
B树索引
Hash索引
R树
Full text
GIS
6.3、B树 基于不同的查找算法分类介绍
image
B树算法普及
B-tree
B+Tree 在范围查询方面提供了更好的性能(> < >= <= like)
B*Tree
**6.4. 在功能上的分类*******
6.4.1 辅助索引(S)怎么构建B树结构的?
(1). 索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度
id name age gender
select * from t1 where id=10;
问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO.
========================================
(1).辅助索引是基于表的列进行生成的
(2).取出索引列的所有值（取出所有键值）
(3).进行所有键值的排序
(4).将所有的键值按顺序落到BTree索引的叶子节点上
(5).进而生成枝节点和根节点
(6).叶子节点除了存储值之外,还存储了相邻叶子节点的指针，另外还会保存指向原表的指针
6.4.2 聚集索引(C)怎么构建B树结构的?
(1)表中设置了主键,主键列就会自动被作为聚集索引.
(2)如果没有主键,会选择唯一键作为聚集索引.
(3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)
========================================
(1).建表时有主键列（ID）
(2).表中进行数据存储，会按照ID列的顺序，有序的存储一行一行的数据到数据页上（这个动作叫做聚集索引组织表）
(3).表中的数据页被作为聚焦索引的叶子节点
(4).把叶子节点的主键值生成上层枝节点和根节点。
6.4.3 聚集索引和辅助索引构成区别总结
聚集索引只能有一个,非空唯一,一般时主键
辅助索引,可以有多个,是配合聚集索引使用的
聚集索引叶子节点,就是磁盘的数据行存储的数据页
MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
辅助索引,只会提取索引键值,进行自动排序生成B树结构
6.5 辅助索引细分
单列的辅助索引
联合多列辅助索引（覆盖索引）
唯一索引
6.6 关于索引树的高度受什么影响?
（1）数据行多，分表
（2）索引列字符长度，前缀索引
（3）char varchar ，表设计
（4）enum 优化索引高度，能用则用。
7.执行计划
