标签：name INT 查询索引 student SQL 优化 SELECT

1、数据准备

学员表插50w条，班级表插1w条

步骤1：建表

CREATE TABLE `class` (
   `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
   `className` VARCHAR(30) DEFAULT NULL,
   `address` VARCHAR(40) DEFAULT NULL,
   `monitor` INT NULL,
   PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `stuno` INT NOT NULL,
   `name` VARCHAR(20) DEFAULT NULL,
   `age` INT(3) DEFAULT NULL,
   `classId` INT(11) DEFAULT NULL,
   	PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步骤2：设置参数

命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1; #不加global只是当前窗口有效。

步骤3：创建函数

保证每条数据不同

 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 //
 DELIMETER;

随机产生班级编号

 #用于随机产生多少到多少的编号
 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;

步骤4：创建存储过程

创建往stu表中插入数据的存储过程

 #往stu表中插入数据的存储过程
 DELIMITER //
 CREATE PROCEDURE insert_stu(START INT, max_num INT)
 BEGIN
 DECLARE i INT DEFAULT 0;
 SET autocommit = 0; #设置手动提交事务
 REPEAT #循环
 SET i = i + 1; # 赋值
 INSERT INTO student(stuno, name, age, classId) VALUES ((START+i),rand_string(6), rand_num(1,50),rand_num(1,10000));
 UNTIL i=max_num
 END REPEAT;
 COMMIT; #提交事务
 END //
 DELIMITER;

创建往class表中插入数据的存储过程

 DELIMITER //
 CREATE PROCEDURE `insert_class` (max_num INT)
 BEGIN
 DECLARE i INT DEFAULT 0;
 SET autocommit = 0;
 REPEAT 
 SET i = i+1;
 INSERT INTO class (className, address, monitor) VALUES (rand_string(8), rand_string(10),rand_num(1, 100000));
 UNTIL i = max_num
 END REPEAT;
 COMMIT;
 END //
 DELIMITER;

步骤5：调用存储过程

class

#执行存储过程，往class表添加1万条数据
CALL insert_class(10000);

stu

#执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);

2、索引失效案例

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。
大多数情况下都（默认）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器?基于cost开销（CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer )，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

2.1 全值匹配我最爱

系统中经常出现的sql语句如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4 and name = 'abcd';

建立索引前执行：（关注执行时间）

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4 AND name='abcd';
Empty set, 1 warning(0.398 sec)

建立索引

 CREATE INDEX idx_age ON student(age);
 CREATE INDEX idx_age_classid ON student(age, classId);
 CREATE INDEX idx_age_classid_name ON student(age,classId,name);

建立索引后执行

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4 AND name='abcd';
Empty set, 1 warning(0.036 sec)

可以看到，创建索引前的查询时间是0.398秒，创建索引后的查询时间是0.036秒，索引帮助我们极大的提高了查询效率。

2.2 最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

举例1：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and name != 'abcd';

举例2：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classId=1 and name = 'abcd';

举例3：索引idx_age_classid_name还能否正常使用？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classId=4 and age=30 and name = 'abcd';

如果索引了多列，要遵守最左前缀法则。指的是查询从索引最左前列开始并且不跳过索引的列。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and name = 'abcd';

虽然可以正常使用，但是只有部分被使用到了。

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classId=1 and name = 'abcd';

完全没有使用上索引。

结论：MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第一个字段时，多列（或联合）索引不会被使用。

2.3 主键插入顺序

对于一个使用InnoDB存储引擎的表来说，在我们没有显示的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间：

如果此时再插入一条主键值为9的记录，那它插入的位置就如下图：

可这个数据页已经满了，再插进来咋办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗！所以如果我们想尽量避免这种无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入。

我们自定义的主键列id拥有AUTO_INCREMENT属性，在插入记录时存储引擎会自动为我们填入自增的主键值，这样的主键占用空间小，顺序写入，减少页分裂。

2.4 计算、函数、类型转换（自动或手动）导致索引失效

1、这两条sql哪种写法更好

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name, 3) = 'abc%';

2、创建索引

 create index idx_name on student(name);

3、第一种索引优化生效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

type为“range”，表示有使用到索引。

第二种：索引优化失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name, 3) = 'abc%';

type为“ALL”，表示没有使用到索引，查询效率比之前低很多。

再举例：

student表的字段stuno上设置有索引

CREATE INDEX idx_sno ON student(stuno);

索引优化失效：（假设：student表的字段stuno上设置有索引）

EXPLAIN SELECT SQL_NO_CACHE id, stuno, name FROM student WHERE stuno+1 = 900001;

运行结果：

你能看到如果对索引进行了表达式计算，索引就失效了。这是因为我们需要把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式，运行时间也会慢很多。

2.5 类型转换导致索引失效

下列哪个sql语句可以用到索引（假设name字段上设置有索引）

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 123;

# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

结论：设计实体类属性时，一定要与数据库字段类型相对应。否则，就会出现类型转换的情况。

2.6 待续。。。。。。

标签：name,INT,查询,索引,student,SQL,优化,SELECT
From： https://www.cnblogs.com/aluna/p/16742384.html

索引优化与查询优化