【mysql】索引优化

查询优化：

（1） 覆盖索引

如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’，不需要回表，减少树的搜索次数

（2）联合索引---最左前缀原则：

如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 。对于a,c, 只走a字段索引，不会走c字段。

（3）前缀索引：前缀索引用来作为字符串的索引，1.倒叙存储；2.使用hash字段，作为校验码；

案例：

查询一个广告文案下面对应的关键词内容，广告文案内容长度一般会有30到50这样子的中文字符。
我们采取的做法是 新增一个hash字段，将广告文案在代码层面进行md5，然后存储到hash字段中，
可能会出现hash冲突，我们在代码层面再做一层 ”数据的完全匹配“；减少索引的长度，同时复杂计算放在代码层面。

  
（4）尽量选择区分度高的列作为索引

（5）把复杂计算放到业务层而不是数据库层

（6）分页查询优化

（7）字段的属性要设置为not null，使用一个特殊的值作为默认值，例如0或者一个空串

（8）小表驱动大表

---

索引失效的情况：

（1）索引列不能参与计算，保持列“干净”，

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

（2）负向条件查询不能使用索引。

select * from order where status!=0 and stauts!=1

not in/not exists都不是好习惯。

查询条件中使用了不等于操作符（<>、!=）的select语句执行慢

 原因：SQL中，不等于操作符会限制索引，引起全表扫描，即使比较的字段上有索引

（3）前导模糊查询不能使用索引。

select * from order where desc like '%XX'

（4）隐式的强制类型转换会全表扫描

select name from user where phone=13800001234

你以为会命中phone索引么？大错特错了！！！

（5）OR语句使用不当

 使用or分割的条件，如果or前后条件的列存在没有索引，那么涉及到的索引都不会使用，导致全表扫描。
 例如：例如：where A=1 or B=2，A上有索引，B上没索引，则会全表查询。

---

为什么推荐使用NOT NULL

允许为null的列，查询有潜在大坑。

1.null字段的数据，只能通过 is null查询出来

2.当用count函数进行统计时，NULL 列不会计入统计。

SELECT count(name) from t

而且，null是一个属性，额外占有1个字节

---

分页查询优化：

select * from auth_user limit offset,len;

mysql 底层是从第一行开始找到offset+len行，再抛弃前面的offset行。

优化分页的问题其实就是offset的问题，尤其是偏移量大的时候。mysql会扫描大量不需要的行然后抛弃，只取limit的数量。所以一般最好不要用offset。解决方法有

1.通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据,而不是通过二级索引获取主键再通过主键去遍历数据页。

2.可以把limit查询转换成已知位置的查询，变成between XXX and XXX 。

3.可以记录上次查询的数据的位置，下一次查询直接从该位置开始扫描。

查询1：
SELECT `id` FROM `table` WHERE `node` = 2 ORDER BY `id` ASC LIMIT 10
 
假设查询1的第10条数据的id是10，那么查询第二页的SQL如下

SELECT `id` FROM `table` WHERE `node` = 2 AND `id` >10 ORDER BY `id` ASC LIMIT 10

---

小表驱动大表：

in先执行子查询，也就是in()所包含的语句。子查询查询出数据以后，将前面的查询分为n次普通查询(n表示在子查询中返回的数据行数)

exists先查询出外层数据，然后会判断是不是存在和exitsts（）中的字段相等，相等才保留数据。