Skip to main content

Home/ Larvata/ Contents contributed and discussions participated by 張 旭

Contents contributed and discussions participated by 張 旭

Simple Middle Filter: All | Bookmarks | Topics
張 旭

Getting Started with MariaDB Galera Cluster - MariaDB Knowledge Base - 0 views

mariadb.com/...ed-with-mariadb-galera-cluster

mysql database cluster mariadb

shared by 張 旭 on 16 Apr 18 - No Cached
  • most users are not going to bootstrap a server by executing "mysqld --wsrep-new-cluster" manually.
  • galera_new_cluster
  • Prerequisites
  • ...7 more annotations...
  • Once you have a cluster running and you want to add/reconnect another node to it, you must supply an address of one of the cluster members in the cluster address URL
  • The new node only needs to connect to one of the existing members
  • It will automatically retrieve the cluster map and reconnect to the rest of the nodes
  • it's better to list all nodes of the cluster so that any node can join a cluster connecting to any other node, even if one or more are down
  • The wsrep_cluster_address parameter should be added in my.cnf of each node, listing all the nodes of the cluster,
  • the minimum recommended number of nodes in a cluster is 3
  • While two of the members will be engaged in state transfer, the remaining member(s) will be able to keep on serving client requests.
張 旭

DNS Records: an Introduction - 0 views

linode.com/...dns-records-an-introduction

dns server networking

shared by 張 旭 on 26 Mar 18 - No Cached
  • reading from right to left
  • top-level domain, or TLD
  • first-level subdomains plus their TLDs (example.com) are referred to as “domains.”
  • ...37 more annotations...
  • Name servers host a domain’s DNS information in a text file called the zone file
  • Start of Authority (SOA) records
  • You’ll want to specify at least two name servers. That way, if one of them is down, the next one can continue to serve your DNS information.
  • Every domain’s zone file contains the admin’s email address, the name servers, and the DNS records.
  • a zone file, which lists domains and their corresponding IP addresses (and a few other things)
  • TLD nameserver
  • ISPs cache a lot of DNS information after they’ve looked it up the first time
  • Usually caching is a good thing, but it can be a problem if you’ve recently made a change to your DNS information
  • An A record matches up a domain (or subdomain) to an IP address
  • point different subdomains to different IP addresses
  • An AAAA record is just like an A record, but for IPv6 IP addresses.
  • An AXFR record is a type of DNS record used for DNS replication
  • used on a slave DNS server to replicate the zone file from a master DNS server
  • DNS Certification Authority Authorization uses DNS to allow the holder of a domain to specify which certificate authorities are allowed to issue certificates for that domain.
  • A CNAME record or Canonical Name record matches up a domain (or subdomain) to a different domain.
  • You should not use a CNAME record for a domain that gets email, because some mail servers handle mail oddly for domains with CNAME records
  • the target domain for a CNAME record should have a normal A-record resolution
  • a CNAME record does not function the same way as a URL redirect
  • A DKIM record or domain keys identified mail record displays the public key for authenticating messages that have been signed with the DKIM protocol
  • An MX record or mail exchange record sets the mail delivery destination for a domain (or subdomain).
  • Ideally, an MX record should point to a domain that is also the hostname for its server.
  • Your MX records don’t necessarily have to point to your Linode. If you’re using a third-party mail service, like Google Apps, you should use the MX records they provide.
  • Lower numbers have a higher priority
  • NS records or name server records set the nameservers for a domain (or subdomain).
  • You can also set up different nameservers for any of your subdomains.
  • The order of NS records does not matter; DNS requests are sent randomly to the different servers, and if one host fails to respond, another one will be queried.
  • A PTR record or pointer record matches up an IP address to a domain (or subdomain), allowing reverse DNS queries to function.
  • PTR records are usually set with your hosting provider. They are not part of your domain’s zone file.
  • An SOA record or Start of Authority record labels a zone file with the name of the host where it was originally created.
  • The administrative email address is written with a period (.) instead of an at symbol (<@>).
  • The single nameserver mentioned in the SOA record is considered the primary master for the purposes of Dynamic DNS and is the server where zone file changes get made before they are propagated to all other nameservers.
  • An SPF record or Sender Policy Framework record lists the designated mail servers for a domain (or subdomain).
  • An SPF record for your domain tells other receiving mail servers which outgoing server(s) are valid sources of email, so they can reject spoofed email from your domain that has originated from unauthorized servers.
  • Your SPF record will have a domain or subdomain, type (which is TXT, or SPF if your name server supports it), and text (which starts with “v=spf1” and contains the SPF record settings).
  • An SRV record or service record matches up a specific service that runs on your domain (or subdomain) to a target domain.
  • A TXT record or text record provides information about the domain in question to other resources on the Internet.
  • One common use of the TXT record is to create an SPF record on nameservers that don’t natively support SPF.
張 旭

redis cluster管理工具redis-trib.rb详解 | 魏子珺的博客 - 0 views

weizijun.cn/...edis-trib-rb%E8%AF%A6%E8%A7%A3

linux redis cache cluster

shared by 張 旭 on 26 Feb 18 - No Cached
  • reshard命令可以在线把集群的一些slot从集群原来slot负责节点迁移到新的节点，利用reshard可以完成集群的在线横向扩容和缩容。
張 旭

How To Use Bash's Job Control to Manage Foreground and Background Processes | DigitalOcean - 0 views

www.digitalocean.com/...round-and-background-processes

linux system server command

shared by 張 旭 on 06 Feb 18 - No Cached
  • Most processes that you start on a Linux machine will run in the foreground. The command will begin execution, blocking use of the shell for the duration of the process.
  • By default, processes are started in the foreground. Until the program exits or changes state, you will not be able to interact with the shell.
  • stop the process by sending it a signal
  • ...17 more annotations...
  • Linux terminals are usually configured to send the "SIGINT" signal (typically signal number 2) to current foreground process when the CTRL-C key combination is pressed.
  • Another signal that we can send is the "SIGTSTP" signal (typically signal number 20).
  • A background process is associated with the specific terminal that started it, but does not block access to the shell
  • start a background process by appending an ampersand character ("&") to the end of your commands.
  • type commands at the same time.
  • The [1] represents the command's "job spec" or job number. We can reference this with other job and process control commands, like kill, fg, and bg by preceding the job number with a percentage sign. In this case, we'd reference this job as %1.
  • Once the process is stopped, we can use the bg command to start it again in the background
  • By default, the bg command operates on the most recently stopped process.
  • Whether a process is in the background or in the foreground, it is rather tightly tied with the terminal instance that started it
  • When a terminal closes, it typically sends a SIGHUP signal to all of the processes (foreground, background, or stopped) that are tied to the terminal.
  • a terminal multiplexer
  • start it using the nohup command
  • appending output to ‘nohup.out’
  • pgrep -a
  • The disown command, in its default configuration, removes a job from the jobs queue of a terminal.
  • You can pass the -h flag to the disown process instead in order to mark the process to ignore SIGHUP signals, but to otherwise continue on as a regular job
  • The huponexit shell option controls whether bash will send its child processes the SIGHUP signal when it exits.
張 旭

Rails Application Templates - Ruby on Rails Guides - 1 views

guides.rubyonrails.org/ils_application_templates.html

rails development template

shared by 張 旭 on 09 Jan 18 - No Cached
  • Application templates are simple Ruby files containing DSL for adding gems/initializers etc. to your freshly created Rails project or an existing Rails project.
  • copy_file will accept relative paths to your template's location
張 旭

An Introduction to HAProxy and Load Balancing Concepts | DigitalOcean - 0 views

www.digitalocean.com/...xy-and-load-balancing-concepts

server haproxy loading HA architecture

shared by 張 旭 on 07 Dec 17 - No Cached
  • HAProxy, which stands for High Availability Proxy
  • improve the performance and reliability of a server environment by distributing the workload across multiple servers (e.g. web, application, database).
  • ACLs are used to test some condition and perform an action (e.g. select a server, or block a request) based on the test result.
  • ...28 more annotations...
  • Access Control List (ACL)
  • ACLs allows flexible network traffic forwarding based on a variety of factors like pattern-matching and the number of connections to a backend
  • A backend is a set of servers that receives forwarded requests
  • adding more servers to your backend will increase your potential load capacity by spreading the load over multiple servers
  • mode http specifies that layer 7 proxying will be used
  • specifies the load balancing algorithm
  • health checks
  • A frontend defines how requests should be forwarded to backends
  • use_backend rules, which define which backends to use depending on which ACL conditions are matched, and/or a default_backend rule that handles every other case
  • A frontend can be configured to various types of network traffic
  • Load balancing this way will forward user traffic based on IP range and port
  • Generally, all of the servers in the web-backend should be serving identical content--otherwise the user might receive inconsistent content.
  • Using layer 7 allows the load balancer to forward requests to different backend servers based on the content of the user's request.
  • allows you to run multiple web application servers under the same domain and port
  • acl url_blog path_beg /blog matches a request if the path of the user's request begins with /blog.
  • Round Robin selects servers in turns
  • Selects the server with the least number of connections--it is recommended for longer sessions
  • This selects which server to use based on a hash of the source IP
  • ensure that a user will connect to the same server
  • require that a user continues to connect to the same backend server. This persistence is achieved through sticky sessions, using the appsession parameter in the backend that requires it.
  • HAProxy uses health checks to determine if a backend server is available to process requests.
  • The default health check is to try to establish a TCP connection to the server
  • If a server fails a health check, and therefore is unable to serve requests, it is automatically disabled in the backend
  • For certain types of backends, like database servers in certain situations, the default health check is insufficient to determine whether a server is still healthy.
  • However, your load balancer is a single point of failure in these setups; if it goes down or gets overwhelmed with requests, it can cause high latency or downtime for your service.
  • A high availability (HA) setup is an infrastructure without a single point of failure
  • a static IP address that can be remapped from one server to another.
  • If that load balancer fails, your failover mechanism will detect it and automatically reassign the IP address to one of the passive servers.
張 旭

Understanding the Nginx Configuration File Structure and Configuration Contexts | Digit... - 0 views

www.digitalocean.com/...ure-and-configuration-contexts

nginx web server

shared by 張 旭 on 05 Dec 17 - No Cached
  • discussing the basic structure of an Nginx configuration file along with some guidelines on how to design your files
  • /etc/nginx/nginx.conf
  • In Nginx parlance, the areas that these brackets define are called "contexts" because they contain configuration details that are separated according to their area of concern
  • ...50 more annotations...
  • contexts can be layered within one another
  • if a directive is valid in multiple nested scopes, a declaration in a broader context will be passed on to any child contexts as default values.
  • The children contexts can override these values at will
  • Nginx will error out on reading a configuration file with directives that are declared in the wrong context.
  • The most general context is the "main" or "global" context
  • Any directive that exist entirely outside of these blocks is said to inhabit the "main" context
  • The main context represents the broadest environment for Nginx configuration.
  • The "events" context is contained within the "main" context. It is used to set global options that affect how Nginx handles connections at a general level.
  • Nginx uses an event-based connection processing model, so the directives defined within this context determine how worker processes should handle connections.
  • the connection processing method is automatically selected based on the most efficient choice that the platform has available
  • a worker will only take a single connection at a time
  • When configuring Nginx as a web server or reverse proxy, the "http" context will hold the majority of the configuration.
  • The http context is a sibling of the events context, so they should be listed side-by-side, rather than nested
  • fine-tune the TCP keep alive settings (keepalive_disable, keepalive_requests, and keepalive_timeout)
  • The "server" context is declared within the "http" context.
  • multiple declarations
  • each instance defines a specific virtual server to handle client requests
  • Each client request will be handled according to the configuration defined in a single server context, so Nginx must decide which server context is most appropriate based on details of the request.
  • listen: The ip address / port combination that this server block is designed to respond to.
  • server_name: This directive is the other component used to select a server block for processing.
  • "Host" header
  • configure files to try to respond to requests (try_files)
  • issue redirects and rewrites (return and rewrite)
  • set arbitrary variables (set)
  • Location contexts share many relational qualities with server contexts
  • multiple location contexts can be defined, each location is used to handle a certain type of client request, and each location is selected by virtue of matching the location definition against the client request through a selection algorithm
  • Location blocks live within server contexts and, unlike server blocks, can be nested inside one another.
  • While server contexts are selected based on the requested IP address/port combination and the host name in the "Host" header, location blocks further divide up the request handling within a server block by looking at the request URI
  • The request URI is the portion of the request that comes after the domain name or IP address/port combination.
  • New directives at this level allow you to reach locations outside of the document root (alias), mark the location as only internally accessible (internal), and proxy to other servers or locations (using http, fastcgi, scgi, and uwsgi proxying).
  • These can then be used to do A/B testing by providing different content to different hosts.
  • configures Perl handlers for the location they appear in
  • set the value of a variable depending on the value of another variable
  • used to map MIME types to the file extensions that should be associated with them.
  • this context defines a named pool of servers that Nginx can then proxy requests to
  • The upstream context should be placed within the http context, outside of any specific server contexts.
  • The upstream context can then be referenced by name within server or location blocks to pass requests of a certain type to the pool of servers that have been defined.
  • function as a high performance mail proxy server
  • The mail context is defined within the "main" or "global" context (outside of the http context).
  • Nginx has the ability to redirect authentication requests to an external authentication server
  • the if directive in Nginx will execute the instructions contained if a given test returns "true".
  • Since Nginx will test conditions of a request with many other purpose-made directives, if should not be used for most forms of conditional execution.
  • The limit_except context is used to restrict the use of certain HTTP methods within a location context.
  • The result of the above example is that any client can use the GET and HEAD verbs, but only clients coming from the 192.168.1.1/24 subnet are allowed to use other methods.
  • Many directives are valid in more than one context
  • it is usually best to declare directives in the highest context to which they are applicable, and overriding them in lower contexts as necessary.
  • Declaring at higher levels provides you with a sane default
  • Nginx already engages in a well-documented selection algorithm for things like selecting server blocks and location blocks.
  • instead of relying on rewrites to get a user supplied request into the format that you would like to work with, you should try to set up two blocks for the request, one of which represents the desired method, and the other that catches messy requests and redirects (and possibly rewrites) them to your correct block.
  • incorrect requests can get by with a redirect rather than a rewrite, which should execute with lower overhead.
張 旭

thoughtbot/paperclip: Easy file attachment management for ActiveRecord - 0 views

github.com/paperclip

rails file upload

shared by 張 旭 on 04 Dec 17 - No Cached
  • Paperclip is intended as an easy file attachment library for ActiveRecord.
  • treat files as much like other attributes as possible
  • they aren't saved to their final locations on disk, nor are they deleted if set to nil, until ActiveRecord::Base#save is called.
張 旭

循序漸進理解 HTTP Cache 機制 | TechBridge 技術共筆部落格 - 0 views

blog.techbridge.cc/...cache-introduction

http web cache performance

shared by 張 旭 on 25 Sep 17 - No Cached
  • 減少資源的損耗
  • Cache，台灣翻譯叫做快取，中國翻譯叫做緩存
  • 用到這些資訊的時候，都可以用極快的速度撈出來，而不是再到資料庫裡面重新算一次
  • ...9 more annotations...
  • Server side 的 Cache，藉由把 Database 的資料撈出來之後存到別的地方達成。
  • Server 跟瀏覽器之間的 Cache 機制
  • 瀏覽器會檢視「現在的時間」是否有超過這個 Expires
  • HTTP 1.1 有一個新的 header 出現了，叫做：Cache-Control
  • max-age設定成31536000秒，也就是 365 天的意思
  • max-age會蓋過Expires。因此現在的快取儘管兩個都會放，但其實真正會用到的是max-age。
  • 過期了不代表不能用
  • 在 Server 傳送 Response 的時候，可以多加一個Last-Modified的 Header，表示這個檔案上一次更改是什麼時候。
  • 用If-Modified-Since來跟 Server 指定拿取：某個時間點以後有更改的資料。
張 旭

Deploying Rails Apps, Part 6: Writing Capistrano Tasks - Vladi Gleba - 0 views

vladigleba.com/...art-6-writing-capistrano-tasks

rails capistrano deploy ruby docker devops

shared by 張 旭 on 23 Aug 17 - No Cached
  • we can write our own tasks to help us automate various things.
  • organizing all of the tasks here under a namespace
  • upload a file from our local computer.
  • ...27 more annotations...
  • learn about is SSHKit and the various methods it provides
  • SSHKit was actually developed and released with Capistrano 3, and it’s basically a lower-level tool that provides methods for connecting and interacting with remote servers
  • on(): specifies the server to run on
  • within(): specifies the directory path to run in
  • with(): specifies the environment variables to run with
  • run on the application server
  • within the path specified
  • with certain environment variables set
  • execute(): the workhorse that runs the commands on your server
  • upload(): uploads a file from your local computer to your remote server
  • capture(): executes a command and returns its output as a string
    • 張 旭
      張 旭 on 23 Aug 17
       
      capture 是跑在遠端伺服器上
  • upload() has the bang symbol (!) because that’s how it’s defined in SSHKit, and it’s just a convention letting us know that the method will block until it finishes.
  • But in order to ensure rake runs with the proper environment variables set, we have to use rake as a symbol and pass db:seed as a string
  • This format will also be necessary whenever you’re running any other Rails-specific commands that rely on certain environment variables being set
  • I recommend you take a look at SSHKit’s example page to learn more
  • make sure we pushed all our local changes to the remote master branch
  • run this task before Capistrano runs its own deploy task
  • actually creates three separate tasks
  • I created a namespace called deploy to contain these tasks since that’s what they’re related to.
  • we’re using the callbacks inside a namespace to make sure Capistrano knows which tasks the callbacks are referencing.
  • custom recipe (a Capistrano term meaning a series of tasks)
  • /shared: holds files and directories that persist throughout deploys
  • When you run cap production deploy, you’re actually calling a Capistrano task called deploy, which then sequentially invokes other tasks
  • your favorite browser (I hope it’s not Internet Explorer)
  • Deployment is hard and takes a while to sink in.
  • the most important thing is to not get discouraged
  • I didn’t want other people going through the same thing
張 旭

我必须得告诉大家的MySQL优化原理 - 简书 - 0 views

www.jianshu.com/d7665192aaaf

mysql performance system

shared by 張 旭 on 19 Aug 17 - No Cached
  • 很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已
  • MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。
  • 当查询语句很长的时候，需要设置max_allowed_packet参数。
  • ...70 more annotations...
  • 如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常
  • 服务器响应给用户的数据通常会很多，由多个数据包组成
  • 减小通信间数据包的大小和数量是一个非常好的习惯
  • 查询中尽量避免使用SELECT *以及加上LIMIT限制
  • 在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。
  • 两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。
  • MySQL将缓存存放在一个引用表
  • 如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果 都不会被缓存。
  • MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。
  • 在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。
  • 查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外
  • 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
  • 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗
  • 并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗
  • 用多个小表代替一个大表
  • 批量插入代替循环单条插入
  • 合理控制缓存空间大小
  • 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存
  • 不要轻易打开查询缓存，特别是写密集型应用
  • 将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。
  • 预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。
  • 一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。
  • 查询当前会话的last_query_cost的值来得到其计算当前查询的成本。
  • 尝试预测一个查询使用某种执行计划时的成本
  • 有非常多的原因会导致MySQL选择错误的执行计划
  • MySQL值选择它认为成本小的，但成本小并不意味着执行时间短
  • 提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）
  • 找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值
  • 在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。
  • 查询过程中的每一张表由一个handler实例表示。
  • MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息
  • 即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如改查询影响到的行数以及执行时间等等。
  • 结果集返回客户端是一个增量且逐步返回的过程
  • 整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。
  • 如果计划在列上创建索引，就应该将该列设置为NOT NULL
  • UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。
  • 没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT
  • 大表ALTER TABLE非常耗时
  • MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表
  • 索引是提高MySQL查询性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。
  • 索引是指B-Tree索引，它是目前关系型数据库中查找数据最为常用和有效的索引，大多数存储引擎都支持这种索引
  • B+Tree中的B是指balance，意为平衡。
  • 平衡二叉树首先需要符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度差不能大于1。
  • 索引往往以索引文件的形式存储的磁盘上
  • 如何减少查找过程中的I/O存取次数？
  • 减少树的深度，将二叉树变为m叉树（多路搜索树），而B+Tree就是一种多路搜索树。
  • 页是计算机管理存储器的逻辑块，硬件及OS往往将主存和磁盘存储区分割为连续的大小相等的块
  • B+Tree为了保持平衡，对于新插入的值需要做大量的拆分页操作，而页的拆分需要I/O操作，为了尽可能的减少页的拆分操作，B+Tree也提供了类似于平衡二叉树的旋转功能。
  • 在多数情况下，在多个列上建立独立的索引并不能提高查询性能。理由非常简单，MySQL不知道选择哪个索引的查询效率更好
  • 当出现多个索引做联合操作时（多个OR条件），对结果集的合并、排序等操作需要耗费大量的CPU和内存资源，特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高。
  • explain时如果发现有索引合并（Extra字段出现Using union），应该好好检查一下查询和表结构是不是已经是最优的
  • 索引选择性是指不重复的索引值和数据表的总记录数的比值
  • 唯一索引的选择性是1，这时最好的索引选择性，性能也是最好的。
  • 如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。
  • 对结果集进行排序的操作
  • 按照索引顺序扫描得出的结果自然是有序的
  • 如果explain的结果中type列的值为index表示使用了索引扫描来做排序。
  • 在设计索引时，如果一个索引既能够满足排序，有满足查询，是最好的。
  • 比如有一个索引(A,B)，再创建索引(A)就是冗余索引。
  • 对于非常小的表，简单的全表扫描更高效。对于中到大型的表，索引就非常有效。对于超大型的表，建立和维护索引的代价随之增长，这时候其他技术也许更有效，比如分区表。
  • explain后再提测是一种美德。
  • 如果要统计行数，直接使用COUNT(*)，意义清晰，且性能更好。
  • 执行EXPLAIN并不需要真正地去执行查询，所以成本非常低。
  • 确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序
  • 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化
  • MySQL关联执行的策略非常简单，它对任何的关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。然后根据各个表匹配的行，返回查询中需要的各个列。
  • 最外层的查询是根据A.xx列来查询的，A.c上如果有索引的话，整个关联查询也不会使用。再看内层的查询，很明显B.c上如果有索引的话，能够加速查询，因此只需要在关联顺序中的第二张表的相应列上创建索引即可。
  • MySQL处理UNION的策略是先创建临时表，然后再把各个查询结果插入到临时表中，最后再来做查询。
  • 要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。
  • 尽可能不要使用存储过程
« First ‹ Previous 381 - 400 of 596 Next › Last »
Showing 20▼ items per page