python/MySQL（索引、执行计划、BDA、分页）
---恢复内容开始---
python/MySQL（索引、执行计划、BDA、分页）
MySQL索引：
所谓索引的就是具有（约束和加速查找的一种方式）
创建索引的缺点是对数据进行（修改、更新、删除）比较慢！
索引共分为：
1、主键索引：
特性：加速查找、不能为空、不能重复
2、普通索引：
特性：加速查找
3、唯一索引：
特性：加速查找、可以为空、不能重复
4、联合索引：
特征：（多列）联合主键索引、联合唯一索引、联合普通索引
5、全文索引：
特征：对文本的内容进行分词，进行搜索
索引用途及案例：
在频繁查找使用的数据进行创建索引
加速查找：
通过设置得索引去查找速度较快；
例如：
1 mysql> select * from useru where uname='alex23232'; 2 +-------+-----------+---------------+--------+ 3 | id | uname | emlia | gender | 4 +-------+-----------+---------------+--------+ 5 | 23232 | alex23232 | 23232@.qq.com | 男 | 6 +-------+-----------+---------------+--------+ 7 1 row in set (0.04 sec) 8 =========================================== 9 10 上边是通过索引查找，速度快！ 11 12 =========================================== 13 mysql> select * from useru where emlia='23423@.qq.com'; 14 +-------+-----------+---------------+--------+ 15 | id | uname | emlia | gender | 16 +-------+-----------+---------------+--------+ 17 | 23423 | alex23423 | 23423@.qq.com | 男 | 18 +-------+-----------+---------------+--------+ 19 1 row in set (0.18 sec) 20 ========================================== 21 上边的就是没有通过索引进行查找，速度较慢！
无索引：是从前到后依次查找（那样相当于手中拿着一本厚厚的新华字典，如果想要查看数据就要从头一直翻到数据那页） 有索引：是从特殊符索引表里找到相应的位置，然后再找到数据存放在硬盘的位置。
索引类型：
hash索引： 这是一种索引方式，它把数据表中的设置为索引的哪一列转换成hash值开辟一个新的表格进行存储，当要进行索引查询时就去新开辟的表格中查找相应的存储部位。（hash很适合找单独数据，在找范围内的数据比较慢！） btree索引：也称为二叉树索引，在数据库中默认使用 （范围内可以快速查找！）
索引的操作：
创建普通索引：
1 create index in_de on useru(uname); 2 create index 索引名 on 需要创建的表名（表列）
创建唯一索引：
1 create unique index in_ss on useru(uname)
唯一索引比普通的索引要快（因为普通的索引会出现重复）
创建组合索引：（最左前缀匹配）
1 create index index__s on useru(uname,emali); 2 组合索引需要遵循最左前缀匹配 3 例如： 4 select * from useru where unaem='alex212132'； 这样就是在执行索引查询 （命中） 5 select * from useru where emali='234322@.qq.com'； 这样的就没有执行索引查询（未命中） 6 select * from useru where uname='alex2323' and emali='234234242@.11.com'; 这样就是在执行索引查询（命中）
索引合并：
1 create index in_s on useru（uname）； 2 create index in_c on useru(emali); 3 分别设置俩个索引，把他们合并进行查找； 4 select * from useru where uname='alex1212' and emali='9439493@.qq.com'; 5 select * from useru where emali='67453345@.qq.com' 6 组合索引没有最左前缀的限制
覆盖索引：
1 select uname from useru where uname='alex232343'; 2 在索引文件中直接获取数据
命中索引：
like
1 like ‘al%0’ 2 select * from useru where unaem like 'a%0';
未命中索
使用函数
1 mysql> select * from useru where reverse(uname)='0324xela'; 2 +------+----------+--------------+--------+ 3 | id | uname | emlia | gender | 4 +------+----------+--------------+--------+ 5 | 4230 | alex4230 | 4230@.qq.com | 男 | 6 +------+----------+--------------+--------+ 7 1 row in set (12.59 sec) 8 9 未命中
or 未命中
1 mysql> select * from useru where emlia='234234@.qq.com' or uname='alex23432'; 2 +--------+------------+----------------+--------+ 3 | id | uname | emlia | gender | 4 +--------+------------+----------------+--------+ 5 | 23432 | alex23432 | 23432@.qq.com | 男 | 6 | 234234 | alex234234 | 234234@.qq.com | 男 | 7 +--------+------------+----------------+--------+ 8 2 rows in set (0.05 sec) 9 10 mysql> select * from useru where uname='alex23432'; 11 +-------+-----------+---------------+--------+ 12 | id | uname | emlia | gender | 13 +-------+-----------+---------------+--------+ 14 | 23432 | alex23432 | 23432@.qq.com | 男 | 15 +-------+-----------+---------------+--------+ 16 1 row in set (0.00 sec) 17 未命中，因为查询当中有未创建索引导致加速失败
or 命中
1 mysql> select * from useru where id='323423' or uname='alex23432'; 2 +--------+------------+----------------+--------+ 3 | id | uname | emlia | gender | 4 +--------+------------+----------------+--------+ 5 | 23432 | alex23432 | 23432@.qq.com | 男 | 6 | 323423 | alex323423 | 323423@.qq.com | 男 | 7 +--------+------------+----------------+--------+ 8 2 rows in set (0.04 sec) 9 命中，因为俩个都设置了索引，加速成功
类型不一样
1 mysql> select * from useru where uname=3432; 2 Empty set, 65535 warnings (13.03 sec)
！= （不等于）
1 select * from useru where uname!='alex122'; 2 未命中，因为需要进行匹配 3 4 select * from uname where id！=232； 5 命中，因为主键还是会走索引
>
1 select * from useru where uname > 'alex232' 2 未命中，因为设置索引不是整型 3 4 select * from useru where id > 3232; 5 命中，因为id是整型，如果设置的索引是整型就会加速；
order by
1 select * from useru order by uname desc； 2 未命中，因为根据排序时候，选择的映射如果不是索引，则不走索引 3 select * from useru order by id desc;
组合索引最左前缀
1 如果组合索引为：(name,email) 2 name and email -- 使用索引 3 name -- 使用索引 4 email -- 不使用索引
其他注意事项：
避免使用select * count（1）或count（列）代替count（*） 创建表时尽量 char 代替 vachar 表的字段顺序固定长度的字段优先 组合索引代替多个单列索引（经常使用多个条件查询时） 尽量使用短索引 使用连接（join）来代替子查询（sub-queries） 连表时注意条件类型需一致 索引散列值（重复少）不适合建索引，例：性别不适合
执行计划：
explain +sql语句 用于显示sql执行信息参数，根据参数信息可以进行sql优化（模拟出运行时间）
1 mysql> explain select * from useru; 2 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+ 3 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 4 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+ 5 | 1 | SIMPLE | useru | NULL | ALL | NULL | NULL | NULL | NULL | 1835575 | 100.00 | NULL | 6 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+ 7 1 row in set, 1 warning (0.00 sec) 8 9 主要是查看type类型
select——type （查询类型）
1 SIMPLE 简单查询 2 3 PRIMARY 最外层查询 4 5 SUBQUERY 映射为子查询 6 7 DERIVED 子查询 8 9 UNION 联合 10 11 UNION RESULT 使用联合的结果 12 table 正在访问的表名 13 14 type 15 16 查询时分的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const 17 18 ALL 全表扫描，对于数据表从头到尾找一遍 19 20 特别的：如果limit限制，则找到之后就不在继续向下扫描 21 22 虽然上述俩个语言都会进行全表扫描，第二句使用了limit 则找到一个后就不在继续查找 23 24 index 全索引扫描，对索引从头到尾字找一遍 25 26 select id from useru； 27 28 ret 根据索引查找一个或多个值 29 30 select * from useru where uname='alex12122l; 31 32 33 34 EQ_REF 连接时使用primary key 或unique类型 35 36 select useru.id,useru.uname from useru left join usert on useru.id=usert.nid; 37 38 39 40 const 常量： 41 42 表最多有一个匹配行，因为仅有一行，在这行的列值可被优化器剩余部分认为是常数，const表很快，因为只读取一次 43 44 system 系统 45 46 表仅有一行（=系统表）这是const连接类型的一个特例。 47 48 select * from（select id from uname where id =1）as A; 49 50 51 52 possible_keys 53 54 可以使用的索引 55 56 key 57 58 真实使用的 59 60 key_len 61 62 MySQL中使用索引字节长度 63 64 rows 65 66 MYSQL 估计为了找到所需的行儿要读取的行数---------只是预估值 67 68 extra 该列包含MySQL解决查询的详细信息 69 70 “Using index” 71 72 此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。 73 74 “Using where” 75 76 这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 77 78 “Using temporary” 79 80 这意味着mysql在对查询结果排序时会使用一个临时表。 “Using filesort” 这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。 81 82 “Range checked for each record(index map: N)” 83 84 这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
慢日志查询：
配置MySQL自动记录慢日志
1 1 slow _query_log=OFF 是否开启慢日志记录 2 2 long_query_time=2 时间限制，超过此时间，则记录 3 3 slow_query_log_file=/usr/low/slow.log 日志文件 4 4 log_queseris_not_indexds=OFF 为使用索引的搜素是否记录 5 5 6 6 注：查看当前配置信息： 7 7 show variables like ‘%query%’ 8 8 修改当前配置： 9 9 set global 变量名=值
1 mysqldumpslow -s at -a /usr/local/var/mysql/MacBook-Pro-3-slow.log
1 1 """ 2 2 --verbose 版本 3 3 --debug 调试 4 4 --help 帮助 5 5 6 6 -v 版本 7 7 -d 调试模式 8 8 -s ORDER 排序方式 9 9 what to sort by (al, at, ar, c, l, r, t), 'at' is default 10 10 al: average lock time 11 11 ar: average rows sent 12 12 at: average query time 13 13 c: count 14 14 l: lock time 15 15 r: rows sent 16 16 t: query time 17 17 -r 反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first) 18 18 -t NUM 显示前N条just show the top n queries 19 19 -a 不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S' 20 20 -n NUM abstract numbers with at least n digits within names 21 21 -g PATTERN 正则匹配；grep: only consider stmts that include this string 22 22 -h HOSTNAME mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard), 23 23 default is '*', i.e. match all 24 24 -i NAME name of server instance (if using mysql.server startup script) 25 25 -l 总时间中不减去锁定时间；don't subtract lock time from total time 26 26 """
limit 分页：
无论是否有索引，limit分页是一个值得关注的问题：
1 mysql> select * from useru limit 10,10; 2 +----+--------+------------+--------+ 3 | id | uname | emlia | gender | 4 +----+--------+------------+--------+ 5 | 11 | alex11 | 11@.qq.com | 男 | 6 | 12 | alex12 | 12@.qq.com | 男 | 7 | 13 | alex13 | 13@.qq.com | 男 | 8 | 14 | alex14 | 14@.qq.com | 男 | 9 | 15 | alex15 | 15@.qq.com | 男 | 10 | 16 | alex16 | 16@.qq.com | 男 | 11 | 17 | alex17 | 17@.qq.com | 男 | 12 | 18 | alex18 | 18@.qq.com | 男 | 13 | 19 | alex19 | 19@.qq.com | 男 | 14 | 20 | alex20 | 20@.qq.com | 男 | 15 +----+--------+------------+--------+ 16 10 rows in set (0.00 sec)
这样如果数据多，查看的页数越多就相当于扫描全部文件在限制的位置停止，这样查询还不是走到索引、
又要实现分页功能还要走索引查询：
1 mysql> select 2 -> * 3 -> from 4 -> useru 5 -> where 6 -> id < (select id from (select id from useru where id < 970 order by id desc limit 40)as A order by A.id asc limit 1) 7 -> order by 8 -> id desc 9 -> limit 10; 10 +-----+---------+-------------+--------+ 11 | id | uname | emlia | gender | 12 +-----+---------+-------------+--------+ 13 | 929 | alex929 | 929@.qq.com | 男 | 14 | 928 | alex928 | 928@.qq.com | 男 | 15 | 927 | alex927 | 927@.qq.com | 男 | 16 | 926 | alex926 | 926@.qq.com | 男 | 17 | 925 | alex925 | 925@.qq.com | 男 | 18 | 924 | alex924 | 924@.qq.com | 男 | 19 | 923 | alex923 | 923@.qq.com | 男 | 20 | 922 | alex922 | 922@.qq.com | 男 | 21 | 921 | alex921 | 921@.qq.com | 男 | 22 | 920 | alex920 | 920@.qq.com | 男 | 23 +-----+---------+-------------+--------+ 24 10 rows in set (0.00 sec)
