MySQL性能优化

来源：互联网发布：帝国仿内涵吧网源码编辑：程序博客网时间：2024/05/29 15:07

MySQL高级操作

mysql表复制

复制表结构+复制表数据

mysql> create table t3 like t1;

mysql> insert into t2 select * from t1;

如果两个表结构不完全相同时，需要指定拷贝的列

mysql索引

1-ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引

ALTER TABLE table_name ADD INDEXindex_name(column_list)

ALTER TABLE table_name ADD UNIQUEindex_name(column_list)

ALTER TABLE table_name ADD PRIMARY KEYcolumn_list

2-alter table tablename drop

ALTER TABLE table_name DROP INDEXindex_name

ALTER TABLE table_name DROP PRIMARY KEY

如果主键有自增,则无法删除,需要将auto_increment去掉

alter table t1 modify id intunsigned not null;

3-create index （无法创建主键索引）

CREATE INDEX index_name ONtable_name(column_list)

CREATE UNIQUE INDEX index_name ONtable_name(column_list)

4-drop index

DROP INDEX index_name ON table_name

mysql视图

创建视图

mysql>create view v_t1 as select * fromt1 where id>4 and id<11;

ALTER VIEW

CREATE VIEW

DROP VIEW

查看视图

show tables

mysql内置函数

字符串函数

concat() 连接字符串

lcase()

ucase()

length()

ltrim()

rtrim()

repeat(string, count)

replace(str, search_str, replace_str)

substring(str, position [,length])

space(count) 生成count个空格

数学函数

bin () 十进制转二进制

ceiling() 向上取整

floor() 向下取整

max()

min()

sort() 开平方

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

可以用来随机排序

select* from t1 order by rand();

日期函数

curdate()

curtime()

now()

unix_timestamp(date)

from_unixtime()

week(date)

year(date)

datediff(expr, expr2) 返回起始时间expr和结束时间expr2间的天数

mysql预处理语句

设置stmt1预处理，传递一个数据作为一个where判断条件

mysql>prepare stmt1 from ‘select * fromt1 where id>?’;

set @i=1;

execute stmt1 using @i;

set @i=5;

execute stmt1 using @i;

drop prepare stmt1;

mysql事务处理

关闭自动提交功能

set autocommit=0;

从表t1中删除了一条记录

delete from t1 where id=11;

此时做一个p1还原点

savepoint p1；

再次从表t1中删除一条记录

delete from t1 where id=10;

再次做一个p2还原点

savepoint p2;

此时恢复到p1还原点，当然后面的p2这些还原点自动会失效

rollback to p1;

退回到最原始的还原点

rollback;

mysql存储

循环插入100条数据

\d //

create procedure p1()

begin

set @i=3;

while @i<=100 do

insert into t2(name)values(concat("user", @i));

set @i=@i+1;

end while;

end//

call p1()

show procedure status\G

show create procedure p1\G

mysql触发器

mysql>\d //

mysql>create trigger tg1 before inserton t1 for each row

>begin

>insert into t2(id) values(new.id);

>end//

删除t1后t2表中的记录也跟着删除

create trigger tg2 before delete on t1 foreach row

begin

delete from t2 where id=old.id;

end

更改t1后t2中的记录跟着更改

create trigger tg3 before update on t1 foreach row

begin

update t2 set id=new.id where id=old.id;l

end

重排auto_increment值

mysql数据库自动整长的ID如何恢复

清空表的时候不使用delete from tablename；

而是要用：

truncate table tablename；

这样auto_increment就恢复成1了

或者清空内容后直接用ALTER命令修改表：

alter table tablename auto_increment=1;

常见sql技巧

正则

mysql>select name,email from t whereemail REGEXP “@163[.,]com$”;

正则比like更消耗系统资源

使用rand()提取随机行

mysql>select * from std order by rand();

mysql>select * from stu order by rand()limit 3; 随机抽取3条数据样本

利用GROUPBY的WITH ROLLUP

使用group by的withrollup子句可以检索出更多的分组聚合信息

with rollup不可以和orderby同时使用

mysql>select cnam,pname,count(pname)from demo group by cname, pname with rollup;

使用rollup之后会再计算每次聚合后count(pname)的总数

cname

pname

用BITGROUP FUNCTIONS做统计

在使用group by 语句是可以同时使用bit_and、bit_or函数来完成统计工作。这两个函数的作用主要是做数值之间的逻辑位运算

mysql>select id, bit_or(kind) fromorder_rab group by id

对order_rab表中id分组时对kind做位与和或计算

mysql>select id, bit_and(kind) fromorder_rab group by id

使用外键需要注意的问题

创建外键的方式如下

mysql> create table temp(id int, namechar(20)), forgein key(id) references outTable(id) on delete cascade on updatecascade);

注意：Innodb类型的表支持外键，myisam类型的表，虽然创建外键可以成功，但是不起作用，主要原因是不支持外键

mysqlhelp使用

同时使用？来获取mysql命令的帮助信息

? %可以获得更多命令如 ? create% , ? reg%

? contents 涵盖了mysql所有的帮助信息

\G反转行和列

MySQL数据库优化

数据库优化

1. 优化表的类型

2. 通过拆分提高表的访问效率

3. 使用中间表提高统计查询速度

SQL语句优化

1、通过showstatus命令了解各种SQL的执行频率

格式：mysql>show [session|global]status;

其中：session(默认)表示当前连接，

global表示自数据库启动至今

mysql>show status;

mysql>show global status;

mysql>show status like 'Com_%';

mysql>show global status like 'Com_%';

增删改查次数Com_select、Com_update、Com_delete、Com_insert

只针对InnoDB存储引擎的

InnoDB_rows_read执行select操作的影响行数

InnoDB_rows_updated执行update操作的影响行数

InnoDB_rows_inserted执行insert操作的影响行数

InnoDB_rows_deleted执行delete操作的影响行数

其他：

connections连接mysql的数量

Uptime服务器已经工作的描述

Slow_queries：慢查询的影响行数

2、定位执行效率较低的SQL语句

explain select * from table where id=1000;

desc select * from table where id=1000;

重点要看

影响行数rows

单表查询还是多表查询select_type

是否用到索引,可能用到索引

每一列的解释

select_type:

SIMPLE 简单表，不使用表连接或子查询

PRIMARY 主查询，即外层的查询

UNION UNION中的第二个或后面的查询语句

SUBQUERY 子查询中的第一个SELECT

table

type

性能由好到差

system 表仅一行

const 只一行匹配

eq_ref 对于前面的每一行使用主键和唯一

ref 同eq_ref，但没有使用主键和唯一

ref_or_null 同前面对null查询

index_merge 索引合并优化

unique_subquery 主键子查询

index_subquery 非主键子查询

range 表单中的范围查询

index 都通过查询索引来得到数据

all 通过全表扫描得到的数据

possible_keys 表查询是可能使用的索引

key 表实际使用的索引

key_len 索引字段的长度

ref

rows 扫描行的数量

Extra Using where;Using index执行情况的说明和描述

常用SQL的优化

1、大批量插入数据

普通导入导出

mysqldump -uroot -p123456 test>/tmp/test.sql //导出

mysql -uroot -p123456 test </tmp/test.sql //导入

大批量导出导入采用infile outfile，因为outfile导出的文件没有表结构，没有创建表的语句，执行的时候更快

?infile ?outfile查看导入导出帮助

mysql>select name from t1 into outfile"/tmp/test.txt"; //导出

mysql>load data infile "/tmp/test.txt"into table t1(name); //导入

更快速的方法是在导入的时候关闭非唯一索引

disable keys和enablekeys用来打开或关闭MyISAM表非唯一索引的更新，可以提高速度，注意：对InnoDB表无效

mysql> alter table t1 disable keys;

Query OK, 0 rows affected (0.00 sec)

mysql> load data infile"/tmp/test.txt" into table t1(name);

Query OK, 7 rows affected (0.00 sec)

Records: 7 Deleted: 0 Skipped: 0 Warnings: 0

mysql> alter table t1 enable keys;

Query OK, 0 rows affected (0.00 sec)

(1)针对InnoDB类型数据导入的优化

因为InnoDB表按照主键顺序保存，所以将导入的数据主键顺序排列，可以提高导入效率

(2)关闭唯一行校验可以提高导入效率

确保导入的数据里不会出现冲突值时可以关闭唯一索引

setunique_checks=0; //关闭

setunique_checks=1; //开启

mysql> set unique_checks=0;

Query OK, 0 rows affected (0.00 sec)

mysql> load data infile"/tmp/test.txt" into table t1(name);

Query OK, 7 rows affected (0.00 sec)

Records: 7 Deleted: 0 Skipped: 0 Warnings: 0

mysql> set unique_checks=1;

Query OK, 0 rows affected (0.00 sec)

(3)关闭自动提交可以提高导入效率

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> load data infile"/tmp/test.txt" into table t1(name);

Query OK, 7 rows affected (0.00 sec)

Records: 7 Deleted: 0 Skipped: 0 Warnings: 0

mysql> set autocommit=1;

Query OK, 0 rows affected (0.01 sec)

2、优化insert语句

尽量使用多个连值insert语句，缩短客户端与数据库的连接、关闭等损耗

可以使用insert delayed(马上执行)语句得到更高的效率

将索引文件和数据文件分别放在不同的磁盘上

可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是只对MyISAM表使用

当从一个文件中装载一个表时，使用LOAD DATA INFILE比使用多个insert语句要快20倍

3、优化group by语句

如果查询包含group by但用户想要避免排序结果的损耗，则可以使用order by null来禁止排序

mysql> desc select * from t1 group byname\G

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table: t1

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 5

Extra: Using temporary; Using filesort

1 row in set (0.00 sec)

mysql> desc select * from t1 group byname order by null\G

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table: t1

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 5

Extra: Using temporary

1 row in set (0.00 sec)

4、优化嵌套查询

嵌套查询转换成多表联合查询和关联查询

MySQL索引优化

1、索引的存储分类

MyISAM存储引擎的表的数据和索引是自动分开存储的，各自是独立的一个文件，InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但可以由多个文件组成。

MySQL不支持函数索引，但是能对lieder前面某一部分进行索引，例如name字段，可以只取name的前4个字符进行索引，这个特性可以大大缩小索引文件的大小。

mysql>create index ind_company2_name oncompany2(name(4));

(3)如果对大的文本进行搜索，使用全文索引而不是用like"%...%"

(4)如果列名是索引，使用column_nameis null将使用索引。

mysql>explain select * from company2where name is null\G

mysql> desc select * from t1 where nameis null\G

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table: t1

type: ref

possible_keys: in_name

key: in_name

key_len: 93

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

2、存在索引但不使用索引

(1)如果MySQL估计使用索引比全表扫描更慢，就不使用索引。对进行范围类字段查询时，如果选在大于第一条，小于最后一条时，可能选择放弃索引。如key_part1均匀分布在1到100之间，mysql>select * from table_name wherekey_part1>1 and key_part1<90;

(2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。

(3)用or或and分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

mysql> desc select * from t1 where id=1or name="user1"\G

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table: t1

type: index_merge

possible_keys: PRIMARY,in_name

key: PRIMARY,in_name

key_len: 4,93

ref: NULL

rows: 2

Extra: Using union(PRIMARY,in_name); Using where

1 row in set (0.00 sec)

mysql> alter table t1 drop indexin_name;

mysql> desc select * from t1 where id=1or name="user1"\G

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table: t1

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 6

Extra: Using where

1 row in set (0.01 sec)

mysql> desc select * from t1 where id=1and name="user5"\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

Extra: Impossible WHERE noticed after reading const tables

1 row in set (0.00 sec)

(4) 对于创建的多列索引，只要查询条件中用到最左边的列，索引一般就会被使用

mysql>createindex in_sales2_com_mon onsales2(company_id,moneys);

mysql>explainselect * from sales2 where company_id=2006\G 使用了复合索引

mysql>explainselect * from sales2 where moneys=1\G 没有使用到复合索引

(5)使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用

mysql>explainselect * from company2 where name like "%3"\G

mysql> desc select * from t1 where namelike "user%"\G

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table: t1

type: ALL

possible_keys: in_name //使用了in_name索引

key: NULL

key_len: NULL

ref: NULL

rows: 5

Extra: Using where

1 row in set (0.00 sec)

mysql> desc select * from t1 where namelike "%5"\G

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table: t1

type: ALL

possible_keys: NULL //未使用索引

key: NULL

key_len: NULL

ref: NULL

rows: 5

Extra: Using where

1 row in set (0.00 sec)

(6)如果列类型是字符串，但在查询时把一个数值行常量复制给了一个字符型的列名name，那么虽然在name列上有索引，但是也没有用到。

mysql> desc select * from t1 wherename="123"\G

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table: t1

type: ref

possible_keys: in_name

key: in_name

key_len: 93

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

mysql> desc select * from t1 wherename=123\G

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table: t1

type: ALL

possible_keys: in_name

key: NULL

key_len: NULL

ref: NULL

rows: 7

Extra: Using where

1 row in set (0.00 sec)

3、查看索引使用情况

如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。

mysql> show status like"handler_read%";

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Handler_read_first | 0 |

| Handler_read_key | 3 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 52 |

+-----------------------+-------+

6 rows in set (0.00 sec)

表优化

1、定期分析表和检查表

ANALYZE[LOCAL|NO_WRITE_TO_BINLOG] TABLEtbl_name[,tbl_name]...

本语句用户分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的行计划。

检查表CHECK TABLE tbl_name;

mysql> check table v_t1;

+-----------+-------+----------+----------+

+-----------+-------+----------+----------+

+-----------+-------+----------+----------+

1 row in set (0.00 sec)

2、定期优化表

OPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG] TABLEtbl_name[,tbl_name]...

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多的改动，则需要做定期优化。这个命令可以将表中的空间碎片进行合并，但是此命令值对MyISAM,BDB和InnoDB表起作用。

mysql> optimize table t1;

+---------+----------+----------+----------+

+---------+----------+----------+----------+

+---------+----------+----------+----------+

1 row in set (0.00 sec)

MySQl服务器优化

myisam读锁定

1.locak table t1 read

2.开启另一个mysql连接终端，接着去尝试

select * from t1

3.再insert、update和delete t1这张表，会发现所有的数据都停留在终端上没有真正的去操作

4.读锁定对在做备份大量数据时非常有用

myisam写锁定

1.locak table t1 write

2.打开另一个mysql终端，尝试select、insert、update和delete，都不能操作，只有等第一个终端操作完毕，第二个终端才能真正执行

3.写锁比读锁更严格

4.一般情况很少显示的进行read和write锁定，myisam会自动进行锁定

四种字符集问题

default-character-set=utf8

character-set-server=utf8

collation-server=utf8_general_ci 校验字符集，排序使用

showcharacter set;可以查看各种字符集的校验字符集

binarylog日志问题

log-bin=mysql-bin

showlog慢查询日志问题

开启和设置慢查询时间

vi/etc/my.cnf

log_slow_queries=slow.log

long_query_time=5

mysql>show variables like "%slow%";

+---------------------+-----------------------------------------+

|Variable_name | Value |

+---------------------+-----------------------------------------+

|log_slow_queries | OFF |

|slow_launch_time | 2 |

|slow_query_log | OFF |

|slow_query_log_file | /usr/local/mysql/var/localhost-slow.log |

+---------------------+-----------------------------------------+

mysql>show variables like "%long%";

+--------------------+-----------+

|Variable_name | Value |

+--------------------+-----------+

|long_query_time | 10.000000 |

|max_long_data_size | 1048576 |

+--------------------+-----------+

socket问题

有时无法用socket登录，可以换成tcp方式去登录，但是测试时可以这样用，但是必须要在php用之前把问题解决

mysql-uroot -p --protocol tcp -hlocalhost

[client]

port=3306

socket=/tmp/mysql.sock

[mysqld]

port=3306

socket=/tmp/mysql.sock

skip-locking

root密码丢失

1.service mysqld stop

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

跳过授权表mysql.user和mysql.db这些表

3.mysql -uroot

4.mysql>update mysql.user setpassword=password("pwd") where user='root' and host='localhost';

5.mysql>set password for root@localhost=password("pwd");

6.mysql>setpassword=password("pwd");

阅读全文

0 0