MySQL优化方法

MySQL优化方法

Scroll Down
小提示,本文编写于  826  天前,最后编辑于  505  天前,某些信息可能有些出入,仅供参考。


MySQL的优化,大体上可以分为此三种:索引的优化,sql语句的优化,表的优化。

索引优化

建索引的几个原则

  • 选择唯一性索引
    唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话,可能存在同名现象,从而降低查询速度。
  • 为经常需要排序、分组和联合操作的字段建立索引
    经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效地避免排序操作。
  • 为常作为查询条件的字段建立索引
    如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建立索引,可以提高整个表的查询速度。
  • 限制索引的数目
    索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。
  • 尽量使用数据量少的索引
    如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
  • 尽量使用前缀来索引
    如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。
  • 删除不再使用或者很少使用的索引
    表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。
  • 最左前缀匹配原则,非常重要的原则。
    mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
  • =和in可以乱序。
    比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
  • 尽量选择区分度高的列作为索引。
    区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就 是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条 记录
  • 索引列不能参与计算,保持列“干净”。
    比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
  • 尽量的扩展索引,不要新建索引。
    比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可
  • 当单个索引字段查询数据很多,区分度都不是很大时,则需要考虑建立联合索引来提高查询效率

注意:选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则,但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断,选择最合适的索引方式。

1.索引

一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的也是最容易出现问题的,还是一些复杂的查询操作,因此对查询语句的优化是重中之重,加速查询最好的方法就是索引。

索引:简单的说,相当于图书的目录,可以帮助用户快速的找到需要的内容。

在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。

总结:索引的目的在于提高查询效率,与我们查询图书所用的目录是一个道理:先定位到章,然后定位到该章下的一个小结,然后找到页数。相似的例子还有:查字典,查地图等。

2.索引类型

普通索引

是最基本的索引,它没有任何限制。

唯一索引

与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

组合索引

指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。

主键索引

是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引

全文索引

主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。

3.索引优化

只要列中含有NULL值,就最好不要在此例设置索引,复合索引如果有NULL值,此列在使用时也不会使用索引
尽量使用短索引,如果可以,应该制定一个前缀长度
对于经常在where子句使用的列,最好设置索引,这样会加快查找速度
对于有多个列where或者order by子句的,应该建立复合索引
对于like语句,以%或者‘-’开头的不会使用索引,以%结尾会使用索引
尽量不要在列上进行运算(函数操作和表达式操作)
尽量不要使用not in和<>操作

SQL慢查询的优化

1.如何捕获低效sql

1)slow_query_log

这个参数设置为ON,可以捕获执行时间超过一定数值的SQL语句。

2)ong_query_time

当SQL语句执行时间超过此数值时,就会被记录到日志中,建议设置为1或者更短。

3)slow_query_log_file

记录日志的文件名。

4)log_queries_not_using_indexes

这个参数设置为ON,可以捕获到所有未使用索引的SQL语句,尽管这个SQL语句有可能执行得挺快。

2.慢查询优化的基本步骤

1)先运行看看是否真的很慢,注意设置SQL_NO_CACHE

2)where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高

3)explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)

4)order by limit 形式的sql语句让排序的表优先查

5)了解业务方使用场景

6)加索引时参照建索引的几大原则

7)观察结果,不符合预期继续从1开始分析

3.优化原则

  1. 查询时,能不要 * 就不用 * ,尽量写全字段名
  2. 大部分情况连接效率远大于子查询
  3. 多使用explain和profile分析查询语句
  4. 查看慢查询日志,找出执行时间长的sql语句优化
  5. 多表连接时,尽量小表驱动大表,即小表 join 大表
  6. 在千万级分页时使用limit
  7. 对于经常使用的查询,可以开启缓存
  8. 数据库表优化
  9. 表的字段尽可能用NOT NULL
  10. 字段长度固定的表查询会更快
  11. 把数据库的大表按时间或一些标志分成小表
  12. 将表拆分
  13. 数据表拆分:主要就是垂直拆分和水平拆分。
  • 水平切分:将记录散列到不同的表中,各表的结构完全相同,每次从分表中查询, 提高效率。
  • 垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。

总之:

Mysql的优化主要就在于

  1. 索引的优化
  2. sql语句的优化
  3. 表的优化
  4. 在高并发网络环境下,除了优化数据库外
  5. 还会涉及到分布式缓存,CDN,数据库读写分离等高并发优化技术。

我们必须考虑用户输入的各种情况,我们必须认定用户输入的数据都是不安全的,我们都需要对用户输入的数据进行过滤处理。

Navicat远程连接mysql很慢

远程连接的时候刚开始很快,用着用着就打开一个表就很慢

解决方法

修改navicat保持连接间隔时间为60 甚至是30S即可

navicat