MySQL-索引补充

1

2

3

4

5

6

7

8

9

10

11

12

13

- 查看表结构

????desc?表名

- 查看生成表的SQL

????show?create?table?表名

- 查看索引

????show?index?from??表名

- 查看执行时间

????set?profiling = 1;

????SQL...

????show profiles;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

由于索引是专门用于加速搜索而生，所以加上索引之后，查询效率会快到飞起来。

# 有索引

mysql>?select?*?from?tb1?where?name?=?'wupeiqi-888';

+-----+-------------+---------------------+----------------------------------+---------------------+

| nid |?name????????| email?????????????? | radom??????????????????????????? | ctime?????????????? |

+-----+-------------+---------------------+----------------------------------+---------------------+

| 889 | wupeiqi-888 | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 |

+-----+-------------+---------------------+----------------------------------+---------------------+

1 row?in?set?(0.00 sec)

# 无索引

mysql>?select?*?from?tb1?where?email =?'wupeiqi888@live.com';

+-----+-------------+---------------------+----------------------------------+---------------------+

| nid |?name????????| email?????????????? | radom??????????????????????????? | ctime?????????????? |

+-----+-------------+---------------------+----------------------------------+---------------------+

| 889 | wupeiqi-888 | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 |

+-----+-------------+---------------------+----------------------------------+---------------------+

1 row?in?set?(1.23 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

-?like?'%xx'

????select?*?from?tb1?where?name?like?'%cn';

- 使用函数

????select?*?from?tb1?where?reverse(name) =?'wupeiqi';

-?or

????select?*?from?tb1?where?nid = 1?or?email =?'seven@live.com';

????特别的：当or条件中有未建立索引的列才失效，以下会走索引

????????????select?*?from?tb1?where?nid = 1?or?name?=?'seven';

????????????select?*?from?tb1?where?nid = 1?or?email =?'seven@live.com'?and?name?=?'alex'

- 类型不一致

????如果列是字符串类型，传入条件是必须用引号引起来，不然...

????select?*?from?tb1?where?name?= 999;

- !=

????select?*?from?tb1?where?name?!=?'alex'

????特别的：如果是主键，则还是会走索引

????????select?*?from?tb1?where?nid != 123

- >

????select?*?from?tb1?where?name?>?'alex'

????特别的：如果是主键或索引是整数类型，则还是会走索引

????????select?*?from?tb1?where?nid > 123

????????select?*?from?tb1?where?num > 123

-?order?by

????select?email?from?tb1?order?by?name?desc;

????当根据索引排序时候，选择的映射如果不是索引，则不走索引

????特别的：如果对主键排序，则还是走索引：

????????select?*?from?tb1?order?by?nid?desc;

- 组合索引最左前缀

????如果组合索引为：(name,email)

????name?and?email???????-- 使用索引

????name?????????????????-- 使用索引

????email????????????????-- 不使用索引

1

2

3

4

5

6

7

8

9

- 避免使用select?*

-?count(1)或count(列) 代替?count(*)

- 创建表时尽量时?char?代替?varchar

- 表的字段顺序固定长度的字段优先

- 组合索引代替多个单列索引（经常使用多个条件查询时）

- 尽量使用短索引

- 使用连接（JOIN）来代替子查询(Sub-Queries)

- 连表时注意条件类型需一致

- 索引散列值（重复少）不适合建索引，例：性别不适合

1

2

3

4

5

6

7

mysql> explain?select?*?from?tb2;

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

| id | select_type |?table?| type | possible_keys |?key??| key_len | ref? |?rows?| Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

|? 1 | SIMPLE????? | tb2?? |?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|??? 2 |?NULL??|

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

1 row?in?set?(0.00 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

"""

--verbose??? 版本

--debug????? 调试

--help?????? 帮助

-v?????????? 版本

-d?????????? 调试模式

-s ORDER???? 排序方式

?????????????what to sort by (al, at, ar, c, l, r, t), 'at' is default

??????????????al: average lock time

??????????????ar: average rows sent

??????????????at: average query time

???????????????c: count

???????????????l: lock time

???????????????r: rows sent

???????????????t: query time

-r?????????? 反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)

-t NUM?????? 显示前N条just show the top n queries

-a?????????? 不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'

-n NUM?????? abstract numbers with at least n digits within names

-g PATTERN?? 正则匹配；grep: only consider stmts that include this string

-h HOSTNAME? mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),

?????????????default is '*', i.e. match all

-i NAME????? name of server instance (if using mysql.server startup script)

-l?????????? 总时间中不减去锁定时间；don't subtract lock time from total time

"""