MySql普通索引的管理

手机游戏开发者 · 2024-9-18 03:09:20

一、普通索引的使用规则：

一个表中可以有多个index
字段的值可以重复，且可以赋值为null
通常在where条件中的字段上配置Index
index索引字段的标志为MUL

二、普通索引的语法结构：

添加索引(新表)

CREATE TABLE  库.表(
字段列表 ,
INDEX(字段名) ,
INDEX(字段名)
);
添加索引(旧表)

CREATE  INDEX 索引名  ON  库.表(字段名)
删除索引

DROP  INDEX 索引名 ON  库.表；
查看索引详细信息

show index from 库.表；

三、explain命令可以查看执行的查询select语句，是否使用到索引做查询了

不添加索引查询

# 所有表头都没有MUL 标记mysql> desc db1.t3; +----------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+--------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | char(50) | YES | | NULL | || password | char(1) | YES | | NULL | || uid | int(11) | YES | | NULL | || gid | int(11) | YES | | NULL | || comment | varchar(200) | YES | | NULL | || homedir | varchar(60) | YES | | NULL | || shell | varchar(30) | YES | | NULL | |+----------+--------------+------+-----+---------+----------------+8 rows in set (0.00 sec) # 查看表的总行数mysql> select count(*) from db1.t3; +----------+| count(*) |+----------+| 22 |+----------+1 row in set (0.00 sec)# 查找名字叫sshd的用户mysql> select * from db1.t3 where name="sshd";+----+------+----------+------+------+-------------------------+-----------------+---------------+| id | name | password | uid | gid | comment | homedir | shell |+----+------+----------+------+------+-------------------------+-----------------+---------------+| 18 | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |+----+------+----------+------+------+-------------------------+-----------------+---------------+1 row in set (0.00 sec)# 查看（key列、 rows列）使用没有索引的表头做查询条件找1条记录也要遍历整张表的所有行mysql> explain select * from db1.t3 where name="sshd"; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 22 | 10.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

比对添加索引查询

# 给db1库下的t3表的name表头下的数据创建排队信息索引名叫xingmingmysql> create index xingming on db1.t3(name) ; mysql> desc db1.t3;+----------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+--------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | char(50) | YES | MUL | NULL | || password | char(1) | YES | | NULL | || uid | int(11) | YES | | NULL | || gid | int(11) | YES | | NULL | || comment | varchar(200) | YES | | NULL | || homedir | varchar(60) | YES | | NULL | || shell | varchar(30) | YES | | NULL | |+----------+--------------+------+-----+---------+----------------+8 rows in set (0.00 sec)# 查看(key列、 rows列)不会遍历所有行 mysql> explain select * from db1.t3 where name="sshd"; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| 1 | SIMPLE | t3 | NULL | ref | xingming | xingming | 51 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql> select * from db1.t3 where name="sshd";+----+------+----------+------+------+-------------------------+-----------------+---------------+| id | name | password | uid | gid | comment | homedir | shell |+----+------+----------+------+------+-------------------------+-----------------+---------------+| 18 | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |+----+------+----------+------+------+-------------------------+-----------------+---------------+1 row in set (0.00 sec)

MySql普通索引的管理

所属分类: 问答交流

新帖推荐: 30日

推荐作品