一、建索引的一些原则
1. 先存数据，再建索引（有索引的好处是搜索比较快但是在有索引的前提下进行插入、更新操作会很慢）
2. 不要对规模小的数据表建立索引，数据量超过300的表应该有索引
3. 当对表的查询操作比更新操作频率更高时，对该表建立索引
4. 在不同值较少的字段上不必要建立索引，如性别字段
5. 对于查询操作中频繁使用的列建立索引，不对很少或从来不作为查询条件的列建立索引
6. 表的主键、外键必须有索引
7. 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引
8. 索引应该建在选择性高的字段上；在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引
9. 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引
10. 索引并不是越多越好，索引固然可 以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要
11. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
二、避免全表扫描的方法
1. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
1）不能用null作索引，任何包含null值的列都将不会被包含在索引中
2. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
1）MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE
3. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
1）可以使用union all来代替or条件
4. in 和 not in 也要慎用，否则会导致全表扫描
1）对于连续的数值，能用 between 就不要用 in 了
5. 模糊查询的 abc% 可以用索引，但是%abc,和%abc%就不能使用索引，只能全表扫描了
6. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引
7. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致
8. 很多时候用 exists 代替 in 是一个好的选择
三、Mysql性能优化经验
1. 为查询缓存优化你的查询
1）SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而 开启缓存
2. EXPLAIN 你的 SELECT 查询
1）使 用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈
2）EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的（还有比如查询了多少行）
3. 当只要一行数据时使用 LIMIT 1
4. 为搜索字段建索引
1） 索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧
5. 在Join表的时候使用相当类型的列，并将其索引
1）如果你的应用程序有很多JOIN查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。而且，这些被用来Join的字 段，应该是相同的类型的
2）如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）
6. 千万不要 ORDER BY RAND()
1）MySQL会不得不去执行RAND()函数 （很耗CPU时间），而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序）
7. 避免 SELECT *
1）从数据库里读出越多的数据，那么查询就会变得越慢
2）并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载
8. 永远为每张表设置一个ID
1）应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的 AUTO_INCREMENT标志（聚簇索引）
9. 使用 ENUM 而不是 VARCHAR
1）ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串
10. 从 PROCEDURE ANALYSE() 取得建议 （待求证）
11. 固定长度的表会更快
12. 拆分大的 DELETE 或 INSERT 语句
13. 越小的列会越快
14. 选择正确的存储引擎
15. 使用一个对象关系映射器（Object Relational Mapper）
四、MySQL性能优化之参数配置
1. 性能瓶颈定位
1）show命令
（1）MySQL服务器配置信息mysql show variables
（2）查看MySQL服务器运行的各种状态值 show global status
2）慢查询日志
（1）慢查询日志开启/关闭 show variables like '%slow_query_log%'
（2）超过多少时间记录 show variables like '%long_query_time%'
3）explain分析查询
（1）可以看到sql执行计划，根据type,rows等指标来分析
4）profiling分析查询（更详细，调试功能？）
（1）查看开关设置 show variables like '%profiling%'
2. 配置优化
1）连接请求的变量
（1）max_connections MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量
a. show variables like 'max_connections' 最大连接数
b. show status like 'max_used_connections' 响应的连接数
c. max_used_connections / max_connections * 100% （理想值≈ 85%）
（2）back_log MySQL能暂存的连接数量
a. 如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源
（3）wait_timeout 指的是MySQL在关闭一个非交互的连接之前所要等待的秒数,默认数值是28800，可调优为7200
a. 如果设置太小，那么连接关闭的很快，从而使一些持久的连接不起作用
b. 如果设置太大，容易造成连接打开时间过长，在show processlist时，能看到太多的sleep状态的连接，从而造成too many connections错误
c. 一般希望wait_timeout尽可能地低
（4）interactive_timeout 指的是mysql在关闭一个交互的连接之前所要等待的秒数，比如我们在终端上进入mysql管理，使用的即使交互的连接
2）缓冲区变量
（1）key_buffer_size 指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度
a. 通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理
b. 比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好
c. 上述状态值可以使用SHOWSTATUS LIKE ‘key_read%’获得
d. 未命中缓存概率 key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%
e. key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值
（2）query_cache_size 使用查询缓冲，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果
a. 一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用查询缓存
b. 两个SQL语句，只要相差哪怕是一个字符（例如大小写不一样；多一个空格等），那么这两个SQL将使用不同的一个CACHE
c. 通过检查状态值’Qcache%’(SHOW STATUS LIKE ‘Qcache%’)，可以知道query_cache_size设置是否合理
d. 相关值
I. Qcache_free_blocks 缓存中相邻内存块的个数。如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块
II. Qcache_free_memory Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了
III. Qcache_hits 表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想
IV. Qcache_inserts 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想
V. Qcache_lowmem_prunes 多少条Query 因为内存不足而被清除出QueryCache
VI. Qcache_not_cached 不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数
VII. Qcache_queries_in_cache 当前Query Cache 中cache 的Query 数量
VIII. Qcache_total_blocks 当前Query Cache 中的block 数量
e. query_cache的配置 (show variables like "%query_cache%")
I. query_cache_limit 超过此大小的查询将不缓存
II. query_cache_min_res_unit 缓存块的最小大小 ，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费
III. query_cache_size 查询缓存大小 (注：QC存储的最小单位是1024 byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)
IV. query_cache_type 缓存类型, 0 不缓存，1 将会缓存所有的结果，2 则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询
（3）max_connect_errors 是一个MySQL中与安全有关的计数器值
a. 它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况,当超过指定次数，MYSQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息
b. max_connect_errors的值与性能并无太大关系
（4）sort_buffer_size 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUPBY操作
a. Sort_Buffer_Size 是一个connection级参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存
b. Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源
（5）max_allowed_packet MySQL根据配置文件会限制Server接受的数据包大小
a. 有时候大的插入和更新会受 max_allowed_packet 参数限制，导致写入或者更新失败。最大值是1GB，必须设置1024的倍数
（6）join_buffer_size 用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享
（7）thread_cache_size 服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量
a. 当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取
b. 如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能
c. 增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用
d. 设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)
e. show status like "threads_%" 查看线程连接状态相关的信息
3）配置InnoDB的几个变量
（1）innodb_buffer_pool_size InnoDB使用该参数指定大小的内存来缓冲数据和索引
a. 对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样
b. 最大可以把该值设置成物理内存的80%。根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了
（2）innodb_thread_concurrency 此参数用来设置innodb线程的并发数量，默认值为0表示不限制
a. 若要设置则与服务器的CPU核数相同或是cpu的核数的2倍，建议用默认设置，一般为8
（3）innodb_log_buffer_size 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小
（4）innodb_log_file_size 此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.
（5）read_buffer_size MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区
a. 如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能
b. 和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享
（6）read_rnd_buffer_size MySql 的随机读（查询操作）缓冲区大小
a. 当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值
b. 但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大
c. 顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的
（7）bulk_insert_buffer_size 批量插入数据缓存大小，可以有效提高插入效率，默认为8M
参考网址
注：文章是经过参考其他的文章然后自己整理出来的，有可能是小部分参考，也有可能是大部分参考，但绝对不是直接转载，觉得侵权了我会删，我只是把这个用于自己的笔记，顺便整理下知识的同时，能帮到一部分人。
ps : 有错误的还望各位大佬指正,小弟不胜感激
扫一扫
2243
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
