-
十二、Mysql的索引
十二、Mysql的索引
一、什么是索引
索引是对数据库表的一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
举例说明索引：如果把数据库中的某一张看成一本书，那么索引就像是书的目录，可以通过目录快速查找书中指定内容的位置，对于数据库表来说，可以通过索引快速查找表中的数据。
简单来说：索引提供了类似于书中目录的作用,目的是为了优化查询
二、常见索引的种类(算法)
B树索引 （现在使用的） Hash索引 R树 Full text GIS- 1
- 2
- 3
- 4
- 5
三、B树 基于不同的查找算法分类介绍
B树分为： B-树，B+树（MyISAM和InnoDB引擎默认使用），B*树（现在不使用）
B+树在范围查询方面提供了更好的性能(> < >= <= like)
1、B+树结构
Level 0 叶子层为B+树的叶子节点，所有的叶子节点存储的是指向数据的指针而非数据本身。同时每个叶子结点都有指向下一个叶子结点的双向链接。 Level 1 枝干层和Level 2 树干层就是纯索引节点（数据）和主键，同一层的索引节点也都有指向下一个索引节点的双向链接。- 1
- 2
B+树查询过程的简易说明，使用数字来说明其查询过程
1、B+树的上一层存储的是下一层的最小数编号 2、查询数据是从最上层及树干层开始向下查询最后查询到叶子层 3、叶子层仅存储真实数据的指针- 1
- 2
- 3
我们以找数字7的数据为例：
1、把树干分为相邻的分支5、28、65 2、树干层：7属于5-28之间，28为一个分支的最少值，7不应该属于28所在分支；7大于5，7在5所在分支 3、树枝层：同理树干层选择5所在分支 4、最终在叶子层查到数字7所在的真实数据的指针。最终查询到数据内容- 1
- 2
- 3
- 4
2、B-树
B-树与B+树基本一致，除了同一层的索引节点没有指向下一个索引节点的双向链接。其查询方式也基本与B+树一样。
四、索引的功能性分类
1、辅助索引(S)及构建B树结构
(1). 索引是基于表中,列(索引键)的值生成的B树结构 (2). 首先提取此列所有的值,进行自动排序 (3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K) (4). 然后生成此索引键值所对应得后端数据页的指针 (5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度 id name age gender select * from t1 where id=10; 问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
辅助索引的分类
1.普通的单列辅助索引 2.联合索引 多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表 查询 3.唯一索引 索引列的值都是唯一的.- 1
- 2
- 3
- 4
- 5
- 6
2、聚集索引©及构建B树结构
构建前提
(1)表中设置了主键,主键列就会自动被作为聚集索引. (2)如果没有主键,会选择唯一键作为聚集索引. (3)聚集索引必须在建表时才有意义,一般是表的无关列,比如ID等为主键- 1
- 2
- 3
聚集索引©构建B树结构
(1) 在建表时,设置了主键列(ID) (2) 在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚集索引组织表) (3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点- 1
- 2
- 3
3、聚集索引和辅助索引构成区别
1、聚集索引只能有一个,非空唯一,一般为主键列 2、辅助索引,可以有多个,是配合聚集索引使用的 3、聚集索引叶子节点,就是磁盘的数据行存储的数据页 4、MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据 5、辅助索引,只会提取索引键值,进行自动排序生成B树结构- 1
- 2
- 3
- 4
- 5
4、关于索引树的高度受什么影响
1. 数据量级, 解决方法:分表,分库,分布式 2. 索引列值过长 , 解决方法:前缀索引 3. 数据类型: 变长长度字符串,使用了char,解决方案:变长字符串使用varchar enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......) 1 2 3 4.索引树最高设置为4层- 1
- 2
- 3
- 4
- 5
- 6
- 7
五、索引的管理
1、索引建立前相关信息
[world]>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 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) Field :列名字 key :有没有索引,索引类型 PRI: 主键索引 UNI: 唯一索引 MUL: 辅助索引(单列,联和,前缀)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
2、单列普通辅助索引
[world]>alter table city add index idx_name(name); 表 索引名（列名） [world]>create index idx_name1 on city(name); [world]>show index from city; 注意: 以上操作不代表生产操作,我们不建议在一个列上建多个索引 同一个表中，索引名不能同名。 ### 7.1.2 删除索引: db01 [world]>alter table city drop index idx_name1; 表名 索引名- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
3、覆盖索引(联合索引)
[world]>alter table city add index idx_co_po(countrycode,population);- 1
4、前缀索引
仅用于字符串，数字不能使用前缀索引。
常用于字符串很长的列
[world]>alter table city add index idx_di(district(5)); 注意：数字列不能用作前缀索引。- 1
- 2
5、唯一索引
只能用于主键列或唯一列
[world]>alter table city add unique index idx_uni1(name); ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1' #不是主键或唯一列会报错 [world]>alter table city add unique index idx_uni1(id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 #只能用于主键列或唯一列- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
统计city表中，以省的名字为分组，统计组的个数
select district,count(id) from city group by district; 需求: 找到world下,city表中 name列有重复值的行,最后删掉重复的行 [world]>select name,count(id) as cid from city group by name having cid>1 order by cid desc; [world]>select * from city where name='suzhou';- 1
- 2
- 3
- 4
6、索引创建的原则
(1) 最左前缀匹配原则
对于多列索引，总是从索引的最前面字段开始，接着往后，中间不能跳过。比如创建了多列索引(name,age,sex)，会先匹配name字段，再匹配age字段，再匹配sex字段的，中间不能跳过。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。一般，在创建多列索引时，where子句中使用最频繁的一列放在最左边。
看一个补符合最左前缀匹配原则和符合该原则的对比例子。
实例：表city建有索引(district,population)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CLG1YgGT-1668429729240)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20220613232151466.png)]
[world]>desc select * from city where population=49; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) #该sql直接用了第二个索引字段population，跳过了第一个索引字段district，不符合最左前缀匹配原则,因此没有走索引 [world]>desc select * from city where district='sichuang' and population=100000; +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | city | NULL | ref | idx_di_po | idx_di_po | 24 | const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) [world]>desc select * from city where district='sichuang' ; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | city | NULL | ref | idx_di_po | idx_di_po | 20 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) #上面2个例子符合索引的最左匹配，因此走了索引- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
(2) 尽量选择区分度高的列作为索引。
比如，我们会选择ID做索引，而不会population性别来做索引。
(3) =和in可以乱序
比如a = 1 and b = 2 and c = 3，建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
(4) 索引列不能参与计算，保持列“干净”
比如：Flistid+1>‘2000000608201108010831508721‘。原因很简单，假如索引列参与计算的话，那每次检索时，都会先将索引计算一次，再做比较，显然成本太大。
(5) 尽量的扩展索引，不要新建索引。
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
7、索引的不足
虽然索引可以提高查询效率，但索引也有自己的不足之处。
索引的额外开销：
(1) 空间：索引需要占用空间；
(2) 时间：查询索引需要时间；
(3) 维护：索引须要维护（数据变更时）；
不建议使用索引的情况：
(1) 数据量很小的表
(2) 空间紧张
8、常用索引的优化
1、有索引但未被用到的情况（不建议）
(1) Like的参数以通配符开头时
尽量避免Like的参数以通配符开头，否则数据库引擎会放弃使用索引而进行全表扫描。
(2) where条件不符合最左前缀原则时
例子已在最左前缀匹配原则的内容中有举例。
(3) 使用！= 或 <> 操作符时
尽量避免使用！= 或 <>操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。
(4) 索引列参与计算
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
select * from city where id-1=9;
(5) 对字段进行null值判断
应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： 低效：select * from city where population is null ;
可以在population上设置默认值0，确保表中population列没有null值，然后这样查询： 高效：select * from city where population =0;
(6) 使用or来连接条件
应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： 低效：select * from city where countrycode = ‘CHN’ or countrycode = ‘USA’;
可以用下面这样的查询代替上面的 or 查询： 高效：
select * from city where countrycode=“CHN”;
union all
select * from city where countrycode="USA;
2、避免select *
在解析的过程中，会将’*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
所以，应该养成一个需要什么就取什么的好习惯。
3、order by 语句优化
任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
方法：1.重写order by语句以使用索引；2.为所使用的列建立另外一个索引；3.绝对避免在order by子句中使用表达式
4、GROUP BY语句优化
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP by JOB
HAVING JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’
GROUP by JOB
5、用 exists 代替 in
很多时候用 exists 代替 in 是一个好的选择： select num from a where num in(select num from b) 用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)
6、使用 varchar/nvarchar 代替 char/nchar
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
7、能用DISTINCT的就不用GROUP BY
SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
可改为：
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10
8、能用UNION ALL就不要用UNION
UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源。
9、在Join表的时候使用相当类型的例，并将其索引
如果应用程序有很多JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。
而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）
六、执行计划获取及分析
1、构建实验环境
CREATE DATABASE oldboy CHARSET utf8mb4 COLLATE utf8mb4_bin; USE oldboy; CREATE TABLE t_100w(id INT,num INT,k1 CHAR(2),k2 CHAR(4),dt TIMESTAMP); DELIMITER // CREATE PROCEDURE rand_data(IN num INT) BEGIN DECLARE str CHAR(62) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; DECLARE str2 CHAR(2); DECLARE str4 CHAR(4); DECLARE i INT DEFAULT 0; WHILE i- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
2、执行计划说明
(1)获取到的是优化器选择完成的,他认为代价最小的执行计划. 作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题. 如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。 (2) select 获取数据的方法 1. 全表扫描(应当尽量避免,因为性能低) 2. 索引扫描 3. 获取不到数据- 1
- 2
- 3
- 4
- 5
- 6
- 7
3、执行计划获取
获取优化器选择后的执行计划
获取执行计划的命令 desc explain- 1
- 2
- 3
table:查询的表 type:查询类型 possible_keys:可能走的索引 key:走的索引名 key_len:应用索引的长度 rows:查询结果集的长度 extra:额外信息- 1
- 2
- 3
- 4
- 5
- 6
- 7
关注的执行计划的重点信息
[world]>desc select * from city\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: city #查询的表明 partitions: NULL type: ALL possible_keys: NULL #可能会走的索引 key: NULL #真正走的索引 key_len: NULL ref: NULL #索引类型 rows: 4188 filtered: 100.00 Extra: NULL #额外信息 1 row in set, 1 warning (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
4、索引类型详解
从左到右性能依次变好. ALL : 全表扫描,不走索引 例子: 1. 查询条件列,没有索引 SELECT * FROM t_100w WHERE k2='780P'; [oldboy]>desc select * from t_100w where k2='780p'; +----+-------------+--------+------------+------+---------------+------+---------+------+--------+---------- | id | select_type|table|partitions|type|possible_keys|key|key_len| ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+---------- | 1 | SIMPLE |t_100w|NULL | ALL | NULL |NULL|NULL | NULL | 997589 | 10.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+---------- 1 row in set, 1 warning (0.00 sec) 2. 查询条件出现以下语句(使用辅助索引列) USE world DESC city; DESC SELECT * FROM city WHERE countrycode <> 'CHN'; DESC SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA'); DESC SELECT * FROM city WHERE countrycode LIKE '%CH%'; 注意:对于聚集索引列,使用以上语句,依然会走索引 DESC SELECT * FROM city WHERE id <> 10; [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 |range| PRIMARY |PRIMARY| 4 | NULL |2103 |100.00 |Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------- 1 row in set, 1 warning (0.00 sec) INDEX : 全索引扫描 1. 查询需要获取整个索引列的值时: DESC SELECT countrycode FROM city; [world]>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.00 sec) 2. 联合索引中,任何一个非最左列作为查询条件时: idx_a_b_c(a,b,c) ---> a ab abc SELECT * FROM t1 WHERE b SELECT * FROM t1 WHERE c RANGE : 索引范围扫描 辅助索引> < >= <= LIKE IN OR 主键 <> NOT IN 例子: 1. DESC SELECT * FROM city WHERE id<5; 2. DESC SELECT * FROM city WHERE countrycode LIKE 'CH%'; 3. DESC SELECT * FROM city WHERE countrycode IN ('CHN','USA'); [world]>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 |range|PRIMARY | PRIMARY | 4 |NULL|2103| 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------- 1 row in set, 1 warning (0.00 sec) 注意: 1和2例子中,可以享受到B+树的优势,但是3例子中是不能享受的. 所以,我们可以将3号列子改写: DESC SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA'; ref: 非唯一性索引,等值查询 DESC SELECT * FROM city WHERE countrycode='CHN'; [world]>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: 在多表连接时,连接条件使用了唯一索引(uk pK) DESC SELECT b.name,a.name FROM city AS a JOIN country AS b ON a.countrycode=b.code WHERE a.population <100; DESC country [world]>DESC SELECT b.name,a.name FROM city AS a -> JOIN country AS b -> ON a.countrycode=b.code -> WHERE a.population <100; +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------- | id |select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered| Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------- | 1 | SIMPLE | a | NULL | ALL |CountryCode |NULL|NULL | NULL 4188 | 33.33 | Using where | | 1 | SIMPLE | b | NULL | eq_ref | PRIMARY |PRIMARY| 3 |world.a.CountryCode| 1 |100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------- 2 rows in set, 1 warning (0.00 sec system,const : 唯一索引的等值查询 DESC SELECT * FROM city WHERE id=10; [world]>DESC SELECT * FROM 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 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
5、其他字段解释
extra: filesort ,文件排序. 当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序；如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序，使用临时表）。 SHOW INDEX FROM city; ALTER TABLE city ADD INDEX CountryCode(CountryCode); ALTER TABLE city DROP INDEX idx_c_p; DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population ALTER TABLE city ADD INDEX idx_(population); DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population ALTER TABLE city ADD INDEX idx_c_p(countrycode,population); ALTER TABLE city DROP INDEX idx_; ALTER TABLE city DROP INDEX CountryCode; DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
结论: 1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现 2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引 3. 根据子句的执行顺序,去创建联合索引- 1
- 2
- 3
- 4
6、索引优化效果测试
优化前: [root@vm01 ~]# mysqlslap --defaults-file=/etc/my.cnf \ > --concurrency=100 --iterations=1 --create-schema='oldboy' \ > --query="select * from oldboy.t_100w where k2='fgcd'" engine=innodb \ > --number-of-queries=2000 -uroot -p123456 -verbose mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Running for engine rbose Average number of seconds to run all queries: 518.454 seconds Minimum number of seconds to run all queries: 518.454 seconds Maximum number of seconds to run all queries: 518.454 seconds Number of clients running queries: 100 Average number of queries per client: 20 创建k2的辅助索引 alter talbe t_100w add index idx_k2(k2); 优化后: [root@vm01 ~]# mysqlslap --defaults-file=/etc/my.cnf \ > --concurrency=100 --iterations=1 --create-schema='oldboy' \ > --query="select * from oldboy.t_100w where k2='fgcd'" engine=innodb \ > --number-of-queries=2000 -uroot -p123456 -verbose mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Running for engine rbose Average number of seconds to run all queries: 1.584 seconds Minimum number of seconds to run all queries: 1.584 seconds Maximum number of seconds to run all queries: 1.584 seconds Number of clients running queries: 100 Average number of queries per client: 20- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
7、联合索引
联合索引: 1. SELECT * FROM t1 WHERE a= b= 我们建立联合索引时: ALTER TABLE t1 ADD INDEX idx_a_b(a,b); ALTER TABLE t1 ADD INDEX idx_b_a(b,a); 以上的查询不考虑索引的顺序,优化器会自动调整where的条件顺序 注意: 索引,我们在这种情况下建索引时,需要考虑哪个列的唯一值更多,哪个放在索引左边. 2. 如果出现where 条件中出现不等值查询条件 DESC SELECT * FROM t_100w WHERE num <1000 AND k2='DEEF'; 我们建索引时: ALTER TABLE t_100w ADD INDEX idx_2_n(k2,num); 语句书写时 DESC SELECT * FROM t_100w WHERE k2='DEEF' AND num <1000 ; 3. 如果查询中出现多子句 我们要按照子句的执行顺序进行建立索引.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
8、explain(desc)使用场景
题目意思: 我们公司业务慢,请你从数据库的角度分析原因 1.mysql出现性能问题,我总结有两种情况: （1）应急性的慢：突然夯住 应急情况:数据库hang(卡了,资源耗尽) 处理过程: 1.show processlist; 获取到导致数据库hang的语句 2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况 3. 建索引,改语句 （2）一段时间慢(持续性的): (1)记录慢日志slowlog,分析slowlog (2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况 (3)建索引,改语句- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
9、索引应用规范
9.1走索引的情况
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?- 1
1、(必须的) 建表时一定要有主键,一般是个无关列
2、选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。 例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。 如果使用姓名的话，可能存在同名现象，从而降低查询速度。 优化方案: (1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分 (2) 可以将此列和其他的查询类,做联和索引 select count(*) from world.city; select count(distinct countrycode) from world.city; select count(distinct countrycode,population ) from world.city;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
3、(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段，创建索引
排序操作会浪费很多时间。 where A B C ----》 A B C in where A group by B order by C A,B，C 如果为其建立索引，优化查询 注：如果经常作为条件的列，重复值特别多，可以建立联合索引。- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
4、尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。- 1
5、限制索引的数目
索引的数目不是越多越好。 可能会产生的问题: (1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。 (2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。 (3) 优化器的负担会很重,有可能会影响到优化器的选择. percona-toolkit中有个工具,专门分析索引是否有用- 1
- 2
- 3
- 4
- 5
- 6
6、删除不再使用或者很少使用的索引(percona toolkit)
pt-duplicate-key-checker 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理 员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。- 1
- 2
- 3
- 4
7、大表加索引,要在业务不繁忙期间操作
8、尽量少在经常更新值的列上建索引
9、建索引原则
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列 (2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为) (3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引 (4) 列值长度较长的索引列,我们建议使用前缀索引. (5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx) (6) 索引维护要避开业务繁忙期- 1
- 2
- 3
- 4
- 5
- 6
9.2不走索引的情况
1、没有查询条件，或者查询条件没有建立索引
select * from tab; 全表扫描。 select * from tab where 1=1; 在业务数据库中，特别是数据量比较大的表。 是没有全表扫描这种需求。 1、对用户查看是非常痛苦的。 2、对服务器来讲毁灭性的。 （1） select * from tab; SQL改写成以下语句： select * from tab order by price limit 10 ; 需要在price列上建立索引 （2） select * from tab where name='zhangsan' name列没有索引 改： 1、换成有索引的列作为查询条件 2、将name列建立索引- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
2、查询结果集是原表中的大部分数据，应该是25％以上。
查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。 假如：tab表 id，name id:1-100w ，id列有(辅助)索引 select * from tab where id>500000; 如果业务允许，可以使用limit控制。 怎么改写 ？ 结合业务判断，有没有更好的方式。如果没有更好的改写方案 尽量不要在mysql存放这个数据了。放到redis里面。- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
3、 索引本身失效，统计数据不真实
索引有自我维护的能力。 对于表内容变化比较频繁的情况下，有可能会出现索引失效。 一般是删除重建 现象: 有一条select语句平常查询时很快,突然有一天很慢,会是什么原因 select? --->索引失效,，统计数据不真实 DML ? --->锁冲突- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
4、查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子： 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10; 算术运算 函数运算 子查询- 1
- 2
- 3
- 4
- 5
- 6
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>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
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'; 单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit or或in 尽量改成union EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119'); 改写成： EXPLAIN SELECT * FROM teltab WHERE telnum='110' UNION ALL SELECT * FROM teltab WHERE telnum='119'- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
7、 like “%_” 百分号在最前面不走
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引扫描 EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引 %linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品- 1
- 2
- 3
—±------±-----±--------------±--------±--------±------±-----±----------------------+
1 row in set (0.00 sec)
mysql>6、<> ，not in 不走索引（辅助索引）- 1
- 2
- 3
- 4
- 5
- 6
- 7
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’;单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in 尽量改成union
EXPLAIN SELECT * FROM teltab WHERE telnum IN (‘110’,‘119’);
改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum=‘110’
UNION ALL
SELECT * FROM teltab WHERE telnum=‘119’7、 like "%_" 百分号在最前面不走- 1
- 2
- 3
- 4
- 5
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘31%’ 走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘%110’ 不走索引
%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品- 1
- 2
9.程序的机器级代码表示，CISC和RISC
PyQt/PySide ImportError: DLL load failed while importing Shiboken，PyQt库和python
《算法系列》之数学
搭建自己的语义分割平台deeplabV3+
二十、一起学习Lua 面向对象
多线程下的单例设计模式（新手必看！！！）
2023年Java应该怎么学？零基础能自学成功吗？
[DP] DP优化总结
Centos中利用自带的定时器Crontab_实现mysql数据库自动备份_linux中mysql自动备份脚本---Linux运维工作笔记056