mysql优化
mysql优化
一、索引优化
索引的数据结构是 B+Tree，而 B+Tree 的查询性能是比较高的，所以建立索引能提升 SQL 的查询性能。
1、建立普通索引
对经常出现在 where 关键字后面的表字段建立对应的索引。
2、建立复合索引
如果 where 关键字后面常出现的有几个字段，可以建立对应的 复合索引。要注意可以优化的一点是：将单独出现最多的字段放在前面。
例如现在我们有两个字段 a 和 b 经常会同时出现在 where 关键字后面：
select * from t where a = 1 and b = 2; \* A *\
也有很多 SQL 会单独使用字段 a 作为查询条件：
select * from t where a = 2; \* B*\
此时，我们可以建立复合索引 index(a,b)。因为不但 A可以利用复合索引，B 也可以利用复合索引。
3、最左前缀匹配原则
如果我们使用的是复合索引，应该尽量遵循 最左前缀匹配原则。MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
假如此时我们有一条 SQL ：
select * from t where a = 1 and b = 2 and c > 3 and d = 4;
那么我们应该建立的复合索引是：index(a,b,d,c) 而不是 index(a,b,c,d)。因为字段 c 是范围查询，当 MySQL 遇到范围查询就停止索引的匹配了。
a,b,d 在 SQL 的位置是可以任意调整的，优化器会找到对应的复合索引。
还要注意一点的是：最左前缀匹配原则不但是复合索引的最左 N 个字段；也可以是单列（字符串类型）索引的最左 M 个字符。
-
例如我们常说的 like 关键字，尽量不要使用全模糊查询，因为这样用不到索引；
-
所以建议是使用右模糊查询：select * from t where name like '李%'（查询所有姓李的同学的信息）。
4、索引下推
例如此时我们有一个复合索引：index(name,age) ，然后有一条 SQL 如下：
select * from user where name like '张%' and age = 10 and sex = 'm';
根据复合索引的最左前缀匹配原则，MySQL 匹配到复合索引 index(name,age) 的 name 时，就停止匹配了；然后接下来的流程就是根据主键回表，判断 age 和 sex 的条件是否同时满足，满足则返回给客户端。
但是由于有索引下推的优化，匹配到 name 时，不会立刻回表；而是先判断复合索引 index(name,age) 中的 age 是否符合条件；符合条件才进行回表接着判断 sex 是否满足，否则会被过滤掉。
5、干净的索引列
索引列不能参与计算，要保持索引列“干净”。
假设我们给表 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');
6、扩展索引
应该尽量 扩展索引，而不是新增索引，一个表最好不要超过 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);
2.select 后面不要带上不必要的字段，因为如果单行长度太长导致查询数据太多， MySQL 会利用 rowid 排序来代替全字段排序，这样会导致多了回表的操作。
如果我们只是查询学生的姓名、年龄和生日，千万不要写 select *;
而是只查询需要的字段：select name, age, birthday。
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 优化
-
如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null。
-
尽量让 group by 过程用上表的索引，不但不需要临时表，还不需要额外的排序。
-
如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表。
-
如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。
4、OR 优化
在 Innodb 引擎下 or 关键字无法使用组合索引。
假设现在关于订单表有一条 SQL ：
select id，product_name from orders where mobile = '12345678900' or user_id = 6;
一般为了提升上面 SQL 的查询效率，会想着为字段 mobile 和 user_id 建立一个复合索引 index(mobile,user_id)；
可是使用 explain 可以发现执行计划里面并没有提示到使用复合索引，所以 or 关键字无法命中 mobile + user_id 的组合索引。
那么可以分别为两个字段建立普通索引，然后采用 union 关键字，如下所示：
(select id，product_name from orders where mobile = '12345678900') union (select id，product_name from orders where user_id = 6);
此时 mobile 和 user_id 字段都有索引，查询才最高效。
5、IN 优化
in 关键字适合主表大子表小，exist 关键字适合主表小子表大。由于查询优化器的不断升级，很多场景这两者性能差不多一样了，可以尝试改为 join 查询。
假设我们现在有一条 SQL ，要查询 VIP 用户的所有订单数据：
select id from orders where user_id in (select id from user where level = 'VIP');
发现不会有任何关于索引的优化，所以我们可以采用 join查询，如下所示：
select o.id from orders o join user u on o.user_id = u.id and u.level = 'VIP';
此时被驱动表应该是 user，那么可以利用到 user 表的主键索引，即可以使用 BKA 算法来提升 join 查询的性能。
6、Like 优化
like 用于模糊查询，但是如果是全模糊查询，将不能命中对应字段的索引。
假设现在关于学生表有一条 SQL：
SELECT name,age,birthday FROM student WHERE name like '%张%';
使用 explain 可以发现执行计划提示查询未命中索引。
因为本来需求就是查询姓张的所有同学信息，所以没必要使用全模糊查询，使用右模糊查询即可。
修改后：
SELECT name,age,birthday FROM student WHERE name like '张%';
三、数据表设计优化
1、数据类型：应该选择更简单或者占用空间更小的类型。
-
整型选择：可以根据长度选择 tinyint、smallint、medium_int，而不是直接使用 int。
-
字符串选择：能确定字符串长度的，尽量使用 char 类型，而不是变长的 varchar 类型。
-
浮点型选择：精度要求比较高的使用 decimal 而不是 double；也可以考虑使用 BIGINT 来保存，小数位保存可以使用乘以整百来解决。
-
日期选择：尽量使用 timestamp 而不是 datetime。
2、避免空值：
-
NULL 值依然会占用空间，并且会使索引更新更加复杂，更新 NULL 时容易发生索引分裂的现象。
-
可以使用有意义的值来代替 NULL 值，例如 “none” 字符串等等。
3、超长字符串：
-
一般超长字符串，varchar 难以存储，我们一般会使用 text 类型。
-
但是 text 类型的字段尽量避免放在主表中，而是抽出来在子表里，用业务主键关联
over
