实践中如何优化MySQl

1. 先来搞清楚何为“MySQL优化”：

实践项目中，我们使用MySQL的目的就是用来存储数据、查询数据，这个数据库的作用，而“优化”数据库，就是让它性能更高，我们用的更爽，即是：提升数据库的 插入、删除、更新数据的响应速度，提升数据库的查询速度。

2. 优化手段：

四条，从影响效果上逐次下降：

2.1 SQL语句及索引的优化：

索引优化：什么时候应该建立索引，建什么样的索引，如何避免索引失效。

2.2 数据库表结构的优化：

使用可以存下你的数据的最小数据类型（如tinyint）；尽量避免NULL；使用简单的数据类型，int比varchar更容易被MySQL处理；尽量避免使用text；适当的分库分表。

2.3 系统配置的优化：

2.4 硬件的优化：

3. SQL语句的优化：

3.1 SQL语句优化到底是在优化什么？

从这几方面考虑：

所谓“SQL语句的优化”，就是能够让我们以更快的速度定位到想要操作的表中的那一行数据（无论是update、delete、select 删改查 中的哪一种操作，都要先通过where子句过滤出符合条件的行，然后再对这些行进行操作），所以，SQL语句优化就转化成了 “查找出某一行数据的速度” 的优化。

在MySQL中，能对查找进行优化的唯一手段，就是使用索引。

那么，“SQL语句优化”这个问题就又进一步转化成了：“如何优化索引”，分两个方面：
如何合理的建立索引、如何合理的使用索引。

逻辑链：
如何优化SQL语句 -----> 如何高效查找出某一行或某几行数据 -----> 如何优化索引

3.2 如何进行SQL语句优化：

具体出下面这些条措施：

1. 对查询进行优化，应尽量避免全表扫描（ALL，遍历），首先应考虑在 where 及 order by 涉及的列上建立索引；（查询、排序，请尽量使用索引）；（为什么“order by” 排序操作也要选择使用索引呢？ 因为索引树是B+树，索引树本身就是有序的，例如一个SQL语句“select * from staff order by name;” 如果name列上已经有索引了，那么order by就省去了排序的操作，效率就提高了）
2. 一般使用 int整型的“自增主键”（AUTO_INCREMENT）做 主键索引，这样可以保证：① 在B+树中顺序存储；② 其他索引的B+树的叶子节点存储主键id是int型，叶子节点更小；
3. where子句中避免使用 > < != 等，会导致索引失效；
4. where子句中避免使用 is null 、 is not null 判断，会导致索引失效；
5. 解决方法是在null的列上设置默认值0，确保表中num列没有null值，然后is null 就替换成了 = 0，可以使用索引了；
6. where子句中避免使用 or 来连接条件；（使用OR的场景可以细分成多种情况，见下文）；
7. where子句中使用 LIKE“%value” 时，会导致索引失效，解决方法是使用“覆盖索引”替换select *；
8. where子句中 in 和 not in 慎用，可能会导致索引失效；（主键索引除外）（可使用覆盖索引解决）
9. where子句中使用参数，也会导致索引失效，全表扫描。因为SQL只有在运行时才会解析局部变量，例如这样的SQL语句就是不好的语句：select id from t where num = @num; 解决方法是强制查询使用索引：select id from t force index(index) where num = @num;
10. 同（9），where子句中使用表达式，也会导致索引失效，全表扫描。反例SQL语句：select id from t where num/2=100; 修改方式：select id from t where num = 100*2;
11. where子句中对字段进行函数操作，也会导致索引失效，全表扫描。反例SQL语句：select id from t where substring(name, 1, 3) = 'abc'; //name以abc开头的id； 修改方式：select id from t where id like "%abc"; //%like使用覆盖索引，只要id这一列有索引就好，遍历索引
12. where子句的“=”等号左边不要进行函数、算数运算或其他表达式运算，否则可能会导致系统无法使用正确的索引；
13. 在使用“组合索引”（复合索引）时，必须按照“最佳左前缀法则”，即必须保证where后面的多个判断条件按照组合索引中各个列的顺序传入，必须从第一个开始，中间必须不能间断，如果有间断，则间断之后的列上的索引都无法使用。例如：select * from staff where name = 'Alice' and age = 22 and pos = 'HR';（按照组合索引 name-age-pos 的顺序）；注意 在使用 “select *” 格式查找时，where后面的判断条件中如果使用了 “>”、“<”、或者 OR 等，将会导致组合索引失效，解决方法是使用“覆盖索引”；
14. 并不是所有索引都会查询有效，MySQL是会根据数据和索引进行评估成本，例如一张表的sex列中male和female各占一半，那么即使在这张表上建了索引，查找时可能也不会使用这张表，因为这并不能起到过滤的作用；
15. 索引并不是越多越好，过多的索引占用内存空间，降低update、delete、insert等其他操作的效率（这些操作需要重建每个现有的索引）。一般一张表的索引个数最好不要超过 6个（平衡select 与 update/delete/insert 操作的效率），若太多则需要考虑一些不太常用的列上建的索引是否有必要；
16. 要尽量避免更新 聚集索引（clustered index，一般情况下，当InnoDB中存在主键时，主键就是聚集索引，叶子节点存储行数据本身，其余普通索引（也称二级索引）的B+树上的叶子节点存储主键的值），因为一旦聚集索引更新，其余各索引都要跟着更新，这会耗费相当大的资源；如果clustered index在频繁的更新的话，就要考虑这个列是否适合被用作聚集索引；
17. 对于字段的数据类型：① 尽量使用数字型，代替字符型，因为字符型会降低查找和连接的性能，并且增加存储开销；② 尽量使用 varchar 代替 char，因为变长字段存储空间小，一方面可以节省存储空间，另一方面较小的字段可以提升查找的效率。
18. 任何地方都不要使用 “select *”，使用具体的字段代替 *，不要返回用不到的任何字段，也可以争取使用覆盖索引而避免回表、避免导致索引失效；
19. 临时表：避免频繁创建和销毁临时表，以减少系统表资源的消耗；
20. 避免一次性向客户端返回大量数据，优化为分多次、少量的返回；
21. 避免大事务操作，提高系统的 并发能力。

4. 索引的优势及劣势：

1. 索引的优势：
   通过索引列对数据进行排序，降低数据排序的成本，提高查找的效率；
2. 索引的劣势：
   a. 索引也是一棵B+树，也要占内存空间；
   b. 且，执行insert、update、delete等更新表的操作时，附带更新这颗索引树，虽然提高了查询速度，但是降低了更新速度；

5. 哪些情况适合建立索引：

1. 频繁作为查询条件的字段的列，应该建立索引；（经常作为where子句的判断条件的列，例如 where age = 22;）
2. 查询中作为排序的字段，应该建立索引，排序字段若通过索引去访问会大大提高排序速度；（作为 order by 子句的列，例如 select .. from .. order by age desc）;
3. 其他，暂且不写、、

6. 哪些情况不适合建立索引：

1. 频繁被更新的字段；（插入、更新、删除）
2. where子句中用不到的；（建了索引又不用，反而占内存空间）