索引在底层的数据结构用的是 B+树,它的原理见数据结构篇。
注:参考地址 《和刚入门的菜鸟们聊聊–什么是聚簇索引与非聚簇索引》 《MYSQL索引:对聚簇索引和非聚簇索引的认识》 《通俗易懂 索引、单列索引、复合索引、主键、唯一索引、聚簇索引、非聚簇索引、唯一聚簇索引 的区别与联系》
聚簇索引规定了一个数据表的排序方式,一个数据表只能有一个聚簇索引,通常使用聚簇索引的是数据表的主键。
聚簇索引和数据行是存放在一起的,所以使用聚簇索引的查询效率很高。同时由于聚簇索引已经进行了排序,所以范围查找的效率很高。但是聚簇索引插入删除的代价可能会比较高,可能会引起页分裂的情况(B+Tree 的数据结构特性,因为 B+Tree 的一个节点的度通常是数据页的大小,向一个满度的节点插入数据,就会导致分页)。
非聚簇索引又称二级索引,可以有多个,它也是一个 B+Tree 结构,它的叶节点指向的是行的 key 字段和主键值。所以通过非聚簇索引搜索时,首先通过非聚簇索引获取到行的主键值(先获取到数据表的聚簇索引值),然后根据主键值获取到数据行信息,相当于比聚簇索引多了一倍的 IO。
聚簇索引和非聚簇索引不是矛盾关系。
什么时候要使用索引?
ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col1’,‘col2’,‘col3’);
什么时候不要使用索引?
索引失效的情况:
’%aaa%’
不会使用索引,也就是索引会失效,但是 ‘aaa%’’
可以使用索引。select * from users where YEAR(adddate)>2007
,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate > ’2007-01-01′
。其它通配符同样,也就是说,在查询条件中使用正则表达式时,只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。select * from sunyang where id=‘123’;
索引的优化
SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);