文章目录
创建索引并不是优化SQL语句的唯一方式.
创建索引通常能够在尽量不影响系统整体的情况下获得很大的性能提升,且在一些特定环境中索引是最容易实现的优化方式.
我文章需要使用的表
CREATE table artist(
artist_id int unsigned not null,
type ENUM('band','person','unknown','combination') not null,
name VARCHAR(255) not null,
gender ENUM('male','female') DEFAULT NULL,
founded YEAR DEFAULT null,
country_id SMALLINT UNSIGNED DEFAULT null,
PRIMARY KEY (artist_id)
)ENGINE=INNODB;
CREATE TABLE album(
album_id INT UNSIGNED not null,
atrist_id INT UNSIGNED NOT null,
album_type_id int UNSIGNED not null,
name VARCHAR(255) not NULL,
first_released YEAR not null,
country_id SMALLINT UNSIGNED DEFAULT null,
PRIMARY KEY (album_id)
)ENGINE=INNODB;
已有的索引
在MySQL中把上面的代码跑一下,创建出那两张表后,跟着我通过下面的示例优化SQL语句.
用来获取指定艺人的相关信息:
SELECT artist_id ,type,founded FROM artist WHERE name='Coldplay';
我们试试在Select语句前添加Explain关键字来查看QEP.这并不会真正执行SQL语句.
EXPLAIN SELECT artist_id ,type,founded FROM artist WHERE name='Coldplay'
尽管EXPLAIN命令不会执行SQL语句,但当执行计划确定时它会执行FROM语句中的子查询.
使用下面的命令验证表的结构,索引以及使用的存储引擎:
show create table artist
CREATE TABLE artist (
artist_id int(10) unsigned NOT NULL,
type enum(‘band’,‘person’,‘unknown’,‘combination’) NOT NULL,
name varchar(255) NOT NULL,
gender enum(‘male’,‘female’) DEFAULT NULL,
founded year(4) DEFAULT NULL,
country_id smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (artist_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
单列索引
单列索引是最基础的索引,这是一种建立在数据库表中特定列上的索引.
创建单列索引的语法
ALTER TABLE 表名 ADD PRIMARY KEY|INDEX 索引名 (列名)
比如:
ALTER TABLE artist ADD PRIMARY KEY indexname (name)
当创建非主码索引时,key和index关键字可以以互换.但创建主码索引时只能使用key关键字.
利用索引限制查询读取的行数
现在我们有这样的一个需求:按照出道时间来获取艺人信息.
我们可以通过创建索引来避免每次都扫描整张表.
如果在EXPALIN的结果中看到type=ALL或者key=NULL,则可以判断这条查询扫描了整张表.
我们给founded列上创建一个索引:
ALTER TABLE artist add INDEX (founded)
现在重新执行EXPALIN SELECT语句来查看加了索引之后执行速度怎么样:
用户并不需要指定索引的名称.MySQL会根据索引所在的首列的名称自动为索引命名,并在名字后面添加可选的附加信息确保唯一性.
使用索引连接表
索引的另一个好处就是可以提高关系表连接操作的性能.例如下列SQL语句要获取指定艺人的专辑信息:
EXPLAIN SELECT ar.name,ar.founded,al.name,al.first_released from artist ar
INNER JOIN album al USING (artist_id) WHERE ar.name='Queen'
结果:
这个示例结果显示album表会执行全表查询.我们可以通过为连接条件添加索引并重复EXPLAIN命令来查看是否改变.
ALTER TABLE album ADD INDEX (artist_id)
再执行:
EXPLAIN SELECT ar.name,ar.founded,al.name,al.first_released from artist ar
INNER JOIN album al USING (artist_id) WHERE ar.name='Queen'
在album表中我们现在使用了由key值新创建的artist_id索引,并且可以看到ref的值显示album表将要和artist表做连接操作.
理解索引的基数
当一个查询中使用不止一个索引的时候,MySQL会试图找到一个最高效的索引.它通过分析每条索引内部数据分布的统计信息来做到这点.本例中我们要查询创建于1980年的所有品牌,因此我们在artist表的type列上创建索引:
ALTER TABLE artist ADD INDEX(type);
接着禁用一个优化器设置:
SET @@session.optimizer_switch='index_merge
intersection=off';
本例中,MySQL必须在possible_keys列出的索引中做出选择.
优化器会根据最少工作量的估算开销来选择索引,这往往和人们想到的选择顺序不一样.
该列中唯一值的数量越多,那么越有可能在选用这个索引时以更少的读操作中找到需要的记录.这些统计信息只是估计值.
使用索引进行模式匹配
利用通配符可以通过索引来做模式匹配的工作:
EXPLAIN SELECT artist_id,type,founded FROM artist WHERE name LIKE 'Queen%';
如果你查找的词是以通配符开头的,则MySQL不会使用索引.
EXPLAIN SELECT artist_id,type,founded FROM artist WHERE name LIKE '%Queen';
如果你经常需要一个以通配符开头的查询,常用的方法是在数据库中保存需要查询的值的反序值.例如,假设你想要找所有以.com结尾的电子邮箱,当使用like"%.com"时不能使用索引.但是如果使用like Reverse(’%.com’)就可以使用索引.
MySQL不支持基于索引的函数.
因为使用了应用到name列上的uppre()函数(转大写),MySQL不会使用name上的索引.
选择唯一的行
如果我们想要保证每个艺人都有一个唯一的名字,可以创建唯一索引.唯一的索引有两个目的:
- 提供数据完整性以保证在列中任何值都只出现一次
- 告知优化器对给定的记录最多只可能有一行结果返回;这一点很重要,因为有了这些信息就可以避免额外的索引扫描.
我们使用show status命令来查看一般索引和唯一索引在查询内部造成的不同影响.
在内部,MySQL会去读取索引中下一项记录来判断name索引的下一个值不是那个指定的值,
创建一个唯一索引并再次运行同一个查询,我们可以看到以下结果:
对比两个结果可以发现,当使用唯一索引时MySQL知道最多可能返回一行数据,当找到一个匹配结果之后就不需要继续扫描了.当数据确实是唯一的情况下,把索引定义为唯一索引是非常好的方式.
在可以为空的列定义唯一索引也是可行的.这种情况下,null的值被认为是一个未知的值,并且null!=null.这就是三台逻辑的好处,他避免了使用默认值或者一个空字符串"".
结果排序
索引也可以用来对查询结果进行排序.如果没有索引,MySQL会使用内部文件排序算法对返回行按照指定顺序进行排序.
可以看到,通过在Extra的属性中设置了using filesort信息,mysql内部使用sort_buffer来对结果进行排序.
通过使用基于索引的数据排序方法,就可以免去分类的过程了
多列索引
索引可以创建在两列或多列上.多列索引也被称为混合索引或者连接索引
确定使用何种索引
这个查询根据where语句的限制在表上使用两个不同的索引,我们先创建索引:
尽可能地合并给定表DML语句会获得更高的效率.如果选择以两条独立语句地方式分别运行这些alter语句,则会有下面这样的结果:
我想说的就是合并alter语句会显著地节省时间.
创建索引是一件非常耗时的工作,并且会阻塞其他操作.你可以使用一条alter语句将给定表上多个索引创建的语句合并起来.
我们看一下下面的结果:
第一个查询选择了country_id列上的索引.实际结果显示获取了92000行,而选择album_type_id则会有289000行被获取.
第二个查询选择了album_type列上索引的情况下,实际结果显示获取了111000行,与之前的92000行作比较.如果你把实际数量与QEP估算的行数作比较,你会发现一个矛盾–例如对于第二个查询估计有58000行数据,但实际数据有111000行之多,几乎是实际行数的两倍.
多列索引的语法
创建多列索引的语法和之前相同,唯一不同的是需要指定该索引是要跨多列的:
创建更好的索引
我们接下来做个测试,创建一个多列索引和他的反序索引:
执行查询你会发现它使用的反序:
我们来看看这两个索引的基数:
m1的基数更多,它提供了唯一行的更高分布,但是MySQL还是选择了m2,这意味着基数并不是选出使用索引的唯一标准.
多个列上的索引
虽然索引可以包含多列,但实际上对索引的效率会有所限制.索引是用于改进性能的关系模型的一部分.
索引的行的宽度应该尽可能地短,这样就可以在一个索引数据页面中包含更多的索引记录.这样做的好处是可以读取尽量少的数据,从而尽可能快的遍历索引.
Explain命令结果中的key_len和ref两个属性的值可以用来判断选中的索引的列利用率.
合并where和order by语句
单索引的执行例子:
我们可以建立多列索引进行优化:
MySQL优化器的特性
MySQL可以在where,order by以及group by列中使用索引;
最常见的索引合并操作是两个索引取并集:
第二种类型索引合并是对两个有少量唯一值的索引取交集:
第三种类型的索引合并操作是先排序再取并集:
查询提示
总查询提示
索引提示
在这个查询优化器中有多个索引可供选择,但它最终选择了founded索引.
下面我们试着指定使用的索引:
force index会对基于开销的优化器产生影响,让优化器更倾向于索引扫描而不是全表扫描.
添加索引造成的影响
尽管添加索引可以优化sql语句,但是索引的创建依旧会带来不小的开销.
DML影响
在表上添加索引会影响写操作的性能.
可以发现这张表上有很多索引.
接下来我们试试在包含较少索引的原始状态的数据插入效率:
重复索引
在各种索引优化的技术中最简单的就是删除重复的索引.虽然找到重复索引很容易,但是如果一个索引与主码或者某些其他索引的子集相匹配就不能直接删除.任何包含在其他索引的最左边部分中的索引都属于重复索引,且不会被使用:
索引是否被使用
DDL影响
随着表大小的不断增长,对性能的影响也不断加大.例如在主表上添加索引平均需要20到30秒.
磁盘空间影响
information_schema.tables查询表大小.
在添加索引之前:
添加索引之后:
MySQL的限制和不足
基于开销的优化器
基于函数的索引
240
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
