同乐学堂一、索引优缺点以及索引的设计：
1、大大减少对服务器扫描的数据量
2、避免排序和临时表
3、可以将随机IO,变为顺序IO
缺点：会消耗磁盘空间，让插入，更新、删除，变慢。
适用于：中大型表。
补充：在特大表的情况下，如索引优化已达到瓶颈，且前端应用程序无法进行调优SQL,以及没有使用分库分表的技术考量
量下，可以使用数据库的分区技术，来减少对MySQL表的大范围扫描。来补充索引的不足！
索引设计：前期，一定要在系统上线之前设计好较完美的索引
。
后期，采集生产环境的工作负载样本，然后在该工作负载下为Select语句生成一组候选索引。
二、索引总结
三、explan 查询sql语句的执行计划
四、索引优化目标：
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索引 不包含ｂ的索引。
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 http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
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
备注：
如果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
原创文章，转载请注明出处以及原文链接
微信扫一扫，打赏作者吧～
