The difference between using an index and not using an index in MySQL

To answer the index's impact on database performance, a simple experiment was done.

Test environment

MySQL 5.7.10 InnoDB 1 million random data

Table structure

CREATE TABLE ' table1 ' (  ' id ' int (one) unsigned not null auto_increment,  ' col1 ' varchar () DEFAULT NULL,  PRI MARY KEY (' id ')) engine=innodb auto_increment=1000000 DEFAULT Charset=utf8;

Generate random data and insert data

Call Insert_calues (10000) is executed in MYQL;

CREATE  FUNCTION ' rand_string ' (n INT) RETURNS varchar (CHARSET) utf8begin    DECLARE chars_str varchar (100) DEFAULT ' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ';    DECLARE return_str VARCHAR (+) DEFAULT ';    DECLARE i INT DEFAULT 0;    While I < n does        SET return_str = concat (return_str,substring (CHARS_STR, floor (1 + RAND () *62), 1));        SET i = i +1;    END while;    RETURN Return_str; END;

DELIMITER;; CREATE  PROCEDURE ' insert_values ' (M INT) BEGIN  SET @i=1; While @i<m does  INSERT into  TABLE1 (col1) SELECT rand_string (ten);   SET @[email protected]+1; END while; END;;D Elimiter;

Test method

Shell concurrency Query

Add indexes and test

ALTER TABLE table1 ADD INDEX col1 (col1 (10));

#time bash    sql_test.shtime:0real    0m0.313suser    0m0.308ssys     0m0.129s

Delete index and test alter TABLE table1 DROP index col1;

#time bash    sql_test.shtime:10real    0m9.989suser    0m0.322ssys     0m0.130s

The sql_test.sh content used in the test is as follows

Trap "exec 203>&-;exec 203<&-;exit 0" 2mkfifo 203fifoexec 203<>203fiform-rf 203fifo# Control the number of concurrent, such as n<= 20 for concurrent 20 times for ((n=1;n<=20;n++))    does    echo >&203donestart= ' Date "+%s" ' for i ' {1..100};    Do            read-u203            {                   key=$ (OpenSSL rand-base64 9)                mysql test1-e ' select Sql_no_cache ID from table1 where co L1 = "${key}" '                  echo >&203            }& donewaitend= ' date ' +%s ' echo ' time: ' Expr $end-$start ' "Exec 203>& Amp;-exec 203<&-

Summarize

When using indexes, SQL performance can be greatly improved.

The difference between using an index and not using an index in MySQL