一、MySQL索引的定义和分类
1. 索引概念
索引就像一本书的目录,目的是加快数据检索速度。然而,并非索引越多越好,这就像一本1000页的书,如果有500页都是目录,效率反而会降低。因为创建每一个索引都会占用磁盘空间。
在技术层面,索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它包含对数据表里所有记录的引用指针。
2. MySQL索引的结构
-
Hash索引:MySQL很少使用,以hash形式组织数据索引,查找某一条记录的速度非常快。但由于是hash结构,每个键只对应一个值,且是散列方式分布,因此不支持范围查找和排序等功能。
-
B+树索引:MySQL最常用的索引数据结构,以平衡树形式组织,适合用来处理排序,范围查找等功能。虽然查找单条记录的速度比不上hash索引,但由于更适合排序等操作,所以更受欢迎。
3. MySQL常见索引分类
-
PRIMARY KEY(主键索引):唯一标识一条记录,每张表只能定义一个主键索引。
-
UNIQUE INDEX(唯一索引):保证数据记录的唯一性,但允许有空值。
-
INDEX(普通索引):最基本的索引,主要作用是加快对数据的访问速度。允许被索引的数据列包含重复的值。
-
FULLTEXT INDEX(全文索引):从 MySQL 5.6 版本开始,InnoDB 存储引擎也支持全文索引(FULLTEXT Index)。在此之前,全文索引仅在 MyISAM 存储引擎上受到支持。
-
组合索引:提高mysql效率,可以建立组合索引,遵循”最左前缀“原则。
4. MySQL各种索引区别
- 普通索引:最基本的索引,没有任何限制
- 唯一索引:索引列的值必须唯一,但允许有空值
- 主键索引:一种特殊的唯一索引,不允许有空值
- 全文索引:从 MySQL 5.6 版本开始,InnoDB 存储引擎也支持全文索引(FULLTEXT Index)。在此之前,全文索引仅在 MyISAM 存储引擎上受到支持。
- 组合索引:为了提高MySQL效率,可建立组合索引,需遵循"最左前缀"原则
二、创建索引
1、在CREATE TABLE语句中创建索引
2、CREATE INDEX语句创建索引
CREATE 索引类型 索引名 ON 表名(列名) USING 索引方式 COMMENT '注释';
列如: CREATE INDEX idx_addtime ON test(addtime) USING BTREE COMMENT 'c';
CREATE UNIQUE INDEX idx_addtime ON test(addtime) USING HASH COMMENT 'c';
3、ALTER TABLE 语句创建索引:
ALTER TABLE 表名 ADD 索引类型 索引名(列名) USING 索引方式 COMMENT '注释', ALGORITHM=INPLACE, LOCK=NONE;
3.1、其中索引类型包括:
主键索引(PRIMARY KEY),联合索引(UNIQUE INDEX)、普通索引(INDEX)和全文索引(FULLTEXT INDEX)
3.2、索引方式包括:
HASH和BTREE,默认为BTREE
3.3、ALGORITHM的值包括INPLACE和COPY
3.3.1、ALGORITHM=INPLACE,当前表加索引;
步骤:
1.创建索引(二级索引)数据字典
2.加共享表锁,禁止DML,允许查询
3.读取聚簇索引,构造新的索引项,排序并插入新索引
4.等待打开当前表的所有只读事务提交
5.创建索引结束
3.3.2、ALGORITHM=COPY,通过临时表创建索引,需要多一倍存储,还有更多的IO,
步骤:
1.新建带索引(主键索引)的临时表
2.锁原表,禁止DML,允许查询
3.将原表数据拷贝到临时表
4.禁止读写,进行rename,升级字典锁
5.完成创建索引操作
3.4、LOCK不同值的用法
LOCK=DEFAULT:默认方式,MySQL自行判断使用哪种LOCK模式,尽量不锁表
LOCK=NONE:无锁:允许Online DDL期间进行并发读写操作。如果Online DDL操
作不支持对表的继续写入,则DDL操作失败,对表修改无效
LOCK=SHARED:共享锁:Online DDL操作期间堵塞写入,不影响读取
LOCK=EXCLUSIVE:排它锁:Online DDL操作期间不允许对锁表进行任何操作
4、重建索引
4.1、REPAIR TABLE 表名 QUICK;
是用于修复MySQL数据库中指定表的命令,并使用QUICK修复选项。QUICK选项只能修复一些简单的表损坏问题,例如在索引中删除未使用的空间。它不执行完整的表扫描并且不检查所有的行和列是否正确。
重建索引在常规的数据库维护操作中经常使用。在数据库运行了较长时间后,索引都有损坏的可能,这时就需要重建。对数据重建索引可以起到提高检索效率。
5.2、alter table T engine=InnoDB重建主键索引
三、查询数据表的索引信息
1、查询表的全部索引信息:
SHOW INDEX FROM 表名; 或 SHOW keys FROM 表名;
比如: SHOW INDEX FROM user;
+-----------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| user | 0 | PRIMARY | 1 | member_id | A | 1099 | NULL | NULL | | BTREE | |
| user | 1 | idx_nickname_passwd | 1 | member_nickname | A | 549 | NULL | NULL | | BTREE | |
| user | 1 | idx_nickname_passwd | 2 | member_password | A | 1099 | NULL | NULL | | BTREE | |
| user | 1 | member_mobile | 1 | member_mobile | A | 1099 | NULL | NULL | | BTREE | |
+-----------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
解释:
Table:索引所属的表名。
Non_unique:表示索引是否允许重复值。如果该值为0,则索引是唯一索引;如果该值为1,则索引允许重复值。
Key_name:索引的名称。
Seq_in_index:索引中列的顺序。对于复合索引,这个值表示列在索引中的位置。
Column_name:索引中的列名。
Collation:指定用于该列排序的字符集规则。有值'A'(升序)或NULL(无分类)。
Cardinality:索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。
Sub_part:如果索引只使用了列的一部分,则此字段表示使用的列的长度。
Packed:指示索引是否被压缩的标志。如果该值为NULL,则索引未压缩。
Null:表示索引中的列是否允许NULL值。如果该值为YES,则列允许NULL值;如果该值为NO,则列不允许NULL值。
Index_type:索引的类型,如BTREE、HASH等。
Comment:索引的注释。
2、冗余索引查看的方法:
select table_schema,table_name,redundant_index_name,redundant_index_columns,dominant_index_name,dominant_index_columns from sys.schema_redundant_indexes;
3、查看未使用的索引的方法:
select * from sys.schema_unused_indexes;
4、查看索引的可见性
mysql8.0以上,可以通过information_schema.statistics、show index查看索引的可见性:
select index_name, is_visible from information_schema.statistics where table_schema = 'abce' and table_name = 't1';
show index from t1;
四、删除索引
删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。
1、DROP 索引类型 索引名 ON 表名 列名;
2、DROP INDEX可以在ALTER TABLE内部作为一条语句处理。如:
ALTER TABLE 表名 DROP 索引类型 索引名 列名;
ALTER TABLE 表名 DROP PRIMARY KEY 索引名 列名;
3、删除PRIMARY KEY索引时可以不指定索引名,因为一个表只可能有一个PRIMARY KEY索引。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。
4、如果从表中删除某列,则索引会受影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。
五、索引的统计和采样
1、查询索引采样统计信息 innodb_index_stats;
查看某个表的索引采样统计信息:SELECT * FROM mysql.innodb_index_stats WHERE table_name='表名'
stat_name------统计信息名称,其对应的值保持在stat_value
stat_name 值说明:
1.size:stat_value 表示索引中的总页的数量
2.n_leaf_pages:stat_value 表示索引叶子页的数量
3.n_diff_pfxNN:(NN代表数字)stat_value 表示索引的Frist column 列的唯一数量,列如NN表示02:第一个和第二个列组合索引的唯一值数量。
stat_value-----保存stat_name的字段对应统计信息值
sample_size-------stat_value字段提供的统计信息值的采样页数
stat_description--------统计信息名称stat_name字段中指定的统计信息的说明。
***select stat_value pages,index_name,stat_value*@@innodb_page_size/1024/1024 size from mysql.innodb_index_stats where table_name='oa_client' and stat_name='size'//获取每个索引的大小 #AND index_name!='PRIMARY'#sum(stat_value*@@innodb_page_size/1024/1024) size
2、设置采样基数
set GLOBAL innodb_stats_persistent_sample_pages=20000
innodb_stats_persistent这个参数控制着统计信息是否写入到磁盘上,否则频繁计算的统计信息可能导致执行计划发生改变。
innodb_stats_persistent_sample_pages这个参数控制着采样的数量,跟oracle动态采样参数含义相同。
统计信息存放在 mysql.innodb_table_stats and mysql.innodb_index_stats两个表中,通过这2个表上的last_update字段,可以看到最后的统计信息收集的时间。
可以使用ALTER TABLE tbl_name STATS_PERSISTENT=0这个语句来进行统计信息的非持久化。
innodb_stats_auto_recalc 这个参数控制着在表中行的数量改变超过10%的时候,是否重新收集统计信息。这个收集的动作是异步的,
在执行完大的dml后,可能会过一段时间才重新收集统计信息,如果想要及时的统计信息,执行analyze命令去收集
******在下面的情况下考虑修改innodb_stats_persistent_sample_pages采样数:
1、统计信息不够精确,执行计划没有最优化。统计是否准确,可以通过手工统计跟mysql.innodb_index_stats 中的信息对比。
2、analzye命令太慢了,可能是采样太多了
可以看到索引的数量也是跟统计信息收集快慢有关的,及主键的构成。
***查看索引的大小,可以在统计信息中看
SELECT SUM(stat_value) pages, index_name,SUM(stat_value)*@@innodb_page_size size FROM mysql.innodb_index_stats WHERE table_name = '表名' AND stat_name = 'size' GROUP BY index_name;
3、从新统计索引信息:
analyze table 表名;
六、索引优化相关
1、看下MySQL的会话状态值: show status like 'Handler_read_next'
此选项表明在进行索引扫描时,按照索引从数据文件里取数据的次数
2、查看监控全表扫描的sql语句
select * from sys.statements_with_full_table_scans where db = 'test';
3、可以通过IGNORE INDEX、 force INDEX来影响索引的选择
4、覆盖索引
当查询的字段和条件字段都在索引中就会用到覆盖索引,不需要回表查了
5、索引合并交集访问算法
当WHERE条件中AND结合的子句是多个索引中确切的值,或者键的范围条件和其他索引确切的值时,会用到索引合并交集访问算法。
例如:WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;
WHERE primary_key < 10 AND key_col1=20;
6、索引合并并集访问算法
当WHERE条件中or结合的子句都能用到索引时,
例如:WHERE key1_part1=1 AND key1_part2=2 OR key3=3;
7、索引合并排序并集访问算法
当WHERE子句结合OR被转换为不同的关键字的几个范围条件,但索引合并方法联合算法并不适用的时候。
WHERE key_col1 < 10 OR key_col2 < 20;
七、组合索引
1、组合索引可以支持最左前缀原则
2、在建立组合索引的时候,如何确定组合索引内的字段顺序。
首先,要看索引的复用能力。因为组合索引可以支持最左前缀,所以当已经有了 (a,b) 这个组合索引后,一般就不需要单独在 a 上建立索引了。因此,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。
其次,当既有关于(a,b)的组合查询,又有基于 a、b 各自的查询时,查询条件里面只有 b 的语句,是无法使用 (a,b) 这个组合索引的,这时候你不得不维护另外一个索引,也就是需要同时维护 (a,b)、(b) 或者(b,a)、 (a)这样的索引。这时候,就要考虑的索引的存储空间空间了。比如a字段占用的空间比b字段大时。因为(a,b)和(b,a)占用的空间是一样的。所以建立(a,b)、(b) 两个索引占用的空间就比(b,a)、 (a)两个索引的空要小。