一、MySQL 如何高效率随机获取N条数据?
答案:
假设表叫做mm_account。
ID连续的情况下(注意不能带where,否则结果不好):
SELECT *
FROM `mm_account` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `mm_account`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 4;
ID不连续的情况下:
SELECT * FROM `mm_account`
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `mm_account`))) and city="city_91" and showSex=1
ORDER BY id LIMIT 4;
如果有一个字段叫id,最快的方法如下(随机获取5条):
SELECT * FROM mm_account
WHERE id >= ((SELECT MAX(id) FROM mm_account)-(SELECT MIN(id) FROM mm_account)) * RAND() + (SELECT MIN(id) FROM mm_account)
limit 5;
如果带where语句,上面就不适合了,带where语句请看下面:
SELECT *
FROM `mm_account` AS t1 JOIN (SELECT ROUND(RAND() * (
(SELECT MAX(id) FROM `mm_account` where id<1000 )-(SELECT MIN(id) FROM `mm_account` where id<1000 ))+(SELECT MIN(id)
FROM `mm_account` where id<1000 )) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 5;
二、MySQL 简述聚簇索引和非聚簇索引
答案:
聚簇索引的叶节点就是数据节点,而非聚簇索引的叶节点仍然是索引节点,并保留一个链接指向对应数据块。
MyISAM的是非聚簇索引,B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。
InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上
MySQL InnoDB一定会建立聚簇索引,把实际数据行和相关的键值保存在一块,这也决定了一个表只能有一个聚簇索引
1.InnoDB通常根据主键值(primary key)进行聚簇
2.如果没有创建主键,则会用一个唯一且不为空的索引列做为主键,成为此表的聚簇索引
3.上面二个条件都不满足,InnoDB会自己创建一个虚拟的聚集索引
聚簇索引的
优点:就是提高数据访问性能。
缺点:维护索引很昂贵,特别是插入新行或者主键被更新导至要分页(page split)的时候。
三、MySQL 有哪些索引类型
答案:
- 1.主键索引
- 2.普通索引
- 3.唯一索引
- 4.全文索引
四、MySQL 有哪些存储引擎?
答案:
1.InnoDB:默认存储引擎,使用最广泛。
2.MyISAM:表锁,不支持事务。
3.Archive:适合日志和数据采集类应用。
4.Memory:适合访问速度快,数据丢失也没有关系的场景。
5.CSV:将普通csv保存再MySQL中,主要用于数据交换。
五、MySQL 悲观锁和乐观锁是什么?
答案:
悲观锁和乐观锁都是为保证一致性的一种锁。
一、悲观锁
使用:
- 1.关闭autocommit=0;
- 2.在事务中使用 select .. from … where … for update;给行加排他锁
- 3.select命中的行必须有索引,否则会锁表
优点:
- 1.保守策略,所以数据安全性高
缺点:
- 1.有加锁等额外开销,效率低
- 2.可能引起死锁
- 3.降低并行行,数据被锁住后其他事物必须等待
二、乐观锁
使用:
- 1.表中增加版本号或时间戳数据列
- 2.读取数据是同时读取版本号
- 3.更新数据时添加版本号为条件,同时版本号增加1
- 4.如果更更新失败,提示用户
优点:
- 1.没有锁,效率高
- 2.不会引起死锁
缺点:
- 1.这里是列表文本遇到两个事务统一时间读取一行数据时,会引起问题
六、MySQL InnoDB和MyISAM存储引擎有什么区别?
答案:
1、存储结构
MyISAM:每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB:所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。
2、存储空间
MyISAM:可被压缩,存储空间较小。支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。
InnoDB:需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
3、可移植性、备份及恢复
MyISAM:数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB:免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。
4、事务支持
MyISAM:强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持。
InnoDB:提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
5、AUTO_INCREMENT
MyISAM:可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。
InnoDB:InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引,如果是组合索引也必须是组合索引的第一列。
6、表锁差异
MyISAM:只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。
InnoDB:支持事务和行级锁,是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的。
7、全文索引
MyISAM:支持 FULLTEXT类型的全文索引
InnoDB:5.7之后支持FULLTEXT类型的全文索引
8、表主键
MyISAM:允许没有任何索引和主键的表存在,索引都是保存行的地址。
InnoDB:如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。
9、表的具体行数
MyISAM:保存有表的总行数,如果select count(*) from table;会直接取出出该值。
InnoDB:没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样。
10、CURD操作
MyISAM:如果执行大量的SELECT,MyISAM是更好的选择。
InnoDB:如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。DELETE 从性能上InnoDB更优,但DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除,在innodb上如果要清空保存有大量数据的表,最好使用truncate table这个命令。
11、外键
MyISAM:不支持
InnoDB:支持
12、崩溃自动恢复
MyISAM:不支持
InnoDB:支持