mysql 索引优化

索引 失效的常见情况

1. 复合索引多个列时，查询条件中没有索引第一列时索引会失效，如果跳过中间列，索引也只能用到部分列，中断的地方后索引列没有使用到。
2. 不要在索引列上做任务计算操作，函数，类型转换，因为会导致索引失效全表扫描。
3. 索引列使用范围查询会导致复合索引中后面的索引列失效
4. 使用!= ，<>，is null ,is not null 会导致索引失效
5. 以like ‘%aaa’ 查询会导致索引失效，可以使用覆盖索引防止失效
6. 字符串不加单引号索引会失效
7. 少用or,or连接导致索引失效

– 删除表索引
drop index idx_article_ccv on article;
– 创建表索引
create index idx_article_ccv on article (category_id,views);
– 显示表索引
show INDEX from article;

– 查看慢SQL日志记录是否开启
show VARIABLES like ‘%slow_query_log%’;
– 开启 只对当前数据库有效，MYSQL重启则失效，要永久生效则修改MYSQL的配置文件
set GLOBAL slow_query_log=1;
slow_query_log_file 慢查询日志文件路径
– 查看慢查询时间值
show VARIABLES like ‘%long_query_time%’;
– 设置慢查询时间值
set GLOBAL long_query_time=3;
– show PROFILES 默认关闭的 只保存最近15次的运行结果
– 查看是否打开 PROFILES
show VARIABLES like ‘%profiling%’
– 打开
set profiling=on
– 查看 show PROFILES
show PROFILES;
– 诊断SQL
show PROFILE cpu,block io for query 78;
– 显示列信息
ALL 显示所有开销
BLOCK IO 显示块开销
CONTEXT SWITCHES 上下文切换开销
CPU CPU开销信息
IPC 显示发送和接收的相关开销
MEMORY 显示内存相关开销
PAGE FAULTS 显示页面错误相关开销
SOURCE 显示SURCE_FUNCTION,SOURCE_FILE,SOURCE_LINE相关的开销信息
SWAPS 显示交换次数相关开销信息
– status 出现以下的是很严重的需要性能优化的
converting HEAP to MyISAM 查询结果太大，内存不够用在往磁盘上搬数据
Creating tmp table 创建了临时表
Copying to tmp table on disk 把内存中的表复制到磁盘
locked
– 开启全局查询功能 记录所有SQL
set GLOBAL general_log=1;
– 以表格形式输出
set global log_output=‘TABLE’;
– 查看SQL记录
select * from mysql.general_log;

– 查询被锁的表
show open tables;

show status like ‘table%’;
– Table_locks_immediate 产生表级锁的次数
– Table_locks_waited 出现表级锁发生锁挣用产生等待的次数

– 查看事务隔离等级
show variables like ‘tx_isolation’;