MySQL数据库优化总结
- - CSDN博客推荐文章 对于一个以数据为中心的应用，数据库的好坏直接影响到程序的性能，因此数据库性能至关重要. 一般来说，要保证数据库的效率，要做好以下四个方面的工作：数据库设计、sql语句优化、数据库参数配置、恰当的硬件资源和操作系统，这个顺序也表现了这四个工作对性能影响的大小. 一、数据库设计
适度的反范式,注意是适度的.
MySQL数据库优化的一些笔记
标签：
Internet
| 发表时间：2012-05-16 02:38 | 作者：Xiaoxia
出处：http://xiaoxia.org
0. 索引很重要
之前列举记录用了下面的语句。state字段为索引。
SELECT * FROM feed_urls WHERE state='ok' AND feed_url<>'' LIMIT N,10
当记录数量很大时，有几万之后，这句SQL就很慢了。主要是因为feed_url没有建立索引。后来的解决方法是，把feed_url为空的，设为一个ok以外的state值，就行了。
1、索引不是万能的
为了计算记录总数，下面的语句会很慢。
mysql> SELECT COUNT(*) FROM feed_urls WHERE state='error';
+----------+
| COUNT(*) |
+----------+
| 30715 |
+----------+
1 row in set (0.14 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM feed_urls WHERE state='error'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: feed_urls
type: ref
possible_keys: state,page_index
key: page_index
key_len: 10
ref: const
rows: 25936
Extra: Using where; Using index
1 row in set (0.00 sec)
state为索引，请求用时140ms。遍历了state='error'索引下的每一条记录。
mysql> SELECT state,COUNT(*) FROM feed_urls GROUP BY state;
+----------+----------+
| state | COUNT(*) |
+----------+----------+
| error | 30717 |
| fetching | 8 |
| nofeed | 76461 |
| ok | 74703 |
| queued | 249681 |
+----------+----------+
5 rows in set (0.55 sec)
mysql> EXPLAIN SELECT state,COUNT(*) FROM feed_urls GROUP BY state\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: feed_urls
type: index
possible_keys: NULL
key: state
key_len: 10
ref: NULL
rows: 431618
Extra: Using index
1 row in set (0.00 sec)
请求用时550ms。遍历了每个state下的每一条记录。
改进方法：
独立一个表用来计数，使用MySQL的Trigger同步计数：
CREATE TRIGGER my_trigger AFTER UPDATE ON feed_urls
FOR EACH ROW BEGIN
IF OLD.state <> NEW.state THEN
IF NEW.state='ok' THEN
UPDATE feed_stat SET count_feed = count_feed + 1;
END IF;
IF NEW.state IN ('ok', 'error', 'nofeed') THEN
UPDATE feed_stat SET count_access = count_access + 1;
END IF;
END IF;
END
2. 当分页很大时
mysql> SELECT * FROM feed_urls LIMIT 230000, 1\G
*************************** 1. row ***************************
id: 736841f82abb0bc87ccfec7c0fdbd09c30b5a24d
link: http://mappemunde.typepad.com/
title: Tim Peterson
feed_url: NULL
update_time: 2012-05-12 11:01:56
state: queued
http_server: NULL
abstract: NULL
previous_id: ceea30e0ba609b69198c53ce71c44070d69038c5
ref_count: 1
error: NULL
aid: 230001
1 row in set (0.50 sec)
mysql> EXPLAIN SELECT * FROM feed_urls LIMIT 230000, 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: feed_urls
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 431751
Extra:
1 row in set (0.00 sec)
读取一条记录，耗时500ms，因为表记录是变长的，所以MySQL不能算出目标位置，只能每一条记录的数过去。
改进方法：
通过索引定位，数索引比数记录要快，因为索引占用的空间比整条记录小很多。
mysql> SELECT * FROM (SELECT aid FROM feed_urls ORDER BY aid LIMIT 215000, 1) d JOIN feed_urls u ON d.aid=u.aid\G
*************************** 1. row ***************************
aid: 215001
id: 2e4b1a385c8aae40b3ec2af9153805ca446f2029
link: http://ncse.com/
title: NCSE
feed_url: NULL
update_time: 2012-05-12 10:47:15
state: queued
http_server: NULL
abstract: NULL
previous_id: 819a6e3c5edc1624a9b8f171d8d3ae269843785f
ref_count: 3
error: NULL
aid: 215001
1 row in set (0.06 sec)
mysql> EXPLAIN SELECT * FROM (SELECT aid FROM feed_urls ORDER BY aid LIMIT 215000, 1) d JOIN feed_urls u ON d.aid=u.aid\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table:
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: u
type: const
possible_keys: aid
key: aid
key_len: 4
ref: const
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: feed_urls
type: index
possible_keys: NULL
key: aid
key_len: 4
ref: NULL
rows: 211001
Extra: Using index
3 rows in set (0.15 sec)
耗时60ms，比之前的方法快了将近10倍。如果LIMIT语句里还有WHERE a=1，应该建立一个（a，aid）的索引。
话说，MySQL好像还是不能直接算出第21500条索引的位置呀，这种方法还是数了索引了，能算出来就直接0ms了。不过这样的效率，对于百万级的，还能应付吧。如果是千万级的或者像我之前在KS创建的一张上亿条记录的表（120G），这种方法就肯定不行了。
经过上述优化，打开最后一页的速度已经很快了（之前需要800ms，现在则为300ms左右）。
膜拜下这Burst.NET最低档次的VPS (30RMB/month)。
root@xiaoxia-pc:~/# ping feed.readself.com -n PING app.readself.com (184.82.185.32) 56(84) bytes of data. 64 bytes from 184.82.185.32: icmp_req=1 ttl=45 time=161 ms 64 bytes from 184.82.185.32: icmp_req=2 ttl=45 time=161 ms 64 bytes from 184.82.185.32: icmp_req=3 ttl=45 time=161 ms
用同样的方法，优化了搜索引擎的排名算法。即排名过程中选取尽量少的值出来排序，排序后再JOIN一次获取结果的信息。
排序过程如下：
SELECT u.*, count_level(u.id) lv
FROM(
SELECT f.id, f.ref_count, MATCH(i.link,i.title) AGAINST (keywords) score
FROM feed_index i
JOIN feed_urls f ON f.id=i.id
WHERE MATCH(i.link,i.title) AGAINST (keywords)
ORDER BY score*0.5 + score*0.5*(ref_count/max_ref_count_in_result) DESC
LIMIT offset,10
) d JOIN feed_urls u ON u.id = d.id
目前处理10万记录的全文索引数据，MySQL还是可以满足的，就是不知道上百万之后，还能不能撑下去。撑不下去就依赖第三方的工具了，例如Sphinx 
3. SELECT里的函数
给FeedDB增加了层次的显示。因为本人太懒，所以没有给数据库表增加一个记录深度的字段。所以，直接写了一个MySQL的自定义函数 count_level，用来统计通过parent_id一直找到顶层经过的路径长度（Level）。
CREATE DEFINER=`feeddb_rw`@`%` FUNCTION `count_level`(fid char(40)) RETURNS int(11)
BEGIN
SET @levels = 0;
SET @found = false;
WHILE NOT @found DO
SELECT previous_id INTO @prev_id FROM feed_urls WHERE id=fid;
IF @prev_id is null OR @prev_id = '' THEN
SET @found = true;
ELSE
SET @levels = @levels + 1;
SET fid = @prev_id;
END IF;
END WHILE;
IF @prev_id is null THEN
RETURN null;
END IF;
RETURN @levels;
END
在网页显示的时候用了类似下面的SQL语句。
mysql> SELECT u.*, count_level(u.id) FROM feed_urls u ORDER BY ref_count DESC LIMIT 12000,1\G
*************************** 1. row ***************************
id: e42f44b04dabbb9789ccb4709278e881c54c28a3
link: http://tetellita.blogspot.com/
title: le hamburger et le croissant
feed_url: http://www.blogger.com/feeds/7360650/posts/default
update_time: 2012-05-15 14:50:53
state: ok
http_server: GSE
abstract: Lepekmezest un épais sirop bordeaux obtenu par réduction dumoût de raisin, une sorte de mélasse de raisin, en somme. Légèrement acidulé, il apporte du pep's aux yaourts et nappe avec bonheur les
previous_id: 129cabd96e7099a53b78c7ddeff98658351082e9
ref_count: 9
error: NULL
aid: 174262
count_level(u.id): 8
1 row in set (4.10 sec)
好吧，悲剧了！4100ms。一定对12000个条目都算了一次count_level，然后再进行排序。所以才用上了4秒那么漫长的时间！！！
改进方法：
先SELECT LIMIT，再在派生的临时表里，计算count_level。
mysql> SELECT u.*, count_level(u.id) FROM (
SELECT id FROM feed_urls ORDER BY ref_count DESC LIMIT 27521,1
) d JOIN feed_urls u ON u.id=d.id\G
*************************** 1. row ***************************
id: 61df288dda131ffd6125452d20ad0648f38abafd
link: http://mynokiamobile.org/
title: My Nokia Mobile
feed_url: http://mynokiamobile.org/feed/
update_time: 2012-05-14 14:06:57
state: ok
http_server: Apache/2.2.19 (Unix) mod_ssl/2.2.19 OpenSSL/1.0.0-fips mod_auth_passthrough/2.1 mod_bwlimited/1.4 FrontPage/5.0.2.2635
abstract: ArchivesSelect MonthMay 2012April 2012March 2012February 2012January 2012December 2011November 2011October 2011September 2011August 2011July 2011June 2011May 2011April 2011March 2011February 2011Janua
previous_id: f37af92bb89c08f6d4b69e72eab05d8ab1e2aca4
ref_count: 5
error: NULL
aid: 154996
count_level(u.id): 8
1 row in set (0.09 sec)
如此，优化之后效果好很多了！但是还可以继续优化，例如建立一个字段存储Level的值应该是最好的办法了。
初次了解MySQL一些工作机制，欢迎一起探讨！
参考文献：
http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/
相关 [mysql 数据库 优化] 推荐：
浅谈MySQL 数据库性能优化
- - BlogJava-qileilove数据库是 IO 密集型的程序，和其他数据库一样，主要功能就是数据的持久化以及数据的管理. 本文侧重通过优化MySQL 数据库缓存参数如查询缓存，表缓存，. 日志缓存，索引缓存，innodb缓存，插入缓存，以及连接参数等方式来对MySQL数据库进行优化. 这里先引用一句话，从内存中读取一个数据的时间消耗是微秒级别，而从普通硬盘上读取一个数据是在毫秒级别，二者相差3个数量级.
MySQL数据库优化实践
- - OurMySQL 最近一段时间，我们整理了一些关于Percona，Linux，Flashcache，硬件设备的优化经验，分享给大家：. 1.开启BBWC. RAID卡都有写cache(Battery Backed Write Cache)，写cache对IO性能的提升非常明显，因为掉电会丢失数据，所以必须由电池提供支持.
MySQL数据库优化二三事
- -平时在开发新项目时，有时因为工期紧张，经常会以实现功能为目标，不太注意效率问题，特别是在SQL语句上. 简单来说是加索引，重建结构，杀进程，杀DBA……如果在一个没有DBA的公司，上线一时爽，事后火葬场，卑微测试一不小心背黑锅. 测试人员也会和数据打交道，今天总结数据库的优化知识. 主要介绍可以从哪些方面优化数据库，提高数据库的执行效率.
MySQL 数据库性能优化之缓存参数优化
- flychen50 - Sky.Jian 朝阳的天空在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题，所以最近打算写一个MySQL数据库性能优化方面的系列文章，希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣的朋友们有所帮助. 这是 MySQL数据库性能优化专题 系列的第一篇文章：MySQL 数据库性能优化之缓存参数优化.
MySQL 数据库性能优化之SQL优化
- - OurMySQL注：这篇文章是以 MySQL 为背景，很多内容同时适用于其他关系型数据库，需要有一些索引知识为基础. IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是. SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段.
MySQL数据库性能优化之表结构优化
- - haohtml's blog由于MySQL数据库是基于行（Row）存储的数据库，而数据库操作 IO 的时候是以 page（block）的方式，也就是说，如果我们每条记录所占用的空间量减小，就会使每个page中可存放的数据行数增大，那么每次 IO 可访问的行数也就增多了. 反过来说，处理相同行数的数据，需要访问的 page 就会减少，也就是 IO 操作次数降低，直接提升性能.
MySQL优化之数据库结构：数据对象优化
- - CSDN博客数据库推荐文章使用PROCEDURE ANALYSE函数优化表的数据类型. 表需要使用何种数据类型，是需要根据应用来判断的. 在MySQL中，可以使用函数PROCEDURE ANALYSE()对当前应用的表进行分析，该函数可以对数据库中列的数据类型提出优化建议，用户可以根据应用的实际情况斟酌考虑是否实施优化. 以下是函数PROCEDURE ANALYSE()的使用方法：.
mysql数据库性能优化的关键参数及mysql服务器优化
- - CSDN博客数据库推荐文章MySQL数据库性能优化的关键参数. 关键参数一： back_log. 要求 MySQL 能有的连接数量. 当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程. back_log 值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中.
数据库优化：mysql数据库单机数十亿数据查询设计
- - Seay's blog 网络安全博客 很久没写文章，是不是想着写点什么东西，分享下我的数据库设计思路，主要是针对单机数十亿及以上数据查询优化技巧. 如果只是简单的查询，没有频繁的写入操作，对查询速度不要求在毫秒级别，就不需要什么大型的数据库软件设计复杂的集群关系，也不需要分布式水平分割等太重的优化. 只需要用mysql在本机笔记本搭建一个普通的环境就行.