一、Mysql索引主要有两种结构:B+Tree索引和Hash索引
Hash索引
mysql中,只有Memory(Memory表只存在内存中,断电会消失,适用于临时表)存储引擎显示支持Hash索引,是Memory表的默认索引类型,尽管Memory表也可以使用B+Tree索引。Hash索引把数据以hash形式组织起来,因此当查找某一条记录的时候,速度非常快。但是因为hash结构,每个键只对应一个值,而且是散列的方式分布。所以它并不支持范围查找和排序等功能。
B+Tree索引
B+Tree树是mysql使用最频繁的一个索引数据结构,是Inodb和Myisam存储引擎模式的索引类型。相对Hash索引,B+Tree在查找单条记录的速度比不上Hash索引,但是因为更适合排序等操作,所以它更受欢迎。毕竟不可能只对数据库进行单条记录的操作。
带顺序访问指针的B+Tree
B+Tree所有索引数据都在叶子节点上,并且增加了顺序访问指针,每个叶子节点都有指向相邻叶子节点的指针。
这样做是为了提高区间效率,例如查询key为从18到49的所有数据记录,当找到18后,只要顺着节点和指针顺序遍历就可以以此向访问到所有数据节点,极大提高了区间查询效率。
大大减少磁盘I/O读取
数据库系统的设计者巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,这样每个节点需要一次I/O就可以完全载入。
二、选择索引的数据类型
Mysql支持很多数据类型,选择合适的数据类型存储数据对性能有很大的影响。
(1)越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和cpu缓存中都需要更少的空间,处理起来更快。
(2)简单的数据类型更好:整形数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应用内置的日期和时间数据类型,而不是字符串来存储时间;以及用整形数据存储IP地址。
(3)尽量避免NULL:应该制定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为他们使得索引、索引的统计信息以及比较运算更加复杂。
三、MySQL常见索引有:主键索引、唯一索引、普通索引、全文索引、组合索引
1,INDEX(普通索引):ALTER TABLE 'table_name' ADD INDEX index_name('col')
最基本的索引,没有任何限制
2,UNIQUE(唯一索引):ALTER TABLE 'table_name' ADD UNIQUE('col')
与“普通索引”类似,不同的就是:索引列的值必须唯一,但允许有空值。
3,PRIMARY KEY(主键索引):ALTER TABLE 'table_name' ADD PRIMARY KEY('col')
是一种特殊的唯一索引,不允许有空值。
4,FULLTEXT(全文索引):ALTER TABLE 'table_name' ADD FULLTEXT('col')
仅可用于MyISAM和InoDB,针对较大的数据,生成全文索引很耗时耗空间
5,组合索引:ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')
为了更多的提高mysql效率可建立组合索引,遵循“最左前缀”原则。创建复合索引应该将最常用(频率)做限制条件的列放在最左边,一次递减。组合索引最左字段用in是可以用到索引的。相当于建立了col1,col1col2,col1col2col3三个索引
四,索引使用注意事项
1,不要滥用索引
①,索引提高查询速度,却会降低更新表的速度,因为更新表时,mysql不仅要更新数据,保存数据,还要更新索引,保存索引
②,索引会占用磁盘空间
2,索引不会包含含有NULL值的列
复合索引只要有一列含有NULL值,那么这一列对于此符合索引就是无效的,因此我们在设计数据库设计时不要让字段的默认值为NULL。
3,MySQL查询只是用一个索引
如果where字句中使用了索引的话,那么order by中的列是不会使用索引的
4,like
like '%aaa%'不会使用索引而like "aaa%"可以使用索引
转自 点击打开链接
https://blog.csdn.net/qq_19557947/article/details/76951912
----------------------------------------------------------
https://blog.csdn.net/u012978884/article/details/52416997#commentsedit
----前导列----------------------------------------------------------------
1)对于where子句中出现的列要想索引生效,会有一些限制,这就和前导列有关。
2)所谓前导列,就是在创建复合索引语句的第一列或者连续的多列。比如通过:CREATE INDEX comp_ind ON table1(x, y, z)创建索引,那么x,xy,xyz都是前导列,而yz,y,z这样的就不是。下面讲的这些,对于其他数据库或许会有一些小的差别,这里以sqlite为标准。在where子句中,前导列必须使用等于或者in操作,最右边的列可以使用不等式,这样索引才可以完全生效。同时,where子句中的列不需要全建立了索引,但是必须保证建立索引的列之间没有间隙。举几个例子来看吧:
用如下语句创建索引:
CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);
这里是一个查询语句:
...WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello'
这显然对于abcd四列都是有效的,因为只有等于和in操作,并且是前导列。
... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'
那这里只有a,b和c的索引会是有效的,d列的索引会失效,因为它在c列的右边,而c列使用了不等式,根据使用不等式的限制,c列已经属于最右边。
... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello'
索引将不会被使用,因为没有使用前导列,不是从a列开始,这个查询会是一个全表查询。
3)对于between,or,like,都无法使用索引。(like ‘’index%‘’使用索引,like '%index' 百分号在前,不使用索引)
like的使用https://blog.csdn.net/mywaster/article/details/51479772
注:在测试的时候别忘记建立索引了
如 ...WHERE myfield BETWEEN 10 and 20;这时就应该将其转换成:
...WHERE myfield >= 10 AND myfield <= 20;
此时应该将它转换成:
...WHERE myfield >= 'sql' AND myfield < 'sqm'; #第一次遇见,的确可行
用上述的下一种方式 检索速度更快了
此时应该将它转换成:
...WHERE myfield IN ('abc', 'xyz');
https://www.cr173.com/html/17298_1.html