mysql 索引
一个索引 :可以理解是创建一个额外额文件
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。上述SQL语句,在没有索引的情况下,数据库会遍历全部200万条数据后选择符合条件的;而有了相应的索引之后,数据库会直接在索引中查找符合条件的选项。如果我们把SQL语句换成“SELECT * FROM article WHERE id=2000000”,那么你是希望数据库按照顺序读取完200万行数据以后给你结果还是直接在索引中定位呢?加上索引后查询数据会直接在索引中定位。
---------------------
在MYSQL中建立任何一张数据表,
在其数据目录对应的数据库目录下都有对应表的.frm文件,.frm文件是用来保存每个数据表的元数据(meta)信息,包括表结构的定义等,
.frm文件跟数据库存储引擎无关,也就是任何存储引擎的数据表都必须有.frm文件,命名方式为数据表名.frm,如user.frm. .frm文件可以用来在数据库崩溃时恢复表结构。
MySQL文件包括MySQL所建数据库文件和MySQL所用引擎创建的数据库文件。
.frm 文件与操作系统和数据库引擎无关,都有这么个与表名同名文件。
MyISAM引擎的文件:
.myd 即 my data,表数据文件
.myi 即my index,索引文件
.log 日志文件。
InnoDB引擎的文件:
采用表空间(tablespace)来管理数据,存储表数据和索引,
InnoDB数据库文件(即InnoDB文件集,ib-file set):
ibdata1、ibdata2等:系统表空间文件,存储InnoDB系统信息和用户数据库表数据和索引,所有表共用。
.ibd文件:单表表空间文件,每个表使用一个表空间文件(file per table),存放用户数据库表数据和索引。
日志文件: ib_logfile1、ib_logfile2
################################################################################################################################################################
Innodb存储引擎管理主要基于两个文件:表空间数据文件和日志文件。
InnoDB存储它的表&索引在一个表空间中,表空间可以包含数个文件(或原始磁盘分区)。
如果没有指定InnoDB配置选项,MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件,以及两个名为ib_logfile0和ib_logfile 1的5MB大小的日志文件。
ibdata1的大小在my.cnf文件中配置:innodb_data_file_path = ibdata1:10G:autoextend
可以设置最大数据文件限制,以免超过系统支持的最大文件:
innodb_data_file_path = ibdata1:100M:autoextend:max:500M
日志文件大小在my.cnf文件中配置:innodb_log_file_size = 256M innodb_log_files_in_group = 2
Innodb存储引擎可以使用共享表空间或独立表空间,使用独立表空间时,需要将innodb_file_per_table加到配置文件中,也可以在variables中开启。
共享表空间是将所有的表的数据和索引保存在ibdata1中,这样的缺点是拷贝时必须拷贝整个大文件,而且删除表后容易产生碎片。
独立表空间是为每个表建立一个.ibd文件用来存储数据和.frm用来存数据词典信息,这样,mysql就将innodb表的数据存入各自对应的.ibd文件中了,但结构等信息还是会写入ibdata。
innodb_file_per_table变量只能在配置文件里修改,不能使用set global ...
将innodb_file_per_table关闭之后,建立innoDB表时只生成.frm文件,数据和索引都保存在共享表空间ibdata1中。################################################################################################################################################################
MySQL数据库存放位置:
1、MySQL如果使用MyISAM存储引擎,数据库文件类型就包括.frm、.MYD、.MYI,默认存放位置是C:\Documentsand Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data
2、MySQL如果使用InnoDB存储引擎,数据库文件类型就包括.frm、ibdata1、.ibd,存放位置有两个,
.frm文件默认存放位置是C:\Documents and Settings\All Users\ApplicationData\MySQL\MySQL Server 5.1\data,ibdata1、.ibd文件默认存放位置是MySQL安装目录下的data文件夹
索引算法:
hash索引表顺序和原来不一致了
如 表A
id | name |
1 | lidongming |
如 name 的 hash 索引是 name hash值+ 内存指针
优点 对于固定的 索引 where id =3 || where name =’sdd‘ 这种查询快
缺点 范围 id > 3 比较 慢
btree 索引:
范围 id > 3 和 对于固定的 索引 都好, 原因
首先是父亲,然后子的左边小右边大 这种规律 少查了很多次。 如 24** 40 就查了 40 次 。
mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2";
-- 结果:
id: 1
select_type: SIMPLE -- 查询类型(简单查询、联合查询、子查询)
table: user -- 显示这一行的数据是关于哪张表的 。
type: range -- 区间索引(在小于1990/2/2区间的数据),这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,
const代表一次就命中,ALL代表扫描了全表才确定结果。
一般来说,得保证查询至少达到range级别,最好能达到ref。
possible_keys: birthday -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。
key: birthday -- 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。
key_len: 4 -- 最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好。
ref: const -- 显示哪个字段或常数与key一起被使用。
rows: 1 -- 这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。
Extra: Using where; Using index -- 执行状态说明,这里可以看到的坏的例子是Using temporary和Using
select_type
- simple:简单select(不使用union或子查询)。
- primary:最外面的select。
- union:union中的第二个或后面的select语句。
- dependent union:union中的第二个或后面的select语句,取决于外面的查询。
- union result:union的结果。
- subquery:子查询中的第一个select。
- dependent subquery:子查询中的第一个select,取决于外面的查询。
- derived:导出表的select(from子句的子查询)。
其它说明
- Distinct:一旦MYSQL找到了与行相联合匹配的行,就不再搜索了。
- Not exists: MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了。
- Range checked for each Record(index map:#):没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一。
- Using filesort: 看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
- Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候。
- Using temporary 看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上。
- Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序)。
- system 表只有一行:system表。这是const连接类型的特殊情况。
- const:表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待。
- eq_ref:在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用。
- ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于索引匹配的记录多少,越少越好。
- range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况。
- index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据)。
- ALL:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免。
其中type:
- 如果是Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。
- 如果是where used,就是使用上了where限制。
- 如果是impossible where 表示用不着where,一般就是没查出来啥。
- 如果此信息显示Using filesort或者Using temporary的话会很吃力,WHERE和ORDER BY的索引经常无法兼顾,如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。
索引的类型
UNIQUE唯一索引
不可以出现相同的值,可以有NULL值。
INDEX普通索引
允许出现相同的索引内容。
PRIMARY KEY主键索引
不允许出现相同的值,且不能为NULL值,一个表只能有一个primary_key索引。
fulltext index 全文索引
上述三种索引都是针对列的值发挥作用,但全文索引,可以针对值中的某个单词,比如一篇文章中的某个词,然而并没有什么卵用,因为只有myisam以及英文支持,并且效率让人不敢恭维,但是可以用coreseek和xunsearch等第三方应用来完成这个需求。
索引的CURD
索引的创建
ALTER TABLE
适用于表创建完毕之后再添加。
ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)
ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名,可要可不要;如果不要,当前的索引名就是该字段名。
ALTER TABLE `table_name` ADD UNIQUE (`column_list`)
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`)
ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)
CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引。
--例:只能添加这两种索引
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
另外,还可以在建表时添加:
CREATE TABLE `test1` (
`id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 注意,下面创建了主键索引,这里就不用创建了
`username` varchar(64) NOT NULL COMMENT '用户名',
`nickname` varchar(50) NOT NULL COMMENT '昵称/姓名',
`intro` text,
PRIMARY KEY (`id`),
UNIQUE KEY `unique1` (`username`), -- 索引名称,可要可不要,不要就是和列名一样
KEY `index1` (`nickname`),
FULLTEXT KEY `intro` (`intro`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='后台用户表';
索引的删除
DROP INDEX `index_name` ON `talbe_name`
ALTER TABLE `table_name` DROP INDEX `index_name`
-- 这两句都是等价的,都是删除掉table_name中的索引index_name;
ALTER TABLE `table_name` DROP PRIMARY KEY -- 删除主键索引,注意主键索引只能用这种方式删除
索引的查看
show index from tablename;
索引的更改
更改个毛线,删掉重建一个既可
创建索引的技巧
- 维度高的列创建索引。
- 数据列中不重复值出现的个数,这个数量越高,维度就越高。
- 如数据表中存在8行数据a,b ,c,d,a,b,c,d这个表的维度为4。
- 要为维度高的列创建索引,如性别和年龄,那年龄的维度就高于性别。
- 性别这样的列不适合创建索引,因为维度过低。
- 对 where,on,group by,order by 中出现的列使用索引。
- 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键。
- 为较长的字符串使用前缀索引。
- 不要过多创建索引,除了增加额外的磁盘空间外,对于DML操作的速度影响很大,因为其每增删改一次就得从新建立索引。
- 使用组合索引,可以减少文件索引大小,在使用时速度要优于多个单列索引。
组合索引与前缀索引
注意,这两种称呼是对建立索引技巧的一种称呼,并非索引的类型。
组合索引
MySQL单列索引和组合索引究竟有何区别呢?
为了形象地对比两者,先建一个表:
CREATE TABLE `myIndex` (
`i_testID` INT NOT NULL AUTO_INCREMENT,
`vc_Name` VARCHAR(50) NOT NULL,
`vc_City` VARCHAR(50) NOT NULL,
`i_Age` INT NOT NULL,
`i_SchoolID` INT NOT NULL,
PRIMARY KEY (`i_testID`)
);
假设表内已有1000条数据,在这 10000 条记录里面 7 上 8 下地分布了 5 条 vc_Name=”erquan” 的记录,只不过 city,age,school 的组合各不相同。来看这条 T-SQL:
SELECT `i_testID` FROM `myIndex` WHERE `vc_Name`='erquan' AND `vc_City`='郑州' AND `i_Age`=25; -- 关联搜索;
首先考虑建MySQL单列索引:
在 vc_Name 列上建立了索引。执行 T-SQL 时,MYSQL 很快将目标锁定在了 vc_Name=erquan 的 5 条记录上,取出来放到一中间结果集。在这个结果集里,先排除掉 vc_City 不等于”郑州”的记录,再排除 i_Age 不等于 25 的记录,最后筛选出唯一的符合条件的记录。虽然在 vc_Name 上建立了索引,查询时MYSQL不用扫描整张表,效率有所提高,但离我们的要求还有一定的距离。同样的,在 vc_City 和 i_Age 分别建立的MySQL单列索引的效率相似。
为了进一步榨取 MySQL 的效率,就要考虑建立组合索引。就是将 vc_Name,vc_City,i_Age 建到一个索引里:
ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age);
建表时,vc_Name 长度为 50,这里为什么用 10 呢?这就是下文要说到的前缀索引,因为一般情况下名字的长度不会超过 10,这样会加速索引查询速度,还会减少索引文件的大小,提高 INSERT 的更新速度。
执行 T-SQL 时,MySQL 无须扫描任何记录就到找到唯一的记录!
如果分别在 vc_Name,vc_City,i_Age 上建立单列索引,让该表有 3 个单列索引,查询时和上述的组合索引效率一样吗?答案是大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引,另外两个是用不到的,也就是说还是一个全表扫描的过程。
建立这样的组合索引,其实是相当于分别建立了:
- vc_Name,vc_City,i_Age
- vc_Name,vc_City
- vc_Name
这样的三个组合索引!为什么没有 vc_City,i_Age 等这样的组合索引呢?这是因为 mysql 组合索引 “最左前缀” 的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个 T-SQL 会用到:
SELECT * FROM myIndex WHREE vc_Name=”erquan” AND vc_City=”郑州” SELECT * FROM myIndex WHREE vc_Name=”erquan”
而下面几个则不会用到:
SELECT * FROM myIndex WHREE i_Age=20 AND vc_City=”郑州” SELECT * FROM myIndex WHREE vc_City=”郑州”
也就是,name_city_age(vc_Name(10),vc_City,i_Age) 从左到右进行索引,如果没有左前索引Mysql不执行索引查询。
前缀索引
如果索引列长度过长,这种列索引时将会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行索引前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值就可以创建)。
SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; — 这个值大于0.31就可以创建前缀索引,Distinct去重复 ALTER TABLE `user` ADD INDEX `uname`(title(10)); — 增加前缀索引SQL,将人名的索引建立在10,这样可以减少索引文件大小,加快索引查询速度。
什么样的sql不走索引
要尽量避免这些不走索引的sql
SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引,因为所有索引列参与了计算
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引,因为使用了函数运算,原理与上面相同
SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引
-- 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因
-- 字符串与数字比较不使用索引;
CREATE TABLE `a` (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引
select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引,我们建议大家尽量避免使用or 关键字
-- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
多表关联时的索引效率
- SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; — 不会使用索引,因为使用了函数运算,原理与上面相同
- SELECT * FROM `houdunwang` WHERE `uname` LIKE’后盾%’ — 走索引
- SELECT * FROM `houdunwang` WHERE `uname` LIKE “%后盾%” — 不走索引
从上图可以看出,所有表的type为all,表示全表索引。也就是6 6 6,共遍历查询了216次。
除第一张表示全表索引(必须的,要以此关联其他表),其余的为range(索引区间获得),也就是6+1+1+1,共遍历查询9次即可。
所以我们建议在多表join的时候尽量少join几张表,因为一不小心就是一个笛卡尔乘积的恐怖扫描,另外,我们还建议尽量使用left join,以少关联多。因为使用join 的话,第一张表是必须的全扫描的,以少关联多就可以减少这个扫描次数。
索引的弊端
不要盲目的创建索引,只为查询操作频繁的列创建索引,创建索引会使查询操作变得更加快速,但是会降低增加、删除、更新操作的速度,因为执行这些操作的同时会对索引文件进行重新排序或更新。
但是,在互联网应用中,查询的语句远远大于DML的语句,甚至可以占到80%~90%,所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引。
索引名词:
. 普通索引
这是最基本的索引,它没有任何限制,比如上文中为title字段创建的索引就是一个普通索引,MyIASM中默认的BTREE类型的索引,也是我们大多数情况下用到的索引。
唯一索引
与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。
全文索引(FULLTEXT)
不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
单列索引、多列索引
多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。
. 组合索引(最左前缀)
平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引:ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:
覆盖索引: 查的事索引内容,就是覆盖索引
组合索引 效果 > 多列索引
索引分类:
MySQL索引的优化
聚集索引:
一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理顺序。
聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。
聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节省成本。
当索引值唯一时,使用聚集索引查找特定的行也很有效率。例如,使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法,是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。
非聚集索引:
非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容,该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块。
备注:聚集索引一张表只能创建一个,非聚集索引一张表可以创建多个,在mysql中InnoDB引擎是唯一支持聚集索引的存储引擎。InnoDB按照主键(Primary Key)进行聚集,如果没有定义主键,InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引,InnoDB就会定义隐藏的主键然后在上面进行聚集。
1. 何时使用聚集索引或非聚集索引?
2. 索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
3. 使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
例:CREATE INDEX index_name ON table(column(10 or 20));
4. 索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
5. like语句操作
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
6. 不要在列上进行运算
例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate<’2007-01-01′。关于这一点可以围观:一个单引号引发的MYSQL性能损失。
---------------------
总结:
最后总结一下,MySQL只对以下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引,不过除非是数据量真的很多,否则过多的使用索引也不是那么好玩的,比如我刚才针对text类型的字段创建索引的时候,系统差点就卡死了
---------------------