通常小型项目涉及数据量比较小,数据查询频率不高,索引通常会被忽略。但当数据量较大,涉及多个表,查询较为频繁时,我们需要对查询性能进行优化,此时,建立合适的索引确是主要的方法之一。
通常,mysql数据库查询有两种方式:
一种是:全表扫描;
另外一种是:利用数据表中建立的索引进行扫描。
索引可以提高查询效率,下面我们来举例说明如何做到的。
例如:有三个表,table1,table2, table3,分别只含有一列c1、c2、c3,每个表中分别含有N行记录,现要查询对应值相等的行。
SQL查询语句如下:
SELECT c1,c2,c3 FROM table1,table2, table3 WHERE c1=c2 AND c1=c3;
不建索引时,需要从遍历table1、table2、table3中的所有记录组合,查询次数为:N*N*N,如下图。
如果对三个表都建索引,那么从table1中的第一行记录中的c1的值开始,如值为value,通过table2的索引表,直接通过value的指针找到value在table2中的记录,然后同理通过table3的索引表,通过value的指针找到value在table3中的记录;接着继续查询table1中的下一行记录,重复上面的步骤。最终扫描次数为:N。如下图所示。
由以上可以看出,建立索引查询效率大大提高。
常用索引类型:
主键索引:系统自动创建的,唯一,并且列值不能为空。创建使用关键字,PRIMARY KEY;
唯一索引:索引列值必须唯一,不能重复,但可以为空值。创建使用关键字,UNIQUE;
普通索引:索引列值可以重复,可以取空值。创建使用关键字,INDEX或者KEY;
聚簇索引:就是数据存储的物理存储顺序,非聚簇索引就是索引顺序与数据的物理顺序无关。一个表只能有一个聚簇索引。目前只有InoDB和solidDB支持。
全文索引:只能创建在varchar或text的列上;建立全文索引能够在全文索引的列上进行查找。
(1)单列索引:一个索引只包含表中的一个列;比创建一个学号ID的索引;以name再创建一个姓名的单列索引。即每个索引包含一个列。
(2)组合索引(复合索引或多列索引):就是表中的两个列或多个列来创建成一个索引;比如;以用户ID、用户名Name、用户年龄Age来创建的索引就是联合索引。
索引SQL语句
# 创建索引
mysql> create index index_sname on student(sname);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
# 查询表索引
mysql> show index from student;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | sid | A | 3 | NULL | NULL | | BTREE | | |
| student | 1 | FK_CID | 1 | cid | A | 2 | NULL | NULL | YES | BTREE | | |
| student | 1 | index_sname | 1 | sname | A | 3 | NULL | NULL | YES | BTREE | | |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set
# 删除索引
mysql> drop index index_sname on student;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | sid | A | 3 | NULL | NULL | | BTREE | | |
| student | 1 | FK_CID | 1 | cid | A | 2 | NULL | NULL | YES | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)