深入MySQL索引(1)
MySQL索引是什么?
索引是一张表,该表保存了主键与索引字段,并指向实体表的记录。
合理使用索引优化SQL查询是高级开发者必备技能
优点
可以快速检索,减少I/O次数,加快检索速度;根据索引分组和排序,可以加快分组和排序;
缺点
- 索引也是一种表,因此会占用存储空间,索引表占用的空间比数据表大;
- 索引表的维护和创建需要时间成本,这个成本随着数据量增大而增大;
- 构建索引会降低数据表的修改操作(删除,添加,修改)的效率,因为在修改数据表的同时还需要修改索引表;
MySQL用来做什么?
索引是快速搜索的关键,MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度
MySQL怎么用?
索引类型
主键索引 PRIMARY KEY:
特殊的唯一索引,不允许有空值
# 通过ALTER创建 除了通过ALTER也可以在创表时指定,其他索引都一样,不再说明
ALTER TABLE tableName ADD PRIMARY KEY(column) ## 注意:每个表只能有一个主键索引
唯一索引 UNIQUE
唯一索引列的值必须是唯一,允许空值
# 通过ALTER创建
ALTER TABLE tableName ADD UNIQUE (columnName)
普通索引 INDEX
最基本的索引,没有任何限制
# ALTER命令创建
ALTER TABLE tableName ADD INDEX index_name(columnName)
组合索引
组合索引,即一个索引包含多个列。可以在创建表的时候指定,也可以修改表结构,如:
ALTER TABLE `tablename` ADD INDEX index_name(`column1`, `column2`, `column3`)
注意:遵循最左前缀原则,把最常用作为检索或排序的列放在最左,依次递减,组合索引相当于建立了column1,column1column2,column1column2column3三个索引,而col2或者col3是不能单独使用索引的。
全文索引 FULLTEXT
全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。
ALTER TABLE `table_name` ADD FULLTEXT (`column`)
MySQL在哪用?
什么时候要使用索引?
- 主键自动建立唯一索引;
- 经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引;
- 作为排序的列要建立索引;
- 查询中与其他表关联的字段,外键关系建立索引
- 高并发条件下倾向组合索引;
- 用于聚合函数的列可以建立索引,例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引
什么时候不要使用索引?
- 经常增删改的列不要建立索引;
- 有大量重复的列不建立索引;(比如sex字段,只有男女两个值,就没有必要加字段,可以以此推类)
- 表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时,它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录,它们往往在执行完第一条查询命令之后就被全部加载到内存里,这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时,数据库的性能测试结果才有意义。
索引失效的情况:
- 在组合索引中不能有列的值为NULL,如果有,那么这一列对组合索引就是无效的。
- 在一个SELECT语句中,索引只能使用一次,如果在WHERE中使用了,那么在ORDER BY中就不要用了。
- LIKE模糊查询中,避免%和_在开头,’%aaa%'和’aaa’,会导致索引失效,但是‘aaa%’和’aaa_'可以使用索引,其它通配符同样,也就是说,在查询条件中使用正则表达式时,只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。。
- 在索引的列上使用表达式或者函数会使索引失效,函数会对表中每行数据进行运算,这将导致索引失效而进行全表扫描
- 在查询条件中使用’<’,’>’,’!='会导致索引失效。如果对主键索引使用!=则不会使索引失效,如果对主键索引或者整数类型的索引使用‘<’或者‘>’不会使索引失效。
- 在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
- 如果排序的字段使用了索引,那么select的字段也要是索引字段,否则索引失效。如果排序的是主键索引则不会导致索引失效。
- 使用or来连接条件,应尽量避免在where子句中使用or来连接条件,可能使数据库引擎放弃使用索引而进行全表扫描(注意:并不是使用or就不走索引,而是因为or条件中有未建立索引的列才会使索引失效,否则依然会走索引)
如: 低效:
select * from t_credit_detail where id= '23' or Flistid = '37';
可以用下面这样的查询代替上面的 or 查询: 高效:
select * from tbName where id = '23' union all select* from tbName where id = '37';
- 类型不一致会导致索引失效,字符串不加单引号会导致索引失效(比如字段email是字符串类型的,使用WHERE email=99999 则会导致失败,应该改为WHERE email=‘99999’)。