mysql简单整理

//mysql忽略主外键
SET foreign_key_checks = 0;

//mysql回复主外键
SET foreign_key_checks = 1;

\G 行列转换

//正则匹配，大致了解下即可，基本用不到
select * from t1 where email regexp '@163[.-]com$';

//随机抽取记录，效率很差，基本用不到
select * from t1 order by rand() limit 3;

//group by时使用with rollup可以聚合出更多统计信息，不能order by同时使用
select cname,pname,count(pname) from t1 group by cname,pname,with rollup;

//group by时使用bit_and(与运算)、bit_or(位运算)来统计
select id,bit_and(score) from t1 group by id;
select id,bit_or(score) from t1 group by id;

//主外键 innodb表支持
create table t1(id int,name char(20)),foreign key(id) references t2(id) on delete cascade on update cascade;

//mysql通过? % 配合，能查看很多帮助信息 比如：?opti% 记不住optimize的全称，可以%来匹配
//?contents可以得到所有的帮助大纲，然后通过?逐层查找

//show status可以查询各种SQL执行频率 当前session的值（登陆以来）show global status（服务器启动以来）
show status like "Com_%"
show global status like "Com_%"

//Com_xxx表示每个xxx语句执行的次数
Com_select Com_update Com_insert Com_insert_select Com_delete 

//innodb表执行的行数（跟上面的次数有所区别）
InnoDB_rows_read InnoDB_rows_updated InnoDB_rows_inserted InnoDB_rows_deleted
  
//connections 连接mysql的数量  Uptime 服务器已经工作的秒数  Slow_queries 慢查询次数

//慢查询 需要开启慢查询日志 在mysql配置文件中配置
show variables like '%slow%' 
show varibales like '%long%'

//对表的某个字段的前4个字符进行索引
create index index_name on t1(name(4));

//mysql如果判断使用索引比不使用索引更慢的情况会舍弃索引，and or 前后字段都要索引
//比如表一共100条数据，使用如下SQL将不使用索引
select * from t1 where id>1 and id<100

//一般优化流程:慢查询日志(需要开启慢查询日志，可以设定秒数等)->定位SQL->DESC分析

//Handler_read_key表示索引值被读的次数 Handler_read_rnd_next的值高表示查询低效，需要建立索引
show status like 'Handler_read%'

//定期分析表和检查表
check table t1;

//定期优化表
optimize table sales;

//数据量较大的导入可以使用 infile outfile 替代mysqldump
//在导入前关闭唯一性校验 set unique_checks=0 可提高导入效率
select * from t1 into outfile '/tmp/test.txt'
load data infile '/tmp/test.txt' into table t1
//导入完恢复唯一性校验 set unique_checks=1
//同理导入前关闭自动提交set autocommit=0 导入后恢复自动提交 set autocommit=1 可提高导入效率

//如果查询包含group by 但是想避免排序结果的损耗，可以使用order by null

//复制表结构和表内容
create table t1 like t2;
insert into t1 select * from t2;

//对于某个表的经常会被搜索的记录创建视图（当中间表使用）

//\s查看mysql状态 如:字符集等 在mysql配置文件中把4种字符集全部设置为utf-8并设置校验字符集utf8_general_ci

//mysql.sock被误删的话，重启mysql即可 但此时需要紧急登陆的话，可临时使用tcp登陆
mysql -u root -p 123 --protocol tcp -h localhost

//mysql密码忘记 先停止mysql 跳过权限表
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql &
//然后无密码直接登陆
mysql -u root
//然后修改root密码并重启mysql即可
update user set password=password('123') where user='root' and host='localhost'

//mysql忽略主外键

SET foreign_key_checks = 0;

//mysql回复主外键

SET foreign_key_checks = 1;

\G 行列转换

//正则匹配，大致了解下即可，基本用不到

select * from t1 where email regexp '@163[.-]com$';

//随机抽取记录，效率很差，基本用不到

select * from t1 order by rand() limit 3;

//group by时使用with rollup可以聚合出更多统计信息，不能order by同时使用

select cname,pname,count(pname) from t1 group by cname,pname,with rollup;

//group by时使用bit_and(与运算)、bit_or(位运算)来统计

select id,bit_and(score) from t1 group by id;

select id,bit_or(score) from t1 group by id;

//主外键 innodb表支持

create table t1(id int,name char(20)),foreign key(id) references t2(id) on delete cascade on update cascade;

//mysql通过? % 配合，能查看很多帮助信息比如：?opti% 记不住optimize的全称，可以%来匹配

//?contents可以得到所有的帮助大纲，然后通过?逐层查找

//show status可以查询各种SQL执行频率当前session的值（登陆以来）show global status（服务器启动以来）

show status like "Com_%"

show global status like "Com_%"

//Com_xxx表示每个xxx语句执行的次数

Com_select Com_update Com_insert Com_insert_select Com_delete

//innodb表执行的行数（跟上面的次数有所区别）

InnoDB_rows_read InnoDB_rows_updated InnoDB_rows_inserted InnoDB_rows_deleted

//connections 连接mysql的数量 Uptime 服务器已经工作的秒数 Slow_queries 慢查询次数

//慢查询需要开启慢查询日志在mysql配置文件中配置

show variables like '%slow%'

show varibales like '%long%'

//对表的某个字段的前4个字符进行索引

create index index_name on t1(name(4));

//mysql如果判断使用索引比不使用索引更慢的情况会舍弃索引，and or 前后字段都要索引

//比如表一共100条数据，使用如下SQL将不使用索引

select * from t1 where id>1 and id<100

//一般优化流程:慢查询日志(需要开启慢查询日志，可以设定秒数等)->定位SQL->DESC分析

//Handler_read_key表示索引值被读的次数 Handler_read_rnd_next的值高表示查询低效，需要建立索引

show status like 'Handler_read%'

//定期分析表和检查表

check table t1;

//定期优化表

optimize table sales;

//数据量较大的导入可以使用 infile outfile 替代mysqldump

//在导入前关闭唯一性校验 set unique_checks=0 可提高导入效率

select * from t1 into outfile '/tmp/test.txt'

load data infile '/tmp/test.txt' into table t1

//导入完恢复唯一性校验 set unique_checks=1

//同理导入前关闭自动提交set autocommit=0 导入后恢复自动提交 set autocommit=1 可提高导入效率

//如果查询包含group by 但是想避免排序结果的损耗，可以使用order by null

//复制表结构和表内容

create table t1 like t2;

insert into t1 select * from t2;

//对于某个表的经常会被搜索的记录创建视图（当中间表使用）

//\s查看mysql状态如:字符集等在mysql配置文件中把4种字符集全部设置为utf-8并设置校验字符集utf8_general_ci

//mysql.sock被误删的话，重启mysql即可但此时需要紧急登陆的话，可临时使用tcp登陆

mysql -u root -p 123 --protocol tcp -h localhost

//mysql密码忘记先停止mysql 跳过权限表

/usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql &

//然后无密码直接登陆

mysql -u root

//然后修改root密码并重启mysql即可

update user set password=password('123') where user='root' and host='localhost'

唐晓锋–BLOG

This is just a personal note.

发表评论取消回复

发表评论 取消回复

发表评论取消回复