一:Mysql数据库索引的创建
1)如何为我们的mysql数据库添加索引呢?很简单,请看下面的创建mysql普通索引格式:
CREATE INDEX [index name] ON [table name]([column name])
参数说明:NameDescription
index name索引名称.
table name需要添加索引的表名称.
column name需要添加索引的列名称.
例如我们要创建一个索引,就可以使用如下的mysql命令运行即可:CREATE INDEX myindex ON mytable(aut_id)
2)唯一索引的创建,和mysql普通索引差不多,只需要在INDEX关键词前面加入UNIQUE关键词即可,mysql命令如下:CREATE UNIQUE INDEX myindex ON mytable(aut_id)
我们也可以使用USING BTREE关键字,B-tree算法减少定位记录时所经历的中间过程,从而加快存取速度,mysql命令如下:CREATE UNIQUE INDEX myindex ON mytable(aut_id) USING BTREE
二:查看mysql数据库索引
我们可以使用以下命令查询刚刚新建的mysql数据库索引,“from mytable”代表查询该数据库表里面有哪些索引,mysql命令如下:show index from mytable
创建成功之后,会在“key_name”字段中显示刚刚创建的数据库索引名称,如图所示:
三:删除mysql数据库索引
我们也可以使用mysql命令删除某张表的索引,命令如下:drop index english on mytable
四:查询sql语句是否使用了mysql索引
我们上面常见好mysql数据库索引之后,如果想要知道我们在执行sql语句时是否使用了数据库索引,就要在sql语句前面加入“EXPLAIN”关键词,命令如下所示:EXPLAIN SELECT * FROM `allword` where english='America'
如果使用到了我们创建的字段索引,就会看到如图所示的字段都不会是NULL空值,如下:
在有些情况下mysql索引会失效,也就是在执行到sql语句时没有使用到我们创建的数据库表字段索引,可能有以下这些情况:
1:使用了OR关键字查询
2:或者LOWER(),UPPER()函数,
3:还有一种就是使用了LIKE关键字查询,像(like '%XX'或者like '%XX%')这样的语句,但是像(like 'XX%')这种情况的mysql表索引是不会失效的。
字符串创建索引方式:
1、直接创建完整索引,比较占用空间。
2、创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引。
3、倒序存储,在创建前缀索引,用于绕过字符串本身前缀的却分度不够的问题。
4、创建hash字段索引,查询性能稳定,有额外的存储和计算消耗。
倒序存储和hash字段索引都不支持范围查询。倒序存储的字段上创建的所有是按照倒序字符串的方式排序的。hash字段的方式也只能支持等值查询。
mysql>alter table SUser add index index1(email):包含了每个记录的整个字符串
或
mysql>alter table SUser add index index2(email(6)):-对于每个记录只取前6个字节
全字段索引 *** 作流程
使用的是 index1(即 email 整个字符串的索引结构),执行顺序是这样的:
1、从 index1 索引树找到满足索引值是’ [email protected] ’的这条记录,取得 ID2 的值;
2、到主键上查到主键值是 ID2 的行,判断 email 的值是正确的,将这行记录加入结果集;
3、取 index1 索引树上刚刚查到的位置的下一条记录,发现已经不满足 email=' [email protected] ’的条件了,循环结束。
前缀字段索引 *** 作流程
如果使用的是 index2(即 email(6) 索引结构),执行顺序是这样的:
1、从 index2 索引树找到满足索引值是’zhangs’的记录,找到的第一个是 ID1;
2、到主键上查到主键值是 ID1 的行,判断出 email 的值不是’ [email protected] ’,这行记录丢弃;
3、取 index2 上刚刚查到的位置的下一条记录,发现仍然是’zhangs’,取出 ID2,再到 ID 索引上取整行然后判断,这次值对了,将这行记录加入结果集;
4、重复上一步,直到在 idxe2 上取到的值不是’zhangs’时,循环结束。
倒序查询和hash字段的区别
它们的区别,主要体现在以下三个方面:
1、从占用的额外空间来看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而 hash 字段方法需要增加一个字段。当然,倒序存储方式使用 4 个字节的前缀长度应该是不够的,如果再长一点,这个消耗跟额外这个 hash 字段也差不多抵消了。
2、在 CPU 消耗方面,倒序方式每次写和读的时候,都需要额外调用一次 reverse 函数,而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话,reverse 函数额外消耗的 CPU 资源会更小些。
3、从查询效率上看,使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)