mysql下小内存大数据量系统索引问题的讨论
时间：2010-03-09 来源：pkman110
tOny 说: (1分钟前)
现在又个1000w 左右的商品表，80G 数据，index 800多MB tOny 说: (1分钟前)
mysql key_buffer_size 只分配了512MB tOny 说: (1分钟前)
Key_read_requests | 1171481240 |
| Key_reads | 21467675
tOny 说:
索引太大了
Saver 说:
这个是索引太大了？还是索引优化不够呢？
[email protected] 说:
命中率也太低了吧
isql 说:
你想partition？
是啊
Saver 说:
嗯，我也是觉得命中率贼低
isql 说:
百分之二？
tOny 说:
| tb_goods | 1 | cid_total | 1 | cid | A | 11856 | NULL | NULL | | BTREE | NULL |
| tb_goods | 1 | cid_total | 2 | lmsn | A | 186052 | NULL | NULL | | BTREE | NULL |
| tb_goods | 1 | key_total | 1 | cid | A | 11856 | NULL | NULL |
服务器只有2G内存，还跑java程序
Saver 说:
命中率要看你的查询吧。。。
光给了索引，咱也不知道你跑得查询啥样
tOny 说:
indexa( varchar(10),int(11) ) 这个的key_len 是32太大了
查询都是用了索引
Saver 说:
查询explain下看看
索引看看能不能优化下
tOny 说:
mysql> explain select iid,title,pic_path,price from tb_goods where nick='zhenghaimiao'
-> order by lmsn desc
-> limit 360,20;
+----+-------------+----------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
Saver 说:
你要是都走了索引，咋命中率这么低呢？
tOny 说:
1 | SIMPLE | tb_goods | ref | nickgood | nickgood | 32 | const | 39 | Using where
[email protected] 说:
走索引和命中率低没有关系的
tOny 说:
现在的问题 是 ，slow query 很多
[email protected] 说:
索引太大，内存太小，照样要从磁盘上取索引数据
tOny 说:
是啊
这个我 猜是主要原因
我用explain 看，索引影响的行数 也不是很多，如果是在buffer中，不io索引，速度肯定飞快
看来，我要把varchar(10) 改小点了 varchar(5)？
isql 说:
查询其他字段还是要到磁盘上
业务需求允许改小吗？
tOny 说:
如果 我把varchar 改成 char 好像 索引没变小
业务上有个 用了复合索引的查询
[email protected] 说:
没用的
isql 说:
varchar 5+字符长度位
char 5
好像是这样
[email protected] 说:
char会更大的
isql 说:
？
tOny 说:
恩。我昨天试了，char 居然索引更大了
isql 说:
为什么？realzyy?
timothy 说:
如何看索引大小。
tOny 说:
show table status like 'tb_name'
[email protected] 说:
char会自动补充的
isql 说:
哦
想起来了
每个都是5 tOny 说:
1000w 级的 表，改了索引，太慢了，而且是线上数据库
Saver 说:
线上库的话，你也不好做切分了
tOny 说:
server 上的cpu 很低，一般只有4%左右，httpd 也没占什么资源
vmstat 1看，好像io 有点压力
rocs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 5 797716 53812 57520 1046032 0 0 3820 0 1464 1291 3 1 71 25 0
0 6 797716 52248 57204 1045284 0 0 4364 0 1802 1416 2 1 68 29 0
0 4 797716 52520 57152 1042612 0 0 3304 320 1756 1493 2 1 7
Saver 说:
b值好高
tOny 说:
io,中的i是读吧？ 一般都几千的数值
b值，是等待中的进程？
确实 基本没什么空闲，读文件的压力 太大
Saver 说:
•Procs
–r:
运行的和等待(CPU时间片)运行的进程数，这个值也可以判断是否需要增加CPU(长期大于1)
–b:
处于不可中断状态的进程数，常见的情况是由IO引起的 tOny 说:
cpu 占用 是很低的，主要 我觉得还是要读取 太多的索引
走文件 太慢
谁 给点建议？
要把 varchar (10) 索引 改小点，然后把key_buffer_size 加大点? 还要其他什么办法吗？
Saver 说:
nickgood | 32
这个索引32？
这么长？
tOny 说:
恩，太大了
Saver 说:
不能截短么？
tOny 说:
nickgood (varchar(10),int 11) ,我现在想就是把varchar(10) 改小点，确实太大了，业务上可能Cardinality 达到90%就行了
isql 说:
看看前缀索引能不能满足业务需求
选择性命中率什么的和原来差不多的话可以考虑
tOny 说:
现在 就是前缀索引啊，原字段是varchar( 60),索引 我只是varchar(10)
isql 说:
不好意思，没看到这个
tOny 说:
索引太大了，机器跟不上，麻烦啊
Saver 说:
做个分区？
不过看你这个估计希望用nickgood分区
够呛
tOny 说:
怎么分法？现在的mysql 版本是5.0.22
Saver 说:
那不考虑分区了
merge引擎考虑下？
tOny 说:
goods 表上主要 有2个复合 索引太大，还要一个是3个字段的索引，int 11,varchar(10),int 11,这样的
merge引擎 分表？
Saver 说:
嗯
要不建立冗余表，来做独立查询，主表上只保留primaryKEY的index
不过这么干就要调整业务逻辑了
现在又个1000w 左右的商品表，80G 数据，index 800多MB tOny 说: (1分钟前)
mysql key_buffer_size 只分配了512MB tOny 说: (1分钟前)
Key_read_requests | 1171481240 |
| Key_reads | 21467675
tOny 说:
索引太大了
Saver 说:
这个是索引太大了？还是索引优化不够呢？
[email protected] 说:
命中率也太低了吧
isql 说:
你想partition？
是啊
Saver 说:
嗯，我也是觉得命中率贼低
isql 说:
百分之二？
tOny 说:
| tb_goods | 1 | cid_total | 1 | cid | A | 11856 | NULL | NULL | | BTREE | NULL |
| tb_goods | 1 | cid_total | 2 | lmsn | A | 186052 | NULL | NULL | | BTREE | NULL |
| tb_goods | 1 | key_total | 1 | cid | A | 11856 | NULL | NULL |
服务器只有2G内存，还跑java程序
Saver 说:
命中率要看你的查询吧。。。
光给了索引，咱也不知道你跑得查询啥样
tOny 说:
indexa( varchar(10),int(11) ) 这个的key_len 是32太大了
查询都是用了索引
Saver 说:
查询explain下看看
索引看看能不能优化下
tOny 说:
mysql> explain select iid,title,pic_path,price from tb_goods where nick='zhenghaimiao'
-> order by lmsn desc
-> limit 360,20;
+----+-------------+----------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
Saver 说:
你要是都走了索引，咋命中率这么低呢？
tOny 说:
1 | SIMPLE | tb_goods | ref | nickgood | nickgood | 32 | const | 39 | Using where
[email protected] 说:
走索引和命中率低没有关系的
tOny 说:
现在的问题 是 ，slow query 很多
[email protected] 说:
索引太大，内存太小，照样要从磁盘上取索引数据
tOny 说:
是啊
这个我 猜是主要原因
我用explain 看，索引影响的行数 也不是很多，如果是在buffer中，不io索引，速度肯定飞快
看来，我要把varchar(10) 改小点了 varchar(5)？
isql 说:
查询其他字段还是要到磁盘上
业务需求允许改小吗？
tOny 说:
如果 我把varchar 改成 char 好像 索引没变小
业务上有个 用了复合索引的查询
[email protected] 说:
没用的
isql 说:
varchar 5+字符长度位
char 5
好像是这样
[email protected] 说:
char会更大的
isql 说:
？
tOny 说:
恩。我昨天试了，char 居然索引更大了
isql 说:
为什么？realzyy?
timothy 说:
如何看索引大小。
tOny 说:
show table status like 'tb_name'
[email protected] 说:
char会自动补充的
isql 说:
哦
想起来了
每个都是5 tOny 说:
1000w 级的 表，改了索引，太慢了，而且是线上数据库
Saver 说:
线上库的话，你也不好做切分了
tOny 说:
server 上的cpu 很低，一般只有4%左右，httpd 也没占什么资源
vmstat 1看，好像io 有点压力
rocs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 5 797716 53812 57520 1046032 0 0 3820 0 1464 1291 3 1 71 25 0
0 6 797716 52248 57204 1045284 0 0 4364 0 1802 1416 2 1 68 29 0
0 4 797716 52520 57152 1042612 0 0 3304 320 1756 1493 2 1 7
Saver 说:
b值好高
tOny 说:
io,中的i是读吧？ 一般都几千的数值
b值，是等待中的进程？
确实 基本没什么空闲，读文件的压力 太大
Saver 说:
•Procs
–r:
运行的和等待(CPU时间片)运行的进程数，这个值也可以判断是否需要增加CPU(长期大于1)
–b:
处于不可中断状态的进程数，常见的情况是由IO引起的 tOny 说:
cpu 占用 是很低的，主要 我觉得还是要读取 太多的索引
走文件 太慢
谁 给点建议？
要把 varchar (10) 索引 改小点，然后把key_buffer_size 加大点? 还要其他什么办法吗？
Saver 说:
nickgood | 32
这个索引32？
这么长？
tOny 说:
恩，太大了
Saver 说:
不能截短么？
tOny 说:
nickgood (varchar(10),int 11) ,我现在想就是把varchar(10) 改小点，确实太大了，业务上可能Cardinality 达到90%就行了
isql 说:
看看前缀索引能不能满足业务需求
选择性命中率什么的和原来差不多的话可以考虑
tOny 说:
现在 就是前缀索引啊，原字段是varchar( 60),索引 我只是varchar(10)
isql 说:
不好意思，没看到这个
tOny 说:
索引太大了，机器跟不上，麻烦啊
Saver 说:
做个分区？
不过看你这个估计希望用nickgood分区
够呛
tOny 说:
怎么分法？现在的mysql 版本是5.0.22
Saver 说:
那不考虑分区了
merge引擎考虑下？
tOny 说:
goods 表上主要 有2个复合 索引太大，还要一个是3个字段的索引，int 11,varchar(10),int 11,这样的
merge引擎 分表？
Saver 说:
嗯
要不建立冗余表，来做独立查询，主表上只保留primaryKEY的index
不过这么干就要调整业务逻辑了
相关阅读 更多 +
