目录
1.
1.1 group by name
1.2 关于group by的sql_mode
only_full_group_by
说明：
在5.7版本中MySQL sql_mode参数中自带，5.6没有
在带有group by 字句的select中，select 后的条件列（非主键列），要么是group by后的列，要么需要在函数中包裹
实例：导入world库
use world; select name,population from city group by district; #报错违反only_full_group_by 关闭only_full_group_by： select @@sql_mode; #查看当前sql_mode; 显示信息为：only_full_group_by,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 仅复制only_full_group_by后面的字段，然后修改mysql配置文件。 vim /etc/my.cnf 在[mysqld]中添加一行： sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 保存退出 systemctl restart mysqld
1.3 group_concat
列转行聚合函数
mysql> select user,group_concat(host) from mysql.user group by user;
1.4 concat
做列值拼接
mysql> select concat(user,"@",host) from mysql.user;
1.5 关于多表连接语法规则 ***
首先找涉及到的所有表
找到表和表之间的关联列
关联条件写在on后面 A join B on 关联列
所有需要查询的信息放在select后
其他的过滤条件where group by having order by limit 往最后放
注意：对多表连接中，驱动表选择数据行少的表。后续所有表的关联列尽量是主键或唯一键（表设计），至少建立一个索引。
1.6 别名
表别名 from 表名 as 表别名
列别名 select 列名 as 列别名
1.7 distinct
mysql> select count(distinct countrycode) from city;
1.8 select 执行顺序
select user ,count(name) from 表 where 列 group by user having 聚合函数 order by 列 limit 3 offset 1 ;
2. 扩展类内容-元数据获取
2.0 元数据介绍及获取介绍
元数据是存储在"基表"中。
通过专用的DDL语句，DCL语句进行修改
通过专用视图和命令进行元数据的查询
information_schema中保存了大量元数据查询的视图
show 命令是封装好功能，提供元数据查询基础功能
2.1 information_schema的基本应用 ***
tables 视图的应用
mysql> use information_schema; mysql> desc tables; TABLE_SCHEMA 表所在的库名 TABLE_NAME 表名 ENGINE 存储引擎 TABLE_ROWS 数据行 AVG_ROW_LENGTH 平均行长度 INDEX_LENGTH 索引长度
例子：
USE information_schema;
DESC TABLES;
-- 1. 显示所有的库和表的信息
SELECT table_schema,table_name FROM information_schema.tables;
-- 2. 以以下模式 显示所有的库和表的信息
-- world city,country,countrylanguage SELECT table_schema,GROUP_CONCAT(table_name) FROM information_schema.tables GROUP BY table_schema;
-- 3. 查询所有innodb引擎的表
SELECT table_schema,table_name ,ENGINE FROM information_schema.tables WHERE ENGINE='innodb';
-- 4. 统计world下的city表占用空间大小 ****
-- 表的数据量=平均行长度*行数+索引长度
-- AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 FROM information_schema.TABLES WHERE table_schema='world' AND table_name='city';
-- 5. 统计world库数据量总大小 ****
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 FROM information_schema.TABLES WHERE table_schema='world';
-- 6. 统计每个库的数据量大小，并按数据量从大到小排序 ****
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB FROM information_schema.TABLES GROUP BY table_schema ORDER BY total_KB DESC ;
2.2 配合concat()函数拼接语句或命令
例子：
-- 1. 模仿以下语句,进行数据库的分库分表备份。
备份world库下city表：
mysqldump -uroot -p123 world city >/bak/world_city.sql SELECT CONCAT("mysqldump -uroot -p ",table_schema," ",table_name ," >/bak/",table_schema,"_",table_name,".sql") FROM information_schema.tables;
-- 2. 模仿以下语句,进行批量生成对world库下所有表进行操作
ALTER TABLE world.city DISCARD TABLESPACE; SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," DISCARD TABLESPACE;") FROM information_schema.tables WHERE table_schema='world';
2.2 show介绍*****
show databases; 查看数据库名 show tables; 查看表名 show create database xx; 查看建库语句 show create table xx; 查看建表语句 show processlist; 查看所有用户连接情况 show charset; 查看支持的字符集 show collation; 查看所有支持的校对规则 show grants for xx; 查看用户的权限信息 show variables like 'innodb%' 查看参数信息 show engines; 查看所有支持的存储引擎类型 show index from city; 查看表的索引信息 show engine innodb status\G 查看innoDB引擎详细状态信息 show binary logs ; 查看二进制日志的列表信息 show binlog events in '' 查看二进制日志的事件信息 show master status ; 查看mysql当前使用二进制日志信息 show slave status\G 查看从库状态信息 show relaylog events in '' 查看中继日志的事件信息 show status like '' 查看数据库整体状态信息(%innodb_rows_update%:更新次数；%innodb_rows_inserted% 插入次数； %innodb_rows_read% 读取次数；%innodb_rows_delete% 删除次数)
索引及执行计划
1. 索引的作用
类似于一本书中的目录,通过索引可以快速定位到数据具体的物理存储位置，起到优化查询的作用。
2. 索引的分类(算法)
B树 默认使用的索引类型(原型：平衡二叉树算法)
R树
Hash
FullText
GIS 索引
3. BTREE索引算法演变(了解)
B- 叶子节点无水平指针
B+ 叶子节点有水平指针，可以方便范围查询
B* 枝节点有水平指针，mysql当前默认
4. Btree索引功能上的分类
4.1 聚集索引(唯一性) ***
(1)MySQL 会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的.
(2)MySQL进行存储数据时,会按照聚集索引列值的顺序,有序存储数据行
(3)聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列向上生成枝和根
4.2 辅助索引（多个） ***
(1) 提取索引列的所有值,进行排序
(2) 将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点
(3) 在叶子节点中的值,都会对应存储主键ID
4.3 聚集索引和辅助索引的区别 *****
(1) 表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可
(2) 在一张表中,聚集索引只能有一个,一般是主键.
(3) 辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.
(4) 聚集索引,叶子节点存储的时有序的整行数据.
(5) MySQL 的表数据存储是聚集索引组织表，辅助索引查询表。
注：mysql的查询过程就是通过辅助索引找到主键索引的id号，再通过主键索引查数据行
5. 辅助索引细分
5.1 单列辅助索引
5.2 联合索引(覆盖索引) *****
5.3 唯一索引
6. 索引树高度
索引树高度应当越低越好,一般维持在3-4最佳
6.1 数据行数较多
分区: partition 用的比较少了.
分片,分布式架构.
6.2 字段长度
业务允许,尽量选择字符长度短的列作为索引列
业务不允许,采用前缀索引.
6.3 数据类型
char 和 varchar
enum
7. 索引的命令操作
7.1 查询索引
desc city;
PRI ==> 主键索引
MUL ==> 辅助索引
UNI ==> 唯一索引
mysql> show index from city\G
7.2 创建索引
单列的辅助索引:
mysql> alter table city add index idx_name(name);
多列的联合索引:
mysql> alter table city add index idx_c_p(countrycode,population);
唯一索引:
mysql> alter table city add unique index uidx_dis(district); mysql> select count(district) from city; mysql> select count(distinct district) from city;
前缀索引
mysql> alter table city add index idx_dis(district(5));
7.3 删除索引
mysql> alter table city drop index idx_name; mysql> alter table city drop index idx_c_p; mysql> alter table city drop index idx_dis;
8. 压力测试准备
mysql> source /tmp/t100w.sql
8.1 未做优化之前测试
mysqlslap --defaults-file=/etc/my.cnf \ --concurrency=100 --iterations=1 --create-schema='world' \ --query="select * from t100w where k2='MN89'" engine=innodb \ --number-of-queries=2000 -uroot -p123.com -verbose mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Running for engine rbose Average number of seconds to run all queries: 755.861 seconds Minimum number of seconds to run all queries: 755.861 seconds Maximum number of seconds to run all queries: 755.861 seconds Number of clients running queries: 100 Average number of queries per client: 20
8.2 索引优化后
创建k2列索引：
alter table t100w add index idx_k2(k2);
再次并发测试：
mysqlslap --defaults-file=/etc/my.cnf \ --concurrency=100 --iterations=1 --create-schema='world' \ --query="select * from t100w where k2='MN89'" engine=innodb \ --number-of-queries=2000 -uroot -p -verbose mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Running for engine rbose Average number of seconds to run all queries: 1.678 seconds Minimum number of seconds to run all queries: 1.678 seconds Maximum number of seconds to run all queries: 1.678 seconds Number of clients running queries: 100 Average number of queries per client: 20
9. 执行计划分析
9.1 作用
将优化器 选择后的执行计划 截取出来.便于管理管判断语句得执行效率.
9.2 获取执行
desc SQL语句 explain SQL 语句 mysql> mysql> desc select * from t100w where k2='MN89'; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 1027638 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
9.3 分析执行计划
9.3.1 table
表名
9.3.2 type
查询的类型:
全表扫描: ALL
索引扫描:
index 全索引扫描
range 范围
ref 等值
eq_ref 联合等值
const(system) 主键等值
NULL 没有索引
index: 全索引扫描
mysql> desc select countrycode from city; range: 索引范围扫描(> < >= <= , between and ,or,in,like ) mysql> desc select * from city where id>2000; mysql> desc select * from city where countrycode like 'CH%'; 对于辅助索引来讲,!= 和not in等语句是不走索引的 对于主键索引列来讲,!= 和not in等语句是走range === mysql> desc select * from city where countrycode='CHN' or countrycode='USA'; mysql> desc select * from city where countrycode in ('CHN','USA'); 一般改写为 union all desc select * from city where countrycode='CHN' union all select * from city where countrycode='USA'; ref: 辅助索引等值查询 desc select * from city where countrycode='CHN' union all select * from city where countrycode='USA'; eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件 A join B on a.x = B.y desc select b.name,a.name ,a.population from city as a join country as b on a.countrycode=b.code where a.population<100; const(system) : 主键或者唯一键的等值查询 mysql> desc select * from city where id=100;
