目录
②创建唯一索引，在百万记录中取1000条记录,耗时0.07s
一、索引对DML语句效率的影响
在数据库所占的CPU资源中，其中有90%是解析和执行各类SQL语句所耗费的，因此提高SQL语句的执行效率对于高并发数据库应用程序至关重要。
在众多SQL语句中，select查询语句是用途最广泛的。加快查询速度的最有效手段是使用索引，当单个表数据量增大时，MySQL还提供表分区等手段，其核心思想是降低数据库磁盘的I/O开销。通过降低从磁盘读取的数据量来加快查询速度。
1、实验准备
当表的数据量很大时，使用索引的优势会非常明显。
①创建数据库
create database test;
use test;
create table t(id int, name varchar(30));②创建用于向表中插入数据的存储过程proc1()
delimiter $$
#存储过程命名为proc1，参数cnt为准备插入数据行的数量，调用是输入该值
create procedure proc1(cnt int)
begin
#定义变量i为整型，默认值为1
declare i int default 1;
#开启事务
start transaction;
#mysql repeat循环结构
repeat
#插入字段
insert into test.t(id,name) values(i,concat('a',i));
#变量自增1i
set i=i+1;
until i>cnt end repeat;
#提交事务
commit;
end$$
delimiter;③调用存储过程proc1，设置插入1000000条测试记录
call proc1(1000000);④查看表t中的记录数和前5条数据
select count(*) from t;
select * from t limit 5; 
2、explain语句
使用explain语句可以查看mysql服务器执行SQL语句的相关信息。
explain select * from test.t; 
| 字段 | 描述 |
| id | SQL语句标识符。如果仅包含一个表的简单查询，id始终为1；如果是多表连接查询，id 按序递增 |
| select_type | 查询类型。如 SIMPLE 表示简单查询，不包括子查询和UNION 运算符 |
| table | 对应的表名 |
| partitions | 被分配到的分区，非分区表始终为NULL |
| type | 表对象间的连接类型。如果值为ALL，表示使用低效的全表扫描方式；红果值为CONST，表示仅有一行匹配的结果记录；如果值为RANGE，表示使用索引范围查找 |
| possible_keys | 执行时可能用到的索引名 |
| key | 执行时实际用到的索引名 |
| key_len | 执行时使用索引的长度 |
| ref | 与索引对照的字段 |
| rows | 涉及的行记录数。在 ImnoDB 存储引擎的表中，该数值为估算值而不是实际值 |
| filtered | 返回结果的行记录数占执行中所有读取到的行记录数的百分比。在 InmnDB存储引擎的表中，该数值为估算值而不是实际值，最高为100 |
| Extra | 相关补充信息 |
3、百万记录取一条
①查询id=1500的记录，使用时间为1.23s
select * from t where id=1500;
②查看执行select语句的相关信息
explain select * from t where id=1500;
③为id字段创建索引，并查看
create unique index idx_id on t(id);
show index from t\G;
④查询id=1500的记录，使用时间为0.05s
⑤查看执行select语句的相关信息
4、百万记录取1000条
①无索引取1000条记录，耗时1.23s
select * from t where id between 1001 and 2000;
②创建唯一索引，在百万记录中取1000条记录,耗时0.07s
create unique index idx_id on t(id);
select * from t where id between 1001 and 2000;
explain select * from t where id between 1001 and 2000;
5、百万记录取全部，耗时1.4s
select * from t;建立索引后，耗时1.49s。
当返回记录数相对总记录数较大时，查询条件字段使用索引不能起到优化查询的作用。
6、百万记录更新一条数据
update t set name='china' where id=1;
建立索引，并更新一条记录，耗时0.11s
当update语句使用索引时，从百万记录中更新较少记录时，update语句可以借助索引快速定位。
7、总结
从以上测试可以得出：
索引如同一把双刃剑，其主要优点是当返回记录数少时，可以是select语句查询速度大幅提升，通过索引定位更新少量数据时也是如此。
然而，索引也有副作用，除了占用额外的磁盘空间外，索引通常会使数据表的增删改操作变慢。因此，在为常用查询条件创建索引时，要找出常用的查询条件字段，如果这些条件字段返回的记录数控制在总记录的3%~5%，就可以在这些字段上创建单列索引或多列索引，一定要把多列索引中筛选数据最多的字段放到索引创建语句的最左边。
二、SQL语句优化
1、避免使用表达式作为查询条件
select * from t where id-1<5; #采用这种方式，索引会失效，优化器会使用全表扫描
select * from t where id<5-1; 
2、尽量使用in代替or
3、条件列表值连续时使用between代替in
4、有条件使用where子句替代having子句
where子句和having子句都对记录起到过滤的作用，主要区别在于having子句是针对group by子句产生的结果，而where子句先于having子句运行，主要目的是缩减查询结果集的记录数。
5、使用like时通配符要放在右侧
select *from t where name like 'a150_';通配符放在左侧，索引会失效。
三、表分区对查询效率的影响
优化SQL语句除了建立索引以外，还可以利用MySQL提供的表分区技术。表分区可以将一个表按照一列所存储数据的特性，将一个表水平分为多个存储单元，这些独立的存储单元被称为表段。
例如，在性别字段中，可以将‘男性’和 ‘女性’ 人员记录分别存放在不同的区中。当查询条件为‘男性’ 时，不考虑索引，MySQL服务器仅将所有放在‘男性’ 区中的表页装载到内存中，这样I/O负载就降低了一半，查询效率就会提高。
1、创建分区表
①按照表 t 的结构创建表 t1。
create table t1(id int, name varchar(30))
partition by range(id)(
partition p1 values less than (200000),
partition p2 values less than (400000),
partition p3 values less than (600000),
partition p4 values less than (800000),
partition p5 values less than (1000000),
partition p6 values less than (maxvalue)
);②以表t作为数据，导入t1
insert into t1 select * from t;2、普通表与分区表查询效率对比
①
select * from t where id=1500;
select * from t1 where id=1500;
没有建立索引，都采用全表扫描的方法，分区表查询分区后，扫描的数据变少，所以时间短。
②
select * from t where name='a1500';
select * from t1 where name='a1500';
由以上测试可知，如果查询字段不包括分区字段，分区表会扫描所有分区的表页，耗时高于普通表。因此，在使用分区表时，一定要在where子句的查询条件中添加分区字段。
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
