MySQL性能优化
关于MySQL百万级数据量设计查询的优化
一、数据库设计：
- 首先将数据表的字段大小都进行了修改，以前都是用默认的char（255），现在全部根据大小改到varchar（20），关于char和varchar的区别网上有很多说法，这里不进行叙述，可点击这里查看；
- 能用数字就不用字符，因为MySQL中数字只对比一次，而字符串会一个一个的对比；
- 删除掉包含汉字的字段，尽量使用id代替；
- 为某个经常查询的字段设置索引；
5、并非索引创建越多越好。索引固然可以提高相应的查询效率，但是同样会降低insert以及update的效率。因为在insert或是update的时候有可能会重建索引或是修改索引。所以索引怎样创建需要慎重考虑，视情况而定。一个表中所以数量最好不要超过6个。若太多，则需要考虑一些不常用的列上创建索引是否有必要。
二、数据库查询：
1、尽量避免在where字句中对字段进行null值的判断。否则将会导致引擎放弃使用索引而进行全表扫描。
例如：select id from user where num is null 。可以将num是这个字段设置默认值0.确保表中没有null值，然后在进行查询。
sql如下：select id from user where num=0;
（考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引）
2：尽量避免在where字句中对字段进行null值的判断。否则将会导致引擎放弃使用索引而进行全表扫描。
例如：select id from user where num is null 。可以将num是这个字段设置默认值0.确保表中没有null值，然后在进行查询。
sql如下：select id from user where num=0;
（考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引）
3：应尽量避免在where子句中使用!=或者是<>操作符号。否则引擎将放弃使用索引，进而进行全表扫描。
4：应尽量避免在where子句中使用or来连接条件，否则导致放弃使用索引而进行全表扫描。可以使用 union 或者是 union all代替。
例如： select id from user where num =10 or num =20 这个语句景导致引擎放弃num索引，而要全表扫描来进行处理的。
可以使用union 或者是 union all来代替。如下：
select id from user where num = 10;
union all
select id from user where num =20;
(union 和 nuion all 的区别这里就不赘述了)
5：in 和 not in 也要慎用，否则将会导致全表扫描。
in 对于连续的数组，可以使用between …and.来代替。
例如：
select id from user where num in (1,2,3);
像这样连续的就可以使用between …and…来代替了。如下：
select id from user where num between 1 and 3;
6：like使用需注意
下面这个查询也将导致全表查询：
select id from user where name like ‘%三’；
如果想提高效率，可以考虑到全文检索。比如solr或是luncene
而下面这个查询却使用到了索引：
select id from user where name like ‘张%’；
7：where子句参数使用时候需注意
如果在where子句中使用参数，也会导致全表扫描。因为sql只会在运行时才会解析局部变量。但优化程序不能将访问计划的选择推迟到运行时；必须在编译时候进行选择。然而，如果在编译时建立访问计划，变量的值还是未知大，因而无法作为索引选择输入项。
如下面的语句将会进行全表扫描：
select id from user where num = @num
进行优化，我们知道num就是主键。是索引。
所以可以改为强制查询使用索引：
select id from user where (index(索引名称)) where num = @num;
8：尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
例如：select id from user where num/2=100
应修改为：
select id from user where num = 100*2;
9：尽量避免爱where子句中对字段进行函数操作，这将导致引擎放弃索引，而进行全表扫描。
例如：
select id from user substring(name,1,3) = ‘abc’ ，这句sql的含义其实就是，查询name以abc开头的用户id
(注：substring(字段，start,end)这个是mysql的截取函数)
应修改为：
select id from user where name like ‘abc%’;
10：不要在where子句中的”=”左边进行函数、算术运算或是使用其他表达式运算，否则系统可能无法正确使用索引
11：复合索引查询注意
在使用索引字段作为条件时候，如果该索引是复合索引，那么必须使用该索引中的第一个字段作为条件时候才能保证系统使用该所以，否则该索引将不会被使用，并且应尽可能的让字段顺序和索引顺序一致。
12：不要写一些没意义的查询。
例如：需要生成一个空表结构和user表结构一样(注：生成的新 new table的表结构和 老表 old table 结构一致)
select col1,col2,col3…..into newTable from user where 1=0
上面这行sql执行后不会返回任何的结果集，但是会消耗系统资源的。
应修改为：
create table newTable (….)这种语句。
13：很多时候用exists 代替 in是一个很好的选择。
比如：
select num from user where num in(select num from newTable);
可以使用下面语句代替：
select num from user a where exists(select num from newTable b where b.num = a.num );
14：并不是所有索引对查询都有效，sql是根据表中数据进行查询优化的，当索引lie(索引字段)有大量重复数据的时候，sql查询可能不会去利用索引。如一表中字段 sex、male、female 几乎各一半。那么即使在sex上创建了索引对查询效率也起不了多大作用。
15、任何地方都不要使用 select * from t 、用具体的字段列表代替“*”、不要返回用不到的任何字段。
三、系统设置；
1、缓存的配置：在MySQL中有多种多样的缓存，有的缓存负责缓存查询语句，也有的负责缓存查询数据。这些缓存内容客户端无法操作，是由server端来维护的。它会随着你查询与修改等相应不同操作进行不断更新。通过其配置文件我们可以看到在MySQL中的缓存：
在这里主要分析query cache，它是主要用来缓存查询数据。当你想使用该cache，必须把query_cache_size大小设置为非0。当设置大小为非0的时候，server会就会缓存每次查询返回的结果，到下次相同查询server就直接从缓存获取数据，而不是再执行查询。能缓存的数据量就和你的size大小设置有关，所以当你设置的足够大，数据可以完全缓存到内存，速度就会非常之快。
但是，query cache也有它的弊端。当你对数据表做任何的更新操作(update/insert/delete)等操作，server为了保证缓存与数据库的一致性，会强制刷新缓存数据，导致缓存数据全部失效。所以，当一个表格的更新数据表操作非常多的话，query cache是不会起到查询提升的性能，还会影响其他操作的性能。
2：slow_query_log分析。
其实对于查询性能提升，最重要也是最根本的手段也是slow_query的设置。
当你设置slow_query_log为on的时候，server端会对每次的查询进行记录，当超过你设置的慢查询时间(long_query_time)的时候就把该条查询记录到日志。而你对性能进行优化的时候，就可以分析慢查询日志，对慢查询的查询语句进行有目的的优化。可以通过创建各种索引，可以通过分表等操作。那为什么要分库分表那，当不分库分表的时候那个地方是限制性能的地方啊。下面我们就简单介绍。
开启慢查询功能
slow_query_log_file 慢查询日志文件路径
slow_query_log 用于指定是否打开慢查询日志
long_query_time 超过多少秒的查询就写入日志
log_output=file必须指定file或者是table如果是table则慢查询信息会保存到mysql库下的slow_log表中。这点要清楚。默认值是NONE
打开my.cnf配置文件，加入以下代码：
slow_query_log=on ;
slow_query_log_file = /tmp/mysql-slow.log
long_query_time = 2
3、分库分表
分库分表应该算是查询优化的杀手锏了。上述各种措施在数据量达到一定等级之后，能起到优化的作用已经不明显了。这个时候就必须对数据量进行分流。分流一般有分库与分表两种措施。而分表又有垂直切分与水平切分两种方式。下面我们就针对每一种方式简单介绍。
对于mysql，其数据文件是以文件形式存储在磁盘上的。当一个数据文件过大的时候，操作系统对大文件的操作就会比较麻烦与耗时，而且有的操作系统就不支持大文件，所以这个时候就必须分表了。另外对于mysql常用的存储引擎是Innodb，它的底层数据结构是B+树。当其数据文件过大的时候，B+树就会从层次和节点上比较多，当查询一个节点的时候可能会查询很多层次，而这必定会导致多次IO操作进行装载进内存，肯定会耗时的。除此之外还有Innodb对于B+树的锁机制。对每个节点进行加锁，那么当更改表结构的时候，这时候就会树进行加锁，当表文件大的时候，这可以认为是不可实现的。
所以综上我们就必须进行分表与分库的操作。分表细节请点击mysql数据库分库分表(Sharding)
4、 统计类去除实时查询,定时执行保存到表,读取数据
5、大文本数据,多媒体数据不存储
6、常用查询冗余字段,争取不用关联表能取到数据
7、先优化执行频率最高的,再优化频率低的
8、mysql用的是b+树,所有数据都在叶子节点,索引会比较小,常用查询字段建立索引,所有查询尽量在索引中完成
9、唯一性太差的字段,不适合建立索引(如状态字段、类型字段),当一条Query返回的数据超过了全表的15%时 不应该用索引，更新频繁字段不适合索引
10、事务尽量小,减少锁表的时间
爱被打了一巴掌
傷城~
灰太狼
比眉伴天荒![[亲测]java.sql.SQLException: Parameter number X is not an OUT parameter](https://image.dandelioncloud.cn/dist/img/NoSlightly.png "[亲测]java.sql.SQLException: Parameter number X is not an OUT parameter")
还没有评论，来说两句吧...