3.1mysql索引和查询优化---课程笔记

什么是索引：

索引用于快速定位要查找的数据

数据库索引查找：

全表扫描vs索引查找

原理

1. 索引根据字母进行排序

innodb是通过主键ID定位。

2. 如何根据首字母找到所在行

InnoDB表聚簇索引

我们把根据主键排序的InnoDB表成为聚簇索引

把根据**排序的索引成为二级索引

索引相同根据ID的顺序进行排序

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

索引维护

1. 索引维护由数据库自动完成

2. 插入/修改/删除每一个索引行都变成一个内部封装的实务

3.索引越多，事务越长，代价越高

4.索引越多对表的插入和索引字段修改就越慢

如何使用索引

1. 依据where查询条件建立索引

2. 排序order by,group by, distinct 字段添加索引

字段的选择性--某个字段其值的重复程度

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

2. 联合索引中选择性好的字段应该排在前面

联合索引与前缀查询

1. 联合索引能为前缀单列，复列查询提供帮助

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

 3. 非常长的字段上建立索引影响性能

 4. InnoDB索引单字段（utf-8）只能取前767bytes

5. 对长字段处理的方法

     a.Email类，建立前缀索引

        mail_addr varchar(2048) 

         idx_mailadd(Mail_addr(30))

     b.住宅类，分拆字段

索引覆盖扫描

最核心sql考虑索引覆盖 e.g.  username/pwd

不需要回表获取pwd字段，IO最小，效率最高

无法使用索引的情况

索引列进行数学运算或函数运算

未含复合索引的前缀字段

前缀通配，‘_’和'%'通配符

where 条件使用not,<>,!=

字段类型匹配

   并不绝对，但是无法预测的会造成问题，不要使用

利用索引排序

如何确定一个查询走没走索引，走了哪个索引

explain是确定一个查询如何走索引最简便有效的方法

explain select * from tb_test;

type :查询access的方法

key:本次查询最终选择使用哪些索引，null为未使用索引

key_len:选择的索引使用的前缀长度或整个长度

rows:需要扫描的数据量

extra:主要指的是fetch data的具体方法