MySql教程

数据库-mysql索引篇

本文主要是介绍数据库-mysql索引篇,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

点赞再看,养成习惯,微信搜索「小大白日志」关注这个搬砖人。

文章不定期同步公众号,还有各种一线大厂面试原题、我的学习系列笔记。

mysql的索引类型?

mysql中有5种索引:普通索引、唯一索引、主键索引、组合索引、全文索引

  • 普通索引index:最基本的索引,仅加速查询,是我们大多数情况下使用到的索引。
//为book表的bookname字段创建了名为index_bookBookname的索引
create  index  index_bookBookname  on book(bookname) 
  • 唯一索引unique index:与普通索引区别是,加速查询 + 列值唯一(可以有null)
create  unique index  index_bookBookname  on book(bookname) 
  • 主键索引:一个表的主键也是索引(primary key),叫主键索引,该类型的不能为null,一般在建表的时候建立
  • 组合索引:在表的多个字段上创建一个组合索引,为book表的bookname和authors字段建索引
create   index  index_bookBooknameAndAuthors  on book(bookname,authors) 
或者
create table book(
  id   int(20),
  bookname  VARCHAR(32) ,
  authors VARCHAR(32) ,
  content text,
  INDEX index_book (bookname,authors) 
) ;

mysql创建索引时如果是blob 和 text 类型,索引里面必须指定length,如:
create index index_book on book(content(25)) ;

组合索引必遵循最左前缀原则
利用索引中最左边的列集来匹配行,比如新建索引 (bookname,authors,info) 最左边为bookname字段,查询的字段若为(bookname)、(bookname,authors)、(bookname,authors,info)则会启用索引,若为(authors)、(authors,info)则不会启用索引,(bookname,info)只会用到bookname列

  • 全文索引:即FULLTEXT索引,它在很多文字中,通过关键字匹配就能够找到该记录。全文搜索的限制比较多,只有mysql的MyISAM存储引擎支持全文索引(mysql有InnoDB和MyISAM引擎);并且只能为CHAR、VARCHAR和TEXT列建索引;搜索的关键字默认至少要4个字符(关键字太短就会被忽略掉);
//使用全文搜索时,必须借助MATCH函数创建:
create  fulltext  index index_bookBookname  on book(bookname) 
create  fulltext  index index_bookBookname  on book(bookname,authors) 
select  * from book where match(bookname) against('万历十五年');
select  * from book where match(bookname,authors) against('万历十五年',"鲁迅");

主键索引又可以分为聚簇索引、非聚簇索引,它们的区别是什么?

  • 相同点:聚簇索引和非聚簇索引都使用b+树实现
  • 不同点:使用聚簇索引的表,它的表数据物理顺序和索引顺序保持一致,b+树中叶子节点=表数据节点=整行的表数据,它的查询比较快,修改表数据后因为要对数据进行重排序所以速度慢,mysql的Innodb引擎的主键索引就是聚簇索引(若没有主键则Innodb会选择一个唯一非空的索引列做聚簇索引,如果也没有唯一非空的索引列,则Innodb隐式生成一个主键来做聚簇索引);而非聚合索引的表则相反:它的表数据物理顺序和索引顺序不一致,b+树中叶子节点不等于表数据节点(叶子节点存的是索引字段的值、该索引字段值的表数据节点指针,故由非聚簇索引找到索引字段的值后,还要回表查询该字段值对应的行数据),修改表数据不需要对数据重排序所以速度快

谈谈sql的优化策略

  • 避免全表扫描:避免使用select ,select count(),union all(求并集,包含重复值,union all比IN和union都强),select语句务必指明字段名称
  • 慎用in 和 not in (in不会导致索引失效;not in会导致索引失效;当in的范围是确定的且范围比较小可以用in,不管怎样,多用between/exists 代替 in)
  • 多表查询时,量小的表放在from的右边;
  • where条件的and由后往前执行,故把能筛掉大量数据的and条件放在右边;where查询比having快;
  • 尽量在join的on中写条件,而非on之后的where;尽量小表驱动大表,然后大表的条件列加索
  • 利用索引:
    • 什么时候加索引:where/on/orderby/groupby/distinct by后面的字段使用索引;对较小的列加索引,这样索引占的空间就会小
    • 什么时候不要加索引:重复数据比较多的列,如0/1;text、image、bit类型的列;insert/deltete/update操作比select操作多的列(因为索引只对select语句有效)
    • 使用索引的注意事项->索引有效:要符合最左前缀匹配原则;like匹配只有右模糊匹配才走索引(like xxx%);若是组合索引,则where条件的字段在组合索引中位于order by字段之前;使用explain分析sql执行计划,查看索引、使用了那些表,再进行优化;
    • 使用索引的注意事项->索引失效:以下会导致索引失效,进而导致全表扫描=在where中对字段进行 null 值判断(放弃索引);在where中对字段进行表达式操作/函数操作(放弃索引);where 子句中使用!=或<>操作符(两个都是不等于的意思);where 子句中使用 or 来连接条件导致放弃索引(除非每个or条件字段都建了索引);like使用双%%、有前缀%(放弃索引)

mysql的explain分析sql执行情况

  • 特点:explain分析只是估算值并非准确值;explain只对select语句有效;
  • 字段:
    • id:SQL执行的顺序标识,越大越先执行,如果说数字一样大,那么就从上往下依次执行
    • select_type:表示每个select子句的类型,如SIMPLE,PRIMARY,UNION,DERIVED;SIMPLE,简单的SELECT,表示不用UNION或子查询的select;
    • PRIMARY,需要union操作或者含有子查询的select;
    • table:数据表的名字。他们按被读取的先后顺序排列,这里因为只查询一张表,所以只显示book
    • type:指定本数据表和其他数据表之间的关联关系,type最好能达到ref级别
    • possible_keys:MySQL在搜索数据记录时可以选用的各个索引,查询涉及到的字段上若存在索引,则该索引将会被列出,但不一定被当前查询实际使用。
    • key:实际选用的索引,若为null,则没有使用到索引
    • key_len:计算使用了的索引长度,以字节为单位,字段类型 int为4个,date为3,datetime为4,char(n)为3n,varchar(n)为3n+2个
    • ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
    • extra:提供了与关联操作有关的信息,如Using filtersort文件排序(没有索引),Using index使用了索引;extra最好不要出现【using temporary=使用临时表】、【using filesort=使用文件排序=需要回表查询导致更多地io操作+需要更多的额外空间】
    • rows :查询返回的行数

image

mysql索引为什么使用B+树而不是B-树(也称B树)?

  • 由数据结构可知,B-树每个节点关键字个数为【[m/2]-1 ~ m-1】个,而B+树每个节点的关键字个数为【[m/2] ~ m】个,即B+树比B-树节点能存储更多的数据,所以同样的数据量在B+树的高度会比B-树低=访问B+树进行的I/O操作比B-树少=查询B+树所需的时间更少
  • B-树每个节点都存有关键字,而B+树的所有关键字都存于叶子节点,所以B+树的查找速度更稳定

OK,如果文章哪里有错误或不足,欢迎各位留言。

创作不易,各位的「三连」是二少创作的最大动力!我们下期见!

这篇关于数据库-mysql索引篇的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!