1. 索引的作用
优化查询，类似于书的目录
2. 索引算法分类
Btree
Rtree
Hash
fulltext
gis
3. 聚集索引和辅助索引的逻辑构成
最核心的区别就在叶子节点上，辅助索引是把单列的值存放在叶子节点上，聚集索引是把整行的值存放在叶子节点上。
4. 辅助索引细分
单列
多列（联合索引）
唯一
5. 索引树的高度会受什么影响？
1. 原因：数据行。
解决方法：分表，如果是数据量大，就分区
如果是数据行多，就分表。
2.原因：索索引列值较长。
解决方法：前缀索引。
3. 数据类型（char、varchar、enum）
6. 索引的管理操作
创建索引(普通辅助索引)
alter table t100w add index idx_k2(XYno); 在t100w表中的XYno列上创建一个名为idx_k2的索引。
上述命令会造成短暂的锁表，尽量在在业务不忙的时候做
查看索引：（MUL 普通辅助索引、UNI 唯一索引、PRI 聚集索引）
desc table_name
show index from table_name;
show index from table_name\G
添加唯一索引
特性：列的值不能重复。
查看想设置唯一索引的列是否有重复值
（1）3306 [oldboy]>select count(distinct (k1)) from t100w;
+----------------------+
| count(distinct (k1)) |
+----------------------+
| 1225 | #1225表示的为不重复的值
+----------------------+
1 row in set (0.48 sec)
（2）3306 [oldboy]> select k1,count(k1) from t100w group by k1 having count(k1)>1;
#如果显示出来的值（count(k1)）不为1，那就表示它不是唯一的，就不能创建唯一索引。
+------+-----------+
| k1 | count(k1) |
+------+-----------+
| 00 | 266 |
| 01 | 250 |
| 02 | 272 |
……省略部分内容
3306 [oldboy]>alter table t100w add unique index idx_k1(k1);
ERROR 1062 (23000): Duplicate entry 'BX' for key 'idx_k1' #因为K1列有重复值，所以创建唯一索引时报错
添加前缀索引：
注意：前缀索引只能应用到字符串上的列
alter table city add index idx_name(name(5));
#选择name这一列从左到右的前5个字符作为前缀索引
创建联合索引：
联合索引是在多个列上创建的
alter table city add index idex_co_po(countrycode,population);
删除索引：
show index from city;
alter table city drop index idx idex_co_po;
7. 执行计划
7.0 为什么要有执行计划
（1）到底该怎么建立索引
（2）到底怎么去分析用户的行为
（3）如何才能知道索引的设定是否合理
（4）如何知道SQL语句到底走没走索引
以上这些原因，就是要有执行计划的原因，有了执行计划后，就能对执行计划进行分析。
这里的执行计划，正是优化器选择后的执行计划
7.1 作用
上线新的查询语句之前，进行提前预估语句的性能。
在出现性能问题时，找到合理的解决思路。
7.2 执行计划的获取
执行计划的获取,一般是针对select语句进行的
应用场景为：
（1）上线一个新的查询业务之前
（2）出现了性能问题之后
模拟场景：上线一个新的查询业务之前
评估语句为：select * from oldboy.t100w where k2='EF12'
查询方法：
3306 [oldboy]>desc select * from oldboy.t100w where k2='EF12'\G
*************************** 1. row ***************************
id: 1 （语句的序号，不重要）
select_type: SIMPLE （查询类型：普通的。不用特别关注）
table: t100w （表名。重要，表示了执行计划是针对t100w这张表进行的。企业应用场景：多表查询时，可以通过此项来评估，到底是哪张表出了问题）
partitions: NULL
type: ref （重要。索引的类型：）
possible_keys: idx_k2 （重要。可能会使用到的索引：idx_k2）
key: idx_k2（重要。 实际上使用的索引：idx_k2）
key_len: 5 （重要。联合索引的覆盖长度。越高越好 ）
ref: const
rows: 573 （重要。查询的行数。这个值越少越好，行数越少，代价越低）
filtered: 100.00
Extra: NULL （重要。额外的信息）
1 row in set, 1 warning (0.00 sec)
3306 [oldboy]>explain select * from oldboy.t100w where k2='EF12'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t100w
partitions: NULL
type: ref
possible_keys: idx_k2
key: idx_k2
key_len: 5
ref: const
rows: 573
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
7.3 执行计划的分析
type：ref 索引的应用级别
可能的应用级别包括以下几类：
（1）ALL
（2）Index
（3）range
（4）ref
（5）eq_ref
（6）const或system（这两种级别相同）
（7）NULL
上述应用级别是从上到下越来越好。
应用级别详解
ALL：全表扫描，不走索引。（在MySQL的查询中，要么是全表扫描，要么是索引扫描。这种级别在索引优化中应当避免出现）
ALL会出现在两种情况中：
（1）没有索引。
（2）有索引也不走
什么情况下会导致不走索引呢？
（1）没建立索引
（2）建立索后不走索引的。
模拟环境：建立索后不走索引的几种情况
（1）desc select * from t100w; 因为直接查找全表的数据，所以不走索引
（2）desc select * from t100w where k1='aa'; 这里是没有以索引列作为查询条件，所以即使有索引，它也不会走
（3）desc select * from t100w where k2 !='aaaa'; 这里的K2是索引列，但是由于查找的条件排除了k2这一例，所以不走索引
（4）desc select * from t100w where k2 like '%xt%'; 查询语句中应当避免出现双%的情况，因为这种它也是不会走索引的
Index：全索引扫描（扫描整个索引树，这种级别在索引优化中应当避免出现）,查询的值为整个索引列的所有值
出现场景：
（1）desc select k2 from t100w; 这里的K2为辅助索引列
range：索引范围扫描（不扫描整个索引树，而只是扫描一部分索引。在索引优化中出现的级别至少应该是“range”）
出现场景：
辅助索引语句中包含：>、<、>=、<=、like、in、or
in、or（这两种要尽量避免或改写）
主键列：!=
模拟场景：
3306 [oldboy]> desc select * from world.city where id>3000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1079 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
3306 [oldboy]>desc select * from world.city where id!=3000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3173 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.10 sec)
3306 [oldboy]>desc select * from world.city where countrycode like 'C%';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | city | NULL | range | CountryCode | CountryCode | 3 | NULL | 551 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
3306 [oldboy]>desc select * from world.city where countrycode in ('CHN','USA');
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | city | NULL | range | CountryCode | CountryCode | 3 | NULL | 637 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
改写in的方法：
3306 [oldboy]>desc select * from world.city where countrycode ='CHN' union all select * from world.city where countrycode ='USA';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | PRIMARY | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | NULL |
| 2 | UNION | city | NULL | ref | CountryCode | CountryCode | 3 | const | 274 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
ref：辅助索引等值扫描。在同样的数据量级下，ref比range性能要高很多
**出现场景：**
3306 [oldboy]>3306 [oldboy]>desc select * from world.city where countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
eq_ref：在多表连接查询时，on的条件列时是唯一索引或主键列。
出现场景：
desc select a.name,b.name,b.surfacearea
from city as a join country as b
on a.countrycode=b.code （on的条件列）
where a.population <100;
const或system（这两种级别相同）：主键或唯一键等值查询，代价（IO）最低。
出现场景：
3306 [oldboy]> desc select * from world.city where id=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00
NULL：空
出现场景：
几乎不可能，除非要查找的数据不存在。
Extra：：额外的信息
using filesort：当Extra这一栏下出现using filesort时，表示索引设计不合理或语句写错，排序语句最容易出现filesort
注意：
当查询条件出同时出现了where和order by时，必须要加上联合索引，避免出现using filesort
7.4 explain(desc)使用场景（面试题）
题目意思: 我们公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
（1）应急性的慢：突然夯住，突然卡住，增删改查都无法操作，或反应速度很慢。
处理过程:
1.show processlist(显示用户正在运行的线程); 获取到导致数据库hang的语句
- 使用explain或desc 分析SQL的执行计划,有没有走索引,索引的类型情况
- 建索引,改语句
（2）一段时间慢(持续性的):
- 查看记录慢日志的slowlog,分析slowlog
- explain 分析SQL的执行计划,有没有走索引,索引的类型情况
- 建索引,改语句
8. 索引应用规范
“业务”，根据业务，建立合理的索引
1.产品的功能
2.用户的行为
"热"查询语句 --->较慢--->slowlog
"热"数据
8.1 建立索引的原则（DBA运维规范）
说明
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
那么索引设计原则又是怎样的?
（1） (必须的) 建表时一定要有主键,一般是个无关列
（2）选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。
重复值查询优化方案:
(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;
（3）必须要为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段，建立索引。因为排序操作会浪费很多时间。
当一条语句中同时存在where A、GROUP BY B、ORDER BY C怎么办？
1. 联合索引
2. 必须按照子句的顺序建立联合索引(A,B,C) ，否则会出现漏走索引的情况。
3. 在where后面的条件，如果有group by或order by，尽量where条件不要出现不等值查询。
注：如果经常作为条件的列，重复值特别多，可以建立联合索引。
（4）当查询的字段值很长，可以使用前缀索引来减小索引树的高度
如果索引字段的值很长，最好使用值的前缀来索引。
因为，当查询的字段值很长，数据量级又特别大的情况下，如果直接拿这个列来建立索引，会增加索引树的高度，会增加查询的代价。
（5）限制索引的数目
在MySQL中，一个表最多可以有64个索引，但是索引的数目并不是越多越好。
索引太多可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。索引太多，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用
建立索引建议：
（1）表中条目小于10W行，不用建立索引，可以直接进行全表扫描。
（2）10W行以上，根据业务的特点，合理建立索引。
（3）需要经常更新的列，不建议建立索引。
（6）删除不再使用或者很少使用的索引(percona toolkit)
pt-duplicate-key-checker（检查数据库中重复的索引）
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
（7）大表加索引,要在业务不繁忙期间操作
（8）尽量少在经常更新值的列上建索引
总结：建索引原则
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引要清理掉,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
关于联合索引
（1）当一条语句中既有where和group by，又有order by时候，一定要建立联合索引，而且查询的值必须是这样(A,B,C)的顺序。
（2）只有where时，怎么办？
1. 假设都是等值 ,在5.5 以后无关索引顺序，把控一个原则：唯一值多的列（重复值少），放在联合索引的最左侧
2. 如果有不等值，例如以下情况
select where A= and B> and C=xxx
建立索引顺序：ACB （等值中，唯一值最多的放在最前面，不等值放在最后面）
语句改写为 ：ACB（等值中，唯一值最多的放在最前面，不等值放在最后面）
8.2 不走索引的情况有哪些（开发规范）
1. 没有查询条件，或者查询条件没有建立索引
select * from tab; 全表扫描不走索引。
在业务数据库中，特别是数据量比较大的表。
是没有全表扫描这种需求。
1、对用户查看是非常痛苦的。
2、对服务器来讲毁灭性的。
（1）
select * from tab;
SQL改写成以下语句：
select * from tab order by price limit 10 ; 需要在price列上建立索引。limit 10 取前10行。
（2）
select * from tab where name='zhangsan' name列没有索引
改：
1、换成有索引的列作为查询条件
2、将name列建立索引
2. 查询结果集是原表中的大部分数据(25％以上)。
当查询的结果集，超过了总数行数25%，优化器会觉得就没有必要走索引了，自动转换为全表扫描。
假如：tab表 id，name id:1-100w ，id列有(辅助)索引
select * from tab where id>500000;
如果业务允许，可以使用limit控制。
怎么改写 ？
结合业务判断，有没有更好的方式。如果没有更好的改写方案，就尽量不要在mysql存放这个数据了。放到redis里面。
3. 索引本身失效，统计数据不真实
索引本身其实是有自我维护能力的 。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
解决办法一般是删除重建
现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select? --->索引失效,，统计数据不真实
DML ? --->锁冲突，耗尽了所有资源，导致什么也干不了。
4. 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询
5. 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
这样会导致索引失效.
错误例子演示：
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| telnum | varchar(20) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql>
总结：上面的telnum定义的为字符串类型，但是where查询的时候，查询的是数字，所以不走索引。加上单引号就走索引了。
6. <> ，not in 出现在辅助索引列的时候，不走索引
EXPLAIN SELECT * FROM teltab WHERE telnum <> '110';
EXPLAIN SELECT * FROM teltab WHERE telnum NOT IN ('110','119');
mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum <> '1555555';
7. 单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in 尽量改成union all
EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');
改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'
8. like "%_" 百分号在最前面不走
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引
%linux%类的搜索需求，可以使用elasticsearch或 mongodb 专门做搜索服务的数据库产品
总结：like 专门针对字符串列，数字列不能使用
网友评论