1. 利用查询缓存
MySQL 的查询缓存可以显著提高性能。在数据更新较少，类似查询较多的情况下，开启查询缓存后，若数据没有更新，相同的查询 SQL 会使用缓存数据返回结果。
查询缓存的配置信息可以通过一些命令查看，如 show variables like '%query_cache%';，其中 have_query_cache 表示是否支持查询缓存，query_cache_type 表示缓存类型，OFF 表示关闭查询缓存，ON 表示开启查询缓存，DEMAND 表示用户自定义查询缓存，query_cache_limit 表示支持的最大单条查询 SQL 数据量，query_cache_min_res_unit 表示查询缓存最小单位，query_cache_size 表示查询缓存空间大小，query_cache_wlock_invalidate 表示查询缓存是否支持写锁。
开启查询缓存可以在 MySQL 配置文件中进行设置。例如，query_cache_type=0(OFF) 关闭，query_cache_type=1(ON) 缓存所有结果，除非 select 语句使用 SQL_NO_CACHE 禁用查询缓存，query_cache_type=2(DEMAND)，只缓存 select 语句中通过 SQL_CACHE 指定需要缓存的查询。
查询缓存的使用也有一些限制，只有字符串相等查询的 SQL 才使用相同缓存，即 select name from users 与 SELECT name FROM users 不使用同一个缓存。在 query_cache_type 为 ON 的情况下，默认所有查询都使用缓存，我们可以使用 sql_no_cache 显示指定某个查询不使用缓存，如 select sql_no_cache name from users；在 query_cache_type 为 DEMAND 的情况下，需要使用 sql_cache 指定某个查询使用缓存，如 select sql_cache name from users。
然而，查询缓存也有失效的情况。当 SQL 语句不一致时，不会命中查询缓存；当查询语句中有一些不确定的操作时，如 now()、current_date()、curdate()、curtime()、rand()、uuid()、user()、database() 等函数，也不会缓存；不使用任何表查询语句，如 select 'a' 查询 MySQL，information_schema 或 performance_schema 数据库中的表时，不会走查询缓存；在存储的函数，触发器或事件的主体内执行的查询也不会被缓存；如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。
2. 使用 EXPLAIN 分析查询
使用 EXPLAIN 关键字可以了解 MySQL 如何处理 SQL 语句。通过 EXPLAIN，我们可以分析出表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及每张表有多少行被优化器查询等信息。
EXPLAIN 的基本语法为 EXPLAIN [EXTENDED| PARTITIONS] SELECT select_options。其中 EXTENDED 提供更多的执行计划信息，包括优化器重写查询的详细信息；PARTITIONS 显示分区相关的信息。
EXPLAIN 的输出包含许多字段，每个字段提供不同的执行计划信息。例如，id 是查询中执行的每个子查询或联合查询的标识符，数值越大，优先级越高；select_type 表示查询的类型，如 SIMPLE（简单查询）、PRIMARY（主查询）、UNION（联合查询的一部分）、SUBQUERY（子查询）等；table 是查询涉及的表；type 表示表的访问类型，常见类型包括 ALL（全表扫描，性能最差）、index（遍历索引扫描）、range（索引范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）、const/system（常量表访问）等；possible_keys 显示查询中可能使用的索引；key 是实际使用的索引；key_len 表示索引中使用的字节数；ref 显示索引的那一列被使用了；rows 是估计的扫描行数；filtered 表示表中满足条件的行百分比；Extra 包含不适合在其他列中显式但十分重要的额外信息，如 Using filesort（说明 MySQL 会对数据使用一个外部的索引排序）、Using temporary（使用了用临时表保存中间结果）、Using index（表示相应的 select 操作中使用了覆盖索引）、Using where（表明使用了 where 过滤）、Using join buffer（表明使用了连接缓存）等。
例如，对于全表扫描的查询 EXPLAIN SELECT * FROM employees WHERE age > 30;，输出解释为 type: ALL，表示全表扫描；possible_keys: NULL，没有可用的索引；key: NULL，未使用索引；rows:10000，估计扫描 10000 行。这种情况说明查询没有使用索引，可能会导致性能问题。可以考虑为 age 列添加索引。
对于使用索引的查询 EXPLAIN SELECT * FROM employees WHERE age > 30 AND department_id = 1;，输出解释为 type: range，索引范围扫描；possible_keys: idx_age_department；key: idx_age_department，实际使用了索引；rows:500，估计扫描 500 行。在这个查询中，使用了复合索引 (age, department_id)，显著减少了扫描的行数，提高了查询性能。
对于复杂查询 EXPLAIN SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id WHERE e.age > 30;，输出解释为 id:1, 1；select_type: SIMPLE, SIMPLE；table: e, d；type: ref, eq_ref；possible_keys: idx_department_id, PRIMARY；key: idx_department_id, PRIMARY；rows: 500, 1。这里可以看到，查询涉及两个表的连接，MySQL 使用了 ref 和 eq_ref 类型的索引扫描，优化了连接查询的性能。
3. LIMIT 1 提高效率
当已知查询结果只有一条数据时，使用 LIMIT 1 可以让 MySQL 数据库引擎在找到一条数据后停止搜索，提高性能。
例如在某些情况下，如果明知道查询结果只有一个，SQL 语句中使用 LIMIT 1 会提高查询效率。例如下面的用户表（主键 id，邮箱，密码）：createtable t_user(id int primary key auto_increment,email varchar(255),password varchar(255));每个用户的 email 是唯一的，如果用户使用 email 作为用户名登陆的话，就需要查询出 email 对应的一条记录。SELECT * FROM t_user WHERE email=?;上面的语句实现了查询 email 对应的一条用户信息，但是由于 email 这一列没有加索引，会导致全表扫描，效率会很低。SELECT * FROM t_user WHERE email=? LIMIT 1;加上 LIMIT 1，只要找到了对应的一条记录，就不会继续向下扫描了，效率会大大提高。
LIMIT 1 适用于查询结果为 1 条（也可能为 0）会导致全表扫描的的 SQL 语句。如果 email 是索引的话，就不需要加上 LIMIT 1，如果是根据主键查询一条记录也不需要 LIMIT 1，主键也是索引。例如：SELECT * FROM t_user WHERE id=?;就不需要写成：SELECT * FROM t_user WHERE id=? LIMIT 1;二者效率没有区别。
附上我做的实验：存储过程生成 100 万条数据：
BEGIN
DECLARE i INT;
START TRANSACTION;
SET i=0;
WHILE i<1000000 DO
INSERT INTO t_user VALUES(NULL,CONCAT(i+1,'@xxg.com'),i+1);
SET i=i+1;
END WHILE;
COMMIT;
END查询语句 SELECT*FROM t_user WHERE email='222@xxg.com'; 耗时 0.56 s；SELECT*FROM t_user WHERE email='222@xxg.com' LIMIT 1; 耗时 0.00 s。
在 MySQL 数据库操作中，我们在做一些查询的时候总希望能避免数据库引擎做全表扫描，因为全表扫描时间长，而且其中大部分扫描对客户端而言是没有意义的。其实我们可以使用 Limit 关键字来避免全表扫描的情况，从而提高效率。
有个几千万条记录的表 on MySQL 5.0.x，现在要读出其中几十万万条左右的记录。常用方法，依次循环：select * from mytable where index_col = xxx limit offset, limit;经验：如果没有 blob/text 字段，单行记录比较小，可以把 limit 设大点，会加快速度。问题：头几万条读取很快，但是速度呈线性下降，同时 mysql server cpu 99%，速度不可接受。调用 explain select * from mytable where index_col = xxx limit offset, limit; 显示 type = ALL。在 MySQL optimization 的文档写到 “All” 的解释 A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.看样子对于 all, mysql 就使用比较笨的方法，那就改用 range 方式？因为 id 是递增的，也很好修改 sql。select * from mytable where id > offset and id < offset + limit and index_col = xxx，explain 显示 type = range，结果速度非常理想，返回结果快了几十倍。
Limit 语法：SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset。LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0 (而不是 1)。为了与 PostgreSQL 兼容，MySQL 也支持句法：LIMIT # OFFSET #。mysql> SELECT * FROM table LIMIT 5,10; //检索记录行 6-15。//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1。mysql> SELECT * FROM table LIMIT 95,-1; //检索记录行 96-last。//如果只给定一个参数，它表示返回最大的记录行数目，换句话说，LIMIT n 等价于 LIMIT 0,n。mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行。
MySQL 的 limit 给分页带来了极大的方便，但数据量一大的时候，limit 的性能就急剧下降。同样是取 10 条数据，下面两句就不是一个数量级别的。select * from table limit 10000,10；select * from table limit 0,10。文中不是直接使用 limit，而是首先获取到 offset 的 id 然后直接使用 limit size 来获取数据。根据他的数据，明显要好于直接使用 limit。这里我具体使用数据分两种情况进行测试。
1、offset 比较小的时候：select * from table limit 10,10// 多次运行，时间保持在 0.0004 - 0.0005 之间。Select * From table Where vid >=(Select vid From table Order By vid limit 10,1) limit 10// 多次运行，时间保持在 0.0005 - 0.0006 之间，主要是 0.0006。结论：偏移 offset 较小的时候，直接使用 limit 较优。这个显然是子查询的原因。
2、offset 大的时候：select * from table limit 10000,10// 多次运行，时间保持在 0.0187 左右。Select * From table Where vid >=(Select vid From table Order By vid limit 10000,1) limit 10// 多次运行，时间保持在 0.0061 左右，只有前者的 1/3。可以预计 offset 越大，后者越优。
总结：limit 在某些情况下可以提高查询效率，但在数据量大且偏移量较大时，需要考虑其他优化方法。
limit 效率问题 limit 是由 mysql 的 server 层实现的。
一、如何用 limit 提升查询性能：select * from T where login_name = 'XXX‘如果 T.login_name 没有建立索引，则上面的查询语句会全表扫描。select * from T where login_name = 'XXXX' limit 1如果加上 limit 1，当扫描到一条的数据的时候则不会再继续扫描，效率会大大的提高。
二、limit 低效率的情况：在一种情况下 limit 效率非常低，只使用 limit 来查询的语句，并且偏移量特别大的情况。例如：select * from table limit 15000000，1000；select * from table while id>=150000 limit 1000。语句 1 为 0.2077 秒；语句 2 为 0.0063 秒。两条语句的时间比是：语句 1 / 语句 2＝32.968。比较以上的数据时，我们可以发现采用 where...limit.... 性能基本稳定，受偏移量和行数的影响不大，而单纯采用 limit 的话，受偏移量的影响很大，当偏移量大到一定值后性能开始大幅下降。不过在数据量不大的情况下，两者的区别不大。所以应当先使用 where 等查询语句，配合 limit 使用，效率才高。
二、索引优化
1. 为搜索字段建索引
经常用于搜索的字段应该建立索引。在 MySQL 中，为搜索字段建索引可以显著提高查询性能。例如，如果有一个用户表，经常需要根据用户名进行搜索，那么为用户名字段建立索引可以大大加快搜索速度。当执行查询语句时，MySQL 可以直接使用索引快速定位到满足条件的记录，而不需要进行全表扫描。这样可以减少磁盘 I/O 操作和 CPU 资源的消耗，从而提高查询效率。
2. 连接表时的索引优化
连接两张表时，Join 的字段应该被建过索引，且字段类型应相同，对于字符串类型还需有相同的字符集。
索引优化在连接表的操作中起着至关重要的作用。当连接两张表时，确保 Join 的字段被建过索引是提高查询性能的关键。如果连接字段没有索引，MySQL 将不得不进行全表扫描来查找匹配的记录，这会导致查询速度非常缓慢。
此外，连接字段的类型应相同，对于字符串类型还需有相同的字符集。如果字段类型不同，MySQL 可能需要进行类型转换，这会降低查询效率。例如，如果一个表中的连接字段是整数类型，而另一个表中的连接字段是字符串类型，MySQL 将需要进行类型转换才能进行连接操作。同样，如果两个表中的字符串连接字段的字符集不同，MySQL 也需要进行字符集转换，这也会影响查询性能。
3. 避免索引失效
索引列不能是表达式的一部分或函数的参数，默认类型转换会使索引失效，IS NULL 或 IS NOT NULL 查询也会使索引失效。
在 MySQL 中，索引列不能是表达式的一部分或函数的参数。如果索引列出现在表达式中，MySQL 将无法使用索引进行查询优化，而是需要计算表达式的值，然后进行全表扫描来查找满足条件的记录。例如，如果有一个表中有一个字段是价格，并且为价格字段建立了索引，但是在查询语句中使用了价格字段的表达式，如SELECT * FROM table WHERE price * 2 > 100;，MySQL 将无法使用索引进行查询优化。
默认类型转换也会使索引失效。如果在查询语句中，索引列的类型与查询条件中的值的类型不匹配，MySQL 可能会进行类型转换。如果这种类型转换不是显式的，而是默认的类型转换，那么索引可能会失效。例如，如果有一个表中有一个字段是整数类型，并且为这个字段建立了索引，但是在查询语句中使用了字符串类型的值作为查询条件，如SELECT * FROM table WHERE int_field = '123';，MySQL 可能会进行默认类型转换，将字符串类型的值转换为整数类型，但是这种类型转换可能会导致索引失效。
IS NULL 或 IS NOT NULL 查询也会使索引失效。当使用 IS NULL 或 IS NOT NULL 查询时，MySQL 无法使用索引进行查询优化，而是需要进行全表扫描来查找满足条件的记录。这是因为索引通常不包含 NULL 值，所以无法通过索引快速定位到 NULL 或 NOT NULL 的记录。
三、字段属性优化
1. 选择合适的字段属性
在设计 MySQL 数据库表时，选择合适的字段属性至关重要。例如，当定义邮政编码时，没必要使用char(255)这样的大宽度字段，char(6)通常就足够了。对于整型字段，如果没有特别大的数值需求，尽量使用MEDIUMINT而不是BIGINT。这样可以减少数据库存储空间的占用，提高数据存储和查询的效率。因为较小的字段宽度占用更少的磁盘空间，在进行数据读取和写入时，也能减少 I/O 操作的时间。
2. 设置字段为 NOT NULL
在可能的情况下，应尽量把字段设置为NOT NULL。这样做有诸多好处。首先，数据库建表时，将字段设置为NOT NULL可以节省空间，因为NULL列需要一个额外字节作为判断是否为NULL的标志位。其次，在空指针查询时，可以在一定程度上减少NullPointerException。再者，如果有Null column存在的情况下，count(NULL column)需要格外注意，NULL值不会参与统计，这可能导致聚合函数计算不准确。此外，含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值，所以如果索引的字段可以为NULL值，索引的效率会下降。最后，NOT IN、!=等负向条件查询在有NULL值的情况下，返回永远为空结果，查询容易出错。
3. 使用 ENUM 类型
对于取值有限且固定的字段，如 “性别”“国家” 等，可以使用ENUM类型。在 MySQL 中，ENUM类型被当作数值型数据处理，速度比文本类型快。例如，创建包含ENUM类型字段的表，如CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, category ENUM('electronics','furniture','clothing','toys') NOT NULL);，在这个例子中，category字段只能存储 'electronics'、'furniture'、'clothing' 或 'toys' 之一，插入或更新时如果试图存入其他值，会报错。ENUM类型的值以整数形式存储，而不是直接存储为字符串，每个枚举值在存储时被映射为一个从 1 开始的整数，这种存储方式使得ENUM类型在空间占用上比较紧凑，存储大小取决于枚举值的数量。同时，ENUM类型强制数据只能在预定义的列表中选择，避免了存入非法值的情况，对数据一致性和完整性有很大帮助，尤其是在用户输入不可控的情况下。此外，使用ENUM可以使代码和数据结构更加清晰，程序员和维护人员可以从数据库结构中很容易地看到一个字段允许的值范围，提升了代码和数据库表设计的可读性。但ENUM类型也有缺点，一旦定义了ENUM类型，如果需要添加新的枚举值，必须使用ALTER TABLE修改表结构，对于大表来说，修改表结构可能会影响性能。如果应用需求中枚举值的变化较频繁，ENUM类型可能不适合。而且ENUM类型只适用于值范围固定且相对较小的字段，如果有更复杂的需求（例如支持多选），ENUM不适合使用。查询时，ENUM的底层存储为整数，而查询返回的是字符串，因此在一些情况下，特别是涉及数值比较时，可能会产生混淆。
四、其他优化策略
1. 避免 SELECT *
在数据库操作中，从数据库里读出的数据越多，查询就会越慢，还会增加网络传输负载。应该养成只取需要字段的好习惯。比如在一些情况下，如果查询的字段包含大字段如 text、longtext、blob、tinytext、mediumtext、longblob、mediumblob、tinyblob 等，使用 SELECT *会显著增加磁盘 IO 开销和网络传输开销，对查询效率产生很大影响。
2. 使用连接代替子查询
使用连接（JOIN）来代替子查询（Sub-Queries），因为 MySQL 不需要在内存中创建临时表来完成逻辑上需要两个步骤的查询工作，速度更快。连接操作通常在数据库查询优化器中被更好地优化，可以更有效地利用索引，减少全表扫描，从而提高查询性能。相比子查询，连接查询的代码更加清晰明了，更加易于维护。在分布式数据库或客户端 / 服务器架构中，使用连接（JOIN）可以减少网络传输的开销，因为连接操作可以在数据库服务器端完成，只将结果集返回给客户端，而不需要将中间结果或临时表传输到客户端。此外，子查询有一些限制和注意事项，例如不能在子查询中直接修改被查询的表，以及在某些情况下可能导致不可预测的结果。使用连接（JOIN）可以避免这些限制，并提供更可靠和一致的结果。当需要关联更多的表或进行更复杂的查询时，使用连接（JOIN）可以更容易地扩展查询逻辑。
3. 使用联合代替手动创建临时表
MySQL 从 4.0 版本开始支持 union 查询，可以把需要使用临时表的两条或更多的 select 查询合在一个查询中，提高效率。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用 union 来创建查询的时候，我们只需要用 UNION 作为关键字把多个 select 语句连接起来就可以了，要注意的是所有 select 语句中的字段数目要相同。
4. 避免 ORDER BY RAND ()
ORDER BY RAND () 会让数据库性能呈指数级下降，可以使用其他方法打乱返回的数据行。当在大型数据表中使用 ORDER BY RAND () 时，由于 MySQL 需要为所有的数据行生成一个随机值并对这些值进行排序，这会导致查询性能下降。尤其是当数据表的数据量很大时，这种性能下降会更加明显。为了解决这个问题，可以使用以下几种方法：使用 LIMIT 和 OFFSET ：可以通过将查询结果分页来减少返回的数据量。例如，只返回前 10 条记录：SELECT * FROM table_name ORDER BY id LIMIT 10;然后，可以使用 OFFSET 来获取下一页的数据：SELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 10;这样可以有效地减少查询的数据量，从而提高性能。使用 TABLESAMPLE ：TABLESAMPLE 可以从数据表中随机抽取一部分数据进行查询。例如，以下查询将从数据表中随机抽取 10% 的数据并返回：SELECT * FROM table_name TABLESAMPLE BERNOULLI(10);这种方法可以显著提高查询性能，但需要注意的是，抽样可能不是完全随机的，因此结果可能不完全准确。使用 MEMORY 存储引擎：可以将数据表复制到一个内存表中，然后在内存表上执行 ORDER BY RAND () 查询。这样可以减少磁盘 I/O 操作，从而提高查询性能。例如：CREATE TABLE memory_table ENGINE=MEMORY AS SELECT * FROM table_name;SELECT * FROM memory_table ORDER BY RAND();需要注意的是，这种方法可能会消耗大量内存，因此需要根据实际情况进行权衡。
5. 为每张表设置 ID
为每张表设置一个 INT 型（推荐使用 UNSIGNED）的 ID 作为主键，并设置自动增长的 AUTO_INCREMENT 标志，提高性能。MySQL 自增主键 ID 在表有数据的情况下可以添加。在 MySQL 数据库中，自增主键 ID 是一种非常常见的设置，用于唯一标识表中的每一行数据。通常情况下，我们会在创建表的时候设置一个自增主键 ID，以确保每行数据都有一个唯一的标识。如果在表中已经有数据的情况下需要添加自增主键 ID，可以通过 ALTER TABLE 语句来实现。例如：ALTER TABLE users ADD COLUMN new_id INT AUTO_INCREMENT PRIMARY KEY FIRST;通过上面的命令，我们给表 users 添加了一个新的自增主键 new_id，并将其设置为主键。InnoDB 使用两种索引来组织数据，Clustered Index 和 Second Index。Clustered Index 与主键有千丝万缕的关系，可以简单认为是相等关系，数据存储会按照主键来进行排序。使用自增 ID 充当主键，就可以解决因为数据插入不规律导致的频繁页分裂问题，提高存储和查询效率。对于 InnoDB 的主键，尽量用整型，而且是递增的整型，这样在存储和查询上都是非常高效的。
