mysql 查看多少索引 mysql查看索引语句

转载

mob6454cc780924 2024-07-20 16:36:35

文章标签 mysql 查看多少索引 mysql 数据库 MySQL 慢查询 文章分类 MySQL 数据库

MySQL中的一张表可以支持多个索引，如果SQL语句中没有主动指定使用哪个索引，那么，MySQL会自己来确定使用哪个索引。由MySQL中的优化器负责选择索引。

RR隔离级别下，假设有如下表：

-- 建表
CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;
-- 通过存储过程插入数据
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

mysql 查看多少索引 mysql查看索引语句_MySQL

按顺序执行上述语句就会发现，MySQL用错了索引，导致了更长的执行时间。实验过程如下：

set long_query_time=0;
select * from t where a between 10000 and 20000; 
select * from t force index(a) where a between 10000 and 20000;

第一句，将慢查询日志的阈值设置为0，表示这个线程接下来的语句都会被记录到慢查询日志中。

第二句，session B的语句

第三句，在session B的基础上增加了force index(a)

慢查询日志如下：

mysql 查看多少索引 mysql查看索引语句_MySQL_02

从上图中可以看到，第二句扫描了10万行，走了全表扫描，执行时间40毫秒；第三句扫描了10001行，执行了21毫秒。

优化器选择索引时的判断标准主要有：扫描行数、是否使用临时表、是否排序等

MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”，一个索引上不同的值的个数，称之后“基数”（cardinality），基数越大，索引的区分度越好。可以通过语句：show index来查看一个索引的基数。如下图所示：

mysql 查看多少索引 mysql查看索引语句_mysql 查看多少索引_03

MySQL中索引的基数是通过采样统计得来的，采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后再乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变，所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

在MySQL中，有两种存储索引统计的方式，可以通过参数innodb_stats_persistent的值来选择：