1、MySQL索引使用注意事项 区分度 = 用到索引的记录数 / 总数 (select count(*) from a where idx = xxxx / select count(*)) 可以从几个维度去看这个问题,查询是否够快,效率是否稳定,存储数据多少,以及查找磁盘次数,为什么不是二叉树,为什么不是平衡二叉树,为什么不是B树,而偏偏是B+树呢? 为什么不是一般二叉树? 如果二叉树特殊化为一个链表,相当于全表扫描。平衡二叉树相比于二叉查找树来说,查找效率更稳定,总体的查找速度也更快。 为什么不是平衡二叉树呢? 我们知道,在内存比在磁盘的数据,查询效率快得多。如果树这种数据结构作为索引,那我们每查找一次数据就需要从磁盘中读取一个节点,也就是我们说的一个磁盘块,但是平衡二叉树可是每个节点只存储一个键值和数据的,如果是B树,可以存储更多的节点数据,树的高度也会降低,因此读取磁盘的次数就降下来啦,查询效率就快啦。 那为什么不是B树而是B+树呢? 1)B+树非叶子节点上是不存储数据的,仅存储键值,而B树节点中不仅存储键值,也会存储数据。innodb中页的默认大小是16KB,如果不存储数据,那么就会存储更多的键值,相应的树的阶数(节点的子节点树)就会更大,树就会更矮更胖,如此一来我们查找数据进行磁盘的IO次数有会再次减少,数据查询的效率也会更快。 2)B+树索引的所有数据均存储在叶子节点,而且数据是按照顺序排列的,链表连着的。那么B+树使得范围查找,排序查找,分组查找以及去重查找变得异常简单。 方案一:如果id是连续的,可以这样,返回上次查询的最大记录(偏移量),再往下limit 方案二:order by + 索引(id为索引) 方案三:利用延迟关联或者子查询优化超多分页场景。(先快速定位需要获取的id段,然后再关联) 复合索引,也叫组合索引,用户可以在多个列上建立索引,这种索引叫做复合索引。 当创建一个组合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。 举例说明下吧: 假设表A表示某企业的员工表,表B表示部门表,查询所有部门的所有员工,很容易有以下SQL: 等价于: 先查询部门表B select deptId from B 再由部门deptId,查询A的员工 select * from A where A.deptId = B.deptId 显然,除了使用in,我们也可以用exists实现一样的查询功能,如下: 因为exists查询的理解就是,先执行主查询,获得数据后,再放到子查询中做条件验证,根据验证结果(true或者false),来决定主查询的数据结果是否得意保留。 执行相当于 : 结论: 数据库最费劲的就是跟程序链接释放。假设链接了两次,每次做上百万次的数据集查询,查完就走,这样就只做了两次;相反建立了上百万次链接,申请链接释放反复重复,这样系统就受不了了。 即mysql优化原则,就是小表驱动大表,小的数据集驱动大的数据集,从而让性能更优。 因此,我们要选择最外层循环小的,也就是,如果B的数据量小于A,适合使用in,如果B的数据量大于A,即适合选择exists,这就是in和exists的区别 Q:没明白为什么要先删除索引。。。。我的理解,先删除,不就锁表了吗? 还有一种删除思路: delete truncate drop 类型 DML DDL DDL 回滚 可回滚 不可回滚 不可回滚 删除内容 表结构还在,删除表的全部或者一部分数据行 表结构还在,删除表中的所有数据 从数据库中删除表,所有的数据行,索引和权限也会被删除 删除速度 删除速度慢,逐行删除 删除速度快 删除速度最快 图片上传失败。。 排查过程: 处理: 主从复制原理,简言之,就三步曲,如下: 图片上传失败。。 上图主从复制分了五个步骤进行: 步骤一:主库的更新事件(update、insert、delete)被写到binlog 步骤二:从库发起连接,连接到主库。 步骤三:此时主库创建一个binlog dump thread,把binlog的内容发送到从库。 步骤四:从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log 步骤五:还会创建一个SQL线程,从relay log里面读取内容,从ExecMasterLog_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db Explain 执行计划包含字段信息如下:分别是 id、selecttype、table、partitions、type、possiblekeys、key、key_len、ref、rows、filtered、Extra 等12个字段。 重点关注的是type,它的属性排序如下: system > const > eq_ref > ref > ref_or_null >index_merge > unique_subquery > index_subquery >range > index > ALL innodb两种日志redo和undo 日志的存放形式 事务是如何通过日志来实现的 18、数据库是否支持emoji表情存储,如果不支持,如何操作 更换字符集utf8→utf8mb4 (5.6以上的版本才支持) 19、一个6亿的表a,一个3亿的表b,通过外间tid关联,你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录 1、如果A表TID是自增长,并且是连续的,B表的ID为索引 2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。 关注不迷路~持续输出~ 1.1、 索引哪些情况会失效
1.2 、索引不适合哪些场景
2、InnoDB与MyISAM的区别
3、数据库索引的原理,为什么要用B+树,为什么不用二叉树
4、limit 1000000加载很慢的话,怎么解决的呢
连续id:
select id,name from employee where id>1000000 limit 10
order by 主键:
select id,name from employee order by id limit 1000000,10
联表查询:
SELECT a.* FROM employee a, (select id from employee where 条件 LIMIT 1000000,10 ) b where a.id=b.id
5、如何写sql能够有效的使用到复合索引
联合索引:
select * from table where k1=A AND k2=B AND k3=D
6、mysql中in 和exists的区别
in 操作:
select * from A where deptId in (select deptId from B);
exists查询:
select * from A where exists (select 1 from B where A.deptId = B.deptId);
select * from A,先从A表做循环
select * from B where A.deptId = B.deptId,再从B表做循环.
exists 与 in 操作正好相反。。。。。7、创建索引有什么原则
8、百万级别或以上的数据,如何删除
删除百万数据:
create table test_a like test_b; 会保留索引相关的信息
create table test_a as test_b; 不推荐
insert into test_a select * from test_b where id >0 and id<50000 # 50000条数据导一次
最后
rename table test_b to test_b_bak 备份
rename table test_a to test_b
9、覆盖索引、回表
10、B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据
11、drop、delete与truncate的区别
12、UNION与UNION ALL的区别
13、Sql的执行顺序
14.MySQL数据库cpu飙升,怎么处理
15 .MySQL的复制原理以及流程
16、MySQL的Explain
17、Innodb的事务与日志的实现方式
大数据快速查询01:
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
大数据快速查询02:
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;
20、Innodb的事务实现原理