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 或者一个空串

索引失效的情况：

（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