Shulou(Shulou.com)05/31 Report--

This article mainly explains "what impact type conversion has on MySQL selection index". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn "what impact type conversion has on MySQL selection index" together!

I encountered several cases where MySQL did not use the expected index. After reading some documents, I found that MySQL's type conversion has a moderate impact on index selection.

For example, there is a MySQL table like this:

CREATE TABLE `indextest` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(10) DEFAULT NULL,

`age` tinyint(3) unsigned NOT NULL DEFAULT ’0′,

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

KEY `idx_name` (`name`),

KEY `idx_age` (`age`),

KEY `idx_create` (`create_time`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

name is an indexed varchar field, and the data in the table looks like this:

+--+--–+-–+-------+

| id | name | age | create_time |

+--+--–+-–+-------+

| 1 | hello | 10 | 2012-02-01 20:00:00 |

| 2 | world | 20 | 2012-02-02 20:00:00 |

| 3 | 111222 | 30 | 2012-02-03 20:00:00 |

| 4 | wow | 40 | 2012-02-04 20:00:00 |

+--+--–+-–+-------+

Query the name field with the string '111222' as an argument, Execution Plan uses the index idx_name on the name field as expected:

mysql [localhost] {msandbox} (test) > explain select age from

-> indextest where name=’111222′\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: indextest

type: ref

possible_keys: idx_name

key: idx_name

key_len: 13

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

When querying the name field with a number as an argument, explain indicates that this will be a full table scan:

mysql [localhost] {msandbox} (test) > explain select age from

-> indextest where name=111222\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: indextest

type: ALL

possible_keys: idx_name

key: NULL

key_len: NULL

ref: NULL

rows: 4

Extra: Using where

1 row in set (0.00 sec)

The reason for this is that when comparing text fields with numbers, MySQL needs to do implicit type conversion to compare them because of the different types, and the result is the same as mentioned in the example above.

MySQL's documentation (Type Conversion in Expression Evaluation) mentions that when making comparisons, the necessary type conversions are performed according to such rules:

When at least one of the two parameters is NULL, the result of the comparison is NULL, except that when comparing two NULL, it will return 1. In both cases, no type conversion is required.

Both parameters are strings and will be compared according to strings without type conversion

Both parameters are integers, compared by integers, without type conversion

Hexadecimal values are treated as binary strings when compared to non-digits, and numbers are treated as follows

One argument is TIMESTAMP or DATETIME, and the other argument is constant, which is converted to timestamp.

If one argument is decimal or integer, the integer is converted to decimal for comparison, and if the other argument is floating point, the decimal is converted to floating point for comparison.

In all other cases, both arguments are converted to floating-point numbers for comparison.

For example:

mysql [localhost] {msandbox} (test) > SELECT ’18015376320243459′ =

-> 18015376320243459;

+-------------–+

| ’18015376320243459′ = 18015376320243459 |

+-------------–+

| 0 |

+-------------–+

1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT ’18015376320243459′ + 0;

+---------+

| ’18015376320243459′ + 0 |

+---------+

| 1.80153763202435e+16 |

+---------+

1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT

-> cast(’18015376320243459′ as unsigned) = 18015376320243459;

+-------------------–+

| cast(’18015376320243459′ as unsigned) = 18015376320243459 |

+-------------------–+

| 1 |

+-------------------–+

1 row in set (0.00 sec)

Because of the floating-point precision (53 bits) problem, and because MySQL uses different methods for converting strings to floating-point numbers and integers to floating-point numbers, the string '18015376320243459' is not equal to the integer 18015376320243459.

According to these rules, for the above example, the value of the name field and the query parameter '111222' will be converted to floating point numbers for comparison, and many texts can be converted to values equal to 111222, such as '111222',' 111222aabb','111222' and '1112.2e1', so MySQL cannot use indexes effectively. It degenerates into index scans or even full table scans.

On the other hand, if you use a string as a query parameter to compare a numeric field, MySQL is an index that can be effectively used:

mysql [localhost] {msandbox} (test) > explain select name from

-> indextest where age=’30′\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: indextest

type: ref

possible_keys: idx_age

key: idx_age

key_len: 1

ref: const

rows: 1

Extra:

1 row in set (0.00 sec)

The reason is that MySQL can convert the query parameter '30' to a definite value of 30, and then quickly find its equivalent in the index.

In addition, using functions to explicitly type or calculate index fields will also prevent MySQL from using indexes:

mysql [localhost] {msandbox} (test) > explain select name from

-> indextest where cast(age as unsigned)=30\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: indextest

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 4

Extra: Using where

1 row in set (0.00 sec)

As above, using the cast function to explicitly type age will invalidate the index. Of course, this is rarely written in actual code, but it is more common to use the time field like the following:

mysql [localhost] {msandbox} (test) > explain select * from

-> indextest where date(create_time)=’2012-02-02′\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: indextest

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 4

Extra: Using where

1 row in set (0.00 sec)

For the requirements of this example, you want to find the record of the day create_time is 2012-02-02, and you can use the index effectively by avoiding the operation on the index field:

mysql [localhost] {msandbox} (test) > explain select * from

-> indextest where create_time between ’2012-02-02′ and ’2012-02-03′\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: indextest

type: range

possible_keys: idx_create

key: idx_create

key_len: 4

ref: NULL

rows: 1

Extra: Using where

1 row in set (0.00 sec)

Thank you for reading, the above is the content of "what impact type conversion has on MySQL selection index", after learning this article, I believe that you have a deeper understanding of what impact type conversion has on MySQL selection index, and the specific use needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.