一、普通索引的使用规则：
- 一个表中可以有多个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)
