MySQL核心操作与优化-优快云博客

本文链接：https://blog.youkuaiyun.com/yuedawn/article/details/45824853

1.1 mysql 表复制

create ticket t2 like t1; 复制整个表结构

insert into t2 select * from t1;

如果只拷贝一部分的话：描述清楚具体的字段

1.2 mysql 索引

唯一索引

强调唯一，就是索引值必须唯一，关键字unique index

创建索引：

1、create unique index 索引名 on 表名(列名);

2、alter table 表名 add unique index 索引名 (列名);

alter table 表名 add index 索引名 (列名);

删除索引：

1、 drop index 索引名 on 表名;

2、 alter table 表名 drop index 索引名;

1.3 视图

create view vt1 as select * from call_out;

droip view vt1;

1.4 mysql 内置函数

字符串函数

cancat (String1, [,***] )

select concat( 'hello', 'world') as myname;

select length("linux");

select ltrim(string1);

select repeat(String2, count);

reaplace(str, search_str, replace_str);

select subStr('linux is very good', 1, 5);

数学函数

bin(decimal_number) 十进制转二进制 select bin（255）

ceiling（number）向上取正

foor（number）向下取整

max(mum1, num2) 聚合时使用

min(n1,n2) 聚合时使用

sort(n1) // 开平方

rand（） //返回0到1的我随机数

select * from v1 order by rand();

日期函数

unix_timestamp(date) 返回当前date 的unix时间戳

datediff（date1， date2）

curdate（）返回当前日期

1.5 mysql预处理语句

prepare s1 "select * from v1 where id > ?";

set @i = 100;

execute s1 using @i;

好处：省事情省方便

1.6 mysql 的事务处理

set autocommit = 0; //关闭自动提交

select * from v1；

savepoint p1;

delete from v1 where id >20;

savepoint p2;

delete from v1 where id >20;

rollback; 或者rollback to p1；

commit;

mysql myisam不支持事务机制。。

一旦提交再次rollback就没有意义了。

1.7 mysql 的存储

一段sql的块。代码段

createprocedurep2()
set@i=50;
while@1<100do
insertintot(name)vaalues(concat('user',@i));
set@i=@i+1;
endwhile;

showprocedure status;

showcreateprocedurep2;

1.8 触发器

truncate  table name； // 清空表很快，同时也能将auto_incremnet恢复成1

二常见sql技巧

2.1 正则表达式

select ‘linux idveryu goood ’ regexp '^linux';

利用group by 的with rollup；

再起进行聚合，相当于统计更多的信息。

bit_or bit_and 这两个方法目的是取分组内结果

selectbit_or(score)fromdemo2groupbyname;

mysql不清楚使用？ 或者%

？ create%

？ contents;  这个就是所有查询文档的根节点。从这个节点可以往后面找想要的命令。

三  SQL语句优化

3.1 优化sql的一般步骤

通过show status 命令了解各种sql的执行效率。

show [session|global] status;

其中session（默认） 表示当前连接时 增删改查

global 表示数据库启动至今的增删改查

show status like 'com_select%';

show status like 'com_insert%';

show status like 'com_delete%';

Com_XXX  表示每一个XXX的执行次数

showstatuslike'innodb_rows%';   
innodb 看查询影响的行数。
show status like 'connections'; 无论连接成功失败都记录次数
show status like 'slow_query'; 查看慢查询的相关参数。 
show variables  like '%long%';   查看慢查询的记录时间


descselect*fromdemo2; 查看sql的效率相关。
explain  select*fromdemo2; 查看sql的效率相关。
重点要看的是影响行数 是否用到索引或者是否是单表查询。

1： 通过慢查询日志。（一定要开启慢查询日志）

2： 解析sql语句  explain 命令 或者desc 查询影响的函数。

格式：

3.2 索引问题

3.3 两个简单使用的优化方法

3.4 常用的sql优化


mysql 在物理磁盘空间分成三部分
结构文件 + 数据文件 + 索引文件
索引： 主键索引 唯一索引和普通索引
 sql优化：
tt id, name 这两列
select * from tt where tt.
（1）：  使用索引
（2）： 使用like 后面常量只有%号不在第一个字符才可能被使用。
（3） 如果一列用了索引，这一列使用is null 或者not null 也会用到索引的
（4）： or 或者and关键字两端必须都使用索引，如果有一个部分没有索引，则索引失效
（5）： 如果name 是字符串，然而使用select * from tt where tt.name = 123 ; 会使索引失效

show status like 'handle_read%'; 
如果handler_read_rnd_next这个值很高，则table表很多数据需要增加索引了。

3.3 两个简单的优化方法
check table v_t1; //查看表是否有错误
rename table t2 to t1;   //对表rename
优化表空间： 提出碎片 空洞
optimizetablett；
3.4 常用的sql的优化
3.4.1 大批数据导入导出
mysqldump导入导出数据。
（1）： 导入导出比较快捷的方式是infile outfile
原始的导入导出每一行都会更新索引。所以会慢。

（2）： 所以可以先关闭普通索引，最后导入完毕，然后再开启索引。

alter table t1 disable keys； // 关闭索引

alter table t1 enable keys； // 开启索引
以上两条只能关闭非唯一索引。
set unique_checks = 0; 关闭唯一索引
set unique_checks = 1; 开启唯一索引
（3）： 针对innodb事务机制，可以关闭自动提交，可以提高高如效率
set autocommit = 0； // 关闭自动提交set autocommit = 1； // 开启自动提交
3.4.2 优化sql insert 语句
每次insert 都会导致连接 开启 连接关闭一次。
所以好的方式：
insert into tt(name) values ("user1") ,("user2"), ("user4");
而不是使用insert into tt(name) values ("user1") ；
3.4.3 优化group by 语句
group by 默认会进行排序，所以关闭排序会提高效率。
select id count（num） from tt group by id order by null;
尽量少使用嵌套查询。
select * from tt where id (select * from tt2);  // 通过desc 可以分析出来。这个外面没有使用到索引、
可以用到左连接之类。

mysql基础知识