InnoDB存储引擎只支持BTree索引
聚簇索引的叶子节点包含了行的全部数据,而节点页只包含了索引列,比如下图索引列 图2
BTREE: B+树索引
HASH: HASH索引
FULLTEXT: 全文索引
RTREE: R树索引
假如查找id为33的数据,首先判断大小后去"根"
找P2对应的枝节点,然后在"枝节点"
又找到P1,最后找到在"叶子节点"
内id为33页码,然后根据"数据页码"
直接去数据行找数据。
B+树索引就是在查找到id为33存在的"叶子节点"
后,它可以通过图中的粉红色Q字母
自动识别相邻的叶子存储的id号,下次再去查找时可以跳过"根"
与"枝节点"
直接去对应的叶子找到要找的id号。
叶子节点存储的是真实一行一行的数据行数据
叶子节点存储排序数据行后生成的数据页页码号
辅助索引生成的数据页码号
值不再是单纯的数据页码号,而是数据行的聚集索引生成的ID号对应辅助索引数据页码号
每个InnoDB的表都拥有一个索引,称之为聚集索引,此索引中存储着行记录,一般来说,聚集索引是根据主键生成的。为了能够获得高性能的查询、插入和其他数据库操作,理解InnoDB聚集索引是很有必要的。
聚集索引整体是一个b+树
,非叶子节点存放的是键值
,叶子节点存放的是行数据
,称之为数据页,这就决定了表中的数据也是聚集索引中的一部分,数据页之间是通过一个双向链表来链接的,上文说到B+树是一棵平衡查找树,也就是聚集索引的数据存储是有序的,但是这个是逻辑上的有序
,但是在实际数据的物理存储上是,因为数据页之间是通过双向链表来连接,假如物理存储是顺序的话,那维护聚集索引的成本非常的高。
除了聚集索引之外的索引都可以称之为辅助索引,与聚集索引的区别在于辅助索引的叶子节点中存放的是主键的键值
。一张表可以存在多个辅助索引,但是只能有一个聚集索引,通过辅助索引来查找对应的航记录的话,需要进行两步,第一步通过辅助索引来确定对应的主键,第二步通过相应的主键值在聚集索引中查询到对应的行记录
,也就是进行两次B+树搜索
。相反通过辅助索引来查询主键的话,遍历一次辅助索引就可以确定主键
了,也就是所谓的索引覆盖,不用回表(查询聚集索引)。
第一步通过辅助索引来确定对应的主键,第二步通过相应的主键值在聚集索引中查询到对应的行记录
建立两列以上的索引,即可查询联合索引里的列的数据而不需要进行回表二次查询,如index(col1, col2),执行下面的语句:
select col1, col2 from t1 where col1 = '213';
因为联合索引的列包括了col1和col2,不需要查询别的列,所以不需要进行二次查询。
要注意使用联合索引需要满足最左侧索引的原则,也就是查询的时候如果where条件里面没有最左边的一到多列,索引就不会起作用。
假设创建了联合索引index(A,B,C),那么其实相当于创建了如下三个组合索引:
index(A,B,C)
index(A,B)
index(A)
这就是最左索引原则,就是从最左侧开始组合。
CREATE INDEX index_name ON table(column(length))
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)
DROP INDEX index_name ON table
**表示:**PRI
聚簇索引的叶子节点包含了行的全部数据,而节点页只包含了索引列
聚簇索引,索引中包含了所有的行数据。聚簇索引和主键是一个意思的两种叫法
。当SQL语句通过聚簇索引访问表数据时,由于通过索引能直接定位并访问表数据,所以性能很高。
mysql> desc temp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(64) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
# 对id列添加自增
mysql> alter table temp modify id int(11) AUTO_INCREMENT;
或
# 对id列添加主键与自增
mysql> alter table temp modify id int(11) primary key AUTO_INCREMENT;
# 先把自增删除
mysql> alter table temp modify id int(11);
# 删除主键
mysql> alter table temp drop primary key;
mysql> desc temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(64) | YES | | NULL | |2019-11-06 15:36:06 星期三
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
**表示:**MUL
mysql> DESC student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| cardid | char(18) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | NO | | 18 | |
| gender | enum('男','女') | NO | | 男 | |
| birthday | datetime | NO | | CURRENT_TIMESTAMP | |
| tnum | char(11) | NO | UNI | NULL | |
| state | enum('1','0') | NO | | 1 | |
+----------+---------------------+------+-----+-------------------+----------------+
PRI是主键索引
UNI是唯一索引(不能有重复的值)
#对name行添加索引并命名为idx_name,把name行对应的数据添加为索引
mysql> alter table student add index idx_name(name);
#从student表中查询索引
mysql> show index from student;
#对name行删除idx_name索引
mysql> alter table student drop index idx_name;
表示: UNI
mysql> DESC student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| cardid | char(18) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | NO | | 18 | |
| gender | enum('男','女') | NO | | 男 | |
| birthday | datetime | NO | | CURRENT_TIMESTAMP | |
| tnum | char(11) | NO | UNI | NULL | |
| state | enum('1','0') | NO | | 1 | |
+----------+---------------------+------+-----+-------------------+----------------+
#判断tnum列的数据重复次数大于0的
mysql> select tnum,count(tnum) from student group by tnum having count(tnum) > 0;
+-------------+----+
| tnum | ct |
+-------------+----+
| 18805403432 | 1 |
| 18805407666 | 1 |
| 18805407677 | 1 |
| 18805407688 | 1 |
| 18805407699 | 1 |
| 81818181818 | 1 |
+-------------+----+
#基于tnum列的数据创建唯一索引,并命名为idx_tnum
mysql> alter table student add unique index idx_tnum(tnum);
#从student表中查询索引
mysql> show index from student;
#删除基于tnum列创建的唯一索引
mysql> alter table student drop index idx_tnum;
mysql> alter table student add note varchar(200);
mysql> desc student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| cardid | char(18) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | NO | | 18 | |
| gender | enum('男','女') | NO | | 男 | |
| birthday | datetime | NO | | CURRENT_TIMESTAMP | |
| tnum | char(11) | NO | UNI | NULL | |
| state | enum('1','0') | NO | | 1 | |
| note | varchar(200) | YES | | NULL | |
+----------+---------------------+------+-----+-------------------+----------------+
#基于note行创建前缀索引,也就是每一条数据的前10个字符作为索引
alter table student add index idx_note(note(10));
#从student表中查询索引
mysql> show index from student;
#删除基于note行创建的前缀索引
alter table student drop index idx_note;
#根据年龄与性别创建索引
mysql> desc student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| cardid | char(18) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | NO | | 18 | |
| gender | enum('男','女') | NO | | 男 | |
| birthday | datetime | NO | | CURRENT_TIMESTAMP | |
| tnum | char(11) | NO | UNI | NULL | |
| state | enum('1','0') | NO | | 1 | |
| note | varchar(200) | YES | | NULL | |
+----------+---------------------+------+-----+-------------------+----------------+
#创建一个性别与年龄的联合索引
alter table student add index idx_object(gender,age);
#从student表中查询索引
mysql> show index from student;
#删除基于年龄与性别的联合索引
alter table student drop index idx_object;
避免辅助索引的叶子节点数据页数据不够导致要查询主键索引
,所以要创建覆盖索引。另类的联合索引
select name,age,gender from students where name='olda';
这时就会通过主键 id 值去二次查询聚集索引
。不需要二次查询聚集索引
。mysql> desc students;
+----------+---------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+-------------------+-------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 18 | |
| gender | enum('男','女') | NO | | 男 | |
| birthday | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+----------+---------------------+------+-----+-------------------+-------------------+
5 rows in set (0.01 sec)
# 当有经常使用的字段数据查询时,可以考虑创建覆盖索引
mysql> select name,age,gender from students where name='olda';
# 根据 select 的字段进行创建索引
mysql> alter table students add index idx_name(name,age,gender);
当数据表过大时,可以选择创建辅助索引
当查询的数据表内值唯一,可以选择创建唯一索引
当数据表值字符过长,可以选择创建前缀索引
当数据表查询的条件不唯一时,可以选择创建联合索引
优化器决定走全表扫描
还是索引扫描
怎么确认语句是否走索引扫描?
1、从查询速度上
2、优化器执行计划选择情况
加在where条件上
加在表之间join的键值上
不要将索引加在区别率不高的字段上
字段上增加函数,则字段上的索引用不了,需考虑改变写法
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
mysql> select sname from students force index(idx_name);
+--------+
| sname |
+--------+
| Andrew |
| Andy |
| Bob |
| Cindy |
| John |
| Mike |
| Ruth |
| Susan |
+--------+
8 rows in set (0.01 sec) # 强制使用指定索引后使用了10毫秒
mysql> select sname from students;
+--------+
| sname |
+--------+
| Andrew |
| Andy |
| Bob |
| Cindy |
| John |
| Mike |
| Ruth |
| Susan |
+--------+
8 rows in set (0.00 sec) # 没有强制使用指定索引 使用了0毫秒
表示执行顺序,值越大则优先级越高;值相同则从上而下执行
simple: 表示不需要union操作或者不包含子查询的简单select查询。有连接查询时,外层的查询为simple,且 只有一个
primary: 一个需要union操作或者含有子查询的select,位于最外层的单位查询的select_type即为primary。 且只有一个
union: union连接的两个select查询,第一个查询是dervied派生表,除了第一个表外,第二个以后的表 select_type都是union
dependent union: 与union一样,出现在union 或union all语句中,但是这个查询要受到外部查询的影响
union result: 包含union的结果集,在union和union all语句中,因为它不需要参与查询,所以id字段为null
subquery: 除了from字句中包含的子查询外,其他地方出现的子查询都可能是subquery
dependent subquery: 与dependent union类似,表示这个subquery的查询要受到外部表查询的影响
derived: from字句中出现的子查询,也叫做派生表,其他数据库中可能叫做内联视图或嵌套select
ALL -> index -> range -> ref -> eq_ref -> const,system -> NULL
ALL: Full Table Scan, MySQL将遍历全表以找到匹配的行
index: Full Index Scan,index与ALL区别为index类型只遍历索引树
range: 索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询
unique_subquery: 用于where中的in形式子查询,子查询返回不重复值唯一值
index_subquery: 用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重
ref: 非唯一性索引扫描,返回匹配某个单独值的所有行。常见于使用非唯一索引和唯一索引的非唯一前缀进行的查找
eq_ref: 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描的多表链接操作中
system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该 查询转换为一个常量。System为表中只有一行数据或者是空表,且只能用于myisam和memory表。如果是Innodb引擎表,type列在这个情况通常都是all或者index
const: 使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const。其他数据库也叫做唯一索引扫描
NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引
不一定被查询使用
MySQL在查询中实际使用的索引
,若没有使用索引,显示为NULL估算
的找到所需的记录所需要读取的行数
Using index: 该值表示相应的select操作中使用了覆盖索引(Covering Index)
Using where: 表示MySQL服务器在存储引擎收到记录后进行“后过滤” 多个where条件,根据查询出的数据进行二次筛选
Using temporary: 表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询
Using filesort: MySQL中无法利用索引完成的排序操作称为“文件排序”,常见于order by和group by语句中
题目意思:
我们公司业务慢,请你从数据库的角度分析原因
一.mysql出现性能问题,我总结有两种情况:
1)应急性的慢:突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1. show processlist; 获取到导致数据库hang的语句
2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句
2)一段时间慢(持续性的):
处理过程:
1.记录慢日志slowlog,分析slowlog
2.explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3.建索引,改语句
Cardinality
: 3 字段是数据不重复的数据行数
mysql> show index from temp;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| temp | 1 | idx_name | 1 | name | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
table: 被扫描的表名
type: 优化器走的索引类型
ALL 全表扫描
示例:desc select * from city;
index 需要扫描整个索引树,获取到想要数据,比ALL性能好,顺序IO,可以减少回表查询
示例:desc select id from city;
range 基于索引范围作为查询条件,> <>= <= or like
示例:desc select * from city where id<10;
ref 普通索引,等值查询
示例:desc select * from city where CountryCode='CHN';
示例:desc select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
eq_ref 在发生join操作时,on的条件列是主键或唯一键
示例:desc select country.name,city.name,country.SurfaceArea from city join country on city.countrycode=country.code where city.name='shenyang';
const或system 唯一键索引,主键索引等值查询
示例:desc select * from city where id='1';
NULL 索引中扫描不到这个数据
示例:desc select * from city where id=5000;
possible_keys 可能会用到的索引
key 真正使用的索引
key_len 索引占用磁盘空间大小,越小越好
rows 扫描后返回的数据行,超过所有数据行的25%会自动转为全表扫描
Extra Using index
desc + select语句` 或 `explain + select语句
#看type的类型与key最后走的索引,(这是一个全表查询的选择)
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.10 sec)
#这是一个通过自定义的idx_name辅助索引查询的
mysql> explain select * from student where name='olda';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_name | idx_name | 152 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
执行过程Extra字段的参数意思