【ONED-976】ギリギリモザイク 6つのコスチュームでパコパコ！ Ami MySQL索引旨趣
一般的应用系统，读写比例在10:1傍边，而且插入操作和一般的更新操作很少出现性能问题，在分娩环境中，咱们碰到最多的，亦然最容易出问题的，如故一些复杂的查询操作，因此对查询语句的优化显著是重中之重。提及加速查询，就不得不提到索引了。
1.2 什么是索引？索引在MySQL中也叫是一种“键”，是存储引擎用于快速找到纪录的一种数据结构。索引关于精粹的性能颠倒关节，尤其是当表中的数据量越来越大时，索引关于性能的影响愈发烧切。
索引优化应该是对查询性能优化最有用的技能了。索引能够豪放将查询性能提高好几个数目级。
索引相等于字典的音序表，淌若要查某个字，淌若不使用音序表，则需要从几百页中逐页去查。
1.3 你是否对索引存在歪曲？索引是应用圭臬盘算和开拓的一个热切方面。若索引太多，应用圭臬的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个均衡点，这对应用圭臬的性能至关热切。一些开拓东说念主员老是在过后才念念起添加索引----我一直合计，这源于一种失实的开拓模式。淌若知说念数据的使用，从一发轫就应该在需要处添加索引。开拓东说念主员常常对数据库的使用停留在应用的层面，比如编写SQL语句、存储进程之类，他们以至可能不知说念索引的存在，或合计过后让关系DBA加上即可。DBA常常不够了解业务的数据流，而添加索引需要通过监控多半的SQL语句进而从中找到问题，这个形式所需的时候深信是繁多于发轫添加索引所需的时候，况兼可能会遗漏一部分的索引。固然索引也并不是越多越好，我也曾碰到过这么一个问题：某台MySQL奇迹器iostat知道磁盘使用率一直处于100%，经过分析后发现是由于开拓东说念主员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率随即下跌为20%。可见索引的添加也长短常有工夫含量的。
二、索引的旨趣 2.1 索引旨趣索引的方向在于提高查询遵守，与咱们查阅典籍所用的目次是一个真义：先定位到章，然后定位到该章下的一个末节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
本体王人是：通过束缚地松开念念要赢得数据的范畴来筛选出最终念念要的驱散，同期把偶然的事件酿陋习矩的事件，也即是说，有了这种索引机制，咱们可以老是用并吞种查找方式来锁定数据。
数据库亦然一样，但显著要复杂的多，因为不仅靠近着等值查询，还有范畴查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该禁受怎样样的方式来搪塞扫数的问题呢？咱们回念念字典的例子，能弗成把数据分红段，然后分段查询呢？最苟简的淌若1000条数据，1到100分红第一段，101到200分红第二段，201到300分红第三段......这么查第250条数据，唯有找第三段就可以了，一下子去除了90%的无效数据。但淌若是1千万的纪录呢，分红几段相比好？稍有算法基础的同学会念念到搜索树，其平均复杂度是lgN，具有可以的查询性能。但这里咱们忽略了一个关节的问题，复杂度模子是基于每次疏通的操作资原本计划的。而数据库达成相比复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来规划，因为咱们知说念探望磁盘的资本简略是探望内存的十万倍傍边，是以苟简的搜索树难以餍足复杂的应用场景。
2.2 磁盘IO与预读前边提到了探望磁盘，那么这里先苟简先容一下磁盘IO和预读，磁盘读取数据靠的是机械指点，每次读取数据突然的时候可以分为寻说念时候、旋转延伸、传输时候三个部分，寻说念时候指的是磁臂迁徙到指定磁说念所需要的时候，主流磁盘一般在5ms以下；旋转延伸即是咱们常常外传的磁盘转速，比如一个磁盘7200转，示意每分钟能转7200次，也即是说1秒钟能转120次，旋转延伸即是1/120/2 = 4.17ms；传输时候指的是从磁盘读出或将数据写入磁盘的时候，一般在零点几毫秒，联系于前两个时候可以忽略不计。那么探望一次磁盘的时候，即一次磁盘IO的时候约等于5+4.17 = 9ms傍边，听起来还挺可以的，但要知说念一台500 -MIPS（Million Instructions Per Second）的机器每秒可以蔓延5亿条教导，因为教导依靠的是电的性质，换句话说蔓延一次IO的时候可以蔓延约450万条教导，数据库动辄十万百万乃至千万级数据，每次9毫秒的时候，显著是个横祸。下图是规划机硬件延伸的对比图，供大家参考：
计划到磁盘IO长短常文明的操作，规划机操作系统作念了一些优化，当一次IO时，不光把刻下磁盘地址的数据，而是把相邻的数据也王人读取到内存缓冲区内，因为局部预读性旨趣告诉咱们，当规划机探望一个地址的数据的时候，与其相邻的数据也会很快被探望到。每一次IO读取的数据咱们称之为一页(page)。具体一页有多大数据跟操作系统关系，一般为4k或8k，也即是咱们读取一页内的数据时候，现实上才发生了一次IO，这个表面关于索引的数据结构盘算颠倒有匡助。
三、索引的数据结构MySQL索引的数据结构-B+树先容：https://www.cnblogs.com/nickchen121/p/11152523.html
四、MySQL索引处理 4.1 功能 索引的功能即是加速查找 mysql中的primary key，unique，聚合独一也王人是索引，这些索引除了加速查找除外，还有拘谨的功能 4.2 MySQL常用的索引 平日索引INDEX：加速查找 独一索引：主键索引PRIMARY KEY：加速查找+拘谨（不为空、弗成重叠） 独一索引UNIQUE:加速查找+拘谨（弗成重叠）
聚合索引：PRIMARY KEY(id，name):聚合主键索引 UNIQUE(id，name):聚合独一索引 INDEX(id，name):聚合平日索引
4.3 各个索引应用场景举个例子来说，比如你在为某阛阓作念一个会员卡的系统。 这个系统有一个会员表 有下列字段： 会员编号 INT 会员姓名 VARCHAR(10) 会员身份证号码 VARCHAR(18) 会员电话 VARCHAR(10) 会员住址 VARCHAR(50) 会员备注信息 TEXT 那么这个 会员编号，作为主键，使用 PRIMARY 会员姓名 淌若要建索引的话，那么即是平日的 INDEX 会员身份证号码 淌若要建索引的话，那么可以禁受 UNIQUE （独一的，不允许重叠） # 除此之外还有全文索引，即FULLTEXT 会员备注信息 ， 淌若需要建索引的话，可以禁受全文搜索。 用于搜索很长一篇著述的时候，成果最佳。 用在相比短的文本，淌若就一两行字的，平日的 INDEX 也可以。 但其实关于全文搜索，咱们并不会使用MySQL自带的该索引，而是会禁受第三方软件如Sphinx，成心来作念全文搜索。 # 其他的如空间索引SPATIAL，了解即可，险些无须 各个索引的应用场景 各个索引的应用场景4.4 索引的两大类型hash与btree
咱们可以在创建上述索引的时候，为其指定索引类型，分两类：
hash类型的索引：查询单条快，范畴查询慢 btree类型的索引：b+树，层数越多，数据量指数级增长（咱们就用它，因为innodb默许撑抓它）不同的存储引擎撑抓的索引类型也不一样：
InnoDB 撑抓事务，撑抓行级别锁定，撑抓 B-tree、Full-text 等索引，不撑抓 Hash 索引； MyISAM 不撑抓事务，撑抓表级别锁定，撑抓 B-tree、Full-text 等索引，不撑抓 Hash 索引； Memory 不撑抓事务，撑抓表级别锁定，撑抓 B-tree、Hash 等索引，不撑抓 Full-text 索引； NDB 撑抓事务，撑抓行级别锁定，撑抓 Hash 索引，不撑抓 B-tree、Full-text 等索引； Archive 不撑抓事务，撑抓表级别锁定，不撑抓 B-tree、Hash、Full-text 等索引； 4.5 创建/删除索引的语法# 圭表一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完好性拘谨条款…]，
字段名2 数据类型 [完好性拘谨条款…]，
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
# 圭表二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
# 圭表三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
# 删除索引：DROP INDEX 索引名 ON 表名字;
4.6 示例
# 方式一
create table t1(
id int，
name char，
age int，
sex enum('male'，'female')，
unique key uni_id(id)，
index ix_name(name) # index莫得key
);
create table t1(
id int，
name char，
age int，
sex enum('male'，'female')，
unique key uni_id(id)，
index(name) # index莫得key
);
# 方式二
create index ix_age on t1(age);
# 方式三
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);
# 检察
mysql> show create table t1;
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL，
`name` char(1) DEFAULT NULL，
`age` int(11) DEFAULT NULL，
`sex` enum('male'，'female') DEFAULT NULL，
UNIQUE KEY `uni_id` (`id`)，
KEY `ix_name` (`name`)，
KEY `ix_age` (`age`)，
KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
五、测试索引
5.1 数据准备
# 1. 准备表
create table s1(
id int，
name varchar(20)，
gender char(6)，
email varchar(50)
);
# 2. 创建存储进程，达成批量插入纪录
delimiter $$ # 声明存储进程的驱散记号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i，'eva'，'female'，concat('eva'，i，'@oldboy'));
set i=i+1;
end while;
END$$ # $$驱散
delimiter ; # 再行声明分号为驱散记号
# 3. 检察存储进程
show create procedure auto_insert1\G
# 4. 调用存储进程
call auto_insert1();
1、在莫得索引的前提下测试查询速率
无索引：mysql根底就不知说念到底是否存在id等于333333333的纪录，只可把数据表从新到尾扫描一遍，此时有些许个磁盘块就需要进行些许IO操作，是以查询速率很慢
mysql> select * from s1 where id=333333333; Empty set (0.33 sec)
2、在表中还是存在多半数据的前提下，为某个字段段建筑索引，建筑速率会很慢
3、在索引建筑完了后，以该字段为查询条款时，查询速率升迁昭彰
肃肃：【ONED-976】ギリギリモザイク 6つのコスチュームでパコパコ！ Ami
mysql先去索引内外凭据b+树的搜索旨趣很快搜索到id等于333333333的纪录不存在，IO大大缩短，因而速率昭彰升迁 咱们可以去mysql的data目次下找到该表，可以看到占用的硬盘空间多了 需要肃肃，如下图
MySAM索引文献和数据文献是分离的，索引文献仅保存数据纪录的地址。而在innodb中，表数据文献本人即是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完好的数据纪录。这个索引的key是数据表的主键，因此innodb表数据文献本人即是主索引。 因为inndob的数据文献要按照主键相聚，是以innodb要求表必须要有主键（Myisam可以莫得），淌若莫得显式界说，则mysql系统会自动禁受一个可以独一标记数据纪录的列作为主键，淌若不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.
六、正确使用索引 6.1 索引未掷中并不是说咱们创建了索引就一定会加速查询速率，若念念应用索引达到料念念的提高查询速率的成果，咱们在添加索引时，必须撤职以下问题：
1、范畴问题，或者说条款不解确，条款中出现这些记号或关节字：>、>=、<、<=、!= 、between...and...、like、大于号、小于号
不等于！=
between ...and...
like
2、尽量禁受诀别度高的列作为索引，诀别度的公式是count(distinct col)/count(*)，示意字段不重叠的比例，比例越大咱们扫描的纪录数越少，独一键的诀别度是1，而一些景况、性别字段可能在大数据面前诀别度即是0，那可能有东说念主会问，这个比例有什么告戒值吗？使用场景不同，这个值也很难笃定，一般需要join的字段咱们王人要求是0.1以上，即平均1条扫描10笔纪录。
先把表中的索引王人删除，让咱们专心操办诀别度的问题：
mysql> desc s1; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | name | varchar(20) | YES | | NULL | | | gender | char(5) | YES | | NULL | | | email | varchar(50) | YES | MUL | NULL | | +--------+-------------+------+-----+---------+-------+ rows in set (0.00 sec) mysql> drop index a on s1; Query OK， 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop index d on s1; Query OK， 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc s1; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | gender | char(5) | YES | | NULL | | | email | varchar(50) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ rows in set (0.00 sec)
分析原因：
咱们编写存储进程为表s1批量添加纪录，name字段的值均为egon，也即是说name这个字段的诀别度很低（gender字段亦然一样的，咱们稍后再答理它）
回忆b+树的结构，查询的速率与树的高度成反比，要念念将树的高下驱散的很低，需要保证：在某一层内数据项均是按照从左到右，从小到大的规矩递次排开，即左1<左2<左3<...
而关于诀别度低的字段，无法找到大小关系，因为值王人是很是的，毫无疑问，还念念要用b+树存放这些等值的数据，只可加多树的高度，字段的诀别度越低，则树的高度越高。顶点的情况，索引字段的值王人一样，那么b+树险些成了一根棍。本例中即是这种顶点的情况，name字段扫数的值均为'nick'
面前咱们得出一个论断：为诀别度低的字段建筑索引，索引树的高度会很高，关联词这具体会带来什么影响呢？？？
淌若条款是name='xxxx'，那么深信是可以第一时候判断出'xxxx'是不在索引树中的（因为树中扫数的值均为'nick’），是以查询速率很快 淌若条款赶巧是name='nick'，查询时，咱们历久无法从树的某个位置得到一个明确的范畴，只可往下找，往下找，往下找。。。这与全表扫描的IO次数莫得多大区别，是以速率很慢3、索引列弗成在条款中参与规划，保抓列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就弗成使用到索引，原因很苟简，b+树中存的王人是数据表中的字段值，但进行检索时，需要把扫数元素王人应用函数才气相比，显著资本太大。是以语句应该写成create_time = unix_timestamp(’2014-05-29’)
4、and/or
and与or的逻辑条款1 and 条款2：扫数条款王人成立才算成立，凡是要有一个条款不成立则最终驱散不成立 条款1 or 条款2：唯有有一个条款成立则最终驱散就成立 4. and的职责旨趣
条款：a = 10 and b = 'xxx' and c > 3 and d =4 索引：制作聚合索引(d，a，b，c) 职责旨趣：关于连气儿多个and：mysql会按照聚合索引，从左到右的规矩找一个诀别度高的索引字段(这么便可以快速锁定很小的范畴)，加速查询，即按照d—>a->b->c的规矩 8. or的职责旨趣
条款：a = 10 or b = 'xxx' or c > 3 or d =4 索引：制作聚合索引(d，a，b，c) 职责旨趣：关于连气儿多个or：mysql会按照条款的规矩，从左到右递次判断，即a->b->c->d
在左边条款成立然而索引字段的诀别度低的情况下（name与gender均属于这种情况），会递次往右找到一个诀别度高的索引字段，加速查询。
经过分析，在条款为name='nick' and gender='male' and id>333 and email='xxx'的情况下，咱们足够没必要为前三个条款的字段加索引，因为只可用上email字段的索引，前三个字段的索引反而会缩短咱们的查询遵守
5、最左前缀匹配原则，颠倒热切的原则，关于组合索引mysql会一直向右匹配直到碰到范畴查询(>、<、between、like)就罢手匹配(指的是范畴大了，有索引速率也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 淌若建筑(a，b，c，d)规矩的索引，d是用不到索引的，淌若建筑(a，b，d，c)的索引则王人可以用到，a，b，d的规矩可以浪漫挽回。
6、其他情况
- 使用函数
select * from tb1 where reverse(email) = 'nick';
- 类型不一致
淌若列是字符串类型，传入条款是必须用引号引起来，否则...
select * from tb1 where email = 999;
#排序条款为索引，则select字段必须亦然索引字段，否则无法掷中
- order by
select name from s1 order by email desc;
当凭据索引排序时候，select查询的字段淌若不是索引，则速率仍然很慢
select email from s1 order by email desc;
颠倒的：淌若对主键排序，则如故速率很快：
select * from tb1 order by nid desc;
- 组合索引最左前缀
淌若组合索引为：(name，email)
name and email -- 掷中索引
name -- 掷中索引
email -- 未掷中索引
- count(1)或count(列)代替count(*)在mysql中莫得离别了
- create index xxxx on tb(title(19)) #text类型，必须制定长度
6.2 其他肃肃事项
幸免使用select *
使用count(*)
创建表时尽量使用 char 代替 varchar
表的字段规矩固定长度的字段优先
组合索引代替多个单列索引（由于mysql中每次只可使用一个索引，是以常常使用多个条款查询时更妥当使用组合索引）
尽量使用短索引
使用连续（JOIN）来代替子查询(Sub-Queries)
连表时肃肃条款类型需一致
索引散列值（重叠少）不妥当建索引，例：性别不妥当
七、聚合索引和掩盖索引
7.1 聚合索引
聚合索引是指对表上的多个列合起来作念一个索引。聚合索引的创建圭表与单个索引的创建圭表一样，不同之处仅在于有多个索引列，如下：
松岛枫avmysql> create table t(
-> a int，
-> b int，
-> primary key(a)，
-> key idx_a_b(a，b)
-> );
Query OK， 0 rows affected (0.11 sec)
那么何时需要使用聚合索引呢？在征询这个问题之前，先来看一下聚合索引里面的驱散。从本体上来说，聚合索引即是一棵B+树，不同的是聚合索引的键值得数目不是1，而是>=2。接着来征询两个整型列构成的聚合索引，假设两个键值得称号分别为a、b如图：
可以看到这与咱们之前看到的单个键的B+树并莫得什么不同，键值王人是排序的，通过叶子结点可以逻辑上规矩地读出所特地据，就上头的例子来说，即（1，1），（1，2），（2，1），（2，4），（3，1），（3，2），数据按（a，b）的规矩进行了存放。
因此，关于查询select * from table where a=xxx and b=xxx， 显著是可以使用(a，b) 这个聚合索引的，关于单个列a的查询select * from table where a=xxx，亦然可以使用（a，b）这个索引的。
但关于b列的查询select * from table where b=xxx，则不可以使用（a，b） 索引，其实你不难发现原因，叶子节点上b的值为1、2、1、4、1、2显著不是排序的，因此关于b列的查询使用不到(a，b) 索引
聚合索引的第二个公正是在第一个键疏通的情况下，还是对第二个键进行了排序处理，举例在很厚情况下应用圭臬王人需要查询某个用户的购物情况，并按照时候进行排序，临了取出最近三次的购买纪录，这时使用聚合索引可以帮咱们幸免多一次的排序操作，因为索引本人在叶子节点还是排序了，如下
# ===========准备表==============
create table buy_log(
userid int unsigned not null，
buy_date date
);
insert into buy_log values
(1，'2009-01-01')，
(2，'2009-01-01')，
(3，'2009-01-01')，
(1，'2009-02-01')，
(3，'2009-02-01')，
(1，'2009-03-01')，
(1，'2009-04-01');
alter table buy_log add key(userid);
alter table buy_log add key(userid，buy_date);
# ===========考证==============
mysql> show create table buy_log;
| buy_log | CREATE TABLE `buy_log` (
`userid` int(10) unsigned NOT NULL，
`buy_date` date DEFAULT NULL，
KEY `userid` (`userid`)，
KEY `userid_2` (`userid`，`buy_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
# 可以看到possible_keys在这里有两个索引可以用，分别是单个索引userid与聚合索引userid_2，然而优化器最终禁受了使用的key是userid因为该索引的叶子节点包含单个键值，是以表面上一个页能存放的纪录应该更多
mysql> explain select * from buy_log where userid=2;
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
| 1 | SIMPLE | buy_log | ref | userid，userid_2 | userid | 4 | const | 1 | |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
row in set (0.00 sec)
# 接着假设要取出userid为1的最近3次的购买纪录，用的即是聚合索引userid_2了，因为在这个索引中，在userid=1的情况下，buy_date王人还是排序好了
mysql> explain select * from buy_log where userid=1 order by buy_date desc limit 3;
+--+-----------+-------+----+---------------+--------+-------+-----+----+------------------------+
|id|select_type|table |type|possible_keys | key |key_len|ref |rows| Extra |
+--+-----------+-------+----+---------------+--------+-------+-----+----+------------------------+
| 1|SIMPLE |buy_log|ref |userid，userid_2|userid_2| 4 |const| 4 |Using where; Using index|
+--+-----------+-------+----+---------------+--------+-------+-----+----+------------------------+
row in set (0.00 sec)
# ps：淌若extra的排序知道是Using filesort，则意味着在查出数据后需要二次排序(如下查询语句，莫得先用where userid=3先定位范畴，于是即便掷中索引也没用，需要二次排序)
mysql> explain select * from buy_log order by buy_date desc limit 3;
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+
|id|select_type| table |type |possible_keys|key |key_len|ref |rows|Extra |
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+
| 1|SIMPLE |buy_log|index| NULL |userid_2| 8 |NULL| 7 |Using index; Using filesort|
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+
# 关于聚合索引（a，b），下述语句可以径直使用该索引，无需二次排序
select ... from table where a=xxx order by b;
# 然后关于聚合索引(a，b，c)来首，下列语句雷同可以径直通过索引得到驱散
select ... from table where a=xxx order by b;
select ... from table where a=xxx and b=xxx order by c;
# 然而关于聚合索引(a，b，c)，下列语句弗成通过索引径直得到驱散，还需要我方蔓延一次filesort操作，因为索引（a，c)并未排序
select ... from table where a=xxx order by c;
7.2 掩盖索引
InnoDB存储引擎撑抓掩盖索引（covering index，或称索引掩盖），即从提拔索引中就可以得到查询纪录，而不需要查询相聚索引中的纪录。
使用掩盖索引的一个公正是：提拔索引不包含整行纪录的扫数信息，故其大小要远小于相聚索引，因此可以减少多半的IO操作。
肃肃：掩盖索引工夫最早是在InnoDB Plugin中完成并达成，这意味着关于InnoDB版块小于1.0的，或者MySQL数据库版块为5.0以下的，InnoDB存储引擎不撑抓掩盖索引特点。
关于InnoDB存储引擎的提拔索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primary key1，priamey key2，...，key1，key2，...）。举例：
select age from s1 where id=123 and name = 'nick'; #id字段有索引，然而name字段莫得索引，该sql掷中了索引，但未掩盖，需要去相聚索引中再查找详确信息。 最给力的情况是，索引字段掩盖了扫数，那全程通过索引来加速查询以及赢得驱散就ok了 mysql> desc s1; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | gender | char(6) | YES | | NULL | | | email | varchar(50) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ rows in set (0.21 sec) mysql> explain select name from s1 where id=1000; #莫得任何索引 +--+-----------+-----+----------+----+-------------+----+-------+----+-------+--------+-----------+ |id|select_type|table|partitions|type|possible_keys|key |key_len|ref | rows |filtered| Extra | +--+-----------+-----+----------+----+-------------+----+-------+----+-------+--------+-----------+ | 1| SIMPLE | s1 | NULL |ALL | NULL |NULL| NULL |NULL|2688336| 10.00 |Using where| +--+-----------+-----+----------+----+-------------+----+-------+----+-------+--------+-----------+ row in set， 1 warning (0.00 sec) mysql> create index idx_id on s1(id); #创建索引 Query OK， 0 rows affected (4.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select name from s1 where id=1000; #掷中提拔索引，然而未掩盖索引，还需要从相聚索引中查找name +--+-----------+-----+----------+----+-------------+------+-------+-----+----+--------+-----+ |id|select_type|table|partitions|type|possible_keys| key|key_len| ref |rows|filtered|Extra| +--+-----------+-----+----------+----+-------------+------+-------+-----+----+--------+-----+ | 1| SIMPLE | s1 | NULL | ref| idx_id |idx_id| 4 |const| 1 | 100.00 | NULL| +--+-----------+-----+----------+----+-------------+------+-------+-----+----+--------+-----+ row in set， 1 warning (0.08 sec) mysql> explain select id from s1 where id=1000; #在提拔索引中就找到了一齐信息，Using index代表掩盖索引 +--+-----------+-----+----------+----+-------------+------+-------+-------+------+----------+-----+ |id|select_type|table|partitions|type|possible_keys| key |key_len| ref |rows|filtered| Extra | +--+-----------+-----+----------+----+--------------------+-------+-------+------+----------+-----+ | 1| SIMPLE | s1 | NULL | ref| idx_id |idx_id| 4 |const| 1 | 100.00 |Using index| +--+-----------+-----+----------+----+-------------+------+-------+-----+----+--------+-----------+ row in set， 1 warning (0.03 sec)
掩盖索引的另外一个公正是对某些统计问题而言的。基于上一小结创建的表buy_log，查询操办如下：
mysql> explain select count(*) from buy_log; +--+-----------+-------+-----+-------------+------+-------+----+----+-----------+ |id|select_type|table | type|possible_keys|key |key_len|ref |rows|Extra | +--+-----------+-------+-----+-------------+------+-------+----+----+-----------+ | 1| SIMPLE |buy_log|index| NULL |userid| 4 |NULL| 7 |Using index| +--+-----------+-------+-----+-------------+------+-------+----+----+-----------+ row in set (0.00 sec)
innodb存储引擎并不会禁受通过查询相聚索引来进行统计。由于buy_log表有提拔索引，而提拔索引远小于相聚索引，禁受提拔索引可以减少IO操作，故优化器的禁受如上key为userid提拔索引
关于（a，b）形式的聚合索引，一般是不可以禁受b中所谓的查询条款。但淌若是统计操作，况兼是掩盖索引，则优化器如故会禁受使用该索引，如下：
# 聚合索引userid_2（userid，buy_date），一般情况，咱们按照buy_date是无法使用该索引的，但特殊情况下：查询语句是统计操作，且是掩盖索引，则按照buy_date手脚念查询条款时，也可以使用该聚合索引 mysql> explain select count(*) from buy_log where buy_date >= '2011-01-01' and buy_date < '2011-02-01'; +--+-----------+-------+-----+-------------+--------+-------+----+----+------------------------+ |id|select_type| table |type |possible_keys| key |key_len|ref |rows|Extra | +--+-----------+-------+-----+-------------+--------+-------+----+----+------------------------+ | 1| SIMPLE |buy_log|index| NULL |userid_2| 8 |NULL| 7 |Using where; Using index| +--+-----------+-------+-----+-------------+--------+-------+----+----+------------------------+ row in set (0.00 sec)7.3 合并索引
mysql> explain select count(email) from index_t where id = 1000000 or email='eva100000@oldboy'; +--+-----------+------+--------------+--------------------------------+---------------+--------+-----+----+-----------------------------------------+ | id | select_type| table | type | possible_keys | key | key_len | ref |rows | Extra | +--+-----------+------+--------------+--------------------------------+---------------+--------+-----+----+-----------------------------------------+ | 1 | SIMPLE | index_t| index_merge | PRIMARY，email，ind_id，ind_email | PRIMARY，email | 4，51 |NULL| 2 |Using union(PRIMARY，email); Using where | +--+-----------+------+--------------+--------------------------------+---------------+--------+-----+----+-----------------------------------------+ row in set (0.01 sec)八、查询优化神器-explain
MySQL性能分析之Explain：https://www.cnblogs.com/nickchen121/p/11155917.html【ONED-976】ギリギリモザイク 6つのコスチュームでパコパコ！ Ami
九、慢查询优化的基本形式 先运行望望是否确切很慢，肃肃设立SQL_NO_CACHE where条款单表查，锁定最小复返纪录表。这句话的真义是把查询语句的where王人应用到表中复返的纪录数最小的表发轫查起，单表每个字段分别查询，看哪个字段的诀别度最高 explain检察蔓延操办，是否与1预期一致（从锁定纪录较少的表发轫查询） order by limit 形式的sql语句让排序的表优先查 了解业务方使用场景 加索引时参照建索引的几大原则 不雅察驱散，不相宜预期持续从0分析 十、慢日记处理慢日记
- 蔓延时候 > 10
- 未掷中索引
- 日记文献旅途
确立：
- 内存
show variables like '%query%';
show variables like '%queries%';
set global 变量名 = 值
- 确立文献
mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
my.conf内容：
slow_query_log = ON
slow_query_log_file = D:/....
肃肃：修改确立文献之后，需要重启奇迹
MySQL日记处理 ======================================================== 失实日记: 纪录 MySQL 奇迹器启动、关闭及运行失实等信息 二进制日记: 又称binlog日记，以二进制文献的方式纪录数据库中除 SELECT 除外的操作 查询日记: 纪录查询的信息 慢查询日记: 纪录蔓延时候杰出指定时候的操作 中继日记： 备库将主库的二进制日记复制到我方的中继日记中，从而在土产货进行重放 通用日记： 审计哪个账号、在哪个时段、作念了哪些事件 事务日记或称redo日记： 纪录Innodb事务关系的如事务蔓延时候、查验点等 ======================================================== 一、bin-log 1. 启用 # vim /etc/my.cnf [mysqld] log-bin[=dir\[filename]] # service mysqld restart 2. 暂停 //仅刻下会话 SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=1; 3. 检察 检察一齐： # mysqlbinlog mysql.000002 定时间： # mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" # mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54" # mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" 按字节数： # mysqlbinlog mysql.000002 --start-position=260 # mysqlbinlog mysql.000002 --stop-position=260 # mysqlbinlog mysql.000002 --start-position=260 --stop-position=930 4. 截断bin-log（产生新的bin-log文献） a. 重启mysql奇迹器 b. # mysql -uroot -p123 -e 'flush logs' 5. 删除bin-log文献 # mysql -uroot -p123 -e 'reset master' 二、查询日记 启用通用查询日记 # vim /etc/my.cnf [mysqld] log[=dir\[filename]] # service mysqld restart 三、慢查询日记 启用慢查询日记 # vim /etc/my.cnf [mysqld] log-slow-queries[=dir\[filename]] long_query_time=n # service mysqld restart MySQL 5.6: slow-query-log=1 slow-query-log-file=slow.log long_query_time=3 单元为秒 检察慢查询日记 测试:BENCHMARK(count，expr) SELECT BENCHMARK(50000000，2*3);
