一、索引
本文以mySQL为例。
使用索引是最常见的优化方式,在千万级数据库以下,索引能大幅度提升查询效率。
这里介绍一下索引使用的几个注意点。
先介绍一下几种常见的索引种类。
Primary key
主键索引(所有的主键都是主键索引,唯一,不为空)
Unique
唯一索引(设置为该索引后唯一)
Index
普通索引(仅表示该字段设置为索引)
Index
(id,username) 联合索引(将多个字段设置为索引,同时查找这几个字段时触发索引)
Full text
全文索引
1、索引有“最左匹配原则”
在模糊查询中,如果出现左模糊和全模糊,则不会命中索引。
即左边有‘%’时,不命中索引。
//命中索引
SELECT id FROM user WHERE username LIKE "aa%";
//未命中索引
SELECT id FROM user WHERE username LIKE "%aa";
//未命中索引
SELECT id FROM user WHERE username LIKE "%aa%";
在联合索引中,左边的字段命中索引。
我们假设只设置了Index(id,username,iphone)
的联合索引。
如果查询条件只出现了左边的的字段一样可以命中索引。
但如果左边的字段没出现,却出现了右边的字段则没有命中索引。
//名中索引
SELECT * FROM user WHERE id='001' AND username='tom' AND iphone='110';
//名中索引
SELECT * FROM user WHERE id='001' AND username='tom';
//名中索引
SELECT * FROM user WHERE id='001';
//未命中索引
SELECT * FROM user WHERE username='tom' AND iphone='110';
//未命中索引
SELECT * FROM user WHERE id='001' AND iphone='110';
//未命中索引
SELECT * FROM user WHERE iphone='110';
2、其他无法命中索引的操作
WHERE子句中对字段进行表达式操作,函数操作等操作时无法名中索引
//未名中索引
SELECT id FROM user WHERE number/2 = 100;
//未名中索引
SELECT id FROM user WHERE substring(name,1,3) = 'abc';
//将运算放在‘=’右边后,命中索引
SELECT id FROM user WHERE number = 100*2;
使用OR连接条件时,若其中一个条件未设置索引则不会命中索引。
//如果id和phone中有一个未设置索引,则不命中索引。
SELECT * FROM user WHERE id='001' OR phone='110';
判断一个字段是否不为NULL时,不会命中索引。
判断一个字段是否为NULL时,命中索引
//未命中索引
SELECT id FROM user WHERE phone IS NOT NULL;
//命中索引
SELECT id FROM user WHERE phone IS NULL;
使用NOT IN时不会命中索引
//未命中索引
SELECT * FROM user WHERE id NOT IN(1,2,3);
//命中索引
SELECT * FROM user WHERE id IN(1,2,3);
索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引。
推荐一篇不错的数据库优化博客。
数据库SQL优化大总结1之- 百万级数据库优化方案
二、exists和in的选择
经常听到一些说法,说in的效率没有exists高,应该用exists替换掉in子句。但实际上并不是这样的,我们应该根据实际情况进行选择。
我们假设一个情况,一张学生表student和一张寝室表dorm。
假设我们要查找所有寝室在A区的学生id,这里section字段未设置索引。
select id from student where dorm_id in (select id from dorm where section='A');
select id from student where exists(select null from dorm where id=dorm_id and section='A');
看上面两个sql语句,做同一件事,一个使用的是in一个使用exists。
第一条语句中,外表可以使用了dorm _id的索引,内表没使用索引。
第二条语句中,外表没有使用索引,内表可以使用id的索引。
因此,当内表很大时,第一条语句没有使用索引效率低下,第二条语句使用索引就可以提高效率。
而当内表不大,反而外表很大时,第一条语句就具有优势。
所有,使用exists时,就是在内表使用索引。使用in时,就是在外表使用索引。
总结:IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
详细请看这篇博客。
Exists和in的效率哪个高
三、分库分表
当数据库数据达到千万级开始,索引的作用就越来越差了,为了提高效率,程序员们想到了拆分数据的方法。
1、水平分表
当一个表数据过多时,查询效率自然变慢,为了提高查询效率我们可以将表A拆分成表A1,A2,A3…字段都是一样的,将数据以一定规律均匀分布在多个表中,提高数据库操作效率。
2、垂直分表
垂直分表是指,有些表字段很多,我们可以将一些不常用的,比较长的字段拆分出来,做成一个新表,即‘大表拆小表’,便于开发与维护。
分表说到底还是在一个数据库中操作,没有解决数据库连接不够的问题,数据库瓶颈依然存在,因此我们进行分库操作。
3、水平分库
水平分库,就是不将所有的表放在一个数据库中,比如好友模块的表放在一个数据库中,另一些交易模块的表放在另一个数据库中。这样就实现了高内聚低耦合,现如今的微服务框架就是水平分库的应用。
4、垂直分库
垂直分库其实就是集群的概念,将数据库A拆分成A1,A2,A3…里面的构造都是一样的,只把数据均匀分散在多个数据库中,以提高数据库操作效率。
同时分库分表也会带来很多新的问题,比如说事务变得更加复杂,全局主键避免重复等等。
详细请看这篇博客。
数据库分库分表思路
(未完待续)