MySQL Order By索引优化

转自http://www.phpq.net/mysql/mysql-order-by.html) 在一些情况下,MySQL可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序。尽管 ORDER BY 不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的 ORDER BY 字段在 WHERE 子句中都被包括了。 使用索引的MySQL Order By 下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分: SELECT * FROM t1 ORDER BY key_part1,key_part2,… ; SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2; SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2; SELECT * FROM [Read More →]

Posted in mysql. 评论暂缺 »

mysql 中where 和 order by 的索引优化

mysql的where和order by 在一起时,order by 后的索引不能命中。 有这样的问题: mysql> explain SELECT * FROM product WHERE compID = 120  LIMIT 0,15; +—-+————-+————+——+——————+———–+———+——-+——+——-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+————+——+——————+———–+———+——-+——+——-+ | 1 | SIMPLE | product | ref | comp_prod,compID | comp_prod | 4 [Read More →]

Posted in mysql. 评论暂缺 »

关于innodb外键约束

1. 什么是参照完整性? ——————– 参照完整性(完整性约束)是数据库设计中的一个重要概念,当数据库中的一个表与一个或多个表进行关联时都会涉及到参照完整性。比如下面这个例子: 文章分类表 – categories category_id     name 1               SQL Server 2               Oracle 3               PostgreSQL 5               SQLite 文章表 – articles article_id      category_id     title 1               1               aa 2               2               bb 3               4               cc 可见以上两个表之间是通过category_id,其中categories表有4条记录,articles表有3条记录。 然而可能因为某种原因我们删掉了categories 表中category_id=4的记录,而articles表却还是有一条category_id=4的记录,很明显,category_id=4的这条 记录不应该存在在articles表中,这样会很容易造成数据错乱。 相反,外键关系(Foreign Key relationships)讨论的是父表(categories)与子表(articles)的关系,通过引入外键(Foreign Key)这个概念来保证参照完整性(Referential integrity),将使会数据库变的非常简单。比如,要要做到删除categories表中category_id=4记录的同时删除 articles 表中category_id=4的所有记录,如果没有引入外键的话,我们就必须执行2条SQL语句才行;如果有外键的话,可以很容易的用一条SQL语句就 可以达到要求。 2. 使用外键的条件 —————– MySQL只在v3.23.34版本以后才引入外键的,所以在这之前的版本就别想了:),除此之外,还必须具备以下几个条件: 1) 在my.cnf配置文件中打开InnoDB引擎支持。 # Uncomment the following [Read More →]

Posted in mysql. 评论暂缺 »

MySQL 服务器调优(转自ibm)

如今,开发人员不断地开发和部署使用 LAMP(Linux®、Apache、MySQL 和 PHP/Perl)架构的应用程序。但是,服务器管理员常常对应用程序本身没有什么控制能力,因为应用程序是别人编写的。这份 共三部分的系列文章 将讨论许多服务器配置问题,这些配置会影响应用程序的性能。本文是本系列文章的第三部分,也是最后一部分,将重点讨论为实现最高效率而对数据库层进行的调优。 关于 MySQL 调优 有 3 种方法可以加快 MySQL 服务器的运行速度,效率从低到高依次为: 替换有问题的硬件。 对 MySQL 进程的设置进行调优。 对查询进行优化。 替换有问题的硬件通常是我们的第一考虑,主要原因是数据库会占用大量资源。不过这种解决方案也就仅限于此了。实际上,您通常可以让中央处理器(CPU)或磁盘速度加倍,也可以让内存增大 4 到 8 倍。 第二种方法是对 MySQL 服务器(也称为 mysqld)进行调优。对这个进程进行调优意味着适当地分配内存,并让 mysqld 了解将会承受何种类型的负载。加快磁盘运行速度不如减少所需的磁盘访问次数。类似地,确保 MySQL 进程正确操作就意味着它花费在服务查询上的时间要多于花费在处理后台任务(如处理临时磁盘表或打开和关闭文件)上的时间。对 mysqld 进行调优是本文的重点。 最好的方法是确保查询已经进行了优化。这意味着对表应用了适当的索引,查询是按照可以充分利用 MySQL 功能的方式来编写的。尽管本文并没有包含查询调优方面的内容(很多著作中已经针对这个主题进行了探讨),不过它会配置 mysqld 来报告可能需要进行调优的查询。 虽然已经为这些任务指派了次序,但是仍然要注意硬件和 mysqld 的设置以利于适当地调优查询。机器速度慢也就罢了,我曾经见过速度很快的机器在运行设计良好的查询时由于负载过重而失败,因为 mysqld 被大量繁忙的工作所占用而不能服务查询。 记录慢速查询 在一个 SQL 服务器中,数据表都是保存在磁盘上的。索引为服务器提供了一种在表中查找特定数据行的方法,而不用搜索整个表。当必须要搜索整个表时,就称为表扫描。通常来说,您可能只希望获得表中数据的一个子集,因此全表扫描会浪费大量的磁盘 I/O,因此也就会浪费大量时间。当必须对数据进行连接时,这个问题就更加复杂了,因为必须要对连接两端的多行数据进行比较。 当然,表扫描并不总是会带来问题;有时读取整个表反而会比从中挑选出一部分数据更加有效(服务器进程中查询规划器用来作出这些决定)。如果索引的使 用效率很低,或者根本就不能使用索引,则会减慢查询速度,而且随着服务器上的负载和表大小的增加,这个问题会变得更加显著。执行时间超过给定时间范围的查 询就称为慢速查询。 您可以配置 mysqld 将这些慢速查询记录到适当命名的慢速查询日志中。管理员然后会查看这个日志来帮助他们确定应用程序中有哪些部分需要进一步调查。清单 [Read More →]

Posted in mysql. 评论暂缺 »

mysqlreport 中文文档

mysqlreport 以很友好的方式显示 MySQL状态变。事实上,它几乎报告了所有的状态。不像 SHOW STATUS 只是在显示了100多个状态值,mysqlreport 则以人性化的方式阐释和格式化了这些状态值,大大增加了其可读性。可以 点击这里 查看mysqlreport的例子。mysqlreport 的好处是可以快速的查看各种状态参数组,从而了解服务器的运行状态情况,而无需从 SHOW STATUS 的结果中人工计算。例如索引读取比率是个重要的参数,但是 SHOW STATUS 中并没有显示;它是一个推断值(key_reads 和 key_read_requests 的比值)。 本文档描述了 mysqlreport 的所有命令行选项,大部分是用于控制如何显示结果的。本文并没有解释这些推断值是怎么计算得到的,可以点击 理解mysqlreport 查看具体的计算方法。 注意 命令行选项的格式是 –选项,不过 -选项 的格式也可以。所有的选项都有其缩写,只要是唯一的。例如,选项 –host 可以缩写成 –ho,不过不能写成 –h,因为 –h 有歧义,可能是 –host 或者 –help。 选项列表 –user USER –password –host ADDRESS –port PORT –socket SOCKET –no-mycnf –help 这些选项是模拟其他标准应用程序的。从 2.3 版本开始,–password 可以在后面加上参数,如 “–password [Read More →]

Posted in mysql. 评论暂缺 »

mysql分区表介绍

相信有很多人经常会问同样的一个问题:当 MySQL 的总记录数超过了100万后,会出现性能的大幅度下降吗?答案是肯定的,但是性能下 降>的比率不一而同,要看系统的架构、应用程序、还有>包括索引、服务器硬件等多种因素而定。当有网友问我这个问题的时候,我最常见的回 答>就是:分表,可以根据id区间或者时间先后顺序等多 种规则来分表。分表很容易,然而由此所带来的应用程序甚至是架构方面的改动工作却不>容小觑,还包括将来的扩展性等。 在以前,一种解决方案就是使用 MERGE 类型,这是一个非常方便的做饭。架构和程序基本上不用做改动,不过,它的缺点是显见的: 只能在相同结构的 MyISAM 表上使用 无法享受到 MyISAM 的全部功能,例如无法在 MERGE 类型上执行 FULLTEXT 搜索 它需要使用更多的文件描述符 读取索引更慢 这个时候,MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了: 与单个磁盘或文件系统分区相比,可以存储更多的数据 很容易就能删除不用或者过时的数据 一些查询可以得到极大的优化 涉及到 SUM()/COUNT() 等聚合函数时,可以并行进行 IO吞吐量更大 分区允许可以设置为任意大小的规则,跨文件系统分配单个表的多个部分。实际上,表的不同部分在不同的位置被存储为单独的表。 二、分区的类型 RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。参见18.2.1节,RANGE分区 LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。参见18.2.2节,LIST分区 HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。参见18.2.3节,HASH分区 KEY 分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。 参见18.2.4节,KEY分区 三、分区例子: RANGE 类型 CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY [Read More →]

Posted in mysql. 评论暂缺 »

mysql进程状态值 show processlist ;

explain来了解SQL执行的状态。 explain select * from wp_posts\G; 使用show status like “Handler_read%”; 来了解索引的效果。 Handler_read_key 值高表示索引效果好,Handler_read_rnd_next值高表示索引低效。 用show processlist 查看当前运行状态。 mysql> show processlist; +—–+————-+——————–+ | Id | User | Host | db | Command | Time| State | Info +—–+————-+——————–+ |207|root |192.168.0.20:51718 |mytest | Sleep | 5 | | NULL |208|root |192.168.0.20:51719 |mytest | Sleep | 5 | | [Read More →]

Posted in mysql. 评论暂缺 »

mysql explain命令详解

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法,在select语句前加上explain就可以了: 如:explain select surname,first_name form a,b where a.id=b.id 分析结果形式如下: table type possible_keys key key_len ref rows Extra a range id first_name First_name 9 NULL 23112 Using where Using temporary Using filesort b ref id first_name id 4 id 2 Using where   EXPLAIN列的解释: table 显示这一行的数据是关于哪张表的 type   这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL possible_keys 显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 key   实 际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引 [Read More →]

Posted in mysql. 评论暂缺 »

MySql不能启动 Can’t start server : Bind on unix socket: Permission denied

centos5 mysqld Can’t start server : Bind on unix socket: Permission denied 原因:mysql.sock无法建立,权限问题! 解决:chmod -R 777 /var/lib/mysql 后记:这是当初刚开始工作的时候遇到的问题,想想都3年了…

Posted in mysql. 评论暂缺 »

mysql Innodb Shutdown completed; log sequence number解决办法

071129 17:08:42 InnoDB: Starting shutdown… 071129 17:08:46 InnoDB: Shutdown completed; log sequence number 0 2566023 071129 17:08:46 [Note] /usr/libexec/mysqld: Shutdown complete 据说这是mysql的bug,找到的解决办法是重新初始化mysql #mysql_install_db #service mysqld start

Posted in mysql. 评论暂缺 »