MySQL之索引

老油条IT记

发布于 2020-03-22 10:47:42

6510

发布于 2020-03-22 10:47:42

1.索引相关概述　

索引就像书的目录一样，如果在字段上建立了索引，那么以索引为查询条件时可以加快查询数据的速度，这是对MySQL的优化

#索引的作用

约束和加速查找

#常见的索引

主键索引，普通索引，唯一索引，联合索引(多列)

#有无索引的区别

无索引：需要从前往后一条一条的查询

有索引：创建索引的时候，就会创建一个额外的文件，查询的时候，先去这个额外的文件找，定好位置，再去原始表中直接查询

2.主键索引

查询数据库，按主键查询速度是最快的，每个表只能有一个主键列，可以有多个普通索引列，主键列要求的所有内容必须唯一

#建立主键索引方法

#1.在建表时，增加建立主键索引

mysql> create table student (
    -> id int(4) not null auto_increment,
    -> name char(20) not null,
    -> age tinyint(4) not null,
    -> primary key(id),
    -> key index_name(name));
Query OK, 0 rows affected (0.02 sec)

mysql> show index from student;  #查看索引

#提示：
　　1.primary key(id)       #在id字段创建主键
　　2.key index_name(name)  #在name字段创建普通索引

#2.如果在创建表的时候没有添加主键，使用alter添加

#alter添加语法：alter table 表名 add primary key(列名)

mysql> alter table test add primary key(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(4)     | NO   | PRI | NULL    |       |
| name  | char(20)   | NO   |     | NULL    |       |
| age   | tinyint(2) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

#删除主键索引

#语法：alter table 表名 drop primary key;

　　　　alter table 表名 modify 列名 int,drop primary key;

mysql> alter table test drop primary key;  #删除主键索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test; #查看时已经没有了
Empty set (0.00 sec)

mysql> alter table test add primary key(id); #再次添加
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test modify id int,drop primary key; #删除主键索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test; #查看
Empty set (0.00 sec)

3.普通索引

#作用：加速查找

#语法格式：create index 索引名字 on 表名(列名)

　　　　　 alter table 表名 add index 索引名字(列名)

#查看帮助：help create index

mysql> help create index
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

Normally, you create all indexes on a table at the time the table
itself is created with CREATE TABLE. See [HELP CREATE TABLE]. This
guideline is especially important for InnoDB tables, where the primary
key determines the physical layout of rows in the data file. CREATE
INDEX enables you to add indexes to existing tables.

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.
See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY
KEY; use ALTER TABLE instead. For more information about indexes, see
https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html.

URL: https://dev.mysql.com/doc/refman/5.7/en/create-index.html

#创建普通索引

#1.创建表的时候就创建普通索引

mysql> create table student (
    -> id int(4) not null auto_increment,
    -> name char(20) not null,
    -> age tinyint(4) not null,
    -> primary key(id),
    -> key index_name(name));
Query OK, 0 rows affected (0.02 sec)

mysql> show index from student;  #查看索引

#2.如果建表的时候没有创建普通索引，可以使用以下方式创建

mysql> alter table student add index index_name(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index ix_age on student(age);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#删除普通索引

#语法：alter table 表名 drop index 索引名字；

　　　　drop index 索引名字 on 表名；

#例子：删除name和age字段索引

mysql> alter table student drop index index_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index ix_age on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.唯一索引

#作用：加速查找和唯一约束

#语法：create unique index 索引名 on 表名(列名)

#创建唯一索引

#1.在创建表的时候就创建唯一索引

mysql> create table test (
    -> id int(4) not null auto_increment,
    -> name char(20) not null,
    -> age varchar(4) not null,
    -> primary key(id),
    -> unique index ix_name(name)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show index from test; #查看索引

#2.如果建表时没有创建，使用create进行创建

mysql> create unique index ix_age on test(age);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test; #查看索引

#删除唯一索引

#语法：drop index 索引名字 on 表名；

mysql> drop index ix_age on test;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

5.联合索引

#联合索引是将n个列组合成一个索引

#应用场景：需要对多个列进行查询的时候，如：where name='guo' and age='20';

#语法：create index 索引名 on 表名(列名1，列名2)

#例子：为name,dept一起创建索引

mysql> create index ix_name_dept on test(name,dept);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#指定前n个字符创建索引

#例如1.指定前8个字符创建索引

mysql> create index ix_dept on test(dept(8));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | ix_dept  |            1 | dept        | A         |           0 |        8 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

#最左前缀

#如果使用联合索引，如将name和dept组合索引时，当查询name字段时会使用索引，查询name和dept时会使用索引，查询dept时不会使用索引，因为当查左边那个才会使用索引

create index ix_name_dept on test(name,dept);
select * from test where name='guo';  #使用索引
select * from test where name='guo' and dept = 'computer'; #使用索引
select * from test where dept = 'computer' #不使用索引

6.索引注意事项

  1.避免使用select *
  2.count(1)或count(列) 代替count(*)
  3.创建表时尽量使用char代替varchar
  4.表的字段顺序固定长度的字段优先
  5.组合索引代替多个单列索引（经常使用多个条件查询时）
  6.尽量使用短索引 （create index ix_title on tb(title(16));特殊的数据类型 text类型）

#思考：能不能给所有的列建索引呢？
    #答案是否定的，因为索引不但会占用系统空间，更新数据库时还需要维护索引数据，因此并不是越多越好，例如数十到几百行的小表上无需建立索引

7.执行计划explain

#使用explain判断有没有索引

#查看帮助：help explain

mysql> help explain
Name: 'EXPLAIN'
Description:
Syntax:
{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan
(that is, an explanation of how MySQL would execute a query).

URL: https://dev.mysql.com/doc/refman/5.7/en/explain.html

#查看有没有创建索引

mysql> explain select * from test where name='wu';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test where name='wu'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

#上面的name字段没有创建索引，所以查询的时候就会进行全表扫描，就会慢，如果有索引的时候就很快

#创建索引再次查找

mysql> create index ix_name on test(name); #创建索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from test where name='wu'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: ix_name
          key: ix_name
      key_len: 20
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

#当创建了索引之后，这次就扫描了一行

#参数详解

select_type：
                查询类型
                    SIMPLE          简单查询
                    PRIMARY         最外层查询
                    SUBQUERY        映射为子查询
                    DERIVED         子查询
                    UNION           联合
                    UNION RESULT    使用联合的结果
table：
                正在访问的表名
type：
                查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
                ALL             全表扫描，对于数据表从头到尾找一遍
                                select * from userinfo;
                                特别的：如果有limit限制，则找到之后就不在继续向下扫描
                                       select * from userinfo where email = 'alex112@oldboy'
                                       select * from userinfo where email = 'alex112@oldboy' limit 1;
                                       虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。

INDEX ：      全索引扫描，对索引从头到尾找一遍
                                select nid from userinfo;

RANGE：        对索引列进行范围查找
                                select *  from userinfo where name < 'alex';
                                PS:
                                    between and
                                    in
                                    >   >=  <   <=  操作
                                    注意：!= 和 > 符号


INDEX_MERGE：  合并索引，使用多个单列索引搜索
                                select *  from userinfo where name = 'alex' or nid in (11,22,33);

REF：       根据索引查找一个或多个值
                                select *  from userinfo where name = 'alex112';

EQ_REF：    连接时使用primary key 或 unique类型
                                select userinfo2.id,userinfo.name from userinfo2 left join tuserinfo on userinfo2.id = userinfo.id;



CONST：常量
            表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
                 select id from userinfo where id = 2 ;

SYSTEM：系统
             表仅有一行(=系统表)。这是const联接类型的一个特例。
                 select * from (select id from userinfo where id = 1) as A;


possible_keys：可能使用的索引

key：真实使用的

key_len：　　MySQL中使用索引字节长度

rows： mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值

extra：
                该列包含MySQL解决查询的详细信息
                “Using index”
                    此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
                “Using where”
                    这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
                “Using temporary”
                    这意味着mysql在对查询结果排序时会使用一个临时表。
                “Using filesort”
                    这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
                “Range checked for each record(index map: N)”
                    这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-03-02 ，如有侵权请联系 cloudcommunity@tencent.com 删除

add