MySql普通索引的管理

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

• 一个表中可以有多个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表头下的数据创建排队信息 索引名叫xingming
mysql>  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)