做网站 珠海,一元夺宝网站开发,沈阳网站设计制作公司,网站开发技术发展索引 mysql最常用的索引结构是btree(O(log(n)))#xff0c;但是总有一些情况下我们为了更好的性能希望能使用别的类型的索引。hash就是其中一种选择#xff0c;例如我们在通过用户名检索用户id的时候#xff0c;他们总是一对一的关系#xff0c;用到的操作符只是而已#…索引 mysql最常用的索引结构是btree(O(log(n)))但是总有一些情况下我们为了更好的性能希望能使用别的类型的索引。hash就是其中一种选择例如我们在通过用户名检索用户id的时候他们总是一对一的关系用到的操作符只是而已假如使用hash作为索引数据结构的话时间复杂度可以降到O(1)。不幸的是目前的mysql版本(5.6)中hash只支持MEMORY和NDB两种引擎而我们最常用的INNODB和MYISAM都不支持hash类型的索引。 1、BTree索引 BTree多路搜索树并不是二叉的是一种常见的数据结构。使用BTree结构可以显著减少定位记录时所经历的中间过程从而加快存取速度。按照翻译B 通常认为是Balance的简称。这个数据结构一般用于数据库的索引综合效率较高。 B-Tree索引可以被用在像,,,,和BETWEEN这些比较操作符上。而且还可以用于LIKE操作符只要它的查询条件是一个不以通配符开头的常量。 EXPLAIN select * from blog.my_user where name in (rhythmk251,rhythmk2151,rhythmk7251,rhythmk685);
-----------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------
| 1 | SIMPLE | my_user | ALL | NULL | NULL | NULL | NULL | 778424 | Using where |
-----------------------------------------------------------------------------------------
1 row in set添加 idx_name 索引 ALTER TABLE blog.my_user
ADD INDEX idx_name (name) USING BTREE ;mysql EXPLAIN select * from blog.my_user where name in (rhythmk251,rhythmk2151,rhythmk7251,rhythmk685);
--------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------
| 1 | SIMPLE | my_user | range | idx_name | idx_name | 303 | NULL | 4 | Using where |
--------------------------------------------------------------------------------------------mysql EXPLAIN select * from blog.my_user where name LIKE rhythmk830%;
--------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------
| 1 | SIMPLE | my_user | range | idx_name | idx_name | 303 | NULL | 11 | Using where |
--------------------------------------------------------------------------------------------
1 row in set任何一个没有覆盖所有WHERE中AND级别条件的索引是不会被使用的。也就是说要使用一个索引这个索引中的第一列需要在每个AND组中出现。 mysql EXPLAIN select * from blog.my_user where name LIKE rhythmk830% or age35;
---------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------
| 1 | SIMPLE | my_user | ALL | idx_name | NULL | NULL | NULL | 7266 | Using where |
---------------------------------------------------------------------------------------
1 row in set
mysql EXPLAIN select * from blog.my_user where name LIKE rhythmk830% and age35;
--------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------
| 1 | SIMPLE | my_user | range | idx_name | idx_name | 303 | NULL | 11 | Using where |
--------------------------------------------------------------------------------------------
1 row in set 有时候mysql不会使用索引即使这个在可用的情况下。例如当mysql预估使用索引会读取大部分的行数据时。在这种情况下一次全表扫描可能比使用索引更快因为它需要更少的检索。然而假如语句中使用LIMIT来限定返回的行数mysql则会使用索引。因为当结果行数较少的情况下使用索引的效率会更高。 mysql EXPLAIN select * from blog.my_user where name rhythmk830 ;
---------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------
| 1 | SIMPLE | my_user | ALL | idx_name | NULL | NULL | NULL | 7769 | Using where |
---------------------------------------------------------------------------------------
1 row in setmysql EXPLAIN select * from blog.my_user where name rhythmk830 limit 5;
--------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------
| 1 | SIMPLE | my_user | range | idx_name | idx_name | 303 | NULL | 5658 | Using where |
--------------------------------------------------------------------------------------------
1 row in set 2、Hash 索引特征 Hash类型的索引有一些区别于以上所述的特征 精确查找非常快包括 和in其检索效率非常高索引的检索可以一次定位不像BTree 索引需要从根节点到枝节点所以 Hash 索引的查询效率要远高于 B-Tree 索引。 不适合 1、不适合模糊查询和范围查询包括likebetween……and等。由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值所以它只能用于等值的过滤不能用于基于范围的过滤因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系并不能保证和Hash运算前完全一样 2、不适合排序数据库无法利用索引的数据来提升排序性能同样是因为Hash值的大小不确定 3、复合索引不能利用部分索引字段查询Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值而不是单独计算 Hash 值所以通过组合索引的前面一个或几个索引键进行查询的时候Hash 索引也无法被利用。 3、备注 1、索引列需要根据业务判断 在where条件中出现的列。2、使用索引需要考虑某列中值的分布比如性别搜索 “男/女” 都会得出大约一半的行所有索引没有多大的用处。3、使用短索引好处多多。对字符串进行索引的时候应该指定一个前缀长度。索引长度小节约索引空间及磁盘IO同时让索引短的值比较起来更快同时Mysql 可以在内存中存储更多的值。4、利用最左前缀。创建一个多列索引可起几个索引的作用。因为可利用索引最左边的列来匹配行。这样的列集称为最左前缀。5、不要过度索引。 额外的索引需要占用空间并降低写操作的性能。修改表的时候索引必须进行更新有时可能需要重构索引越多时间越长。索引太多也可能导致MYSQL选择不到最优索引。6、INNODB 存储引擎记录优先按主键保存如果没有主键但是有唯一索引那么就按唯一索引列保存。如果两者都没有那么表中会生成一个内部列。按照这个列的顺序保存InnoDB普通索引都会保存主键所以主键要尽可能选择较短的数据类型。 7、尽量避免NULL应该指定列为NOT NULL除非你想存储NULL。在MySQL中含有空值的列很难进行查询优化因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。 8、使用--查看当前session所有已产生的profile mysql show profiles; 转载于:https://www.cnblogs.com/rhythmK/p/5352714.html
