mysql使用小结

1.设置编码

set names 'utf8';

2.查询

主键查询贼快

select * from xxxxx where id between {} and {}

limit缓慢原因

limit10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行
1.从数据表中读取第N条数据添加到数据集中
2.重复第一步直到 N = 10000 + 20
3.根据 offset 抛弃前面 10000 条数
4.返回剩余的 20 条数据

数据库的数据存储是随机的,使用B+Tree,Hash等方式组织索引

如果筛选列只是主键效率不是很慢

查询所有DB大小

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;

查询指定DB表大小

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='wholeprocess'
order by data_length desc, index_length desc;

3.存储过程

查看所有存储过程

show procedure status;

查询存储创建语句

show create procedure insetrows;

# 30亿的数据需要两三天导入
CREATE DEFINER=`boss_step`@`%` PROCEDURE `insetrows`()
BEGIN
DECLARE rn int(11) default 3000000000;

WHILE (rn >=0) DO

INSERT into ipv6guid_partition(sguid, etl_stamp, indate) select *, substr(etl_stamp,19,8) as indate from ipv6guid limit 5000;
DELETE from ipv6guid limit 5000;
SET rn=rn-5000;
commit;

END WHILE;

END