Archive

Archive for the ‘mysql’ Category

认识mysql前缀索引

March 25th, 2011 No comments
今天在测试环境中加一个索引时候发现一警告
root@test 07:57:52>alter table  article drop index ind_article_url;
Query OK, 144384 rows affected (16.29 sec)
Records:[……]

Read more

Categories: database, mysql, sql优化 Tags:

while creating a secondary index with plugin, queries are blocked until it ends

March 10th, 2011 No comments

董开同学说在测试innodb plugin创建非主键索引的时候,会阻塞查询,有点怀疑,但还是想测试一下

在Pulgin的官方文档中这样描述到:

While a secondary index is being created or dropped, the table is[……]

Read more

Categories: mysql Tags:

巧用information_schema

March 8th, 2011 No comments

在DBA的日常工作中可能会遇到这样的操作:一些应用下线时,将生产库中的表批量移到test库中;对数据库的的表进行批量optimize,analyze;批量删除某个用户的到数据库的connections等。这时候如果你的库中有成千上百的表,也行就有点麻烦了,时候需要借助于mysql的informati[……]

Read more

Categories: mysql Tags:

大数据分页的一种优化方法

November 23rd, 2010 No comments

通常应用需要对表中的数据进行翻页,如果数据量很大,往往会带来性能上的问题:

root@sns 07:16:25>select count(*) from  reply_0004 where thread_id = 5616385 and deleted = 0;
+———-+

[……]

Read more

Categories: mysql, sql优化 Tags:

mysql给同一个表添加多个索引的测试

October 25th, 2010 No comments

分别给xc表添加ind_name和ind_status的索引:

root@test 11:44:13>create index ind_name on xc(name);
Query OK, 6815744 rows affected (1 min 43.75 sec)
Record[……]

Read more

Categories: database, mysql Tags: