MySQL优化

最新推荐文章于 2025-08-10 13:24:43 发布

转载最新推荐文章于 2025-08-10 13:24:43 发布 · 89 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zhony/p/10617392.html

文章标签：

#数据库 #前端 #后端 #ViewUI

本文介绍MySQL的基础操作及优化技巧，包括表复制、索引管理、视图使用、内置函数、预处理语句、事务处理等内容，并详细讲解了索引的分类、创建、使用场景及其对查询性能的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL优化

1. MySQL基础操作

1.1. MySQL表复制

复制表结构+复制表数据（推荐）

mysql>create table t2 like t1;

mysql>insert into t2 select * from t1;

直接复制表数据，但无索引和约束

mysql>create table t3 select * from t1;MySQL索引

1.2. MySQL索引

什么是索引？

就像是书的目录，能够提高查询速度，降低写入速度，占用磁盘空间

分类：

主键、唯一、普通、全文(sphinx代替)

ALTER TABLE ADD（增）

ALTER TABLE table_name ADD INDEX index_name(column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

ALTER TABLE DROP（删）

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

注意：删除主键索引，该字段不能有auto_increment，如果有先修改掉再删。

修改索引

先删除、再添加

查看索引

show index from table_name;

1.3. MySQL视图

创建视图：

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

视图帮助信息：

mysql>? view

ALTER VIEW

CREATE VIEW

DROP VIEW

查看视图：

mysql>show tables;

删除视图：

drop view v_t1;

视图相当于表查询的快捷方式，表数据改变，视图也跟着变。

1.4. MySQL内置函数

字符串函数：

CONCAT(string1,string2[,…]) //连接字符串

LCASE(string) //转换成小写

UCASE(string) //转换成大写

LENGTH(string) //string的长度

LTRIM(string) //去除前端空格

RTRIM(string) //去除后端空格

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

REPLACE(str,search_str,replace_str) //从str中用replace替换search_str

SUBSTRING(str,position[.length]) //从str的position开始，取length个字符

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

数学函数：

ABS() //绝对值

PI() //圆周率

SQRT() //平方根

MOD() //取余数

CEIL() //进一法取整

FLOOR() //舍去法取整

MAX() //最大值

MIN() //最小值

RAND() //返回0-1内随机值

日期函数：

CURDATE() //返回当前日期

CURTIME() //返回当前时间

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

UNIX_TIMESTAMP(date) //返回当前的date的UNIX时间戳10位，单位秒

FROM_UNIXTIME() //返回UNIX时间戳的日期值

WEEK(date) //返回日期date为一年中的第几周

YEAR(date) //返回日期date的年份

DATEDIFF(expr,expr2) //返回stop时间expr和start时间expr2间的天数

1.5. MySQL预处理语句

预先处理SQL的语法，通过传值完成SQL。

优势：提高效率（重用），防止SQL注入（安全）。

PDO中的prepare就是调用该方法！！！

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

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

2、设置一个变量

msyql>set @i=1;

3、执行stmt1预处理

mysql>execute stmt1 using @i;

4、删除预处理

mysql>drop prepare stmt1;

1.6. MySQL事务处理

多个步骤为一个过程的事物（整体）

中间有任何一个环节出问题，都会造成事物的回滚

前提：

表类型（引擎）：innodb

1、关闭自动提交功能

mysql>set autocommit=0

2、删除记录

mysql>delete from t1 where id = 11;

3、创建还原点

mysql>savepoint p1;

4、再次删除记录

mysql>delete from t1 where id = 22;

5、再创建还原点

mysql>savepoint p2;

6、此时恢复到还原点1,2会自动失效

mysql>rollback to p1;

7、退回起始点

mysql>rollback;

8、确认提交

mysql>commit;

1.7. MySQL存储（MySQL的自定义函数）

批量的有规律的mysql操作可以事前存在procedure中，后期调用。

1、创建一个存储p1()

mysql>\d // //将结束符修改为”//”

mysql>create procedure p1()

->begin

->set @i=1;

->while @i<10 do

->select @i;

->set @i=@i+1;

->end while;

->end

2、执行存储p1()

mysql>\d ; //将结束符修改回；

mysql>call p1;

3、查看所有procedure的status信息

mysql>show procedure status\G

4、查看procddure p1()的具体信息

mysql>show create procedure p1\G

5、删除procedure

mysql>drop procedure p1;

1.8. MySQL触发器

监视某种事件，并触发某种操作。（商品添加，订单消除等连贯表操作时使用）

触发四要素：1、监视地点（table）2、监视事件（insert/update/dalete）3、触发时间（after/before）4、触发事件（insert/update/dalete）

1、修改delimiter为//

mysql>\d //

2、创建一个名为tg1的触发器，当向t1表中插入数据是，就向t1表中插入一条数据

mysql>create trigger tg1 before insert on t1 for each row #固定写法

->begin

->insert into t2(id) values(new,id);

->end//

3、修改delimiter为;

mysql>\d ;

4、插入数据测试

mysql>insert into t1 values(1),(2),(3);

1.9. 重置自增

MySQL数据库自增ID如何恢复

清空表

delete from tablename;

只能清空数据，不能重置ID

truncate table tablename; #推荐

auto_increment自动成1

或者修改表

alter table tablename auto_increment=1；

1.10. 数据导入导出

完整备份：

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p -l -F test>'/tmp/test.sql'

导出一个数据库中每一个表的相关SQL语句，包含建表、增删改查等导入导出速度慢！

单表数据备份：

mysql>select * from users into outfile '/tmp/users.txt';

仅仅是导出表数据，查什么就导出什么。

相当于

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p -l -F test users --table'/tmp/'

调用 into outfile 语句生成tablename.txt文件。

导入数据：

truncate users; #准备工作，先清空表，或自己创建一个表

mysql>load data infile '/tmp/users.txt' into table users;

可以指定某一些列，空置字段为NULL或者默认值

//mysql>load data infile '/test/users.txt' into table users(id,name);?

1.11. 临时表

create temporary table tmp1(id int) engine=myisam default charset=utf8;

只对当前会话(连接)有效，断开后，临时表自动清除，也可以自己drop table tmp1;

临时表在销毁前，文件暂存/tmp下

2. MySQL索引

测试数据

create table users (

id int AUTO_INCREMENT PRIMARY KEY,

name varchar(255)

)engine=myisam default charset=utf8;

create table score (

id int AUTO_INCREMENT PRIMARY KEY,

uid int ,

score int default 0

)engine=myisam default charset=utf8;

insert into users (name) values ('jack'),('mary'),('lily');

insert into score (uid,score) values (1,90),(2,95),(5,50);

索引分类

主键索引

唯一索引

常规索引

全文索引（不支持中文，用coreseek代替）

主键索引与唯一索引：

CREATE TABLE t2(id int primary key,name varchar(255) unique);

显示所有索引：

SHOW INDEX FROM t2\G

insert into t2 values(null,'jack');

ERROR 1048 (23000): Column 'id' cannot be null

insert into t2 values(1,null);

insert into t2 values(2,null);

select null = null

select * from t2 where name = null

select * from t2 where name is null

主键字段值不能为null，一张表一个主键。

唯一索引可以多个null，不为null则必须唯一。

分析SQL语句

普通查询分析：

desc select * from users\G 或 explain select * from users\G 效果一样

详细解释：

id：SQL执行标识，从大到小

select_type：常见标识select的类型

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

PRIMARY（主查询，即外层的查询）

SUBQUERY（子查询中的第一个SELECT）

DEPENDENT SUBQUERY（子查询内层的第一个SELECT，依赖于外部查询的结果集）

DERIVED（子查询在from子句中，执行查询的时候会把子查询的结果集放到临时表）

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

UNION RESULT（从UNION临时表获得结果集合）

table：输出结果集的表名

type：表示表连接的类型，性能从好到差

system（表仅一行）

const（只一行匹配）

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

ref（用到了普通索引，但没有使用主键和唯一）

ref_or_null（同前面对null查询）

index_merge（索引合并优化）

unique_subquery（主键子查询）

index_subquery（非主键子查询）

range（表单中的范围查询）

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

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

possible_keys：表查询时可能使用的索引

key：表示实际使用的索引

key_len：索引字段的最大长度

ref：参考(推荐我们)

rows：扫描行的数量

Extra：执行情况的说明和描述

重点看 type key rows

分析举例：

主键查询：

desc select * from users where id=2\G

精确匹配：

desc select * from users where name='jack'\G

范围查询：

desc select * from users where id>2 and id<4\G

子查询：（查询学员成绩）

desc select users.* from users where id in(select uid from score)\G

子查询本身用不到索引而且往往会导致外层查询用不到索引（不推荐）

join查询：（查询参考学员成绩）

desc select users.name,score.score from score \

left join users on users.id=score.uid\G

附加知识点：join的使用

有索引用，但不使用索引

1、 where 中使用 or

准备：

alter table users add status int default 0;

alter table users add index ind_status(status);

update users set status = 1 where id=2;

desc select * from users where status <2 or status >3 \G

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

id: 1

select_type: SIMPLE

table: users

type: range

possible_keys: ind_status

key: ind_status

key_len: 5

ref: NULL

rows: 2

Extra: Using where

1 row in set (0.00 sec)

备注：正常使用status可以用到索引。

desc select * from users where status =1 or id=2\G

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

id: 1

select_type: SIMPLE

table: users

type: ALL

possible_keys: PRIMARY,ind_status

key: NULL

key_len: NULL

ref: NULL

rows: 3

Extra: Using where

1 row in set (0.00 sec)

当or作用于多字段时，都使用不到索引，并进行全表扫描。

where or 解决方案 union all

mysql> desc select * from users where status = 1 union all select * from users where id = 2\G

2、 where 中使用 and

准备：

alter table users add email varchar(255);

update users set email = concat(name,'@qq.com');

insert into users (name,email) values ('jack','jack2@qq.com');

desc select * from users where status =0 and email like "ja%" \G

因为email字段没有索引,会在满足status条件的情况下，扫描email字段

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

id: 1

select_type: SIMPLE

table: users

type: ref

possible_keys: ind_status

key: ind_status

key_len: 2

ref: const

rows: 2

Extra: Using where

1 row in set (0.00 sec)

为新字段添加索引

alter table users add index ind_email(email);

desc select * from users where status =0 and email like "ja%" \G

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

id: 1

select_type: SIMPLE

table: users

type: range

possible_keys: ind_status,ind_email

key: ind_status

key_len: 768

ref: NULL

rows: 2

Extra: Using where

1 row in set (0.00 sec)

两个字段都有索引，有可能同时用到两个索引，可以避免描扫

也有可能只用到其中一个索引，就会扫描另一个字段

组合索引

alter table users add index ind_status_email (status,email);

将两个字段共同添加一条索引

desc select * from users where status =0 and email like "ja%"\G

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

id: 1

select_type: SIMPLE

table: users

type: ref

possible_keys: ind_status,ind_email,ind_status_email

key: ind_status_email

key_len: 5

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

desc select * from users where email like "ja%"\G

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

id: 1

select_type: SIMPLE

table: users

type: range

possible_keys: ind_email

key: ind_email

key_len: 768

ref: NULL

rows: 2

Extra: Using where

1 row in set (0.00 sec)

删除email的索引和status的索引

alter table users drop index ind_email;

alter table users drop index ind_status;

desc select * from users where status =1\G

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

id: 1

select_type: SIMPLE

table: users

type: ref

possible_keys: ind_status_email

key: ind_status_email

key_len: 5

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

desc select * from users where email like "ja%"\G

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

id: 1

select_type: SIMPLE

table: users

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 7

Extra: Using where

1 row in set (0.00 sec)

当前组合索引是这样一个顺序 ind_status_email(status,email)

单独查询status时，可以用到这个索引，单独查询email时，却用不到

3、 如果是字符串类型，查询时给数值（无引号），使用不到索引

insert into users (name) values ('123');

desc select * from users where name=123\G

用不到索引

desc select * from users where name='123'\G

可以用到索引

4、 like中索引的使用情况：

desc select * from users where name like "jac%"\G

可以使用到索引

desc select * from users where name like "%jac%"\G

不可以使用索引，当%号在第一个字符，索引不被使用

5、 is null 可以使用索引

desc select * from users where name is null\G

3. 常用语句优化

优化insert

insert into stu (name,age) values ('jack',18),('mary',19),('lily',18);

优化group by 查询

如果包含group by , 默认会排序

可以使用 order by null来禁止排序

用desc来查看 Extra 是否有Using filesort

合理的范围查询不要使用in查询

desc select * from users where id in (2,3)\G

in查询使用索引不稳定，不建议使用，用union代替。

4. 大数据

检测：

文件大小

watch -n1 ls -lh

每一秒刷新一次，可动态查看文件大小变化。

内存和CPU的使用

free –m

top

准备工作

1、建表

create table t1 (id int auto_increment primary key,name varchar(255),name1 varchar(255)) engine=myisam default charset=utf8;

2、插入测试数据

导入sql文件

3、复制成约500万条

insert into t1 (name,name1) select name,name1 from t1;

将本表现存数据再次插入，相当于复制。

4、复制过程中查看服务器信息（文件大小、内存、CPU%）

5、查看数据文件

相关数据文件 /usr/local/mysql/var/data/test/路径下

-rw-rw---- 1 mysql mysql 8.5K 07-19 21:36 t1.frm

-rw-rw---- 1 mysql mysql 148M 07-19 22:50 t1.MYD

-rw-rw---- 1 mysql mysql 51M 07-19 22:50 t1.MYI

MyISAM引擎的表有三个文件

xxx.frm 是表的表结构

xxx.MYD 是表的数据

xxx.MYI 是表的索引

InnoDB引擎的表有两个文件

xxx.frm 是表的表结构

在上层data下还有一个

ibdata1是所有innodb表的数据，大小10M

500万条数据，自增主键的索引有51M

500万条数据，占了148M硬盘空间(只有两列姓名的数据)。

6、查看数据条数

select count(*) from t1;

mysql优化的count速度极快

7、开始查询

select count(*) from t1 where name like '王%';

查询响应时间慢长

8、为name字段添加索引

alter table t1 add index ind_name (name);

在创建时，系统占用内存300M,占用CPU 90%

在索引时，会使用临时文件，以#号开头的是临时文件

-rw-rw---- 1 mysql mysql 8.5K 07-19 22:58 #sql-d09_2.frm

-rw-rw---- 1 mysql mysql 57M 07-19 23:06 #sql-d09_2.MYD

//这个临时文件大小，接近原数据大小时，说明快创建完成了

-rw-rw---- 1 mysql mysql 24M 07-19 23:06 #sql-d09_2.MYI

完成后索引文件变大

9、再次查询

mysql>select count(*) from t1 where name1 like '王%';

快很多

10、用不到索引的查询

mysql>select count(*) from t1 where name like '%王%';

mysql>select count(*) from t1 where name1 like '%王%';

如果有索引用不到，比没索引还要慢

5、慢查询

1、查看是否开启慢查询

mysql> show variables like "%slow%";

log_slow_queries | OFF

2、查看慢查询时间线

mysql> show variables like "%long%";

| long_query_time | 10.000000 | //默认10秒

3、查看慢查询次数

mysql>show status like "Slow_queries"; //当前链接，超时的查询次数

mysql>show global status like "Slow_queries"; //全局的

如果有慢查询，需要去日志中分析，到底是什么原因

vi /usr/local/mysql/data/slow.log #慢查询日志文件在开启慢查询功能后自动生成

需要将里面的SQL语句，用desc 分析，如果发现没有索引，则添加索引，如果是用不到索引，则修改SQL语句。

4、开启慢查询记录功能

vi /etc/my.cnf

[mysqld]

#将慢查询日志写在这个文件中

log_slow_queries=slow.log

#超过多少秒的算是慢查询

long_query_time=5

#自定义，和理解可，试验用1秒方便查看日志。

5、重启mysql

ps -le | grep mysqld

pkill mysqld或者/usr/local/mysql/bin/mysqladmin –uroot –p shutdown（推荐，安全关闭）

/usr/local/mysql/bin/mysqld_safe –user=mysql &

重启后慢查询功能开启

6、查看MySQL的各项状态

show session status; 当前连接(可以省略session)

show global status; 全局，服务器启动以来

筛选一部份内容查看:

show status like "com_insert%"; 执行insert操作的次数，一次查询只累计加1

show status like "com_update%";

show status like "com_delete%";

show status like "com_select%";

show global status like "com_select%";

//不管有没有查到数据，都算一次查询操作

只针对于InnoDB存储引擎的:

show global status like "innodb_rows%";

InnoDB_rows_read

InnoDB_rows_updated

InnoDB_rows_inserted

InnoDB_rows_deleted

注意：只计算受影响行数或查询了多少条数据

查看连接次数

show status like "Connections";

没连接成功，也算

工作时间(秒)

show status like "Uptime";

查看索引使用情况

show status like 'Handler_read%';

Handler_read_key

如果索引正在工作，这个值将很高，代表被索引的次数

Handler_read_rnd_next

值很高，意味着查询运行低效，并且应该建立索引来补救

6、 root密码丢失找回

1、停止服务

pkill mysqld

2、重启服务，但需要跳过授权表限制

/usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql &

3、登录

mysql -uroot

不用密码即可

4、查看现有用户

select user,host,password from mysql.user;

5、修改密码

update mysql.user set password=password('123') where user='root' and host='localhost'或

mysql> set password for root@localhost=password("123");（正常登陆是修改密码才能使用）

6、退出

exit

7、重启MySQL

pkill mysqld

/usr/local/mysql/bin/mysqld_safe --user=mysql &

修改密码

mysqladmin -u root -p password mypasswd（预定密码）

然后输入旧密码

总结

MySQL优化的一般步骤:

开启慢查询日志，并分析

解析SQL查看引影响数

分析是否需要加索引

分析是否用上了索引

转载于:https://www.cnblogs.com/zhony/p/10617392.html

MySQL优化

MySQL优化

1. MySQL基础操作

1.1. MySQL表复制

1.2. MySQL索引

1.3. MySQL视图

1.4. MySQL内置函数

1.5. MySQL预处理语句

1.6. MySQL事务处理

1.7. MySQL存储（MySQL的自定义函数）

1.8. MySQL触发器

1.9. 重置自增

1.10. 数据导入导出

1.11. 临时表

2. MySQL索引

测试数据

索引分类

分析SQL语句

分析举例：

有索引用，但不使用索引

3. 常用语句优化

4. 大数据

检测：

准备工作

5、 慢查询

6、 root密码丢失找回

总结

5、慢查询