mysql性能优化总结

看完高性能mysql这本书，对mysql性能优化有了更加深刻全面的了解，下面从以下几个方面总结如何优化Mysql查询性能：

---

一、在创建数据库表时选择合适的列.

我们在创建数据库表时所选择的列尽量满足一下几个条件：
1. 选择存储数据的最小数据类型。这样占用的内存最小，消耗的CPU资源最少。
2. 选择简单的数据类型。简单的数据类型通常需要最少的CPU周期，比如整型比字符串类型操作代价更低，这是因为字符集和校对规则使得字符创更加复杂，在选择时间列时使用mysql内建内型而不是使用字符串存储日期。
3. 每个列尽量设置为not null属性，这是因为null占用内存，而且影响索引的性能

二、选择合适的索引

创建高性能mysql索引，索引的顺序，以及索引的最左前缀都会影响查询时的性能，如果你使用的是多列索引，这时就会有几个限制条件，mysql总是先执行最左边的索引列，且不能跳过这个列、在有范围查找时此列后面的索引失效，所以索引的数量和顺序至关重要。如果选择innodb作为Mysql默认的存储引擎，此时默认使用b+tree这种数据结构存储索引，除了保存索引之外，也保存了需要查找的数据。创建高性能索引策略：
1. 选择独立的列
独立的列指的是索引列放在表达式的一侧，也不能是函数的一部分，错误的表达式有：

select id , name form test where id +1 = 5;//mysql是无法解析id + 1的
select ... where TO_DAYS(current_day) - TO_DAYS(yestoday) = 12;

2. 前缀索引和索引的选择性
mysql不允许blob和text类型或者很长的varchar类型的完整字段作为索引，必须使用前缀索引，节约索引空间，从而提高索引性能，这就涉及到索引的选择性了，索引的选择性是指：不重复的索引值（被称为基数）和数据表的记录总数（T）的比这个值在1/t到1之间，这个值越大表示索引的选择性越高，查询的性能越好，唯一索引的索引选择性为1，索引的它的性能最优。

计算前缀的选择性其中一个办法就是计算完整列的选择然后选择和完整列最接近的前缀列的选择性；
select count(distinct city_name) / count(*) from city;
select count(distinct left(city_name,3) ) / count(*) form city;
select count(distinct left(city_name,4) ) / count(*) form city;
通过改变索引列的前缀长度选择最接近完整列的索引选择性，这样的性能最优。
可以通过以下sql来设置前缀索引
alter table city add key(city(4))

3.多列索引的创建
我们经常会听到有人说为where条件中的每个列都创建一个单独索引，这样的建议是错误的，在老的mysql版本中会使用全表扫面，在mysql5.0以后会使用索引合并的策略，比如下面的查询

通过执行计划在extra列可以看到使用了联合索引
![https://img-blog.csdn.net/20180429192228575?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNjYxMDQ0/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70]

msyql使用了索引合并策略，这说明你的索引建立的并不好，当你需要进行or 、 and操作时需要注意

• 当有多个and条件做相交操作的时，通常需要建立一个包含多个列的索引。
• 当有多个or操作做联合索引时，需要消耗大量的CPU和内存资源，在合并扫描返回的大量数据时并不高效，所以尽量避免使用or操作，或者将or操作改为union操作。

4、索引的顺序

• 当不考虑分组和排序时，将选择性最高的列放在索引最前列
• 索引选择性大的前缀索引
• 根据实际情况来

5、覆盖索引
如果一个索引包含要查询的所有字段的值，我们就成为覆盖索引，这样mysql可以使用索引来直接获取列的数据，不需要再读取数据行。（索引的叶子节点包含了所有需要查询的字段的值）

tips: 如何优化查询分页查询，如果我们需要查询1000页以后的数据，那么此时mysql需要丢弃1000页之前的数据，这样做的话会造成资源的浪费，所以可以通过延迟关联的方法去优化，延迟关联是指通过查询主键，然后通过主键关联原表，可以减少mysql需要丢弃的行数。

select <cols> from city inner join
    (select <primary key > from person where sex='M' order by age limit 10000,10) 
as x(using primary key);