This blog to undertake the last article “dry goods! SQL performance optimization, write high quality SQL statements”. Thank you very much here, very face-saving, comments section to give a lot of advice and guidance! In the last article, there was too much theoretical knowledge and no specific test cases were used to convince everyone. Therefore, this piece of dry goods sharing mainly answers the questions raised by digg friends in the last article and provides detailed test cases. If not, please comment ~

Database version mysql 5.7.26

1. Does the where statement use IN to run the index?

Start by creating a test table

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `name` varchar(255) NOT NULL,
  `class` varchar(255) DEFAULT NULL,
  `page` bigint(20) DEFAULT NULL,
  `status` tinyint(3) unsigned NOT NULL COMMENT 'Status: 0 normal, 1 frozen, 2 deleted',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4
Copy the code

Add 100w pieces of data in a batch

DELIMITER ;;
    CREATE PROCEDURE insertData()
    BEGIN
        declare i int;
        set i = 1 ;
        WHILE (i < 1000000) DO
            INSERT INTO student(`name`,class,`page`,`status`)
                VALUES(CONCAT('class_', i),
                    CONCAT('class_', i),
                i, (SELECT FLOOR(RAND() * 2)));
            set i = i + 1;
        END WHILE;
        commit;
		END;;
CALL insertData();
Copy the code

The name field is not indexed, and the test takes 0.8s

SELECT * FROM student WHERE `name` IN
('class_1'.'class_100'.'class_1000'.'class_100000');
Copy the code

After the name field is indexed, the test takes 0.021s

So we find that the WHERE statement uses IN to go indexed

But! If the data volume is too large (million level), such as estimated to be 80% of the full table, then is not to go index, will go full table scan!

Select * from student; select * from student

SELECT * FROM student WHERE `status` IN(0,2);
Copy the code

After the index is added, it is not indexed because of the large amount of data, as shown in the figure:

conclusion

MySql has been optimized for MySql 5.5 and later. IN version 5.5 IN 2010, the optimizer automatically optimizes the IN operator to use indexes for columns that have been indexed. Columns with no indexes will still be scanned in the full table, but if the data volume is large, such as an estimated 80% of the full table, it will be scanned in the full table!

2. Where clause IS NULL or IS NOT NULL

We tested it in three cases

  • Index column name defaults to NULL and is allowed to be NULL
  • Index column name defaults to ‘1’ and is allowed to be null
  • Index column name defaults to ‘1’ and cannot be null

1. The index column name is NULL by default

SELECT * FROM student WHERE `name` is not null
Copy the code

SELECT * FROM student WHERE `name` is null
Copy the code

IS NOT NULL does NOT use the index

2. The index column name defaults to ‘1’ and can be null

IS NOT NULL does NOT use the index

3. The index column name defaults to ‘1’ and cannot be empty

IS NOT NULL; IS NOT NULL

conclusion

Is not null does not use the index when the field is allowed to be null.