欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql 开发基础系列15 索引的设计和使用

程序员文章站 2023-11-18 20:46:10
一.概述 所有mysql 列类型都可以被索引,是提高select查询性能的最佳方法。 根据存储引擎可以定义每个表的最大索引数和最大索引长度,每种引擎对每个表至少支持16个索引,总索引长度至少为256字节。 myisam和innodb引擎的表默认是btree索引,支持前缀索引,前缀索引长度跟存储引擎相 ......

一.概述

  所有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='';

mysql 开发基础系列15 索引的设计和使用

  2.  删除索引

DROP INDEX  ixcityname ON  city;

mysql 开发基础系列15 索引的设计和使用

二 .设计索引的原则:

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

mysql 开发基础系列15 索引的设计和使用

--  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

mysql 开发基础系列15 索引的设计和使用

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

mysql 开发基础系列15 索引的设计和使用

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