MySQL 创建索引的原则，优化的思路

康师傅yyds

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。

按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。

按照作用字段个数进行划分，分成单列索引和联合

创建索引的原则

1     show index from    channel_detail;
2     DROP INDEX idx_cl_channel_id ON channel_label;
3 
4 CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
5 ON table_name (col_name[length],...) [ASC | DESC]
6     CREATE INDEX idx_cd_channel_id ON channel_detail(channel_id);

查询某表索引使用情况

SELECT object_type,object_schema,object_name,index_name,count_star,count_read,COUNT_FETCH
FROM
`performance_schema`.table_io_waits_summary_by_index_usage where object_name='orders_1';

select * from schema_unused_indexes; // 在sys库中查看没用的索引

适合创建索引的情况

　　　　1、字段的数值有唯一性的限制

　　　　　　业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

　　说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

　　　　2、频繁作为 WHERE 查询条件的字段

　　　　3. 经常 GROUP BY 和 ORDER BY 的列

　　　　4、UPDATE、DELETE 的 WHERE 条件列

　　　　　　如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

　　　　5.DISTINCT 字段需要创建索引

　　　　　　对某个字段去重，要对该字段创索引

　　　　6. 多表 JOIN 连接操作时，创建索引注意事项

　　　　　　首先，连接表的数量尽量不要超过 3 张

　　　　　　其次，对 WHERE 条件创建索引

　　　　　　最后，对用于连接的字段创建索引

　　　　7. 使用列的类型小的创建索引

　　　　　　能用小一点的数据类型就用小一点的 int->bigint，InnoDB创建的索引B-tree小一点，查的快一点

　　　　8. 使用字符串前缀创建索引

　　　　　　对字符串创建索引时，截取一部分前缀就可以了。

　　　Alibaba《Java开发手册》

　　　　　　【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

　　　　　　　　说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

　　　　9. 区分度高(散列性高)的列适合作为索引　　　　　　

　　　　10. 使用最频繁的列放到联合索引的左侧

　　　　11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

不适合创建索引的情况

　　　　where不到的字段不创建

　　　　重复数据太多的不创建（如不要对 is_delete 字段创建索引）

　　　　数据量小的表不要创建索引

　　　　经常更新操作的表不要创建太多索引

　　　　不建议用无须的字段撞见索引

　　　　　　例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

　　　　不要定义冗余或重复的索引

　　　　　　重点提现在联合索引和单行索引的重复上

SQL优化的思路

1、SHOW INDEX FROM student_info; //查表中索引
2、DROP INDEX index_name ON table_name; //删除索引
3、set global slow_query_log='ON'; //开启慢查询日志   不用的时候，最好关闭掉。

4、set global long_query_time = 1; //修改long_query_time阈值为一秒  默认我们本地超过一秒为慢查询
　　set long_query_time=1; //上面修改了全局的，还要修改当前回话的

5、show variables like '%slow_query_log%'; // 查看两个参数
6、SHOW GLOBAL STATUS LIKE '%Slow_queries%'; //查看慢查询的次数

数据库重启的话，上面参数要重新设置，可以去配置文件中设置。

慢查询分析工具mysqldumpslow

mysqldumpslow 命令的具体参数如下：

-a: 不将数字抽象成N，字符串抽象成S

-s: 是表示按照何种方式排序：

c: 访问次数

l: 锁定时间

r: 返回记录

t: 查询时间

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间（默认方式）

ac:平均查询次数

-t: 即为返回前面多少条的数据；

-g: 后边搭配一个正则匹配模式，大小写不敏感的；

show variables like "%slow%"; // 查询慢查询日志设置

　show status like "%slow%"; -- 查询慢查询SQL状况;
　show variables like "long_query_time"; -- 慢查询时间

mysql配置文件 /etc/my.cnf

slow_query_log = on -- 开启日志;
slow_query_log_file = /data/f/mysql_slow_cw.log -- 记录日志的log文件; 注意:window上必须写绝对路径,比如 D:/wamp/bin/mysql/mysql5.5.16/data/show-slow.log
long_query_time = 2 -- 最长查询的秒数;
log-queries-not-using-indexes -- 表示记录没有使用索引的查询


mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log //按照查询时间排序，查看查的最慢的前五条 SQL 语句

　　#得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

　　#得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

　　#得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

　　#另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现爆屏情况

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

分析查询语句：EXPLAIN

show variables like 'profiling'; //查看 SQL 执行成本：SHOW PROFILE

set profiling = 'ON'; //设置 profiling='ON’ 来开启 show profile

show profiles; //执行相关的查询语句。接着看下当前会话都有哪些 profiles

show profile; //查看最近一次查询的开销

show profile cpu,block io for query 2; //查询编号2的sql开销

EXPLAIN SELECT select_options；

EXPLAIN 各列

数据表，s1、s2 相同的

 1 CREATE TABLE s1 (
 2 id INT AUTO_INCREMENT,
 3 key1 VARCHAR(100),
 4 key2 INT,
 5 key3 VARCHAR(100),
 6 key_part1 VARCHAR(100),
 7 key_part2 VARCHAR(100),
 8 key_part3 VARCHAR(100),
 9 common_field VARCHAR(100),
10 PRIMARY KEY (id),
11 INDEX idx_key1 (key1),
12 UNIQUE INDEX idx_key2 (key2),
13 INDEX idx_key3 (key3),
14 INDEX idx_key_part(key_part1, key_part2, key_part3)
15 ) ENGINE=INNODB CHARSET=utf8;

table：表名

#1. table：表名
#查询的每一行记录都对应着一个单表
EXPLAIN SELECT * FROM s1;

SHOW INDEX FROM s1;

#s1:驱动表 s2:被驱动表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id

id如果相同，可以认为是一组，从上往下顺序执行

在所有组中，id值越大，优先级越高，越先执行

关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

 1 #2. id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
 2  SELECT * FROM s1 WHERE key1 = 'a';
 3 
 4 
 5  SELECT * FROM s1 INNER JOIN s2
 6  ON s1.key1 = s2.key1
 7  WHERE s1.common_field = 'a';
 8 
 9 
10  SELECT * FROM s1 
11  WHERE key1 IN (SELECT key3 FROM s2);
12 
13 
14  SELECT * FROM s1 UNION SELECT * FROM s2;
15 
16 
17  EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
18  
19  
20  EXPLAIN SELECT * FROM s1 INNER JOIN s2;
21  
22  
23  EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
24  
25  ######查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作########
26  EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');
27  
28  #Union去重
29  EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
30  
31  
32  EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;

select_type：SELECT关键字对应的那个查询的类型,确定小查询在整个大查询中扮演了一个什么角色

 1 #3. select_type：SELECT关键字对应的那个查询的类型,确定小查询在整个大查询中扮演了一个什么角色
 2  
 3  # 查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型
 4  EXPLAIN SELECT * FROM s1;
 5  
 6  
 7  #连接查询也算是`SIMPLE`类型
 8  EXPLAIN SELECT * FROM s1 INNER JOIN s2;
 9  
10  
11  #对于包含`UNION`或者`UNION ALL`或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个
12  #查询的`select_type`值就是`PRIMARY`
13  
14  
15  #对于包含`UNION`或者`UNION ALL`的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询
16  #以外，其余的小查询的`select_type`值就是`UNION`
17  
18  #`MySQL`选择使用临时表来完成`UNION`查询的去重工作，针对该临时表的查询的`select_type`就是
19  #`UNION RESULT`
20  EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
21  
22  EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
23  
24  #子查询：
25  #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是不相关子查询。
26  #该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`SUBQUERY`
27  EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
28  
29  
30  #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是相关子查询，
31  #则该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`DEPENDENT SUBQUERY`
32  EXPLAIN SELECT * FROM s1 
33  WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
34  #注意的是，select_type为`DEPENDENT SUBQUERY`的查询可能会被执行多次。
35  
36  
37  #在包含`UNION`或者`UNION ALL`的大查询中，如果各个小查询都依赖于外层查询的话，那除了
38  #最左边的那个小查询之外，其余的小查询的`select_type`的值就是`DEPENDENT UNION`。
39  EXPLAIN SELECT * FROM s1 
40  WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');
41  
42  
43  #对于包含`派生表`的查询，该派生表对应的子查询的`select_type`就是`DERIVED`
44  EXPLAIN SELECT * 
45  FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;
46  
47  
48  #当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，
49  #该子查询对应的`select_type`属性就是`MATERIALIZED`
50  EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); #子查询被转为了物化表

partition：匹配的分区信息

type：针对单表的访问方法

小结：

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >

unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来（见上图中的蓝

色）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴

开发手册要求）

 1 # 5. type：针对单表的访问方法
 2  
 3  #当表中`只有一条记录`并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，
 4  #那么对该表的访问方法就是`system`。
 5  CREATE TABLE t(i INT) ENGINE=MYISAM;
 6  INSERT INTO t VALUES(1);
 7  
 8  EXPLAIN SELECT * FROM t;
 9  
10  #换成InnoDB    ALL
11  CREATE TABLE tt(i INT) ENGINE=INNODB;
12  INSERT INTO tt VALUES(1);
13  EXPLAIN SELECT * FROM tt;
14  
15  
16  #当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是`const`
17  EXPLAIN SELECT * FROM s1 WHERE id = 10005;
18  
19  EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;
20  
21  
22  #在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的
23  #（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则
24  #对该被驱动表的访问方法就是`eq_ref`
25  EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
26   
27   
28  #当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref`
29  EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
30  
31  
32  #当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法
33  #就可能是`ref_or_null`
34  EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
35  
36  
37  #单表访问方法时在某些场景下可以使用`Intersection`、`Union`、
38  #`Sort-Union`这三种索引合并的方式来执行查询
39  EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
40  
41  
42  #`unique_subquery`是针对在一些包含`IN`子查询的查询语句中，如果查询优化器决定将`IN`子查询
43  #转换为`EXISTS`子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的`type`
44  #列的值就是`unique_subquery`
45  EXPLAIN SELECT * FROM s1 
46  WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a';
47  
48  
49  #如果使用索引获取某些`范围区间`的记录，那么就可能使用到`range`访问方法
50  EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
51  
52  #同上
53  EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
54  
55  
56  #当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index`
57  EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
58  
59  
60  #最熟悉的全表扫描
61  EXPLAIN SELECT * FROM s1;

possible_keys和key：可能用到的索引和实际上使用的索引

key_len：实际使用到的索引长度(即：字节数)

 1 #7.  key_len：实际使用到的索引长度(即：字节数)
 2 # 帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。
 3  EXPLAIN SELECT * FROM s1 WHERE id = 10005;
 4 
 5 
 6  EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
 7 
 8 
 9  EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
10 
11 
12  EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
13 
14  
15  EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
16 
17  EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
18  
19  EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';
20  
21 #练习：
22 #varchar(10)变长字段且允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
23 
24 #varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
25 
26 #char(10)固定字段且允许NULL    = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
27 
28 #char(10)固定字段且不允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)

ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。

 1 # 8. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
 2  #比如只是一个常数或者是某个列。
 3  
 4  EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 5  
 6  
 7  EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
 8  
 9  
10  EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

rows：预估的需要读取的记录条数

1  # 9. rows：预估的需要读取的记录条数
2  # `值越小越好`
3  EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比

 1 # 10. filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比
 2  
 3  #如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用
 4  #到对应索引的搜索条件外的其他搜索条件的记录有多少条。
 5  EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
 6  
 7  
 8  #对于单表查询来说，这个filtered列的值没什么意义，我们`更关注在连接查询
 9  #中驱动表对应的执行计划记录的filtered值`，它决定了被驱动表要执行的次数(即：rows * filtered)
10  EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

Extra:一些额外的信息

 1 #11. Extra:一些额外的信息
 2  #更准确的理解MySQL到底将如何执行给定的查询语句
 3  
 4  
 5  #当查询语句的没有`FROM`子句时将会提示该额外信息
 6  EXPLAIN SELECT 1;
 7  
 8  
 9  #查询语句的`WHERE`子句永远为`FALSE`时将会提示该额外信息
10  EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
11  
12  
13  #当我们使用全表扫描来执行对某个表的查询，并且该语句的`WHERE`
14  #子句中有针对该表的搜索条件时，在`Extra`列中会提示上述额外信息。
15  EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
16  
17  
18  #当使用索引访问来执行对某个表的查询，并且该语句的`WHERE`子句中
19  #有除了该索引包含的列之外的其他搜索条件时，在`Extra`列中也会提示上述额外信息。
20  EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
21  
22  
23  #当查询列表处有`MIN`或者`MAX`聚合函数，但是并没有符合`WHERE`子句中
24  #的搜索条件的记录时，将会提示该额外信息
25  EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
26  
27  EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'NlPros'; #NlPros 是 s1表中key1字段真实存在的数据
28  
29  #select * from s1 limit 10;
30  
31  #当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以
32  #使用覆盖索引的情况下，在`Extra`列将会提示该额外信息。比方说下边这个查询中只
33  #需要用到`idx_key1`而不需要回表操作：  Using index
34  EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a';
35  
36  
37  #有些搜索条件中虽然出现了索引列，但却不能使用到索引
38  #看课件理解索引条件下推   Using index condition
39  EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
40  
41  
42  #在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为
43  #其分配一块名叫`join buffer`的内存块来加快查询速度，也就是我们所讲的`基于块的嵌套循环算法`
44  #见课件说明          Using join buffer (hash join)
45  EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
46  
47  
48  #当我们使用左（外）连接时，如果`WHERE`子句中包含要求被驱动表的某个列等于`NULL`值的搜索条件，
49  #而且那个列又是不允许存储`NULL`值的，那么在该表的执行计划的Extra列就会提示`Not exists`额外信息
50  EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
51  
52  
53  #如果执行计划的`Extra`列出现了`Using intersect(...)`提示，说明准备使用`Intersect`索引
54  #合并的方式执行查询，括号中的`...`表示需要进行索引合并的索引名称；
55  #如果出现了`Using union(...)`提示，说明准备使用`Union`索引合并的方式执行查询；
56  #出现了`Using sort_union(...)`提示，说明准备使用`Sort-Union`索引合并的方式执行查询。
57  EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
58  
59  
60  #当我们的`LIMIT`子句的参数为`0`时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息
61  EXPLAIN SELECT * FROM s1 LIMIT 0;
62  
63  
64  #有一些情况下对结果集中的记录进行排序是可以使用到索引的。
65  #比如：
66  EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
67  
68  
69  #很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）
70  #进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：`filesort`）。
71  
72  #如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的`Extra`列中显示`Using filesort`提示
73  EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
74  
75  
76  #在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们
77  #在执行许多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查询过程中，如果不能有效利用索引来完成
78  #查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行
79  #计划的`Extra`列将会显示`Using temporary`提示
80  EXPLAIN SELECT DISTINCT common_field FROM s1;
81  
82  #EXPLAIN SELECT DISTINCT key1 FROM s1;
83  
84  #同上。
85  EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
86  
87  #执行计划中出现`Using temporary`并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以
88  #我们`最好能使用索引来替代掉使用临时表`。比如：扫描指定的索引idx_key1即可
89  EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
90  
91 #json格式的explain
92 EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 
93 WHERE s1.common_field = 'a';

小结

EXPLAIN不考虑各种Cache

EXPLAIN不能显示MySQL在执行查询时所作的优化工作

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

部分统计信息是估算的，并非精确值

SHOW WARNINGS;

在explain语句之后紧接着使用，可以看到被优化后的sql语句，不过只在命令行有效。

分析优化器执行计划：trace

1 SET optimizer_trace="enabled=on",end_markers_in_json=on;
2 set optimizer_trace_max_mem_size=1000000;

Sys schema视图

1. 主机相关：以host_summary开头，主要汇总了IO延迟的信息。

2. Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。

3. I/o相关：以io开头，汇总了等待I/O、I/O使用量情况。

4. 内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况

5. 连接与会话信息：processlist和session相关视图，总结了会话相关信息。

6. 表相关：以schema_table开头的视图，展示了表的统计信息。

7. 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。

8. 语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。

9. 用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。

10. 等待事件相关信息：以wait开头，展示等待事件的延迟情况。

索引情况

1 #1. 查询冗余索引
2 select * from sys.schema_redundant_indexes;
3 #2. 查询未使用过的索引
4 select * from sys.schema_unused_indexes;
5 #3. 查询索引的使用情况
6 select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
7 from sys.schema_index_statistics where table_schema='dbname' ;

表相关

1 # 1. 查询表的访问量
2 select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
3 sys.schema_table_statistics group by table_schema,table_name order by io desc;
4 # 2. 查询占用bufferpool较多的表
5 select object_schema,object_name,allocated,data
6 from sys.innodb_buffer_stats_by_table order by allocated limit 10;
7 # 3. 查看表的全表扫描情况
8 select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

 1 #1. 监控SQL执行的频率
 2 select db,exec_count,query from sys.statement_analysis
 3 order by exec_count desc;
 4 #2. 监控使用了排序的SQL
 5 select db,exec_count,first_seen,last_seen,query
 6 from sys.statements_with_sorting limit 1;
 7 #3. 监控使用了临时表或者磁盘临时表的SQL
 8 select db,exec_count,tmp_tables,tmp_disk_tables,query
 9 from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
10 order by (tmp_tables+tmp_disk_tables) desc;

IO相关

1 #1. 查看消耗磁盘IO的文件
2 select file,avg_read,avg_write,avg_read+avg_write as avg_io
3 from sys.io_global_by_file_by_bytes order by avg_read limit 10;
4 Innodb 相关

Innodb 相关

1 #1. 行锁阻塞情况
2 select * from sys.innodb_lock_waits;

posted @ 2023-10-16 09:15 长寿奉孝阅读(196) 评论(0) 编辑收藏举报

刷新页面返回顶部

TYT

MySQL 创建索引的原则，优化的思路

康师傅yyds

创建索引的原则

适合创建索引的情况

1、字段的数值有唯一性的限制

2、频繁作为 WHERE 查询条件的字段

3. 经常 GROUP BY 和 ORDER BY 的列

4、UPDATE、DELETE 的 WHERE 条件列

5.DISTINCT 字段需要创建索引

6. 多表 JOIN 连接操作时，创建索引注意事项

7. 使用列的类型小的创建索引

8. 使用字符串前缀创建索引

9. 区分度高(散列性高)的列适合作为索引

10. 使用最频繁的列放到联合索引的左侧

11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

不适合创建索引的情况

where不到的字段不创建

重复数据太多的不创建（如不要对 is_delete 字段创建索引）

数据量小的表不要创建索引

经常更新操作的表不要创建太多索引

不建议用无须的字段撞见索引

不要定义冗余或重复的索引

SQL优化的思路

慢查询分析工具mysqldumpslow

分析查询语句：EXPLAIN

EXPLAIN 各列

table：表名

id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id

select_type：SELECT关键字对应的那个查询的类型,确定小查询在整个大查询中扮演了一个什么角色

partition：匹配的分区信息

type：针对单表的访问方法

possible_keys和key：可能用到的索引 和 实际上使用的索引

key_len：实际使用到的索引长度(即：字节数)

ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。

rows：预估的需要读取的记录条数

filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比

Extra:一些额外的信息

SHOW WARNINGS;

分析优化器执行计划：trace

Sys schema视图

公告

　　　　1、字段的数值有唯一性的限制

　　　　2、频繁作为 WHERE 查询条件的字段

　　　　3. 经常 GROUP BY 和 ORDER BY 的列

　　　　4、UPDATE、DELETE 的 WHERE 条件列

　　　　5.DISTINCT 字段需要创建索引

　　　　6. 多表 JOIN 连接操作时，创建索引注意事项

　　　　7. 使用列的类型小的创建索引

　　　　8. 使用字符串前缀创建索引

　　　　9. 区分度高(散列性高)的列适合作为索引　　　　　　

　　　　10. 使用最频繁的列放到联合索引的左侧

　　　　11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

　　　　where不到的字段不创建

　　　　重复数据太多的不创建（如不要对 is_delete 字段创建索引）

　　　　数据量小的表不要创建索引

　　　　经常更新操作的表不要创建太多索引

　　　　不建议用无须的字段撞见索引

　　　　不要定义冗余或重复的索引

possible_keys和key：可能用到的索引和实际上使用的索引