linux 登录

··· mysql -u root -p ··· 再输入密码

// 列出所有数据库

show databases;

// 选择数据库 use dvsdb30;

// 查看查询计划 explain select ***;

读写分离 中间件

• 官网 https://github.com/sysown/proxysql
• 详细配置 https://www.cnblogs.com/keme/p/12290977.html

sql优化

• https://www.cnblogs.com/keme/p/9882663.html

mycat读写分离 分库分表

• https://juejin.cn/post/6930430192511156237

索引

• https://juejin.cn/post/7022138503115276324

  • unique 唯一索引 不会重复的字段
  • normal 普通索引
  • fulltext 全文索引
  • spatial 空间索引

• 联合索引

  • 使用的时候必须要有第一个字段的查询，否则联合索引会失效

查看数据表所占的空间大小

// 先切换数据库
use information_schema;

// 查看数据库db的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data 
from tables where table_schema='dvsdb30';

// 查询所有表所占空间大小
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 = 'dvsdb30'
	-- 
ORDER BY
	data_length DESC,
	index_length DESC;


// 查询单表所占空间
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 = 'dvsdb30'
	
	and table_name ='CollectDataSummary'
ORDER BY
	data_length DESC,
	index_length DESC;  


// 查询单表所占空间
select 
table_name, 
table_rows, 
TRUNCATE(data_length/1024/1024,2) as 'data(MB)', 
TRUNCATE(index_length/1024/1024,2) as 'index(MB)'
from information_schema.tables
where table_schema = 'dvsdb30'
and table_name = 'CollectDataSummary'

• RLink

  • http://www.yiidian.com/mysql/mysql-rlike-operator.html

• 查看mysql事务的级别

show variables like '%tx_isolation%';

explain 中各个参数的详解说明

• https://juejin.cn/post/7164652941159170078

mysql中的各种函数

• https://juejin.cn/post/7163148228420960263

union all

• https://juejin.cn/post/6844903737228541965