服务器架构之性能扩展-第五章（6）

Mysql>create table t2 like t1; //复制表结构

Mysql>insert into t2 select * from t1; //复制数据内容

索引是一种快速查询的有效方法，可以通过alter增加索引或create语句创建。

mysql>alter table t1 add ind_id (id);

mysql>alter table t1 add unique/primary key (id);

mysql>create index ind_id on table t1 (id);

mysql>show index from t1;

mysql>drop index ind_id on table t1;

mysql>alter table t1 drop index ind_id;

Unique索引时指唯一索引，是没有重复行的索引。

Alter方法创建和删除索引

删除主键，如果索引列是自动增长的变量。首先要取消自动增长，然后才可以删除主键，因为自动增长序列默认为主键。

增加自动增长主键索引

视图:是存在于表之外的另一张表，符合条件的查询结果

Mysql>create view v_t1 as select * from t1 where id >10;

Mysql>drop view v_t1;

视图时随着主表而变化的表

字符串函数：

Concat （string1，……） //连接字符串

Lcase（string1） //换成小写

Ucase（string1）//换成大写

Length（string1） //string1的长度

Rtrim（string1） //去除后端空格

Ltrim（string1）//取出前段空格

Repeat （string1，count）//重复count次

Replace（str，search_str,replace_str） //在str中，用replace_str代替search_str

Substring（str，position，length） //从str的position开始，取length个字符

Space（count） //生成count个空格

数学函数：

Bin（number）//十进制转为二进制

Ceiling（string1）//向上取整

Floor（string）//向下取整

Max（col）//取最大值，聚合时使用

Min（col）//取最小值，聚合时使用

Sqrt（number）//开平方

Rand（）//返回0-1内的随即取值

日期函数：

Curdate（） //返回当前日志

Curtime（） //返回当前时间

Now（） //返回当前日期时间

Week（date） //返回这是多少周

Year（date） //返回年份

Datediff（date1，date2） //返回开始时间date1和结束时间date2间天数

预处理是传递一个参数作为where判断语句：

Mysql>prepare hello from “select * from t1 where id >?”;

Mysql>set @i=1;

Mysql>excute hello using @i;

Mysql>drop prepare stml;

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

mysql>delete from t1 where id=11; //

mysql>savepoint p1; //建立一个还原点

mysql>delete from t1 where id=10;

mysql>savepoint2;

mysql>rollback to p1; //恢复到p1还原点，p2自动失效

mysql>rollback; //退回到原始还原点

修改引擎，使用事处理功能，要使用innodb引擎才可以。

回滚恢复数据

设置回滚点

存储是一种批量插入数据的方法，需要先将结尾符改为//,最后再改回来，然后通过call命令来调用存储

触发器是一种当完成某项动作的同时，可以同时调用其它数据库进程同时进行。通过create trigger创建。

提前存在的值则old，不存在则new。

Insert型触发器：

Delete型触发器

Update型触发器：

联合查看：可以使用联合查看来查询触发与否

常用的删除数据表内容有如下两种方法：

Mysql>delete from t1; //清除表的内容，改变结构

Mysql>truncate table t1; //清楚表的内容，不改变结构，速度快常用

Mysql>alter table t1 auto_increment=1; //如果自动增长值不是从1开始可以这样调整

正则表达式：

Mysql>select name,email from t where email pegexp “@163[,.]com$”; //匹配@163.com或@163，com邮箱

Mysql>select name,email from t where emal like “@163.com” or email kike “@163,com”;

Rand()随机：

Mysql>select * from t order by rand() limit 3; //随机3条数据

Group by的排序扩展

Mysql>select cname,pname,count(pname) from t group by cname,pame with rollup; //分别对个结果进行排序和统计

创建外键：

Mysql>create table t3(id int,name char(20), foreign kye (id) references t2(id) on delete cascade on update cascade); //创建t3使用外键t2

Mysql help使用：

Mysql>? Create 查看命令和用法

首先查看各种语句使用频率

Mysql>show 【session/global】 status； //session当前连接，global表示数据启动至今

登陆以来进行增删改查的次数

Com_update,com_delete分别表示更新和删除次数。

对于innodb引擎可以使用以下查询语句，innodb_rows_delete/update/select/insert

Innodb引擎是影响的行数，myisam引擎是影响的次数。

Innodb是影响的行数，myisam是影响的次数。

Connections代表连接数，uptime代表连接时间，slow_queries代表慢查询次数。

定位执行效率低的语句

Mysql>explain select * from t where id=1000;

Mysql>desc select * from t where id=1000;

我们重点看一下影响行数。

对一个表增加索引之后，查询范围由9变2，快多了。

索引是优化mysql数据库最常用的方法，使用索引注意以下几点。

1， ,对于复合索引，一般只对左边的索引有效。

2，使用通配符时，like左边不能用通配符

例：mysql>explain select * from t where name like “%3”\G //不会使用索引

3.对于null的搜索is null

例：mysql>desc select * from t where name is null \G

1.对于行数低于100的数据表，索引效果不是很好

2.联合查询语句语句要都使用索引，才会使用索引

3.如果索引列是字符串，则查询时要加“”

4.对于handler_read_rnd_next参数较大的，应该建立索引

5.3.1提高数据的导入效率

常用导入数据的方法是通过outfile进行的。通过outfile导出数据，这时是表内容，非表结构。例如导出数据

导入数据

对于innodb引擎表可以通过以下方法提高速度：

1、通过set unique_checks=0,关闭唯一性效验，导入数据完毕后再set unique_checks=1；恢复唯一性效验。

2、关闭自动提交，提高导入效率。导入数据前执行set autocommit=0关闭自动提交；导入后set autocommit=1,恢复自动提交。

使用insert delayed可以使数据库得到更高的效率。并且可以增加bulk_inser_buffer_size的变量值来提高速度

数据库的优化一般可以通过拆分表提高表的访问效率，这也可以解决大存储量;使用中间表技术提高查询速度，中间表一般就是视图技术。

数据库锁定可以使用lock table t read/write;

读锁，有一个人读锁，其他人可读不可以写。

写锁，只有本人可以进行增删改查，其他人不能读写。

Mysql>status;查看系统字符集。它包括服务器字符集，数据库字符集，客户端字符集，链接字符集

字符集可以通过/etc/my.cnf配置文件进行修改

【client】用于定义客户端字符集和链接字符集

【mysqld】控制着服务器字符集和数据库字符集

下面的collation-server是校验字符集

Mysql>show character set; //查看校验字符集

Bin_log日志是系统进行恢复的重要日志

通过修改/etc/my.cnf文件，去掉#号即可

慢查询日志是进行数据库优化的基础。

Vi /etc/my.cnf

Log_slow_queries=slow.log //开启慢查询

Log_query_time=5 //设置慢查询的时间

Socket默认是位于/tmp/mysql.sock, mysql的启动需要socket文件，当然可以通过重启数据库自动建立socket，也可以不用socket便实现登陆。可以使用

Mysql>mysql –u root –p –protocol tcp –hlocalhost

Service mysqld stop

Mysql_safe –skip-grant-tables –user=mysql & //跳过授权表

Mysql –uroot

Mysql>update user set password=password(“123”) where user=”root” and host=”localhost”;

或mysql>set password for root@loaclhost=password(“123”)；

或mysql>set password=password(“123”); //修改密码

本文转自zsaisai 51CTO博客，原文链接:http://blog.51cto.com/3402313/967194

服务器架构之性能扩展-第五章（6）

继续阅读

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述