Mysql索引 （二）
- 引入： 上一篇文章学习之旅8-mysql索引（一）.我们简单的了解的什么是索引，索引执行（磁盘IO）以及索引的简单分类，接下来我们继续了解索引的分类，并通过分析执行计划来具体看看我们执行sql语句时是否使用索引，以及索引是否生效的一些情况。
1、索引进阶
1.1、索引分类
- 上一篇文章我们知道在物理逻辑存储结构上，索引分为MyISAM索引和InnoDB索引（即聚集索引和辅助索引），接下来我们通过应用的层面来分类索引：
1）普通索引：mysql最基本的索引，一个表中可以有多个普通索引，允许空值。（index，key）
2）唯一索引：必须是唯一的列值，可以有多个唯一索引，允许为空值，对于InnoDB存储引擎没有主键的话，唯一键会被作为聚集索引。（unique key）
3）主键索引： 也叫主键，主键是唯一的，自动增长的字段一定是主键，但主键不一定自动增长，一般来说主键的列值意义不大（如序号），主键最好是数值类型，不允许空值，对于InnoDB存储引擎（mysql5.5后默认）会以主键作为聚集索引。（primary key）
4）复合索引：将表中多个列一起作为一个索引字段，此时需要注意的是必须遵守最佳左前缀原则，即列是顺序尤为重要（col1,col2,col3），若 where col2=B and col1=A或者where col2=B 将不走索引
5）前缀索引：当需要作为索引的列字段长度过长，导致索引树高度增加时，我们通常选择大字段前面的部分字符作为索引。char，varchar类型可以指定长度，blob，text类型必须指定，但是数值型不能指定。
6）全文索引：当字段是长文本时，若查询关键字用where col like '%xxx%'时，不满足最佳左前缀原则，索引失效，此时我们就需要建立全文索引（fulltext index）
1.2、索引优化分析
分析执行计划: 优化器(算法)最终得出的,代价最低的,SQL语句的执行方案。用于分析SQL的执行情况。通过explain 执行语句或者desc 执行语句来具体分析。以mysql中自带world数据库中数据为例：
mysql> desc select name,countrycode from 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.03 sec)
mysql> explain select t1.name,countrycode from country t1 join city t2 on t1.code = t2.countrycode where t1.name='china';
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 10.00 | Using where |
| 1 | SIMPLE | t2 | NULL | ref | CountryCode | CountryCode | 3 | world.t1.Code | 18 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
id：表的读取顺序，即select查询的序列号。id越大，优先级越高，越先执行。id相同则自上而下按顺序执行。
select_type：
1）SIMPLE：简单查询，select中不包括子查询或者union语句。
2）PRIMARY：select查询中包含任何复杂的子部分，最外层的查询则为PRIMARY。
3）SUBQUERY ：在select或者where中包含了子查询
4）DERIVED ：在from列表中包含的子查询被标记为DERIVED （衍生），后面对应的id指向对应table，即为该表的衍生表。mysql会递归执行这些子查询，把结果放在临时表中。
5）UNION：若第二个select出现在UNION之后，则被标记为UNION：若UNION包含在from子句的子查询中，外层select将被标记为：DERIVED。
6）UNION RESULT：UNION表获取结果的select。
table ：当前执行对应的表
partitions ：版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。
type：mysql中找到所需行的方式（访问类型），性能：system>const>eq_ref>ref>range>index>all
possible_keys ：可能会使用到的索引（并不一定实际使用），查询的字段若存在索引，则会列出。
key ：实际用到的索引，null则表示没有索引或者索引失效
key_len ：查询用到的索引长度（字节数）。（数值越小说明精度越高）
如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，用多少算多少。
ref ：ref表示使用了那些值进行索引查找，一般为一个常量值或表的某个字段。
rows ：这是mysql估算的需要扫描的行数（不是精确值）。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好
filtered ：这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。
Extra ：包含不适合在其他列中显示但十分重要的额外信息
针对以上explain的参数，并不是所有参数都重点关注的对象，下面我将选取相对重要的几个参数进行详细讲解：
首先我们看一下world数据库下面的city表和country表的结构，都是默认的InnoDB存储引擎。
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
mysql> desc country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | float(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | float(3,1) | YES | | NULL | |
| GNP | float(10,2) | YES | | NULL | |
| GNPOld | float(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
-- 由上可知，city表中有主键ID（聚集索引）,country （普通索引）。country表中有主键Code（聚集索引）
1）type：mysql中找到所需行的方式（访问类型），性能：system>const>eq_ref>ref>range>index>all
- all：全表扫描，条件无索引，需要遍历全表才能找到匹配的行。
mysql> desc select * from city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> desc select * from city where name not like '%CH%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 88.89 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from city where countrycode not in( 'CHN','USA');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | ALL | CountryCode | NULL | NULL | NULL | 4188 | 82.19 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
- index ：全索引扫描，需要遍历索引树（即查询的列有索引），没有where条件的查询。(id 和country分别是主键，普通索引列）
mysql> desc select id from city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | index | NULL | CountryCode | 3 | NULL | 4188 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
mysql> desc select countrycode from city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | index | NULL | CountryCode | 3 | NULL | 4188 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
- range：索引范围查询，通常指模糊查询等，where语句中有> 、< 、>= 、<=、 like、 between and、or、in等。但是这里注意，若where条件后面的列为整型，浮点型时，不能用like做模糊查询，不然此时将全表扫描，不走任何索引，且用like做模糊查询必须满足最佳左前缀原则，不然也不走索引。
-- ID 为int型，不能用like做模糊查询
mysql> desc select * from city where ID in( '100');
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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 sec)
mysql> desc select * from city where ID in( 100,200);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from city where ID =100 or id = 200;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
-- like做模糊查询必须满足最佳左前缀原则，不然也不走索引
mysql> desc select * from country where code like 'US%';
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | country | NULL | range | PRIMARY | PRIMARY | 3 | NULL | 1 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> desc select * from country where code like '%US%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | country | NULL | ALL | NULL | NULL | NULL | NULL | 239 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
但是对于> 、< 、>= 、<=、 like、 between and查询的性能要优于or、in，因为在叶子节点存放的数据是有序排列的，对于B+树来说，每个叶子节点之间是相互连通的，这样就加快了访问速度。而对于or、in 这种连续的查询仅仅相当于B树，需要多次从根节点到叶子节点，查询效率就降低了。对于这种情况我们一般通过union all 来进一步优化查询。
-- type 从范围查询变成了聚集索引等值查询。const > range
mysql> desc select * from city where ID =100 or id = 200;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from city where ID =100 union all select * from city where id = 200;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | PRIMARY | city | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | UNION | city | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)
注意：当对应表中的主键列查询时除外（city表中主键为ID，country表中code为主键），类型为：range
mysql> desc select * from city where ID not in( '1000','200');
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3092 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from country where code in( 'CHN','USA');
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | country | NULL | range | PRIMARY | PRIMARY | 3 | NULL | 2 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- ref：辅助索引等值查询，返回匹配某个单独值的所有行（可能有多个值）
mysql> desc select * from 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：多表连接中，非驱动表（子表）连接条件是主键或者唯一键，对于每个索引键，返回值唯一。
即唯一性索引扫描，
-- 对应city 表中有很多countrycode 与 country表中对应，不只一条数据，此时type类型为ref
mysql> desc select t2.name from city t1 ,country t2 where t1.countrycode=t2.code;
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE | t2 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 100.00 | NULL |
| 1 | SIMPLE | t1 | NULL | ref | CountryCode | CountryCode | 3 | world.t2.Code | 18 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
-- 同理 t2表（country表）虽然只有一个叫china的国家，但是查询出的结果仍然是多行数据，where过滤条件中没有限定唯一的数据行，因此也只能是eef。
mysql> desc select t1.name from city t1 ,country t2 where t1.countrycode=t2.code and t2.name='china';
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE | t2 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 10.00 | Using where |
| 1 | SIMPLE | t1 | NULL | ref | CountryCode | CountryCode | 3 | world.t2.Code | 18 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
-- 而下面这种情况where条件中过滤了city表中叫 chengdu 的城市，只有唯一的一行数据，因此是eq_ref。
mysql> desc select t1.name from city t1 ,country t2 where t1.countrycode=t2.code and t1.name='chengdu';
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | CountryCode | NULL | NULL | NULL | 4188 | 10.00 | Using where |
| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.t1.CountryCode | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
- const：主键或者唯一键等值查询。表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
mysql> desc select * from city where id=2999;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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 sec)
- key_len： 联合索引覆盖长度（一般来说主要看联合索引）。对于联合索引index(a,b,c)，希望对于联合索引应用更加充分。用key_len可以帮助我们查询走了索引的几部分（全覆盖，部分覆盖，不覆盖）
关于长度：只列的储存字节的最大长度（计算最大预留长度 字节byte）,数据的类型以及字符集会影响长度。utf-8来说，一个字符最大占3个字节，而utf-8mb4（支持emoji）一个字符最大占4个字节。另外对于not null 不需要单独的字节，而没有定义（无not null）的字段需要一个字节来单独储存。同理对于varchar类型也需要单独的1-2个字节来储存字节长度这个数，比如varchar(10) ，10这个数字就需要单独字节来储存。
eg：index(a,b,c,d)
全覆盖：
select * from tb1 where a= and b= and c= and d= ;
select * from tb1 where a in and b in and c= and d in ;
select * from tb1 where b= and c= and d= and a= ;
部分覆盖：
select * from tb1 where a= ;
select * from tb1 where a= and c= ;
select * from tb1 where b= and b= order by c;
不覆盖：
select * from b= and c= ;
select * from d= ;
mysql> create table tb1(
-> a int not null, ------->4字节
-> b int, ------->4+1字节
-> c char(10) not null, ------->10*3字节
-> d varchar(10) ------->10*3+2+1字节
-> ) charset=utf8; --------4+5+30+33=72
Query OK, 0 rows affected (0.03 sec)
mysql> create index id_abcd on tb1(a,b,c,d);
mysql> desc select * from tb1 where a=1 and b=1 and c='c' and d='d';
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------------+------+----------+-------------+
| 1 | SIMPLE | tb1 | NULL | ref | id_abcd | id_abcd | 72 | const,const,const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--由上可知，我们在a,b,c,d字段上建立了一个联合索引idx_abcd，当我们查询条件使用到联合索引时（满足最佳最前缀原则）。
--全覆盖：
a=1 and b=1 and c='c' and d='d' 用到4个（4+5+30+33=72）此时索引覆盖长度为72（所有字段都用到）；
--key_len 只指示了WHERE中用于条件过滤时被选中的索引列，是不包含 ORDER BY/GROUP BY 这部分被选中的索引列。
mysql> desc select * from tb1 where a=1 and b=2 order by c;
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+--------------------------+
| 1 | SIMPLE | tb1 | NULL | ref | id_abcd | id_abcd | 9 | const,const | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from tb1 where a=1 and b=2 and c='c' order by d;
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+--------------------------+
| 1 | SIMPLE | tb1 | NULL | ref | id_abcd | id_abcd | 39 | const,const,const | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
--部分覆盖：
a=1 and b=2 用到2个（4+5=9）
a=1 and b=2 and c='c' 用到3个（4+5+30=39）
mysql> desc select * from tb1 where b=2 and c='c' order by d;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
| 1 | SIMPLE | tb1 | NULL | index | NULL | id_abcd | 72 | NULL | 1 | 100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from tb1 where b=2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tb1 | NULL | index | NULL | id_abcd | 72 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
--以上不覆盖：
- Extra：一些额外的比较重要的信息。
Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取（order by ,group by等）。MySQL中无法利用索引。
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | MUL | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
-- 在city表中CountryCode 和 Population上都有单独的普通索引。对于以下查询就会出现Using filesort，因为where条件和order by条件没有联合索引，排序不会按照两者同时进行，因此需要建立联合索引: create index id_cp on city(countrycode,population);
mysql> desc select * from city where countrycode='chn' order by population;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| 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 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
--创建联合索引，Using filesort消失
mysql> create index id_cp on city(countrycode,population);
mysql> desc select * from city where countrycode='chn' order by population;
+----+-------------+-------+------------+------+-------------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | city | NULL | ref | CountryCode,id_cp | id_cp | 3 | const | 363 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------+-------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
1.3、优化案例
- 通过什么语句、软件来进行优化？具体如何优化？
1、mysql出现性能问题，突然变得很慢：
1）首先查询用户连接情况，一般来说都是用户进行查询时出现这种问题，show processlist，获取到导致数据库卡住的语句。
2）获取到该语句后，用explain或desc对该语句进行分析，查看索引情况。
3）进一步建立或者优化索引。
2、一段时间很慢（持续性）
1）查看关于慢日志情况（slowlog）,分析slowlog
2）用explain或desc进行分析，查看索引情况。
3）进一步建立或者优化索引。
1.4、索引应用规范
-
索引建立的原则：
1）一般来说对于建表时应该设置主键，一般来说是无关的列（自增）；
2）经常作为where条件的列，order by ,group by ,join on ,distinct 的条件
3）最好使用唯一值对的列作为联合索引的前导列，其他按照联合索引优化细节来处理；
4）对于列值过长的，建议建立前缀索引，指定长度；
5）降低索引数量，对于不常用的列不要建立索引，通过删除不需要的索引来进行优化处理（percona toolkit）或者sqlyog工具中的信息查看索引情况优化；
6）维护索引比例业务繁忙期；
7）小表一般不建立索引 -
不走索引情况：
1）没有查询条件（select * from t1）或者查询条件没有建立索引；
2）查询结果集是原表中的大部分数据，应该是25%以上（数据量过大也有可能不会走索引）；
3）索引本身失效，统计数据不真实；
同一个语句原来走索引，但是某时间突然很慢–可能是统计信息过旧导致索引失效
4）查询条件使用函数在索引列上，或者对索引列进行运算操作（+,-,* ,/,！等）
5）对于隐式转换导致索引失效：比例定义的字段tel varchar(11)，我们在查询时可以用where =‘110’ 或者 where = 110 ，虽然查询结果一样，但是对于查询的性能却不一样，因为我们定义的tel 为字符类型，我们在该字段建立索引后，若查询条件where = 110，此时mysql会自动将110 转换 ‘110’，此时我们对索引项做了函数处理，故索引失效。
where = ‘110’ 走ref ， 而where = 110 则为 all；
6）not in 、!= 、<> 不走索引 or、in尽量转换为union
7）满足最佳左前缀原则’xx%’，而’%xx%'不走索引。
参考资料： MySQL 5.7参考手册
扫一扫
417
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
