MySQL优化

什么是索引

• 索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录，当我们需要查询某一个字的含义时，正常操作都是先根据字典的索引，找到该字在哪一页，然后直接翻到该页就行了。如果没有这个索引的话，那么我们就得一页一页的翻字典，直到找到该字。
• 索引一般存储在磁盘的文件中，它是占用物理空间的。

索引的数据结构

b树和b+树

索引类型

数据结构维度

• B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。
• 哈希索引: 适合等值查询，检索效率高，一次到位。
• 全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char,text,varchar类型上创建。
• R-Tree索引: 用来对GIS数据类型创建SPATIAL索引

物理存储维度

• 聚簇索引：聚簇索引并不是一种单独的索引类型，而是一种数据的存储方式。在 MySQL 的 InnoDB 存储引擎中，所谓的聚簇索引实际上就是在同一个 B+Tree 中保存了索引和数据行：此时，数据放在叶子结点中，聚簇聚簇，意思就是说数据行和对应的键值紧凑的存在一起
• 非聚簇索引：非聚簇索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。

需要注意的是：在 MySQL 中，如果表本身就有设置主键，那么主键就是聚簇索引；如果表本身没有设置主键，则会选择表中的一个唯一且非空的索引来作为聚簇索引；如果表中连唯一非空的索引都没有，那么就会自动选择表中的隐式主键来作为聚簇索引。

逻辑维度

• 主键索引：一种特殊的唯一索引，不允许有空值。
• 普通索引：MySQL中基本索引类型，允许空值和重复值。
• 联合索引：多个字段创建的索引，使用时遵循最左前缀原则。
• 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
• 空间索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。

索引的优缺点

优点：

• 索引可以加快数据查询速度，减少查询时间。
• 唯一索引可以保证数据库表中每一行的数据的唯一性。

缺点：

• 创建索引和维护索引要耗费时间。
• 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间。
• 对表中的数据进行增、删、改的时候索引也要动态的维护。

分析查询语句：EXPLAIN

基本语法：EXPLAIN SELECT tables

一般来说，我们需要重点关注id、type、rows、filtered、extra、key
id：
id越大越先执行，id相同从上至下执行
type:
type表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：
system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。
• const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。
• eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
• ref : 常用于非主键和唯一索引扫描。
• ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行。
• index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
• unique_subquery：类似于eq_ref，条件用了in子查询
• index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
• range：常用于范围查询，比如：between … and 或 In 等操作。
• index：全索引扫描。
• ALL：全表扫描。

rows:
该列表示MySQL估算要找到我们所需的记录，需要读取的行数。对于InnoDB表，此数字是估计值，并非一定是个准确值。
filtered:
该列是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。
extra：
该字段包含有关MySQL如何解析查询的其他信息，它一般会出现这几个值：

• Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句
• Using index ：表示是否用了覆盖索引。
• Using temporary: 表示是否使用了临时表,性能特别差，需要重点优化。一般多见于group by语句，或者union语句。
• Using where : 表示使用了where条件过滤.
• Using index condition：MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

key：

该列表示实际用到的索引。一般配合possible_keys列一起看。

适合建索引的场景

1. 字段的数值有唯一性的限制
2. 频繁作为 WHERE 查询条件的字段
3. 经常 GROUP BY 和 ORDER BY 的列
4. UPDATE、DELETE 的 WHERE 条件列
5. DISTINCT 字段需要创建索引

不适合建索引的场景

1. 在where中使用不到的字段，不要设置索引
2. 数据量小的表最好不要使用索引
3. 区分度低的字段不适合加索引
4. 避免对经常更新的表创建过多的索引
5. 已经有冗余的索引的情况（比如已经有a,b的联合索引，不需要再单独建立a索引）

索引失效场景

创建测试表

DROP TABLE IF EXISTS `kuser`;
CREATE TABLE `kuser` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `jid` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `kuser` VALUES ('1', '李云龙', '40', '16666666666', '1');
INSERT INTO `kuser` VALUES ('2', '丁伟', '41', '18888888888', '1');
INSERT INTO `kuser` VALUES ('3', '孔捷', '43', '19999999999', '1');
INSERT INTO `kuser` VALUES ('4', '赵刚', '35', '15555555555', '2');



DROP TABLE IF EXISTS `job`;
CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of job
-- ----------------------------
INSERT INTO `job` VALUES ('1', '团长');
INSERT INTO `job` VALUES ('2', '政委');

创建索引

create index kuser_name_idx on kuser(name);
create index kuser_age_idx on kuser(age);
create index kuser_name_phone_idx on kuser(name,phone);

1. 查询条件包含or，可能导致索引失效

优化方式：可以使用union来代替，灵活使用，union需要使用临时表，有时性能可能还不如or。

2. 如果字段类型是字符串，where时一定用引号括起来，否则索引失效


3. like通配符可能导致索引失效

优化方式：尽量使用右模糊

4. 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效（最左前缀原则）

5. 在索引列上使用 mysql 的内置函数，索引失效

6. 对索引列运算（如，+、-、*、/），索引失效

7. 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效

8. 索引字段上使用is null， is not null，可能导致索引失效

优化方式：可以给字段添加默认值 0，对 0 值进行判断。

9. 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效

10. mysql 估计使用全表扫描要比使用索引快,则不使用索引

回表

InnoDB 存储引擎中，所有表的都会有一个聚簇索引，叶子节点存储了具体的数据，二级索引叶子节点只包含索引列和主键，当查询的字段不在二级索引时，会去遍历聚簇索引树，这个操作就叫回表。

-- 回表
select * from kuser where name = '李云龙';

-- 不会回表
select name from kuser where name = '李云龙';

select name,phone from kuser where name = '李云龙';

索引覆盖

覆盖索引是select的数据列只用从索引中就能够取得，不必回表，换句话说，查询列要被所建的索引覆盖。

select name,phone from kuser where name = '李云龙';

索引下推

select * from kuser where name = '李云龙' and phone  = '16666666666'

如果是Mysql5.6之前，在二级索引中，找出所有名字是“李云龙”的人，拿到它们的主键id，然后回表找出数据行，再去对比手机号字段。我们可能觉得奇怪，phone 是联合索引，为什么选出名字包含“李云龙”的数据后，不再顺便看下phone再回表呢。所以呀，MySQL 5.6就引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。因此，MySQL5.6版本之后，选出名字包含“李云龙”的数据后，顺便过滤下phone='16666666666’的，然后再回表。

其他

1. 小表驱动大表

-- a表比b表大
select * from a where cc in (select cc from b)
-- a表比b表小
select * from a where exists (select cc from b where b.cc = a.cc)

2. 分页优化

思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

select * from student t,(select id from student order by id limit 2000000,10)
a where t.id = a.id

思路二

select * from student where id > 2000000 limit 10