MySQL索引

最新推荐文章于 2024-06-17 15:31:11 发布

IQ low

最新推荐文章于 2024-06-17 15:31:11 发布

阅读量236

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_43058911/article/details/102978424

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

索引

什么是索引？

	在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个
表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的
目录，可以根据目录中的页码快速找到所需的内容。

	索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索
引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库
表中的特定信息。

为什么使用索引？

	当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件
进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就在
表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表
中对应的记录。

	索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外
的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

	在MySQL中，索引又称为“key”，创建索引后要会利用索引，正确方法使用索引，才能利用索引的优势提高服务器
的性能

建立索引的作用？

在数据库系统中建立索引主要有以下作用：
	1）快速取数据；
	2）保证数据记录的唯一性；
	3）实现表与表之间的参照完整性；
	4）在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

索引的优缺点

优点：
	1）索引可以降低服务需要扫描的数据量觉少了I/O次数
	2）索引可以帮助服务器避免排序和使用临时表
	3）索引可以帮助将随机I/O转为排序I/O

缺点：
	1）虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。
		因为更新表时，不仅要保存数据，还要保存一下索引文件。
	2）建立索引会占用额外磁盘空间。一般情况这个问题不太严重。

索引的建议

冗余和重复索引
	冗余索引：(A)，(A,B)，很显然无用
	重复索引：已经有索引，再次建立索引
索引优化策略：
	独立的使用列，尽量避免其参与运算，独立的列指的是索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，时钟将索引列单独放在比较符号的一侧	
		例如：select * from student where id+3 >=10;	：错误的
			  select * from student where id >=10-3;	：正确的
	左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估
		索引选择性：不重复的索引值和数据表的记录总数的比值
	多列索引：AND操作时更实用使用多列索引，而非为每个列创建单独的索引
	选择合适的索引列顺序：无排序和分组时，将选择性最高放到左侧

索引优化建议：
	只要列中含有null值，就最好不要在此列设置索引，符合索引如果有null值，此列在使用时也不会使用索引
	尽量使用短索引，如果可以，应该制定一个前缀长度
	对于经常在where字句中使用的列，最好设置索引
	对于有多个列where或者order by字句，应该建立符合索引
	对于like语句，以%或者'-'开头的不会使用索引，以%结尾会使用索引
	尽量不要在列上进行运算，函数操作和表达式操作，最好不做
	尽量不要使用not in 和<>操作

索引的类型

1）普通索引

创建：
	CREATE INDEX index_name ON table(column(length))

以修改表的方式创建索引：
	ALTER TABLE table_name ADD INDEX index_name ON (column(length))

创建表时指定：
	CREATE TABLE table_name([column(length...],INDEX index_name(column1,column 2,...));

2)唯一键索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
关键字：UNIQUE
创建：
	CREATE UNIQUE INDEX index_name ON table(column(length))

以修改表的方式创建索引：
	ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (column(length))

创建表时指定：
	CREATE TABLE table_name([column(length...],UNIQUE index_name(column1,column 2,...));

3）主键索引

一般一张表只有主键索引，在我们为表创建主键时，其实就是为表创建了主键索引

关键字：PRIMARY KEY

创建：CREATE UNIQUE INDEX index_name ON table(column1 PRIMARY KEY,...)

4）复合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
使用组合索引时遵循最左前缀集合

创建：
	CREATE INDEX index_age_name ON table(column1,column2);

以修改表的方式创建索引：
	ALTER TABLE `table` ADD INDEX idx_age_name (age,name);

其他相关：

帮助：help create index;

删除索引：drop index index_name on tb_name;

查看索引：show indexes from [db_name.]tb_name;

优化表空间：optimize table tb_name;

查看索引的使用：
	show variables like 'userstat';	：查看此变量状态
	set global userstat=1|on;	：userstat既是选项又是变量，选项可写在配置文件中
	show index_statistics;：只有userstat选项是ON开启状态，才可以查看索引的使用
							生产中可根据此命令查看有哪些索引没有用，然后删除节约空间

explain工具

官网文档：详细说明，前提是可以翻墙哦！
国内参考

简单到不能再简单的介绍：
	通过explain来分析索引的有效性，通过命令查看索引是否被用过，经常不用的索引就可以考虑删除，占用空间
语法：explain select * from table_name;
	获取查询执行计划信息，用来查看查询优化器如何执行查询

创建一个示例数据库和表及表中内容

创建数据库
	CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hello` /*!40100 DEFAULT CHARACTER SET utf8 */;
创建表结构
	CREATE TABLE `student` (
    `id` tinyint(4) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) DEFAULT NULL,
    `age` tinyint(4) DEFAULT '25',
    `sex` enum('F','M') DEFAULT 'M',
    `phone` char(11) DEFAULT NULL,
    `class` tinyint(4) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
插入表数据
	INSERT INTO `student` VALUES
	(1,'Bai zhantang',24,'M','10086',1),
	(2,'Tong xiangyu',23,'F','10010',NULL),
	(3,'Guo furong',21,'F','11010',2),
	(4,'Li dazui',28,'M','12030',NULL),
	(5,'Mo xiaobei',15,'F','13041',1),
	(6,'Lu xiucai',22,'M','10001',NULL),
	(7,'Yan xiaoliu',20,'M','14325',2),
	(8,'Xing yusen',29,'M','3525436',1),
	(9,'Lu wushuang',19,'F','123545',NULL),
	(10,'Zhao shuai',26,'M','345346',NULL),
	(11,'Bao zheng',33,'M','98745',2),
	(12,'Liu yifei',19,'F','13423543',NULL),
	(13,'Yang mi',29,'F','74568',2),
	(14,'Liu yan',23,'F','677956',2),
	(15,'Hu ge',33,'M','67957695',NULL),
	(16,'Sun wukong',100,'M','46745674',NULL),
	(17,'Zhou xingchi',55,'M','356756',NULL),
	(18,'Liu dehua',57,'M','5624565',NULL);

普通索引示例：

创建：create index idx_name on student(name);

测试：explain select name from student where name='liu yifei';

输出：
	+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
	| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
	+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
	|  1 | SIMPLE      | student | NULL       | ref  | idx_name      | idx_name | 63      | const |    1 |   100.00 | Using index |
	+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+

输出字段介绍：
	id				该SELECT标识符，当有多条命令时，id数递增
	select_type		该SELECT类型
	table			输出行表，执行对象表的名
	partitions		匹配的分区
	type			联接类型
	
	possible_keys	可能用到的索引选择		：这两项关键，区分实际有没有用到索引
	key				实际选择用到的索引
	
	key_len			所选键的长度
	ref				与索引比较的列
	rows			估计要检查的行
	filtered		按表条件过滤的行百分比
	Extra			附加信息

唯一键索引示例：

创建：CREATE UNIQUE INDEX u_idx_name ON student(name); 

测试：explain select name from student where name='liu yifei';

输出：
	+----+-------------+---------+------------+-------+---------------------+------------+---------+-------+------+----------+-------------+
	| id | select_type | table   | partitions | type  | possible_keys       | key        | key_len | ref   | rows | filtered | Extra       |
	+----+-------------+---------+------------+-------+---------------------+------------+---------+-------+------+----------+-------------+
	|  1 | SIMPLE      | student | NULL       | const | u_idx_name,idx_name | u_idx_name | 63      | const |    1 |   100.00 | 	Using index |
	+----+-------------+---------+------------+-------+---------------------+------------+---------+-------+------+----------+-------------+

注意：我在name字段创建了普通索引和唯一键索引，possible_keys字段是可能用到的索引，key字段是实际用到的索引，说明唯一键索引要比普通索引更优

复合键索引示例：

创建：create index idex_age_name on student(age,name);

测试：explain select * from student where age=23 and name="Liu yan";

输出：
	+----+-------------+---------+------------+-------+-----------------------------------+------------+---------+-------+------+----------+-------+
	| id | select_type | table   | partitions | type  | possible_keys                     | key        | key_len | ref   | rows | filtered | Extra |
	+----+-------------+---------+------------+-------+-----------------------------------+------------+---------+-------+------+----------+-------+
	|  1 | SIMPLE      | student | NULL       | const | u_idx_name,idx_name,idex_age_name | u_idx_name | 63      | const |    1 |   100.00 | NULL  |
	+----+-------------+---------+------------+-------+-----------------------------------+------------+---------+-------+------+----------+-------+

注意：我在name字段创建了普通索引和唯一键索引，possible_keys字段是可能用到的索引，key字段是实际用到的索引，说明唯一键索引要比普通索引和复合索引更优

主键索引：

在创建表时就指定了主键，创建主键的同时其实也就是创建了主键索引

测试：explain select * from student where id=13;

输出：
	+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
	| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
	+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
	|  1 | SIMPLE      | student | NULL       | const | PRIMARY       | PRIMARY | 1       | const |    1 |   100.00 | NULL  |
	+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+