2020年6月19日
mysql优化篇
之前看了mysql innodb的书，一直也没整理。先看看别人的总结，结合自己看的，做些记录。
从实际业务中，可以发现，如果join的表过多的话，查询速度会很慢的，系统中的item表，就是例子，之前做了优化，强制走索引才快了一点，但是实际速度还是不太理想。
1.别人的总结：
a.设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
b.选择合适的表字段数据类型和存储引擎，适当的添加索引。
c.mysql库主从读写分离。
d.找规律分表，减少单表中的数据量提高查询速度。
e.添加缓存机制，比如memcached，apc等。
f.不经常改动的页面，生成静态页面。
g.书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.
2.优化
优化顺序，sql语句索引级别的优化、表结构优化、系统配置优化、硬件性能的优化等
3.常用的优化技巧
1.使用查询缓存优化查询
a.SELECT username FROM user WHERE signup_date >= CURDATE()
b.SELECT username FROM user WHERE signup_date >= '2014-06-24‘
上面两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。
2.使用EXPLAIN关键字检测查询
Explain + sql 语句后面会专门用一篇来讲一下。https://www.zeroheart.xyz/wordpress/?p=653
使用这个关键字，可以让我们知道执行中用了什么索引，扫描了多少数据等等。根据这个，可以优化索引，或者强制使用某些索引。
3.当只要一行数据时使用LIMIT 1
查到到一条符合条件的数据之后，sql查找会停止，避免无谓的消耗。
limit 的其他优化，我们常用的分页，当页数靠后（变大）的时候，往往查询速度大幅下降。sql类似这样
select * from test limit 1000000, 100
这样子的语句，是需要把数据都查出来在之后在找100条的，可以改为其他写法,并尽量引导客户使用搜索条件。
1.select * from tsest where id > (select id from test limit 1000000,1) limit 0, 100;
2. SELECT * FROM test a JOIN (SELECT id FROM test LIMIT 1000000,100) b ON a.id = b.id
3.select * from tsest where id between (lastId, lastId+100);
4.为搜索字段建立索引
索引不一定就是给主键或者是唯一的字段，如果在表中，有某个字段经常用来做搜索，需要将其建立索引。
索引的有关操作如下：
1.创建索引
在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
1.1> ALTER TABLE
ALTER TABLE 用来创建普通索引、唯一索引、主键索引和全文索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
ALTER TABLE table_name ADD UNIQUE (column_list);
ALTER TABLE table_name ADD PRIMARY KEY (column_list);
ALTER TABLE table_name ADD FULLTEXT (column_list);
其中table_name是要增加索引名的表名，column_list指出对哪些列列进行索引，多列时各列之间使用半角逗号隔开。索引名index_name是可选的，如果不指定索引名称，MySQL将根据第一个索引列自动指定索引名称，另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
1.2> CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引以及全文索引，但是不可以对表增加主键索引
CREATE INDEX index_name ON table_name (column_list);
CREATE UNIQUE index_name ON table_name (column_list);
CREATE FULLTEXT index_name ON table_name (column_list);
table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名必须指定。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。
2.索引类型
普通索引INDEX：适用于name、email等一般属性
唯一索引UNIQUE：与普通索引类似，不同的是唯一索引要求索引字段值在表中是唯一的，这一点和主键索引类似，但是不同的是，唯一索引允许有空值。唯一索引一般适用于身份证号码、用户账号等不允许有重复的属性字段上。
主键索引：其实就是主键，一般在建表时就指定了，不需要额外添加。
全文检索：只适用于VARCHAR和Text类型的字段。
注意：全文索引和普通索引是有很大区别的，如果建立的是普通索引，一般会使用like进行模糊查询，只会对查询内容前一部分有效，即只对前面不使用通配符的查询有效，如果前后都有通配符，普通索引将不会起作用。对于全文索引而言在查询时有自己独特的匹配方式，例如我们在对一篇文章的标题和内容进行全文索引时：
ALTER TABLE article ADD FULLTEXT ('title', 'content'); 在进行检索时就需要使用如下的语法进行检索：
SELECT * FROM article WHERE MATCH('title', 'content') AGAINST ('查询字符串');
在使用全文检索时的注意事项：
MySql自带的全文索引只能用于数据库引擎为MYISAM的数据表，如果是其他数据引擎，则全文索引不会生效。此外，MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx（斯芬克斯）/Coreseek技术来处理中文。另外使用MySql自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。
3.组合索引
组合索引又称多列索引，就是建立索引时指定多个字段属性。有点类似于字典目录，比如查询 'guo' 这个拼音的字时，首先查找g字母，然后在g的检索范围内查询第二个字母为u的列表，最后在u的范围内查找最后一个字母为o的字。比如组合索引(a,b,c)，abc都是排好序的，在任意一段a的下面b都是排好序的，任何一段b下面c都是排好序的
组合索引的生效原则是 从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；
造成断点的原因：
前边的任意一个索引没有参与查询，后边的全部不生效。
前边的任意一个索引字段参与的是范围查询，后面的不会生效。
(0) select * from mytable where a=3 and b=5 and c=4;
abc三个索引都在where条件里面用到了，而且都发挥了作用
(1) select * from mytable where c=4 and b=6 and a=3;
这条语句为了说明 组合索引与在SQL中的位置先后无关，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样（这个不同人说的不一样，有待确认一下）
(2) select * from mytable where a=3 and c=7;
a用到索引，b没有用，所以c是没有用到索引效果的
(3) select * from mytable where a=3 and b>7 and c=3;
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
(4) select * from mytable where b=3 and c=4;
因为a索引没有使用，所以这里 bc都没有用上索引效果
(5) select * from mytable where a>4 and b=7 and c=9;
a用到了 b没有使用，c没有使用
(6) select * from mytable where a=3 order by b;
a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的
(7) select * from mytable where a=3 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort
(8) select * from mytable where b=3 order by a;
b没有用到索引，排序中a也没有发挥索引效果
(9) select * from mytable where a=3 order by a;
用到索引
(10) select * from mytable where a>3 order by b;
没有用到索引
(11) select * from mytable where a>3 order by a;
用到索引
(12) select * from mytable where order by a desc , b desc;
用到索引
(13) select * from mytable where order by a desc , b asc;
没有用到索引
注意：在查询时，MYSQL只能使用一个索引，如果建立的是多个单列的普通索引，在查询时会根据查询的索引字段，从中选择一个限制最严格的单例索引进行查询。别的索引都不会生效。
4.查看索引
mysql> show index from tblname;
mysql> show keys from tblname;
5.删除索引
删除索引的mysql格式 :DORP INDEX IndexName ON tab_name；
like的索引规律：
LIKE以%开头会导致索引失效；使用覆盖索引解决之。
大概是说，如果某个字段有普通索引，我们利用这个字段模糊查询时，如果使用前缀或者全匹配，%xx或者%xx%，都不会都索引，只有xx%可以，这时候，如果有其他字段和这个字段建立联合索引，查询这个其他字段的时候，无论怎么都会走索引，查索引字段之外的字段，依然不行。
5.在join表的时候使用相当类型的列，并将其索引
SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = “user_id”
两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。
6.切记不要使用ORDER BY RAND()
如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得不去执行RAND()函数（很耗CPU时间），而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序）
7.避免使用SELECT *
8.永远为每张表设置一个ID主键
我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的 AUTO_INCREMENT标志。 就算是我们 users 表有一个主键叫 “email”的字段，我们也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在我们的程序中，我们应该使用表的ID来构造我们的数据结构。 而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区…… 在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。
9.使用ENUM而不是VARCHAR
ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。 如果我们有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，我们知道这些字段的取值是有限而且固定的，那么，我们应该使用 ENUM 而不是 VARCHAR。
10.尽可能的不要赋值为NULL mysql 里面有大坑，系统中有很多is null or ='' or = '使用'的代码就是这个使用不当造成的。
11.固定长度的表会更快。没有实践过，一般不这么用
12.垂直分割
“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。
13.拆分大的DELETE或INSERT
如果我们需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，我们需要非常小心，要避免我们的操作让我们的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。如果我们把我们的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让我们的WEB服务Crash，还可能会让我们的整台服务器马上掛了。所以在使用时使用LIMIT控制数量操作记录的数量。
limit是个好东西
14.越小的列会越快，字段类型设置要考虑
15.选择正确的存储引擎,5.5后的默认引擎是innodb，提供事务，一致性不锁定读等。
16.注意数据库链接的优化
17.在查询语句里面，不要用动态计算，比如 f_quantity - 100 = 10;
应该直接写f_quantity=110,这是个极端的例子，要表达的就是不要计算后在查询。
18.count(*)会忽略所有的列，直接统计所有列数，不要使用count(列名)
19.确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引
20. 用exists代替in，not exists代替not in，效率会更好
21.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
22.应尽量避免在 where 子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
-- 可以这样查询：
select id from t where num=10 union all select id from t where num=20
in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
-- 对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
23.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
-- 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
本文目前大多参考以下文章，还有大量的技巧和详细使用没涉及，会持续补充完善此文章。
https://www.cnblogs.com/yhgn/p/11210130.html
https://blog.csdn.net/u013087513/java/article/details/77899412
https://blog.csdn.net/madongyu1259892936/article/details/98978068