赞
踩
一般的应用系统,读写比例在10:1左右,而且插入和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然是重中之重。
在数据量和访问量不大的情况下,MySQL的访问时非常快速的,是否加索引对访问影响不大。但是当数据和访问量剧增的时候,就会发现MySQL变慢,甚至down掉,这就必须考虑优化SQL了。给数据库建立正确合理的索引,是MySQL优化的一个重要手段。
索引的目的在于提高查询效率,可以类比字典,如果要查"mysql"这个单词,我们肯定需要定位到m字母,然后从前往后找到字母y,再找到剩下的"sql"。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的。出了词典,生活中随处可见索引的例子,如火车站的车次表、图书的目录等。它们的原理都是一样的,通过不断的缩小想要获取数据的范围来筛选出想要的结果,同时把随机的时间变成顺序的时间,也就是我们总是通过一种查找方法来锁定数据。
在创建索引时,需要考虑哪些列会用于SQL查询,然后为这些列创建一个或多个索引。事实上,索引也是一种表,保存着主键或索引字段,以及一个能将每个记录只想实际表的指针(非聚簇索引和聚簇索引,一个在索引中保存的是字段值的地址,一个保存的就是字段的值)。数据库用户是看不到索引的,它们只是用来加速查询的。数据库搜索引擎使用索引来快速定位记录。
INSERT
和 UPDATE
语句在拥有索引的表中执行会花费更多的时间,而 select
语句却会执行得更快,这是因为在进行插入和更新时,数据库也需要插入或更新索引值。
索引的类型:
ALTER TABLE
语句创建索引ALTER TABLE
应用于数据表创建完毕之后再添加。
ALTER TABLE 表名 ADD 索引类型(unique, primary key, fulltext, index) [索引名] (字段名);
例子:
-- 普通索引
alter table table_name add index [index_name] (column_list); -- 不加索引名字index_name时,默认根据第一个索引列赋一个名称
-- 唯一索引
alter table table_name add unique [index_name] (column_list); -- 不加索引名字index_name时,默认根据第一个索引列赋一个名称
或者
alter table table_name add unique index [index_name] (column_list);
-- 主键索引
alter table table_name add primary key (column_list); -- 一张数据表只能有一个主键
ALTER TABLE 可用于创建普通索引、UNIQUE索引和PRIMARY KEY索引3种索引格式,table_name
是要添加索引的表名,column_list
之初对那些列进行索引,多列时各列之间用逗号分隔。索引名index_name
可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以同时创建多个索引。
CREATE INDEX
语句对表增加索引CREATE INDEX
可用于对表增加 普通索引 和 UNIQUE 索引,可用于建表时创建索引。
CREATE INDEX index_name ON table_name (column_name(length));
length
的作用:对于 CHAR 和 VARCHAR 类型的列,只用一列的一部分就可以创建索引。在创建时,使用 column_name(length)
语法,对前缀编制索引。前缀包括每列值得前length个字符。BLOB
和 TEXT
类型的列则必须给出前缀长度 length
. 如下:
CREATE INDEX index_name on table_name (name(10));
用 name
列的前 10 个字符创建索引。
使用列的一部分创建索引可以使索引文件大大减小,从而节约大量的磁盘空间,有可能提高INSERT操作的速度。
总结:length字段的作用,即创建索引时,指定使用字段中的具体部分长度类创建索引。
CREATE INDEX
语法只能创建两种类型的索引:INDEX
和 UNIQUE
.
-- 普通索引
create index index_name on table_name (column_list);
-- 唯一索引
create unique index index_name on table_name (column_list);
不能用 CREATE INDEX
语句创建 PRIMARY KEY
索引.
例子:
drop table if exists `student`;
create table student(
`id` int not null,
`name` varchar(50) not null,
`age` int default 0,
`number` varchar(100) not null
)Engine=InnoDB DEFAULT CHARSET=utf8;
create index name_index on student (name);
create unique index number_index on student (number);
得到的表如下所示,用 name
列创建了普通索引 name_index
. 用 number
列创建了唯一索引 number_index
. (注意在创建唯一索引时必须是 create unique index
,不能像 ALTER TABLE 那里省略).
删除索引可以使用 ALTER TABLE
或 DROP INDEX
语句来实现。DROP INDEX
可以在 ALTER TABLE
内部作为一条语句处理,其格式如下:
drop index index_name on table_name; -- 普通索引和唯一索引可用此方法删除,主键索引不能用此方法删除
alter table table_name drop index index_name; -- 主键索引不能用此方法删除
alter table table_name drop primary key; -- 可以删除主键索引,前提是主键没有自增 auto_increment,如果有自增将无法删除,需先删除自增
删除主键索引只能采用第三种方法,因为一个表只能有一个 PRIMARY KEY
索引,因此不需要指定索引名。并且在删除主键索引的时候必须先删除主键的AUTO_INCREMENT
,否则将不能删除主键索引。
如果从表中删除某列,则索引会受影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引也将被删除。
组合索引
和 前缀索引
是对建立索引技巧的一种称呼,并不是索引的类型。为了更好的表述清楚,建立一个demo表如下:
drop table if exists `user_demo`;
create table user_demo
(
`id` int not null auto_increment comment '主键',
`login_name` varchar(100) not null comment '登录名',
`password` varchar(100) not null comment '密码',
`city` varchar(30) not null comment '城市',
`age` int not null comment '年龄',
`sex` int not null comment '性别(0:女 1:男)',
primary key (`id`)
)Engine=InnoDB DEFAULT CHARSET=utf8;
为了进一步榨取MySQL的效率,就可以考虑建立组合索引,即将login_name
、city
、age
建到一个索引里:
alter table user_demo add index name_city_age (login_name(16), city, age);
建表时,login_name
的长度为100,这里用16,是因为一般情况下名字的长度不会超过16,这样会加快索引查询速度,还会减少索引文件的大小,提高INSERT、UPDATE操作的速度。
如果分别给login_name
、city
、age
建立单列索引,让该表有3个单列索引,查询时和组合索引的效率是大不一样的,甚至远远低于我们的组合索引。虽然此时有3个索引,但MySQL只能用到其中的那个它认为似乎是最有效的单列索引,另外两个是用不到的,也就是说还是一个全表扫描的过程。
建立这样的组合索引,就相当于分别建立如下3种组合索引:
login_name, city, age
login_name, city
login_name
为什么没有 city, age
这样的组合索引呢?因为MySQL组合索引"最左前缀匹配"的结果。简单地理解就是只从最左边的开始组合,并不是只要包含这3列的查询都会用到该组合索引。也就是说 name_city_age(login_name(16), city, age
从左到右进行索引,如果没有左前索引,MySQL不会执行索引查询。
如果索引列长度过长,这种列索引时就会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行,前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值可以创建)。
select count(distinct(left(`login_name`, 10))) / count(*) from arctic; --这个值大于0.31就可以创建索引,distinct去重
alter table `user_demo` add index index_name (login_name(10)); --增加前缀索引SQL,将人名的索引建立在10,这样可以减少索引文件大小,加快索引查询速度
show index from table_name;
可以得到表里所有索引的详细信息。例子:
Explain
可以帮助开发人员分析SQL问题,explain
显示了MySQL如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。
使用该方法,在select语句前加上Explain
就可以了。
explain select * from user where id=1;
尽量避免这些不走索引的SQL:
select `login_name` from `user_demo` where `age` + 10 = 30; --不会走索引,因为所有索引列参与了计算
select `login_name` from `user_demo` where left(`age`, 1) < 3; --不会使用索引,因为使用了函数运算,原理与上面相同
select * from `user_demo` where `login_name` like "Jack%"; -- 走索引
select * from `user_demo` where `login_name` like "%Jack%"; -- 不走索引
正则表达式不适用索引,因此很少在SQL中看到regexp关键字
字符串与数字比较不适用索引
create table `a` (`a` char(10));
explain select * from `a` where `a`="1"; -- 走索引
explain select * from `a` where `a`=1; -- 不走索引
避免使用关键字 or
select * from dept where dname='xxx' or loc='xx' or deptno=45;
如果条件中有 or
,即使其中有条件带索引也不会使用,换言之,就是要求使用的所有字段都必须建立索引,我们建议大家尽量避免使用关键字 or
.
如果MySQL估计使用全表扫描要比使用索引块,则不使用索引
索引虽然好处很多,但过多的索引可能带来相反的问题,索引也是有缺点的:
INSERT
、UPDATE
和 DELETE
。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件;索引只是提高效率的一个方式,如果MySQL有大数据量的表,就要花时间研究建立最优的索引,或优化查询语句。
只要列中包含有NULL值,都将不会被包含在索引中,符合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。
对串列进行索引,如果可以就应该指定一个前缀长度。例如,如果有一个char(255)的列,如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么 order by 中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作,尽量不要包含多个列的排序,如果需要最好给这些列建复合索引。
一般情况下不鼓励使用like操作,如果非用不可,注意正确的使用方式。like '%aaa%'不会使用索引,而 like ‘aaa%’ 可以使用索引.
NOT IN
、<>
、!=
操作,但是<
、<=
、=
、>
、>=
、BETWEEN
、IN
是可以用到索引的这是因为,如果这些列很好用到的话,那么有无索引并不能明显改变查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
text
、image
、bit
数据类型的别不应该增加索引。因为这些列的数据要么相当大,要么取值很少where
和 join
中出现的列需要建立索引主键和外检的数据类型相同
时才使用索引,否则即使建立了索引也不会使用加了索引过后,如何看在查询的时候是否真的用到了索引呢?
通过 explain
关键字查看是否用到了索引,详细内容如下篇博客所示:
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。