MySQL优化要点-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

MySQL优化要点

简介:

MySQL优化要点

 

 

  • 硬件选型优化

  • CPU

主频高,支持多线程

  1. 1.2、  磁盘

SAS磁盘(RAID10)或SSD磁盘(RAID1)

  1. 1.3、    主板

修改主板配置参数,打开磁盘写缓存,关闭磁盘读缓存,以免影响数据插入速度;尽量使用独立RAID,不用主板集成RAID卡

  1. 1.4、      网卡

主机多网卡BOND绑定,保证网络无故障;配备千兆速率以上网卡设备。

  1. 1.5、    内存

配备多通道高频内存,容量至少16G以上,最好是数据库数据内存两倍。

 

 

 

  • 部署以及配置参数优化

  • 部署规范

统一软件部署存放路径、编译参数等;数据库数据文件存放路径名称统一规范。

  1. 2.2、  数据存储

每个实例需要单独磁盘分区或使用LVM分区亦可。

  1. 2.3、  存储引擎

MySQL常用的存储引擎主要有MyISAM和InnoDB

  1. 2.3.1MyISAM

不是事务安全的,也不支持外键。如果事物回滚将造成不完全回滚,不具有原子性。如果执行大量的SELECTMyISAM是更好的选择。MyISAM的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少。能加载更多索引。表格可以被压缩,而且它们支持全文搜索。读性能较强整表锁。对于select count(*) from table 的操作,MyISAM有单独保存表行数,不需读表,可以直接读取。

 

  1. 2.3.2InnoDB

事务安全的、支持外键。你的数据执行大量的INSERTUPDATE,出于性能方面的考虑,应该使用InnoDBInnodb是索引和数据是紧密捆绑的,没有使用压缩从而会造成InnodbMyISAM体积庞大不小。写性能较强行级锁。不保存表的具体行数,所以selectcount(*) from table 是全表扫描。但是当count(*)语句包含 where条件时与MyISAM没有区别。

 

 

  1. 2.4、  配置参数优化

 

  1. 4.1         max_connections

建议值:  3000

说明:MySQL允许最大的进程连接数,如果经常出现Too Many Connections的错误提示,则需要增大此值。

 

  1. 4.2         skip-external-locking

说明:跳过外部锁定; 外部锁定(external-locking)起作用时,每个进程若要访问数据表,则必须等待之前的进程完成操作并解除锁定。由于服务器访问数据表时经常需要等待解锁,因此在单服务器环境下external locking会让MySQL性能下降。所以在很多Linux发行版的源中,MySQL配置文件中默认使用了skip-external-locking来避免external locking。

如果是多服务器环境,希望打开external locking特征,则注释掉这一行即可

#skip-external-locking

如果是单服务器环境,则将其禁用即可,使用如下语句

skip-external-locking

 

  1. 4.3         skip-name-resolve

说明:skip-name-resolve是禁用dns解析,启用后,在mysql的授权表中就不能使用主机名,只能使用IP;如果你有一个很慢的DNS和许多主机,你可以通过用--skip-name-resolve禁用DNS查找或增加HOST_CACHE_SIZE定义(默认值:128)并重新编译mysqld来提高性能。

  1. 4.4         max_allowed_packet

建议值:32M

说明:设置在网络传输中一次消息传输量的最大值。系统默认值 为1MB,最大值是1GB,必须设置1024的倍数。

 

  1. 4.5         wait_timeout

建议值:28800

说明:结束回话等待时间

 

  1. 4.6         thread_cache_size

建议值: 64

说明:服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能.通过比较Connections Threads_created状态的变量,可以看到这个变量的作用。设置规则如下:1GB 内存配置为82GB配置为163GB配置为324GB或更高内存,可配置更大。

 

  1. 4.7         explicit_defaults_for_timestamp

建议值:true

说明:明确的默认timestamp行为属性;即开启此参数,默认不会为timestamp列强制非NULL,或设置默认当前timestamp属性。

 

  1. 4.8         query_cache_limit

建议值: 4M

说明:指定单个查询能够使用的缓冲区大小,缺省为1M

 

  1. 4.9         query_cache_min_res_unit

建议值:2k

说明:默认是4KB,设置值大对大数据查询有好处,但如果你的查询都是小数据查询,就容易造成内存碎片和浪费。

  • 查询缓存碎片率 =Qcache_free_blocks / Qcache_total_blocks * 100%;如果查询缓存碎片率超过20%,可以用FLUSHQUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。

  • 查询缓存利用率 =(query_cache_sizeQcache_free_memory) / query_cache_size * 100%;查询缓存利用率在25%以下的话说明query_cache_size设置的过大,可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes>50的话说明query_cache_size可能有点小,要不就是碎片太多。

  • 查询缓存命中率=(Qcache_hitsQcache_inserts) / Qcache_hits * 100%

 

  1. 4.10     tmp_table_size

建议值:128M

说明:tmp_table_size的默认大小是 32M。如果一张临时表超出该大小,MySQL产生一个 The table tbl_name is full 形式的错误,如果你做很多高级 GROUPBY查询,增加tmp_table_size值。如果超过该值,则会将临时表写入磁盘。

 

  1. 4.11     thread_concurrency

建议值:32

说明:设置thread_concurrency的值的正确与否,mysql的性能影响很大,在多个cpu(或多核)的情况下,错误设置了thread_concurrency的值,会导致mysql不能          充分利用多cpu(或多核),出现同一时刻只能一个cpu(或核)在工作的情况。                      thread_concurrency应设为CPU核数的2倍比如有一个双核的CPU,那么thread_concurrency的应该为4;2个双核的cpu,thread_concurrency的值应为8

 

  1. 4.12     open_files_limit

建议值:10240

说明:每个客户端的连接也是一个文件句柄。

 

  1. 4.13     back_log

建议值:2048

说明:MYSQL暂时停止响应新请求之前,短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的监听队列的大小。

 

  1. 4.14     innodb_flush_log_at_trx_commit

建议值:1

说明:每多少次commit 日志缓存中的数据刷到磁盘中。为了提高事务安全性,我们建议没提交一次就刷新缓存到磁盘,但这会大量消耗IO,需要在实际运用中去均衡考虑这个参数设置。

 

  1. 4.15     innodb_lock_wait_timeout

建议值:50

说明:InnoDB事务在被回滚之前可以等待一个锁定的超时秒数;InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务;InnoDBLOCKTABLES语句注意到锁定设置。默认值是50秒。

 

  1. 4.16     innodb_auto_extend_increment

建议值:64M

说明:Innodb存储引擎表,每次自动扩展空间分配区的大小,加大此值可帮助降低碎片,但可能造成空间浪费。

 

  1. 4.17     innodb_buffer_pool_size

建议值:8192M

说明:这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的key_buffer_size设置下运行的可以,然而Innodb在默认的innodb_buffer_pool_size设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达70-80%的可用内存。一些应用于key_buffer的规则有—如果你的数据量不大,并且不会暴增,那么无需把innodb_buffer_pool_size设置的太大了。

 

  1. 4.18     innodb_log_buffer_size

建议值:16M

说明:此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,但意外的故障将会丢失数据。

 

  1. 4.19     innodb_buffer_pool_instances

建议值:8

说明:用于优化更高并发的负载。

 

  1. 4.20     innodb_log_file_size

建议值:60M

说明:此参数确定数据日志文件的大小,以M为单位,更大的设置可以提高性能,但也会增加恢复故障数据库所需的时间。

 

  1. 4.21     innodb_log_files_in_group

建议值:3M

说明:为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3M

 

  1. 4.22     innodb_io_capacity

建议值:400

说明:InnoDB曾经在代码里写死了假设服务器运行在每秒100I/O操作的单硬盘上。默认值很糟糕。现在可以告诉InnoDB服务器有多大的I/O能力。有时需要把这个值设置得相当高(像SSD这样极快的存储设备上需要设置为上万)才能稳定地刷新脏页。

 

  1. 4.23     innodb_write_io_threads

建议值:4

说明:文件IO的线程数,一般为 4,但是在 Windows 下,可以设置得较大。

 

  1. 4.24     innodb_read_io_threads

建议值:4

说明:文件IO的线程数,一般为 4,但是在 Windows 下,可以设置得较大。

 

  1. 4.25     innodb_thread_concurrency

建议值:8

说明:服务器有几个CPU就设置为几,建议用默认设置,一般为8

 

  1. 4.26     innodb_file_per_table

建议值:ON

说明:其默认值在mysql5.6.6+后为ON。开启此选项后,关于InnoDB表的数据和索引单独存储在自己的表空间中(.ibd结尾的文件)。否则,存储在系统的表空间中(ibdata)。

 

  1. 4.27     innodb_print_all_deadlocks

建议值:1

说明:死锁信息打印到错误日志里。

 

  1. 4.28     innodb_buffer_pool_dump_at_shutdown

建议值:1

说明:在关闭时把热数据dump到本地磁盘。

 

  1. 4.29     innodb_buffer_pool_dump_now

建议值:1

说明:采用手工方式把热数据dump到本地磁盘。

 

  1. 4.30     innodb_buffer_pool_load_at_startup

建议值:1

说明:在启动时把热数据加载到内存。

 

  1. 4.31     innodb_buffer_pool_load_now

建议值:1

说明:采用手工方式把热数据加载到内存。

 

  1. 4.32     table_open_cache

建议值:4096

说明:从官方文档看出在MySQL5.6.8+开始默认值为2000,就能简单的判断出原来默认值是不够的。可以通过观察Opened_tables其值及其一段时间的变化来检查该变量。如果看到Opened_tables的值很大并且又不经常执行FLUSH TABLES          (执行其命令强制所有的表重新关闭且打开),那么可能你应该增加该变量的值。

 

  1. 4.33     max_connect_errors

建议值:1000

说明:设置每个主机的连接请求异常中断的最大次数,当超过该次数,MYSQL服务器将禁止host的连接请求,直到mysql服务器重启或通过flushhosts命令清空此host的相关信息。

 

  1. 4.34     expire_logs_days

建议值:7

说明:如果启用了二进制日志,应该打开这个选项,可以让服务器在指定的天数之后清理旧的二进制日志。如果不启用,最终服务器的空间会被耗尽,导致服务器卡住或崩溃。

 

  1. 4.35     sort_buffer_size

建议值:1M

说明:Sort_Buffer_Size是一个connection级参数,在每个connectionsession)第一次需要使用这个buffer的时候,一次性分配设置的内存。Sort_Buffer_Size并不      是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。例如:500个连接将会消耗500*sort_buffer_size(8M)=4G内存,Sort_Buffer_Size超过2KB的时候,就会使用mmap()而不是malloc() 来进行内存分配,导致效率降低。

 

  1. 4.36     join_buffer_size

建议值:8M

说明:用于表间关联缓存的大小,和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。

 

  1. 4.37     read_buffer_size

建议值:1M

说明:MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。

 

  1. 4.38     read_rnd_buffer_size

建议值:16M

说明:MySql的随机读(查询操作)缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。

 

  1. 4.39     myisam_sort_buffer_size

建议值:256M

说明:MyISAM表发生变化时重新排序所需的缓冲。

 

  1. 4.40     bulk_insert_buffer_size

建议值:64M

说明:批量插入数据缓存大小,可以有效提高插入效率,默认为8M

 

  1. 4.41     key_buffer_size

建议值:1024M

说明:批定用于索引的缓冲区大小,增加它可以得到更好的索引处理性能,对于内存在4GB左右的服务器来说,该参数可设置为256MB384MB

 

  1. 4.42     myisam_max_sort_file_size

建议值:10G

说明:MySQL重建索引时所允许的最大临时文件的大小 ( REPAIR, ALTER TABLE或者 LOAD DATA INFILE)如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)

 

 

#-------------------mysql5.6的主从增加了不少参数,提升了主从同步的安全和效率,以下主从参数详解--------------------#

 

  1. 4.43     server-id

取值:1~n

说明:MySQL服务器标识号,在主从复制架构中用来唯一标识一个服务器,避免多向复制过程中出现混乱。

 

  1. 4.44     binlog-format

建议值:ROW

说明:Binlog记录方式,以前版本是仅支持statement(语句级别),这种模式,易造成跨库(模式)时,数据混乱问题;故后来在5.x版本呢引进ROW模式,这样就针对具体的行来记录,避免复制过程出现数据安全问题。

 

  1. 4.45     log-bin

建议值:master-bin.log

说明:Binlog文件名,建议以标识属于哪个主机或模块的名称,便于识别。

  1. 4.46     log-bin-index

建议值:master-bin.index

说明:记录log-bin文件名的索引,便于MySQL服务器复制进程检索,读取log-bin文件。

 

  1. 4.47     log-slave-updates

建议值:true

说明:指导主从复制架构中从服务器,是否要记录从主服务器传递来的数据变更;开启则,可用于多级复制架构;为了避免复制混乱,可通前面几个参数(server_id,log-bin,log-bin-index)结合,来唯一标识事务,并记录binlog,进行级联复制。

 

  1. 4.48     gtid-mode

建议值:on

说明:2.4.48

 

  1. 4.49     enforce-gtid-consistency

建议值:true

说明:这两个参数是启用mysql5.6中的UUID同步模式,两个参数必须一起打开,否则报错,slave在做同步复制时,无须找到binlog日志和POS点,直接change        master tomaster_auto_position=1即可,自动找点同步。

GTID的局限性:(鉴于这些局限性,慎用,因此做了关闭处理)

  • CREATE TABLE...SELECT语句不支持。因为该语句会被拆分成createtableinsert两个事务,并且这个两个事务被分配了同一个GTID,这会导致insert         备库忽略掉。

  • 不支持CREATETEMPORARY TABLEDROP TEMPORARY TABLE 临时表操作。

 

  1. 4.50     master-info-repository

建议值:TABLE

说明:主复制服务器信息记录容器,放表里安全,便捷,效率高。

 

  1. 4.51     relay-log-info-repository

建议值:TABLE

说明:延迟log信息记录容器,放表里安全,便捷,效率高。

 

  1. 4.52     sync-master-info

建议值:1

说明:同步主服务器信息。

 

  1. 4.53     slave-parallel-workers

建议值:2

说明:默认是0,不开启,最大并发数为1024个线程。主从复制启用4sql线程,提高从服务器吞吐量,减少延迟,使用并发的 SQL 线程对不同数据库并行应用事件。

 

  1. 4.54     binlog-checksum

建议值:CRC32

说明:Binlog文件校验算法。

 

  1. 4.55     master-verify-checksum

建议值:1

说明:主复制服务器验证。

 

  1. 4.56     slave-sql-verify-checksum

建议值:1

说明:从服务器验证。

 

  1. 4.57     binlog-rows-query-log-events

建议值:1

说明:这四个参数是启用binlog/relaylog的校验,防止日志出错。

 

  1. 4.58     sync_binlog

建议值:1

说明:默认情况下,并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃,有可能binlog中最后的语句丢失了。要想防止这种情况,你可以使用sync_binlog全局变量(1是最安全的值,但也是最慢的),使binlog在每Nbinlog写入后与硬盘同步。即使sync_binlog设置为1,出现崩溃时,也有可能表内容和binlog内容之间存在不一致性。如果使用InnoDB表,MySQL服务器处理COMMIT语句,它将整个事务写入binlog并将事务提交到InnoDB中。如果在两次操作之间出现崩溃,重启时,事务被InnoDB回滚,但仍然存在binlog中。可以用--innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。(注释:在MySQL5.1中不需要--innodb-safe-binlog;由于引入了XA事务支持,该选项作废了),该选项可以提供更大程度的安全,使每个事务的binlog(sync_binlog=1)(默认情况为真)InnoDB日志与硬盘同步,该选项的效果是崩溃后重启时,在滚回事务后,MySQL服务器从binlog剪切回滚的InnoDB事务。这样可以确保binlog反馈InnoDB表的确切数据等,并使从服务器保持与主服务器保持同步(不接收回滚的语句)

 

  1. 4.59     relay_log_purge

建议值:1

说明:2.4.59

 

  1. 4.60     relay_log_recovery

建议值:1

说明:这两个是启用relaylog的自动修复功能,避免由于网络之类的外因造成日志损坏,主从停止。

 

  1. 4.61     slave-skip-errors

建议值:1062

说明:从复制服务器可跳过多少错误,而不造成复制中断,有利有弊,跳过可能造成主从数据不一致。

 

  1. 4.62     binlog_cache_size

建议值:4M

说明:binglog缓存区大小。

 

  1. 4.63     max_binlog_cache_size

建议值:2G

说明:指定binary log缓存的最大容量,如果设置的过小,则在执行复杂查询语句时MySQL会出错。

 

  1. 4.64     max_binlog_size

建议值:1G

说明:指定binary log文件的最大容量,默认为1GB

 

 

  • 建表优化

  • 字符集的选择

如果确认全部是中文,不会使用多语言以及中文无法表示的字符,那么GBK是首选。采用UTF-8编码会占用3个字节,而GBK只需要2个字节,为了统一各系统字符集使用,避免跨库字符混乱,我们统一使用UTF-8

 

 

3.2主键

尽可能使用长度短的主键,系统的自增类型AUTO_INCREMEN, 而不是使用类似uuid()等类型。如果可以使用外键做主键,则更好。比如11的关系,使用主表的id作为从表的主键。

主键的字段长度需要根据需要指定。

tinyint 27次方-1 -128  127

smallint 215次方-1 -32768  32767

mediumint表示为 223次方-1:从 -8388608 8388607

int表示为 231次方-1

bigint表示为 263次方-1

在主键上无需建单独的索引,因为系统内部为主键建立了聚簇索引。 
允许在其它索引上包含主键列。

 

 

3.3  外键

外键会影响插入和更新性能,对于批量可靠数据的插入,建议先屏蔽外键检查。对于数据量大的表,建议去掉外键,改由应用程序进行数据完整性检查。尽可能用选用对应主表的主键作作为外键,避免选择长度很大的主表唯一键作为外键,外键是默认加上索引的。

 

 

3.4索引优化建

 被索引的字段的长度越小,该索引的效率越高

 被索引的字段中,值的重复越少, 该索引的效率越高

 查询语句中,如果使用了“group”子句, 根据其中字段出现的先后顺序建立多字段索引

 查询语句中,如果使用了“distinct”,根据其中字段出现的先后顺序建立多字段索引

 “where”子句中,出现对同一表中多个不同字段的“and”条件时,按照字段出现的先后顺序建立多字段索引

 “where”子句中,出现对同一表中多个不同字段的“or”条件时,对重复值最少的字段建立单字段索引

 进行“内/外连接”查询时,对“连接字段”建立索引

 对“主键”的“unique”索引毫无意义,不要使用。对于一个Primary Key的列MySQL已经自动对其建立了Unique Index,无需重复再在上面建立索引了

 被索引字段尽可能的使用“NOT NULL”属性

 对写入密集型表,尽量减少索引,尤其是“多字段索引”和“unique”索引

 MySQL只会使用前缀,例如key(a, b) …where b=5将使用不到索引。

 控制单个索引的长度。使用key(name(8))在数据的前面几个字符建立索引

 相近的键值比随机好。Auto_increment就比uuid好。

  • 联合索引

  • 在特定查询里,联合索引的效果高于多个单一索引,因为当有多个索引可以使用时,MySQL只能使用其中一个。

  • 在查询里,同时用到了联合索引包含的前几个列名,都会使用到联合索引,否则将部分或不会用到。比如我们有一个firstnamelastnameage列上的多列索引,我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时,MySQL将使用fname_lname_age索引:

firstnamelastnameage 
firstname
lastname 
firstname 
从另一方面理解,它相当于我们创建了(firstnamelastnameage)(firstnamelastname)以及(firstname)这些列组合上的索引。

  • 索引长度

对于CHAR或者Varchar的列,索引可以根据数据的分布情况,用列的一部分参与创建索引。 create index idx_t_main on t_main(name(3)); 这里就是指定name的前三个字符参与索引,而不是全部最大允许的长度为1000个字节,对已GBK编码则是500个汉字

  • 以下情况适合于创建索引

  • 在经常需要搜索的列上,可以加快搜索的速度

  • 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构

  • 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度 

  • 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的 

  • 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间

  • 在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。 

  • 以下的情况下不适合创建索引

  • 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。 

  • 对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

  • 对于那些定义为text, imagebit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。 如果表数据很少,比如每个省按市做汇总的表,一般低于2000,且数据量基本没有变化。此时增加索引无助于查询性能,却会极大的影响更新性能。 

    当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当对修改性能的要求远远大于检索性能时,不应该创建索引。

 

 

 

 

  • SQL语句优化

  • 多多利用 “explain”查询索引使用情况,以便找出最佳的查询语句写法和索引设置方案

  • 慎用 “select *”,查询时只选出必须字段

  • 查询使用索引时,所遍历的索引条数越少,索引字段长度越小, 查询效率越高 (可使用 “explain”查询索引使用情况)

  • 避免使用 mysql函数对查询结果进行处理,将这些处理交给客户端程序负责

  • 使用 “limit”时候, 尽量使“limit” 出的部分位于整个结果集的前部, 这样的查询速度更快, 系统资源开销更低

  • 在 “where”子句中使用多个字段的 “and”条件时, 各个字段出现的先后顺序要与多字段索引中的顺序相符

  • 在 “where”子句中使用 “like”时, 只有当通配符不出现在条件的最左端时才会使用索引

  • 在 mysql 4.1以上版本中, 避免使用子查询, 尽量使用 “内/外连接”实现此功能

  • 减少函数的使用,如果可能的话, 尽量用单纯的表达式来代替

  • 避免在 “where”子句中, 对不同字段进行“or” 条件查询, 将其拆分成多个单一字段的查询语句效率更高

  • 查询时使用匹配的类型。例如select * from awhere id=5,如果这里id是字符类型,同时有index,这条查询则使用不到index,会做全表扫描,速度会很慢。正确的应该是… where id=”5”,加上引号表明类型是字符

  • 在海量查询时尽量少用格式转换

  • ORDER BY和GROPU BY使用ORDER BY和GROUPBY短语,任何一种索引都有助于SELECT的性能提高

  • 任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边

  • IN、OR子句常会使用工作表,使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引

  • 只要能满足你的需求,应尽可能使用更小的数据类型:例如使用MEDIUMINT代替INT

  • 尽量把所有的列设置为NOT NULL,如果你要保存NULL,手动去设置它,而不是把它设为默认值



     本文转自aaron428 51CTO博客,原文链接:http://blog.51cto.com/aaronsa/1741486,如需转载请自行联系原作者






版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章