mysql索引及其优化

索引的优点：

• 索引大大减少了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机I/O变为顺序I/O

索引的缺点：

• 增加写入成本（进行增删改操作时需要维护索引）
• 占用存储空间（索引本质上是额外的数据）

mysql索引类型：

• 主键索引：唯一且不能为空，mysql默认使用B+Tree作为索引存储结构
• 唯一索引：约束列的值必须唯一，但允许NULL
• 普通索引：仅用于提高查询速度，没有唯一性约束
• 全文索引：适用于全文搜索，如搜索引擎中的关键词匹配（仅限MyISAM和InnoDB）
• 组合索引（联合索引）：由多个列组合形成的索引；查询时必须使用索引的前导列，否则索引无法生效
• 哈希索引：适用于等值查询，不适用于范围查询；主要用于memory存储引擎

如何优化索引：

• 避免在索引列上使用函数（LEFT（name,3）='abc'无法命中索引）
• 索引列参与运算，会使索引失效
• 避免隐式类型转换（WHERE age='18'，age是INT，会导致全表扫描）
• 索引列不能用!=、<>，会导致全表扫描
• 避免LIKE'%关键字%'查询，可以使用全文索引
• 索引字段尽量短（字符串类型时，可以使用前缀索引）
• 使where后面的条件列按照联合索引的顺序
• 删除低效索引，避免影响增删改操作
• 范围条件右边的列索引失效 （确保范围列为联合索引的最后列）
• is null可以使用索引，is not null不能使用索引
• or前后存在非索引的列，索引失效（or前后的两个条件列都是索引时，索引才生效）
• 数据库和表的字符集要统一（否则索引失效）

对于单列索引，尽量选择针对当前查询过滤性更好的索引
在选择组合索引的时候，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引的时候，尽量选择能够包含当前查询中的where子句中更多字段的索引
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面

关于索引的测试sql
CREATE DATABASE test01;

USE test01;
-- 创建学生表和课程表
CREATE TABLE student_info(
id INT(11) NOT NULL AUTO_INCREMENT,
student_id INT NOT NULL,
name VARCHAR(20) DEFAULT NULL,
course_id INT NOT NULL ,
class_id INT(11) DEFAULT NULL,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
)ENGINE =INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE course(
id INT(11) NOT NULL AUTO_INCREMENT,
course_id INT NOT NULL ,
course_name VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (id)
)ENGINE =INNODB AUTO_INCREMENT=1 DEFAULT CHARSET =utf8;

SELECT @@log_bin_trust_function_creators;

SET GLOBAL log_bin_trust_function_creators=1;

-- 函数1:创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i<n DO
SET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i=i+1;
end while;
RETURN return_str;
END //
DELIMITER ;

-- 函数2:创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num(from_num INT,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i=FLOOR(from_num+RAND()*(to_num-from_num+1));
RETURN i;
end //
DELIMITER ;

-- 存储过程1:创建插入课程表存储过程
DELIMITER //
CREATE PROCEDURE insert_course(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit =0;#设置手动提交事务
REPEAT #循环
SET i=i+1;#赋值
INSERT INTO course(course_id, course_name) VALUES (rand_num(10000,10100),rand_string(6));
UNTIL i=max_num
END REPEAT;
COMMIT;#提交事务
END //
DELIMITER ;

-- 存储过程2:创建插入学生信息表存储过程
DELIMITER //
CREATE PROCEDURE insert_stu(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit =0;#设置手动提交事务
REPEAT #循环
SET i=i+1; #赋值
INSERT INTO student_info(course_id,class_id,student_id,NAME)
VALUES(rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));
UNTIL i=max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;

CALL insert_course(100);
CALL insert_stu(1000000);

---

适合创建索引的情况：

• 字段的数值有唯一性的限制
• 频繁作为WHERE查询条件的字段
• 经常GROUP BY和ORDER BY的列
• UPDATE、DELETE、的WHERE条件列
• DISTINCT字段需要创建索引
• 多表JOIN连接操作时，创建索引注意事项:
  1. 连接的表数量尽量不要超过三张;
  2. 对WHERE条件创建索引;
  3. 对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致;
• 使用列的类型小的创建索引
• 使用字符串前缀创建索引
• 区分度高（散列性高）的列适合作为索引
• 使用最频繁的列放到联合索引的左侧
• 在多个字段都要创建索引的情况下，联合索引优于单值索引

限制索引的数目：建议单张表索引数量不超过6个

不适合创建索引的情况:
在WHERE中使用不到的字段，不要设置索引
数据量小的表最好不要使用索引(少于1000条记录)
有大量重复数据的列上不要建立索引(当数据量重复度高于10%的时候)
避免对经常更新的表创建过多的索引
不建议用无序的值作为索引（如身份证，UUID）
删除不再使用或很少使用的索引
不要定义冗余或重复的索引