一、MySQL索引根本
首先，我们将从索引根本开始先容一下什么是索引，剖析索引的几种类型，并磋商一下如何创建索引以及索引设计的基本原则。
此部分用于测试索引创建的user表的构造如下：
1. 什么是索引？
“索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据构造。”
——《高性能MySQL》
我们须要知道索引实在是一种数据构造，其功能是帮助我们快速匹配查找到须要的数据行，是数据库性能优化最常用的工具之一。其浸染相称于超市里的导购员、书本里的目录。
2. 索引类型
可以利用SHOW INDEX FROM table_name;查看索引详情：
主键索引 PRIMARY KEY：它是一种分外的唯一索引，不许可有空值。一样平常是在建表的时候同时创建主键索引。把稳：一个表只能有一个主键。
唯一索引 UNIQUE：唯一索引列的值必须唯一，但许可有空值。如果是组合索引，则列值的组合必须唯一。可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引：
可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引：
普通索引 INDEX：这是最基本的索引，它没有任何限定。可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引：
组合索引 INDEX：即一个索引包含多个列，多用于避免回表查询。可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引：
全文索引 FULLTEXT：也称全文检索，是目前搜索引擎利用的一种关键技能。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引：
索引一经创建不能修正，如果要修正索引，只能删除重修。可以利用
DROP INDEX index_name ON table_name;删除索引。
3、索引设计的原则
1）适宜索引的列是涌如今where子句中的列，或者连接子句中指定的列；
2）基数较小的类，索引效果较差，没有必要在此列建立索引；
3）利用短索引，如果对长字符串列进行索引，该当指定一个前缀长度，这样能够节省大量索引空间；
4）不要过度索引。索引须要额外的磁盘空间，并降落写操作的性能。在修正表内容的时候，索引会进行更新乃至重构，索引列越多，这个韶光就会越长。以是只保持须要的索引有利于查询即可。
二、MySQL索引优化实战
上面我们先容了索引的基本内容，这部分我们先容索引优化实战。在先容索引优化实战之前，首先要先容两个与索引干系的主要观点，这两个观点对付索引优化至关主要。
此部分用于测试的user表构造：
1、索引干系的主要观点
基数：单个列唯一键（distict_keys）的数量叫做基数。
SELECT COUNT(DISTINCT name),COUNT(DISTINCT gender) FROM user;
user表的总行数是5，gender列的基数是2，解释gender列里面有大量重复值，name列的基数即是总行数，解释name列没有重复值，相称于主键。
返回数据的比例：user表中共有5条数据：
SELECT FROM user;
查询知足性别为0（男）的记录数：
那么返回记录的比例数是：
同理，查询name为'swj'的记录数：
返回记录的比例数是：
现在问题来了，假设name、gender列都有索引，那么SELECT FROM user WHERE gender = 0; SELECT FROM user WHERE name = 'swj';都能命中索引吗？
user表的索引详情：
SELECT FROM user WHERE gender = 0;没有命中索引，把稳filtered的值便是上面我们打算的返回记录的比例数。
SELECT FROM user WHERE name = 'swj';命中了索引index_name，由于走索引直接就能找到要查询的记录，以是filtered的值为100。
因此，返回表中30%内的数据会走索引，返回超过30%数据就利用全表扫描。当然这个结论太绝对了，也并不是绝对的30%，只是一个大概的范围。
回表：当对一个列创建索引之后，索引会包含该列的键值及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描，该当直接走全表扫描。
EXPLAIN命令结果中的Using Index意味着不会回表，通过索引就可以得到紧张的数据。Using Where则意味着须要回表取数据。
2. 索引优化实战
有些时候虽然数据库有索引，但是并不被优化器选择利用。我们可以通过SHOW STATUS LIKE 'Handler_read%';查看索引的利用情形：
Handler_read_key：如果索引正在事情，Handler_read_key的值将很高。
Handler_read_rnd_next：数据文件中读取下一行的要求数，如果正在进行大量的表扫描，值将较高，则解释索引利用不理想。
索引优化规则：
1）如果MySQL估计利用索引比全表扫描还慢，则不会利用索引。
返回数据的比例是主要的指标，比例越低越随意马虎命中索引。记住这个范围值——30%，后面所讲的内容都是建立在返回数据的比例在30%以内的根本上。
2）前导模糊查询不能命中索引。
name列创建普通索引：
前导模糊查询不能命中索引：
EXPLAIN SELECT FROM user WHERE name LIKE '%s%';
非前导模糊查询则可以利用索引，可优化为利用非前导模糊查询：
EXPLAIN SELECT FROM user WHERE name LIKE 's%';
3）数据类型涌现隐式转换的时候不会命中索引，特殊是当列类型是字符串，一定要将字符常量值用引号引起来。
EXPLAIN SELECT FROM user WHERE name=1;
EXPLAIN SELECT FROM user WHERE name='1';
4）复合索引的情形下，查询条件不包含索引列最左边部分（不知足最左原则），不会命中符合索引。
name,age,status列创建复合索引：
ALTER TABLE user ADD INDEX index_name (name,age,status);
user表索引详情：
SHOW INDEX FROM user;
根据最左原则，可以命中复合索引index_name：
EXPLAIN SELECT FROM user WHERE name='swj' AND status=1;
把稳，最左原则并不是说是查询条件的顺序：
EXPLAIN SELECT FROM user WHERE status=1 AND name='swj';
而是查询条件中是否包含索引最左列字段：
EXPLAIN SELECT FROM user WHERE status=2 ;
5）union、in、or都能够命中索引，建议利用in。
union:
EXPLAIN SELECTFROM user WHERE status=1
UNION ALL
SELECTFROM user WHERE status = 2;
in:
EXPLAIN SELECT FROM user WHERE status IN (1,2);
or:
EXPLAIN SELECTFROM user WHERE status=1OR status=2;
查询的CPU花费：or>in>union。
6）用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。
EXPLAIN SELECT FROM payment WHERE customer_id = 203 OR amount = 3.96;
由于or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情形下，就没有必要多一次索引扫描增加IO访问。
7）负向条件查询不能利用索引，可以优化为in查询。
负向条件有：!=、<>、not in、not exists、not like等。
status列创建索引：
ALTER TABLE user ADD INDEX index_status (status);
user表索引详情：
SHOW INDEX FROM user;
负向条件不能命中缓存：
EXPLAIN SELECT FROM user WHERE status !=1 AND status != 2;
可以优化为in查询，但是条件是区分度要高，返回数据的比例在30%以内：
EXPLAIN SELECT FROM user WHERE status IN (0,3,4);
8）范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等。
status,age列分别创建索引：
ALTER TABLE user ADD INDEX index_status (status);
ALTER TABLE user ADD INDEX index_age (age);
user表索引详情：
SHOW INDEX FROM user;
范围条件查询可以命中索引：
EXPLAIN SELECT FROM user WHERE status>5;
范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引：
EXPLAIN SELECT FROM user WHERE status>5 AND age<24;
如果是范围查询和等值查询同时存在，优先匹配等值查询列的索引：
EXPLAIN SELECT FROM user WHERE status>5 AND age=24;
8）数据库实行打算不会命中索引。
EXPLAIN SELECT FROM user WHERE age>24;
EXPLAIN SELECT FROM user WHERE age+1>24;
打算逻辑该当只管即便放到业务层处理，节省数据库的CPU的同时最大限度的命中索引。
9）利用覆盖索引进行查询，避免回表。
被查询的列，数据能从索引中取得，而不用通过行定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速率。
user表的索引详情：
由于status字段是索引列，以是直接从索引中就可以获取值，不必回表查询：
Using Index代表从索引中查询：
EXPLAIN SELECT status FROM user where status=1;
当查询其他列时，就须要回表查询，这也是为什么要避免SELECT的缘故原由之一：
EXPLAIN SELECT FROM user where status=1;
10）建立索引的列，不许可为null。
单列索引不存null值，复合索引不存全为null的值，如果列许可为null，可能会得到“不符合预期”的结果集，以是，请利用not null约束以及默认值。
remark列建立索引：
ALTER TABLE user ADD INDEX index_remark (remark);
IS NULL可以命中索引：
EXPLAIN SELECT FROM user WHERE remark IS NULL;
IS NOT NULL不能命中索引：
EXPLAIN SELECT FROM user WHERE remark IS NOT NULL;
虽然IS NULL可以命中索引，但是NULL本身就不是一种好的数据库设计，该当利用NOT NULL约束以及默认值。
a. 更新十分频繁的字段上不宜建立索引：由于更新操作会变更B+树，重修索引。这个过程是十分花费数据库性能的。
b. 区分度不大的字段上不宜建立索引：类似于性别这种区分度不大的字段，建立索引的意义不大。由于不能有效过滤数据，性能和全表扫描相称。其余返回数据的比例在30%以外的情形下，优化器不会选择利用索引。
c. 业务上具有唯一特性的字段，纵然是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响insert速率，但是对付查询的速率提升是非常明显的。其余，纵然在运用层做了非常完善的校验掌握，只要没有唯一索引，在并发的情形下，依然有脏数据产生。
d. 多表关联时，要担保关联字段上一定有索引。
e. 创建索引时避免以下缺点不雅观念：索引越多越好，认为一个查询就须要建一个索引；宁缺勿滥，认为索引会花费空间、严重拖慢更新和新增速率；抵制唯一索引，认为业务的唯一性一律须要在运用层通过“先查后插”办法办理；过早优化，在不理解系统的情形下就开始优化。
3. 小结
对付自己编写的SQL查询语句，要只管即便利用EXPLAIN命令剖析一下，做一个对SQL性能有追求的程序员。衡量一个程序员是否靠谱，SQL能力是一个主要的指标。作为后端程序员，深以为然。
