索引:对建立索引的列进行排序并记录顺序
索引的目的:减少查找数据扫描的行数,查数据实际很多时候要在磁盘查,有了索引之后可以减少磁盘IO次数
所有的键可以默认是一种索引,primary key ;unique key ;foreign key等
key与index的比较:
key是特殊的index,key关键字包含两层含义,一是约束(如primary,unique,foreign),二是索引(index)
聚集索引:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。(mysql大约可以简单认为primary key就是聚集索引),
非聚集索引:简单认为除了primary key之外的索引都是非聚集索引
索引的分类(聚集/非聚集):
1、主键索引(primary key):主键默认是索引,主键不允许重复或者为空 聚集
2、唯一索引(unique key):设置为unique key的默认也是索引,unique key不允许重复,但是可以为空
我们会发现两个key(primary key和unique key)默认都是索引,是稍微特殊一点点的索引
3、普通索引(index):使用关键index的一般都是普通索引
4、文本索引(fulltext):使用文本列作为索引,属于特殊索引的一种
组合索引:
索引可以是一列,也可以是多列,如果是多列的话,我们一般称之为组合索引,组合索引遵从最左原则
eg:index(a,b,c),那么单独按照a查询会走索引,单独按照ab查也会走索引,单独按照abc也会走索引,即索引是按照a ab abc建立的,那么b,c,bc等就不会走索引了
多个(多列)索引和组合索引,多个是指独立的多个,组合是指将多列同时组合起来作为一个完成的索引
mysql引擎:
什么是引擎:数据索引,存储,查找,保护或者锁等机制组合的统称
查看引擎:show engine
主要引擎:innodb myisym和memory,今天重点说明innodb
BTree索引:
两种节点,指针节点和索引数据节点,交叉进行,每行第一列一定是指向下一列索引数据的节点,最后一列同样如此,每一层结构都完全相同
B+Tree索引:
最下面一层才有数据,上面k-1层全部是指针用于指向一层一层指向叶子结点的数据,所有真实数据都在最下层的叶子结点,三种节点,上面k-1层都是索引节点,指针节点,最下面一层全部是索引数据节点,k-1层第一列一定是索引,然后索引与指针交叉进行排列
B+Tree优点:利用磁盘IO原理,只要key越多效率越高,正好B+Tree上面k-1层全部存储key,最后一层才有数据
建立索引的原则:
where orderby max或者连表查询使用外键索引(外键可以默认是一种索引),主键很重要,索引很重要
索引失效情况:
1、索引列出现null就失效
2、每个select中只能使用一次索引,当有where和order by的时候,where中的索引生效,如果where中没有索引,order by索引生效,如果都有不能同时生效
3、条件中有like ‘%’开头的,function(index-column),is null/not null,<,>,!等条件的
4、index-column数据类型跟where数据类型不一致,eg:column是string where中用int,虽然有默认转换,但是会导致索引失效
使用索引注意事项及建议:
1、or多条索引情况,如果每个or都有索引,最好先select然后union
2、使用短索引(可以用一列中的前n行,就不要用整列,占用资源)
3、列中所有值都不一样适合做索引吗? 如果进场使用查找更新较少,还是适合构建索引,查找效率从o(n)编程o(lg(n))
4、唯一主键(特殊索引)在主从同步中作用很大
5、索引不是越多越好,一定要根据实际情况创建(哪些字段作为查询条件,或者哪些字段作为主要查询条件,如时间等条件)
6、如果有union join等夺标查询情况下,如何科学构建索引?
innodb:mysql的一种数据库引擎,是聚集索引,支持事务型操作,支持行级别锁