mysql 索引

访问量:1407 创建时间:2021-04-22

索引概述

索引index是帮助mysql高效获取数据的数据结构（有序），在数据之外数据库还维护着满足特定查询算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在数据结构上实现高效的查找算法，这种数据结构就是索引。

索引的优势和劣势

有点	缺点
1类似于书籍的陌路索引，提高数据检索效率，降低数据库io成本	实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间。
通过索引对列数据进行排序，降低数据排序的成本，降低CPU的消耗	大大提高查询效率，降低了更新表的速度，比如对表insert,update,delete。因为更新表时，mysql不仅要更新数据，还要保存索引文件每次更新添加的索引列的字段，更新会导致索引信息的变化和维护。

索引的结构

BTREE树和二叉树相比，查询数据的效率更高，因此对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

btree结构参考https://www.jianshu.com/p/ac12d2c83708

索引分类

单值索引：既一个索引包含单个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但允许有多个空值。
复合索引：既一个索引包含多个列。

索引语法

索引在创建表的时候可以同时创建，也可以随时增加新的索引。

准备环境：

create database demo_01 default charset =utf8mb4;
use demo_01;
create table city(
    city_id int(11) not null auto_increment,
    city_name varchar(50) not null,
    country_id int(11) not null ,
    primary key (city_id)
)engine = InnoDB default charset = utf8;

create table country(
    country_id int(11) not null auto_increment,
    country_name varchar(100) not null,
    primary key (country_id)
) engine = InnoDB default charset = utf8;

insert into city values (1,'西安',1);
insert into city values (2,'NewYork',2);
insert into city values (3,'北京',1);
insert into city values (4,'上海',1);

insert into country values (1,'china');
insert into country values (2,'America');
insert into country values (3,'Japan');
insert into country values (4,'UK');

创建索引语法

create [unique|fulltext|spatial] index index_name
[using index_type]
on tbl_name(index_col_name,...)
index_col_name :column_name[(kength)][ASC|DESC]
####示例：(mysql数据库建表，默认主键就是主键索引),不明确指定索引类型，是btree索引
mysql> create index idx_city_name on city(city_name);
Query OK, 0 rows affected (1.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看索引

mysql> show index from city;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| city  |          0 | PRIMARY       |            1 | city_id     | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| city  |          1 | idx_city_name |            1 | city_name   | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

删除索引

mysql> drop index idx_city_name on city;
Query OK, 0 rows affected (0.13 sec)

ALTER命令修改表--修改索引

#下面语句添加一个主键，这意味着索引值必须是唯一的，且不能为null
1. alter table tb_name add primary key(column_list);
#下面语句创建索引的值必须是唯一的(除了NULL外，NULL可能出现多次)
2. alter table tb_name add unique index_name(column_list);
#下面添加普通索引，索引值可以多次出现。
3. alter table tb_name add index index_name(column_list);
#该语句指定了索引为FULLTEXT,用于全文索引。
4. alter table tb_name add fulltext index_name(column_list);

索引设计原则

#创建复合索引
create index idx_name_email_status on tb_name(name,email,status);
##相当于
对 name创建索引；
对name, email创建了索引；
对name,email,status创建了索引；

复合索引

环境准备

create table `tb_seller`(
    `sellerid` varchar(100),
    `name` varchar(100),
    `nickname` varchar(50),
    `password` varchar(60),
    `status` varchar(1),
    `address` varchar(100),
    `createtime` datetime,
    primary key (sellerid)
)engine = innodb default charset = utf8;

insert into `tb_seller`(`sellerid`,`name`,`nickname`,password,status,address,createtime) values('alibaba','阿里巴巴','阿里小店','3t4grgw3rr','1','北京市','2088-01-02 12:00:00')
insert into `tb_seller`(`sellerid`,`name`,`nickname`,password,status,address,createtime) values('baidu','百度科技','百度','3t4grgw34r','1','北京市','2088-01-02 12:00:00')
insert into `tb_seller`(`sellerid`,`name`,`nickname`,password,status,address,createtime) values('huawei','华为科技','华为','3t23gw3rr','1','北京市','2088-01-02 12:00:00')
insert into `tb_seller`(`sellerid`,`name`,`nickname`,password,status,address,createtime) values('jd','京东数科','京东','3t4gfw3rr','1','北京市','2088-01-02 12:00:00')
insert into `tb_seller`(`sellerid`,`name`,`nickname`,password,status,address,createtime) values('bingmayong','兵马俑','兵马俑','3t4gserfw3rr','0','西安市','2088-01-02 12:00:00')
insert into `tb_seller`(`sellerid`,`name`,`nickname`,password,status,address,createtime) values('xiaomi','小米科技','小米','3t4grwrr','1','西安市','2088-01-03 12:00:00')
insert into `tb_seller`(`sellerid`,`name`,`nickname`,password,status,address,createtime) values('lianjia','链家','链家','3t4grwrr','2','北京市','2088-01-03 12:00:00')
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

全值匹配，对索引中所有的列都指定具体指

mysql> explain select * from tb_seller where name='兵马俑' and status='0' and address='西安市';

+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 612     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (2.01 sec)

最左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

###
mysql> explain select * from tb_seller where name='兵马俑';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
mysql> explain select * from tb_seller where name='兵马俑' and status='0';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 309     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
###不使用name字段
mysql> explain select * from tb_seller where  status='0' and address='西安市';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引失效：范围查询，字段运算。

#范围查询，生效的索引为name和status 。address索引失效
mysql> explain select * from tb_seller where name='兵马俑' and status>'0' and address='西安市';
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_seller | NULL       | range | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 309     | NULL |    1 |    14.29 | Using index condition |
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
###在列上进行运算操作，不走索引
mysql> explain select * from tb_seller where substring(name,3,2)='科技';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+-

字符串不加单引号索引失效

#key_len = 303只有name生效
mysql> explain select * from tb_seller where name='兵马俑' and status=0;
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 303     | const |    1 |    14.29 | Using index condition |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
#key_len =309， 2个列索引都生效
mysql> explain select * from tb_seller where name='兵马俑' and status='0';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 309     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+

尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列))，避免select*；如果查询列超出所以列，也会降低性能。

#Extra信息为Using index condition 表示使用了索引但是要回表查询(索引只记录了name的数据没有记录整行数据(8版本extra为NULL))
mysql> explain select * from tb_seller where name='兵马俑';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 303     | const |    1 |   100.00 | NULL(网上教程mysql低版本这里显示Using index condition)  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#Extra信息Using index 表示不需要回表查询
mysql> explain select name from tb_seller where name='兵马俑';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 303     | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------------+

Extra信息	含义
using index	使用覆盖索引的时候出现
using where	查询使用索引，需要回表查询所需的数据
using index condition	使用了索引，但是需要回表查询数据
using index;using where	使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

#name字段是索引列，而createtime不是索引列，中间是or连接，不走索引（and走索引）
mysql> explain select * from  tb_seller where name='兵马俑' or createtime='2088-01-03 12:00:00';
+----+-------------+-----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys            | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | idx_seller_name_sta_addr | NULL | NULL    | NULL |    7 |    26.53 | Using where |
+----+-------------+-----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+

以%开头的Like模糊查询，索引失效，如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效(可以通过覆盖索引，只查询索引列的值走索引查询)。

mysql>  explain select * from  tb_seller where name like '兵%';
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_seller | NULL       | range | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 303     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
mysql>  explain select * from  tb_seller where name like '%科技';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

如果Mysql评估使用索引比全表更慢，则不使用索引(少的走索引，多的走全表扫描)

is null ,is not null 有时走索引(少的走索引，多的走全表扫描)

in走索引，not in索引失效(在mysql8没看出效果)

###下面仅供参考，mysql8和老版本貌似结果不一样
mysql> explain select * from tb_seller where sellerid in ('bingmayong','xiaomi');
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | range | PRIMARY       | PRIMARY | 302     | NULL |    2 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (1.98 sec)

mysql> explain select * from tb_seller where sellerid not in ('bingmayong','xiaomi');
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | range | PRIMARY       | PRIMARY | 302     | NULL |    7 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

单列索引和复合索引，尽量使用复合索引，而少使用单列索引。

##创建复合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
相当于创建了三个索引： name, name+status,name+status+address
###
create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);
##创建单列索引，数据库会选择**一个**最优的索引来使用，并不会使用全部索引。

查看索引的使用情况

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 8     |
| Handler_read_key      | 79    |
| Handler_read_last     | 0     |
| Handler_read_next     | 81    |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 32    |
+-----------------------+-------+
7 rows in set (0.61 sec)
mysql> show global status like 'Handler_read%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Handler_read_first    | 4185   |
| Handler_read_key      | 55943  |
| Handler_read_last     | 1      |
| Handler_read_next     | 60795  |
| Handler_read_prev     | 5      |
| Handler_read_rnd      | 3182   |
| Handler_read_rnd_next | 175077 |
+-----------------------+--------+
7 rows in set (0.07 sec)

变量	解释
Handler_read_first	索引中第一条被读的次数，如果较高，表示服务器正在执行大量的全索引扫描（这个值越低越好）
Handler_read_key	如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用(这个值越高越好)
Handler_read_next	按照键顺序读下一行的请求数，如果你用范围约束或如果执行索引扫描来查询索引列，该值增加
Handler_read_prev	按照键顺序读前一行的请求数，该读方法主要用于优化order by ... desc 。
Handler_read_rnd	根据固定位置读一行的请求数，如果你正执行大量查询并需要对结果进行排序该值较高，你可能使用了大量需要Mysql扫描整个表的查询，或你的连接没有正确使用键，这个值越高，意味着运行效率低，应该建立索引来补救。

<< 上一章节

下一章节 >>

登陆评论: 使用GITHUB登陆