High Performance MySQL 笔录(schema/index 部分)

High Performance MySQL 笔录(schema/index 部分)

                                        时间：2010-6-3

1.uuid用binary保存
建议uuid不要使用char来保存，而用binary(16)来保存。这里在长度上来讲用binary会节省一半。因为一个字符占用1个字节，而一个字节实际上可以表示0-256(2^8)，用16进制的表示需要2个字节00-FF(0-256)。
优化前：SET uuid = UUID() (类型：char(36))
优化后：SET uuid = HEX(REPLACE(UUID(), '-', '')) (类型：binary(16))

笔记：这个东西主要是节省一下空间可以尝试试着用一下哦！

2.用crc32替换长字符串的查找
如果索引列是个很长的字符串，例如url。那可以再建立一个列用来保存这个列的crc32结果，以提高索引的使用速度。
优化前：WHERE url = 'http://willko.javaeye.com/' (索引：url，类型：var/char(?))
优化后：WHERE url_crc32 = CRC32('http://willko.javaeye.com/') AND url = 'http://willko.javaeye.com/' (索引：url_crc32，类型：unsigned int)
示例：

select crc32('http://www.google.cn') from server;

打印的是115600293       就是其做了crc32运算之后的结果了！

笔记与心得：如果以后我们要对一个大的文本类型的字段创建索引的话呢。我们可以再加进来一个字段做冗余处理这个字段的内容保存的为CRC32 的结果值。主要目的是为了用到索引

3.前缀索引和后缀索引
前缀索引听得比较多，优点是减少索引的长度，缺点是排序不能使用前缀索引(影响distinct/order/group)，也不会出现Covering Index(只读取索引就能满足查询)。
后缀索引还是首次听到，孤陋寡闻了。因为MySQL不支持反向索引，所有有时候查询会有问题，例如字段blog保存用户的博客地址 (http://willko.javaeye.com)，那需要查询某个域名有多少个用户就不好查询，可以用一个额外的字段反转保存。 blog_reverse:moc.eyeavaj.willko://ptth，这样就很容易查到javaeye.com(moc.eyeavaj)有 多少用户了，并可以使用索引，也就是解决了 LIKE '%?'的问题，因为查询反转成LIKE '?%'了。

4.散列数据
散列数据就是把原本只有一条记录的散列成多条，充分利用InnoDB行锁的特性，提高并发。
例如，之前是UPDATE hit_counter SET cnt = cnt + 1 WHERE id = ?
散列后是 UPDATE hit_counter SET cnt = cnt + 1 WHERE id = ? AND slot = rand() * 100
散列后查询需要合并数据。
笔记与心得：innodb是行锁。所以啊它在处理这种大的并发的时候是非常高效的。哦因为是行锁并发性能要高。所以在大的论坛的时候是用innodb哦！

另外我在有一篇文章里面有一个东西介绍统计count(*) 在处理统计innodb的时候很慢然后我新建一个表单独存放其总量。然后再做了一次散列原因就是为了提高并发处理能力

5.优化limit和offset
MySQL的limit工作原理就是先读取n条记录，然后抛弃前n条，读m条想要的，所以n越大，性能会越差。
优化前SQL: SELECT * FROM member ORDER BY last_active LIMIT 50,5
优化后SQL: SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)
分别在于，优化前的SQL需要更多I/O浪费，因为先读索引，再读数据，然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了，然后通过member_id读取需要的列。

笔记：这个我得要好好借鉴一下了。因为现在的表如果有超过近千万条记录的话。

SELECT * FROM member ORDER BY last_active LIMIT 50,5

读取的顺序是：

1、 先读索引（前提是你得创建索引 如member_id这个索引）

2、 再读数据（依据索引再读取数据这个是MSYQL优化器做的工作）

3、 然后因为limit 的原理是先读取n条记录再抛弃前n条所以这里面我们得将无需的数据丢弃掉。这样的话其实我们是浪费了大量的IO操作。

优化后的SQL：

SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)
子查询虽然被人骂但用得好还是比较爽的。这里面我们就使用了子查询来提高查询性能

1、 读索引SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5

这个跟第一个语句做的工作其实是一样的。

2、 依据索引读所要的数据（只读5条我们要的数据）

完成！

看到没有 优化之后的SQL语句不需要先把前面的全部数据提取出来，然后再丢弃不要的数据。而是依据索引直接定位。速度非常快！大大地减少了IO

学到了三招！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！