mysql性能优化

mysql性能的优化：

1.mysql基础操作

2.常用的sql技巧

3.sql语句优化

4.mysql数据库优化

5.mysql服务器优化

1.mysql的基础操作

1.1 mysql表复制

命令：

复制表结构：create table table1 like table2

复制数据：insert into table1 select * from table2;

1.2 mysql索引

命令：

创建索引：

普通索引：

alter table table_name add index index_name(column_name)

唯一索引：

alter table table_name add unique un_name(column_name);

主键索引：

alter table table_name add primary key key_name(column);

普通索引和唯一索引：

create table index index_name on table_name(column)

注意：create命令无法创建主键索引

删除索引：

drop index index_name on table_name

alter table table_name drop index index_name

alter table table_name drop primary_key

注意：drop无法删除主键索引

查看索引：

show index from table_name

1.3 mysql视图

定义：是一个临时表，跟随表变化

优点：常用数据放到视图中，读取速度更快

创建视图：

create view view_name as select * from table_name;

1.4 mysql内置函数

字符串函数：

concat(string1[,...]) //连接字符串

lcase(string1) //转换成小写

ucase(string2) //转换成大写

length(string) //字符串长度

ltrim(string) //取出前端空格

rtrim(string) //取出后端的空格

repeat(string, count) //重复count次

replace(str, search_str, replace_str) //在str中把search——str用replace——str替换

substring(str,position[,length]) //在str的position开始读取length个字符，注意position从1开始计算

space(count) //生成count个空格

数学函数：

bin(decimal_number) //十进制转二进制

ceiling(number) //向上取整

floor(number) //向下取整

max(num1, num2) //取最大值

min(num1, num2) //取最小值

sqrt(number) //开平方

rand() //返回0-1内的随机数

日期函数：

curdate() //返回当前日期

curtime() //返回当前时间

now() //返回当前日期时间

unix_timestamp(date) //返回当前日期的时间戳

from_unictime() //返回时间戳的日期型

week(date) //返回date中的第几周

year(date) //返回date中的第几年

datediff(date1,date2) //返回date1，date2之间的天数

1.5 预处理语句

创建预处理语句：

prepare stmt1 from 'select * from user where id > ?''

设置变量：

set @i=1

执行语句：

execute stmt1 using @i

1.6 mysql事务处理

关闭自动提交功能：

set autocommit = 0；

从表中删除一条记录

delete from table where id = 1;

设置一个还原点

savepoint p1;

再次从表中删除一条记录

delete from table where id = 2；

再次做一个还原点：

savepoint p2

恢复到p1还原点，此时p2失效

rollback to p1

退回到最原始的还原点

rollback

1.7 mysql存储

定义：一个存储代码的区域，相当于函数

使用场景：创建100个用户

命令：

\d //

create procedure procedure_name()

begin

set @i=1;

while @i<=100 do

insert into table(name) values(concat('user',@i));

set @i=@i+1;

end while

end//

执行存储：

call procedure_name()

查看存储：

show procedure status

1.8 mysql触发器

定义：一个时间发生，同时引发另一个事件发生

命令：

创建：

\d //
create trigger t1 before insert on t1 for each row
begin
insert into t2(name) values(new.name);
end//
\d ;

作用：插入数据到表中，相关的表也会插入数据

\d //
create trigger t1 before delete on t1 for each row
begin
delete from t2 where id = old.id
end//
\d ;

作用：删除表中的数据，另一个表跟着删除

\d //
create trigger t1 before update on t1 for each row
begin
update t1 set name = (new.name) where name=(old.name);
end//
\d ;

删除触发器：

drop trigger trigger_name;

查看：

show triggers

1.9 重排auto_increment值

清空表不能用delete，而是用truncate

使用命令修改auto_increment

alter table table_name auto_increment = 1

2.常用的sql技巧：

2.1 正则表达式的使用

^ 在字符串的开始进行匹配

& 在字符串的结束进行匹配

. 匹配任意的当个字符

[..] 匹配出括号内的任意字符

[^..] 匹配不出现括号内的任意字符

a* 匹配0个或者多个a

a+ 匹配一个或者多个a

a? 匹配0个或者1个a

a1|a2 匹配a1或者a2

a(m) 匹配m个a

a(m,) 匹配至少m个a

a(m,n) 匹配m到n个a

a(,n) 匹配0到n个a

(...) 将模式元素组成单一元素

2.2 利用rand()提取随机行

select * from user order by rand() limit 10;

2.3 利用group by的with rollup统计更多信息

select name,count(name) from user group by name

2.4 使用外键需要注意

myisam不支持外键，innodb支持外键

形成依赖关系，删除时必须先删除外键对应的记录

2.5 忘记某个命令时使用命令提示命令

? create

? help

? %

? contends

? crea%

\G 在语句后面加上这个代表行列倒写

3 sql语句的优化

3.1 通过show status了解sql的执行效率

命令：

show [session|global]status

其中session代表当前连接，global代表数据库启动至今

show status like 'com_%';

show global status like 'com_%';

innodb存储引擎：

show status like "innodb_rows%";

3.2 特殊查询

show like "connections" //数据库连接数

show like "uptime" //数据库开启了多久

show like "slow_queries" //慢查询次数

3.3 定位执行效率低的sql语句

1）查询慢查询日志

2）使用desc或explain命令。查看rows,select_type,

3.4 索引问题

索引是解决sql性能问题最重要的手段之一，用于快速查找出在某个列中有一特定值的行。

使用场景：在被经常用来当条件的字段

索引使用注意：

1）创建的是复合索引，只要查询条件中用到最左边的列，索引一般会被使用

2）使用like查询，后面是常量并且’%‘不在第一个字符，索引一般能被调用

3）如果是对大文本进行索引，使用全文索引，而不用like

4) 如果列名是索引，则column_name is null或者column_name is not null使用索引

5）如果使用or或者and并列条件，如果有一个条件没有索引则不使用索引

6）如果列值是一个字符串，但在查询时把一个数值付给它则放弃使用索引

查看索引使用情况：

show status like 'handler_read%';

handler_read_key：如果索引正在工作，它的值会很高，代表了一个索引行被当做索引读的次数

handler_read_rnd_next: 值越高，说明查询低效，应该建立索引补救

3.5 对表进行优化

检查表是否存在语法错误

命令：check table table_name

定期优化表：

如果已经删除了表中的一大部分数据，或者对可变长度行的表做了多次变动，则需要将空间碎片进行整合优化

命令：optimize table sales(避免在访问时间使用该命令)

3.6 大量数据的快速导入和导出

导出：

select column_name from table outfile into 'test.txt'

导入:

load data infile 'test.txt' into table table_name(column_name)

打开或者关闭非唯一索引

alter table table_name disable keys

alter table table_name enable keys

关闭自动提交：

set autocommit = 0

load data infile 'test.txt' into table table_name(column_name)

set autocommit = 1

3.7 insert 优化

使用一次插入多个值来代替一次插入一个值。执行一次语句数据库开关一次

3.8 group by 优化

使用group by 则会自动进行排序，所以为了不让排序损耗资源，使用order by null来禁止排序

3.9 多表查询优化

使用左连接或者右连接来替代嵌套查询

4.数据库优化

4.1 表的优化

在不使用事务和外键的情况下，都使用mysiam存储引擎建立表

4.2 通过拆分来提高表的访问速度

4.3 使用中间表来提高统计查询的速度

5.mysql服务器的优化

5.1 mysiam读锁定和写锁定

读锁：只能读不能写

写锁：既不能读也不能写,只有自己那端能读写

命令：

lock table table_name read

lock table table_name write

unlock tables

总之，myisam会自行进行锁操作，无需自己指定，只有备份数据时需要自行读锁定

5.2 四种字符集问题

查看字符集：\s

1）server character  utf8

2) database character utf8

3) client character utf8

4) conn character utf8

mysql配置文件中：

cient和conn：default-character-set=utf8

server,database,table: character-set=utf8

校验字符集(针对排序)：colltion-server=utf8_general_ci

5.3 开启binlog日志(默认开启)

show variables like '%bin%';

配置文件中my.conf:

log-bin=mysql-bin

5.4 慢查询日志：

show variables like '%slow%';

show variables like '%long%';

配置文件my.conf添加:

log_slow_queries=slow.log

long_query_time=5

5.5 socket问题：

连接mysql时需要使用

配置文件my.conf

[client]

port=3306

socket=/tmp/mysql.sock

[mysqld]

port=3306

socket=/tmp/mysql.sock

如果socket文件丢失，会造成用户无法登陆

5.6 root密码丢失

//关闭服务进程

1.service mysqld stop

//跳过授权表

2.mysqld_safe --skip-grant-tables --user=mysql &

3.mysql -uroot

4.update userset password=password('123') where user='root' and host='localhost';