18条MySQL优化技巧( 二 ) _MySQL优化

但是，普通索引的更新操作性能比唯一索引更好；其实很简单，因为普通索引能利用 change buffer 来做更新操作；而唯一索引因为要判断更新的值是否是唯一的，所以每次都需要将磁盘中的数据读取到 buffer pool 中。
7、前缀索引
我们要学会巧妙的使用前缀索引，避免索引值过大。
例如有一个字段是 addr varchar(255) ，但是如果一整个建立索引 [ index(addr) ] ，会很浪费磁盘空间，所以会选择建立前缀索引 [ index(addr(64)) ] 。
建立前缀索引，一定要关注字段的区分度。例如像身份证号码这种字段的区分度很低，只要出生地一样，前面好多个字符都是一样的；这样的话，最不理想时，可能会扫描全表。
前缀索引避免不了回表，即无法使用覆盖索引这个优化点，因为索引值只是字段的前 n 个字符，需要回表才能判断查询值是否和字段值是一致的。
怎么解决？
倒序存储：像身份证这种，后面的几位区分度就非常的高了；我们可以这么查询：
select field_list from t where id_card = reverse('input_id_card_string');增加 hash 字段并为 hash 字段添加索引。
8、干净的索引列
索引列不能参与计算，要保持索引列“干净” 。
假设我们给表 student 的字段 birthday 建立了普通索引。
下面的 SQL 语句不能利用到索引来提升执行效率：
select * from student where DATE_FORMAT(birthday,'%Y-%m-%d') = '2020-02-02';我们应该改成下面这样：
select * from student where birthday = STR_TO_DATE('2020-02-02', '%Y-%m-%d');9、扩展索引
我们应该尽量扩展索引，而不是新增索引，一个表最好不要超过 5 个索引；一个表的索引越多，会导致更新操作更加耗费性能。
二、SQL 优化1、Order By 优化
order by 后面的字段尽量是带索引的，这样能避免使用 sort_buffer 进行排序。

假如有一条 SQL ，根据生日查询所有学生的信息：select * from student order by birthday desc;
那么为了提升 SQL 的查询性能，我们可以为 birthday 字段建立索引：

CREATE INDEX index_birthday ON student(birthday);select 后面不要带上不必要的字段，因为如果单行长度太长导致查询数据太多， MySQL 会利用 rowid 排序来代替全字段排序，这样会导致多了回表的操作。

如果我们只是查询学生的姓名、年龄和生日，千万不要写 select *;
而是只查询需要的字段：select name, age, birthday from student order by birthday desc;

2、Join 优化

在使用 join 的时候，应该让小表做驱动表。小表：总数据量最小的表
使用 join 语句，最好保证能利用被驱动表的索引，不然只能使用 BNL（Block Nested-Loop Join）算法，还不如不用。
启用 BKA（Batched Key Access）算法，使得 NLJ 算法也能利用上 join_buffer ，被驱动表可以批量查询到符合条件的值，然后可以利用 MMR（Multi-Range Read）的顺序读盘特性来提升回表效率。
如果一定要用 join ，而且被驱动表没有索引可以使用，那么我们可以利用临时表（create temporary table xx(...)engine=innodb;）来让 BNL 算法转为 BKA 算法，从而提升查询性能。
join_buffer 是一个无序数组，所以每次判断都需要遍历整个 join_buffer 。我们可以在业务端实现 hash join 来提升 SQL 的执行速度。

3、Group By 优化