gyts62 发表于 2018-10-10 06:15:43

mysql性能优化-索引优化

  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   表名” 或者 “showkeysfrom表名” //查询一个表有哪些索引
  索引的指针类型于书本的目录页码。
  做一个简单测试,假如我们创建了一个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

页: [1]
查看完整版本: mysql性能优化-索引优化