MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
如何使用索引呢?
首先索引有窄索引和宽索引两个概念,窄索引是指索引的列数为1~2,宽索引就是说索引的列数大于2。
因为窄索引的效率要高于宽索引,所以能用窄索引就不要使用宽索引。
那么对单字段索引和复合索引应该如何使用?
目录
单字段索引的情况:
复合索引的优势:
两者的比较:
单字段索引的情况:
1.表的主键,外键必须有索引
2.数据量超过300的表应该有索引
3.经常与其他表进行连接的表,在连接字段上应该建立索引
4.经常出现在where字句中的字段,特点是大表的字段,应该建立索引
5.索引应该建在选择性高的字段上
6.索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建立索引
7.尽量用单字段索引代替复合索引,复合索引的建立需要仔细的斟酌
复合索引的优势:
1.单字段索引很少甚至没有
2.复合索引的几个字段经常同时以AND的方式出现在where语句
当where语句中的条件是OR时,索引不起作用。
两者的比较:
以一个sql语句来举例:SELECT * FROM STUDENT WHERE SEX="男" AND SAGE=18
若在sex 和 sage 两个字段分别创建了单字段索引,mysql查询每次只能使用一个索引,虽然对于未添加索引时使用全盘扫描,我们的效率提升了很多,但如果在sex 和 sage两个字段添加复合索引,效率会跟高,如: 创建(sex, age,teacher)的复合索引,那么其实相当于创建了(area,age,teacher)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。
那对于两者优缺点的比较:
1.对于具有2个用and连接条件的语句,且2个列之间的关联度较低的情况下,复合索引有一定优势。
2.对于具有2个用and连接条件的语句,且2个列之间的关联度较高的情况下,复合索引有很大优势。
3.对于具有2个用or连接条件的语句,单索引有一定优势,因为这种情况下复合索引将会导致全表扫描,而前者可以用到indexmerge的优化。
以上就是如何提高查询效率的全部内容,如果有帮助到你的话记得点个关注哟
MySQL 前缀索引能有效减小索引文件的大小,提高索引的速度。但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)。
集一个索引包含多个列(最左前缀匹配原则)
索引列的值必须唯一,但允许有空值
全文索引为FUllText,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值,全文索引可以在CHAR,VARCHAR,TEXT类型列上创建
设定主键后数据会自动建立索引,InnoDB为聚簇索引
即一个索引只包含单个列,一个表可以有多个单列索引
覆盖索引是指一个查询语句的执行只用从所有就能够得到,不必从数据表中读取,覆盖索引不是索引树,是一个结果,当一条查询语句符合覆盖索引条件时候,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后的回表 *** 作,减少了I/O效率
查看索引
列名解析:
删除索引
查看:
删除前:
删除后:
普通的索引,没有什么介绍
查看:(注意和前缀索引Sub_part的区别)
当索引的列是unique的时候,会生成唯一索引,唯一索引关于null有下列两种情况
SQLSERVER 下的唯一索引的列,允许null值,但最多允许有一个空值
MYSQL下的唯一索引的列,允许null值,并且允许多个空值
查看:
会建立两个索引,一个非聚簇索引,一个是唯一索引
结果:
可以插入两个空值(明人不说暗话,我喜欢MySQL)
一方面,它不会索引所有字段所有字符,会减小索引树的大小.
另外一方面,索引只是为了区别出值,对于某些列,可能前几位区别很大,我们就可以使用前缀索引。
一般情况下某个前缀的选择性也是足够高的,足以满足查询性能。对于BLOB,TEXT,或者很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。
查看:
查看:
复合索引的最左前缀匹配原则 :
对于复合索引,查询在一定条件才会使用该索引
减少开销。 建一个联合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写 *** 作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!
覆盖索引。 对联合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io *** 作。减少io *** 作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。
效率高。 索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出1000w10% 10% *10%=1w。
在模糊搜索中很有效,搜索全文中的某一个字段,可以参考这篇博文
: https://zhuanlan.zhihu.com/p/88275060
我们先进行下面一个实验看看InnoDB下的主键索引的一个现象。
查看:
我们插入进去的时候,数据的id都是乱序的,为什么这里最后select查询出来的结果都是进行了排序?
这是因为InnoDB索引底层实现的是B+tree,B+tree具有下列的特点:
所以上面的排序是为了使用B+tree的结构 ,B+tree为了范围搜索,将主键按照从小到大排序后,拆分成节点。后续还有新的节点进入的时候,和B-tree相同的 *** 作,会进行分裂。
一般来说,聚簇索引的B+tree都是三层
InnoDB中主键索引一定是聚簇索引,聚簇索引一定是主键索引。
为什么这里辅助索引叶子结点不直接存储数据呢?
MYISAM只有非聚簇索引,索引最终指向的都是物理地址。
Q:既然有回表的存在,那么聚簇索引的优势在哪里?
Q:主键索引作为聚簇索引需要注意什么
在查询语句中使用LIke关键字进行查询时,如果匹配字符串的第一个字符为"%",索引不会使用。如果“%”不是在第一位,索引就会使用
多列索引是在表的多个字段上创建的索引,满足最左前缀匹配原则,索引才会被使用
查询语句只有Or关键字时候,如果OR前后的两个条件都是索引,这这次查询将会使用索引,否则Or前后有一个条件的列不是索引,那么查询中将不使用索引
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)