MySQL索引及执行计划
索引
在mysql中称之为键, 一种数据结果, 帮助减少SQL语句经历的IO次数
一. Mysql 查找数据的两种方式
- 全表遍历扫描
- 通过索引查找算法进行遍历扫描
二. 索引作用
提供了类似书中目录的作用, 目的是为了优化查询
三. 索引种类
根据不同的算法进行划分
- B树索引
- Hash索引
- R树
- Full text 全文索引
- GIS
hash索引和BTree索引
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
B树索引分类
- B-TREE
- B+TREE, 默认工作模式(算法)
- B*TREE
B+树的工作原理
b+树的查找过程
如图所示，如果要查找数据项29，那么首先会把磁盘块1(根节点)由磁盘加载到内存，此时发生一次IO，确定29是大于5和大于28这两个数据像，最后肯定是选择与29这个数更接近的28这个磁盘像并锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29大于28，所以只能选择28这个磁盘像并锁定磁盘块3的P1指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高
b+树性质
1.索引字段要尽量的少：如果只需要检索一个id, 正常的只会进行3次IO, 而如果检索多个,则肯定是需要超过3次IO的
2.索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
MySQL Btree 种类细分
辅助索引(二级索引)
人为操作最多的, 创建在表中的列上
**创建过程: **
-
创建索引时, 选择表中的某个列作为索引键(key)
-
会将整个列的值提取出来, 做排序
-
将排序后的值,军训的分布到BTree索引的"叶子节点"中, 进而生成"枝节点", 最终生成"根节点"
-
"叶子节点"同时会存储原表的数据行的指针, 进而找到行中的其他列的数据, 这个动作叫回表查询(随机Io)
随机Io: 对磁盘上进行遍历
覆盖索引(联合索引)
分析业务, 将大部分的数据查询的列, 联合起来建立B树索引, 可以大量减少回表查询, 从而减少随机IO
唯一索引
列的值必须是不重复的
聚集索引(主键索引, 建表时创建)
生成条件:
- 会选择主键列作为聚集索引列(一般主键是在建表时加入)
- 没有主键, 会选择唯一键作为聚集索引列
结构:
- 按照聚集索引列的值的顺序存储数据项形成叶子节点
- 枝节点和根节点只存储下层的最小值和指针
索引的高度
索引其实也是表, 也占磁盘空间
-
数据行数越多, 高度越高
优化方案
- 表分区(一般以800w行作为标准, 比较早期的解决方案)
- 分布式架构(MyCat, TDDL, DBLE,DRDS)
-
索引列的值如果很长的时候, 高度越高
优化方案
-
前缀索引
-
使用varchar()数据类型
变长长度列, 使用char()类型, 如果索引数据的长度小于char()数据类型设置的长度, 则剩下的就会使用空格填充, 这样在计算索引长度的时候会把索引数据本身长度和填充的空格也会算进去, 所以使用varchar()
-
无索引和有索引的区别
无索引： 从前往后一条一条查询
有索引：创建索引的本质，就是创建额外的文件（某种格式存储，查询的时候，先去格外的文件找，定好位置，然后再去原始表中直接查询。但是创建索引越多，会对硬盘也是有损耗
建立索引的目的
- 保存特殊的数据结构
- 查询快，但是插入更新删除依然慢
- 创建索引之后，必须命中索引才能有效
四. 索引案例
据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
使用索引，我们必须知道：
- 创建索引
- 命中索引
- 正确使用索引
案例
准备300W条数据：
1. 准备数据库并切换
MariaDB [(none)]> create database s18 charset='utf8';
MariaDB [(none)]> use s18;
MariaDB [s18]>
2. 准备表
MariaDB [s18]> use s18;create table userinfo(
Database changed
-> id int,
-> name varchar(20),
-> gender char(6),
-> email varchar(50)
-> )
-> ;
MariaDB [s18]> desc userinfo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | char(6) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2. 创建存储过程，实现批量插入记录
MariaDB [s18]> delimiter $$
MariaDB [s18]> create procedure auto_insert1()
-> BEGIN
-> declare i int default 1;
-> while(i<3000000)do
-> insert into userinfo values(i,concat('alex',i),'male',concat('egon',i,'@oldboy'));
-> set i=i+1;
-> end while;
-> END$$
MariaDB [s18]> delimiter ;
3. 查看存储过程
MariaDB [s18]> show create procedure auto_insert1\G
*************************** 1. row ***************************
Procedure: auto_insert1
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `auto_insert1`()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into userinfo values(i,concat('alex',i),'male',concat('egon',i,'@oldboy'));
set i=i+1;
end while;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
4. 调用存储过程
MariaDB [s18]> call auto_insert1();
Query OK, 1 row affected (8 min 36.25 sec)
5. 查看数据的数量
MariaDB [s18]> select count(2) from userinfo;
+----------+
| count(2) |
+----------+
| 2999999 |
+----------+
1 row in set (1.20 sec)
未加索引的查询效率
MariaDB [s18]> select * from userinfo where name='alex2000000';
+---------+-------------+--------+--------------------+
| id | name | gender | email |
+---------+-------------+--------+--------------------+
| 2000000 | alex2000000 | male | egon2000000@oldboy |
+---------+-------------+--------+--------------------+
1 row in set (1.41 sec)
MariaDB [s18]> select * from userinfo where id=2000000;
+---------+-------------+--------+--------------------+
| id | name | gender | email |
+---------+-------------+--------+--------------------+
| 2000000 | alex2000000 | male | egon2000000@oldboy |
+---------+-------------+--------+--------------------+
1 row in set (1.38 sec)
添加索引字段
添加索引需要耗费时间, 索引也是一种数据结构, 所以创建需要时间
MariaDB [s18]> create index ix_id on userinfo(id);
Query OK, 0 rows affected (6.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [s18]> show index from userinfo\G;
*************************** 1. row ***************************
Table: userinfo
Non_unique: 1
Key_name: ix_id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 2984616
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
添加索引后的查询效率
MariaDB [s18]> select * from userinfo where name='alex2000000';
+---------+-------------+--------+--------------------+
| id | name | gender | email |
+---------+-------------+--------+--------------------+
| 2000000 | alex2000000 | male | egon2000000@oldboy |
+---------+-------------+--------+--------------------+
1 row in set (1.74 sec)
MariaDB [s18]> select * from userinfo where id=2000000;
+---------+-------------+--------+--------------------+
| id | name | gender | email |
+---------+-------------+--------+--------------------+
| 2000000 | alex2000000 | male | egon2000000@oldboy |
+---------+-------------+--------+--------------------+
1 row in set (0.02 sec)
五. 索引的基本管理
- 创建表
MariaDB [s18]> create table city (id int primary key auto_increment, name varchar(128), countrycode varchar(4) not null, District varchar(20) not null, population int not null);
辅助索引管理
- 将name字段设置成辅助索引
MariaDB [s18]> alter table city add index idx_name(name);
- 删除索引
MariaDB [s18]> alter table city drop index idx_name;
- 查询索引
MariaDB [s18]> show index from city\G;
*************************** 1. row ***************************
Table: city
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
- 查看创建索引的帮助信息
MariaDB [s18]> help create index
覆盖索引管理(联合索引)管理
- 将countrycode和population 做组合索引
MariaDB [s18]> alter table city add index idx_co_po(countrycode,population);
前缀索引
MariaDB [s18]> alter table city add index idx_name(name(10));
唯一索引
- 创建
MariaDB [s18]> alter table city add unique index idx_name(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [s18]> desc city;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(128) | YES | UNI | NULL | |
| countrycode | varchar(4) | NO | | NULL | |
| District | varchar(20) | NO | | NULL | |
| population | int(11) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
执行计划
select获取数据的方法:
- 全表扫苗
- 索引扫描
一. 执行计划的作用
SQL最终获取的方法
二. 执行计划获取
获取优化器选择后的执行计划
MariaDB [s18]> explain select * from userinfo where id=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: userinfo
type: ref
possible_keys: ix_id
key: ix_id
key_len: 5
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
需要关注的信息
table: userinfo ------>查询操作的表
type: ref ------>索引类型
possible_keys: ix_id ------>可能会走的索引
key: ix_id ------>真正走的索引
Extra: ------>额外信息
type详解
-
ALL 全表扫描
注意: 生产中几乎没有这种需求的,尽量避免
explain select * from userinfo;
-
INDEX 全索引扫描
注意: 生产中几乎没有这种需求的,尽量避免
desc select id from userinfo;
- RANGE 索引范围扫描
select 范围查询: > < >= <=in,or
desc select * from userinfo where id<10;
- REF 辅助索引的等值查询
desc select * from userinfo where id=1union allselect * from userinfo where id=2;
- eq_ref 多表连接查询中, 连接条件是主键或者是唯一键的时候
- system,const 主键或者唯一键的等值查询
为name字段创建唯一键
MariaDB [s18]> alter table userinfo add unique index idx_name(name);
Query OK, 0 rows affected (10.76 sec)
MariaDB [s18]> desc select * from userinfo where name='alex1';
- NULL 索引中不包含查询的值
MariaDB [s18]> desc select * from userinfo where name='alexdddd1';
结论: 在索引扫描类型方面, 至少保证在range以上级别
其他字段解释
extra: Using filesort
MariaDB [s18]> show index from userinfo;
MariaDB [s18]> alter table userinfo drop index ix_id;
MariaDB [s18]> select * from userinfo where id between 10 and 20 order by name;
+------+--------+--------+---------------+
| id | name | gender | email |
+------+--------+--------+---------------+
| 10 | alex10 | male | egon10@oldboy |
| 11 | alex11 | male | egon11@oldboy |
| 12 | alex12 | male | egon12@oldboy |
| 13 | alex13 | male | egon13@oldboy |
| 14 | alex14 | male | egon14@oldboy |
| 15 | alex15 | male | egon15@oldboy |
| 16 | alex16 | male | egon16@oldboy |
| 17 | alex17 | male | egon17@oldboy |
| 18 | alex18 | male | egon18@oldboy |
| 19 | alex19 | male | egon19@oldboy |
| 20 | alex20 | male | egon20@oldboy |
+------+--------+--------+---------------+
desc select * from userinfo where id between 10 and 20 order by name;
解决思路:
索引可以减少排序, 可以很大程度减少CPU时间
辅助索引 应用顺序(优化器选择的)
如果查询条件符合覆盖索引的顺序时, 优先选择覆盖索引
不符合顺序, 优先会走where条件的索引
优化方法: , 将where列和order列建立联合索引
alter table userinfo add index idx_id_name(id,name);
explain使用场景(面试题)
公司的业务慢, 从数据库的角度分析原因
-
mysql出现性能问题(排除硬件,架构原因,参数,锁), 有2种情况
-
应急性的慢
数据库hang住了
处理过程
show processlist; 获取到导致数据库hang住的语句explain分析SQL的执行计划, 有没有走索引, 如果走了, 查看索引的类型情况- 建索引, 改语句
-
-
一段时间慢
处理过程
- 记录慢日志showlog, 分析showlog
explain分析SQL的执行计划, 有没有走索引, 如果走了, 查看索引的类型情况- 建索引, 改语句
索引应用规范
建立索引的原则(运维规范)
为了使索引的使用效率更高, 在创建索引时, 必须考虑在哪些字段上创建索引和创建什么类型的索引
-
建表时一定要有主键, 如果相关列不可以作为主键, 做一个无关列, 一般都是id列
-
选择唯一的索引
唯一性索引的值是唯一的, 可以更快速的通过该索引来确定某条记录
例如:
学生表中学号是具有唯一性的字段. 为该字段建立唯一性索引可以很快的确定某个学生的信息
如果选择姓名的话, 可能存在同名现象, 从而降低查询速度
主键索引和唯一键索引, 在查询中使用是效率最高的
-
为经常需要排序, 分组和联合操作的字段建立索引
经常需要
ORDER BY,GROUP BY,join on等操作的字段, 排序操作会浪费很多时间如果为其建立索引, 可以有效地避免排序操作
-
为经常作为where查询条件的字段建立索引
如果某个字段经常用来做查询条件, 那么该字段的查询速度会影响整个表的查询速度,
因此为这样的字段建立索引, 可以提高整个表的查询速度
where后面查询的字段符合的要走:
- 经常查询
- 列值的重复值少(业务层面调整)
-
尽量使用前缀来索引
如果索引字段的值很长, 最好使用值的前缀来索引
-
限制索引的数目
索引的数目不是越多越好. 每个索引都需要占用磁盘空间, 索引越多, 需要的磁盘空间就越大
修改表时, 对索引的重构和更新很麻烦. 越多的索引, 会使更新表变得浪费时间
-
删除不再使用或者很少使用的索引
表中的数据被大量更新, 或者数据的使用方式被改变后, 原有的一些索引可能不再需要
数据库管理员应当定期找出这些索引, 将他们删除, 从而减少对更新操作的影响
- 大表加索引, 要在业务不繁忙期间操作
- 少在经常更新值的列上建索引
不走索引的情况(开发规范)
-
没有查询条件, 或者查询条件没有建立索引
在业务数据库中, 特别是数据量比较大的表, 是没有全表扫描这种需求的
- 对用户查看是非常痛苦的
- 对服务器来讲毁灭性的
select * from tab;
SQL 改写
select * from tab order by price limit 10;需要在price列上建立索引
select * from tab where name='zhangsan';name列没有索引
修改
- 将有索引条件的列作为查询条件
- 对name列建立索引
-
查询结果集是原表中的大部分数据, 大约在25%以上
查询结果集, 就是select查询输出的结果, 如果这个结果超过了总行数的25%, 优化器觉得没有必要走索引
-
索引本身失效,统计数据不真实
索引有自我维护的能力
对于表内容变化比较频繁的情况下, 有可能会出现索引失效
-
查询条件使用函数在索引列上, 或者对索引列进行运算(
+,-,*,/,子查询)错误的例子:
select * from test where id-1=9;正确d额例子:
select * from test where id=10;
-
隐式转换导致索引失效, 在开发中经常会犯的错误
比如说索引字段是字符串, 通过某个函数把字符串转换为数字, 再利用where条件使用索引查询, 这种情况也是不走索引的
<>,not in不走索引
like "%_"百分号在前面不走
%linux%类的搜索需求, 可以用elasticsearch+mongodb专门做搜索服务的数据
- 单独引用联合索引里非第一位置的索引列, 作为条件查询时不走索引
MariaDB [s18]> create table t1(
-> id int,
-> name varchar(20),
-> age int,
-> sex enum('male','female'),
-> money int
-> );
Query OK, 0 rows affected (0.02 sec)
MariaDB [s18]> alter table t1 add index t1_idx(money,age,sex);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [s18]> desc t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| money | int(11) | YES | MUL | NULL | |
+-------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
MariaDB [s18]> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 1 | t1_idx | 1 | money | A | 0 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | t1_idx | 2 | age | A | 0 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | t1_idx | 3 | sex | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
走索引的where条件语句
where money age sex
where money sex age
where money sex
where money age
不走索引的情况where条件语句
where age sex money
where age money sex
where age sex
where age money
where sex age money
where sex money age
where sex money
where sex age
