判断 服务器架构性能 数据,服务器架构之性能扩展-第五章（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型触发器：

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

5.1.9重排auto_increment值

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

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

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

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

5.1.10常见sql技巧

正则表达式：

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 查看命令和用法

5.2.1优化sql语句一般步骤

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

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常用sql优化

5.3.1提高数据的导入效率

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

导入数据

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

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

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

5.3.2优化insert语句

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

5.3.3避免使用嵌套查询

5.4数据库的优化

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

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

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

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

5.4.2四种字符集问题

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

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

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

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

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

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

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

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

5.4.4慢查询日志

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

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”);//修改密码