mysql count(*) count count(id) explanation

Environment wins10 8g memory mysql5.7.27

mysql my.ini Configuration note (innodb_flush_log_at_trx_commit=0) configuration

[Client]
#Set 3306 port
port = 3306
[mysqld]
#Set 3306 port
port = 3306
# Set the installation directory of mysql
basedir=E:devmysql-5.7.27-winx64
# Set the storage directory of mysql database data
datadir=E:devmysql-5.7.27-winx64data
# Maximum connections allowed
max_connections=200
# The character set used by the server defaults to the 8-bit latin1 character set
character-set-server=utf8
# Default storage engine to use when creating new tables
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Set to 0 when inserting data to improve the inserting data, and modify the inserting data to 1 after completion
innodb_flush_log_at_trx_commit=0  

[mysql]
# Set mysql client default character set
default-character-set=utf8

1. Create table

CREATE TABLE `test_count` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `test1` varchar(100) DEFAULT NULL,
  `test2` varchar(100) DEFAULT NULL,
  `test3` varchar(100) DEFAULT NULL,
  `test4` varchar(100) DEFAULT NULL,
  `test5` varchar(100) DEFAULT NULL,
  `test6` varchar(100) DEFAULT NULL,
  `test10` varchar(100) DEFAULT NULL,
  `test11` varchar(100) DEFAULT NULL,
  `test12` varchar(100) DEFAULT NULL,
  `test13` varchar(200) DEFAULT NULL,
  `test14` varchar(200) DEFAULT NULL,
  `test15` varchar(200) DEFAULT NULL,
  `test16` varchar(200) DEFAULT NULL,
  `test17` varchar(200) DEFAULT NULL,
  `test18` varchar(200) DEFAULT NULL,
  `test19` varchar(200) DEFAULT NULL,
  `test20` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

2. Create stored procedure

CREATE  PROCEDURE `test_count_dure`(IN n int)
BEGIN  
  DECLARE i INT DEFAULT 1;
    WHILE (i <= n ) DO
      INSERT into test_count  (name,age,create_time,test1,test2,test3,test4,test5,test6,test10,test11,test12,test13,test14,test15,test16,test17,test18,test19,test20 ) VALUEs 
															(i,100 ,now(),'213dfdsjfdasfdsdafdffsd',
'213dfdsjfdasfdsdafdffsd','213dfdsjfdasfdsdafdffsd','213dfdsjfdasfdsdafdffsd'
,'213dfdsjfdasfdsdafdffsd','213dfdsjfdasfdsdafdffsd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd' );
			set i=i+1;
    END WHILE;
END

3. Create 1 million data

CALL test_count_dure(1000000);

4. Disk size occupied by view table

SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size,
CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size,
CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free,
CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size
FROM information_schema.tables WHERE TABLE_NAME = 'test_count';

5. Execute count

count(1) count(*) count(1) is basically 8 seconds

Force primary key as index time or 8 seconds

6. Add test_count secondary index

Add a normal index to name
CREATE TABLE `test_count` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `test1` varchar(100) DEFAULT NULL,
  `test2` varchar(100) DEFAULT NULL,
  `test3` varchar(100) DEFAULT NULL,
  `test4` varchar(100) DEFAULT NULL,
  `test5` varchar(100) DEFAULT NULL,
  `test6` varchar(100) DEFAULT NULL,
  `test10` varchar(100) DEFAULT NULL,
  `test11` varchar(100) DEFAULT NULL,
  `test12` varchar(100) DEFAULT NULL,
  `test13` varchar(200) DEFAULT NULL,
  `test14` varchar(200) DEFAULT NULL,
  `test15` varchar(200) DEFAULT NULL,
  `test16` varchar(200) DEFAULT NULL,
  `test17` varchar(200) DEFAULT NULL,
  `test18` varchar(200) DEFAULT NULL,
  `test19` varchar(200) DEFAULT NULL,
  `test20` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

7. Execute count(1) count(*) count(1) again

By multiple executions
count(*) count(1) execution time is 0.2-0.3 seconds
The execution time of count(id) is 0.3-0.4 seconds
Through the explain execution plan view, the secondary index name index is used to improve the query speed

8. The reason why the speed of adding a secondary index is faster through online query

1) General query

The use of primary key index in general query is the fastest, and the overall IO consumption is also the least. When using secondary index, since the primary key index should be located according to the secondary index first, and then the data file should be queried according to the primary key index, the IO consumption is greater than the primary key index

2) Statistical query

For statistical requirements, such as COUNT, the performance of using secondary index is higher than that of primary key index. Because MYSQL uses B + tree, the primary key index and data are stored in the leaf node of B + tree together. If the primary key index is used for statistics, the data file should be scanned every time. When there are many data fields and rows (large data file), the IO consumption is higher. While the secondary index only stores index data, and the index file is relatively small, so it does not need to search the data file, so the overall IO consumption is low

Count (*) = count (1) > = count (primarykey) > = count (non empty field) > = count (can be empty field)

Tags: Programming MySQL Database Stored Procedure

Posted on Sun, 31 May 2020 10:10:15 -0400 by cofey12681