一、索引相关
1、索引基数：
基数是数据列所包含的不同值的数量。 例如，某个数据列包含值1、3、7、4、7、7、3，那么它的基数就是4（这个4就代表的是有1、3、7、4一共四种不同的值）。 索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是"30%"。
所以在上述例子中，如果当select 数据where 条件数据列=7时，MySQL更可能不走索引而改成全表扫描，如果where条件数据列=1时候，MySQL很可能走索引而不是全表扫描。
另一个概念叫做索引选择性
索引选择性=索引基数/数据总数，基数可以通过“show index from 表名”查看。
越接近1就越有可能利用索引，也可以理解1为百分百。
高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行，唯一索引的选择性最佳，值为1。
2、索引失效原因：
1、对索引列运算，运算包括（+、-、*、/、！、<>、%、like’%_'（%放在前面）
2、隐式转换导致索引失效，如字段类型为varchar，where条件用number。
3、对索引应用内部函数，这种情况下应该建立基于函数的索引 如select * from template t where ROUND(t.logicdb_id) = 1 此时应该建ROUND(t.logicdb_id)为索引，mysql8.0开始支持函数索引，5.7可以通过虚拟列的方式来支持，之前只能新建一个ROUND(t.logicdb_id)列然后去维护
4、如果条件有or，即使其中有条件带索引也不会使用（这也是为什么建议少使用or的原因），如果想使用or，又想索引有效，只能将or条件中的每个列加上索引；
5、如果列类型是字符串，那一定要在条件中数据使用引号，否则不使用索引；
6、B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走；
7、复合索引遵循最左原则；
8、如果mysql觉得全表扫描更快时（数据少）；
3、索引的建立
1、最重要的肯定是根据业务经常查询的语句。
2、尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)，也就是索引选择性，表示字段不重复的比率，比率越大我们扫描的记录数就越少，就越合适作为索引。
3、如果业务中唯一特性最好建立唯一键，一方面可以保证数据的正确性，另一方面索引的效率能大大提高。
二、执行计划 基本用法
1、desc 或者 explain 加上你的sql
2、extended explain加上你的sql，然后通过show warnings可以查看实际执行的语句，这一点也是非常有用的，很多时候不同的写法经过sql分析之后实际执行的代码是一样的。
执行计划结果说明
列名说明
id：SELECT识别符，这是SELECT的查询序列号。
select_type：SELECT类型，可以为以下任何一种：
- SIMPLE：简单SELECT(不使用UNION或子查询)。
- PRIMARY：最外面的SELECT。
- UNION：UNION中的第二个或后面的SELECT语句。
- DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询。
- UNION RESULT：UNION 的结果。
- SUBQUERY：子查询中的第一个SELECT。
- DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询。
- DERIVED：导出表的SELECT(FROM子句的子查询)。 table输出的行所引用的表。
partitions：如果查询是基于分区表的话，显示查询将访问的分区。
type：联接类型。按照从最佳类型到最坏类型进行排序：
- system：表仅有一行(=系统表)。这是const联接类型的一个特例。
- const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次！
- eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。
- ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。
- index_merge：该联接类型表示使用了索引合并优化方法。
- unique_subquery：该类型替换了下面形式的IN子查询的ref： value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
- index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)range：只检索给定范围的行，使用一个索引来选择行。
- index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。
- ALL：对于每个来自于先前的表的行组合，进行完整的表扫描，说明查询就需要优化了。一般来说，得保证查询至少达到range级别，最好能达到ref。
possible_keys：指出MySQL能使用哪个索引在该表中找到行。
key：显示MySQL实际决定使用的键(索引)。如果没有选择索引，键是NULL。
key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。在不损失精确性的情况下，长度越短越好。
ref：显示使用哪个列或常数与key一起从表中选择行。
rows：显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
filtered：显示了通过条件过滤出的行数的百分比估计值。
Extra
1、using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” ，其实不一定是文件排序，内部使用的是快排
2、using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
3、using index： 表示相应的SELECT操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。
4、impossible where： WHERE子句的值总是false，不能用来获取任何元组
5、select tables optimized away： 在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作， 不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
6、distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的操作using filesort,using temporary这两项出现时需要注意下，这两项是十分耗费性能的，在使用group by的时候，虽然没有使用order by，如果没有索引，是可能同时出现using filesort,using temporary的，因为group by就是先排序在分组，如果没有排序的需要，可以加上一个order by NULL来避免排序，这样using filesort就会去除，能提升一点性能。
提高性能的特性
1、索引覆盖(covering index)：需要查询的数据在索引上都可以查到不需要回表 EXTRA列显示using index
2、ICP特性(Index Condition Pushdown)：本来index仅仅是data access的一种访问模式，存数引擎通过索引回表获取的数据会传递到MySQL server层进行where条件过滤,5.6版本开始当ICP打开时，如果部分where条件能使用索引的字段，MySQL server会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤。EXTRA显示using index condition。需要了解mysql的架构图分为server和存储引擎层
3、索引合并(index merge)：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。一般用OR会用到，如果是AND条件，考虑建立复合索引。EXPLAIN显示的索引类型会显示index_merge，EXTRA会显示具体的合并算法和用到的索引
三、SQL语句总结
常用的但容易忘的：
1、如果有主键或者唯一键冲突则不插入：insert ignore into
2、如果有主键或者唯一键冲突则更新,注意这个会影响自增的增量：INSERT INTO room_remarks(room_id,room_remarks) VALUE(1,“sdf”) ON DUPLICATE KEY UPDATE room_remarks=“234”
3、如果有就用新的替代，values如果不包含自增列，自增列的值会变化：REPLACE INTO room_remarks(room_id,room_remarks) VALUE(1,“sdf”)
4、备份表：CREATE TABLE user_info SELECT * FROM user_info
5、复制表结构：CREATE TABLE user_v2 LIKE user
6、从查询语句中导入：INSERT INTO user_v2 SELECT * FROM user或者INSERT INTO user_v2(id,num) SELECT id,num FROM user
7、连表更新：UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id
8、连表删除：DELETE user FROM user,black WHERE user.id=black.id
锁相关(作为了解，很少用)
1、共享锁： select id from tb_test where id = 1 lock in share mode;
2、排它锁： select id from tb_test where id = 1 for update
优化时用到：
1、强制使用某个索引： select * from table force index(idx_user) limit 2;
2、禁止使用某个索引：select * from table ignore index(idx_user) limit 2;
3、禁用缓存(在测试时去除缓存的影响)： select SQL_NO_CACHE from table limit 2;
查看状态
1、查看字符集 SHOW VARIABLES LIKE ‘character_set%’;
2、查看排序规则 SHOW VARIABLES LIKE ‘collation%’;
四、SQL编写注意
1、where语句的解析顺序是从右到左，条件尽量放where不要放having
2、采用延迟关联(deferred join)技术优化超多分页场景，比如limit 10000,10,延迟关联可以避免回表
3、distinct语句非常损耗性能，可以通过group by来优化
4、连表尽量不要超过三个表
五、踩坑
1、如果有自增列，truncate语句会把自增列的基数重置为0，有些场景用自增列作为业务上的id需要十分重视
2、聚合函数会自动滤空，比如a列的类型是int且全部是NULL，则SUM(a)返回的是NULL而不是0
3、mysql判断null相等不能用“a=null”,这个结果永远为UnKnown，where和having中,UnKnown永远被视为false，check约束中，UnKnown就会视为true来处理。所以要用“a is null”处理
六、千万大表在线修改
mysql在表数据量很大的时候，如果修改表结构会导致锁表，业务请求被阻塞。mysql在5.6之后引入了在线更新，但是在某些情况下还是会锁表，所以一般都采用pt工具( Percona Toolkit)
如对表添加索引： 如下： pt-online-schema-change --user=‘root’ --host=‘localhost’ --ask-pass --alter “add index idx_user_id(room_id,create_time)” D=fission_show_room_v2,t=room_favorite_info --execute
七、慢查询日志
有时候如果线上请求超时，应该去关注下慢查询日志，慢查询的分析很简单，先找到慢查询日志文件的位置，然后利用mysqldumpslow去分析。查询慢查询日志信息可以直接通过执行sql命令查看相关变量，常用的sql如下：
– 查看慢查询配置
– slow_query_log 慢查询日志是否开启，可以使用show variables like ‘slov_query_log’查看是否开启，如果状态值为OFF，可以使用set GLOBAL slow_query_log = on来开启，它会在datadir下产生一个xxx-slow.log的文件。
– slow_query_log_file 的值是记录的慢查询日志到文件中
– long_query_time 指定了慢查询的阈值
– log_queries_not_using_indexes 是否记录所有没有利用索引的查询 SHOW VARIABLES LIKE ‘%quer%’;
– 查看慢查询是日志还是表的形式 SHOW VARIABLES LIKE ‘log_output’
– 查看慢查询的数量
mysqldumpslow的工具十分简单，我主要用到的是参数如下：
-t:限制输出的行数，我一般取前十条就够了 -s:根据什么来排序默认是平均查询时间at，我还经常用到c查询次数，因为查询次数很频繁但是时间不高也是有必要优化的，还有t查询时间，查看那个语句特别卡。 -v:输出详细信息
例子：mysqldumpslow -v -s t -t 10 mysql_slow.log.2018-11-20-0500
八、查看sql进程和杀死进程
如果你执行了一个sql的操作，但是迟迟没有返回，你可以通过查询进程列表看看他的实际执行状况，如果该sql十分耗时，为了避免影响线上可以用kill命令杀死进程，通过查看进程列表也能直观的看下当前sql的执行状态，如果当前数据库负载很高，在进程列表可能会出现，大量的进程夯住，执行时间很长。命令如下：
–查看进程列表 SHOW PROCESSLIST;
–杀死某个进程 kill 183665
2842
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
