mysql性能优化-索引优化

gyts62

　　1、商业需求的影响
　　Myisam存储引擎内置一个计数器，count(*)时直接从计数器读取；而通过innodb存储引擎查找某个数据时，是必须扫描全表的，所以当执行对表的统计（即使用count(*)函数）时，myisam要比innodb要快的很多。所以一般在innodb上执行count(*)时一般要伴随where，且where中要包含主键以外的索引列。
　　2、系统架构及实现的影响
　　以下几类数据不适合存放在数据库：
　　①二进制多媒体数据：包括图片、音频、视频和其他一些相关的二进制文件。
　　②超大文本数据：从5.0.3版本开始，varchar类型最大长度调整到64KB。
　　3、查询语句对性能的影响
　　最大的性能瓶颈就是在于磁盘IO。
　　下面我们通过用explain来查看执行sql语句的行计划

　　还可以打开mysql的profiling 功能，来查看sql的实际执行计划

　　Explain与profiling的区别：
　　Explain只是预计、估计、估算执行这个计划sql语句需要花费多长时间，并没有真正的执行；而profiling则是实际执行了该命令，后者的准确度更高。
　　通过执行“SHOWPROFILE” 命令获取当前系统中保存的多个Query 的profile（配置文件）的概要信息。

　　4、数据库schema(架构)设计对性能的影响
　　5、硬件选择对性能的影响
　　①数据库主机IO性能是优先考虑的，IO性能主要是由磁盘、内存、网卡共同决定。
　　②数据库主机的CPU的处理能力也必须考虑。
　　③还要考虑网络设备（比如说路由器、交换机）。
　　总结：四句话来概括：商业需求合理化、系统架构最优化、逻辑实现精简化、硬件设施理性化。
　　二、MySQL性能优化之-索引
　　说明：索引优化了查询，但是降低了增、改、删的效率。
　　“Show index from   表名” 或者 “show  keys  from  表名” //查询一个表有哪些索引
　　索引的指针类型于书本的目录页码。
　　做一个简单测试，假如我们创建了一个tb1表，向表中插入20000行数据，表的创建和数据插入用如下脚本实现

　　再手动插入一行数据：

　　下面开始测试，查询stuname=’admin’的记录：
　　情况1：stuname列上没有创建索引的情况

　　情况2：stuname列上创建索引的情况再查询

　　总结：在查找stuname="admin"的记录时，如果在stuname上已经建立了索引，MySQL无须任何扫描全表，即准确可找到该记录。相反，MySQL会扫描所有记录。
　　所以在数据库表中，对字段建立索引可以大大提高查询速度。
　　索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。
　　什么是索引？
　　索引能够帮助我们快速定位数据，可以提高查询的速度，它存在的形式是文件。
　　这里主介绍B-tree索引的结构

　　总结：B树索引又称平衡树索引。
　　磁盘块I又称根节点。
　　磁盘块包括数据项和指针。
　　数据项类似于书本章节的标题；
　　指针类似于书本章节所在的页码。
　　真实的数据存放于叶子节点，非叶子节点不存储真实数据，只存储指引搜索方向的数据项和指针。
　　索引对数据的排序只有升序和降序。
　　为什么使用索引？
　　索引可以让mysql高效运行，可以大大提高mysql的查询（包括排序，分组）效率；数据约束（唯一索引的建立）。
　　索引给我带来什么好处？
　　提高查询效率，快速定位数据。
　　使用索引产生的代价？
　　①磁盘的开销
　　②写数据：需要更新索引，对数据库是个很大的开销，降低表更新、添加和删除的速度。
　　不建议使用索引的情况：
　　①数据1000字以下的表不需要建索引
　　②一列中选择性较低的不建索引（比如这一列是性别的，可能的值只有男和女）（所谓索引的选择性，是指不重复的索引值与表记录数的比值，取值范围（0-1）。选择性越高，索引的价值越大）
　　索引的类型？
　　索引包括单列索引和组合索引。
　　说明：单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。
　　1、 普通索引
　　这是最基本的索引，它没有任何限制。

　　2、 唯一索引
　　它与前面的普通索引类似，不同的就是索引列的值必须唯一，但允许空值，空值是指null。如果是组合索引，组合列的值必须唯一

　　主键索引：一种特殊的唯一索引，不允许有空值，一般在建表的时候自动建立主键索引。

　　3、组合索引
　　为了进一步提升MySQL的效率，就要考虑建立组合索引。
　　例如：创建一个表，包含如下字段：

　　将 username, city, age建到一个索引里：

　　说明：如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于组合索引。虽然此时有了三个索引，但MySQL只能用到其中那个它认为似乎是最有效率的单列索引。
　　建立一个这样的组合索引=其实是相当于分别建立了下面三组组合索引：
　　usernname,city,age   usernname,city   username，没有 city，age这样的组合索引。
　　Mysql组合索引遵循“最左前缀”原则。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引。
　　下面的几个SQL就会用到这个组合索引：

　　而下面几个则不会用到:

　　如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。

　　4、 全文索引(用的非常的少)
　　只用于MyISAM表 对文本域进行索引。字段类型包括char、varchar、text
　　不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

　　查看索引：

　　或者

　　在什么情况下建立索引？
　　一般来说，在WHERE和JOIN子句中出现的列需要建立索引。
　　例如：在username上创建索引

　　此时就需要对两个表的userame上建立索引。
　　使用索引的注意事项
　　①在用like模糊查询时，以通配符%和_开头作查询时，MySQL不会使用索引，而是默认全表扫描。
　　例如下句会使用索引：

　　而下句就不会使用:

②不要在有索引的列上进行运算（运算：即使用函数，加、减、乘、除、统计等等）

　　将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

　　本文出自http://zpf666.blog.51cto.com/11248677/1911477