Database

MySQL使用limit条件时要注意offset不能太大。 这样还可以： select * from user order by user_name limit 100, 20 这样就不好了： select * from user order by user_name limit 10000, 20 这条语句实际上是把 1-10020行都取出来，然后再丢掉前10000条记录，最后再返回剩下的20条记录。所以代价会非常高。 如果数据库是innodb且user_name上有索引的话，可以把sql改写成这样来优化一下： select * from user join (select id from user order by user_name limit 10000,20) as lim on user.id = lim.id 这里仍然会扫描1万多次，但扫描的不是data rows, 而是user_name上的index(并且是covering index) ; 扫完索引之后，再根据扫出的id把20行数据取出来。也就是说，磁盘读会小很多，速度也会快很多。

limit条件不会影响MySQL Explain Plan中的rows结果。比如， explain select * from t limit 10 输出的rows值可能是1百万(如果t中有1百万数据)。 但实际上，MySQL不会为这十行数据扫描整张表的。 Peter Zaitsev说： LIMIT is not taken into account while estimating number of rows Even if you have LIMIT which restricts how many rows will be examined MySQL will still print full number 如果不知道这个潜规则，会被explain plan迷惑死。这是典型的MySQL制造者的懒惰给使用者造成的麻烦。

count(*), count(1), count(someColumn) 倒底有没有分别？ 使用count(someColumn)只取一列，会不会性能好点？ 如果是这样的话，那count(1)是不是性能最佳？ 答案： 1. count(*)和count(1)完全是一码事：都是用来数“行数”的。MySQL遇到"*", 并不会去展开它。在性能方面，这类查询会走covering index.  2. count(someColumn)跟其他完全不同，它数的是someColumn列中非NULL值的个数。在性能方面，系统会根据someColumn有没有索引，来决定走不走covering index. 例： drop table if exists play_count; create table play_count( id bigint auto_increment not null, indexed_column varchar(50), –有索引列 non_indexed_column varchar(50), –无索引列 primary key(id), key idx(indexed_column) ); –插入三行数据，存在空值 insert into play_count(indexed_column,non_indexed_column) value(‘a’,null); insert into play_count(indexed_column,non_indexed_column) value(null,’b’); insert into play_count(indexed_column,non_indexed_column) value(‘c’,’c’); –count查询 select count(*) …

MySQL: count()的坑 Read More »

1.查询出了过多数据。 比如只需查某列数据，应用却要求每行都返回所有字段。 2.数据库扫描了过多数据，虽然不一定全部返回。 如：只需查一行数据，由于没有索引导致全表扫描。

有的数据库支持游标。你输入一个select * from t, 拿到一个ResultSet对象，要求取10行，数据库就临时给你找出10行；再取下一个10行，数据库再给你查询出10行。 MySQL不支持这种机制。你输入select * from t, MySQL就会把所有数据都查出来再给你。在全部查完之前，根本停不下来。这就是 "limit"关键字非常重要的原因。

Using Where意思是 扫描(examine)数据后扔掉了一部分，只返回剩下的。常见的例子是根据无索引的字段进行搜索。 select * from user where description = ‘to be a better man’ 这时MySQL必须把所有数据找出来，然后“使用Where条件”判断一下description是不是"to be a better man"; 等找到之后再把不符合条件的扔掉。

对于高并发应用中的MySQL,按照我的经验，尽量不要表连接；MySQL Optimizer在处理表连接时有时很不智能，搞出个大坑。比如这个。 在需要表连接的场景，尽量把它拆解掉，这就叫做 Join Decomposition. 个人常用的办法有两个： 1.冗余 2.分成两步执行： 先查出符合条件的A表的ID，再根据这些ID使用IN字句去B表中查询。 消除表连接还有个附加好处是两个表不一定要放在同一个库里，这样你做垂直分库会比较自由。

不要以为数据库只有在执行select语句时才会返回数据。对于所有SQL，服务端都会回送数据（或者称响应更适合一些）。这是协议规定的。 举个例子，对于增删改语句，服务端都会返回响应告知受影响的行数。

一个红人发了一个帖子，每秒对这个帖子的“赞”数可能会很高。如果这些赞请求都落到数据库中的同一条记录上，会导致对同一条记录过高的临界访问（因为有锁），导致排队。 解决办法是把这1条记录拆成100条记录，更新赞数时可能会更新到其中任意一条记录上。这样并发访问的竞争就会松很多。当需要显示总赞数时，临时把100条记录的总赞数累加起来即可。 对于NoSQL数据库和关系数据库，实现这种分槽(slot)机制都不难。对于MySQL数据库，最简单的做法是： insert into post_like_counter(post_id, slot, like_count) values (100, rand() * 100, 1) on duplicate key update like_count = like_count + 1 其中slot是槽号(1-100).  <post_id, slot>组成一个唯一键。

看下下面这条语句的explain plan  （user_name已被索引) select user_name from user where user_name = ‘chenjianjx’ 你会发现 Extra这一列中写的是：Using Index 它的意思执行查询时用到了Covering Index.  Covering Index的意思是指索引中直接包含了一个查询所需返回的数据。按user_name只查user_name, 所有数据都可以从user_name的索引中给出。 很明显，这对性能有帮助，因为你不需要再费几次I/O去取数据； 同时由于索引本身排好了序，体积小更容易直接存储在内存中，所以只查索引，性能上的好处是很大的。 如果查整行， select * from user where user_name = ‘chenjianjx’ Extra中就不再有"Using Index"了，因为你的索引中本身没有存储整行。 如果你的存储引擎是Innodb, 这样查呢？ select id, user_name from user where user_name = ‘chenjianjx’ — id是主键 Extra中又会有"Using Index", 这是因为在innodb中，所有secondary index中的结点中都包含了相应数据对应的主键的值。 以上结论的作用是：不要总是傻傻地返回整行( select *) . 如果你直需要索引中的数据，或者索引中的数据加上主键(只对innodb有效)，那就让你的sql只返回索引字段吧(以及主键）。