• 欢迎来到我的博客
  • [email protected]

Mysql 索引 & 索引优化

学习笔记 tianlan 1年前 (2020-04-11) 445次浏览 0个评论 扫描二维码
文章目录[隐藏]

Mysql 索引 & 索引优化

优势 & 劣势

虽然索引大大提高了查询速度,同时却会降低更新表的速度。

索引和约束的区别

约束是为了保证表数据的完整性,索引是为了提高查询效率,两者作用不一样!其次种类也不一样。

Mysql 每次查询最多只会运用一个索引

  • 所以需要合理建立联合索引
  • 尽量扩展索引,而不要新建索引

外键索引可以 nullable

在 Laravel 社区学习的时候 get 到的。

什么情况下建立索引

  • 主键
  • 唯一
  • 外键(很多项目常常不搞外键索引)
  • 直接条件查询的字段
select * from table where col=’7001’

//这里就需要对 col建立索引
  • 查询中统计或分组统计的字段
select max(col1) from table
select col2,count(*) from table group by col2

//这里就需要对 col1和 col2建立索引

什么时候不建或少建索引

  • 表记录太少
  • 经常插入、删除、修改的表
  • 数据重复且分布平均的表字段

假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。

  • 经常和主字段一块查询,但主字段索引值比较多的字段

如gc_dfss(电费实收)表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的情况,如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间,从实际上分析一笔收款如果按收费序号索引就已 经将记录减少到只有几条,如果再按后面的几个字段索引查询将对性能不产生太大的影响。

最左前缀原则

索引

实际上联合索引(a,b,c)相当于建立了索引:(a),(a,b),(a,b,c)

like 语句

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

排序的索引问题

mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

使用短索引

对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

NULL 对索引的影响

  • 索引类 is null 走了索引
  • 索引类 is not null 走了全表

什么情况不走索引

否定查询

  • not in 不会走索引 (建议用 NOT EXISTS 代替)
  • is not null 不会走索引
  • != 不会走索引

索引列参与计算

-- 不会使用索引,因为所有索引列参与了计算
SELECT `sname` FROM `stu` WHERE `age`+10=30;

-- 不会使用索引,因为使用了函数运算,原理与上面相同 
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990;

违反最左前缀

-- 如果只建立了 age 的单列索引,则不走索引(索引列没有放在条件的最左)
SELECT `name` from `stu` WHERE `name`='sky' AND age>18

SELECT * FROM `houdunwang` WHERE `uname` LIKE '后盾%' -- 走索引 

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引 

其他情况

-- 正则表达式不使用索引,这应该很好理解,所以这就是为什么在SQL中很难看到regexp关键字的原因 

-- 字符串与数字比较不使用索引; 
CREATE TABLE `a` (`a` char(10)); 
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引 
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引

-- 如果条件中有 or,如果 dname 建立了索引,那么依旧不会走索引,除非建立了所有条件列的联合索引,我们建议大家尽量避免使用or 关键字
select * from dept where dname='xxx' or loc='xx' or deptno=45 

-- MySQL内部优化器会对SQL语句进行优化,如果优化器估计使用全表扫描要比使用索引快,则不使用索引

天蓝, 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:Mysql 索引 & 索引优化
喜欢 (0)
[[email protected]]
分享 (0)

您必须 登录 才能发表评论!