2019-索引

1 Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。

1. 什么是索引？

“索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据结构。”
——《高性能MySQL》
我们需要知道索引其实是一种数据结构，其功能是帮助我们快速匹配查找到需要的数据行，是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。

2. 索引类型

可以使用SHOW INDEX FROM table_name;查看索引详情：

1 主键索引 PRIMARY KEY：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。注意：一个表只能有一个主键。

2 唯一索引 UNIQUE：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引：

可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引：

3 普通索引 INDEX：这是最基本的索引，它没有任何限制。可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引：

4 组合索引 INDEX：即一个索引包含多个列，多用于避免回表查询。可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引：

5 全文索引 FULLTEXT：也称全文检索，是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引：

索引一经创建不能修改，如果要修改索引，只能删除重建。可以使用
DROP INDEX index_name ON table_name;删除索引。!!!

3、索引设计的原则

1 适合索引的列是出现在where子句中的列，或者连接子句中指定的列；

2 基数较小的列，索引效果较差，没有必要在此列建立索引；--------------------一个表的基数就是表中的行数或是记录数。对于索引而言，基数被认为是索引中惟一值的数量。惟一索引的基数等于表中的行数。但是，非惟一索引的基数可以是从 1 到表中行数的任意数字，具体取决于每个索引键在表中出现的次数。
低基数索引就是那些惟一值相对较少的索引。account enabled 或 published 等列很可能只有两个惟一值（yes 和 no）。此类列上的索引的基数为 2。许多表的列可以包含一个很小的惟一值子集，这些列包括 Status、Color 和 Currency 等，具体情况取决于所存储的数据。

基数：单个列唯一键（distict_keys）的数量叫做基数。比如性别男和女，基数是2不建议索引，-----user表的总行数是5，gender列的基数是2，说明gender列里面有大量重复值，name列的基数等于总行数，说明name列没有重复值，相当于主键。

3 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间；

4 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

二、MySQL索引优化实战

此部分用于测试的user表结构：

1、索引相关的重要概念

基数：单个列唯一键（distict_keys）的数量叫做基数。
SELECT COUNT(DISTINCT name),COUNT(DISTINCT gender) FROM user;

2. 索引优化实战

有些时候虽然数据库有索引，但是并不被优化器选择使用。我们可以通过SHOW STATUS LIKE ‘Handler_read%’;查看索引的使用情况：

索引优化规则：

1 如果MySQL估计使用索引比全表扫描还慢，则不会使用索引。
返回数据的比例是重要的指标，比例越低越容易命中索引。记住这个范围值——30%，后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。

2 前导模糊查询不能命中索引。

前导模糊查询不能命中索引：
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 SELECT*FROM 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. 创建索引时避免以下错误观念：索引越多越好，认为一个查询就需要建一个索引；宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度；抵制唯一索引，认为业务的唯一性一律需要在应用层通过“先查后插”方式解决；过早优化，在不了解系统的情况下就开始优化。

小结

对于自己编写的SQL查询语句，要尽量使用EXPLAIN命令分析一下，做一个对SQL性能有追求的程序员。衡量一个程序员是否靠谱，SQL能力是一个重要的指标。作为后端程序员，深以为然。

10条sql技巧

1 负向条件查询不能使用索引

select * from order where status!=0 and stauts!=1

not in/not exists都不是好习惯

可以优化为in查询：

select * from order where status in(2,3)

2 前导模糊查询不能使用索引

select * from order where desc like ‘%XX’

而非前导模糊查询则可以：

select * from order where desc like ‘XX%’

3 数据区分度不大的字段不宜使用索引

select * from user where sex=1

原因：性别只有男，女，每次过滤掉的数据很少，不宜使用索引。

经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引。

4 在属性上进行计算不能命中索引

select * from order where YEAR(date) < = ‘2017’

即使date上建立了索引，也会全表扫描，可优化为值计算：

select * from order where date < = CURDATE()

或者：

select * from order where date < = ‘2017-01-01’

二、并非周知的SQL实践

5 如果业务大部分是单条查询，使用Hash索引性能更好，例如用户中心

select * from user where uid=?

select * from user where login_name=?

原因：

B-Tree索引的时间复杂度是O(log(n))

Hash索引的时间复杂度是O(1)

6 允许为null的列，查询有潜在大坑

单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集
select * from user where name != ‘shenjian’
如果name允许为null，索引不存储null值，结果集中不会包含这些记录。
所以，请使用not null约束以及默认值。

7 复合索引最左前缀，并不是值SQL语句的where顺序要和复合索引一致

用户中心建立了(login_name, passwd)的复合索引

select * from user where login_name=? and passwd=?

select * from user where passwd=? and login_name=?

都能够命中索引

select * from user where login_name=?

也能命中索引，满足复合索引最左前缀

select * from user where passwd=?
不能命中索引，不满足复合索引最左前缀

8 使用ENUM而不是字符串

ENUM保存的是TINYINT，别在枚举中搞一些“中国”“北京”“技术部”这样的字符串，字符串空间又大，效率又低。

三、小众但有用的SQL实践

9 如果明确知道只有一条结果返回，limit 1能够提高效率

select * from user where login_name=?

可以优化为：

select * from user where login_name=? limit 1

原因：
你知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动

10 把计算放到业务层而不是数据库层，除了节省数据的CPU，还有意想不到的查询缓存优化效果

select * from order where date < = CURDATE()

这不是一个好的SQL实践，应该优化为：

$curDate = date(‘Y-m-d’);

$res = mysql_query(

'select * from order where date < = $curDate');

原因：

释放了数据库的CPU

多次调用，传入的SQL相同，才可以利用查询缓存

11 强制类型转换会全表扫描

select * from user where phone=13800001234

你以为会命中phone索引么？大错特错了，这个语句究竟要怎么改？加引号！

末了，再加一条，不要使用select *（潜台词，文章的SQL都不合格 =_=），只返回需要的列，能够大大的节省数据传输量，与数据库的内存使用量哟。
VO避免过多的join

12 不同字符集，导致全表扫描
两个表的字段名相同，字符集不同。left join的时候导致全表扫描

总结

1 数据区分度不大的字段不宜使用索引 ，经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引

2 在属性上进行计算不能命中索引

3 使用ENUM而不是字符串，别在枚举中搞一些“中国”“北京”“技术部”这样的字符串

4 如果明确知道只有一条结果返回，limit 1能够提高效率

5 把计算放到业务层而不是数据库层，除了节省数据的CPU，还有意想不到的查询缓存优化效果