MySQL学习记录03-MySQL应用优化之MySQL引擎查询优化

第一章 MySQL业务优化与设计

第一节 MySQL引擎查询优化

• 什么是索引
  索引是指通过类似于新华字典中通过偏旁部首快速定位某个字所在页数后实现快速查找的方法，索引也即是“index”或者“目录”。
  
• 数据库索引查找

全表扫描：是指通过遍历全表数据查找出需要的记录的方式，类似于我们用新华字典的时候通过翻页找到自己需要的字而不通过偏旁部首或拼音首字母查找。
索引查找：索引查找是指通过索引记录定位到需要查询的记录，然后根据索引ID查询直接定位到需要查询的数据的方法。
比如上图中需要查询姓名为Dan的记录，首先在因为建立了Name的索引，所以Name会按照字母顺序进行排序放到索引记录中，当需要查询时通过字母顺序快速定位到Dan的记录找到ID=481,然后通过481快速定位到Dan在表中的记录。
Name:索引项
Dan:索引值
ID：主键ID

• 如何根据首字母找到所在行
  • 二分查找法
  • B+tree:拥有自己的根节点和支节点以及存放数据的页节点，并且整颗数分层存储，上一层数据会存放下一层数据的管理范围，直到最后一层页节点。

比如前面提到的查询Dan记录，首先在根节点下有2个支节点，D属于A-M的支节点，然后A-M又有2个支节点，D属于ABCD的支节点，依次类推通过4层5步找到Dan的记录。

• 创建索引
  单列索引

create index idx_test on tb_student (name);

联合索引

create index idx_test1 on tb_student (name,age);

索引先根据name排序，name相同的情况下在根据age排序

• 索引维护
  索引维护由数据库自动完成；
  查询/删除/修改每一个索引行都将变成一个内部封装事务；
  索引越多，事务越长，代价越高；
  索引越多，对表的插入和索引字段修改就越慢。

控制标上索引的数量，切勿胡乱添加索引。

• 如何正确的使用索引

  • 依据WHERE条件创建索引

select a,b from tb_a where a=?

idx_a (a) ✅

select a,b from tb_a where c=? and d=?

idx_cd （c,d) ✅

• 依据order by/group by/distinct字段添加索引

select * from tb_a order by a;
select a,count(*)  from tb_a group by a;

idx_a (a) ✅

select a,b from tb_a order by a,b;

idx_ab （a,b) ✅

select * from tb_a order where c=? by a;

idx_ca （c,a) ✅

• 索引与字段选择性
  

选择性很差的字段通常不适合创建单列索引

• 男女比例相仿的字段中性别字段不适合创建单列索引
• 如果男女比例极不平衡，要查询的又是少数记录，可以考虑使用索引

联合索引中选择性好的字段应该放到前面

select * from tb_a where gender=? and name=?

idx_a1 （name,gender) ✅

• 联合索引与前缀查询
  联合索引能为前缀单列、复列查询提供加速
  idx_abc(a,b,c)
  其中a和a,b都是索引的前缀

select a,b,c from tb_a where a=? ✅
select a,b,c from tb_a where a=? and b=? ✅
select a,b,c from tb_a where a=? and c=? 5.5版本前部分OK,5.5版本后也是可以加速的

• 合理创建索引，避免冗余

(a),(a,b),(a,b,c) ❌
(a,b,c) ✅

• 长字段上创建索引

  • 非常长的字段上创建索引影响性能
  • innodb索引单字段只能取钱767bytes
  • 对长字段的处理方法
    • email类建立前缀索引

    mail_addr varchar(2048)
    idx_mailadd(mail_addr(30)

    • 住址类分拆字段

    home_address varchar(2048)
    province varchar(1024) ,city varchar(1024),distract varchar(1024),local_address varchar(1024) 建立联合索引或单列索引

• 索引覆盖扫描

• 最核心SQL考虑索引覆盖

SELECT NAME FROM TB_USER WHERE USERID=?

KEY IDX_UID_NAME(USERID,NAME)
不需要回表获取name字段，IO开销最小，效率最高。

• 不能使用索引的场景
• 索引列进行数据计算或者函数计算

where id+1=10 ❌
where id=10-1 ✅
year(col)<2017 ❌
col<‘2017-01-01’ 正确

• 未含复合索引的前缀字段

idx_abc(a,b,c)

where b=? and c=?

idx_bc(b,c) ✅

• 前缀通配，’_'和‘%’通配符
  like ‘%xxx%’ ❌
  like ‘xxx%’ ✅

• where 条件使用NOT,<>,!=

• 字段类型匹配

  • 并不绝对，但是无法预测会造成的问题，不要使用
    INT A(11) IDX_A(A)
    WHERE A=‘123’ ❌
    WHERE A=123 ✅

• 利用索引提高排序效率

idx_ab(a,b)

能够帮助提高排序效率的语句

order by a;
a=3 order by b;
order by a,b;
order by a desc , b desc;
a>5 order by a;

不能能够帮助提高排序效率的语句

order by ;
a>3 order by b;
a in (1,3) order by ab;
order by a asc ,b desc;

• 如何确定查询是否使用了索引
  
• explain是确定是个查询是否使用索引的最简单有效的方法

explain select * from tb_test;

• 关注的项目
  - type:查询access的接入方式(range标识范围查询，ref标识等值查询，index索引查询）
  - key:本地查询选择哪个索引，NULL表示未使用索引
  - key_len:选择的索引使用的前缀长度或这个索引长度
  - rows:需要扫描的记录数
  - extra:额外信息，主要指fetch data的具体方式