8. MySQL - 存储过程-触发器-执行计划 - 索引

目录

• 1. 储存过程
  • 创建无参存储过程
  • 创建有参存储过程
  • 查看/删除储存过程
• 2.触发器
  • 创建触发器
  • 查看/删除触发器
• 3. 执行计划 - explain
  • type
    • 1. all
    • 2. index
    • 3. range
    • 4. ref
    • 5. eq_ref
    • 6. const,system
  • key_len
  • extra
    • 1. Using where
    • 2. Using index
    • 3. Using index condition
    • 4. Using temporary
    • 5. Using filesort
• 4. 索引 - index
  • innodb表是如何组织数据的
  • 索引的管理
  • 常见索引类型
    • 1. 聚簇索引
    • 2. 前缀索引
    • 3.联合索引
    • 4. 覆盖索引
    • 5. 回表查询
    • 冗余和重复索引
  • 索引效果压力测试
  • 索引无法命中的情况

1. 储存过程

参考网址;https://www.cnblogs.com/Neeo/articles/13727314.html

存储过程就是保存在MySQL上的一个"别名"——封装SQL语句集。通过整个别名来调用封装好的语句集，相当方便。

创建无参存储过程

-- 创建无参存储过程

delimiter //  -- 修改mysql语句结束符
create procedure p1() -- 声明存储过程，存储过程名称是 p1，无参
begin		-- 开始标识符
-- 过程体,这里开始写SQL，当调用存储过程的时候，这里的所有SQL都会执行
select * from user where id = 2; 
end //     -- 结束标识符
delimiter ;

-- 在终端中使用 call 调用存储过程
call p1()

创建有参存储过程

存储过程允许传参，通常有三种传参方式：

• in -- 可以被过程体引用，但不能被修改, 不能返回
• out -- 不可以可以被过程体引用，但参数可以被修改, 可以返回
• inout -- 即可以被过程体引用,参数也可以被修改, 可以返回

-- 1.参数in声明存储过程

    delimiter //  -- 修改mysql语句结束符
    create procedure p1(in n1 int,in n2 int) -- 传两个参数
    begin		-- 开始标识符
    -- 过程体,这里开始写SQL，当调用存储过程的时候，这里的所有SQL都会执行
    select n1 + n2; 
    end //     -- 结束标识符
    delimiter ;

    call p1(1,5);  -- 调用

-- 2.参数out声明存储过程

    delimiter //  -- 修改mysql语句结束符
    create procedure p2(out n1 int,out n2 int) -- 传两个参数
    begin		-- 开始标识符
    -- 存储过程执行时，首先修改 n1,n2 的值，然后才能被过程体使用
    set n1 = 1;
    set n2 = 2;
    select n1 + n2; 
    end //     -- 结束标识符
    delimiter ;

    set @n1=10;   -- 定义局部变量
    set @n2=20;
    call p2(@n1,@n2);  -- 需要传入变量才能调用  
    select @n1;   -- 查看局部变量会被修改成 n1=1,n2=2
    select @n2;

-- 3.参数inout声明存储过程
    delimiter //  -- 修改mysql语句结束符
    create procedure p3(inout n1 int) -- 传inout参数
    begin		-- 开始标识符
    -- 存储过程执行时，可以选择是否修改 n1 的值，再被过程体使用
    -- set n1 = 1;  
    select n1; 
    end //     -- 结束标识符
    delimiter ;
    
    set @n1=10;
    call p3(@n1) -- 需要传入变量才能调用

查看/删除储存过程

-- 查询MySQL中所有的存储过程 - mysql下的proc表
select db,name from mysql.proc;
-- 查询指定数据库下的所有存储过程
select name from mysql.proc where db='db1';
show procedure status where db='db1';
-- 查询指定存储过程的创建信息
show create procedure p1; -- p1储存过程名称

-- 删除存储过程
drop procedure p1; 

2.触发器

参考网址: https://www.cnblogs.com/Neeo/articles/13677324.html

触发器是与表有关的数据库对象，在满足特定的条件触发，并执行触发器中定义的语句集。

说白了，触发器就像一个牛皮糖，依附于某个表上，当表的行记录有增/删/改的操作时，可以触发触发器内提前写好的语句集的执行。

注意，查询时没有触发器的操作。

创建触发器的四大要素

1. 监视谁：table
2. 监视什么事件：表中记录执行insert/update/delete前后
3. 触发条件：after/before
4. 要触发什么事件：insert/update/delete

另外，还需要注意触发频率：针对每一行记录的操作都会触发触发器的执行。

还有：触发器无法与临时表或视图关联。

在触发器中，还有NEW和OLD语句可用：

触发器类型	NEW和OLD的使用	备注
INSERT型触发器	NEW表示将要或者已经新增的数据	没有OLD
UPDATE型触发器	OLD表示原数据；NEW表示修改后的数据
DELETE型触发器	OLD表示将要或者已经删除的数据	没有NEW

创建触发器

基本语法：

create trigger 触发器名 [before|after] 触发事件
on 表名 for each row      	-- for each row:基于每一行记录变动而触发
begin	-- 表示被触发的事件开始
-- 要执行的语句
end		-- 表示被触发的事件结束

创建表结构:

-- 用户表
CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(32) NOT NULL
)ENGINE=INNODB CHARSET=utf8;

-- 日志表
CREATE TABLE t_log(
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(32) NOT NULL,
t_log_type VARCHAR(32) NOT NULL,
t_log_time DATETIME
)ENGINE=INNODB CHARSET=utf8;

创建触发器:

-- 创建触发器，每当user表插入一条数据，就往log表写入2条记录：
-- 插入前执行触发器
delimiter //
create trigger tg1 before insert
on t_user for each row
begin
insert into t_log(t_log,t_log_type,t_log_time) values(new.t_name,'before insert',now());
end //
delimiter ;

-- 插入前执行触发器
delimiter //
create trigger tg2 after insert
on t_user for each row
begin
insert into t_log(t_log,t_log_type,t_log_time) values(new.t_name,'after insert',now());
end //
delimiter ;

-- 测试: 往user表插入一条数据，在查看下log表
insert into t_user(t_name) values('jyh');
select * from t_log;

查看/删除触发器

-- 查看我们创建的触发器
show treggers \G;

-- 删除触发器:
drop trigger 触发器名;
drop trigger tg1;

3. 执行计划 - explain

参考网址: https://www.cnblogs.com/Neeo/articles/13644285.html

研究执行计划，通过执行计划可以了解MySQL选择了什么执行计划来执行SQL，并且SQL的执行过程到此结束，即并不会真正的往下交给执行器去执行；最终的目的还是优化MySQL的性能。

我们通过EXPLAIN语句来查看查看MySQL如何执行语句的信息；EXPLAIN语句可以查看SELECT、DELETE、INSERT、REPLACT和UPDATE语句。

这里重点关注查询时的执行计划。

-- 基本语法:
explain sql语句:

-- 示例: 
explain select name from t1 where id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

返回中的重要参数：

字段	描述	备注
id	该SELECT标识符
select_type	该SELECT类型
table	输出结果的表
partitions	匹配的分区
type	表的连接类型	需要了解的
possible_keys	查询时可能的索引选择	只是有可能选择的索引，但是也能最后选择的索引不在该字段中
key	实际选择的索引	需要重点了解的
key_len	所选KEY的长度
ref	列与索引的比较
rows	表示MySQL认为执行查询必须检查的行数	innodb中是个估算值
filtered	按表条件过滤的行百分比
Extra	执行情况的附加信息	需要重点了解的

type

type 的官方全称是join type，意思是"连接类型"，这容易让人误会是联表的连接类型，其实不然，这里的join type事实上是数据库引擎查找表的一种方式,

举常见联接类型，性能从最佳到最差排序:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

1. all

all便是所谓的全表扫描了，如果出现了all类型，通常意味着你的SQL处于一种最原始的状态，还有很大的优化空间！

我们来看常见出现all的情况:

# 使用world官方数据
1.查询条件字段是非索引字段
	explain select * from city where district='shanghai';
2.查询条件中，包含 !=、not in、like。
  - 注意，以下情况适用于辅助索引
  explain select * from city where countrycode not in ('CHN','USA');
  explain select * from city where countrycode != 'CHN';
 
  - 而聚集索引来说，还是会走索引
  explain select * from city where id != 1;  -- type = range

  - 而针对于like情况，% 放在首位不会走索引，放在后面会走索引
  explain select * from city where countrycode like 'CH%';  -- type = range
  explain select * from city where countrycode like '%HN';  -- ALL

2. index

​ index是另一种形式上的all类型，只不过index是全索引扫描(但索引也是建立在全表记录上的),为什么比all快,因为索引是有序的

explain select * from city order by id; -- type=index  id是主键
explain select * from city order by population; -- type=ALL  普通字段

3. range

range是基于索引的范围扫描，包含>,<,>=,<=,!=,like,in，not in,or,!=，not in的情况会走range

出现range的条件是：

​ 查询条件列是非PRIMARY KEY和UNIUQE KEY的索引列，也就是说条件列使用了索引，但该索引列的值并不是唯一的，这样的话，即使很快的找到了第一条数据，但仍然不能停止的在指定的范围内继续找。

range的优点是不需要扫描全表，因为索引是有序的，即使有重复值，但也被限定在指定的范围内。

-- 首先为 population 字段建立一个普通索引，现在 population 和 countrycode 是普通索引，而 id 是主键
alter table city add index idx1(population);

-- 示例
explain select * from city where population < 10000; -- 走索引的范围查找
explain select * from city where countrycode like 'CH%'; --因为索引是有序的，也走索引的范围查找

4. ref

ref出现的条件是：

​ 查找条件列使用了索引但不是PRIMARY KEY和UNIQUE KEY。其意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

explain select * from city where countrycode = 'CHN';  -- 索引值CHN有重复

5. eq_ref

在多表连接时，连接条件(ON)使用了唯一索引(UNIQUE NOT NULL,PRIMARY KEY)时，走eq_ref

-- 查询世界上人口小于100人的城市名字
explain select city.name from city inner join country on city.countrycode = country.code where city.population < 100;

6. const,system

首先，system是最优的，它的意思是表只有一行，是const类型的一种特例,

const表示：

​ 该表最多具有一个匹配行，该行在查询开始时读取, 我们将const看作是最快的，因为只有一行匹配结果。

explain select * from city where id = 10;  -- type=const

key_len

执行计划中的key_len字段计算的是使用到的索引的长度，通过ken_len可以帮助我们进一步确定索引的使用情况.

列出我们最常用的int、char、varchar三种数据类型，先来复习一些基础知识：

char和varchar跟字符编码也有密切的联系，latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。

不同字符编码占用的存储空间不同：

char(4) -- 固定长度,储存字节 -- latin1占用4个字节，gbk占用8个字节，utf8占用12个字节

varchar(4) -- 变长,根据实际长度计算, 占用字节数 + 1~2 -- 1~2是储存varchar的自身长度占用的字节

接下来，实验开始:

-- 创建表结构如下
create table k1(
	id int not null primary key auto_increment,
	a int,
	b int not null,
	c char(10),
	d char(10) not null,
	e varchar(10),
	f varchar(10) not null
)engine=innodb charset=utf8;

-- 创建索引
alter table k1 add index(a);
alter table k1 add index(b);
alter table k1 add index(c);
alter table k1 add index(d);
alter table k1 add index(e);
alter table k1 add index(f);


-- key_len的长度是5，对于字段a来说，int类型的索引本身占4个字节，且a字段又允许为空，所以再加1个字节的存储标志位，是否允许为空，加一起，正好是5个字节
explain select * from k1 where a=1;

-- 字段c是char类型，1个字符用3个字节表示，char(10) * 3 = 30字节，且c字段又允许为空，所以再加1个字节的存储标志位，是否允许为空，加一起，正好是31个字节
explain select * from k1 where c='a';

-- 字段e是varchar类型，varchar类型需要额外的1~2个字节存储字符本身的长度，这里取2。是否允许为空又占1个字节，所以是 10 * 3 + 2 + 1 = 33
explain select * from k1 where e='a';

extra

EXPLAIN输出的extra列包含有关MySQL如何解析查询的其他信息，帮助我们进一步了解执行计划的细节信息

1. Using where

-- 1.当WHERE条件是索引列时 > <  != ...
explain select * from city where id < 10;

-- 2.当WHERE条件是非索引列时
explain select * from city where district='shanghai';

通过几个示例，我们也能看到，当Extra栏出现Using where时，可以参考type栏做适当优化：

1. 当type栏是ALL时，说明SQL语句有很大的优化空间。
2. 当Extra栏是Using index时，参考type栏的情况，可以看看是不是建个索引。
3. 如果出现回表查询情况，尽量避免使用select *，而是选择返回指定字段，然后为指定字段尝试是否可以建个联合索引。

2. Using index

当Extra栏出现Using index表示使用覆盖索引进行数据返回，没有回表查询

explain select countrycode from city where countrycode = 'CHN';

-- 本次就出现了回表查询的情况，因为有的字段不在索引中。最后不要使用*
explain select * from city where countrycode = 'CHN';

3. Using index condition

当Extra栏出现Using index condition时，表示先使用索引条件过滤数据，然后根据其他子句进行回表查询操作

-- 首先为population字段建立一个索引, name不是覆盖索引,索引进行了回表查询
alter table city add index idx1(population); 
explain select name,population from city where population < 100;

4. Using temporary

当Extra栏出现Using temporary时，表示MySQL需要创建临时表来保存临时结果；如果查询包含不同列的group by和order by子句时，通常会需要临时表

5. Using filesort

这个filesort并不是说通过磁盘文件进行排序，而是告诉我们在查询时进行了一个排序操作，即在查询优化器所给出的执行计划中被称为文件排序,

1. 双路排序：首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后sort buffer中进行排序，排序后在把查询字段依照行指针取出，共执行两次磁盘I/O。
   • 双路排序由于有大量的随机I/O，效率不高，但是节约内存；排序使用快排(quick sort)算法，但是如果内存不够则会使用块排序(block sort)，将排序结果写入磁盘，然后再将结果合并。
2. 单路排序：一次性取出满足条件行的所有字段，然后在sort buffer中进行排序，执行一次磁盘你I/O；当然，遇到内存不足时仍然会使用临时文件。

挺复杂,看网站博客

4. 索引 - index

参考网址: https://www.cnblogs.com/Neeo/articles/13602317.html

学习环境 : mysql5.7 + innodb

掌握：

• innodb表是如何组织数据的？
  • B - Tree (B树)
  • B + Tree (B+树)
  • MySQL 的B + Tree
• 关于索引的管理
  • 创建删除
• 常见的索引类型
  • 聚簇索引、二级索引......
• 通过结合explain来分析索引的应用情况
• 覆盖索引、索引下推、回表查询
• 无法命中索引的一些情况，关于索引相关的优化

innodb表是如何组织数据的

B - Tree (B树) :

​ 叶子节点存储了完整的记录，但没有存储了左右节点的指针, 不方便查找

B + Tree (B+树)

​ 叶子节点存储了完整的记录，且存储了右节点的指针,

MySQL 的B + Tree

​ 相对于B+树来说，MySQL的B+树的叶子节点存储了完整的记录，且存储了左右节点的指针, 方便查找

索引的管理

MySQL提供了三种创建索引的方法:

-- 1.create index 语句创建索引，为已经存在的表，创建索引
create index 索引名 on 表名(字段名)

-- 2.create table语句创建索引，在建表时同时指定索引
create table t1(
	id int,
	name char(32),
    index idx1(name) -- 给name字段创建索引,索引名idx1
);

-- 3.alter table 语句创建(修改)语句，为已经存在的表，创建索引
alter table 表名 add index 索引名(字段名) -- 基本语法
alter table t1 add index dex_jia(name)

查看/删除索引:

-- 删除索引
alter table 表名 drop index 索引名称;
drop index 索引名称 on 表名;
	alter table t1 drop index idx1;

-- 查看索引
show index from 表名;

常见索引类型

1. 聚簇索引

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，也是一类索引的统称。

说白了，一张InnoDB表的数据都被组织在聚簇索引这棵特殊B+树上。那到底是如何组织的呢？在创建一张InnoDB的表时，如果没有指定主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式的创建一个6字节的主键来作为聚簇索引，然后后续插入的数据都被组织在这棵聚簇索引树上。

非聚簇索引也叫做辅助索引、二级索引等等，又根据索引的不同特点，也有了更多的称呼

聚簇索引和辅助索引构成区别:

1. 聚簇索引只能有一个(主键)，非空且唯一
2. 辅助索引可以有多个，主要配合聚集索引使用的
3. 聚簇索引的叶子节点，就是磁盘数据行存储的数据页
4. MySQL是根据聚簇索引来组织数据，即数据存储时，就是按照聚簇索引的顺序进行存储数据
5. 辅助索引只会提取索引键值，进行自动排序生成B树

2. 前缀索引

要对一个字符列很长的字段，建立索引，通常会让索引变得大而且慢，而解决办法可以仅对字符的开始部分建立索引，这样节省了索引空间，也提高了索引效率，对于这类索引，通常称为——前缀索引。

数据准备: pt.py 通过python录入10万数据

import re
# Faker 随机值,有很多函数, pip install faker
import faker
import random
import pymysql
from pymysql.connections import CLIENT

conn = pymysql.Connect(
    host='192.168.189.135', user='root', password='123',
    database='idb', charset='utf8', client_flag=CLIENT.MULTI_STATEMENTS)
cursor = conn.cursor()
fk = faker.Faker(locale='en_US')


"""
123@qq.com  p_email
qq.com@123	s_email

"""

def create_table():
    """ 创建库和表 """
    sql = """
    DROP TABLE IF EXISTS pt;
    CREATE TABLE pt (
      id int(11) NOT NULL AUTO_INCREMENT,
      text varchar(255) DEFAULT NULL,
      p_email varchar(64) DEFAULT NULL,
      s_email varchar(64) DEFAULT NULL,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    """
    # 注意，一次性执行多行sql，必须在连接时，指定client_flag=CLIENT.MULTI_STATEMENTS
    cursor.execute(sql)
    conn.commit()


def gen(num, tmp_list):
    for i in range(num):
        tmp = fk.paragraph() if random.randint(0, 1) else tmp_list[random.randint(0, (tmp_list.__len__() - 1))]
        email = fk.email()
        yield tmp, email, ''.join(reversed(re.split('(@)', email)))


def insert_many(num):
    """ 批量插入 """
    tmp_list = [fk.paragraph() for i in range(100)]
    sql = "insert into pt(text, p_email, s_email) values(%s, %s, %s);"
    cursor.executemany(sql, gen(num, tmp_list))
    conn.commit()


if __name__ == '__main__':
    num = 100000
    create_table()
    insert_many(num)

为了解决前缀的合适长度，我们一般通过简单的公式来测试，最终筛选出前缀的选择性接近完整列的选择性

-- 给字段text选择索引适合长度
select count(distinct text)/count(*) as '最佳选择性' from pt;

--有了公式，我们就可以来计算尝试计算出一个合适的索引长度
select 
count(distinct text)/count(*) as '最佳选择性',
count(distinct left(text,20))/count(*) as len20,
count(distinct left(text,22))/count(*) as len22,
count(distinct left(text,24))/count(*) as len24,
count(distinct left(text,26))/count(*) as len26,
count(distinct left(text,28))/count(*) as len28
from pt;
-- 一点一点试出符合'最佳选择性'的长度 - 24


-- 建立索引
alter table pt add index idx(text(24));
-- 查看索引
show index from pt;

-- 应用上索引
explain select * from pt where text like "you%";

后缀索引

​ 有时候，后缀索引(suffix index)，或者说是反向索引也很用，例如找到某个域名的所有email。而MySQL原生并不支持后缀索引(suffix index)，但可以通过字符串"反转"的方式，如将ryanlove@yahoo.com"反转"为yahoo.com@ryanlove，然后建立前缀索引达到加速查询的目的。

3.联合索引

MySQL可以创建复合索引，即同时为多个列创建索引，一个复合索引最多可以包含16列。

当然，我们通常更习惯将复合索引叫做联合索引或组合索引，当多个索引列具有唯一性时，你可以称它为联合唯一索引。

最左原则

在联合索引(a,b,c)中，需要重点掌握——最左(前缀匹配)原则,

• 创建了a列索引、ab列索引、ac列索引(这个要是情况而定)。因为这个几个索引都有a开头，也就是向左具有聚集性，所以被称为最左前缀。
• 按道理说，因为b列、bc列、ba列、ca列、cb列不符合最左原则，所以无法应用联合索引。但事实上，MySQL的优化器会在某些情况下，调整where条件的先后顺序，以尽可能的应用上索引，如ca列where c=1 and a=1，就会被优化器调整为where a=1 and c=1，

我们在创建联合索引时，一定要将最常被查询的列放在联合索引的最左侧。

但联合索引中的索引列是表的所有列的话，最左原则就不灵了

索引截断

在联合索引中，where条件遇到大于、小于时，会阻断后续条件的索引的使用，但等于不会，这点是需要我们注意的。

-- 通过 key_len 的结果发现，只有a、b两个索引列被应用，c索引列被阻断
explain select * from t1 where a=1 and b>2 and c=3;

-- 通过 key_len 的结果发现，a、b、c三个索引列都没有被应用
explain select * from t1 where a>1 and b=2 and c=3;

4. 覆盖索引

如果一个索引包含或者说覆盖所有要查询的字段的值，我们就称之为"覆盖索引(也称索引覆盖)"

当发起一个被索引覆盖的查询(也叫做索引覆盖查询)时，再explain的extra字段，会看到"Using index"的信息。

sql查询尽量不要使用select *而是建议使用select id等指定字段，这就是希望能走覆盖索引，进一步提高查询语句的性能。

5. 回表查询

索引的叶子节点中已经包含了要查询的数据，就会走覆盖索引，否则就需要进行回表查询了。

即一个索引不能包含或者说覆盖所有要查询的字段的值,就要进行回表查询

冗余和重复索引

重复索引指在相同列上按照相同的方式创建的相同类型的索引，对于这样的重复索引，应该及时进行移除。

冗余索引

冗余索引和重复索引有些不同

alter table tb add index com_idx(col1,col2);
alter table tb add index idx_1(col1);
alter table tb add index idx_2(col2,col1);

如果创建了联合索引com_idx，再创键idx_1，那么idx_1就是冗余索引，因为idx_1相当于com_idx索引的前缀索引。

而idx_2则不是冗余索引，因为col2列不是com_idx的最左前缀列。

索引效果压力测试

准备数据:

-- MYSQL version 5.7.20
-- 创建库
DROP DATABASE IF EXISTS idb;
CREATE DATABASE idb CHARSET=utf8mb4 collate utf8mb4_bin;
USE idb;

-- 创建表
DROP TABLE IF EXISTS pressure;
CREATE TABLE pressure(
id INT,
num INT,
k1 CHAR(2),
k2 CHAR(4),
dt TIMESTAMP
);

-- 创建储存过程
DELIMITER //
CREATE PROCEDURE rand_data(IN num INT)
BEGIN
DECLARE str CHAR(62) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE str2 CHAR(2);
DECLARE str4 CHAR(4);
DECLARE i INT DEFAULT 0;
WHILE i<num DO
SET str2=CONCAT(SUBSTRING(str,1+FLOOR(RAND()*61),1),SUBSTRING(str,1+FLOOR(RAND()*61),1));
SET str4=CONCAT(SUBSTRING(str,1+FLOOR(RAND()*61),2),SUBSTRING(str,1+FLOOR(RAND()*61),2));
SET i=i+1;
INSERT INTO pressure VALUES (i,FLOOR(RAND()*num),str2,str4,NOW());
END WHILE;
END //
DELIMITER ;

-- 调用存储过程
call rand_data(1000000);
commit;

执行索引压测:

没有创建索引压测

# 压测语句 -- 没有创建索引的时候
[root@cs home]# mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 \
--iterations=1 \
--create-schema='idb' \
--query="select * from idb.pressure where k1='oa';" \
--engine=innodb \
--number-of-queries=2000 \
-uroot -p123 -verbose

# 各项参数
--defaults-file：默认的配置文件。
--concurrency：并发用户数。
--iterations：要运行这些测试多少次。
--create-schema：被压测数据库名。
--query：执行压力测试时的SQL语句。
--number-of-queries：SQL语句执行次数。
相当于，模拟100个用户并发，每个用户执行20次查询操作。


# 压测结果 -- 大约需要330秒 
Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 330.667 seconds
	Minimum number of seconds to run all queries: 330.667 seconds
	Maximum number of seconds to run all queries: 330.667 seconds
	Number of clients running queries: 100
	Average number of queries per client: 20

创建索引,再执行压测

alter table pressure add index k1_idx(k1);

# 有索引压测
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 \
--iterations=1 \
--create-schema='idb' \
--query="select * from idb.pressure where k1='oa';" \
--engine=innodb \
--number-of-queries=2000 \
-uroot -p123 -verbose

# 压测结果 -- 大约2.6秒就完事了,充分证明了索引的作用
Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 2.629 seconds
	Minimum number of seconds to run all queries: 2.629 seconds
	Maximum number of seconds to run all queries: 2.629 seconds
	Number of clients running queries: 100
	Average number of queries per client: 20

索引无法命中的情况

准备数据user.py,通过python录入100万数据

import time
import faker
import pymysql
from pymysql.connections import CLIENT

fk = faker.Faker(locale='zh_CN')

conn = pymysql.Connect(
    host='192.168.189.135', user='root', password='123',
    database='idb', charset='utf8', client_flag=CLIENT.MULTI_STATEMENTS)
cursor = conn.cursor()

def create_table():
    """ 创建表 """
    sql = """
    DROP TABLE IF EXISTS user;
    CREATE TABLE user(
      id int not null primary key unique auto_increment,
      name varchar(32) not null default "张开",
      addr varchar(128) not null default "",
      phone varchar(32) not null,
      email varchar(64) not null 
    ) ENGINE=INNODB CHARSET=utf8;
    """
    # 注意，一次性执行多行sql，必须在连接时，指定client_flag=CLIENT.MULTI_STATEMENTS
    cursor.execute(sql)
    conn.commit()

def insert_many(num):
    """ 批量插入 """
    gen = ((i, fk.name(), fk.address(), fk.phone_number(), fk.email()) for i in range(1, num + 1))
    sql = "insert into user(id, name, addr, phone, email) values(%s, %s, %s, %s, %s);"
    cursor.executemany(sql, gen)
    conn.commit()

if __name__ == '__main__':
    num = 1000000
    create_table()
    insert_many(num)
    cursor.close()
    conn.close()

类型不一致

如果要查询的列是字符串类型，那么查询条件也必须是字符串，否则无法命中：

-- 创建辅助索引
alter table user add index idx_phone(phone);

-- 这里的phone字段类型是varchar
-- 查询条件是字符串，走索引
explain select * from user where phone='123';
--如果是整型，不走索引
explain select * from user where phone=123;

or

当where条件中，or两边都是索引列,走索引, 否则不走索引

-- 这里的id是主键索引,phone是辅助索引
explain select * from user where id=2 or phone='123';
-- 这里的id是主键索引,name是非索引列
explain select * from user where id=2 or name='贾英贺';

对于辅助索引来说，!=、not in 也不走索引, 覆盖索引,主键索引无效

-- 辅助索引
explain select * from user where phone not in ("15592925142", "13449332638", "18257778732");
-- 覆盖索引,走索引
explain select phone from user where phone not in ("15592925142", "13449332638", "18257778732");
-- 主键索引,走索引
explain select phone from user where id not in (1,2,3,4);

在索引列上，使用函数及运算操作(+、-、*、/等)，不走索引

-- 由于有运算，不走索引
explain select phone from user where id+1 = 3;

-- 但把运算放到值上，就可以走索引
explain select phone from user where id= 3+1;

对于like来说，%开头，不走索引,覆盖索引除外

explain select * from user where phone like '%452';
explain select phone from user where phone like '%452';