有关mysql优化的一些东东整理

1.查询帮助 ? contents
2.使用合成的散列值,分离BLOB或者TEXT
3.货币使用定点数(decimal或者numberic)
4.sql_mode?
5.order by rand() limit 1000;
6.优化show status like 'Com' Com_select Com_insert等查看插入多还是查询多
7.Handler_read_key 的值将很高,这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用.Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明表索引不正确或写入的查询没有利用索引.
8.定期分析表   ANALYZE TABLE   CHECK TABLE    CHECKSUM TABLE
9.优化表 OPTIMIZE TABLE
10.导入大数据:Myisam  ALTER TABLE tblname DISABLE KEYS    loading the data    ALTER TABLE tblname ENABLE KEYS;
Innodb  SET UNIQUE_CHECKS=0     SET AUTOCOMMIT=0
11.优化insert:LOAD DATA INFILE    replace   ignore
12.优化group by     ORDER BY NULL
13.show status like 'Table%';               show status like 'innodb_row_lock%';
14.CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
15.影响Mysql 性能的重要参数:
key_buffer_size :  说明:键缓存(变量key_buffer_size) 被所有线程共享;服务器使用的其它缓存则根据需要分配。此参数只适用于myisam 存储引擎。
table_cache:数据库中打开表的缓存数量。table_cache 与max_connections 有关。例如,对于200 个并行运行的连接,应该让表的缓至少有200 * N,这里N 是可以执行的查询的一个联接中表的最大数量。还需要为临时表和文件保留一些额外的文件描述符。
innodb_buffer_pool_size:缓存InnoDB 数据和索引的内存缓冲区的大小。你把这个值设得越高,访问表中数据需要得磁盘I/O 越少。

innodb_flush_log_at_trx_commit:0|1|2
innodb_additional_mem_pool_size:1M
innodb_table_locks:0|1
innodb_lock_wait_timeout:
innodb_support_xa:通过该参数设置是否支持分布式事务,默认值是ON 或者1,表示支持分布式事务。如果确认应用中不需要使用分布式事务,则可以关闭这个参数,减少磁盘刷新的次数并获得更好的InnoDB 性能。
innodb_doublewrite:
innodb_log_buffer_size:
innodb_log_file_size:

1.数据库的设计
尽量把数据库设计的更小的占磁盘空间.
1).尽可能使用更小的整数类型.(mediumint就比int更合适).
2).尽可能的定义字段为not null,除非这个字段需要null.(这个规则只适合字段为KEY的情形)
3).如果没有用到变长字段的话比如varchar,那就采用固定大小的纪录格式比如char.(CHAR 总是比VARCHR快)
4).表的主索引应该尽可能的短.这样的话每条纪录都有名字标志且更高效.
5).只创建确实需要的索引。索引有利于检索记录,但是不利于快速保存记录。如果总是要在表的组合字段上做搜索,那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段.如果总是需要用到很多字段,首先就应该多复制这些字段,使索引更好的压缩。
(这条只适合MYISAM引擎的表,对于INNODB则在保存记录的时候关系不大,因为INNODB是以事务为基础的,如果想快速保存记录的话,特别是大批量的导入记录的时候)
6).所有数据都得在保存到数据库前进行处理。
7).所有字段都得有默认值。
8).在某些情况下,把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时,它可能使用更小的静态格式表的情况下更是如此。
(具体的表现为:MYISAM表的MERGE类型,以及MYISAM和INNODB通用的分区,详情见手册)
9).不会用到外键约束的地方尽量不要使用外键。

2.系统的用途
1).及时的关闭对MYSQL的连接。
2).explain 复杂的SQL语句。(这样能确定你的SELECT 语句怎么优化最佳)
3).如果两个关联表要做比较话,做比较的字段必须类型和长度都一致.(在数据庞大的时候建立INDEX)
4).LIMIT语句尽量要跟order by或者 distinct.这样可以避免做一次full table scan.
5).如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename.
不过有一个问题,truncate 不会在事务处理中回滚。因为她要调用create table 语句。
(Truncate Table 语句先删除表然后再重建,这个是属于文件级别的,所以自然快N多)
实测例子:
song2为INNODB表。


代码如下:

mysql> select count(1) from song2;
+----------+
| count(1) |
+----------+
|   500000 |
+----------+
1 row in set (0.91 sec)
mysql> delete from song2;
Query OK, 500000 rows affected (15.70 sec)
mysql> truncate table song2;
Query OK, 502238 rows affected (0.17 sec)

6).能使用STORE PROCEDURE 或者 USER FUNCTION的时候.(ROUTINE总是减少了服务器端的开销)
7).在一条insert语句中采用多重纪录插入格式.而且使用load data infile来导入大量数据,这比单纯的indert快好多.(在MYSQL中具体表现为:INSERT INTO TABLEQ VALUES (),(),...();)
(还有就是在MYISAM表中插入大量记录的时候先禁用到KEYS后面再建立KEYS,具体表现语句:
 ALTER TABLE TABLE1 DISABLE KEYS;ALTER TABLE TABLE1 ENABLE KEYS;
而对于INNNODB 表在插入前先 set autocommit=0;完了后:set autocommit=1;这样效率比较高。)
8).经常OPTIMIZE TABLE 来整理碎片.
9).还有就是date 类型的数据如果频繁要做比较的话尽量保存在unsigned int 类型比较快。

3.系统的瓶颈
1).磁盘搜索.
并行搜索,把数据分开存放到多个磁盘中,这样能加快搜索时间。
2).磁盘读写(IO)
可以从多个媒介中并行的读取数据。
3).CPU周期
数据存放在主内存中.这样就得增加CPU的个数来处理这些数据。
4).内存带宽
当CPU要将更多的数据存放到CPU的缓存中来的话,内存的带宽就成了瓶颈。

(0)

相关推荐

  • 101个MySQL优化技巧和提示

    MySQL是一个功能强大的开源数据库.随着越来越多的数据库驱动的应用程序,人们一直在推动MySQL发展到它的极限.这里是101条调节和优化MySQL安装的技巧.一些技巧是针对特定的安装环境的,但这些思路是通用的.我已经把他们分成几类,来帮助你掌握更多MySQL的调节和优化技巧. MySQL 服务器硬件和操作系统调节: 1. 拥有足够的物理内存来把整个InnoDB文件加载到内存中--在内存中访问文件时的速度要比在硬盘中访问时快的多. 2. 不惜一切代价避免使用Swap交换分区 – 交换时是从硬盘读

  • mysql数据库sql优化原则(经验总结)

    一.前提 这里的原则只是针对MySQL数据库,其他的数据库某些是殊途同归,某些还是存在差异.我总结的也是MySQL普遍的规则,对于某些特殊情况得特殊对待.在构造SQL语句的时候要养成良好的习惯. 二.原则总结 原则1.仅列出需要查询的字段,这对速度不会明显的影响,主要是考虑节省应用程序服务器的内存. 原来语句: select * from admin 优化为: select admin_id,admin_name,admin_password from admin 原则2.尽量避免在列上做运算,

  • 非常不错的MySQL优化的8条经验

    1.选取最适用的字段属性   MySQL 可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快.因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小.例如,在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数据库增加了不必要的空间,甚至使用 VARCHAR这种类型也是多余的,因为CHAR(6) 就可以很好的完成任务了.同样的,如果可以的话,我们应该使用MEDIUMINT而不是BIGINT来定义整型字段. 另外一个提高

  • MySQL优化必须调整的10项配置

    当我们被人雇来监测MySQL性能时,人们希望我们能够检视一下MySQL配置然后给出一些提高建议.许多人在事后都非常惊讶,因为我们建议他们仅仅改动几个设置,即使是这里有好几百个配置项.这篇文章的目的在于给你一份非常重要的配置项清单. 我们曾在几年前在博客里给出了这样的建议,但是MySQL的世界变化实在太快了!写在开始前-即使是经验老道的人也会犯错,会引起很多麻烦.所以在盲目的运用这些推荐之前,请记住下面的内容: 一次只改变一个设置!这是测试改变是否有益的唯一方法. 大多数配置能在运行时使用SET

  • LAMP服务器性能优化技巧之Mysql优化

    Apache服务器优化.PHP优化.Mysql优化 对于程序开发人员而言,目前使用最流行的两种后台数据库即为MySQL and SQL Server.这两者最基本的相似之处在于数据存储和属于查询系统.如果你想建立一个.NET服务器体系,这一体系可以从多个不同平台访问数据,参与数据库的管理,那么你可以选用SQL Server服务器.如果你想建立一个第三方动态网站,从这一站点可以从一些客户端读取数据,那么MySQL将是一个不错的选择. 1.编译和安装MySQL 通过你的系统挑选可能最好的编译器,你通

  • MySQL优化配置文件my.ini(discuz论坛)

    在Apache, PHP, MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.对于Discuz!论坛程序也是如此,MySQL的设置是否合理优化,直接影响到论坛的速度和承载量!同时,MySQL也是优化难度最大的一个部分,不但需要理解一些MySQL专业知识,同时还需要长时间的观察统计并且根据经验进行判断,然后设置合理的参数. 下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.cnf)的优化. 一.服务

  • MySQL性能优化 出题业务SQL优化

    先简单介绍一下项目背景.这是一个在线考试练习平台,数据库使用MySQL,表结构如图所示: Question是存储题目的表,数据量在3万左右.AnswerResult表是存储用户作答结果的表,分表之后单表记录大概在300万-400万. 需求:根据用户的作答结果出练习卷,题目的优先级为:未做过的题目>只做错的题目>做错又做对的题目>只做对的题目. 在"做错又做对的题目"中,会按错误次数和正确次数的比例进行权重计算,比如:A.做错10次,做对100次:B.做错10次,做对2

  • mysql优化配置参数

    第一种 复制代码 代码如下: #This File was made using the WinMySQLAdmin 1.4 Tool #2004-2-23 16:28:14 #Uncomment or Add only the keys that you know how works. #Read the MySQL Manual for instructions [mysqld] basedir=D:/mysql #bind-address=210.5.*.* datadir=D:/mysq

  • 跟着hsp一步步学mysql优化的方法

    在第二集中 1.show status like 'Com%' 等价于show status session like 'Com%'百年--显示当前控制台情况 show global status like 'Com%'--显示数据库从启动到查询次数 show session status like 'Com_select'; show status like 'uptime' -- 已启动多久show status like 'Connections' --连接的次数 slow_queries

  • mysql优化取随机数据慢的方法

    前天因为工作需要我把从一个5W记录的数据库中随机抽取几条记录了,这里我是直接使用mysql rand by函数来直接,几千条记录没关系,但如果到了几万条感觉要几秒,这个就很慢了,下面小编与大家一起来看看mysql 取随机数据慢优化过程.MySQL很多时候需要获取随机数据,举个例子,要从tablename表中随机提取一条记录,大家一般的写法就是: 复制代码 代码如下: SELECT * FROM tablename ORDER BY RAND() LIMIT 1 但是,后来我查了一下MYSQL的官

  • MySQL优化全攻略-相关数据库命令

    接下来我们要讨论的是数据库性能优化的另一方面,即运用数据库服务器内建的工具辅助性能分析和优化.    ▲ SHOW    执行下面这个命令可以了解服务器的运行状态:   mysql >show status;   该命令将显示出一长列状态变量及其对应的值,其中包括:被中止访问的用户数量,被中止的连接数量,尝试连接的次数,并发连接数量最大值,以及其他许多有用的信息.这些信息对于确定系统问题和效率低下的原因是十分有用的.    SHOW命令除了能够显示出MySQL服务器整体状态信息之外,它还能够显示

随机推荐