mysql 良好的安全连接，自带查询解析，sql语句优化，使用读写锁（细化到行），事务隔离和多版本并发控制提高并发，完备的事务日志记录，强大的存储引擎提供高效查询（表记录可达百万级）。如果是innerdb还可在崩溃后进行完整的恢复。优点非常多，但是还是需要优化
1，SQL优化的一般步棸
1.1，查看sql执行频率
show status like 'Com_%' ; // Com_select :执行select操作的次数，一次累计加1。其他类似
以下只针对InnoDB存储引擎，累加算法略有不同 show status like 'innodb_rows_%'
Innodb_rows_read：select 查询操作插入的行数
Innodb_rows_inserted/updated/deleted：执行insert/update/delete 操作的行数
通过以上的参数，可以了解数据库应用是以查询为主还是写入数据为主。
对于事务性的应用，通过Com_commit和Com_rollback可以了解事务的提交和回滚情况，对于回滚操作非常的频繁的数据库，可能意味着应用编写的存在问题。
基本情况了解：
Connections：试图链接数据库服务的次数
Uptime：服务器工作时间
slow_queries：慢查询次数
1.2，定位执行效率比较低的sql语句
通过慢查询日志定位慢的SQl语句，用--slow_query_log_file[=file_name](mysql5.6之后) 选项启动中，mysqld会写一个所有执行时间超过long_query_time秒的SQL语句的日志文件。
使用SHOW FULL PROCESSLIST：查看当前Mysql在进行的线程，同时对一些锁表操作进行优化
slow_query_log_file=/var/lib/mysql/Dragonwake-slow.log // # 将查询执行时间较慢的语句进行记录 mysql配置文件 /etc/my.cnf 。 如果未指定file_name，默认为host_name-slow.errmysql5.6之前 log-slow-queries
long_query_time=1 # 执行超过1秒的sql会被log下来
日志查询 show variables like 'log_%'
二进制日志数目：show master logs; // 必须使用二进制日志才可以使用查询
1.3，通过explain分析慢SQLi
explain SQl语句
结果：
--select_type：表示select的类型，常见的类型是SIMPLE (既不是用表连接或子查询)，PRIMARY(主查询，即外层的查询)，UNION(Union中的第二个或后面的查询语句)，SUBQUERY(子查询中的第一个select查询)等
--table：输出结果的表名
--type：表示MYSQL在表中找到行的方式，或者叫访问类型
常见的有 ALl index range ref eq_ref const system NUll 从左到右性能由差到好
type=all 全表扫描
type=index 索引全扫描，mysql遍历所有索引来查询
type=range：索引范围扫描，常见于<，<= ，> , >= between
type=ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值得匹配
type=eq_ref 类似ref 就在于使用得索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key（主键）和unique index（唯一索引）作为关联条件
type=const/system 但表中最多有一个匹配行，查询起来非常迅速，一般主键primary key或唯一索引unique index进行得查询，通过唯一索引ek_email访问得时候，类型type为const；而从我们构造的仅有一条记录得a表中检索时，类型type为system
type=null ：mysql不用访问表或索引，就得到就结果
类型type还有其他值，如ref_or_null(与ref相似，区别在于条件包含对null的查询)，index_merge(索引合并优化),unique_subquery( in 后面是一个查询主键字段的子查询 )，index_subquery(与unique_subquery相似，区别在于 in 后面时一个查询非唯一索引字段的子查询)
--possile-key：表示查询时可能使用的索引
--key：实际使用的索引
--key-len：使用到索引字段的长度
--rows ：扫描行的数量
--Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息
Using where ：表示优化器除了利用索引来加速访问之外，还需要索引回表查询数据
1.4，通过show profile 分析SQL
查看当前mysql是否支持profile
默认profile是关闭的，可以通过set语句在session级别开启profiling, set profiling=1
使用方法：
-执行统计查询
查询上面的SQl query ID
查询上述执行过程中每个线程的状态和消耗时间
Sending data 表示mysql线程开始访问数据并行把结果返回客户端，而不仅仅是返回结果给客户端。由于在Sending data 状态下，mysql线程往往需要大量的磁盘读取操作，所以是整个查询中耗时最长的。
查看详细细节并排序
SELECT
STATE,
SUM(DURATION) AS TR,
ROUND(
100 * SUM(DURATION) / (
SELECT
SUM(DURATION)
FROM
information_schema.PROFILING
WHERE
QUERY_ID = 190
),
2
) AS PR,
COUNT(*) AS Calls,
SUM(DURATION) / COUNT(*) AS "R/Call"
FROM
information_schema.PROFILING
WHERE
QUERY_ID = 190
GROUP BY
STATE
ORDER BY
TR DESC;
进一步获取all,cpu,block io , context switch , page faults 等明细来查看mysql在使用什么资源时耗费了过多的时间
此时可通过获取Sending data 时间消耗在cpu
注：InnoDB引擎count(*)没有myISAM 执行速度快，就是因为InnoDB经历Sending data状态，存在访问数据的过程，而MyISAM引擎的表在executing（执行）之后就直接结束查询，完全不需要访问数据。
2，索引问题
索引是数据库里最常见也是最重要的手段之一，通过索引可以帮助用户解决大部分的SQL性能问题
2.1，存储引擎的分类
- B-Tree索引：最常见的索引类型，大部分引擎都支持B树索引
- Hash 索引：只有Memory引擎支持
- R-Tree索引：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型
- Full-text ：全文索引是MyISAM的一种特殊的索引类型，主要用于全文索引，InnoDB从mysql5.6开始支持
Mysql不支持函数索引，但是能对列的前面一部分进行索引，例如：字段title，可以只取title字段前10个字符进行索引，只是在order by和group by 操作的时候无法使用。前缀索引创建例子：create index idx_title on film(title(10))
常用的索引是B-Tree和Hash。Hash是只有memory和Heap引擎支持。适用于key-value查询，通常Hash比B-Tree更迅速，Hash不使用范围索引。Memory和Heap只有在 = 条件下才能使用索引
2.2 如何使用索引
创建一个复合索引：alter table tb_name add index ids_rental_data(rental_data,inventory_id,customer_id)
2.2.1 mysql能够使用索引的典型场景
- 匹配全值，对索引中所有列都指定具体值，既是对所有列都有等值匹配条件。
比如上述的创建， ids_rental_data 包含rental_data,inventory_id,customer_id，如果此时where自句中包含三者，则为全值匹配
- 匹配的值进行范围查找，对索引的值能进行范围查找
-- 匹配最左前缀，意思是，在复合索引中，索引是从左边第一个查找的，不会跨过第一个从第二个查找。比如一个联合索引包含（c1 ,c2 ,c3）,可不能呗c2和c2+c3利用到
添加索引 alter table payment add index idx_payment_data(payment_date,amount,last_update) ; 此时第一个字段是payment-date
如果查询条件包含索引的第一个 payment_date ,能够使用复合索引idx_payment_data 进行过滤
如果使用第二个 amount没有使用第一个payment_date，则 不会使用索引
-- 仅仅对索引字段进行查询，意思是查询的字段都在索引字段中，查询的效率更高。
那么直接访问索引就可以获取所需的数据，不需要索引回表。此时的Extra 就变成了Using index。Using index代表覆盖索引扫描
- 匹配列前缀 ，仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找；
例如：创建索引 alter table yk_book add index idx_book_diata(book(5),type(3));
查询可以看到idx_book_diata被使用，Using where 表示优化器需要索引回表来查询数据
-匹配部分精准，其他部分范围匹配
指定数量 ,不同的客户编号
类型type为range说明优化器选择范围查询 ，索引key为idx_book_data表示优化器选择索引idx_book_data帮助加速查询，同时所查询的字段在索引中，索引Extra中能看到Using index
- 列名 is null 此种情况下会使用索引。
2.2.2存在索引但不能使用的典型场景
- 以 %开头的like查询，不能使用B-Tree索引
B-Tree索引，以%开头的like查询不能使用索引，我们可以通过全文索引（Fulltext）来解决问题，或者使用InnoDB上的二级索引，首先获取满足条件的列表id,之后再根据主键回表去检索记录
- 数据类型出现隐式转换不会使用索引，有些列是字符串，再写where条件时，需要将常量值用引号括起来
- 复合索引查询条件必须包含最左部分，否则不会使用索引。即leftmost
- mysql 执行语句时会有优化器选择的过程，当全表扫描的代价小于索引扫描的代价时，会使用全表扫描，所以需要更换一个筛选性更高的条件
- 用or分开的条件，如果or前的条件有索引，or后的条件没有索引，则不会使用索引
2.3查看索引的使用情况
如果索引正在工作，Handle_read_key 的值将会很高，这个值代表了一行被索引值读的次数。如果很低说明增加索引增加的性能不高，因为索引并没有被经常使用。
Handle_read_rnd_next的值高说明查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读取下一行的请求数。如果值比较大，说明正在进行大量的表扫描，则通常说明表索引不正确或写入的查询没有利用索引。
3，常见的SQL优化
3.1 大批量插入数据
-- MyISAM
- 打开或关闭MyISAM表 非唯一索引的更新，可以提高导入效率（导入数据到非空MyISAM表）
步棸：一步：alter table tb_name disable key ;//disable关闭表的非唯一索引的使用
二步：导入数据
三步： alter table tb_name enable key // enable 开启表的非唯一索引的使用
导入数据到一个空的MyISAM表，默认先导入数据在创建索引，所以不用设置
-- InnoDB
- 因为InnoDB类型的表按照主键顺序保存的，所以导入的数据是按照主键的顺序排列，可以有效的提高导入数据的效率
- 关闭唯一性效应，set unique_checks = 0，导入数据后再恢复
- 如果使用的是自动提交的方式，再导入前使用set AUTOCOMMIT = 0，导入结束后在恢复
3.2 优化 insert 语句
- 同一客户端插入很多行，应尽量使用多个值的insert语句。例如，insert into tb_name (),(),(),();
- 不同客户端插入很多行，可以使用 insert delayed , delayed含义是让insert语句放置到内存的队列中，并没有写入磁盘。
low_priority是在所有其他用户对读写完成后才进行插入。
low_priority 低优先级 update [low_priority] tb_name set col_name = exp1 ,col_name2 = exp2........ //mysql中update 用low_priority 不锁定表
LOW_PRIORITY 关键字应用于 delete ,insert ,load data , update 和replace
HIGH_PRIORITY 关键字应用于 select 和insert语句
delayed 应用于insert和replace语句
- 将索引文件和数据文件分别放置在不同的磁盘
- MyISAM 如果进行批量插入，增加bulk_insert_buffer_size的值
- 从文件装载一个表时，使用Load data infile , 比insert语句快20倍
3.3 优化 order by 语句
3.3.1 mysql排序方式
- 通过有序索引顺序扫描直接返回有序数据。
在book表上有索引 uid 指向字段 uid
查询表的索引信息
此时使用uid排序时，Extra 时Using index 无需进行回表操作，不需要额外的排序，操作效率高
-- 通过fileSort 排序，不通过索引直接返回排序结果的排序都叫做fileSort排序。mysql服务器对排序参数的设置和需要排序数据的大小来决定排序操作是否使用磁盘文件或临时表。
fileSort是通过算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区进行排序，如果内存装不下，就会将磁盘上的数据进行分块，在对各个数据块进行排序，然后合并。sort_buffer_size的排序区为线程独占，可能同时存在多个
比如通过uid排序所有客户记录时，此时为全表扫描，并且使用了filesort
一般优化方式：减少额外的排序，通过索引直接返回有序数据。尽量使where条件和order by使用相同的索引，并且order by顺序和索引数据相同，并且order by的字段都是升序或者降序，否则肯定会出现fileSort
-- 不会使用索引的情况：
- order by的字段混合使用asc和desc select * from tb_name order p_key1 desc,p_key2 asc
- 对于查询的关键字和order by所使用的不同 select * from tb_name where key1=content order by key2
- 对不同的关键字使用order by : select * from tb_name order by key1,key2
3.3.2 优化fileSort
fileSort 有两种排序算法
- 两次扫描算法：首先根据条件取出排序字段和行指针信息，之后再排序区sort buffer中排序。如果排序区sort buffer不够，则在临时表Temporary Table中存储排序结果，完成排序后根据行指针回表读取记录。需要两次访问数据，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，第二次读取操作可能导致大量随机I/O操作，优点是排序的时候内存开销比较少。
- 一次扫描算法：一次性取出满足条件行的所有字段，然后再排序区 sort buffer 中排序后直接输出结果集。排序的内存开销比较大，但排序效率比两次扫描算法高
mysql 通过比较系统变量max_length_for_sort_data 的大小和query语句取出的字段总大小来判断使用哪种算法，max_length_for_sort_data大使用第二种，否则使用第一种
适当加大系统变量max_length_for_sort_data的值，能够让mysql选择更优化的fileSort的排序算法。但是过大会引起cpu的利用率过低和磁盘I/O过高
适当加大sort_buffer_size排序区，尽量让排序再内存区完成，而不是通过创建临时表放在文件中完成；该大小需要考虑数据库活动连接数和服务器内存的大小来适当设置排序区。因为这个参数是每个线程独占的，如果设置过大，会导致服务器swap严重。尽量使用必要的字段而不是 select *
3.3.3 优化group by
默认情况下，mysql对所有的group by的字段进行排序。如果查询包括group by 但是用户想要排除掉排序结果的消耗，则可以指定order by null禁止排序
select xxx from tb_name group by xxx order by null
3.3.4 优化嵌套查询
使用子查询可以一次性完成很多逻辑上需要多个步棸才能完成的sql操作，同时可以避免事务或者表锁死。子查询可以被更有效的连接 join 代替
3.3.5 优化or条件
对于含有or的查询子句，如果要利用索引，如果要利用索引，则or之间的每个条件都要用到索引
3.3.6 优化分页索引
一般分页查询时，通过创建覆盖索引能够比较好的提高性能，但是当分页为1000 20时，此时排序前1020条记录后返回1001到1020条记录，前1000条记录会被抛弃，查询和排序的代价非常高
- 第一种优化思路：从索引完成排序分页的操作，最后根据主键回表查询所需的其他列内容
例如：直接查询
按照索引分页后回表方式改写sql
- 第二种优化思路：把limit查询转换为某个位置的查询
假如需要查询第100页，则可以记录第99页最后一行的id(倒序或正序)，然后查询时where 条件大于或者小于99页最后一行的id,然后直接limit n就可以了，n为每页显示的行数
比较
3.3.7 使用sql提示
- USE INDEX
提示mysql参考使用的索引，可以让mysql不在考虑其他可用索引
比如：select count(1) from tb_name use index(index_name ) where xxxx ; 此时使用了index_name索引，其他索引忽略。
- ignore index
提示mysql忽略一个可用索引
比如： select count(1) from tb_name ignore index(index_name); 此时忽略index_name索引
- force index
强制mysql使用某个索引，使用情况，当where自句取id>1的值，因为数据库中大部分库表都是大于1的，所以会全表扫描，此时使用use index不可用，所以使用force index
比如; select * from tb_name force index(index_name) where id>1;
4，常用的sql技巧
4.1 正则的使用
| 序列 | 序列说明 |
| ^ | 在字符串的开始处进行匹配 |
| $ | 在字符串的末尾处进行匹配 |
| . | 匹配任意单个字符，包括换行符 |
| [...] | 匹配出括号内的任意字符 |
| [^...] | 匹配不出括号内的任意字符 |
| a* | 匹配零个或多个a（包括空串） |
| a+ | 匹配一个或多个a (不包括空串) |
| a? | 匹配一个或零个a |
| a1|a2 | 匹配a1或a2 |
| a(m) | 匹配m个a |
| a(m,) | 匹配m个或多个a |
| a(m,n) | 匹配m个到n个a |
| a(,n) | 匹配0到n个a |
| (....) | 将模式元素组成模式元素 |
- ^ 在字符串开头匹配
匹配是否以a开头 regexp
- $在字符串的末尾处匹配
- . 匹配单个任意字符，包括换行符
- [...] 匹配出括号内的任意字符
- [^...] 不匹配[]中的任意字符
4.2 利用rand()提取随机行
随机抽取n条数据：select * from tb_name order by rand() limit n
4.3 group by 的with rollup
WITH ROLLUP 可以检索出更多的分组聚合信息
比如：统计每日的商品数量 ，不加入with rollup
加入with rollup
with rollup 反应的是一种OLAP思想，可以满足用户想要得到的任何一个分组以及分组组合的聚合信息。with rollup 统计了每日的总数和所有的总数。注意：with rollup 不能和order by一起使用，且limit在with rollup后面
4.4 数据库名和表名大小写问题
由于windows,mac Os，Unix对库表名以及查询使用的大小写敏感不一致，所以最好将库表规范保存，且查询语句也规范使用
mysql通过存储引擎取 Innodb 索引目前有两种BTREE索引和HASH索引。
1072
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
