MySQL学习(索引、引擎、优化)ITeye - 威尼斯人

MySQL学习(索引、引擎、优化)ITeye

2019-01-10 22:49:59 | 作者: 瀚彭 | 标签: 索引,引擎,运用 | 浏览: 666

索引关于查询的速度至关重要,了解索引也是数据库调优的起点。

1. 树立索引前,先设计好树立索引列的数据类型。
1)越小的数据类型功能越好:由于越小的数据类型关于硬盘读取、内存、CPU缓存都需求更少的空间,处理起来更快。
2)简略的数据类型更好:整型比字符型更好。
3)尽量防止运用NULL: 树立索引的列最好是Not Null束缚的,假如必定要用NULL,能够用0或许某特别值代替。由于在MySQL中,关于NULL的值很难进行查询优化,由于他们使得索引的核算愈加杂乱。

2. 索引概述:
关于任何联系型数据库,索引都是进行优化的最首要要素。关于少数的数据,不用运用索引。
假如关于多列进行索引(组合索引),列的次序十分中意。MySQL仅对索引最左面的前缀进行有用的查找。
例如:存在组合索引(c1, c2), 查询句子select * from t1 where c1=1 and c2=2能够运用该索引。select * from t1 where c1=1也能够运用该索引。但select * from t1 where c2=2不能运用该索引。
理由是,没有组合索引的引导列,即,要想运用c2列进行索引,有必要呈现c1等于某值。

2.1 索引的类型
索引与引擎的联系:
索引是在存储引擎中完成的,而不是在服务器层中完成的。所以每种存储引擎的索引都不用定相同,并不是一切的存储引擎都支撑一切的索引类型。

2.1.1 B-Tree索引
假如有一个表:
create table people (
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum(m, f) not null,
key(last_name, first_name, dob)
);
其索引包括了表中每一行的last_name, first_name, dob列。

索引存储的值依照索引列中的次序排序(先last_name, 在first_name, 再dob). 能够用B-Tree索引进行全关键字、关键字规模、关键字前缀进行查询。
所以能够对如下景象收效:
1)匹配全值(Match the full value): 对索引中一切列都指定详细的值。例如查找出1960-01-01出世的Cuba Allen.
2)匹配最左前缀(Match a leftmost prefix): 能够运用索引查找last_name为Allen的人。
3)匹配列前缀(Match a column prefix): 能够列用索引查找last_name为J最初的人,只是运用索引中的榜首列。
4)匹配值的规模查询(Match a range of values): 能够运用索引查找last_name在Allen和Barrymore之间的人,只是运用索引中的榜首列。
5)匹配部分准确而其他部分进行规模匹配(Match one part exactly and match a range on another part): 能够运用索引查找last_name为Allen, 而first_name为K最初的人。

由于B-Tree中的节点都是次序存储的,所以能够运用索引进行查找(找到某值),也能够对查询成果order by.

运用B-Tree索引有下面的约束:
1)查询有必要从索引的最左面的列开端
2)不能越过某一列索引:即不能运用索引查询last_name为Allen, 且1980-01-01出世的人。有必要先查first_name后再查dob.
3)存储引擎不能运用索引中规模条件右边的列。例如:where last_name=Smith and first_name like J% and dob=1976-01-02, 则索引只对last_name和first_name有用,由于like是规模查询。

2.1.2 Hash索引
MySQL中,只要Memory存储引擎支撑hash索引,是Memory引擎默许的索引类型。虽然Memory表也能够运用B-Tree索引。

3. 高功能的索引战略
3.1 聚簇索引(Clustered Indexes) -- 在MySQL的InnoDB引擎中,主键索引便是聚簇索引。
聚簇索引确保关键字的值附近的元组存储的物理方位也相同(所以字符串类型不合适创立索引,特别是随机字符串,会使得体系进行许多的移动操作),且一个表只能有一个聚簇索引。
现在,支撑聚簇索引的引擎只要InnoDB和solidDB.

3.1.1 聚簇索引和非聚簇索引的差异:InnoDB引擎支撑聚簇索引,而MyISAM不支撑聚簇索引。
例如:
create table layout_test (
col1 int NOT NULL,
col2 int NOT NULL,
primary key(col1),
key(col2)
);
假定主键坐落1--10000之间,且按随机次序刺进,然后由optimize table进行优化。col2随机赋予1--100之间的值,所以会存在许多重复的值。
1)MyISAM的布局:
MyISAM依照刺进的次序在磁盘上存储数据。
注:MyISAM不支撑聚簇索引,索引中每一个叶子节点只是包括行号(row number), 且叶子节点依照col1的次序存储。
2)InnoDB的布局:
InnoDB按聚簇索引的方法存储数据。
注:聚簇索引总每个叶子节点包括primary key的值,业务ID和回滚指针(rollback pointer).

3.2 掩盖索引(Covering Indexes)
假如索引包括满意查询的一切数据,就成为掩盖索引。
掩盖一切有如下优点:
1)索引项一般比记载要小,所以MySQL拜访更好的数据。
2)索引都按值的巨细次序存储,相关于随机拜访记载,需求更少的IO.
3)大多数数据引擎更好的缓存了索引。比方MySQL只缓存索引。
4)掩盖索引关于InnoDB表特别有用,由于InnoDB运用聚簇索引安排数据,假如二级索引中包括所需的数据,就不需求在索引中查找了。

3.3 运用索引进行排序
MySQL中,有两种方法生成有序的成果集:一是运用filesort, 二是按索引次序扫描。
运用索引进行排序十分快,且能够运用同一索引一同进行查找和排序操作。
当索引的次序与order by的列次序相一同,且一切的列是同一方向(悉数升序或许悉数降序)时,能够用索引排序。
假如查询时多表衔接,仅当order by中一切的列都是榜首个表的列时,索引才会收效。其他状况都会运用filesort.

例如:
create table actor (
actor_id int unsigned not null auto_increment,
name varchar(16) not null default,
password varchar(16) not null default,
primary key(actor_id),
key(name)
) engine=InnoDB
insert into actor(name,password) values(cat01,1234567);
insert into actor(name,password) values(cat02,1234567);
insert into actor(name,password) values(ddddd,1234567);
insert into actor(name,password) values(aaaaa,1234567);

mysql explain select actor_id from actor order by actor_id;
成果:
id:1
select_type:SIMPLE
table:actor
type:index
possible_keys:NULL
key:PRIMARY
ref:NULL
rows:4
Extra:Using index
1 row in set(0.00 sec)

mysql explain select actor_id from actor order by password;
成果:
id:1
select_type:SIMPLE
table:actor
type:index
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL
rows:4
Extra:Unsing filesort
1 row in set(0.00 sec)

mysql explain select actor_id from actor order by name;
成果:
id:1
select_type:SIMPLE
table:actor
type:index
possible_key:NULL
key:name
key_len:18
ref:NULL
rows:4
Extra:Using index

当MySQL不能运用索引进行排序时,就会运用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序。假如内存装载不下,它会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个数据库合并成有序的成果集。

3.4 索引与加锁
索引关于InnoDB十分重要,由于它能够让查询确定更少的元组。由于InnoDB直到业务提交时才会解锁。有两个原因:
1. 即便InnoDB行级锁十分高效,内存开支十分小,但仍是存在开支。
2. 对不需求的元组加锁,会增加锁的开支,下降并发功能。
InnoDB仅对需求拜访的元组加锁,而索引能够削减InnoDB拜访的元组数。

===========================
MyISAM和InnoDB的差异:
1. 业务处理:MyISAM不支撑,InnoDB支撑
2. 功能:MyISAM好于InnoDB
3. fulltext索引:MyISAM支撑,InnoDB不支撑
4. select count(*) from table1; MyISAM功率好:MyISAM仅读出保存好的行数,而InnoDB要扫描一遍整个表。
5. auto_increment: MyISAM能够用该列与其他列一同树立联合索引,而InnoDB只支撑包括只要该字段的索引。
6. delect from table; InnoDB会一行一行删去,不会从头树立表。

综上,MyISAM和InnoDB的首要差异是,InnoDB支撑业务处理和行级锁。而MyISAM不支撑,所以MyISAM往往被以为只合适在小项目中运用。
MyISAM的优点:
1. 关于读多写少的景象,MyISAM的功能比InnoDB好许多。
2. MyISAM的索引和数据是分隔的,而且索引是有紧缩的,这样内存运用率就提高了不少。而InnoDB的索引和数据是严密绑缚的,并没有运用紧缩,从而会形成InnoDB的体积更巨大。

一般来说,MyISAM合适:
1. 做许多count核算
2. 刺进不频频,查询十分频频
3. 没有业务

InnoDB合适于:
1. 牢靠性要求比较高,或许要求业务
2. 表更新和查询都十分的频频,而且表确定的时机比较大。

============================

MySQL的explain

explain的作用是显现了MySQL怎么运用索引来处理select句子以及衔接表。能够协助挑选更好的索引和写出更优化的查询句子。
运用方法:在select句子前加上explain就能够了。

例如:explain select surname, first_name from a, b where a.id=b.id

剖析成果方法如下:
table | type | possible_keys | key | key_len | ref | rows | extra

别离解说如下:
1. table:显现这一行的数据是关于哪张表的
*2. type:显现衔接运用了哪种类型。最好到最差的衔接类型为:const, eq_reg, ref, range, index, ALL
3. possible_key: 显现能够运用在这张表上的索引。
*4. key: 实际运用的索引。假如为NULL, 则没有运用索引。
5. key_len: 索引的长度
6. ref: 显现索引的哪一列被运用了。
7. rows: MySQL以为有必要查看的用来回来恳求数据的行数。
*8. extra: 额定信息:
1)distinct: 一旦找到与行相匹配的行,就不再查找了。
2)not exist: MySQL优化了左衔接,一旦它找到了匹配left join规范的行,就不再查找了。
3)using filesort: 指Mysql将用外部排序而不是依照index次序排列成果。数据较少时从内存排序,否则从磁盘排序。Explain不会显现的通知客户端用哪种排序。
4)using index: 表明Mysql运用掩盖索引防止全表扫描,不需求再到表中进行二次查找数据。
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表威尼斯人立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1
  • 2
  • 3
  • 4

    MongoDBITeye

    文档,测验,调集
  • 5

    RAC的Diskgroup重建ITeye

    磁盘,重建,数据库
  • 6

    Switch to UTFITeye

    编码,设置,文件
  • 7

    pl/sql使用之使用utlITeye

    文件,办法,输出
  • 8
  • 9

    (转)in 和 existITeye

    分区,查询,一个
  • 10

    oracle正则表达式ITeye

    正则表达式,匹配,表达式