分类目录 ‘Sql’

« 旧条目

二

8

2010

你可能不知道的MySQL

作者: seasun

前言：

实验的数据表如下定义：

mysql> desc tbl_name;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| uid   | int(11)      | NO   |     | NULL    |       |
| sid   | mediumint(9) | NO   |     | NULL    |       |
| times | mediumint(9) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

存储引擎是MyISAM，里面有10,000条数据。
(全文…)

Tags: ,
Posted in Database, Mysql 没有留言 »

十一

10

2009

Web应用中的轻量级消息队列-MySQL

作者: seasun

Web应用中为什么会需要消息队列？主要原因是由于在高并发环境下，由于来不及同步处理，请求往往会发生堵塞，比如说，大量的insert，update之类的请求同时到达 mysql，直接导致无数的行锁表锁，甚至最后请求会堆积过多，从而触发too many connections错误。通过使用消息队列，我们可以异步处理请求，从而缓解系统的压力。在Web2.0的时代，高并发的情况越来越常见，从而使消息队列有成为居家必备的趋势，相应的也涌现出了很多实现方案，像Twitter以前就使用RabbitMQ实现消息队列服务，现在又转而使用Kestrel来实现消息队列服务，此外还有很多其他的选择，比如说：ActiveMQ，ZeroMQ等。
(全文…)

Tags:
Posted in Mysql 评论关闭

十

22

2009

MySQL最好的优化技巧

作者: seasun

1、选取最适用的字段属性

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

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
(全文…)

Tags:
Posted in Mysql 评论关闭

十

22

2009

MySQL语句优化的原则

作者: seasun

1、使用索引来更快地遍历表。

缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。

合理的索引设计要建立在对各种查询的分析和预测上。一般来说：
a.有大量重复值、且经常有范围查询（ > ,< ，> =,< =）和order by、group by发生的列，可考虑建立群集索引；
b.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；
c.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引
越多越好，恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。
(全文…)

Tags:
Posted in Mysql 评论关闭

十

22

2009

MySql索引优化注意

作者: seasun

设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。设计MySql索引的时候有一下几点注意：
1，创建索引
对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加
索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下
降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
(全文…)

Tags:
Posted in Mysql 评论关闭

七

27

2009

Schema的优化和索引-高性能的索引策略-覆盖索引(Covering Indexes)

作者: seasun

索引是高效找到行的一个方法，但是MySQL也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通 过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引（covering indexex）

覆盖索引是非常强大的工具并且可以大幅度提升性能。考虑下仅仅读取索引的好处：

  • 索引的实体往往小于整个行的大小。如果MySQL仅仅读取索引，意味着访问的数据就非常少了。这对于缓存的工作非常有用，这样相应的时间基本都是 来自复制数据而已。对于IO限制也非常有用，因为索引要比数据更小并且更容易的写入内存中。（这对于MyISAM尤其有效，它可以对索引进行压缩，这样索 引就变得更小了）。
  • 索引是根据索引值的来排序的，因此IO限制范围的访问相对比从随机硬盘位置所需的IO是较少的。对于一些存储引擎，比如MyISAM，你甚至可以用OPTIMIZE这个表来获取全部的排序索引。这样可使简单的范围查询使用完全连续的索引的访问。
  • 大部分存储引擎缓存索引要好于数据。一些存储引擎，比如MyISAM只缓存索引。因为操作系统缓存了MyISAM的数据，访问数据需要一个系统的调用。这样会导致非常严重的性能问题。尤其对于缓存来说，系统的调用是数据访问消耗最大的一部分。
  • 覆盖索引对与InnoDB表有些特殊的效用。因为InnoDB是聚簇索引。InnoDB的次要索引在它们叶子节点保存了行的主键。因此，次要索引的覆盖可以避免在主键上另一个索引的查找。
在这些场景中，从索引中满足一个查询消耗要比查询行要低很多。
覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。Hash, spatial, 和full-text索引不存储值，因此MySQL只能使用B-TREE。并且不同的存储引擎实现覆盖索引都是不同的。并不是所有的存储引擎都支持它们。
当一个查询被索引所覆盖。（an index-covered query）。你使用EXPLAIN就会发现EXTRA列的值为“Using index”。一个例子，sakila.inventory表由一个多列的索引在store_id, film_id的列上。MySQL能使用索引来访问这两列。如下
mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: inventory
type: index
possible_keys: NULL
key: idx_store_id_film_id
key_len: 3
ref: NULL
rows: 4673
Extra: Using index
覆盖索引的语句有些诡异的是会关闭优化。MySQL语句优化器在执行语句之前会决定是否有一个索引覆盖它。假使这个索引覆盖了一个WHERE条件，但是并不是整个查询。如果这个条件评估为false，MySQL51以及以前版本都会取出行。
让我们来看看为什么会这样。以及怎样重写这个查询来解决上面所说的问题。
mysql> EXPLAIN SELECT * FROM products WHERE actor=’SEAN CARREY’
-> AND title like ‘%APOLLO%’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: products
type: ref
possible_keys: ACTOR,IX_PROD_ACTOR
key: ACTOR
key_len: 52
ref: const
rows: 10
Extra: Using where
这个索引不能覆盖这个查询有如下两条原因：
  • 没有索引覆盖这个查询，因为我们选择了这个表的所有列，并且没有一个索引覆盖这些列。理论上来说MySQL还能使用一个捷径：WHERE条件中有一列被索引覆盖，因此MySQL会使用索引先找到这个actor并且检查title是否匹配，之后再取整个行。
  • MySQL不能在索引中执行LIKE操作符。这个受限于底层的存储引擎API。在索引的操作，这只能支持简单的比较。MySQL可以LIKE中匹 配前缀，因为把它们转为简单的比较了。但是这个例子中前缀的通配符使使用索引查询变为了不可能。最终。MySQL服务器将会获取和匹配这个行的值，而不是 索引的值。
我们可以使用加索引（artist, title, prod_id）和重写查询来解决上面的问题。重写的查询如下
mysql> EXPLAIN SELECT *
-> FROM products
->    JOIN (
->       SELECT prod_id
->       FROM products
->       WHERE actor=’SEAN CARREY’ AND title LIKE ‘%APOLLO%’
->    ) AS t1 ON (t1.prod_id=products.prod_id)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
…omitted…
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: products
…omitted…
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: products
type: ref
possible_keys: ACTOR,ACTOR_2,IX_PROD_ACTOR
key: ACTOR_2
key_len: 52
ref:
rows: 11
Extra: Using where; Using index
现在MySQL在第一阶段的查询使用了覆盖索引，当它找到了在子查询匹配的行的时候。它不会在这个执行语句使用索引。但是总比没有强。
这个优化的有效性完全依靠于有多少行被找到。假使products表包含了1百万行。让我们来看看不同数据集下这两个查询的表现。
  1. 第一个，Sean Carrey有30000个产品并且title包含Apollo有2000行。
  2. 第二个，分别是30000和40
  3. 第三个，分别是50和10
测试结果如下
Dataset     Original query                 Optimized query
Example1  5 queries per sec            5 queries per sec
Example2  7 queries per sec            35 queries per sec
Example3  2400 queries per sec      2000 queries per sec
让我们解释下结果
  • 在例子一中，返回的大数据集。并没有看到优化效果。大部分时间花在读取和发送数据上了。
  • 例子二中，在索引过滤后，第二个条件过滤了很少的数据，让我们看看语句的油画效果：是没优化的语句的5倍之多！效率高的原因是第二个语句仅仅需要查找40行而不是30000行。
  • 第三个例子中，知道了子查询是低效的。在索引过滤数据非常小的情况下，子查询的消耗大于了直接从整张表查询所有数据的消耗。
在MySQL5.1以及之前的版本中，这个优化有的时候可以避免读取没有必要的行。MySQL6.0就会避免一些额外的语句。如果升级的话，就不必优化了。
在大多数的存储引擎中，一个索引所覆盖仅仅是访问列是索引的一部分的查询语句。然而，InnoDB可以使优化更进一步。回忆一下，InnoDB 的次要索引在叶子节点中保存了主键的值。意味着InnoDB次要索引可由一个额外的列了。InnoDB就可以使用这一特性来覆盖查询语句了。
举个例子，sakila.actor 表使用了InnoDB并且在last_name有个索引。因此这个索引能覆盖语句来获取主键的值actor_id，即使这一列技术上并不是索引的一部分。
mysql> EXPLAIN SELECT actor_id, last_name
-> FROM sakila.actor WHERE last_name = ‘HOPPER’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 2
Extra: Using where; Using index

Tags:
Posted in Mysql 评论关闭

六

30

2009

40+ Web前端开发必备的备忘单[上]

作者: seasun

Cheat Sheet 一词在中文中并没有很贴切的对译，大概是考试作弊条一类的东西，这要求 Cheat Sheet 必须短小精悍又覆盖广泛，作为 Web 开发与设计师，免不了在工作时查询大量资料，某个 Web 色值，某个 JavaScript 库的核心语法，这类资料如果攒齐了，怕有半间屋子那么多，如果用 Cheet Sheet 也许几十页纸就够了，本文收集了近百份用于 Web 开发与设计的 Cheet Sheet，你会发现他们非常实用。

HTML, XHTML, CSS2

CSS2 Cheat Sheet


(全文…)

Tags: , , , , , , , , , , , ,
Posted in JQuery, JavaScript, Mysql, PHP, Prototype, SEO, Sql, Tool 评论关闭

四

22

2009

提高MySQL 查询效率的三个技巧

作者: seasun
MySQL由于它本身的小巧和操作的高效, 在数据库应用中越来越多的被采用.我在开发一个P2P应用的时候曾经使用MySQL来保存P2P节点,由于P2P的应用中,结点数动辄上万个,而且节点变 化频繁,因此一定要保持查询和插入的高效.以下是我在使用过程中做的提高效率的三个有效的尝试.
l 使用statement进行绑定查询
使用statement可以提前构建查询语法树,在查询时不再需要构建语法树就直接查询.因此可以很好的提高查询的效率. 这个方法适合于查询条件固定但查询非常频繁的场合.
使用方法是:
  1. 绑定, 创建一个MYSQL_STMT变量,与对应的查询字符串绑定,字符串中的问号代表要传入的变量,每个问号都必须指定一个变量.
  2. 查询, 输入每个指定的变量, 传入MYSQL_STMT变量用可用的连接句柄执行.
代码如下:

(全文…)

Tags: ,
Posted in Mysql 评论关闭

四

8

2009

常用的数据库统计SQL语句(2)

作者: seasun

1.解决被零除(零做除数时)

–来自百度问吧

在统计系统中,需要求百分数,
如(sum(baseprice)-sum(quote))/sum(baseprice)*100)中,
如果sum(baseprice)为0,系统会报错,不能运行
(全文…)

Tags:
Posted in Sql 评论关闭

四

8

2009

常用的一些数据统计SQL语句

作者: seasun

只有在作统计分析的时候，你才能感觉到数据库的强大，才能感觉到数字游戏的趣味性所在。当然，对于企业而言，则是用于进行决策分析的最好支持之一. 最近刚完工的一个项目中对SQL的运用，让我又对SQL的理解与使用加深了一层。很高兴与大家分享其中的乐趣.
废话少说，我们先建一个原始表吧。
(全文…)

Tags:
Posted in Sql 评论关闭

« 旧条目