一)索引的作用
索引通俗来讲就相当于书的目录,当我们根据条件查询的时候,没有索引,便需要全表扫描,数据量少还可以,一旦数据量超过百万甚至千万,一条查询sql执行往往需要几十秒甚至更多,5秒以上就已经让人难以忍受了。
提升查询速度的方向一是提升硬件(内存、cpu、硬盘),二是在软件上优化(加索引、优化sql;优化sql不在本文阐述范围之内)。
能在软件上解决的,就不在硬件上解决,毕竟硬件提升代码昂贵,性价比太低。代价小且行之有效的解决方法就是合理的加索引。
索引使用得当,能使查询速度提升上万倍,效果惊人。
(二)mysql的索引类型:
mysql的索引有5种:主键索引、普通索引、唯一索引、全文索引、聚合索引(多列索引)。
唯一索引和全文索引用的很少,我们主要关注主键索引、普通索引和聚合索引。
1)主键索引:主键索引是加在主键上的索引,设置主键(primary key)的时候,mysql会自动创建主键索引;
2)普通索引:创建在非主键列上的索引;
3)聚合索引:创建在多列上的索引。
(三)索引的语法:
查看某张表的索引:show index from 表名;
创建普通索引:alter table 表名 add index 索引名 (加索引的列)
创建聚合索引:alter table 表名 add index 索引名 (加索引的列1,加索引的列2)
删除某张表的索引:drop index 索引名 on 表名;
(四)性能测试
测试环境:博主工作用台式机
处理器为Intel Core i5-4460 3.2GHz;
内存8G;
64位windows。
1:创建一张测试表
DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user` (
`id` bigint(20) PRIMARY key not null AUTO_INCREMENT,
`username` varchar(11) DEFAULT NULL,
`gender` varchar(2) DEFAULT NULL,
`password` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
存储引擎使用MyISAM是因为此引擎没有事务,插入速度极快,方便我们快速插入千万条测试数据,等我们插完数据,再把存储类型修改为InnoDB。
2:使用存储过程插入1千万条数据
create procedure myproc()
begin
declare num int;
set num=1;
while num <= 10000000 do
insert into test_user(username,gender,password) values(num,'保密',PASSWORD(num));
set num=num+1;
end while;
end
call myproc();
由于使用的MyISAM引擎,插入1千万条数据,仅耗时246秒,若是InnoDB引擎,插入100万条数据就要花费数小时了。
然后将存储引擎修改回InnDB。使用如下命令: alter table test_user engine=InnoDB;此命令执行时间大约耗时5分钟,耐心等待。
tips:这里是测试,生产环境中不要随意修改存储引擎,还有alter table 操作,会锁整张表,慎用。其次:myisam引擎没有事务,且只是将数据写到内存中,然后定期将数据刷出到磁盘上,因此突然断电的情况下,会导致数据丢失。而InnDB引擎,是将数据写入日志中,然后定期刷出到磁盘上,所以不怕突然断电等情况。因此在实际生产中能用InnDB则用。
3:sql测试
select id,username,gender,password from test_user where id=999999
耗时:0.114s。
因为我们建表的时候,将id设成了主键,所以执行此sql的时候,走了主键索引,查询速度才会如此之快。
我们再执行select id,username,gender,password from test_user where username='9000000'
耗时:4.613s。
我们给username列加上普通索引。
ALTER TABLE `test_user` ADD INDEX index_name(username) ;
此过程大约耗时 54.028s,建索引的过程会全表扫描,逐条建索引,当然慢了。
再来执行:selectid,username,gender,password from test_user where username='9000000'
耗时:0.043s。
再用username和password来联合查询
select id,username,gender,password from test_user where username='9000000' and `password`='*3A70E147E88D99888804E4D472410EFD9CD890AE'
此时虽然我们队username加了索引,但是password列未加索引,索引执行password筛选的时候,还是会全表扫描,因此此时
查询速度立马降了下来。
耗时:4.492s
当我们的sql有多个列的筛选条件的时候,就需要对查询的多个列都加索引组成聚合索引:
加上聚合索引:ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)
再来执行:
耗时:0.001s。
开篇也说过软件层面的优化一是合理加索引;二是优化执行慢的sql。此二者相辅相成,缺一不可,如果加了索引,还是查询很慢,这时候就要考虑是sql的问题了,优化sql。
实际生产中的sql往往比较复杂,如果数据量过了百万,加了索引后效果还是不理想,使用集群。
Tips:
1:加了索引,依然全表扫描的可能情况有:
索引列为字符串,而没带引号;
索引列没出现在where条件后面;
索引列出现的位置没在前面。
2:关联查询不走索引的可能情况有:
关联的多张表的字符集不一样;
关联的字段的字符集不一样;
存储引擎不一样;
字段的长度不一样。
(五)Mysql千万级别数据批量插入只需简单三步
文件中配置
bulk_insert_buffer_size=120M 或者更大
将insert语句的长度设为最大。
Max_allowed_packet=1M
Net_buffer_length=8k
保存
第二步:查看设置的参选有没有生效.
Mysql > SHOW VARIABLES;
bulk_insert_buffer_size默认是8M,我们要把它调成100M或百兆以上,也不要太大。下面看调整个好的,如下图:
第三步:完成设置后,批量插入数据时使用多条模式。
INSERT INTO table (field1,field2,field3) VALUES ('a',"b","c"), ('a',"b","c"),('a',"b","c");
将以上Mysql插入模式用php语言字符串拼接SQL方法如下,你也可转换Java,Python,Golang其它你用的最顺手的语言,将方法转换分享吧:
/**
* 多条数据同时转化成插入SQL语句
* @ CreatBy:IT自由职业者
* @param string $table 表名
* @$arr_key是表字段名的key:$arr_key=array("field1","field2","field3")
* @param array $arr是字段值 数组示例 arrat(("a","b","c"), ("bbc","bbb","caaa"),('add',"bppp","cggg"))
* @return string
*/
function multArray2Insert($table,$arr_key, $arr, $split = '`') {
$arrValues = array();
if (empty($table) || !is_array($arr_key) || !is_array($arr)) {
return false;
}
$sql = "INSERT INTO %s( %s ) values %s ";
foreach ($arr as $k => $v) {
$arrValues[$k] = "'".implode("','",array_values($v))."'";
}
$sql = sprintf($sql, $table, "{$split}" . implode("{$split} ,{$split}", $arr_key) . "{$split}", "(" . implode(") , (", array_values($arrValues)) . ")");
return $sql;
}
以上方法调试过无误的,头条没有代码格式编辑器,所以发代码图。这段代码的返回字符串结果是INSERT INTO table (field1,field2,field3) VALUES ('a',"b","c"), ('a',"b","c"),('a',"b","c") ,做试验时用的普通配置的电脑是每秒1000条批量插入速度,千万条数据写的速度也非常快,新版本PHP语言数据处理能力很强大。如果你的电脑或服务器性能高,你可以调节$arr数组决定你插入多少条数据,灵活使用吧。
mysql在update时,一般也是先select。但注意,在Read Committed隔离级别下,如果没有使用索引,并不会锁住整个表,
还是只锁住满足查询条件的记录而已。使用索引的最佳方式是使用主键,如果我们知道主键的范围(只要是精确范围的超集就可以了),那可以在查询
条件中加上主键的范围,这样查询时,会使用主键索引,就可以提高查询的速度了。这样,我们不用单独再给其它字段加索引,使用已知的索引就可以,加速查询,这种方式感觉很屌。
问题sql背景:项目有6个表的要根据pid字段要写入对应的brand_id字段。但是这个其中有两个表是千万级别的。我的worker运行之后,线上的MySQL主从同步立刻延迟了!运行了一个多小时之后,居然延迟到了40分钟,而且只更新了十几万行数据。问题sql如下:
UPDATE product SET brand_id = [newBrandId] WHERE pid = [pid] AND brand_id = 0
项目组的mysql专家帮我分析了下,因为pid字段没有索引,mysql引擎要逐行扫描出与传入的pid值相等的列,然后更新数据,也就是要扫描完1000W+行磁盘数据才能执行完这个sql。因为是update操作,没有用到索引,于是导致这个sql会占用表锁,其它的sql只能等这个sql执行完成之后才能开始执行。更严重的是,这个千万级的表里面有多少个不同的pid,我就要执行多少个这样的sql。
同事给我的建议的根据id字段进行sql代码层次的横向分表。每次更新1000行的数据,这样mysql引擎就不用每次在扫全表了,数据库压力是之前的万分之一。而且id作为主键,是有索引的,这个时候占用的是这1000行数据的行级锁,不会影响其它的数据。有索引能大大优化查询性能,优化后的sql如下:
UPDATE product SET brand_id = [newBrandId]WHERE pid = [pid] AND brand_id = 0 AND id BETWEEN [startNum] AND [endNum]
仅仅用了id限区间的语句,将一个千万级的大表代码层次上进行横向切割。重新上线worker后,mysql主从没有任何延迟!而且经过监控,短短10分钟就更新了十几万数据,效率是之前的6倍!更重要的是数据库负载均衡,应用健康运行。