西安网页制作模板,网站seo优化推广外包,免费制作h5页面的工具,域名要多少钱全值匹配我最爱#xff0c;最左前缀要遵守#xff1b; 带头大哥不能死#xff0c;中间兄弟不能断#xff1b; 索引列上不计算#xff0c;范围之后全失效#xff1b; LIKE百分写最右#xff0c;覆盖索引不写 *#xff1b; 不等空值还有or#xff0c;索引失效要少用最左前缀要遵守 带头大哥不能死中间兄弟不能断 索引列上不计算范围之后全失效 LIKE百分写最右覆盖索引不写 * 不等空值还有or索引失效要少用 字符单引不可丢SQL高级也不难
索引的种类
众所周知索引类似于字典的目录可以提高查询的效率。
索引从物理上可以分为聚集索引非聚集索引
从逻辑上可以分为普通索引唯一索引主键索引联合索引全文索引
索引优化策略 不要在索引列上进行运算或使用函数
在列上进行运算或使用函数会使索引失效从而进行全表扫描。如下面例子在publish_timeid列上分别加上索引publish_time为datetime类型id为int类型
-- 全表扫描
select * from article where year(publish_time) 2019
-- 走索引
select * from article where publish_time 2019-01-01
-- 全表扫描
select * from article where id 1 5
-- 走索引
select * from article where id 4小心隐式类型转换
假设id为varchar类型
-- 全表扫描
select * from article where id 100
-- 走索引
select * from article where id 100为什么呢
select * from article where id 100
-- 等价于
select * from article where CAST(id AS signed int) 100上一条规则说过不要在索引列上使用函数隐式类型转换在索引字段上做了函数操作因此会全表扫描
那么如果id是int执行下面这个语句是否会导致全表扫描呢
select * from article where id 100答案是会用到索引
前导模糊查询不会使用索引
-- 全表扫描
select * from article where author like %李%李,%李%都会导致全表扫描非前导模糊查询可以使用索引
-- 走索引
select * from article where author like 李%联合索引最左前缀原则
mysql会一直向右匹配直到遇到范围查询(、、between、like)就停止匹配比如a 1 and b 2 and c 3 and d 4 如果建立(a,b,c,d)顺序的索引d是用不到索引的如果建立(a,b,d,c)的索引则都可以用到a,b,d的顺序可以任意调整
1.将区分度最高的字段放在最左边
当不需要考虑排序和分组时将区分度最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找
如果在a b列上建立联合索引该如何建立才能使查询效率最高
select count(distinct a) / count(*), count(distinct b) / count(*), count(*) from table执行如下语句假设3个输出依次为0.001,0.373,16049可以看到b列的选择性最高因此将其作为联合索引的第一列即建立(b, a)的联合索引
2.查询时可以乱序
如果建立了联合索引a, b。例如下面的2个写法是等价的因为MySQL会将查询的顺序优化成和联合索引的顺序一致
select * from table where a 1 and b 1select * from table where b 1 and a 13.优化查询避免出现filesort
select * from table where a ? and b ? order by c最左前缀原则不仅用在查询中还能用在排序中。MySQL中有两种方式生成有序结果集
通过有序索引顺序扫描直接返回有序数据Filesort排序对返回的数据进行排序
因为索引的结构是B树索引中的数据是按照一定顺序进行排列的所以在排序查询中如果能利用索引就能避免额外的排序操作。EXPLAIN分析查询时Extra显示为Using index。
所有不是通过索引直接返回排序结果的操作都是Filesort排序也就是说进行了额外的排序操作。EXPLAIN分析查询时Extra显示为Using filesort当出现Using filesort时对性能损耗较大所以要尽量避免Using filesort
对于如下sql
select * from table where a ? and b ? order by c可以建立联合索引a, b, c
如果索引中有范围查找那么索引有序性无法利用如
select * from table where a 10 order by b索引(ab)无法排序。
放几个例子
-- 使用了a列
where a 3-- 使用了a b列
where a 3 and b 5-- 使用了a b c列
where a 3 and c 4 and b 5-- 没有使用索引
where b 3-- 使用了a列
where a 3 and c 4-- 使用了a b列
where a 3 and b 10 and c 7-- 使用了a b 列
where a 3 and b like xx% and c 7unionorin都能命中索引建议使用in新版MySQL的or可以命中索引
select * from article where id 1 or id 2效率从高到低为unioninor。in和union的效率差别可以忽略不计建议使用in
负向条件索引不会使用索引建议用in
负向条件有!、、not in、not exists、not like 等
-- 全表扫描
select * from article where id ! 1 and id ! 2知道id的所有取值范围可以改为类似如下形式
-- 走索引
select * from article where id in (0, 3, 4)建立覆盖索引 众所周知表数据是放在一个聚集索引上的而建立的索引为非聚集索引非聚集索引的叶子节点存放索引键值以及该索引键指向的主键。一般查找的过程是从非聚集索引上找到数据的主键然后根据该主键到聚集索引上查找记录这个过程称为回表不清楚的看推荐阅读。
如有下面这个sql
select uid, login_time from user where username ? and passwd ?可以建立(username, passwd, login_time)的联合索引由于 login_time的值可以直接从索引中拿到不用再回表查询提高了查询效率
经常更改区分度不高的列上不宜加索引
更新会变更 B 树更新频繁的字段建立索引会大大降低数据库性能。
“性别”这种区分度不大的属性建立索引是没有什么意义的不能有效过滤数据性能与全表扫描类似。
一般区分度在80%以上的时候就可以建立索引区分度可以使用 count(distinct(列名))/count(*) 来计算
明确知道只会返回一条记录可以加limit1
当查询确定只有一条记录时可以加liimit1让MySQL停止游标移动提高查询效率
select uid from user where username ? and passwd ?可改为
select uid from user where username ? and passwd ? limit 1对文本建立前缀索引
用邮箱登录是一个常见的问题如果对email整个字段建立索引会让索引变得大且慢
select username from user where emailxxx; 这时我们可以索引开始的部分字符这样可以大大节约索引空间从而提高索引效率但这样也会降低索引的区分度。索引的区分度是指不重复的索引值和数据表的记录总数的比值。索引的区分度越高则查询效率越高因为区分度高的索引可以让MySQL在查找时过滤掉更多的行。
因此我们选择足够长的前缀保证较高的区分度同时又不能太长以便节约空间
可以进行如下实验
select count(distinct left(email, 5)) / count(*) as col5,count(distinct left(email, 6)) / count(*) as col6,count(distinct left(email, 7)) / count(*) as col7from user假设输出依次为0.03050.03090.0310
查询显示当前缀长度达到7的时候再增加前缀长度区分度提升的幅度已经很小了因此创建email(7)的前缀索引即可
需要注意的一点是前缀索引不能使用覆盖索引因为从索引中获取不到完整的数据还得回表查询
建立索引的列不为NULL
只要列中包含有 NULL 值都将不会被包含在索引中复合索引中只要有一列含有 NULL值那么这一列对于此复合索引就是无效的。
因此在数据库设计时除非有一个很特别的原因使用 NULL 值不然尽量不要让字段的默认值为 NULL。
分页查询优化
MySQL 并不是跳过 offset 行而是取 offsetN 行然后返回放弃前 offset 行返回 N 行那当 offset 特别大的时候效率就非常的低下要么控制返回的总页数要么对超过特定阈值的页数进行 SQL 改写单开一文来讲
