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;

MySQL基础应用拓展、索引及执行计划

1.

2. 扩展类内容-元数据获取

索引及执行计划

1. 索引的作用

2. 索引的分类(算法)

3. BTREE索引算法演变(了解)

4. Btree索引功能上的分类

5. 辅助索引细分

6. 索引树高度

7. 索引的命令操作

8. 压力测试准备

9. 执行计划分析

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像