MySQL性能优化
MySQL数据库配置优化
max_connections
作用：设置最大连接（用户）数，每个连接MySQL的用户均算作一个连接，max_connections的默认值为100。此值需要根据具体的连接数峰值设定。
查看：show variables like “max_connections”;
设置命令：set global max_connections = 200;
设置方法，在my.cnf文件里：
max_connections = 200
table_open_cache
作用:当某一连接访问一个表时，MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开，则会直接访问缓存中的表已加快查询速度；如果该表未被缓存，则会将当前的表添加进缓存并进行查询。
在执行缓存操作之前，table_cache用于限制缓存表的最大数目：如果当前已经缓存的表未达到table_cache，则会将新表添加进来；若已经达到此值，MySQL将根据缓存表的最后查询时间、查询率等规则释放之前的缓存。
默认值 2000
最小值 1
最大值 524288
查看：show variables like “table_open_cache”;
show global status like ‘Open%tables’;
设置 table_open_cache
set global table_open_cache = 2048; （立即生效重启后失效）
比较适合的值：
Open_tables / Opened_tables >= 0.85
Open_tables / table_open_cache <= 0.95
query_cache_size（不开启）
作用：查询缓存大小，如果表的改动非常频繁，或者每次查询都不同，查询缓存的结果会减慢系统性能。可以设置为0。
查看：
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_strip_comments | OFF |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
query_cache_limit：允许 Cache 的单条 Query 结果集的最大容量，默认是1MB，超过此参数设置的 Query 结果集将不会被 Cache
query_cache_min_res_unit：设置 Query Cache 中每次分配内存的最小空间大小，也就是每个 Query 的 Cache 最小占用的内存空间大小
query_cache_size：设置 Query Cache 所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是整数倍，MySQL 会自动调整降低最小量以达到1024的倍数
query_cache_type：控制 Query Cache 功能的开关，可以设置为0(OFF),1(ON)和2(DEMAND)三种，意义分别如下：
0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache
1(ON)：开启 Query Cache 功能，但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后，将不使用Query Cache
2(DEMAND)：开启 Query Cache 功能，但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后，才使用 Query Cache
query_cache_wlock_invalidate：控制当有写锁定发生在表上的时刻是否先失效该表相关的 Query Cache，如果设置为 1(TRUE)，则在写锁定的同时将失效该表相关的所有 Query Cache，如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的 Query Cache。
建议不开启原因：
打开Qcache对读和写都会带来额外的消耗：
a、读查询开始之前必须检查是否命中缓存。
b、如果读查询可以缓存，那么执行完之后会写入缓存。
c、当向某个表写入数据的时候，必须将这个表所有的缓存设置为失效，如果缓存空间很大，则消耗也会很大，可能使系统僵死一段时间，因为这个操作是靠全局锁操作来保护的。
对InnoDB表，当修改一个表时，设置了缓存失效，但是多版本特性会暂时将这修改对其他事务屏蔽，在这个事务提交之前，所有查询都无法使用缓存，直到这个事务被提交，所以长时间的事务，
会大大降低查询缓存的命中
一个表可以被许多类型的语句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。
对于InnoDB而言，事物的一些特性还会限制查询缓存的使用。当在事物A中修改了B表时，因为在事物提交之前，对B表的修改对其他的事物而言是不可见的。为了保证缓存结果的正确性，
InnoDB采取的措施让所有涉及到该B表的查询在事物A提交之前是不可缓存的。如果A事物长时间运行，会严重影响查询缓存的命中率
查询缓存的空间不要设置的太大。
因为查询缓存是靠一个全局锁操作保护的，如果查询缓存配置的内存比较大且里面存放了大量的查询结果，当查询缓存失效的时候，会长时间的持有这个全局锁。
因为查询缓存的命中检测操作以及缓存失效检测也都依赖这个全局锁，所以可能会导致系统僵死的情况
join_buffer_size
作用：每个线程使用连接缓存区大小
mysql中每次join操作都会调用my_malloc、my_free函数申请/释放joib_buffer_size的大小的内存。
join_buffer_size是按照每次操作join表的操作的次数申请和释放joib_buffer_size.
MySQL服务器用来作普通索引扫描、范围索引扫描和不使用索引而执行全表扫描这些操作所用的缓存大小。通常，获取最快连接的方法是增加索引。当不能增加索引的时候，使全连接变快的方法是增大join_buffer_size参数。对于执行全连接的两张表，每张表都被分配一块连接内存。对于没有使用索引的多表复杂连接，需要多块连接内存。通常来说，可以将此参数在全局上设置一个较小的值，而在需要执行大连接的会话中在会话级别进行设置
查看： show variables like ‘%join_buffer_size%’;
设置： set global join_buffer_size= 524288; #设置为 512KB 根据表数据量来判断，还有用户的连接数据(备注：8G内存版本RDS的设置)
sort_buffer_size
作用：connection级的参数，排序缓存大小。一般设置为2-4MB即可。
查看：show variables like ‘%sort_buffer_size%’;
修改：set global sort_buffer_size = 868352; #设置为 848KB 根据表数据量来判断，还有用户的连接数据(备注：8G内存版本RDS的设置)
read_buffer_size
作用：connection级的参数，是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
查看：show variables like ‘%read_buffer_size%’;
修改：set global read_buffer_size = 868352; #设置为 848KB 根据表数据量来判断，还有用户的连接数据(备注：8G内存版本RDS的设置)
max_allowed_packet
作用：网络包的大小，为避免出现较大的网络包错误
查看：show variables like ‘%max_allowed_packet%’;
修改：set global max_allowed_packet =536870912 #设置为 512M 根据表数据量来判断，还有用户的连接数据(备注：8G内存版本RDS的设置 为1G)
innodb_buffer_pool_size
作用：这个是Innodb最重要的参数，和MyISAM的key_buffer_size有相似之处，但也是有差别的。
这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。
查看：show variables like ‘innodb_buffer_pool%’;
调优参考计算方法：
Innodb_buffer_pool_pages_data: Innodb buffer pool缓存池中包含数据的页的数目，包括脏页。单位是page。
Innodb_buffer_pool_pages_total：innodb buffer pool的页总数目。单位是page。
val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75%
val < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (102410241024)
设置命令：set global innodb_buffer_pool_size = 2097152; //缓冲池字节大小，单位kb，如果不设置，默认为128M
set global innodb_buffer_pool_size = 6442450944 #设置6G set global innodb_buffer_pool_size = 6442450944（备注：8G内存版本RDS）
set global innodb_buffer_pool_size =2147483648; #设置2G set global innodb_buffer_pool_size = 6442450944
InnoDB_flush_log_at_trx_commit（默认）
这个参数控制缓冲区的数据写入到日志文件以及日志文件数据刷新到磁盘的操作时机.在正式环境中建议设置成1。
设置0 : 提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。
设置1 : 提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。
设置2 : 提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。做一次刷新操作.
查看：show variables like ‘InnoDB_flush_log_at_trx_commit%’;
InnoDB_lock_wait_timeout（默认）
这个参数自动检测行锁导致的死锁并进行相应处理,但是对于表锁导致的死锁不能自动检测默认值为50秒.
查看：show variables like ‘InnoDB_lock_wait_timeout%’;
Innodb_io_capacity
参数作用：这个参数刷新脏页数量和合并插入数量，改善磁盘IO处理能力
查看:show variables like ‘innodb_io_capacity%’;
修改：set global innodb_io_capacity=200;#设置为 200M 根据硬盘的IOPS来判断(备注：8G内存版本RDS)
| innodb_io_capacity | 磁盘配置 |
|---|---|
| 200 | 单盘SAS/SATA |
| 2000 | SAS*12 RAID 10 |
| 5000 | SSD |
| 50000 | FUSION-IO |
Innodb_flush_method
作用：这个参数控制着innodb数据文件及redo log的打开、刷写模式，对于这个参数，文档上是这样描述的：
有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT
默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer
为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件
为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log
总结一下三者写数据方式：
fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。
O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成
O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲
查看：show variables like “%Innodb_flush_method%”;
设置：set global Innodb_flush_method=O_DIRECT；#（备注：8G内存版本RDS配置）
注：在类unix操作系统中，文件的打开方式为O_DIRECT会最小化缓冲对io的影响，该文件的io是直接在用户空间的buffer上操作的，并且io操作是同步的，因此不管是read()系统调用还是write()系统调用，数据都保证是从磁盘上读取的；O_SYNC方式表示以同步io的方式打开文件，任何写操作都将阻塞到数据写入物理磁盘后才返回。fsync(int filedes)函数只对由文件描述符filedes指定的单一文件起作用，并且等待写磁盘操作结束，然后返回。fdatasync(int filedes)函数类似于fsync，但它只影响文件的数据部分。而除数据外，fsync还会同步更新文件的元信息到磁盘。
innodb_adaptive_hash_index
作用:
InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive） 的。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式 来为某些页建立哈希索引。
根据InnoDB的官方文档显示，启用自适应哈希索引后，读取和写入速度可以提高2倍；对于辅助索引的连接操作，性能可以提高5倍。在我看来，自适应哈希索引是非常好的优化模式，其设计思想是数据库自优化（self-tuning），即无需DBA对数据库进行调整。
Adaptive Hash Index是针对B+树Search Path的优化，因此所有会涉及到Search Path的操作，均可使用此Hash索引进行优化，这些可优化的操作包括：Unique Scan/Range Scan(Locate First Key Page)/Insert/Delete/Purge等等，几乎涵盖InnoDB所有的操作类型
Adaptive，意味着不是所有的叶页面都会以Hash索引维护，叶页面进入Hash 索引的条件是：同种类型的操作(Scan/Insert…)，命中同一叶页面的次数，超过此页面记录数量的1/16，则可将当前叶页面加入Hash索引， 用以优化后续可能的相同Search Path。
判断依据：
| 名称 | 自适应哈希索引 |
|---|---|
| 适合使用场景 | 适合使用 = 和 IN 操作符的等值查询 |
| 不合适场景 | 不适合使用 like 和 % 的范围查询和高并发的joins |
| 优点 | 提高了Innodb的内存使用率和一些情况下二级索引的查询效率 |
| 缺点 | 占用Innodb的内存缓存，使用了 lacth 锁保护内存中的hash结构 |
mysql> show engine innodb status\G
……
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
1、34673：字节为单位，占用内存空间总量
2、通过hash searches、non-hash searches计算自适应hash索引带来的收益以及付出，确定是否开启自适应hash索引
设置
show variables like ‘%innodb_adaptive_hash_index%’;
set global innodb_adaptive_hash_index= on
innodb_log_file_size
作用：
MySQL的InnoDB 存储引擎使用一个指定大小的Redo log空间（一个环形的数据结构）。Redo log的空间通过innodb_log_file_size和innodb_log_files_in_group（默认2）参数来调节。将这俩参数相乘即可得到总的可用Redo log 空间。尽管技术上并不关心你是通过innodb_log_file_size还是innodb_log_files_in_group来调整Redo log空间，不过多数情况下还是通过innodb_log_file_size 来调节。
判断标准：
在mysql工具中如phpmyadmin中执行show engine innodb status;
注意观察Log sequence number
60秒后再次执行
获取Log sequence number
log_diff_minutes = 第二次的Log sequence number - 第一次的Log sequence number / 1024 / 1024
现在你就可以看到1分钟内有多少数据写入日志,如果你可以承受一个小时的日志写入,那么你可以将innodb_log_file_size设置为log_diff_minutes*60
设置
show variables like ‘%innodb_log_file_size%’;
set global innodb_log_file_size= 2147483648
MASTER_AUTO_POSITION
当MASTER_AUTO_POSITION = 1与使用 CHANGE MASTER TO，副本尝试连接到使用基于GTID复制协议的源。从MySQL 5.7开始，CHANGE MASTER TO仅当复制SQL线程和复制I / O线程都停止时，才可以使用此选项。副本和源都必须启用GTID（GTID_MODE=ON， ON_PERMISSIVE,或 OFF_PERMISSIVE副本 [GTID_MODE=ON]上和源上）。自动定位被用于连接，因此坐标表示通过MASTER_LOG_FILE和 MASTER_LOG_POS未使用，和使用任一种或两种这些选项连同 MASTER_AUTO_POSITION = 1导致错误。如果副本上启用了多源复制，则需要MASTER_AUTO_POSITION = 1为每个适用的复制通道设置选项。
要使用基于GTID的复制来启动副本，请不要在用于指导副本从给定源进行复制的语句中添加MASTER_LOG_FILE或 MASTER_LOG_POS选项 CHANGE MASTER TO。这些选项指定日志文件的名称和文件中的起始位置，但是使用GTID时，副本不需要此非本地数据。相反，您需要启用该 MASTER_AUTO_POSITION选项。有关使用基于GTID的复制配置和启动源和副本的完整说明，请参见官网使用GTID。
该MASTER_AUTO_POSITION选项默认为禁用。如果副本上启用了多源复制，则需要为每个适用的复制通道设置选项。MASTER_AUTO_POSITION 再次禁用该选项会使副本恢复为基于文件的复制，在这种情况下，您还必须指定MASTER_LOG_FILE或 MASTER_LOG_POS选项之一或两者 。
当一个复制品已启用GTIDs（ GTID_MODE=ON， ON_PERMISSIVE,或 OFF_PERMISSIVE），并且 MASTER_AUTO_POSITION选项使能，自动定位被用于连接到源激活。必须成功GTID_MODE=ON设置源才能使连接成功。在初始握手中，副本发送一个GTID集，其中包含它已接收，已提交或同时存在的事务。该GTID集等于 gtid_executed系统变量（@@GLOBAL.gtid_executed）中GTID集的并集，以及replication_connection_status作为接收到的事务记录在Performance Schema表中的GTID集 （语句的结果 SELECT RECEIVED_TRANSACTION_SET FROM PERFORMANCE_SCHEMA.replication_connection_status）。
源通过发送记录在其二进制日志中的所有事务进行响应，这些事务的GTID不包括在副本发送的GTID集中。为此，源首先通过检查其Previous_gtids_log_event每个二进制日志文件的标头（从最新开始）来标识要开始使用的适当二进制日志文件 。当来源找到第一个Previous_gtids_log_event 其中不包含副本缺少的事务，它以该二进制日志文件开头。此方法非常有效，并且如果副本位于大量二进制日志文件的源后面，则仅花费大量时间。然后，源读取该二进制日志文件中的事务，以及直到当前文件为止的后续文件，发送带有副本丢失的GTID的事务，并跳过副本发送的GTID集中的事务。副本接收到第一个丢失的事务之前经过的时间取决于其在二进制日志文件中的偏移量。这种交换可确保源仅发送带有尚未接收或提交副本的GTID的事务。
分别在主备库比较 show global variables like '%gtid_executed%' 可以发现主备的 gtid_executed 的值是相等的;
log_timestamps
设置日志参数
show variables like ‘log_timestamps’;
set global log_timestamps=system;
binlog_cache_size
作用：
过程：
1.事物开启。
2.执行dml语句，在dml语句第一次执行的时候会分配内存空间binlog cache。
3.执行dml语句期间生成的event不断写入到binlog cache。
4.如果binlog cache的空间已经满了，则将binlog cache的数据写入到binlog临时文件，同时清空binlog cache。如果binlog临时文件的大小大于了max_binlog_cache_size的设置则抛错ERROR 1197 (HY000)。
5.事物提交，整个binlog cache和binlog临时文件数据全部写入到binlog file中进行固化，释放binlog cache和binlog临时文件。但是注意此时binlog cache的内存空间留用供下次事物使用，但是binlog临时文件被截断为0，保留文件描述符。其实也就是IO_CACHE(参考后文)保留，并且保留IO_CACHE中的分配的内存空间，和物理文件描述符。
6.断开连接，这个过程会释放IO_CACHE同时释放其持有的binlog cache内存空间以及持有的binlog 临时文件。
设置binlog事物的大小
show binlog_cache_size like ‘%binlog_cache_size%’;
set global binlog_cache_size= 8m
查看事物的使用情况：
show status like ‘%binlog%’;
stmt就是statement的意思
Binlog_cache_disk_use （事务类）二进志日志缓存的已经存在硬盘的条数
Binlog_cache_use （事务类）二进制日志已缓存的条数（内存中） 注意，这个不是容量，而是事务个数。每次有一条事务提交，都会有一次增加
Binlog_stmt_cache_disk_use （非事务类）二进志日志缓存的已经存在硬盘的条数
Binlog_stmt_cache_use （非事务类）二进制日志已缓存的条数（内存中） 非事务型的语句，都存在这儿，比如MYISAM引擎的表，插入记录就存在这儿
binlog_cache_use和binlog_cache_disk_use两者结合可以用来调整binlog_cache_size的大小
binglog_stmt_cache_use和binlog_stmt_cache_disk_use两者结合可以有来调整 binlog_stmt_cache_size的大小
计算mysql内存总和
per_thread_buffers=(read_buffer_size+read_rnd_buffer_size+sort_buffer_size+thread_stack+join_buffer_size+binlog_cache_size
+tmp_table_size)*max_connections
global_buffers=
innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+key_buffer_size+query_cache_size
total_memory=global_buffers+per_thread_buffers
建议
innodb_flush_method = fdatasync（服务器不是ssd）
innodb_buffer_pool_size可以提升到8G左右，一般到分配内存的50%-80%
innodb_adaptive_hash_index建议关掉
innodb_log_file_size可以调大一点到2G
master_info_repository 从库的建议改成TABLE
relay_log_info_repository 从库的建议改成TABLE
relay_log_recovery 从库的建议改成1
read_only 从库的建议改成1
super_read_only 从库的建议改成1
在从库上master_info_repository="TABLE"和relay_log_info_repository=“TABLE”和relay_log_recovery=1确保在slave上和复制相关的元数据表也采用innodb引擎，受到innodb事务安全的保护，并开启relay-log自动修复机制
innodb_buffer_pool_size调整这个参数期间会阻塞用户的请求，直到调整完毕，建议在低峰期调整
使用like和%的范围查询以及高并发的joins,不适合使用自适应哈希索引，维护哈希索引结构的额外开销会带来严重性能损耗，这种情况建议关掉，set global innodb_adaptive_hash_index=off/on命令
主库执行
set global Innodb_flush_method = fdatasync；
set global innodb_buffer_pool_size = 8589934592
set global innodb_log_file_size= 2147483648
my.cnf 修改
innodb_log_file_size=2048m
从库执行
set global innodb_buffer_pool_size = 8589934592
set global innodb_log_file_size= 2147483648
set global master_info_repository = TABLE
set global relay_log_info_repository = TABLE
set global relay_log_recovery = 1
set global read_only = on
set global super_read_only = on
my.cnf 修改
innodb_flush_method=fdatasync
innodb_log_file_size=2048m
master_info_repository=table
relay_log_info_repository=table
relay_log_recovery=1
read_only=1
super_read_only=1
service mysqld restart
Mysql查询优化
使用最左前缀规则
如果使用联合索引，要遵守最左前缀规则。即要求使用联合索引进行查询，从索引的最左前列开始，不跳过索引中的列并且不能使用范围查询(>、<、between、like)。
小表驱动大表
第一张表是全表索引(要以此关联其他表)，其余表的查询类型type为range(索引区间获得)，也就是6 * 1 * 1，共遍历查询6次即可;
建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描的，以少关联多就可以减少这个扫描次数.
避免全表扫描
mysql在使用不等于(!=或者<>)的时候无法使用导致全表扫描。在查询的时候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描
当只要一行数据时使用 limit 1
查询时如果已知会得到一条数据，这种情况下加上 limit 1 会增加性能。因为 mysql 数据库引
擎会在找到一条结果停止搜索，而不是继续查询下一条是否符合标准直到所有记录查询完毕。
子查询优化
索引不会被命中的场景
创建表
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`age` int(10) NOT NULL DEFAULT '0',
`class_name` varchar(45) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `name` (`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4;
insert into user (name,age,class_name,height) values('sdhwd',1,'dawdawd',1);
insert into user (name,age,class_name,height) values('da',2,'segseg',1);
insert into user (name,age,class_name,height) values('dwd',3,'segfsg',3);
insert into user (name,age,class_name,height) values('ad',4,'sefsg',4);
insert into user (name,age,class_name,height) values('fawf',5,'sefse',5);
insert into user (name,age,class_name,height) values('awda',6,'sgs',6);
insert into user (name,age,class_name,height) values('awdaw',7,'sefse',23);
insert into user (name,age,class_name,height) values('dawdwad',8,'ghs',7);
insert into user (name,age,class_name,height) values('adawda',9,'sefse',5);
insert into user (name,age,class_name,height) values('awdawd',10,'3r3',8);
insert into user (name,age,class_name,height) values('awdaw',111,'sef',9);
insert into user (name,age,class_name,height) values('adafaf',11,'rhgs',6);
insert into user (name,age,class_name,height) values('grgdth',131,'sefs',67);
insert into user (name,age,class_name,height) values('jgyj',113,'rghsr',678);
insert into user (name,age,class_name,height) values('sefse',11,'efr',678);
insert into user (name,age,class_name,height) values('gsrgsf',12,'gseg',8);
insert into user (name,age,class_name,height) values('adwawd',13,'sefes',9);
insert into user (name,age,class_name,height) values('gsgsf',11,'segs',56);
insert into user (name,age,class_name,height) values('dfsef',11,'sefse',56);
insert into user (name,age,class_name,height) values('grhg',13,'gsrg',234);
insert into user (name,age,class_name,height) values('sefgrs',14,'sefse',7);
insert into user (name,age,class_name,height) values('sef',11,'segse',78);
insert into user (name,age,class_name,height) values('grsgs',12,'awdfa',46);
insert into user (name,age,class_name,height) values('fsef',11,'saefef',465);
1、查询语句or条件
如果条件中有 or ，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因）
注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
如果出现OR的一个条件没有索引时，建议使用 union ，拼接多个查询语句
错误例子：select *from user where id = 5 or class_name= ‘sgyt’;
2.索引列模糊查询
like查询是以%开头，索引不会命中
只有一种情况下，只查询索引列，才会用到索引，但是这种情况下跟是否使用%没有关系的，因为查询索引列的时候本身就用到了索引
错误例子：select *from user where name like"%sg";select *from user where name like"%sgyt%";
正确例子: select *from user where name =“sg”;
3.隐式类型转换
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
错误例子：select *from user where name = 1244 ;
4.索引列不能参与计算
查询条件中，使用函数在索引列上，或者对索引列进行运算，运算包括（+，-，*，/）
错误例子：select *from user where id - 1 = 9;
正确例子：select *from user where id = 10;
5.使用函数not in
错误例子：select *from user where name not in (‘sgyt’)
6.order by 的索引生效
解决方案：
错误例子：select *from user where height > 10 order by height;
1.强制索引 FORCE INDEX（key）（一般不建议）
select *from user force index(height) where height > 10 order by height;
2.联合索引
错误例子：select *from user where name = ‘sg’ order by height;
正确例子：select *from user where name = ‘sg’ order by age; # name,age是联合索引
7、使用了<> 不等于 != 这些不走
< 小于 > 大于 <= >= 这个根据实际查询数据来判断，如果全盘扫描速度比索引速度要快则不走索引 。 优化方法： key<>0 改为 key>0 or key<0。
错误例子：select *from user where name != ‘sgyt’;
修改业务场景命中索引
扫一扫
301
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
