mysql索引一

表

| salary | CREATE TABLE `salary` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `basesalary` int(11) DEFAULT NULL,
  `titlesalary` int(11) DEFAULT NULL,
  `deduction` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `name1` int(11) DEFAULT NULL,
  `name2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`empid`),
  KEY `bs_index` (`basesalary`),
  KEY `name_index` (`name`),
  KEY `index_name1And2` (`name1`,`name2`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |

数据

+-------+------------+-------------+-----------+------+-------+
| empid | basesalary | titlesalary | deduction | name | name1 |
+-------+------------+-------------+-----------+------+-------+
|     1 |       2100 |         700 |       500 | NULL |  NULL |
|     2 |       1999 |         100 |       100 | NULL |  NULL |
|     3 |       1100 |         200 |       100 | NULL |  NULL |
|     4 |       2100 |         100 |       300 | NULL |  NULL |
|     5 |       1900 |         100 |       223 | NULL |  NULL |
|     6 |       2000 |         200 |        22 | NULL |  NULL |
|     7 |        200 |           0 |         0 | NULL |  NULL |
+-------+------------+-------------+-----------+------+-------+

一、 MyISAM和InnoDB的区别

数据存储⽅式：
• InnoDB由两种⽂件组成，表结构，数据和索引
• MyISAM由三种⽂件组成，表结构、数据、索引
• 索引的⽅式：
• 索引的底层都是基于B+Tree的数据结构建⽴
• InnoDB中主键索引为聚簇索引，辅助索引是⾮聚簇索引
• MyISAM中数据和索引存在不同的⽂件中，因此都是⾮聚簇索引
• 事务的⽀持：
• InnoDB⽀持事务
• MyISAM不⽀持事务

二、 一些索引失效情况

1.在字段上加了条件

例：

explain select * from salary where basesalary+1=100;

结果是

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | salary | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

key为NULL,说明没有走索引

2.字段类型不匹配可能会导致不走索引

①字段name是varchar类型，查询时传入的是int类型：

explain select * from salary where name=100;

结果：

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | salary | NULL       | ALL  | name_index    | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

没走索引
②字段name是varchar类型，查询时传入的也是varchar类型：

 explain select * from salary where name='100';

结果：

+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | salary | NULL       | ref  | name_index    | name_index | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+

走了索引

3.like语句导致索引失效

①当%通配符放在开头会导致索引失效
例：

explain select * from salary where name like '%100';

结果：

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | salary | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

未走索引
②当%不放在开头走索引
例：

explain select * from salary where name like '100%';

结果：

+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | salary | NULL       | range | name_index    | name_index | 63      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+----------------------

走了索引

4. 组合索引没有按照最左匹配原则

索引：

 KEY `index_name1And2` (`name1`,`name2`)

例：①

explain select * from salary where name2='12';

结果：

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | salary | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

没走索引
例②

explain select * from salary where name1='12' and name2='23';
或
explain select * from salary where name2='12' and name1='23';

结果：

+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | salary | NULL       | ref  | index_name1And2 | index_name1And2 | 68      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+

走了索引