手机版
你好,游客 登录 注册
背景:
阅读新闻

MySQL索引的设计和使用

[日期:2018-09-23] 来源:Linux社区  作者:MrHSR [字体: ]

一.概述

  所有mysql 列类型都可以被索引,是提高select查询性能的最佳方法。 根据存储引擎可以定义每个表的最大索引数和最大索引长度,每种引擎对每个表至少支持16个索引,总索引长度至少为256字节。

  myisam和innodb引擎的表默认是btree索引,支持前缀索引,前缀索引长度跟存储引擎相关,对于myisam引擎 ,长度可达1000字节长,对于innodb 长度可达767字节,在使用多字节字符集的列指定前缀长度时要考虑。

  支持全文索引(fulltext),只有myisam引擎支持,只限于char,varchar,text列。默认memory引擎使用hash索引,也支持tbree索引。

   1.  例如,要为city 表创建了10 个字节的前缀索引,语法是:

-- 为city表的cityname字段创建10个字节的前缀索引
CREATE INDEX ixcityname ON city(cityname(10));
--  索引查看
EXPLAIN SELECT * FROM city WHERE cityname='';

  2.  删除索引

DROP INDEX  ixcityname ON  city;

二 .设计索引的原则:

1. 索引使用在where后的列,而不是select 选择的列。
2. 索引列的基数越大,索引效果越好。
3. 使用短索引, 如果对字符串进行索引,应该指定一个前缀长度。如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。这样能够节省索引空间。
4.不过度使用索引。因为占用磁盘空间,降低写操作性能。
5. innodb表的普通索引都会保存主键的键值,所以主键的键值尽可能选择较短的类型。
6. 利用最左前缀,在创建一个n列索引时,实际是创建了mysql 可利用的n个索引,多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。

-- 创建city表的多列复合索引
CREATE INDEX ix1 ON city(cityname(10),citycode);
--  走索引的语句
EXPLAIN SELECT * FROM city WHERE  cityname='' AND citycode='';
EXPLAIN SELECT * FROM city WHERE  cityname=''
 -- 不走索引
EXPLAIN SELECT * FROM city WHERE  citycode=''

三. btree索引与hash索引 

memory 引擎可以选择使用btree或hash索引, 两种不同类型的索引各有其不同的适用范围, hash索引使用需要注意:
1. 只用于使用=或 <=>操作符的等式比较。
2. 优化器不能使用hash索引来加速order by 操作。
3. mysql 不能确定在两个值之间大约有多少行。如果将一个myisam表改为hash索引的memory表,会影响一些查询的执行效率。
4. 只能使用整个关键字来搜索一行。

下面来演示下:

-- 在city表上添加city_memory表
 CREATE TABLE city_memory SELECT * FROM city
-- 添加外键
ALTER TABLE city_memory ADD KEY idx_fk_country_id(country_id) ;
-- 添加主键
 ALTER TABLE city_memory ADD PRIMARY KEY(city_id);

-- city的btree 走索引
EXPLAIN SELECT * FROM city WHERE country_id > 1 AND country_id < 10

--  city_memory的 hash 不走索引
EXPLAIN SELECT * FROM city_memory WHERE country_id > 1 AND country_id < 10
EXPLAIN SELECT * FROM city_memory WHERE country_id > 1
EXPLAIN SELECT * FROM city_memory WHERE country_id < 10

-- city_memory的 hash 走索引
EXPLAIN SELECT * FROM city_memory WHERE country_id= 10
EXPLAIN SELECT * FROM city_memory WHERE country_id IN (10,11);

总结:大多数mysql 索引(如 primary key, unique index , index, fulltext index)在btree中存储,只有空间列类型索引使用rtree,并且memory表支持hash索引。

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址https://www.linuxidc.com/Linux/2018-09/154330.htm

linux
相关资讯       MySQL索引 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款