mysql 索引分类
发布时间：2022-12-19 15:26:27 所属栏目：MySql教程 来源：
导读： mysql 索引分类 从物理存储角度分为：
聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
|
mysql 索引分类 从物理存储角度分为： 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置 从逻辑存储角度分为： 普通索引：仅加速查询,无限制 唯一索引：加速查询 ，列值唯一（可以有null）,如果是组合索引，则列值的组合必须唯一 主键索引：加速查询 ，列值唯一（不可以有null），表中只允许有一个主键索引, 是一种特殊的唯一索引 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并（索引合并：使用多个单列索引组合搜索） 只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合 全文索引：目前只有 CHAR、VARCHAR 、TEXT 列上可以创建全文索引 1. 索引的优势 通过创建索引,可以在查询的过程中,提高系统的性能通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间，比如查询order by age desc，因为B+索引树本身就是排好序的，所以再查询如果触发索引，就不用再重新查询了。降低CPU使用率提高查询效率（降低IO使用率）2. 何时创建索引 经常需要搜索的列上作为主键的列上经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度经常需要根据范围进行搜索的列上经常需要排序的列上经常使用在where子句上面的列上3. 创建索引的规范 (1)单张表中索引数量不超过5个。 (2)单个索引中的字段数不超过5个。 (3)索引名必须全部使用小写。 (4)非唯一索引按照“idx_字段名称[_字段名称]”进用行命名。例如idx_age_name。 (5)唯一索引按照“uniq_字段名称[_字段名称]”进用行命名。例如uniq_age_name。 (6)组合索引建议包含所有字段名,过长的字段名可以采用缩写形式。例如idx_age_name_add。 (7)表必须有主键,推荐使用UNSIGNED自增列作为主键。 (8)唯一键由3个以下字段组成,并且字段都是(整)(形)(时),可使用唯一键作为主键。其他情况下,建议使用自增列或发号器作主键。 (9)禁止冗余索引。 (10)禁止重复索引。 (11)联表查询时,JOIN列的数据类型必须相同,并且要建立索引。 (12)选择区分度大的列建立索引。组合索引中,区分度大的字段放在最前。 (13)对字符串使用前缀索引,前缀索引长度不超过8个字符。 (14)不对过长的VARCHAR字段建立索引。建议优先考虑前缀索引,或添加CRC32或MD5伪列并建立索引。 (15)合理创建联合索引,(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。 (16)合理使用覆盖索引减少IO,避免排序。 4. 索引失效的情况 5. 索引的优化 尽量不要使用左模糊和全模糊，如果需要可以使用搜索引擎来解决union,in和or都可以命中索引，建议使用in负向条件查询不能使用索引，可以优化为in查询 负向条件查询有：!= not in not like等等 例如：select * from user where status!=1 and status!=2 优化为：select * from user where status in (0,3,4); 合理使用联合索引的最左前缀原则 如果在(a,b,c)三个字段上建立联合索引，那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。 比如说把(username,password)建立了联合索引，因为业务上几乎没有password的单条件查询，而有很多username的单条件查询需求，所以应该建立(username,password)的联合索引，而不要建立(password,username)的联合索引 注意： 建立联合索引的时候，要把查询频率较高的列放在最左边如果建立了(a,b)索引，就不必再独立建立a索引。同理如果建立了(a,b,c)联合索引，就不必再独立建立a,(a,b)索引存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如 where a>? and b=?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。最左前缀原则，并不是要求where后的顺序和联合索引的一致。下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引。 selectuid, login_time from user where passwd=? and login_name=? 但还是建议 where 后的顺序和联合索引一致，养成好习惯。 把计算放到业务层而不是数据库层。（因为对索引列进行运算，不能命中索引）表数据比较少、更新十分频繁、数据区分度不高的字段上不宜建立索引。 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。 强制类型转换会全表扫描 例如：如果phone字段是varchar类型，则下面的sql不能命中索引 select * from user where phone = 18838003017 可以优化为：select * from user where phone = ‘18838003017’ 利用覆盖索引进行查询操作，避免回表 select uid,login_time from user where username=? and password=? 如果建立了(username,password,login_time)的联合索引,由于login_time已经建立在索引中了,被查询的username和password就不用去row上获取数据了,从而加速查询 在order by和group by中要注意索引的有序性 如果order by是组合索引的一部分,应该将该字段放在组合索引的最后 例如:where a=? and b=? order by c ->可以建立联合索引(a,b,c) 如果索引中有范围查找,则索引的有序性无法利用 例如:where a>10 order by b ->索引(a,b)无法排序 建立索引的列,不许为null 单列索引不存 null 值，复合索引不存全为 null 的值，如果列允许为 null，可能会得到“不符合预期”的结果集，所以，请使用 not null 约束以及默认值。 6. 索引的缺点 索引会占据磁盘空间创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护，索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。7. EXPLAIN参数 mysql 创建索引 锁表_mysql 创建表 索引_mysql索引表 id 如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行 select_type 查询的类型，主要用于区别普通查询、联合查询、子查询等的复杂查询 SIMPLE：简单的select查询，查询中不包含子查询或者UNION PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY(最后加载的那一个 ) SUBQUERY：在SELECT或WHERE列表中包含了子查询 DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)Mysql会递归执行这些子查询，把结果放在临时表里。 UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM字句的查询中，外层SELECT将被标记为:DERIVED UNION RESULT：从UNION表获取结果的SELECT type 显示查询使用了何种类型 从最好到最差依次是 System>const>eq_ref>range>index>All(全表扫描) 一般来说**至少达到range级别，最好达到ref** system：表只有一行记录，这是const类型的特例，平时不会出现(忽略不计) const：表示通过索引一次就找到了,const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。 eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。 ref：非唯一索引扫描，返回匹配某个单独值的行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体 range：只检索给定范围的行，使用一个索引来选择行。有范围的索引扫描，相对于index的全表扫描，他有范围限制，因此要优于index，key列显示使用了哪个索引，一般就是在你的where语句中出现了between、、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点mysql索引表，不用扫描全部索引。 index：FULL INDEX SCAN,index与all区别为index类型只遍历索引树。另一种形式的全表扫描，只不过他的扫描方式是按照索引的顺序，这通常比all快，因为索引文件通常比数据文件小。 ALL：查询全部表中的数据 possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出， 但不一定被查询实际使用 key 实际使用的索引，如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，key参数可以作为使用了索引的判断标准 key_len :表示索引中使用的字节数，可通过该列计算查询中索引的长度，在不损失精确性的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。 ref 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引上的值。 rows 根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数 extra Using index：MYSQL 将使用覆盖索引，表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找操作。 Using where：将在存储引擎检索行后再进行过滤，暗示：查询可受益于不同的索引 Using temporary：MYSQL 在对查询结果排序时会使用一个临时表，常见于排序order by 和分组查询 group by Using filesort：MYSQL 会对查询结果进行一个外部索引排序 range checked for each record (index map: #)：MySQL 发现没有好用的索引，新的索引将在连接的每一行上重新估算。N 是显示在 possible_keys 列中索引的位图，是冗余的。 一般来说，得保证查询至少达到range级别，最好能达到ref，type出现index和all时，表示走的是全表扫描没有走索引，效率低下，这时需要对sql进行调优。 当extra出现Using filesor或Using temproary时，表示无法使用索引，必须尽快做优化。 8. MyISAM和InnoDB区别 1、 存储结构 MyISAM：每个MyISAM在磁盘上存储成三个文件。分别为：表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。 2、 存储空间 MyISAM： MyISAM支持支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。 InnoDB： 需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 3、 可移植性、备份及恢复 MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。 InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。 4、 事务支持 MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。 InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。 5、 AUTO_INCREMENT MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。 InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。 6、 表锁差异 MyISAM： 只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。 InnoDB： 支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。 7、 全文索引 MyISAM：支持 FULLTEXT类型的全文索引 InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。 8、表主键 MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。 InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。 9、表的具体行数 MyISAM： 保存有表的总行数，如果select count() from table;会直接取出出该值。 InnoDB： 没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。 10、CRUD操作 MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。 InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。 11、 外键 MyISAM：不支持 InnoDB：支持 mysql 创建索引 锁表_mysql 创建表 索引_mysql索引表 9. sql语句的优化 1.能用到索引尽量用到索引.对索引的优化实际上就是sql语句的调优 2.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。 3.尽量使用where,而不要使用having 4.尽量使用多表查询,不要使用子查询 5.where后的and.or左右执行顺序是从右至左，运算符为and时--尽量把为假的放在右边 Order by 优化 Order by 语句应该符合最佳左前缀原则。尽可能在索引列上完成排序 eg:索引：index idx_n_a(name,age) select name,age from user age>20 order by name,age (√) select name,age from user age>20 order by name (√) select name,age from user age>20 order by age (×) select name,age from user age>20 order by age,name (×) 注意，当索引idx_n_a的第一个列 name为常量时，order by age也可以使用索引 select name,age from user age=20 order by age (√) group by 优化 与order by 的优化方式基本一致。主要注意的点： Having与Where的区别 where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。 （编辑：航空爱好网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐