MySql优化方案,mysql优化

mysql优化方案总结

u       Mysql数据库的优化技术

对mysql优化时一个综合性的技术,主要包括

a: 表的设计合理化(符合3NF)

b: 添加适当索引(index) [四种:
普通索引、主键索引、唯一索引unique、全文索引]

c: 分表技术(水平分割、垂直分割)

d: 读写[写: update/delete/add]分离

e: 存储过程 [模块化编程,可以提高速度]

f: 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]

g: mysql服务器硬件升级

h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

g:mysql语句本身的优化

 

 

 

 

下面是一些mysql基本使用命令。

 

一:mysql查看基本命令

(1):查看当前有哪些数据库

  show databases;

(2):使用mysql数据库

 use test;

(3):查看当前数据库下的表

  show tables;

(4):查看上述grade表建立的命令

show  create table grade;

(5):查看student表的结构

desc student;

(6):查看数据库支持的存储引擎

show engines; 

show engines \G ;      //  \G让结果更美观

(7):查看默认存储引擎

show variables like ‘storage_engine’;

 

二:mysql的修改表

(1)将grade表的course字段的数据类型修改为varchar(20)

 

alter table grade modify course varchar(20);

 

(2)将s_num字段的位置改到course前面

 

alter table grade modify  s_num  int(10) after id;

 

(3)将grade字段改名为score

 

alter table grade change grade score varchar(10);

 

(4)删除grade的外键约束

alter table grade drop foreign key grade_fk;

 

(5)将grade的存储引擎修改为INnoDB

 

alter grade engine=INnoDB;

 

(6)将student的address字段删除

alter table student drop address;

 

(7)在student表中增加名位phone的字段

alter table student add phone int (10);

 

(8)将grade的表名修改为gradeinfo

lter table grade rename gradeinfo;

 

(9):删除student表

drop table student;        
//由于先前已经删除外键,所以才能删除父表studnet

 

 

 

 

Mysql优化查询工具:工具之三种:使用索引,使用EXPLAIN分析查询以及调整MySQL的内部配置

 

 

创建索引:create INDEX idx_typeid on book1(typeid);

 

MySQL在建立索引时,对不同的数据表有不同的细节:MyISAM数据表,数据行将被保存在数据文件里,其索引值将被保存在索引文件里,而BDB处理程序把同一个BDB数据表的数据值和索引值保存在同一个文件里,InnoDB处理程序则是把所有InnoDB数据表的数据值和索引值都保存在同一个表空间里。

 

建立索引也要考虑它的缺点。首先,索引需要消耗磁盘空间(现在磁盘已经很便宜了),索引越多,消耗的空间也就有多。对于MyISAM数据表,过多的索引会使索引文件优先与数据文件达到尺寸上限。对于BDB数据表,因为它把数据值和索引值都保存在同一个文件里,所以增加索引必然会使他更快地达到BDB数据文件的尺寸上限。InnoDB数据表共同分享着InnoDB表空间里的存储空间,所以增加索引必然会加快InnoDB表空间的消耗速度。不过,只要还能增加磁盘空间,就能通过给InnoDB表空间增加组件的办法扩充之

 

其次,索引过多,会减慢被索引的数据列上的插入、删除和修改操作的速度。因为在写入记录时,MySQL还必须修改与之有关的所有索引

 

设计表时的优化

可以将相关表的字段的长度设置的一样,查询时可以减少查询时匹配的次数

 

 

 

 

MySql优化方案总结:

数据库优化以及一些常见的数据库应用函数和方法:

 

mysql 优化方案总结 u
Mysql 数据库的优化技术 对 mysql 优化时一个综合性的技术,主要包括 a:
表的设计合理化 ( 符合…

mysql优化,mysql优化方案

1.启用InnoDB的每张表一个数据文件设置

InnoDB可以把每个数据库的信息保存在一个.idb数据文件中,每个.ibd文件代表自己的表空间,通过这样的方式可以更快地完成类

似“TRUNCATE”的数据库操作,当删除或截断一个数据库表时你也可以回收未使用的空间,这样配置的另一个好处是你可以将某些数据

 库表存放在一个单独的存储设备,这大大提升你磁盘的I/O负载

MySQL5.6及以上的版本默认启用了innodb_file_per_table=1

 

2.将MySQL数据分储到独立分区上,注意此设置在MariaDB上无效

建议使用单独的磁盘(最好是SSD)用于MySQL服务,创建新分区,格式化新分区,并挂载新分区这里略过,假设/ssd是我们新分区挂载的

目录,MySQL数据目录是/var/lib/mysql, 接下来复制整个数据目录到新分区中 cp
-Rp /var/lib/mysql /ssd/  复制完成后重命名原来的数据目

录 mv /var/lib/mysql /var/lib/mysql-backup 接下来创建一个符号链接 ln -sv
/ssd/mysql /var/lib/mysql

 

3.优化使用InnoDB的缓冲池,InnoDB引擎在内存中有一个缓冲池用于缓存数据和索引,这有助于你更快地执行MySQL/MariaDB查询语

句,在一个专用的机器上,你可能会把60%~70%的内存分配给innodb_buffer_pool_size,如果机器还运行了其它的服务应该重新考虑这个值

得设置

 

4.在MySQL中避免使用swappiness,‘交换’是一个当系统移动部分内存到一个称为‘交换空间’的特殊磁盘空间时的过程,当系统用完

物理内存后就会出现这种情况,系统将信息写入磁盘而不是释放一些内存,磁盘要比内存慢得多因此
sysctl -w vm.swappiness=0 关闭

swappiness

 

5.设置MySQL的最大连接数,只有当执行MySQL请求的时候才会建立连接,执行完成后会关闭连接并被新的连接取代,记住,太多的连

接会导致内存的使用量过高并且会锁住你的MySQL服务器,一般小网站100~200,较大可能需要500~800甚至更多,可以动态地改变

max_connections的值而无需重启MySQL服务器 set global max_connections =
500;

 

6.配置MySQL的线程缓存数量,当客户端断开连接时,如果当前线程数小于thread_cache_size,它的线程将被放入缓存中,下一个请求通

过使用缓存池中的线程来完成,要提高服务器的性能,你可以将此值设置相对高一些,查看线程缓存命中率
show status like

‘threads_created’; show status like ‘connections’;
使用以下公式来计算线程池的命中率 100 – ((threads_created / connections)
*

100)
如果得到一个较低的数字,意味着大多数mysql连接使用新的线程,而不是从缓存中加载,这时需要增加thread_cache_size值,这里有

一个好处是可以动态地改变thread_cache_size,而无需重启MySQL服务,通过以下方法实现
mysql > set global thread_cache_size = 16;

 

7.禁用MySQL的DNS反向查询,在配置文件中添加以下选项 skip_name_resolve

 

 

8.配置MySQL的查询缓存容量,如果你有很多重复的查询并且数据不经常改变—请使用缓存查询,query_cache_size
设置为GB级会降低服

务器的性能,原因是更新过程中线程需要锁定缓存,通常设置为200~300MB应该足够了,网站比较小可以先给64M并在以后及时去增

加,在配置文件中添加如下配置 query_cache_type = 1 query_cache_size =
64M  query_cache_limit = 256K  query_cache_min_res_unit = 2k

 

 

9.配置临时表容量和内存表最大容量,tmp_table_size 和
max_heap_table_size 这两个变量的大小应该相同,它们可以让你避免磁盘写

入,tmp_table_size
是内置内存表的最大空间,如果表的大小超出限值将会被转换为磁盘上的MyISAM表,这会影响数据库的性能,建议

在服务器上设置这两个值为没GB内存给64M, tmp_table_size= 64M
 max_heap_table_size= 64M

 

10.启用MySQL慢查询日志

 

11.检查MySQL的空闲连接,空闲连接会消耗资源,空闲连接是出于‘sleep’状态并且保持了很长一段时间的连接,使用
mysqladmin

processlist -uroot -p | grep “sleep” 查看空闲连接 set global
wait_timeout= 60 要重启也有效则需修改配置文件,默认的值为28800秒

 

12.有时候MySQL/MariaDB
数据库中的表很容易崩溃,尤其是服务器意外关机,文件系统突然崩溃或复制过程中仍然访问数据库,有一

个称为‘mysqlcheck’的免费开源工具,它会自动检查,修复和优化linux中数据库的所有表
 mysqlcheck –auto-repair –check –optimize –

-all-databases

mysqlcheck –auto-repair –check –optimize databasename

 

1.启用 InnoDB
的每张表一个数据文件设置 InnoDB 可以把每个数据库的信息保存在一个 .idb
数据文件中,每个 .ibd 文件…

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注