MySQL 中的索引优化
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺
点。虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE
和DELETE 次数大于查询次数时,放弃索引。因为更新表时,MySQL 不仅要保存数据,还要
保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但
如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。索引只是提高效率的一
个因素,如果你的MySQL 有大数据量的表,就需要花时间研究建立最优秀的索引,或优化
查询语句。
1、索引不会包含有NULL 值的列
只要列中包含有NULL 值都将不会被包含在索引中,组合索引中只要有一列含有NULL
值,那么这一列对于此组合索引就是无效的。所以我们在数据库设计时不要让字段的默认值
为NULL。create table table_name(c1 varchar(32) default ‘0’)
2、使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,
如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不
仅可以提高查询速度而且可以节省磁盘空间和I/O 操作。
CREATE INDEX index_name ON table_name (column(length))
3、索引列排序
MySQL 查询只使用一个索引,因此如果where 子句中已经使用了索引的话,那么order
by 中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操
作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
4、like 语句操作
一般情况下不鼓励使用like 操作,如果非使用不可,如何使用也是一个问题。like “%aaa%”
不会使用索引,而like “aaa%”可以使用索引。
5、不要在列上进行运算
例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导
致索引失效而进行全表扫描, 因此我们可以改成: select * from users where
adddate<’2007-01-01′
八、 索引总结
最后总结一下,MySQL 只对以下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时
候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16 个索引,不过除
非是数据量真的很多,否则过多的使用索引也不是那么好玩的。
建议:一个表的索引数最好不要超过6 个,若太多则应考虑一些不常使用到的列上建的
索引是否有必要。
九、MySQL 中的SQL 的常见优化策略
1、避免全表扫描
对查询进行优化,应尽量避免全表扫描,首先应考虑在where 及order by 涉及的列上
建立索引。
2、避免判断null 值
应尽量避免在where 子句中对字段进行null 值判断,否则将导致引擎放弃使用索引
而进行全表扫描,如:
select id from t where num is null
可以在num 上设置默认值0,确保表中num 列没有null 值,然后这样查询:
select id from t where num=0
3、避免不等值判断
应尽量避免在where 子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表
扫描。
4、避免使用or 逻辑
应尽量避免在where 子句中使用or 来连接条件,否则将导致引擎放弃使用索引而进
行全表扫描,如:
select id from t where num=10 or num=20
可以这样查询:
select id from t where num=10
union all
select id from t where num=20
5、慎用in 和not in 逻辑
in 和not in 也要慎用,否则会导致全表扫描,如:
select id from t1 where num in(select id from t2 where id > 10)
此时外层查询会全表扫描,不使用索引。可以修改为:
select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id
此时索引被使用,可以明显提升查询效率。
6、注意模糊查询
下面的查询也将导致全表扫描:
select id from t where name like ‘%abc%’
模糊查询如果是必要条件时,可以使用select id from t where name like 'abc%'来实现模
糊查询,此时索引将被使用。如果头匹配是必要逻辑,建议使用全文搜索引擎(Elastic search、
Lucene、Solr 等)。
7、避免查询条件中字段计算
应尽量避免在where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进
行全表扫描。如:
select id from t where num/2=100
应改为:
select id from t where num=100*2
8、避免查询条件中对字段进行函数操作
应尽量避免在where 子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行
全表扫描。如:
select id from t where substring(name,1,3)=‘abc’–name 以abc 开头的id
应改为:
select id from t where name like ‘abc%’
9、WHERE 子句“=”左边注意点
不要在where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统
将可能无法正确使用索引。
10、组合索引使用
在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一
个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让
字段顺序与索引顺序相一致。
11、不要定义无异议的查询
不要写一些没有意义的查询,如需要生成一个空表结构:
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
create table #t(…)
12、exists
很多时候用exists 代替in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)
13、索引也可能失效
并不是所有索引对查询都有效,SQL 是根据表中数据来进行查询优化的,当索引列有大
量数据重复时,SQL 查询可能不会去利用索引,如一表中有字段sex,male、female 几乎各
一半,那么即使在sex 上建了索引也对查询效率起不了作用。
14、表格字段类型选择
尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和
连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每
一个字符,而对于数字型而言只需要比较一次就够了。
尽可能的使用varchar 代替char ,因为首先可变长度字段存储空间小,可以节省存储
空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
15、查询语法中的字段
任何地方都不要使用select * from t ,用具体的字段列表代替“*”,不要返回用不到的
任何字段。
16、索引无关优化
不使用*、尽量不使用union,union all 等关键字、尽量不使用or 关键字、尽量使用等
值判断。
表连接建议不超过5 个。如果超过5 个,则考虑表格的设计。(互联网应用中)
表连接方式使用外联优于内联。
外连接有基础数据存在。如:A left join B,基础数据是A。
A inner join B,没有基础数据的,先使用笛卡尔积完成全连接,在根据连接条件得到内
连接结果集。
大数据量级的表格做分页查询时,如果页码数量过大,则使用子查询配合完成分页逻辑。
Select * from table limit 1000000, 10
Select * from table where id in (select pk from table limit 100000, 10)