【小白专用已验证24.6.13】mysql 添加索引-结合实战项目

zgscwxd

已于 2024-06-13 18:49:58 修改

阅读量244

点赞数 3

文章标签： mysql 数据库

于 2024-05-30 09:50:34 首次发布

本文链接：https://blog.csdn.net/zgscwxd/article/details/139313272

版权

CREATE TABLE user (
  `id` varchar(32) NOT NULL COMMENT `id`,
  `name` varchar(100) NOT NULL COMMENT `姓名`,
  `gender` varchar(200) NOT NULL COMMENT `性别`,
  `age` int NOT NULL COMMENT `年龄`,
  `updateTime` datetime DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY user_info (`name`,`gender`,`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

》》》》》》》》》》》》》》案例24.6.13

DROP TABLE IF EXISTS `hkcms_admin_panel`;
CREATE TABLE `hkcms_admin_panel` (
  `auth_rule_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '菜单ID',
  `admin_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
  UNIQUE KEY `userid` (`auth_rule_id`,`admin_id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='管理后台常用菜单';

如果你想要在现有的表上创建这样的唯一索引，你可以使用ALTER TABLE语句，如下：

ALTER TABLE your_table_name
ADD UNIQUE KEY `userid` (`auth_rule_id`,`admin_id`) USING BTREE;

如果你想要在创建新表的时候直接定义这个唯一索引，你可以在CREATE TABLE语句中包含类似的定义：

CREATE TABLE your_table_name (
    ...
    UNIQUE KEY `userid` (`auth_rule_id`,`admin_id`) USING BTREE,
    ...
);

mysql中key 、primary key 、unique key 与index区别

MySQL Key值（PRI, UNI, MUL）的含义：

注：
1、如果对于一个列的定义，同时满足上述4种情况的多种，比如一个列既是PRI，又是UNI（如果是PRI，则一定是UNI）
那么"desc 表名"; 的时候，显示的Key值按照优先级来显示 PRI->UNI->MUL
那么此时，显示PRI。

2、如果某列不能含有空值，同时该表没有主键，则一个唯一性索引列可以显示为PRI，

3、如果多列构成了一个唯一性复合索引，那么一个唯一性索引列可以显示为MUL。（因为虽然索引的多列组合是唯一的，比如ID+NAME是唯一的，但是每一个单独的列依然可以有重复的值，因为只要ID+NAME是唯一的即可）

DROP TABLE IF EXISTS `hkcms_admin_panel`;
CREATE TABLE `hkcms_admin_panel` (
  `auth_rule_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '菜单ID',
  `admin_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
  UNIQUE KEY `userid` (`auth_rule_id`,`admin_id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='管理后台常用菜单';

一.MySQL的BTREE索引

CREATE TABLE act_info(
id BIGINT NOT NULL AUTO_INCREMENT,
act_id VARCHAR(50) NOT NULL COMMENT "活动id",
act_name VARCHAR(50) NOT NULL COMMENT "活动名称",
act_date datetime NOT NULL,
PRIMARY KEY(id),
KEY idx_actid_name(act_id,act_name) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ROW_FORMAT=COMPACT COMMENT "活动记录表";

mysql 索引中的USING BTREE 的意义
索引是在存储引擎中实现的，因此每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。

根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。

大多数存储引擎有更高的限制。MySQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；

MYISAM和InnoDB存储引擎只支持BTREE索引；MEMORY和HEAP存储引擎可以支持HASH和BTREE索引

B-tree索引是数据库中存取和查找文件(称为记录或键值)的一种方法.B-tree算法减少定位记录时所经历的中间过程,从而加快存取速度.

一个B-tree的典型例子就是硬盘中的结点.与内存相比,硬盘花成倍的时间来存取一个数据元素,这是硬盘的机械部件读写数据的速度远远赶不上纯电子媒体的内存.与一个结点两个分支的二元树相比,B-tree利用多个分支（称为子树）的结点,减少获取记录时所经历的结点数,从而达到节省存取时间的目的.

B-TREE索引的特点

B-TREEB-TREE以B+树结构存储数据，大大加快了数据的查询速度
B-TREE索引在范围查找的SQL语句中更加适合（顺序存储）

B-TREE索引使用场景

全值匹配的查询SQL，如 where act_id= '1111_act'
联合索引汇中匹配到最左前缀查询，如联合索引 KEY idx_actid_name(act_id,act_name) USING BTREE，只要条件中使用到了联合索引的第一列，就会用到该索引，但如果查询使用到的是联合索引的第二列act_name，该SQL则便无法使用到该联合索引（注：覆盖索引除外）
匹配模糊查询的前匹配，如where act_name like '11_act%'
匹配范围值的SQL查询，如where act_date > '9865123547215'（not in和<>无法使用索引）
覆盖索引的SQL查询，就是说select出来的字段都建立了索引

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

二.MySQL的BTREE索引-主键索引

1、MyISAM索引-主键索引

 CREATE TABLE `t_user_myisam` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(20) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE,
 KEY `idx_age` (`age`) USING BTREE
 ) ENGINE=MyISAM;

等值查询数据
select * from t_user_myisam where id=30;
1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）

2. 将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）

3. 检索到叶节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项。（1次磁盘IO）

4. 从索引项中获取磁盘地址，然后到数据文件t_user_myisam.MYD中获取对应整行记录。（1次磁盘IO）

5. 将记录返给客户端。

磁盘IO次数：3+1次。

 CREATE TABLE `t_user_innodb` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(20) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE,
 KEY `idx_age` (`age`) USING BTREE
 ) ENGINE=InnoDB;

2、InnoDB索引-主键索引

等值查询数据
select * from t_user_innodb where id=30;
1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）

2. 将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）

3. 检索到叶节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）

磁盘IO次数：3次。

3：辅助索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。以表t_user_innodb的age列为例，age索引的索引结果如下图。

4：组合索引

1. 组合索引存储结构

我们在使用索引时，组合索引是我们常用的索引类型。那组合索引是如何构建的，查找的时候又是如何进行查找的呢？

表t_multiple_index，id为主键列，创建了一个联合索引idx_abc(a,b,c)，构建的B+树索引结构如图所示。索引树中节点中的索引项按照（a，b，c）的顺序从大到小排列，先按照a列排序，a列相同时按照b 列排序，b列相同按照c列排序。在最地城的叶子节点中，如果两个索引项的a，b，c三列都相同，索引项按照主键id排序。

所以组合索引的最底层叶子节点中不存在完全相同的索引项。

 CREATE TABLE `t_multiple_index` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 `c` varchar(10) DEFAULT NULL,
 `d` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE, 8 KEY `idx_abc` (`a`,`b`,`c`)
) ENGINE=InnoDB;

4. 组合索引创建原则

1. 频繁出现在where条件中的列，建议创建组合索引。

2. 频繁出现在order by和group by语句中的列，建议按照顺序去创建组合索引。

order by a,b 需要组合索引列顺序（a,b）。如果索引的顺序是（b,a），是用不到索引的。

3. 常出现在select语句中的列，也建议创建组合索引。

对于第1种情况和第3种情况，组合索引创建的顺序对其来说是等价的，这种情况下组合索引中的顺序，是很重要的。由于组合索引会使用到最左前缀原则，使用频繁的列在创建索引时排在前面。

5：覆盖索引

前面我们提到，根据在辅助索引树查询数据时，首先通过辅助索引找到主键值，然后需要再根据主键值到主键索引中找到主键对应的数据。这个过程称为回表。

~~~~~~~~~~~~~~~~~~~~

mysql的索引有很多种，下面我们来介绍以下。

1.添加主键索引： primary key

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加唯一索引：

ALTER TABLE `table_name` ADD UNIQUE (`column` )

3.添加普通索引：

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加多列索引(组合索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

DROP TABLE IF EXISTS `hysc_credits_order_log`;
CREATE TABLE `hysc_credits_order_log` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`order_type` TINYINT(1) NOT NULL DEFAULT 1,
	`uid` INT(10) NOT NULL,  
	`orderno` VARCHAR(30) NOT NULL,
	`credits` INT(10) NOT NULL,
	`create_time` INT(10) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT='会员订单领取积分日志表';
-- 
-- indexes of hysc_credits_order_log
-- 
ALTER TABLE `hysc_credits_order_log` ADD INDEX uid(uid);
ALTER TABLE `hysc_credits_order_log` ADD INDEX orderno(orderno);
ALTER TABLE `hysc_credits_order_log` ADD INDEX create_time(create_time);


DROP TABLE IF EXISTS `hysc_pay_log`;
CREATE TABLE `hysc_pay_log` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`pay_type` VARCHAR(20) NOT NULL,
	`trade_type` VARCHAR(20) NOT NULL,
	`uid` INT(10) NOT NULL,
	`fans_id` INT(10) NOT NULL,
	`openid` VARCHAR(255) NOT NULL,
	`unionid` VARCHAR(255) NOT NULL,
	`tid` VARCHAR(30) NOT NULL,
	`fee` DECIMAL(10,2) NOT NULL,
	`tag` VARCHAR(2000) NOT NULL,
	`refund_tag` VARCHAR(2000) NOT NULL,
	`status` TINYINT(2) NOT NULL,
	`create_time` INT(10) NOT NULL,
	`pay_time` INT(10) NOT NULL,
	`refund_time` INT(10) NOT NULL,
	`remark` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT='支付日志表';
-- 
-- indexes of hysc_pay_log
-- 
ALTER TABLE `hysc_pay_log` ADD INDEX pay_type(pay_type);
ALTER TABLE `hysc_pay_log` ADD INDEX uid(uid);
ALTER TABLE `hysc_pay_log` ADD INDEX fans_id(fans_id);
ALTER TABLE `hysc_pay_log` ADD INDEX openid(openid);
ALTER TABLE `hysc_pay_log` ADD INDEX tid(tid);
ALTER TABLE `hysc_pay_log` ADD INDEX status(status);