浅谈mysql性能优化

青春须早为，岂能长少年。这篇文章主要讲述浅谈mysql性能优化相关的知识，希望能为你提供帮助。
写下这篇文章就是为了记录一下对索引的优化，合理建立索引。
什么是索引mysql官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。
我们可以简单理解为：快速查找排好序的一种数据结构。Mysql索引主要有两种结构：B+Tree索引和Hash索引。
索引就相当于字典前的目录，如果这个目录划分规划更好，那么我们找到想要的数据就会更方便，也就提高了查询的效率。
索引的类型有：

normal：表示普通索引
unique：表示唯一的，不允许有重复值的索引
full textl: 表示全文搜索的索引，用于搜索很长一篇文章的时候，效果最好。

建立索引的场景索引不是越多越好，因为每次更新、插入数据，就需要对索引文件进行变动，会减低该类型操作的执行效率。
如果建立索引的字段太多，影响就会很大。
所以我们只在合理的字段上建立索引。

在经常用来当查询条件(where,on,group by,order by)的字段上建立索引。
在数据的维度比较大的字段上建立索引。
对数据较小的列使用索引，可以使索引文件更小，同时内存中也可以装载更多的索引键。
为较长的字符串使用前缀索引。比如数据的长度大部分是150个字节，我们只建立前100个字节的索引。

常见的可以用于建立索引的字段场景：
① 用户id
在订单表中的用户id字段上建立索引，根据用户id筛选订单，则会很快查询出用户的订单。
用户一般是在自己的后台查看订单，所以表中的其他用户数据与他无关，如果没有建立索引，每次查询都是全表扫描，则会很慢。（我们每个人在淘宝平台上占的订单比例都不到0.0001）
② 商品名
同上，如果不建立索引，我们在淘宝输入框搜索键盘，怎么快速查询出键盘商品？
③ 日期
常用的活跃数据，一般都是是最近产生的，很少人没事去翻半年前的订单数据吧？
合适的还有很多很多场景，需要各位小伙伴自己好好思量。
索引不生效的场景并不是建立了索引就在查询语句中生效。

当语句中带有or的时候，索引会失效
当索引的字段使用like查询，并且使用了前通配比如%Siam，索引失效。后通配会生效Siam%
当索引的字段是字符类型，但是储存的值是数字，比如 user_name:’123456’，在查询语句中要 user_name = ‘123456’而不能 user_name = 123456 否则发生类型转换，索引失效，其他类型的字段比如日期等也同理
当使用的条件语句，预计结果数量超过全表数据的一定比例时，会转为全表扫描（mysql一般是30%左右）这就是为什么在建立索引的时候要选择维度（区别度）比较高的列，性别这种字段不适合建立索引。
语句中出现列数据运算才判断的，比如where age – 10 > 0 每一行都要运算之后才知道是否大于0 所以就是全表扫描，如果age > 10 则可使用索引。使用函数转换列数据也一样原理。
组合索引时使用的条件语句。

mysql中，多个索引同时使用？先来看一条语句

select * from test where user_name = ``siam_007` `and` `create_time = 1563280050

假设在这个表中，user_name字段和create_time都是建立了索引的。也没有发生数据类型转换等情况。
是2个吗？并不是这样子的。
虽然两个字段的索引都没有问题，如果单独使用也都能生效。
但是：MySQL会从可用的索引中猜测出效率最高的一个索引并使用它
怎么证明？使用explain语句（详解可以在网上其他资料找到）
只要在sql语句最前面加上该语法，则会显示查询类型相关的信息。

Explain select * from mysql_index_test where user_name = ``siam_007` `and` `create_time = 1563280050

文章图片

我建立了两个索引，都能用，都是本次查询只能使用一个。

组合索引
多个单字段索引冲突

组合索引先来说说组合索引吧，我们在新建索引的时候（可视化软件），是这样子的页面

文章图片

当我们勾选上2个或者2个以上就能把它叫为组合索引了
可以看到还有调整顺序的功能
在组合索引中，字段顺序也是极为重要的。
假设我们有这样一个索引：new : a,b,c 字段
【浅谈mysql性能优化】在查询时使用where a = 1 and b = 2 and c= 3。那么这个语句肯定是生效的。
如果我们使用where b = 2 and c =3没有a条件而a又是在组合索引中最左侧的，那么索引就不会生效。
还是要扫描全部行
where a,c where a,b where b,a,c 这样子使用都是可以生效的。
组合索引要注意字段顺序，是指在创建索引时候的排序，而不是sql语句中where的顺序，我们使用where b = 2 and a = 1 and c = 3也是可以生效的
那么组合索引的字段顺序要如何排比较好?
后面会有新的一篇文章会讲这个
多个单字段索引冲突如果我们在表中，拥有两个索引，比如user_name 用户名, user_phone 用户手机号
现在有这样子的数据量：
100W条数据 user_name=’我是用户名’
100条数据 user_phone=’110′
5条数据 user_name=’我是用户名’ and user_phone=’110′
假设有这样子一条语句：

select * from test where user_name = ``我是用户名` `and` `user_phone=``110

有两个字段都有索引可用，mysql会选择一个使用。这是属于mysql的内部处理判断
正常情况下，如果用user_phone索引生效的话，会很快得到结果（先筛选出100条再筛选）
如果user_name生效，则要先筛选100W条数据，再筛选user_phone
mysql的内部判断可能使得user_name索引生效，此时效率就会很低了，我们可以强制使用某个索引
phone是索引名是新建的时候填的