二、MySQL相关

apzs...大约 7 分钟

二、MySQL相关

2.1、添加索引open in new window

隐式创建：使用CREATE TABLE创建表时，在声明有主键约束open in new window、唯一性约束、外键约束的字段上，会自动的添加相关的索引。

#CREATE TABLE时隐式创建索引
 
CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);
 
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

可以使用 SHOW INDEX FROM 表名，来查看索引，如下：

显示创建：

创建普通索引

#创建普通索引
CREATE TABLE book(
book_id INT,
book_name VARCHAR(100),
`authors` VARCHAR(100),
info VARCHAR(100),
`comment` VARCHAR(100),
year_publication YEAR,
#声明普通索引
INDEX idx_bname(book_name)
);

创建唯一索引

声明有唯一索引的字段，在添加数据时，要保证唯一性，但是可以添加NULL值。

#创建唯一索引
CREATE TABLE book1(
book_id INT,
book_name VARCHAR(100),
`authors` VARCHAR(100),
info VARCHAR(100),
`comment` VARCHAR(100),
year_publication YEAR,
UNIQUE INDEX uk_idx_cmt(`comment`)
);

创建主键索引

通过定义主键约束的方式定义主键索引，如上所示。不能类比以PRIMARY KEY INDEX方式创建。

创建单列索引

这个没必要再赘述，以上作用于单个字段索引的都可称之为单列索引。

创建联合索引

如下表，联合索引会以book_id, book_name, info的顺序进行排序，声明时顺序颠倒排序也会变，即遵循最左前缀原则。所以要把最常检索的字段放在最前面！

#创建联合索引
CREATE TABLE book2(
book_id INT,
book_name VARCHAR(100),
`authors` VARCHAR(100),
info VARCHAR(100),
`comment` VARCHAR(100),
year_publication YEAR,
#声明普通索引
INDEX mulidx_bid_bname_info(book_id, book_name, info)
);

创建全文索引

值得说明的是，全文索引比LIKE + %的方式快N倍！但要注意版本支不支持！此外，如果需要全文索引的是大量数据，建议先添加数据，后创建索引。

#创建全文索引
CREATE TABLE book3(
book_id INT,
book_name VARCHAR(100),
`authors` VARCHAR(100),
info VARCHAR(100),
`comment` VARCHAR(100),
year_publication YEAR,
FULLTEXT INDEX futxt_idx_info(info(50))
);

在表已经创建之后再添加索引

通过 ALTER TABLE 表名 ADD 索引这种方式来添加索引

CREATE TABLE book4(
book_id INT,
book_name VARCHAR(100),
`authors` VARCHAR(100),
info VARCHAR(100),
`comment` VARCHAR(100),
year_publication YEAR,
);

添加普通索引

ALTER TABLE book4 ADD INDEX idx_cmt(`comment`);

添加主键

alter  table 表名  add  primary key(字段列表);

添加外键

alter  table  需要添加外键的表名  add  constraint  外键名 foreign  key(本表字段列表)  references  主表名(字段列表);

通过 CREATE INDEX 索引 ON 表(列)

CREATE INDEX idx_cmt ON book4(comment);

2.2、每隔m行选择n个open in new window

select  *
from    (
        select  *
        ,       @rn := @rn + 1 as rn
        from    Table1
        join    (select @rn := 0) i
        ) s
where   rn mod 2 = 0 -- Use = 1 for the other set

2.3、MySQL每隔N分钟汇总open in new window

前些日子，在做项目的时候遇到一个比较奇葩（其实就是不会）的查询，就是根据传入的时间查询条件，对返回的数据做一次汇总，可能是按照n分钟、n小时或者n天（具体怎么根据时间控制间隔，我这里就不说了）。下面进入真题。

思路

时间间隔类型可分为分钟、小时、天，根据不同的时间间隔类型，有如下规则(其中小时、天的原理与分钟基本一致）：

1）按分钟汇总。

取数据中时间字段的【分钟】计为m，计时间间隔span，则当前分钟的取数为： m/span*span（取整，再乘以倍数，用于消余数），然后两位左补0。然后拼接上前面的年、月、日、时，处理后的时间极为time

2）按小时汇总。

取数据中时间字段的【小时】计为h，计时间间隔span，则当前小时的取数为： h/span*span（取整，再乘以倍数，用于消余数），然后两位左补0。然后拼接上前面的年、月、日，处理后的时间极为time

2）按天汇总。

取数据中时间字段的【天】计为d，计时间间隔span，则当前小时的取数为： d/span*span+1（取整，再乘以倍数，用于消余数，并加1，因为天是从01开始的），然后两位左补0。然后拼接上前面的年、月，处理后的时间极为time

获取到格式处理后的时间，然后根据time进行聚合查询（group by time）

举例1（日期字段为bigint类型，yyyyMMddHHmm格式）

我们有一张分级日志表m_minute_metric_log，里面有个字段minute，格式为yyyyMMddHHmm的bigint类型字段，用于每分钟记录一条监控日志。

按每7分钟汇总

CONCAT(
        SUBSTR(m.minute FROM 1 FOR 10),
        LPAD(floor(SUBSTR(m.minute FROM 11 FOR 2) / 7) * 7,2,'0')
      )  time

按每3小时汇总

-- 按小时（每3小时汇总）
      CONCAT(
        SUBSTR(m.minute FROM 1 FOR 8),
        LPAD(floor(SUBSTR(m.minute FROM 9 FOR 2) /3) * 3,2,'0'),
          '00'
      ) time

按每3天汇总

 -- 按天（每3天汇总）
      CONCAT(
        SUBSTR(m.minute FROM 1 FOR 6),
        LPAD(floor(SUBSTR(m.minute FROM 7 FOR 2) / 3) * 3 + 1,2,'0'),
        '0000'
      ) time

bigint类型的yyyyMMddHHmm日期汇总完整SQL

SELECT
    IFNULL(SUM(m.pass),0) total,
    IFNULL(SUM(m.success),0) normal,
    IFNULL(SUM(m.fail),0) fail,
   -- 按分钟（每7分钟汇总）
      CONCAT(
        SUBSTR(m.minute FROM 1 FOR 10),
        LPAD(floor(SUBSTR(m.minute FROM 11 FOR 2) / 7) * 7,2,'0')
      )  time
--     -- 按小时（每3小时汇总）
--       CONCAT(
--         SUBSTR(m.minute FROM 1 FOR 8),
--         LPAD(floor(SUBSTR(m.minute FROM 9 FOR 2) /3) * 3,2,'0'),
--           '00'
--       ) time
--     -- 按天（每3天汇总）
--       CONCAT(
--         SUBSTR(m.minute FROM 1 FOR 6),
--         LPAD(floor(SUBSTR(m.minute FROM 7 FOR 2) / 3) * 3 + 1,2,'0'),
--         '0000'
--       ) time
  FROM
    m_minute_metric_log m
  GROUP BY
    time

按分钟汇总查询结果展示

举例2（日期字段为Date类型）

```
Date类型的汇总完整SQL
```

SELECT
		IFNULL(sum(d.elapsed),0) elapsed,
		count(*) total,
-- 		-- 按3分钟 
-- 			CONCAT(
-- 				DATE_FORMAT(d.req_time, '%Y%m%d%H'),
-- 				LPAD(FLOOR(DATE_FORMAT(d.req_time, '%i') /  3) *  3,2,'0')
-- 			) time
-- 		-- 按3小时
			CONCAT(
				DATE_FORMAT(d.req_time, '%Y%m%d'),
				LPAD(FLOOR(DATE_FORMAT(d.req_time, '%H') /  3) *  3,2,'0'),
				'00'
			) time
-- 		-- 按3天 
-- 			CONCAT(
-- 				DATE_FORMAT(d.req_time, '%Y%m'),
-- 				LPAD(FLOOR(DATE_FORMAT(d.req_time, '%d') / 3) *  3 + 1,2,'0'),
-- 				'0000'
-- 			) time
  	FROM
  		m_external_rpc_detail d
    GROUP BY
		time

```
按小时汇总查询结果展示
```

2.4、查看容量open in new window

1. 查看所有数据库容量大小

select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

2. 查看所有数据库各表容量大小

select 
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

3. 查看指定数据库容量大小

例：查看mysql库容量大小

select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

4. 查看指定数据库各表容量大小

例：查看mysql库各表容量大小

select 
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

2.5、导出为sql

1.导出sql文件导出sql文件可以使用mysqldump。主要有如下几种操作： ①导出整个数据库（包括数据库中的数据）：

mysqldump -u username -ppassword dbname > dbname.sql；

②导出数据库中的数据表（包括数据表中的数据）：

mysqldump -u username -ppassword dbname tablename > tablename.sql；

③导出数据库结构（不包括数据，只有创建数据表语句）：

mysqldump -u username -ppassword -d dbname > dbname.sql；

④导出数据库中数据表的表结构（不包括数据，只有创建数据表语句）：

mysqldump -u username -ppassword -d dbname tablename > tablename.sql;

昵称

邮箱

网址

按正序
按倒序
按热度

二、MySQL相关

# 二、MySQL相关

# 2.1、添加索引open in new window

# 2.2、每隔m行选择n个open in new window

# 2.3、MySQL每隔N分钟汇总open in new window

# 2.4、查看容量open in new window

# 1. 查看所有数据库容量大小

# 2. 查看所有数据库各表容量大小

# 3. 查看指定数据库容量大小

# 4. 查看指定数据库各表容量大小

# 2.5、导出为sql

预览:

二、MySQL相关

2.1、添加索引open in new window

2.2、每隔m行选择n个open in new window

2.3、MySQL每隔N分钟汇总open in new window

2.4、查看容量open in new window

1. 查看所有数据库容量大小

2. 查看所有数据库各表容量大小

3. 查看指定数据库容量大小

4. 查看指定数据库各表容量大小

2.5、导出为sql