文章目录
MySQL优化
在MySQL下可以用show status语句查询一些MySQL数据库的性能
show status like “value“;
value为需要查询的参数下面列举一些常用的参数
- connections: 连接数据库服务器的次数
- uptime: 数据库服务器的上线时间
- slow_queries: 慢查询的次数
- com_select: 查询操作的次数
- com_insert: 插入操作的次数
- com_update: 更新操作的次数
- com_delete: 删除操作的次数
其中查询慢查询次数参数可以结合慢日志查找出慢查询的语句,然后针对慢日志语句进行表查询
优化查询
分析查询语句
EXPLAIN
explain [extended] select select_options
*extebded 将产生附加信息 select_options 是select的查询选项
explain select * from table
- id select标识符 select的查询序列号
- select_type select语句的类型
- table 表示查询的表
- type 表示标的连接类型
select_type
- SIMPLE 表示简单的查询,其中不包括连接查询和子查询
- PRIMARY 表示主查询,或者外层的查询语句
- UNION 表示连接查询的第二个或后面的查询语句
- DEPENDENT UNION 连接查询的第二个或者后面的select语句 取决于外面的查询
- UNION RESULT 连接查询的结果
- SUBQUERY 子查询的第一个select语句
- DEPENDENT SUBQUERY 子查询中的第一个select 取决于外面的查询
- DERIVED 导出表的select
type 连接类型
- system
该表示仅有一行的系统表。这是const连接类型的一个特列
- const
数据表最多只有一个匹配行,const查询速度很快,因为只读取一次。通常用于使用常数值比较primary key或者unique索引
- eq_ref
可以用于使用“=”操作符比较带索引的列--------
- ref
•对于来自前面表的任意行组合,将从该表中读取索引匹配行
索引对查询速度的影响
不加索引会对全字段进行扫描增加查询的时间
create table student (name char (5));
insert into student value(xixi,haha,lala,hihi);
使用explain语句
explain select * from student where name=“haha”;
rows字段会显示4,表示查询了四条记录
接下来加入索引
create index index_name on student(name);
仍使用explain语句
explain select * from student where name=“haha”;
此时row表格显示的字段变成了 1,说明只查询了一条记录大大加快了查询的速度
正确使用索引
索引可以提高查询的速度,但是索引有时候却不会生效,此时索引便无法起到作用。
-
使用like查询
只有%不在第一个位置索引才起作用,所以在使用like的时候尽量避免%开头的现象 -
多列索引查询
一个索引可以包括16个字段,对于多索引,只有查询条件使用了这些字段的第一个字段索引才会被使用。 -
使用or关键字
查询语句中只有or前后两个条件都是索引才生效
优化子查询
子查询虽然可以是查询语句更灵活,但是执行的效率不高。执行子查询的时候MySQL需要为内存查询语句的查询结果建立一个临时表,然后外层查询语句从临时表中查询记录。查询完毕后再撤销临时表
可以用JOIN连接查询代替字表,其速度比子查询要快。
优化数据库结构
将字段多的表分解为多个表
对于字段较多的表,如果有些字段的使用低,可以创建一个新表。因为当一个表的数据量很大时,会由于使用频率低的字段存在而变慢。
增加中间表
对于经常联合查询的表,建立中间表 提高查询效率
适当增加冗余字段
优化插入记录的数据
-
禁用索引
alter table tablename disable keys; 关闭索引
alter table tablename enable keys; 开启索引 -
禁用唯一性检查
set unique_checks=0;
set unique_checks=1; -
使用一条insert批量插入
批量插入比单条数据多次插入块 -
使用LOAD DATA INFILE批量导入
load data infile 导入速度比insert块
对于innodb的优化;
禁用唯一性检查
set unique_checks=0;
禁用外键检查
set foreign_key_checks=0;
禁止自动提交
set autocommit;
分析表、检查表和优化表
分析表
analyze table table_name; 分析者会自动加读锁
TABLE | op | msy_type | msg_text |
---|---|---|---|
表名 | 执行的操作 | 表示信息类型 | 输出 |
检查表
检查InnoDB和MyISAM是否存在错误,对于MyISAM还会更新关键字统计数据,还可检查师徒是否有错误。
check table table_name[option];
option有五个取值,只对MyISAM有效,会上读锁
- quick 不扫描行,不检查错误连接
- fast 只检查没有被正确关闭的表
- changed上次检查后被更改的和没有被正确关闭的表
- medium 扫描行,验证被删除的连接都是有效的,也可以计算各行关键字校验和,并使用计算出的校验和验证
- extend 对每行的所有关键字进行全面的关键字查找,花的时间比较长
优化表
使用optimize table 语句来优化表,对InnoDB和MyISAM类型的表都有效,但是只优化varchar,blob,text类型的字段,通过optimize table 可消除删除和更新造成的文件碎片,执行过程会加读锁
optimize [LOCAL|NO_WRITE_TO_BINLOG] table table_name
LOCAL | NO_WRITE_TO_BINLOG 的意义和分析表相同,都是指定不写入二进制日志
服务器的优化
优化硬件
- 内存加大,内存的速度比磁盘I/O快得多,使增加系统缓冲区容量减少磁盘I/O
- 配置告诉磁盘系统,提高响应速度
- 合理分配磁盘I、O,减少资源竞争
- 配置多处处理器,mysql是多线程的数据库,多处理器可同时执行多个线程。
优化MySQL参数
相关配置都在my.cnf或者my.ini中配置