mysql-comparison-tools_Mysql查询优化-DB篇

本文重点从数据库本身角度，硬件和环境的优化不在本文范围内

1. 使用索引(Index All Columns Used in 'where', 'order by', and 'group by' Clauses)

索引的作用就不用在这里说了，需要说明的是其副作用：索引会占用更多的空间，并导致增删改速度会变慢

一个很重要的问题是，如果建立索引之后，发现查询速度并没有加快，那么如果去排查呢？这就需要借助于执行计划(execution plan)了

下面是建立索引前后的执行计划：

explain SELECT title, body FROM test.articles where title ='数据库管理'

建索引前：

建立索引：ALTER TABLE articles ADD INDEX index_title (title)

建索引后：

索引是否有效，不只需要确认执行计划中的key，而且需要确认key_len

比如此处的title是varchar(200)，而且表的编码为utf8mb4(兼容性好于utf8)，一个字符占4个字节，key_len=200*4 + 1(标示是否NULL) + 2(长度)

索引的数据结构：

Mysql的索引使用的是B+二叉树(进一步，需要了解二叉树，平衡二叉树或者AVL树，红黑树，B/B+树)

B+树索引并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

**对于连接查询，只需要在关联顺序中的第二张表的相应列上创建索引即可。

**覆盖索引(包含所有需要查询的字段的值)对于字段少的查询非常有用

**应避免冗余和重复索引，参考

**不支持函数索引，但支持前缀索引(也就是对前N个字符创建索引)

**列的基数(比如数值范围)越大，索引效果越好

**尽量使用短索引

**利用最左前缀：例如 ALTER TABLE student ADD INDEX lname_birth_age (name,birthday,age); 相当于建立了三个索引：(name),(name,birthday),(name,birthday,age)，所以要把常用的放在最左边

2.  用Union代替like(Optimize Like Statements With Union Clause)

如果是分别建立索引，那么作用会更大。另外，UNION ALL 要比 UNION 快很多(如果知道不重复，就应该使用Union All)

mysql> select * from students where first_name like 'Ade%' or last_name like 'Ade%';=>mysql> select from students where first_name like 'Ade%' union all select from students where last_name like 'Ade%' ;

3. 避免在开头使用通配符(Avoid Like Expressions With Leading Wildcards)

会导致索引无法使用(比如 like '%abc')而进行全表扫描，索引就类似字典的目录，试想如果只知道词语的后半部分，又如何去查找目录呢？

解决方法：

1)对于'%xx'，使用reverse函数：reverse(name) like reverse(‘%245′)

2)对于'%xx%'，使用instr函数： instr(t.column,’xx’)> 0

4. 使用Mysql的全文搜索(FTS：full-text search)

使用举例：

mysql>Alter table students ADDFULLTEXT (first_name, last_name);

mysql>Select * from students where match(first_name, last_name) AGAINST ('Ade'); // 默认使用自然语言匹配单词

关于FTS，有几个需要注意的地方：

1)只在MyISAM或者InnoDB适用

2)只用于字段char，varchar，text

3)自5.7.6开始，支持CJK语言(需要使用ngram解析：ADD FULLTEXT INDEX `first_name` (`first_name` ASC, `last_name` ASC)  WITH PARSER ngram；)

4)对于大量数据迁移，可以先迁移数据再创建FTS，这样比先建FTS再迁移快得多

5. 优化表结构

1)数据类型：shorter is always better. 比如如果用户量不多于100，可以用TINYINT作为ID；再如如果DateTime够用，就不要用Timestamp；数字类型不要用varchar表示

“shorter is alwasys better”同样适用于查询语句：保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

2)避免使用Null：如果使用，则在sum这列值的时候就需要筛选，否则就会出现错误

3)避免定义的列数太多，可以用多个小表代替一个大表，但也要注意不要过度设计

4)join时尽可能少关联表

6. 使用Mysql的缓存机制

查看是否使用缓存：show variables like 'have_query_cache';

关于缓存的参数可以在my.cnf中设置

对于缓存的命中，需要了解以下情况

1)两个查询在任何字符上的不同(例如：空格、注释)，都会导致缓存不会命中。

2)如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，将这样的查询结果缓存起来没有任何的意义

3)缓存失效时机：查询对应的表数据发生变化

4)只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升

5)可以使用SQL_CACHE和SQL_NO_CACHE控制查询语句是否使用缓存，比如：select SQL_NO_CACHE count(*) from users where email = 'hello';

6)不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存

7.用 PreparedStatement， 一般来说比 Statement 性能高

一个 sql发给服务器去执行，涉及步骤：语法检查、语义分析， 编译，缓存

持续补充中...