聊聊数据库~4.SQL优化篇 - 鲲逸鹏 - 博客园 (cnblogs.com)

索引

大方向：减少冗余索引，避免重复（无用）索引

建表优化

1. 定长和变长分离（具体得看业务）
   • eg：varchar、text、blob等变长字段单独出一张表和主表关联起来即可
2. 常用字段和不常用字段分离
   • 根据业务来分析，不常用的字段拎出来
3. 在1对多需要关联统计的字段上添加点冗余字段
   • 分表分库时，扩表跨库查询的情景（注意数据一致性）
   • eg：在分类表中添加一个数量字段，统计每天新增商品数量
     • 添加商品时，选完分类就update一下count值（第二天清零）
4. 字段类型一般都是按照这个优先级：（尽量使用优先级高的类型）
   • 数值 > 日期 > char > varchar > text、blob
   • PS：总体原则就是够用即可，然后尽量避免null（不利于索引，浪费空间）
     • eg：varchar(10)和varchar(300)，在表连接查询时，需要的内存是不一样的
5. 伪hash法：比如商品url是一个varchar的列
   • 这时候再建一个hash(url)之后的列，把索引设置到该列
     • 推荐使用crc32（用bigint存储）索引空间就会小很多而且可以避免全表扫描
     • eg：select crc32('http://www.baidu.com/shop/1.html');
   • PS：如果DBA配置了crc64，则使用；如果没有，可以加个条件（CRC32碰撞后的解决方案）
     • 对于少部分碰撞的记录，只需要多扫描几行就行了，不会出现全表扫描的情况
     • eg：select xxx from urls where crc_url=563216577 and url='url地址'

PS：需要关注的技术点：crc32

组合索引专题

项目里面使用最多的是组合索引，这边先以组合索引为例：

1.尽可能多的使用索引列，尽可能使用覆盖索引。

-- 覆盖索引：仅仅查找索引就能找到所需要的数据

2.最左前缀原则

-- 查询的时候从最左边的列开始，并且不跳过中间的列，一直到最后

3.范围条件放在最后面（范围条件后面的列索引会失效）

4.不在索引列上做其他操作

容易导致全表扫描，这时候利用覆盖索引可以简单优化

 !=、is not null、is null、not in、in、like 慎用

like案例：尽量使用xxx%的方式来全文搜索，能和覆盖索引联合使用更好. 

在like 索引上限定索引长度

5. 查询时避免在索引上计算、函数、类型转换（自动 or 手动）【尽量避免】

6.查询语句优化：

 　　使用union all 替代 or, in 

　　不要count可空列

count优化案例：（有时候拆分查询会更快）

-- 需要统计id>10000的数据总量（实际中可能会根据时间来统计）
explain
    select count(*) as count
    from userinfo
    where id > 10000; -- 2s

-- 分解成用总数-小数据统计 ==> 1s
explain
    select (select count(*) from userinfo) - (select count(*) from userinfo where id <= 10000) as count;

7. group by和order by的列尽量相同，这样可以避免filesort

8.用连接查询来代替子查询

-- 用exists代替in？MySQL查询优化器针对in做了优化（改成了exists，当users表越大查询速度越慢）
explain
    select *
    from students
    where name in (select username from users where id < 7);

-- ==> 等同于：
explain
    select *
    from students
    where exists(select username from users where username = students.name and users.id < 7);

-- 真正改进==>用连接查询代替子查询
explain
    select students.*
    from students
             inner join users on users.username = students.name and users.id < 7;

-- 等效写法：这个tmp是临时表，是没有索引的，如果需要排序可以在（）里面先排完序
explain
    select students.*
    from students
             inner join (select username from users where id < 7) as tmp on students.name = tmp.username;

扩展：索引误区和冗余索引

1.索引误区

很多人喜欢把where条件的常用列上都加上索引，但是遗憾的事情是：独立的索引只能同时用上一个

根据测试得知，一次只能使用1个索引。索引优先级：主键 > 唯一 > 组合 > 普通

• PS：在实际应用中往往选择组合索引