I:mysql数据库索引类型
(1) 唯一索引:不允许表中任意两行具有相同值的索引。
(2) 非唯一索引:允许其中任何两行具有相同索引值的索引。
(3) 主键索引:数据库表中有一列或列组合,其值唯一标识表中的每一行。
(4) 聚集索引: 表中记录的物理顺序与索引的顺序一致。一张表只能有一个聚集索引。
(5) 非聚集索引:表中记录的物理顺序和索引的顺序不一致。
(6) 联合索引:又叫做复合索引,两个或多个列上的索引被称作复合索引。mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部分,但只能是最左侧部分。例如索引是key index(a,b,c),可以支持a|a,b|a,b,c三种组合进行查找,但不支持b,c进行查找。
II:唯一索引和主键索引的区别:一个表中只能有一个主键索引,而一个表中可以有多个唯一索引;主键可以被其他表引用为外键,而唯一索引不能。
III:索引实现原理:B+
IV:建立索引的优缺点:
优点:是可以加快数据的检索速度;
缺点:创建和维护索引需要耗费时间;除了数据表需要占用空间外,索引也需要占用空间;当对表中数据进行增加、删除和修改时,索引也要动态维护,这样就降低了数据的维护速度。
V:非聚集索引和聚集索引的区别:
聚集索引 | 非聚集索引 |
B+树实现 | B+树实现 |
叶子节点存储真实的数据 | 叶子节点存储指向真实数据的指针 |
表中记录的物理顺序与索引的顺序一致 | 非聚集索引:表中记录的物理顺序和索引的顺序不一致。 |
VI:会使索引失效的情况
(1)条件中有or,即使其中有条件带索引也不会使用(要想既使用or,又不想让索引失效,只能将or条件中每个列都加上索引)。解决方法:通过UNION连接多条select语句。
(2)like查询并且以%开头。解决方法:使用FULLTEXT(全文本搜索)代替like。
(3)若列类型为字符串,则一定要在条件中将数据用引号引起来,否则不使用索引。
(4)若mysql估计使用全表扫描比索引快,则不使用索引。
(5)对索引列进行运算导致索引列失效。
VII:应该在那些列上加索引?
(1)经常用于搜索的列,加快搜索速度。
(2)作为主键的列,强制该列的唯一性。
(3)在经常用在连接的列上,这些列主要是外键,可以加快连接速度。
(4)在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,指定的范围是连续的。
(5)在经常需要排序的列上。
VIII:怎么样判断mysql中的一个select语句是否使用了索引?
可以在select语句前加上explain,比如explain select * from tablename;返回的结果中,若列名为key的那列为null,则没有使用索引,若不为null,则返回实际使用的索引名。让select语句强制使用索引的语法:select * from tablename force index(indexname)。
VIIII:怎么样为select语句指定索引?
select * from student use index(s_d) where...
注:s_d是索引名称。
补充:
(1)USE INDEX
在你查询语句中表名的后面,添加 USE INDEX 来提供你希望 MySQ 去参考的索引列
表,就可以让 MySQL 不再考虑其他可用的索引。
Eg:SELECT * FROM mytable USE INDEX (mod_time, name) ...
(2)IGNORE INDEX
如果你只是单纯的想让 MySQL 忽略一个或者多个索引,可以使用 IGNORE INDEX 。
Eg:SELECT * FROM mytale IGNORE INDEX (priority) ...
(3)FORCE INDEX
为强制 MySQL 使用一个特定的索引,可在查询中使用 FORCE INDEX 。
Eg:SELECT * FROM mytable FORCE INDEX (mod_time) ...
VV:在字段上加上索引,查询的时候,满足一定条件就可以使用索引:
比如 create table test (id int,name varchar(10));
然后在id上加索引 create index test_index on test(id);
查询的时候 select * from test where id=?就会走索引,如果 是select * from test where name=?就不走索引。、
VVI:索引是根据你的条件来判断是否使用索引的
举个例子:有个D表,有D1(number),D2(varchar2),D3(number) 三个字段,其中D1,D2字段有作了索引。
select * from D ;--这时候尽管表中有索引,但是不会走索引,会全表扫描。
select D1 from D ;--这时候会走D1索引;
select * from D where D1=1,--这时候会走D1索引
select * from D where D2=1,--这时候不会走索引。尽管D2有索引,但是D2是字符型,where条件中是数值型,加上单引就可以走D2索引。
update ,delete和select 是同样的。