mysql优化问题

sql优化几个步骤   

        1.SQL语句及索引的优化

        2. 数据库表结构的优化

        3.系统配置的优化

        4.硬件的优化

第一点：  SQL语句的优化

1、减少返回数据

（1）只返回必要的列，用具体的字段列表代替 select * 语句。

（2）只返回必要的行，使用 Limit 语句来限制返回的数据。如果不使用 Limit  的话，MySQL将会一行一行的将全部结果按照顺序查找，最后返回结果，借助 Limit 可以实现当找到指定行数时，直接返回查询结果，提高效率。

2、优化深度分页的场景：利用延迟关联或者子查询

（1） 延迟关联：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

（2） 覆盖索引：select的数据列只用从索引中就能够得到，不用回表查询。

3、分解大连接查询

（1）减少锁竞争。

（2）让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。

（3）分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。

（4）在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。

（5）查询本身效率也可能会有所提升。比如使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。
 

4、避免使用select的内联子查询

（1）在select后面有子查询的情况称为内联子查询，SQL返回多少行，子查询就需要执行过多少次，严重影响SQL性能。

5、尽量使用Join代替子查询

（1）由于MySQL的优化器对于子查询的处理能力比较弱，所以不建议使用子查询，可以改写成Inner Join，之所以 join 连接效率更高，是因为 MySQL不需要在内存中创建临时表。

例如：

select 
    b.1,b.2, a.1,a.2 
from 
    a a 
inner join 
    (select 1,2 from b where `3` = 1) as b 
on 
    a.1 = b.1;

6、多张大数据量的表进行JOIN连接查询，最好先过滤在JOIN

（1）在多个表进行 join 连接查询的时候，最好先在一个表上先过滤好数据，然后再用过滤好的结果集与另外的表 Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省 IO 操作所消耗的时间，连表之前可以先加过滤条件。

7、避免在使用or来连接查询条件

（1）如果使用了or并且一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

8、union、in、or 都能够命中索引，但推荐使用 in

（1）union：能够命中索引，并且MySQL 耗费的 CPU 最少

select * from doc where status=1
union all 
select * from doc where status=2;

（2）in：能够命中索引，查询优化耗费的 CPU 比 union all 多，但可以忽略不计

select * from doc where status in (1, 2);

（3）or：新版的 MySQL 能够命中索引，但是如果一个字段有建立索引、一个字段没有建立索引，那么将导致索引失效而进行全表扫描，or 查询优化耗费的 CPU 比 in 多

select * from doc where status = 1 or status = 2

对于上面三种关键词：union all 分两步执行，而 in 和 or 只用了一步，效率高一点。用 or 的执行时间比 in 时间长。因为使用 or 条件查询，会先判断一个条件进行筛选，再判断 or 中另外的条件再筛选，而 in 查询直接一次在 in 的集合里筛选，并且or 查询优化耗费的 CPU 比 in 多，所以推荐使用in。
9、对于连续的数值，能用 between 就不要用 in

（1）这不用多说，如果是in就需要一个一个数去对比。between不需要。

10、小表驱动大表，即小的数据集驱动大的数据集

（1）使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件；使用in一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据。

2）in在内表查询或者外表查询过程中都会用到索引；exists仅在内表查询时会用到索引。

（3）一般来说，当子查询的结果集比较大，外表较小使用exist效率更高；当子查询的结果集较小，外表较大时，使用in效率更高。

4）对于 not in 和 not exists，not exists 效率比 not in 的效率高，与子查询的结果集无关，因为 not in 对于内外表都进行了全表扫描，没有使用到索引。not exists的子查询中可以用到表上的索引。

11、使用union all 替换 union

（1）当SQL语句需要union两个查询结果集合时，这两个结果集合会以union all的方式被合并，然后再输出最终结果前进行排序。如果用union all替代union，这样排序就不是不要了，效率就会因此得到提高.。需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。

12、优化Group by，使用where子句替换Having子句

（1）避免使用having子句，having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过where子句提前过滤查询的数目，就可以减少这方面的开销。

（2）on、where、having这三个都可以加条件的子句，on是最先执行，where次之，having最后。

（3）提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。

举个例子：

低效: SELECT a,b FROM erp GROUP by JOB HAVING a = 1 OR a = 2 

高效: SELECT a,b FROM erp WHERE a = 1 OR a = 2 GROUP by a

13、尽量使用数字型字段

(1)若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能。引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

14、写出统一的SQL语句

(1)注意格式，注意大小写，注意空格之类的。

15、使用复合索引须遵守最左前缀原则

(1)复合索引必须使用到最左边字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

16、当需要全表删除且无需回滚时，使用Truncate替代delete

drop、truncate、delete的区别：https://blog.csdn.net/a745233700/article/details/85238118

17、使用表的别名

(1)当在SQL语句中连接多个表时, 使用表的别名并把别名前缀用于每个Column上，这样可以减少解析的时间并减少那些由Column歧义引起的语法错误。

18、避免使用耗费资源的操作

(1)带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句，会启动SQL引擎执行耗费资源的排序功能，DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。通常。带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写，如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强。

19、Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志

20、应尽可能的避免更新聚簇索引数据列，因为聚簇索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。

21、尽量使用表变量来代替临时表

22、考虑使用“临时表”暂存中间结果。临时表并不是不可使用，适当地使用它们可以使某些查询更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。将临时结果暂存在临时表，后面的查询就在临时表中查询了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。但是，对于一次性事件，较好使用导出表。

23、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert

24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定

25、避免频繁创建和删除临时表，以减少系统表资源的消耗

26、尽量避免使用游标，因为游标的效率较差。与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。

27、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 

28、尽量避免大事务操作，提高系统并发能力

29、在运行代码中，尽量使用PreparedStatement来查询，不要用Statement

第二点：  索引优化

1、like语句的前导模糊查询不使用索引

例如：

select * from erp where a like '%XX'；   --不能使用索引
select * from erp where a like 'XX%'；   --非前导模糊查询，可以使用索引

2、负向条件查询不能使用索引

(1)负向条件有：!=、<>、not in、not exists、not like 等。

例如：

select * from erp where a != 1 and a != 2;     --不能使用索引

select * from erp where a in (3,4);           --优化为 in 查询，可以使用索引

3、范围条件右边的列不能使用索引（范围列可以用到索引）

(1)范围条件有：<、<=、>、>=、between等。

(2)索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

4、在索引列做任何操作（计算、函数、表达式）会导致索引失效而转向全表扫描

例如：

select * from erp where YEAR(create_time) <= '2016';                 -- 不能使用索引
select * from erp where create_time<= '2016-01-01';                  -- 可以使用索引
select * from erp where date < = CURDATE()；                         -- 不能使用索引
select * from erp where date < = '2018-01-2412:00:00';               -- 可以使用索引
select id from erp where substring(name,1,3)=’abc’                   -- 不能使用索引
select id from erp where name like ‘abc%’                            -- 可以使用索引
select id from erp where num/2=100                                   -- 不能使用索引
select id from erp where num=100*2                                   -- 可以使用索引

5、where 子句中索引列使用参数，也会导致索引失效

(1)因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描。

select id from t where num=@num                                   -- 不能使用索引

select id from t with(index(索引名)) where num=@num   --可以改为强制查询使用索引

6、强制类型转换会导致全表扫描

（1）字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。 

（2）如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引，因为内部发生的类型转换。

select * from user where phone=13800001234;      -- 不能使用索引

select * from user where phone='13800001234';     -- 可以使用索引

7、is null, is not null 在无法使用索引，不过在mysql的高版本已经做了优化，允许使用索引

select id from t where num is null;     -- mysql低版本不能使用索引

select id from t where num=0;           -- 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询

8、使用组合索引时，要符合最左前缀原则

（1）建立联合索引的时候，区分度最高的字段在最左边：

（2）存在等号和非等号混合判断条件时，在建立索引时，把等号条件的列前置，如 where a > ? and b= ?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。

（3）最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致，但还是建议 where 条件的顺序和联合索引一致。

（4）假如index(a,b,c), where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用。

（5）组合索引的字段数不允许超过5个。如果在a,b,c三个字段上建立联合索引 index(a,b,c)，那么他会自动建立 a、(a,b)、(a,b,c) 三组索引。 

9、利用覆盖索引来进行查询操作，避免回表，减少select * 的使用

（1）覆盖索引：被查询列要被所建的索引覆盖，被查询列的数据能从索引中直接取得，不用通过行定位符 再到 row 上获取，加速查询速度。

例如登录业务需求，SQL语句如下。

Select uid, login_time from user where login_name=? and passwd=?

10、索引的选择性与前缀索引
既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

第一种情况是表记录比较少，没必要建索引，让查询做全表扫描就好了。

第二种情况是索引的选择性较低。所谓索引的选择性，是指 不重复的索引值 与 表记录数量 的比值：

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

例如，employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+

title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

假设employees表只有一个索引<emp_no>，那么如果我们想按名字搜索一个人，就只能全表扫描了：

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+

<first_name>显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+

选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+

这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引建上：

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));
此时再执行一遍按名字查询，比较分析一下与建索引前的结果：

SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+

性能的提升是显著的，查询速度提高了120多倍。