快乐学习
前程无忧、中华英才非你莫属!

51CTO-自学派MySQL运维优化—》索引优化

一、索引优缺点以及索引的设计:

1、大大减少对服务器扫描的数据量
2、避免排序和临时表
3、可以将随机IO,变为顺序IO
缺点会消耗磁盘空间,让插入,更新、删除,变慢。
适用于:中大型表。
补充:在特大表的情况下,如索引优化已达到瓶颈,且前端应用程序无法进行调优SQL,以及没有使用分库分表的技术考量
量下,可以使用数据库的分区技术,来减少对MySQL表的大范围扫描。来补充索引的不足!
索引设计:前期,一定要在系统上线之前设计好较完美的索引
                后期,采集生产环境的工作负载样本,然后在该工作负载下为Select语句生成一组候选索引。
  

二、索引总结
http://www.ztloo.com/2017/05/15/day22-mysql之详解索引篇/

三、explan 查询sql语句的执行计划
http://www.ztloo.com/2017/05/28/day31-(转载)mysql-explain执行计划解读/

四、索引优化目标:
1、找到由于索引不合适而导致运行太慢的查询语句
2、设计索引,让所有查询语句都运行的足够快。(表的维护(插入、更新、删除)也必须足够快)

五、、索引创建规则
 
1、经常查询的字段要创建索引
 
2、唯一性太差的字段。即使需要经常查询,也不适合创建创建单列索引,如,性别 、状态、类型的这些字段,总共就辣么几个值重复使用。优化器会选择不使用,并带来极大的性能隐患。
 
3、更新频繁的字段不适合创建索引,
 
4、不会出现在WHERE字句中的字段不应该创建索引。
 
5、包含多个字段的where过滤条件语句,一定要建立组合索引。
总结
1、对于单列索引,尽量选择针对当前query过滤性能更好的索引。
 
2、在选择组合索引时,当前query中过滤性能最好的字段在索引字段排列越靠前越好。
 
 
3、尽量选择可以包含当前Query的WHERE字句更多字段的说明;

六、慢查询日志管理
配置文件
【mysqld】
// 开启慢查询
slow_query_log = on
// 设置慢查询时间 1 s
long_query_time=1
// 慢查询日志保存路径
slow_query_log_file=”/data/mysql/slow_query.log”
// 未使用索引的查询也被记录到慢查询日志中(可选项,,默认是off)。
log_queries_not_using_indexes = on   
在线设置,需要重新打开连接和会话才能生效,备注(当前的客户端的链接是不生效的!)!
show variables like ‘%quer%’;
set global slow_query_log = on;
set global  long_query_time=0.01;
set global   log_queries_not_using_indexes = on

七、索引的限制
1、 InnoDB存储引的最大索引长度256字节、
2、BLOB和TEXT类型的列,只能创建前缀索引。
3、目前不支持函数索引。
4、使用不等于、is null 和is not null ,not in, not like, <>,  ,!>,!<  )这些负向查询 ,无法使用索引
5、过滤字段使用了函数运算,无法使用索引
6、Join语句中Join条件字段类型不一致的时候,无法使用索引
7、使用like操作符时,已%开头的无法使用索引。
8、where 语句里面如果带有or条件, myisam表能用到索引, innodb表不行。
*(注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引)
9、如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引。
10、复合索引的情况下,查询条件不满足索引最左的原则,无法使用索引。
         index(a,b)多列索引只包含了  a、  ab索引    不包含b的索引。
11、独立的列 索引 不能是表达式的一部分


八、分析索引使用情况
pt-index-usage   
*(总结:最好不要直接在线上使用,应该作为上线辅助或故障后离线分析的工具,也可以做性能测试的时候配合着使用。)
功能:
从log文件中读取查询语句,并用explain分析他们是如何利用索引。完成分析之后会生成一份关于索引没有被查询使用过的报告。
用法:
从满查询中的sql查看索引使用情况范例:
pt-index-usage slave1-slow.log –h localhost –password xxxxx
将分析结果保存到数据库范例:

八、怎么创建索引
1. SELECT A, B, D, E  
2.   FROM mydb
3.  WHERE B BETWEEN :B1 AND :B2   — (FF = 1…10%)  
4.    AND C = 1                   — (FF = 2%)  
5.    AND E > 0                   — (FF = 50%)  
6.    AND F = :F                  — (FF = 0.1…1%)  
7.  ORDER BY A, B, C, F  
FF=是条件字段占查询需要扫描行数的占比。
然后根据FF的占比,设计最佳索引为INDEX(F,C,B,E,A,D)
拆分详解,首先根据ff占比设计 (F,C,B,E,),然后在根据任意顺序将SELECT 语句中的其余的列添加至索引中(但是需要以  不易变的列开始)。
最终为(F,C,B,E,A,D)

九、 索引优化工具
SQLAdvisor
SQLAdvisor 是由美团点评公司北京DBA团队开发维护的 SQL 优化工具:输入SQL,输出索引优化建议,减轻了DBA日常索引维护,SQL审核的工作,放飞双手。
安装过程
yum install -y git
yum install -y cmake libaio-devel libffi-devel glib2 glib2-devel bison
yum install Percona-Server-shared-56 -y
cd /usr/lib64/
ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so
cd /usr/local/SQLAdvisor/
cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./    (如果编译不成功,按照下面重新安装cmake,并删除之前的/usr/local/SQLAdvisor/)
make && make install
cd ./sqladvisor/
cmake -DCMAKE_BUILD_TYPE=debug ./
make
cp /usr/local/SQLAdvisor/sqladvisor/sqladvisor /usr/bin/sqladvisor
在/etc/下新建sql.cnf
[sqladvisor]
username=xxx
password=xxx
host=xxx
port=xxx
dbname=xxx
测试:
sqladvisor -f /etc/sql.cnf -q “SELECT *  FROM customer  WHERE  c_id = 998 ” -v 1    
   
https://github.com/zyw/sqladvisor-web

备注:
如果cmake,编译出错,本地安装cmake,进行重新编译SQLAdvisor
yum -y remove cmake  卸载掉。
cmake,如果问题,重装,进行本地安装。
yum install gcc gcc-c++ ncurses-devel perl
tar zxvf cmake-3.3.2.tar.gz (因为网速问题可以用迅雷下载,然后传到服务器上,如果服务器网速快,直接wget https://cmake.org/files/v3.3/cmake-3.3.2.tar.gz
cd cmake-3.3.2
./configure
make
make install
mv cmake-3.3.2 cmake // 修改文件夹名
vi /etc/profile
PATH=/usr/local/cmake/bin:$PAT
source /etc/profile
cmake –version
http://www.ttlsa.com/mysql/mysql-manager-tools-mysql-utilities-tutorial/

原创文章,转载请注明出处以及原文链接
打赏

未经允许不得转载:同乐学堂 » 51CTO-自学派MySQL运维优化—》索引优化

分享到:更多 ()

评论 1

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
  1. #1

    testpost接口

    zhangtongle7个月前 (12-09)回复

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!